Hive Join & Tutorial de subconsulta com exemplos

Índice:

Anonim

Neste tutorial, você aprenderá-

  • Consultas de junção
  • Diferentes tipos de junções
  • Subconsultas
  • Incorporação de scripts personalizados
  • UDFs (funções de definição do usuário)

Consultas de junção:

As consultas de junção podem ser executadas em duas tabelas presentes no Hive. Para entender claramente os conceitos de junção, estamos criando duas tabelas aqui,

  • Sample_joins (relacionado aos detalhes dos clientes)
  • Sample_joins1 (relacionado aos detalhes dos pedidos feitos pelos funcionários)

Passo 1) Criação da tabela "sample_joins" com os nomes das colunas ID, Nome, Idade, endereço e salário dos funcionários

Etapa 2) Carregar e exibir dados

Da captura de tela acima

  1. Carregando dados em sample_joins de Customers.txt
  2. Exibindo o conteúdo da tabela sample_joins

Etapa 3) Criação da tabela sample_joins1 e carregamento, exibindo dados

Na captura de tela acima, podemos observar o seguinte

  1. Criação da tabela sample_joins1 com colunas Orderid, Date1, Id, Amount
  2. Carregando dados em sample_joins1 de orders.txt
  3. Exibindo registros presentes em sample_joins1

Mais adiante, veremos diferentes tipos de junções que podem ser realizadas em tabelas que criamos, mas antes disso você deve considerar os seguintes pontos para junções.

Alguns pontos a serem observados em Joins:

  • Somente junções de igualdade são permitidas em junções
  • Mais de duas tabelas podem ser unidas na mesma consulta
  • As junções LEFT, RIGHT, FULL OUTER existem para fornecer mais controle sobre a cláusula ON para a qual não há correspondência
  • As junções não são comutativas
  • As junções são associativas à esquerda, independentemente de serem junções LEFT ou RIGHT

Diferentes tipos de junções

As associações são de 4 tipos,

  • Junção interna
  • União externa esquerda
  • Junção Externa Direita
  • Full Outer Join

Junção interna:

Os registros comuns às duas tabelas serão recuperados por este Inner Join.

Na captura de tela acima, podemos observar o seguinte

  1. Aqui, estamos realizando uma consulta de junção usando a palavra-chave JOIN entre as tabelas sample_joins e sample_joins1 com condição de correspondência como (c.Id = o.Id).
  2. A saída exibindo registros comuns presentes em ambas as tabelas, verificando a condição mencionada na consulta

Consulta:

SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);

União Externa Esquerda:

  • A linguagem de consulta do Hive LEFT OUTER JOIN retorna todas as linhas da tabela à esquerda, embora não haja correspondências na tabela à direita
  • Se a cláusula ON corresponder a zero registros na tabela certa, as junções ainda retornam um registro no resultado com NULL em cada coluna da tabela certa

Na captura de tela acima, podemos observar o seguinte

  1. Aqui estamos executando uma consulta de junção usando a palavra-chave "LEFT OUTER JOIN" entre as tabelas sample_joins e sample_joins1 com condição de correspondência como (c.Id = o.Id).

    Por exemplo, aqui estamos usando o id do funcionário como uma referência, ele verifica se o id é comum tanto à direita quanto à esquerda da mesa ou não. Ele atua como uma condição de correspondência.

  2. A saída exibe os registros comuns presentes em ambas as tabelas, verificando a condição mencionada na consulta.

    Os valores NULL na saída acima são colunas sem valores da tabela Right que é sample_joins1

Consulta:

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

União externa direita:

  • A linguagem de consulta do Hive RIGHT OUTER JOIN retorna todas as linhas da tabela à direita, embora não haja correspondências na tabela à esquerda
  • Se a cláusula ON corresponder a zero registros na tabela à esquerda, as junções ainda retornarão um registro no resultado com NULL em cada coluna da tabela à esquerda
  • As junções RIGHT sempre retornam registros de uma tabela à direita e registros correspondentes da tabela à esquerda. Se a tabela da esquerda não tiver valores correspondentes à coluna, ela retornará valores NULL naquele local.

