Associe pedidos do Hive a dados de clientes

Introdução

Os pedidos da empresa varejista informam o que foi vendido, mas a região de cada cliente está em outra tabela. Copiar o texto da região para cada pedido repetiria informações. Em vez disso, você pode conectar o identificador de cliente de um pedido a um registro de cliente ao consultar os dados. Essa operação é uma junção (join).

Você inspecionará os dois lados dessa relação, enriquecerá pedidos pagos com regiões, explorará o que acontece quando não é possível encontrar um cliente e criará um relatório regional de receita. Uma junção precisa de uma regra significativa de correspondência: sem ela, linhas não relacionadas podem se combinar e inflar um relatório. A tabela de clientes preparada tem exatamente uma linha por identificador de cliente, portanto cada pedido correspondente encontra um único cliente.

Este laboratório prepara de forma independente os seis pedidos limpos, dois clientes e um exemplo separado de correspondência ausente. O Hive e o Beeline estão prontos. Você deve conhecer SELECT, WHERE, agrupamento e cálculos decimais de vendas; a sintaxe de junção é apresentada aqui. Não é necessário Java, Python nem arquivos de outro laboratório. Use o Terminal.

Encontre a chave de correspondência

Nesta etapa, você identificará como as duas tabelas descrevem os mesmos clientes. Comece no espaço de trabalho local:

cd /home/labex/project

Inspecione os registros fornecidos de pedidos e clientes:

cat /home/labex/project/data/orders.csv

cat /home/labex/project/data/customers.csv

Os pedidos repetem customer_id: um cliente pode fazer vários pedidos. A tabela de clientes tem um registro por customer_id, com c1 em East e c2 em West. Esse identificador é a chave de junção. Posições iguais de linhas ou nomes com aparência semelhante não são regras confiáveis de correspondência.

Leia e salve a tabela de consulta de clientes pelo Hive. O -u do Beeline escolhe o serviço, -n o nome de usuário e -e a instrução SQL. A saída TSV e os cabeçalhos desativados facilitam salvar as linhas de resultado; > substitui o arquivo local nomeado:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT customer_id, region FROM retail_joins.customers ORDER BY customer_id;" > /home/labex/project/output/customers.tsv

cat /home/labex/project/output/customers.tsv

Espere c1 East e c2 West como duas linhas separadas por tabulação. Para o pedido o1, agora você pode prever a correspondência: seu identificador c1 se conecta a East. Para o2, c2 se conecta a West. Essa tabela costuma ser chamada de dimensão ou tabela de consulta porque acrescenta contexto descritivo aos registros de pedidos.

Este exercício fornece chaves únicas de consulta. Em um conjunto real de dados, chaves de cliente duplicadas poderiam produzir várias linhas associadas por pedido e inflar a receita; conhecer a relação pretendida faz parte da validação de uma junção.

Enriqueça pedidos pagos com INNER JOIN

Nesta etapa, você adicionará uma região a cada pedido pago. orders o dá à tabela um alias curto, o; customers c dá à outra tabela o alias c. Uma referência como o.customer_id identifica qual tabela fornece uma coluna, especialmente quando ambas têm o mesmo nome de coluna.

INNER JOIN mantém linhas com correspondência nos dois lados. A condição explícita ON o.customer_id = c.customer_id define a regra de correspondência. Salve esta consulta completa com um here-document: o shell grava literalmente no arquivo as linhas entre o marcador inicial SQL entre aspas e o SQL final:

cat > /home/labex/project/queries/enriched-orders.sql <<'SQL'
SELECT o.order_id, o.customer_id, c.region,
       o.quantity * o.unit_price AS order_amount
FROM retail_joins.orders o
INNER JOIN retail_joins.customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'paid'
ORDER BY o.order_id;
SQL

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/enriched-orders.sql > /home/labex/project/output/enriched-orders.tsv

cat /home/labex/project/output/enriched-orders.tsv

A opção de execução de arquivo -f executa o SQL salvo. Espere quatro linhas:

Os quatro pedidos pagos encontraram um cliente. A junção acrescentou informações descritivas sem mudar seus valores calculados; os valores ainda somam 125.00. Um INNER JOIN descartaria um pedido sem cliente correspondente. Você observará esse caso separadamente para manter inalterados os dados limpos de relatório.

Mantenha um pedido sem correspondência com LEFT JOIN

Nesta etapa, você comparará os dois tipos de junção em um pequeno exemplo independente. Leia seus dois registros:

