Aula 14 – Apache Sqoop – Continuação 01

toticavalcanti

Apache Sqoop – Big Data Analytics

Ferramentas de big data analytics do ecossistema hadoop

O Apache Sqoop intermedia o Hadoop file system e bancos relacionais (SGBDR)

Esse é o link da documentação oficial:

http://sqoop.apache.org/

Continuando

Vamos agora importar todas as tabelas do retail_db do mysql para o hdfs no formato Avro.

Na aula passada foi explicado o que são os formatos: avro, parquet, sequência e arquivo de texto.

Só pra uma rápida lembrada. 😉

Avro é um sistema de serialização de dados que suporta estruturas de dados ricas e uma codificação binária compacta.

O comando abaixo importa todas as tabelas no mysql do banco retail_db para a pasta /apps/hive/warehouse/retail_db/ no HDFS em formato Avro.

O –as-avrodatafile define que é pra usar o sistema Avro.

O -m 12 ou –num-mappers 12 diz: Use n tarefas de map para importar em paralelo.

Se não especificar ele vai usar o default que é 4.
sqoop import-all-tables \ -m 12 \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --as-avrodatafile \ --warehouse-dir=/apps/hive/warehouse/retail_db/

Entre na interface web da máquina cloudera http://192.168.56.101:8888/

O comando abaixo importa a tabela departments no mysql do banco retail_db para a pasta /user/root/departments no HDFS em formato Textfile.

sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --table departments \ --as-textfile \ --target-dir=/user/root/departments

O comando a seguir importa a tabela departments no mysql do banco retail_db para a pasta /user/root/departments_sequence_file no HDFS em formato Sequencefile.

Só pra lembrar, Sequencefile é um formato de arquivo binário de pares chave-valor.

sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --table departments \ --as-sequencefile \ --target-dir=/user/root/departments_sequence_file

O comando abaixo importa a tabela departments no mysql do banco retail_db para a pasta /user/root/departments_avro_file no HDFS em formato Avro.

sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --table departments \ --as-avrodatafile \ --target-dir=/user/root/departments_avro_file

Na pasta onde você tá no shell, no sistema de arquivos local da máquina cloudera, quando você roda comandos como esses acima, ele vai criando arquivos com extensão .avsc e .java, nesse caso do comando logo acima, ele cria departments.avsc e departments.java.

Provavelmente, se você rodou os comandos anteriores, você tenha outros .avsc gerados nessa mesma pasta.

Você pode criar tabelas Hive com esses arquivos .avsc

Vamos interagir com Hive também agora.

Copie pelo shell putty mesmo, o departments.avsc do sistema de arquivos local da máquina cloudera, para o HDFS na pasta /user/root/avsc:

Crie a pasta no HDFS

hadoop fs -mkdir /user/root/avsc/

Agora copie o departments.avsc para o HDFS

hadoop fs -put departments.avsc /user/root/avsc/

Entre no shell do hive digitando hive no shell putty.

O prompt muda para

hive>

Nele digite:
CREATE EXTERNAL TABLE departments ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' LOCATION 'hdfs:///user/root/hive/departments' TBLPROPERTIES ('avro.schema.url'='hdfs://192.168.56.101/user/root/avsc/departments.avsc');

O comando sql hive acima, cria a tabela externa chamada departments usando como entrada o esquema que copiamos anteriormente para o HDFS em /user/root/avsc/departments.avsc, e como saída cria a tabela Hive departments no formato Avro na pasta no hdfs: user/root/hive/departments

No hdfs://192.168.56.101, o hdfs:// é porque queremos acessar a pasta no hdfs e o 192.168.56.101 é o ip da máquina cloudera.

O restante, /user/root/avsc/departments.avsc é o caminho para o arquivodepartments.avsc no hdfs.

O departments.avsc serviu de molde para a criação da tabela Hive em formato Avro.

Se quiser fazer o drop da tabela departments no shell do hive, use:
drop table departments;

Lembrando que, para remover pastas no hdfs pode ser usado a interface web da máquina cloudera ou acessar a máquina via shell putty:

hadoop fs -rm -R /user/root/hive/departments

O –boundary-query é usado para criar divisões nos dados para paralelizar.

Para entender melhor, entre no mysql:

mysql -u root -p

Digite cloudera em password

Uma vez dentro do mysql digite:

mysql>use retail_db;
mysql> select * from departments;

A saída será:
+—————+—————–+
| department_id | department_name |
+—————+—————–+
| 2 | Fitness |
| 3 | Footwear |
| 4 | Apparel |
| 5 | Golf |
| 6 | Outdoors |
| 7 | Fan Shop |
+—————+—————–+

Então essa é a estrutura da tabela departments, podemos usar o –boundary-query para pegar todos os registros da tabela departments.

Note que a tabela usa o separador pipe “|“

Agora use –boundary-query para pegar todos os registros:sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --table departments \ --target-dir /user/root/departments \ -m 2 \ --boundary-query "select min(department_id ), max(department_id ) from departments"

Pela interface web da máquina cloudera acesse a pasta /user/root/departments ou mesmo pelo shell com:
hadoop fs -ls /user/root/departments/

Você verá que foram criados dois arquivos, algo como: part-m-00000 e part-m-00001.

Resultado do -m 2.

Se eu quiser só o primeiro e o segundo registro, eu posso usar o id dos registros:

sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --table departments \ --target-dir /user/root/departments \ -m 2 \ --boundary-query "select 2, 3 from departments"

Dê um –boundary-query na tabela order_items, pegando apenas os dois primeiros registros:
sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --table order_items \ --target-dir /user/root/order_items \ -m 2 \ --boundary-query "select 1, 2 from order_items"

Veja que os campos da tabela order_items são esses abaixo:

order_item_id,
order_item_order_id,
order_item_product_id,
order_item_quantity,
order_item_subtotal,
order_item_product_price

Vamos supor que eu queira pegar apenas os campos order_item_order_id, order_item_product_id e order_item_product_price, então, eu posso usar o –columns:

sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --table order_items \ --target-dir /user/root/order_items \ -m 2 \ --boundary-query "select 1, 2 from order_items" \ --columns order_item_order_id,order_item_product_id,order_item_product_price
Para pegar todos os 172198 registros do order_items:

sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --table order_items \ --target-dir /user/root/order_items \ -m 2 \ --boundary-query "select min(order_item_id), max(order_item_id) from order_items"

Vamos usar agora o –query e o –split-by.

sqoop import \ --connect "jdbc:mysql://localhost:3306/retail_db" \ --username=root \ --password=cloudera \ --query="select * from orders join order_items on orders.order_id = order_items.order_item_order_id where \$CONDITIONS" \ --target-dir /user/root/order_join \ --split-by order_id \ --num-mappers 4
Esse comando dá um join da tabela orders com a order_items.

Divide pelo order_id, ou seja, o identificador da compra.

Gera então 4 arquivos, resultado do –num-mappers 4: part-m-00000, part-m-00001, part-m-00002 e part-m-00003.

Pela interface web da máquina cloudera acesse a pasta /user/root/order_join ou mesmo pelo shell com:
hadoop fs -ls /user/root/order_join/