Transposição de colunas Arrays no Apache Hive com o Cloud Dataproc

Nesta postagem, mostramos como fazer a transposição(pivoteamento) de dados dentro no Apache Hive. Todos os comandos foram executados dentro do Google Cloud, porém os comandos de Hive podem ser usados normalmente independentemente da plataforma.

O que é Apache Hive?

O Apache Hive permite a realizar consultas e análises nos dados que estejam hospedados no sistema de arquivos (HDFS) do Apache Hadoop.

Sendo considerado um Data Warehouse, ele oferece uma linguagem de consulta semelhante ao SQL, que se chama Hive Query Language, na sigla HQL ou HiveQL.

O Hive precisa de um database externo para guardar informações de seus metadados, como por exemplo, as estruturas de suas tabelas ou views. Geralmente os bancos MySQL e PostgreSQL são instalados no cluster para fazer esse armazenamento.

O que é Cloud Dataproc?

É a plataforma de big data totalmente gerenciado, para processar grandes quantidades de dados com rapidez, de forma econômica e em grande escala. Usando ferramentas de código aberto como o Apache Spark, o Apache Hive, o Apache Hadoop e o Apache Pig combinadas à escalabilidade dinâmica do Compute Engine e ao armazenamento escalável do Cloud Storage, o Dataproc oferece às equipes analíticas os mecanismos e a elasticidade para executar análises na escala de petabytes por uma fração do custo dos clusters locais tradicional, além de ser facilmente incorporado a outros serviços do Google Cloud Platform (GCP). Neste tutorial será usado para executar os comandos do Hive em Jobs.

O que é um Job?

Dentro do Dataproc, o Job seria um código que é executado dentro de um cluster, atualmente são aceitos diversos tipos, entre eles os principais são:

· Hadoop;· Spark;· SparkR;· PySpark;· Hive;· SparkSql;· Pig;· Presto.

O que é Cloud Storage?

Fornece, a nível mundial, um armazenamento de objetos altamente durável que escalona para exabytes de dados. É possível acessar dados instantaneamente de qualquer classe de armazenamento, integrar o armazenamento aos seus aplicativos com uma API exclusiva unificada e otimizar o preço e desempenho de forma fácil. Neste tutorial será usado para armazenar o arquivo da tabela externa do Hive.

Passo a Passo — Linha de comando

O primeiro passo a fazer é o login em sua conta do GCP, para este tutorial você vai precisar de uma conta que possua um projeto.

2. Vá até a barra superior e clique no ícone do Cloud Shell, igual a imagem abaixo.

3. Agora temos que preparar as variáveis de ambiente que usaremos nos outros passos:

a) Para definir a variável de região usaremos o código abaixo:

export REGION=us-central1

b) Para definir a variável de Zona usaremos o código abaixo:

export ZONE=us-central1-a

4. Nesse passo vamos definir o projeto a ser usado dentro do cloud shell, no campo [PROJECT_ID], insira o id do seu projeto:

gcloud config set project [PROJECT_ID]

5. Nesse passo vamos definir a Zona a ser usada dentro do cloud shell com base na variável que definimos antes:

gcloud config set compute/zone $ZONE

6. Nesse passo vamos definir a Região a ser usada dentro do cloud shell com base na variável que definimos antes:

gcloud config set compute/region $REGION

7. Após isso vamos definir a variável PROJECT com o id do projeto:

export PROJECT=$(gcloud info --format='value(config.project)')

8. Com o código abaixo a api do dataproc será habilitada, caso já esteja pode desconsiderar:

gcloud services enable dataproc.googleapis.com sqladmin.googleapis.com

9. Caso não exista, crie um cluster do dataproc com o comando abaixo:

gcloud dataproc clusters create meu-cluster \
--image-version 1.3 \
--region $REGION \
--subnet default \
--master-machine-type n1-standard-1 \
--master-boot-disk-size 500 \
--num-workers 2 \
--worker-machine-type n1-standard-1 \
--worker-boot-disk-size 500

Observações:

A propriedade “subnet” faz referência a sub-rede que usaremos no cluster.

A propriedade “master-machine-type” permite selecionar o tipo de máquina a ser usado pelo master.

A propriedade “worker-machine-type” permite selecionar o tipo de máquina a ser usado pelos workes.

10. Criaremos o bucket do Cloud Storage que será o repositório de dados do hive:

gsutil mb -l $REGION gs://[PROJECT_ID]-warehouse

11. Utilize o comando nano para gerar o arquivo gatos.csv com o seguinte conteúdo:

Garfield, 15, Laranja, Peixe|Leite|Lasanha
Mingau, 3, Branco, Leite
Matias, 8, Cinza, Leite

12. Copie seu arquivo para o seu bucket:

gsutil cp gatos.csv gs://[PROJECT_ID]-warehouse/gatos/

13. Neste passo vamos gerar um job que vai criar uma tabela no hive:

gcloud dataproc jobs submit hive \
--cluster meu-cluster \
--region $REGION \
--execute "CREATE EXTERNAL TABLE gatos( \
nome string, \
idade string, \
coloracao string, \
alimentos_preferidos array<string>)\
ROW FORMAT DELIMITED \
FIELDS TERMINATED BY ',' \
COLLECTION ITEMS TERMINATED BY '|'\
STORED AS INPUTFORMAT \
'org.apache.hadoop.mapred.TextInputFormat' \
OUTPUTFORMAT \
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'\
LOCATION 'gs://[PROJECT_ID]-warehouse/gatos/'"

Observações:

· A propriedade “submit” faz referência ao tipo de job que vamos executar, pode-se substituir o valor “hive” por “spark” ou “spark-sql” por exemplo.

· A propriedade “cluster” faz referência ao cluster que executaremos o job.

· A propriedade “region” faz referência a região em que o cluster está.

· A propriedade “execute” contêm o comando a ser executado, nesse caso a instrução de create.

13. Faça um select na tabela criada no passo anterior:

gcloud dataproc jobs submit hive \
--cluster meu-cluster \
--region $REGION \
--execute "select * from gatos"

O retorno será semelhante a este:

Repare no primeiro registro de gatos.alimentos_preferidos, no arquivo ele está como: Peixe|Leite|Lasanha. O trecho COLLECTION ITEMS TERMINATED BY ‘|’ do código de criação da tabela permite especificar o delimitador de campos array, nesse caso o pipe (‘|’), assim, ao consultarmos a tabela podemos pegar elementos isolados.

14. Faça um select na tabela criada no passo anterior:

gcloud dataproc jobs submit hive \
--cluster meu-cluster \
--region $REGION \
--execute "select * from gatos \
LATERAL VIEW explode(alimentos_preferidos) sss AS alimentos;"

No resultado final, uma linha vai acabar virando três linhas:

Observação: Esse exemplo é especifico para coluna que já esta como ARRAY, para trabalhar com coluna string use a função ‘split’ para transformar a coluna em um array, conforme código abaixo:

gcloud dataproc jobs submit hive \
--cluster meu-cluster \
--region $REGION \
--execute "select nome, alimentos from \
(select split('Peixe/Leite/Lasanha','/') as alimento, nome from gatos) t \
LATERAL VIEW explode(alimento) sss AS alimentos;"