cat /home/labex/project/data/example-orders.csv

example1 se refere a c1, enquanto example2 se refere a c_missing, que não tem registro na tabela de consulta. Preveja o resultado do INNER JOIN antes de executá-lo:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT o.order_id, o.customer_id, c.region
FROM retail_joins.example_orders o
INNER JOIN retail_joins.customers c ON o.customer_id = c.customer_id
ORDER BY o.order_id;" > /home/labex/project/output/example-inner.tsv

cat /home/labex/project/output/example-inner.tsv

Apenas example1 deverá aparecer, com East. O pedido sem correspondência desapareceu porque INNER JOIN exige uma correspondência.

LEFT JOIN mantém cada linha da tabela à sua esquerda, mesmo que a tabela da direita não tenha uma chave correspondente. As colunas fornecidas pelo registro ausente da direita se tornam NULL. Mude apenas essa escolha de junção:

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -e "SELECT o.order_id, o.customer_id, c.region
FROM retail_joins.example_orders o
LEFT JOIN retail_joins.customers c ON o.customer_id = c.customer_id
ORDER BY o.order_id;" > /home/labex/project/output/example-left.tsv

cat /home/labex/project/output/example-left.tsv

Agora espere ambas as linhas: example1/c1/East e example2/c_missing/NULL. O NULL descreve uma correspondência ausente na tabela de consulta, não uma região conhecida chamada "NULL". O identificador original de cliente é mantido da tabela da esquerda, o que ajuda a identificar o cliente não resolvido.

Escolher INNER ou LEFT JOIN é uma decisão de negócio. Um relatório com regiões completas sobre dados limpos e totalmente correspondentes pode usar INNER JOIN; uma revisão de qualidade de dados pode precisar de LEFT JOIN para manter registros não resolvidos. A tabela de exemplo é separada de orders, portanto não altera os dados de vendas de seis linhas nem sua receita.

Concilie um relatório regional de vendas

Nesta etapa, você aplicará o agrupamento já aprendido aos registros enriquecidos. A junção fornece uma região, WHERE seleciona pedidos pagos e GROUP BY combina esses pedidos por região. Mantenha a condição explícita de correspondência ao adicionar agregação:

cat > /home/labex/project/queries/regional-sales.sql <<'SQL'
SELECT c.region, SUM(o.quantity * o.unit_price) AS revenue
FROM retail_joins.orders o
INNER JOIN retail_joins.customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'paid'
GROUP BY c.region
ORDER BY c.region;
SQL

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/regional-sales.sql > /home/labex/project/output/regional-sales.tsv

cat /home/labex/project/output/regional-sales.tsv

Espere East 70.00 e West 55.00. Juntos, eles conciliam com a mesma receita paga de 125.00 da origem sem junção. As chaves únicas de clientes e as correspondências completas significam que nenhum pedido pago foi multiplicado nem perdido.

Como uma pequena mudança de prática, preveja os valores regionais apenas para 14 de outubro. Aplique o predicado de data já aprendido às linhas de pedidos antes de agrupar:

cat > /home/labex/project/queries/regional-sales-one-day.sql <<'SQL'
SELECT c.region, SUM(o.quantity * o.unit_price) AS revenue
FROM retail_joins.orders o
INNER JOIN retail_joins.customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'paid' AND o.order_date = DATE '2026-10-14'
GROUP BY c.region
ORDER BY c.region;
SQL

beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/regional-sales-one-day.sql > /home/labex/project/output/regional-sales-one-day.tsv

cat /home/labex/project/output/regional-sales-one-day.tsv

Os valores agora deverão ser East 20.00 e West 25.00, conciliando com os 45.00 desse dia. A regra de correspondência permaneceu igual; apenas as linhas de pedidos selecionadas mudaram. Seu regional-sales.sql principal mantém o relatório completo dos dois dias.

Agora você consegue explicar tanto a nova coluna descritiva quanto os totais de negócio inalterados. A seguir, partições por data conectarão essas condições SQL de data à forma como arquivos diários são organizados e registrados.

Resumo

Você associou pedidos a chaves únicas de clientes, usou aliases para tornar a relação explícita e comparou INNER e LEFT JOIN em uma correspondência ausente. Depois, conciliou os totais regionais de vendas pagas com a origem e mudou uma condição de data sem alterar a junção. A seguir, você organizará dados diários com os metadados de partição do Hive.