Dicas de Impala-Shell e Beeline

Dicas que trazem funcionalidades ótimas das ferramentas linha de comando

Fala pessoal, tudo bem?

Recentemente acabei de terminar o cronograma de treinamento preparatório para a prova de certificação da Cloudera CCA Data Analyst, esse curso é realmente muito bom e caso você tenha interesse em faze-lo é um pack de 3 treinamentos do Coursera, você pode acessa-los nesse link.

Nesse treinamento tem alguns materiais extra chamados "with honors" que na minha opinião são grandes diferenciais, trazendo tarefas que otimizam absurdamente a manipulação de dados em alguns cenários com ferramentas linha de comando. Esse post é meio que um resumo dos que eu achei bem foda.

O que são essas ferramentas de linha de comando?

Se você já atuou com outros bancos de dados como Postgres, MySQL e Oracle por exemplo, sabe que além da interface gráfica para fazer queries, esses bancos dispõem de interfaces linha de comando para acesso as informações e consultas como o psql do Postgres, o sql*plus do Oracle e assim vai, trabalhei muito tempo com Oracle e eu fazia muito uso da tela preta com o sql*plus.

Nem todo ambiente você tem acesso a interfaces gráficas como Hue, SQL Developer, DBeaver e etc, pra esses casos usamos interfaces via linha de comando, com elas alguns processos de automação são possíveis, gerando scripts e algumas tarefas que não seriam possíveis em ferramentas gráficas.

Esse post vai dar algumas dicas sobre essas ferramentas, vamos começar:

Bora vê saparada aeeee…

Overview rápido sobre as ferramentas

Acho que já falei em algum post aqui antes, mas nem o Hive nem o Impala armazenam dados, eles são os meios para os fins, ou seja, os dados mesmos ficam armazenados no HDFS ou no S3.

O Hive é um metadado, que cria uma estrutura de tabela apontando para o arquivo final que estão nesses locais. A ferramenta via linha de comando para acessar os dados do Hive é o beeline.

O Impala é uma ferramenta que foi projetada pela Cloudera para acesso mais rápido as informações, tanto do Hive como de outros bancos de dados como Kudu e HBase usando a linguagem SQL (Que não são linguagens nativas desses bancos). Além da vantagem de centralizar o acesso a vários bancos numa única linguagem, ele também faz um cache de consultas já realizadas, ou seja, se fiz um select de uma tabela, e eu tenho essa informação no cache e não preciso trazer de novo. O acesso ao Impala via linha de comando é com o utilitário impala-shell.

Existem outras vantagens do Impala mas fica pra outra conversa. Segue um desenho tosco de como isso funciona basicamente:

O Impala acaba atuando como tradutor das linguagens dos outros bancos para SQL

Usando uma VM maneira clandestina

O curso que eu fiz disponibiliza o download de uma VM que tem o Cloudera 5.13 instalado, com Hue, Hive e Impala, use o VMware/Virtualbox pra subir na sua máquina, o pré requisito é ter no mínimo 8gb de RAM no seu computador, para baixar essa VM clique aqui.

Com essa VM você tem tudo para realizar os testes desse artigo e até dar uma curiada em como funciona o HDFS e outras coisas.

Principais vantagens da linha de comando

Geralmente quando usamos ferramentas gráficas, como o HUE e outras, temos um retorno limitado de linhas, o HUE mesmo limita a saída em 100000 linhas, sendo que as vezes uma tabela tem mais que isso, via linha de comando tenho o retorno completo.

Outro ponto é se caso eu queira exportar essa consulta para um arquivo .csv ou .txt por exemplo? O modo gráfico até faria, mas limitaria o numero de registros, já via linha de comando não.

Um ultimo ponto que uso desde o Oracle, vamos supor que eu queira automatizar alguns batches, ou funções administrativas como atualização de estatísticas nas tabelas, posso criar scripts (Python ou Shell por exemplo) e usar ferramentas de agendamento como Rundeck e outras. No fundo dos scripts estão os comandos que você rodará já já.

Usando o Beeline