Na captura de tela acima, podemos observar o seguinte

  1. Aqui estamos executando uma consulta de junção usando a palavra-chave "RIGHT OUTER JOIN" entre as tabelas sample_joins e sample_joins1 com condição de correspondência como (c.Id = o.Id).
  2. A saída exibindo registros comuns presentes em ambas as tabelas, verificando a condição mencionada na consulta

Consulta :

 SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Junção externa completa:

Ele combina os registros das tabelas sample_joins e sample_joins1 com base na condição JOIN fornecida na consulta.

Ele retorna todos os registros de ambas as tabelas e preenche valores NULL para os valores ausentes das colunas correspondentes em ambos os lados.

Na captura de tela acima, podemos observar o seguinte:

  1. Aqui estamos executando uma consulta de junção usando a palavra-chave "FULL OUTER JOIN" entre as tabelas sample_joins e sample_joins1 com condição de correspondência como (c.Id = o.Id).
  2. A saída exibe todos os registros presentes em ambas as tabelas, verificando a condição mencionada na consulta. Valores nulos na saída aqui indicam os valores ausentes nas colunas de ambas as tabelas.

Consulta

SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)

Subconsultas:

Uma consulta presente em uma consulta é conhecida como subconsulta. A consulta principal dependerá dos valores retornados pelas subconsultas.

As subconsultas podem ser classificadas em dois tipos

  • Subconsultas na cláusula FROM
  • Subconsultas na cláusula WHERE

Quando usar:

  • Para obter um determinado valor combinado de dois valores de coluna de tabelas diferentes
  • Dependência de valores de uma tabela em outras tabelas
  • Verificação comparativa dos valores de uma coluna de outras tabelas

Sintaxe:

Subquery in FROM clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE col1 IN (SubQuery);

Exemplo:

SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2

Aqui, t1 e t2 são nomes de tabelas. O colorido é a Subconsulta realizada na tabela t1. Aqui, a e b são colunas adicionadas em uma subconsulta e atribuídas a col1. Col1 é o valor da coluna presente na tabela principal. Esta coluna "col1" presente na subconsulta é equivalente à consulta da tabela principal na coluna col1.

Incorporando scripts personalizados:

O Hive oferece a possibilidade de escrever scripts específicos do usuário para os requisitos do cliente. Os usuários podem escrever seu próprio mapa e reduzir scripts para os requisitos. Eles são chamados de scripts personalizados incorporados. A lógica de codificação é definida nos scripts personalizados e podemos usar esse script no horário ETL.

Quando escolher Scripts incorporados:

  • Em requisitos específicos do cliente, os desenvolvedores precisam escrever e implantar scripts no Hive
  • Onde as funções integradas do Hive não funcionarão para requisitos de domínio específicos

Para isso, no Hive, ele usa a cláusula TRANSFORM para incorporar os scripts de mapa e redutor.

Nestes scripts personalizados incorporados, temos que observar os seguintes pontos

  • As colunas serão transformadas em string e delimitadas por TAB antes de entregá-las ao script do usuário
  • A saída padrão do script do usuário será tratada como colunas de string separadas por TAB

Script Embutido de Amostra,

FROM (FROM pv_usersMAP pv_users.userid, pv_users.dateUSING 'map_script'AS dt, uidCLUSTER BY dt) map_outputINSERT OVERWRITE TABLE pv_users_reducedREDUCE map_output.dt, map_output.uidUSING 'reduce_script'AS date, count;

A partir do script acima, podemos observar o seguinte

Este é apenas o script de exemplo para compreensão

  • pv_users é a tabela de usuários que contém campos como ID do usuário e data conforme mencionado em map_script
  • Script redutor definido na data e contagem das tabelas pv_users