SQL para Hadoop e o inverso, Parte 1: técnicas básicas de intercâmbio de dados

Nesta série de artigos, analisaremos um conjunto de métodos distintos para a integração entre Hadoop e os bancos de dados SQL tradicionais, incluindo métodos de troca de dados simples, compartilhamento e troca de dados ativos entre os dois sistemas e o uso de camadas baseadas em SQL sobre o Apache Hadoop, incluindo HBase e Hive, agindo como o método de integração. Na Parte 1, examinaremos alguns dos aspectos arquitetônicos básicos da troca de informações e as técnicas fundamentais para realizar o intercâmbio de dados.

Martin C. Brown, Director of Documentation

Martin BrownEscritor profissional há mais de 15 anos, Martin (MC) Brown é autor e contribuidor de mais de 26 livros abordando diversos assuntos, incluindo o recém-publicado Getting Started with CouchDB. Ele tem experiência em uma ampla gama de linguagens e plataformas de desenvolvimento: Perl, Python, Java, JavaScript, Basic, Pascal, Modula-2, C, C++, Rebol, Gawk, Shellscript, Windows, Solaris, Linux, BeOS, Microsoft WP, Mac OS, entre outras. Atualmente, ele trabalha como Diretor de documentação na Continuent.



01/Nov/2013

Big Data e SQL

"Big Data" é um termo usado com frequência há quase uma década. Em conjunto com tecnologias como NoSQL, os Big Data são considerados os substitutos das anteriormente bem-sucedidas soluções RDBMS que usam SQL. Atualmente, o DB2®, Oracle, o Microsoft® SQL Server MySQL e o PostgreSQL dominam o espaço de SQL e ainda abrangem uma porção considerável do mercado geral. Os Big Data e os sistemas e serviços de banco de dados que os acompanham tornaram-se partes adicionais dos sistemas modernos. No entanto, como é possível integrar seus armazenamentos de dados baseados em SQL existentes com o Hadoop de maneira a aproveitar as diferentes tecnologias sempre que forem necessárias? Examinaremos os aspectos arquitetônicos básicos da troca de informações e as técnicas fundamentais para realizar o intercâmbio de dados.


Considerações sobre dados e consultas

A consideração mais importante ao trocar informações entre o Hadoop e o SQL é o formato de dados das informações. O formato deve ser criado inteiramente a partir da perspectiva das informações e do motivo pelo qual estão sendo exportadas.

Simplesmente exportar seus dados e reimportá-los no Hadoop não soluciona nenhum problema. É necessário saber exatamente o que está importando, por que tal informação está sendo importada e qual é o resultado esperado do processo.

Antes de analisarmos por que os dados estão sendo trocados, em primeiro lugar, devemos considerar a natureza da troca de dados. Ela é unidirecional? Ou é bidirecional?

A troca de dados unidirecional — do SQL para o Hadoop, ou do Hadoop para o SQL— é prática em situações nas quais os dados estão sendo transportados para aproveitar a funcionalidade de consulta, e a origem não é a solução de banco de dados associada. Por exemplo, dados somente de texto, ou os resultados de um programa ou uma análise computacional, podem ser armazenados no Hadoop, processados com o MapReduce e armazenados no SQL (consulte a Figura 1).

Figura 1. Conversões de Hadoop para SQL

O inverso não é muito comum, em que as informações são extraídas do SQL no Hadoop, mas pode ser usado para processar conteúdos baseados em SQL que geram texto, como blogs, fóruns, CRM e outros sistemas (consulte a Figura 2).

Figura 2. Conversões de SQL para Hadoop

A troca de dados bidirecional é mais comum e oferece o melhor de ambos os recursos em termos de troca e processamento de dados (consulte a Figura 3).

Figura 3. Conversões bidirecionais

Embora haja diversos exemplos, o mais comum é converter grandes conjuntos de dados lineares e conjuntos de dados textuais do SQL em informações resumidas que podem ser processadas por um cluster do Hadoop. As informações resumidas podem ser importadas de volta para seu armazenamento de SQL. Isso é especialmente útil nos casos em o processamento de dados de grandes conjuntos de dados em uma consulta de SQL seria muito demorado. Um exemplo seria um grande conjunto de pontuações de revisão ou contagem de palavras/termos.