Como disse o Beeline é a ferramenta para logar no Hive, usando os exemplos da maquina virtual para logar, abra o terminal Linux e rode o seguinte comando

beeline -u jdbc:hive2://localhost:10000/default -n training -p

Agora vou explicar o comando:

• -u: é o parâmetro que pede a URL de comunicação com o Hive
• jdbc: é o tipo de conexão que será feito, por padrão nesse ambiente a conexão é usando um driver JDBC, mas poderia ser um ODBC também em ambientes Microsoft ou outros possíveis, o mais recomendado é o JDBC mesmo.
• hive2: A conexão JDBC pode usar várias libs, mysql, oracle, postgres, nesse caso estamos usando a lib do Hive.
• localhost: como esse Hive está na maquina, acabei usando localhost mesmo (localhost quer dizer ela própria), mas no seu caso coloque o endereço onde o serviço Hive está rolando no seu cluster.
• 10000: é a porta padrão do Hive.
• default: é o database que no caso quero me conectar, poderia ser outra caso ela exista no banco, tanto como eu posso não colocar nada nesse parâmetro, mas ele inicia o beeline sem se conectar em nenhum banco.
• -n: Nome do usuário
• -p: Senha do usuário que podemos deixar vazio e digitar num prompt ou digitar na linha de comando mesmo, o ruim é que é inseguro e a senha ficaria exposta.

Se tudo deu certo no seu comando as mensagens exibidas serão mais ou menos assim:

Parabéns, agora é só rodar as queries

OBS: esse comando não contempla autenticação, Kerberos, Criptografia na conexão entre outros parâmetros, rode o comando `beeline -h` para ver a ajuda em demais parâmetros.

Usando o Impala-shell

Agora vamos ao login com o Impala-shell. É bem mais simples que o beeline, pois ele já considera que no server onde estou rodando o bash existe um Impala daemon rodando, sendo assim ele já tem as informações padrão como host e porta, mas assim como o Beeline que citei acima existem outros vários parâmetros que podem ser usados, basta olhar o help usando o comando "impala-shell -h".

Vamos logar no database default com o impala-shell:

impala-shell -d default

Só sobrou o parâmetro -d para explicar, ele diz em qual database você esta logando, mas é opcional também, posso setar depois de logado no Impala. Se você logou, a tela vai ser mais ou menos essa:

Impala-shell OK

Você vai notar no bash depois do localhost o :21000, que é a porta padrão do Impala-shell maaassssss….

OBS: Numa conexão externa com o Impala usando o driver JDBC a porta padrão é a 21050 e não a 21000, a 21000 é so do impala-shell.

exitBom agora que sabemos como logar nos 2 vamos a manipulação básica que é a mesma nos 2, é bem parecido com o MySQL.

• show databases: Mostra todos os databases o qual o usuário logado tem acesso.
• use nomedodatabase: Tem a mesma função do "-d" do Impala e do /default na conexão do Hive, seta qual o database vou usar.
• show tables: Lista todas as tabelas do database o qual você selecionou.
• !q ou !quit: Esse é o comando pra sair do Beeline, mas um CTRL+C também funciona.
• exit ou CTRL+D: Para sair do impala-shell

Bom por enquanto foram os comandos básicos e tal, vamos agora para as dicas do que pode ser feito.

Fazendo consultas no modo não interativo

Aprendemos como usar o Beeline e o Impala-shell logando neles de forma interativa, realizando as consultas após login. No modo interativo você precisa sair manualmente da ferramenta. O modo não interativo permite que a gente execute as consultas numa única linha de comando e saindo automaticamente do programa, isso facilita muito quando precisamos criar scripts para automatizar tarefas e coisas do gênero.

No Beeline

Usaremos a mesma sintaxe no Beeline, com acréscimo dos parâmetros abaixo de acordo com a necessidade:

• -e : quando quero executar uma consulta SQL, por exemplo -e 'select * from fun.games, nesse exemplo pedir o bash ele já traz o resultado dessa consulta e sai automaticamente do beeline. (sempre adiciona aspas e não precisa do ponto e virgula como no bash)
• -f : A ideia é a mesma mas a consulta está em um arquivo SQL por exemplo. O parâmetro -f é de file. Ficaria o mesmo comando do modo interativo só adicionamos -f nomedoarquivo.sql , mas não necessariamente ele precisa ter a extensão sql, um arquivo comum com o conteúdo na linguagem HQL já canta.
• --silent=true : O Beeline retorna varias informações do Hive que podem ser desnecessárias para essas consultas, usamos esse parâmetro pra que seja retornado somente o conteúdo da consulta. A diferença desse parâmetro é que ele deve vir logo no início do comando, antes do -uno caso.

Segue um exemplo abaixo:

Fiz uma consulta na tabela planes do database fly usando o silent

No Impala-shell

Aqui funciona da mesma forma só que com parâmetros diferentes, se liga.

• -q : Equivalente ao -e do beeline, é usado pra rodar uma consulta na linha de comando. Mesmo esquema usando aspas na instrução select e não precisa do ponto e virgula. Seria "q" de query.
• -f : Nesse caso é exatamente a mesma coisa do Beeline, usado rodar as consultas SQL que estão em um arquivo.
• --quiet é equivalente ao silent do Beeline, a ideia é suprimir as mensagens desnecessárias do Impala-shell.

DICA: Sempre quando eu tenho campos de tabelas que possuem espaços por exemplo "nome pai", uso o escape do S.O, colocando o $ na frente da consulta e \'nome pai\' no nome do campo, vai ficar mais ou menos assim impala-shell -q $'select '\nome pai\' from database.tabela'

Segue um exemplo:

Nesse exemplo não coloquei user e senha mas fiz um alias pro campo da tabela com espaço

Formatando a saída do Beeline e Impala-Shell

No HUE você tem uma limitação de 100000 linhas na consulta e se por acaso a tabela ou o resultado tem mais do que isso, eu não consigo exportar o resultado correto no formato .csv por exemplo, ou .txt. Por padrão tanto o Beeline como o Impala-shell colocam bordas nos resultado, isso facilita a visualização na tela é e chamado de pretty printing, mas no caso de gerar os formatos citados devemos desativar esse tipo de saída e mostrar como queremos o resultado, vou mostrar abaixo como fazer isso nas 2 ferramentas.

No Beeline

Abaixo vou mostrar alguns parâmetros que podem ajudar na geração de seu arquivo, recomendo usar o silent=true assim já elimina uma boa quantidade de informações desnecessárias.

• --outputformat : desativa o pretty printting e dá a opções dos formatos de output como csv2 (separado por virgula) e tsv2 (separado por tab)
• --showHeader : Por padrão sempre vai vir o cabeçalho com o nome das colunas, caso queira desativar, basta colocar essa opção =false.

OBS: ambas as informações devem vir antes da consulta, ou seja, dos parâmetros -e ou -f

O resultado é enviado sempre para tela, para gerar o arquivo basta usar no final do comando > nomedoarquivo.csv ou tsv se for o caso.

Exemplo da saída de formato csv gerado na tela.

No Impala-shell

Aqui é o contrario, quando você muda o formato da saída o cabeçalho é desativado, sendo assim você deve ativa-lo, segue os parâmetros abaixo.

• --delimited : desabilita o pretty printing e o padrão de delimitador é o tab nesse caso.
• --output_delimiter : Caso você não queira usar o delimitador padrão de tab, pode usar esse parâmetro e expecificar qual o delimitador vai usar, por exemplo --output_delimiter=',' vai gerar a saída separada por virgula.
• --print_header : como disse por padrão ele não mostra o cabeçalho, caso queira ativar use esse parâmetro.

OBS: Assim como no caso do Beeline, todos esses parâmetros citados devem vir antes no -q ou -f.

• -o : Para gerar o arquivo com o conteúdo, seria "o" de output, use -o nomedoarquivo.csv ou o formato que desejar de acordo com o delimitador.

Gerando um arquivo .csv com a saída da query

Bom passei algumas malandragens com essas 2 ferramentas, logo faço um post 2 com outras funcionalidades legais, espero que tenham gostado, deixa uma palminha ae e segue a gente.

Abraço…

Anselmo Borges

`