Hive Join & SubQuery Handledning med exempel
David Carter
Gå med i frågor
Anslutningsfrågor kan utföras på två tabeller som finns i Hive. För förståelse Gå med Concepts helt klart här skapar vi två tabeller här,
- Sample_joins (relaterat till kundinformation)
- Sample_joins1 (Relaterat till beställningsdetaljer gjorda av anställda)
Steg 1) Skapande av tabell "sample_joins" med kolumnnamn ID, Namn, Ålder, adress och lön för de anställda
Steg 2) Ladda och visa data
Från ovanstående skärmdump
- Laddar data till sample_joins från Customers.txt
- Visar sample_joins-tabellinnehåll
Steg 3) Skapande av sample_joins1-tabell och laddning, visning av data
Från ovanstående skärmdump kan vi observera följande
- Skapande av tabell sample_joins1 med kolumner Orderid, Date1, Id, Amount
- Laddar data till sample_joins1 från orders.txt
- Visar poster som finns i sample_joins1
Framöver kommer vi att se olika typer av joins som kan utföras på tabeller vi har skapat men innan dess måste du överväga följande punkter för joins.
Några punkter att observera i Joins:
- Endast Equality joins är tillåtna i Joins
- Fler än två tabeller kan sammanfogas i samma fråga
- LEFT, RIGHT, FULL OUTER-anslutningar finns för att ge mer kontroll över ON-klausul som det inte finns någon matchning för
- Joins är inte kommutativa
- Joins är vänsterassociativa oavsett om de är LEFT- eller RIGHT-anslutningar
Olika typer av sammanfogningar
Anslutningar är av 4 typer, dessa är
- Inre koppling
- Vänster yttre Sammanfogning
- Höger yttre anslutning
- Full yttre anslutning
Inre koppling:
Posterna som är gemensamma för båda tabellerna kommer att hämtas av denna Inner Join.
Från ovanstående skärmdump kan vi observera följande
- Här utför vi en kopplingsfråga med JOIN-nyckelordet mellan tabellerna sample_joins och sample_joins1 med matchande villkor som (c.Id= o.Id).
- Utdata som visar vanliga poster som finns i båda tabellen genom att kontrollera villkoret som nämns i frågan
Fråga:
SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);
Vänster yttre skarv:
- Hive-frågespråk LEFT OUTER JOIN returnerar alla rader från den vänstra tabellen även om det inte finns några matchningar i den högra tabellen
- Om ON-satsen matchar noll poster i den högra tabellen, returnerar joinningarna fortfarande en post i resultatet med NULL i varje kolumn från den högra tabellen
Från ovanstående skärmdump kan vi observera följande
- Här utför vi en kopplingsfråga med nyckelordet "LEFT OUTER JOIN" mellan tabellerna sample_joins och sample_joins1 med matchande villkor som (c.Id= o.Id).Till exempel här använder vi anställd id som referens, det kontrollerar om id är vanligt i höger och vänster tabellen eller inte. Det fungerar som matchande tillstånd.
- Utdata som visar vanliga poster som finns i båda tabellen genom att kontrollera villkoret som nämns i frågan. NULL-värden i ovanstående utdata är kolumner utan värden från den högra tabellen som är sample_joins1
Fråga:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Höger yttre skarv:
- Hive-frågespråk RIGHT OUTER JOIN returnerar alla rader från den högra tabellen även om det inte finns några matchningar i den vänstra tabellen
- Om ON-satsen matchar noll poster i den vänstra tabellen, returnerar joinningarna fortfarande en post i resultatet med NULL i varje kolumn från den vänstra tabellen
- RIGHT joins returnerar alltid poster från en höger tabell och matchade poster från den vänstra tabellen. Om den vänstra tabellen inte har några värden som motsvarar kolumnen, kommer den att returnera NULL-värden på den platsen.
Från ovanstående skärmdump kan vi observera följande
- Här utför vi en kopplingsfråga med nyckelordet "RIGHT OUTER JOIN" mellan tabellerna sample_joins och sample_joins1 med matchande villkor som (c.Id= o.Id).
- Utdata som visar vanliga poster som finns i båda tabellen genom att kontrollera villkoret som nämns i frågan
Fråga:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Full yttre sammanfogning:
Den kombinerar poster för både tabellerna sample_joins och sample_joins1 baserat på JOIN-villkoret som anges i frågan.
Den returnerar alla poster från båda tabellerna och fyller i NULL-värden för kolumnerna som saknar värden matchade på båda sidor.
Från ovanstående skärmdump kan vi observera följande:
- Här utför vi en kopplingsfråga med nyckelordet "FULL OUTER JOIN" mellan tabellerna sample_joins och sample_joins1 med matchande villkor som (c.Id= o.Id).
- Utdata som visar alla poster som finns i båda tabellen genom att kontrollera villkoret som nämns i frågan. Nullvärden i utdata här indikerar de saknade värdena från kolumnerna i båda tabellerna.
Fråga
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Underfrågor
En fråga som finns i en fråga kallas en underfråga. Huvudfrågan kommer att bero på de värden som returneras av underfrågorna.
Underfrågor kan klassificeras i två typer
- Underfrågor i FROM-satsen
- Underfrågor i WHERE-satsen
När du ska använda:
- För att få ett visst värde kombinerat från två kolumnvärden från olika tabeller
- En tabells beroende av andra tabeller
- Jämförande kontroll av en kolumns värden från andra tabeller
Syntax:
Subquery in FROM clause SELECT <column names 1, 2…n>From (SubQuery) <TableName_Main > Subquery in WHERE clause SELECT <column names 1, 2…n> From<TableName_Main>WHERE col1 IN (SubQuery);
Exempelvis:
SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2
Här är t1 och t2 tabellnamn. Den färgade är Subquery utförd på tabell t1. Här är a och b kolumner som läggs till i en underfråga och tilldelas till col1. Kol1 är kolumnvärdet som finns i huvudtabellen. Denna kolumn "col1" som finns i underfrågan motsvarar huvudtabellfrågan i kolumn kol1.
Bädda in anpassade skript
Bikupa ger möjlighet att skriva användarspecifika skript för kundens krav. Användarna kan skriva sin egen karta och minska skript för kraven. Dessa kallas inbäddade anpassade skript. Kodningslogiken definieras i de anpassade skripten och vi kan använda det skriptet i ETL-tiden.
När du ska välja inbäddade skript:
- I kundspecifika krav måste utvecklare skriva och distribuera skript i Hive
- Där Hives inbyggda funktioner inte kommer att fungera för specifika domänkrav
För detta i Hive använder den TRANSFORM-satsen för att bädda in både kart- och reducerskript.
I detta inbäddade anpassade skript måste vi observera följande punkter
- Kolumner kommer att omvandlas till strängar och avgränsas av TAB innan de ges till användarskriptet
- Standardutdata från användarskriptet kommer att behandlas som TAB-separerade strängkolumner
Exempel på inbäddat skript,
FROM ( FROM pv_users MAP pv_users.userid, pv_users.date USING 'map_script' AS dt, uid CLUSTER BY dt) map_output INSERT OVERWRITE TABLE pv_users_reduced REDUCE map_output.dt, map_output.uid USING 'reduce_script' AS date, count;
Från ovanstående skript kan vi observera följande
Detta är bara exempelskriptet för att förstå
- pv_users är användartabellen som har fält som användar-id och datum som nämns i map_script
- Reducer-skript definierat på datum och antal pv_users-tabeller