IBM InfoSphere BigInsights

O InfoSphere BigInsights torna a integração entre o Hadoop e o SQL muito mais simples, pois fornece as ferramentas e os mecanismos necessários para exportar e importar dados entre diferentes bancos de dados. Usando o InfoSphere BigInsights, é possível definir origens de bancos de dados, visualizações, consultas e outros critérios de seleção e convertê-los automaticamente em uma variedade de formatos antes de importar o conjunto diretamente no Hadoop (consulte Recursos para obter mais informações).

Por exemplo, é possível criar uma consulta que extraia os dados e preencha um array JSON com os dados de registro. Depois de exportada, uma tarefa pode ser criada para processar e dividir os dados antes de exibi-los, ou importar os dados processados e exportá-los de volta ao DB2.

Faça o download do InfoSphere BigInsights Quick Start Edition, uma versão complementar e disponível para download do InfoSphere BigInsights. Usando a Quick Start Edition, é possível testar os novos recursos desenvolvidos pela IBM para ampliar o valor do Hadoop de software livre, como Big SQL, analítica de texto e BigSheets.

Em geral, há três motivos principais para criar uma interface entre o SQL e o Hadoop:

  1. Exportar para armazenamento — O Hadoop oferece uma solução prática para armazenar grandes quantidades de dados usados com pouca frequência em um formato que pode ser consultado, processado e extraído. Por exemplo: registros de uso, logs de acesso e informações de erro são práticas para serem inseridas em um cluster do Hadoop, para que seja possível aproveitar a arquitetura HDFS. Um recurso secundário desse tipo de exportação é o fato de que as informações podem ser processadas ou analisadas e convertidas em um formato que pode ser usado novamente.
  2. Exportar para análise — Dois casos comuns são exportar para reimportar no SQL e exportar a saída da análise para que seja usada diretamente em seu aplicativo (analisar e armazenar o resultado no JSON, por exemplo). Nesse caso, o Hadoop é vantajoso, pois permite o processamento de informações distribuídas em larga escala, em vez do processamento de host de uma única tabela, ofereci do no SQL. Com a rota de análise, as informações originais, em geral, são mantidas, mas o processo de análise é usado para fornecer uma base estatística ou de resumo que funcione em conjunto com os dados originais.
  3. Exportar para processamento — As exportações baseadas em processamento são projetadas para obter as informações originais brutas da origem, processá-las e reduzir ou simplificá-las e, em seguida, armazenar essas informações de volta para substituir os dados originais. Esse tipo de troca é usado com mais frequência nos casos em que as informações da origem foram captadas, mas as informações originais brutas não são mais necessárias. Por exemplo, os dados de log de diversas formas podem ser alterados com facilidade para uma estrutura mais simples, seja buscando tipos de evento específicos ou resumindo os dados para contagens de erros ou ocorrências específicos. Em geral, os dados brutos não são necessários aqui. Reduzir esses dados por meio do Hadoop e carregar as estatísticas de resumo de volta economiza tempo e facilita a consulta do conteúdo.

Com esses princípios básicos em mente, analisaremos as técnicas para uma troca de dados básica entre o SQL e o Hadoop.


Exportar dados a partir do SQL

Ao exportar do SQL, a principal consideração é o formato das informações que são geradas. Como o Hadoop não é um banco de dados tabular, é necessário escolher um formato flexível para os dados que serão processados por ele. Uma opção é o formato CSV, caso você deseje trabalhar com informações tabulares puras, mas também é possível usar texto bruto com separadores ou identificadores adequados.

Para estruturas complexas, pode ser necessário armazenar as informações em uma estrutura que permita a separação e distribuição fáceis. Um exemplo seria gerar dados do registro como JSON e exportar os blocos de dados, por exemplo, 10 mil registros por arquivo. Usar um formato de encapsulamento flexível como o JSON soluciona muitos dos problemas gerados pelo intercâmbio de dados.

Usar uma exportação de dump ou consulta padrão

A maior parte dos bancos de dados e interfaces de SQL tem um método para exportar os dados em formatos específicos. Por exemplo, no MySQL, é possível criar um arquivo CSV usando a linha de comando, como exibido na Listagem 1.

Lista 1. Criar um arquivo CSV usando a linha de comando
SELECT title, subtitle, servings, description into OUTFILE  'result.csv'
FIELDS TERMINATED BY ',' FROM recipes t;

A mesma solução existe no DB2 (veja a Listagem 2).

Lista 2. Criar um arquivo CSV no DB2
EXPORT TO result.csv OF DEL MODIFIED BY NOCHARDEL SELECT title, subtitle,
servings, description FROM recipes;

O arquivo resultante pode ser carregado diretamente no Hadoop por meio do HDFS. Gerar a mesma saída com um script simples no Perl, Python ou Ruby é tão simples quanto isso.

Gravar um programa customizado

Dependendo do conjunto de dados, usar um aplicativo customizado para exportar os dados pode ser mais prático. Isso é válido principalmente nos casos de dados estruturados, nos quais as informações que você deseja armazenar estão baseadas no conteúdo de diversas tabelas e estruturas.

Em geral, o método mais fácil é usar dados estruturados, chegar a um acordo sobre um formato ou estrutura de saída (para que possa ser analisado pelo Hadoop) e, em seguida, descartar essas informações.

Por exemplo, ao processar dados de receitas para buscar temas e encadeamentos comuns, é possível usar a ferramenta interna para buscar o registro da receita, incluindo os ingredientes, o método de preparo e outros dados e, em seguida, usar o objeto criado da receita para enviar as informações para processamento no Hadoop, armazenando cada receita como um objeto JSON (consulte a Listagem 3).

Lista 3. Exportar dados complexos
use JSON;
use Foodware;
use Foodware::Public;
use Foodware::Recipe;

my $fw = Foodware->new();

my $recipes = $fw->{_dbh}->get_generic_multi('recipe','recipeid',
    { active => 1});

my $js = new JSON;

foreach my $recipeid (keys %{$recipes})
{
    my $recipe = new Foodware::Recipe($fw,$recipeid,{ measgroup => 'Metric',
                                                      tempgroup => 'C',});

    my $id = $recipe->{title};
    $id =~ s/[ ',\(\)]//g;
    my $record = {
        _id => $id,
        title => $recipe->{title},
        subtitle => $recipe->{subtitle},
        servings => $recipe->{servings},
        cooktime => $recipe->{metadata_bytag}->{totalcooktime},
        preptime => $recipe->{metadata_bytag}->{totalpreptime},
        totaltime => $recipe->{metadata_bytag}->{totaltime},
        keywords => [keys %{$recipe->{keywordbytext}} ],
        method => $recipe->{method},
        ingredients => $recipe->{ingredients},
        comments => $recipe->{comments},
    };

    foreach my $ingred (@{$recipe->{ingredients}})
    {
        push(@{$record->{ingredients}},
             {
                 meastext => $ingred->{'measuretext'},
                 ingredient => $ingred->{'ingredonly'},
                 ingredtext => $ingred->{'ingredtext'},
             }
            );
    }

    print to_json($record),"\n";
}

Os dados são exportados para um arquivo que contém os dados da receita (consulte a Listagem 4).

Lista 4. Arquivo contendo os dados da receita
{
   "_id" : "WarmpotatotunaandCheshiresalad",
   "comments" : null,
   "preptime" : "20",
   "servings" : "4",
   "keywords" : [
      "diet@wheat-free",
      "diet@peanut-free",
      "diet@corn-free",
      "diet@citrus-free",
      "meal type@salads",
      "diet@shellfish-free",
      "main ingredient@fish",
      "diet@demi-veg",
      "convenience@add bread for complete meal",
      "diet@gluten-free"
   ],
   "subtitle" : "A change from cold salads...",
   "totaltime" : "35",
   "cooktime" : "15",
   "ingredients" : [
      {
         "scaled_fromqty" : 100,
         "_error_ingredid" : 1,
...
      }
    ]
}

O resultado pode ser carregado diretamente no HDFS e processado por uma tarefa MapReduce adequada, para que as informações necessárias sejam extraídas. Um benefício dessa abordagem estruturada é o fato de que permite realizar quaisquer pré-processamentos necessários ao envio, incluindo estruturar as informações em um formato que é possível ser usado na infraestrutura do Hadoop MapReduce.

A frase "importar no Hadoop" significa que basta copiar as informações no HDFS para que elas sejam disponibilizadas (consulte a Listagem 5).

Lista 5. Copiar as informações no HDFS
$ hdfs dfs mkdir recipes
$ hdfs dfs -copyFromLocal recipes.json recipes

Depois que os arquivos forem copiados, eles podem ser usados pelas tarefas do Hadoop MapReduce conforme o necessário.

Para maior flexibilidade no HDFS, o envio pode ser dividido em diversos arquivos, os quais podem ser carregados. Dependendo do seu caso de uso e dos requisitos de processamento, extrair os dados em arquivos individuais (um por registro nocional) pode ser mais eficaz para o processamento distribuído.


Usar o Sqoop para mover dados

O Sqoop é uma ferramenta adicional para o Hadoop que se conecta a um banco de dados existente usando um driver JDBC e importa tabelas e bancos de dados do JDBC de origem diretamente no HDFS. Para a maior parte das importações, nas quais os dados brutos das tabelas do SQL estão sendo importados no Hadoop sem processamento, o Sqoop oferece o processo mais simples e eficiente para mover os dados. Por exemplo, todas as tabelas em um único banco de dados podem ser carregadas usando a Listagem 6.

Lista 6. Carregar todas as tabelas em um único banco de dados
$ sqoop import-all-tables --connect jdbc:mysql://192.168.0.240/cheffy
    --username=cheffy

Para os drivers que oferecem suporte, use a opção --direct para ler os dados diretamente e gravá-los no HDFS. O processo é muito mais rápido, pois não necessita de arquivos de intervenção. Ao carregar dados dessa maneira, os diretórios são criados no HDFS de acordo com os nomes das tabelas. Por exemplo, no conjunto dos dados de receita, as informações do log de acesso estão na tabela access_log e os dados importados estão gravados nos arquivos de texto no diretório access_log (consulte a Listagem 7).

Lista 7. Visualizar dados importados do Sqoop
$ hdfs dfs -ls access_log
Found 6 items
-rw-r--r--   3 cloudera cloudera          0 2013-08-15 09:37 access_log/_SUCCESS
drwxr-xr-x   - cloudera cloudera          0 2013-08-15 09:37 access_log/_logs
-rw-r--r--   3 cloudera cloudera   36313694 2013-08-15 09:37 access_log/part-m-00000
-rw-r--r--   3 cloudera cloudera   36442312 2013-08-15 09:37 access_log/part-m-00001
-rw-r--r--   3 cloudera cloudera   36797470 2013-08-15 09:37 access_log/part-m-00002
-rw-r--r--   3 cloudera cloudera   36321038 2013-08-15 09:37 access_log/part-m-00003

Por padrão, os arquivos são divididos em blocos de aproximadamente 30 MB, e os dados são separados por vírgulas (consulte a Listagem 8).

Lista 8. Dados da tabela Sqoop convertidos para CSV
1,1,1135322067,09890012-11583713-542922105,recipeview,779
2,1,1135322405,09890012-11583713-542922105,recipeview,288
3,89,1135327750,26458011-11487731-455118105,search-ingredient,
4,89,1135327750,26458011-11487731-455118105,ingredient,pork
5,89,1135327750,26458011-11487731-455118105,ingredient,cheese
6,89,1135327765,26458011-11487731-455118105,recipeview,1421

Para selecionar tabelas individuais, use o código na Listagem 9.

Lista 9. Selecionar tabelas individuais
$ sqoop import-all-tables --connect jdbc:mysql://192.168.0.240/cheffy --username
=cheffy  --table access_log

Para selecionar colunas individuais desta tabela, use o código fornecido na Listagem 10.

Lista 10. Selecionar colunas individuais
$ sqoop import-all-tables --connect jdbc:mysql://192.168.0.240/cheffy
 --username=cheffy --table access_log --columns id,userid,operation

Em vez de selecionar individualmente as tabelas e colunas, uma abordagem mais prática é usar uma consulta para especificar as informações a serem enviadas. Ao usar esse método, você deve utilizar a variável $CONDITIONS em sua instrução e especificar a coluna a ser usada ao dividir os dados em pacotes individuais usando a opção --split-by , conforme exibido na Listagem 11.

Lista 11. Especificar as informações a serem enviadas
$ sqoop import-all-tables --connect jdbc:mysql://192.168.0.240/cheffy --username
=cheffy --query 'select recipeid,recipe,description from recipe WHERE $CONDITION'
--split-by id

No entanto, uma limitação do Sqoop é o fato de que oferece habilidade limitada para formatar e criar as informações. Para dados complexos, as funções de exportar e carregar de uma ferramenta customizada podem oferecer maior funcionalidade.


Extrair dados do Hadoop

Ao processar dados brutos e processados a partir do Hadoop, é necessário obter o envio dos arquivos pela tarefa do Hadoop. Assim como com a exportação, você deve garantir que sua tarefa do Hadoop envie as informações em um formato que possa ser analisado de maneira eficaz.

Importar para o SQL

Usar o CSV é simples e rápido, porém, para estruturas mais complexas, pode ser necessário usar a rota JSON novamente, visto que ela facilita todo o processo de conversão e envio.

Disponibilizar as informações requer o uso da ferramenta HDFS para enviar seus arquivos de saída de volta para o sistema de arquivos, no qual é possível carregar —$ hdfs dfs -copyToLocal processed_logs/*, por exemplo. Depois que os arquivos estão disponíveis, é possível carregar as informações usando o método mais adequado para a informação e estrutura de origem.

Exportar a partir do Sqoop

Como com o processo de importação, o Sqoop oferece um método simplificado de conversão das informações de sua tarefa do Hadoop de volta para uma tabela do SQL.

Ao enviar as informações resultantes do Sqoop, use o formato CSV para uma exportação mais fácil. E, para importar as informações, é necessário criar uma tabela adequada para aceitar os logs processados. Por exemplo, a partir de nossos logs de acesso, a saída do Hadoop mapeou os dados em resumos do número de operações, por isso, é necessário primeiro criar uma tabela adequada: CREATE TABLE summary_logs (operation CHAR(80), count int). Em seguida, as informações podem ser importadas diretamente do Hadoop para sua tabela do SQL (consulte a Listagem 12).

Lista 12. Exportar do Hadoop para o SQL
$ sqoop export --connect jdbc:mysql://192.168.0.240/cheffy --username=root
    --export-dir processed_log --table processed_log
13/08/15 10:04:34 INFO manager.MySQLManager: Preparing to use a MySQL streaming
    resultset.
13/08/15 10:04:34 INFO tool.CodeGenTool: Beginning code generation
13/08/15 10:04:35 INFO manager.SqlManager: Executing SQL statement: SELECT t.
    * FROM `access_log` AS t LIMIT 1
13/08/15 10:04:35 INFO manager.SqlManager: Executing SQL statement: SELECT t.
    * FROM `access_log` AS t LIMIT 1
13/08/15 10:04:35 INFO orm.CompilationManager: HADOOP_MAPRED_HOME
    is /usr/lib/hadoop-mapreduce
13/08/15 10:04:35 INFO orm.CompilationManager: Found hadoop core jar at:
 /usr/lib/hadoop-mapreduce/hadoop-mapreduce-client-core.jar
Note: /tmp/sqoop-cloudera/compile/8034e8d9feb8c1b0f69a52fede8d1da7/access_log.java
    uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/08/15 10:04:37 INFO orm.CompilationManager: Writing jar file:
 /tmp/sqoop-cloudera/compile/8034e8d9feb8c1b0f69a52fede8d1da7/access_log.jar
13/08/15 10:04:37 INFO mapreduce.ExportJobBase: Beginning export of access_log
13/08/15 10:04:39 WARN mapred.JobClient: Use GenericOptionsParser for parsing the
    arguments. Applications should implement Tool for the same.
13/08/15 10:04:39 INFO input.FileInputFormat: Total input paths to process : 4
13/08/15 10:04:39 INFO input.FileInputFormat: Total input paths to process : 4
13/08/15 10:04:39 INFO mapred.JobClient: Running job: job_201308150649_0006
13/08/15 10:04:40 INFO mapred.JobClient:  map 0% reduce 0%
13/08/15 10:04:57 INFO mapred.JobClient:  map 2% reduce 0%
...
13/08/15 10:08:06 INFO mapred.JobClient:
    CPU time spent (ms)=27470
13/08/15 10:08:06 INFO mapred.JobClient:
    Physical memory (bytes) snapshot=317607936
13/08/15 10:08:06 INFO mapred.JobClient:
    Virtual memory (bytes) snapshot=2076659712
13/08/15 10:08:06 INFO mapred.JobClient:
    Total committed heap usage (bytes)
    =188350464
13/08/15 10:08:06 INFO mapreduce.ExportJobBase: Transferred 139.1333 MB in
    207.5656 seconds (686.3975 KB/sec)
13/08/15 10:08:06 INFO mapreduce.ExportJobBase: Exported 2401906 records.

O processo está concluído. Mesmo no nível resumido, estamos olhando para 2,4 milhões de registros de dados simplificados retirados de um armazenamento de conteúdo cerca de 600 vezes maior.

Com as informações importadas, agora podemos realizar consultas e estruturas rápidas e simples nos dados. Por exemplo, esse resumo das principais atividades leva cerca de 5 segundos (veja a Figura 4).

Figura 4. Operações de resumo

No conjunto de dados completo, o projeto levou quase uma hora. De maneira semelhante, uma consulta dos principais termos de pesquisa levou menos de um segundo, em comparação a mais de três minutos, uma economia de tempo que possibilita a inclusão de uma consulta na página inicial (veja a Figura 5).

Figura 5. Pesquisa resumida dos ingredientes

Estes são exemplos simplificados do processamento de redução externa sendo usado no Hadoop, mas que demonstram de maneira eficaz as vantagens da interface externa.


Conclusões

Não é complicado incluir e excluir informações, baseadas nos dados do SQL, nos dados do Hadoop, contanto que você saiba quais são os dados, seu formato e como as informações devem ser processadas e representadas internamente. A conversão, exportação, o processamento e a importação são surpreendentemente fáceis.

As soluções neste artigo abordaram dumps de informações de conjuntos de dados inteiros que podem ser exportados, processados e importados no Hadoop. O processo pode ser SQL para Hadoop, Hadoop para SQL ou SQL para Hadoop e de volta para SQL. Na realidade, toda a sequência pode ser automatizada ou inserida em script, mas isso será abordado em outro tópico nesta série.

Na Parte 2, analisaremos exemplos mais avançados de como realizar essa conversão e o envio de conteúdo usando uma das camadas do SQL sobre o HDFS. Também definiremos a base para o fornecimento de uma transmissão de dados completa, em tempo real, para processamento e armazenamento.

Recursos

Aprender

Obter produtos e tecnologias

Discutir

Comentários

developerWorks: Conecte-se

Los campos obligatorios están marcados con un asterisco (*).


Precisa de um ID IBM?
Esqueceu seu ID IBM?


Esqueceu sua senha?
Alterar sua senha

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


A primeira vez que você entrar no developerWorks, um perfil é criado para você. Informações no seu perfil (seu nome, país / região, e nome da empresa) é apresentado ao público e vai acompanhar qualquer conteúdo que você postar, a menos que você opte por esconder o nome da empresa. Você pode atualizar sua conta IBM a qualquer momento.

Todas as informações enviadas são seguras.

Elija su nombre para mostrar



Ao se conectar ao developerWorks pela primeira vez, é criado um perfil para você e é necessário selecionar um nome de exibição. O nome de exibição acompanhará o conteúdo que você postar no developerWorks.

Escolha um nome de exibição de 3 - 31 caracteres. Seu nome de exibição deve ser exclusivo na comunidade do developerWorks e não deve ser o seu endereço de email por motivo de privacidade.

Los campos obligatorios están marcados con un asterisco (*).

(Escolha um nome de exibição de 3 - 31 caracteres.)

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


Todas as informações enviadas são seguras.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=80
Zone=Information Management
ArticleID=950983
ArticleTitle=SQL para Hadoop e o inverso, Parte 1: técnicas básicas de intercâmbio de dados
publish-date=11012013