Hive Join & SubQuery Handledning med exempel

Gå med i frågor

Join-frågor kan utföras på två tabeller som finns i BikupaFör att förstå kopplingskoncept tydligt skapar vi två tabeller här:

• sample_joins (relaterat till kunduppgifter)
• sample_joins1 (relaterat till orderuppgifter som lagts av anställda)

Steg 1) Skapande av tabellen "sample_joins" med kolumnnamnen Id, Namn, Ålder, adress och lön för de anställda. Skärmdumpen nedan visar CREATE TABLE-satsen och dess bekräftelse.

Steg 2) Laddar och visar data. Nästa skärmdump visar kommandot load följt av innehållet i tabellen.

Från skärmdumpen ovan:

1. Laddar data till sample_joins från Customers.txt
2. Visar sample_joins-tabellinnehåll

Steg 3) Skapande av tabellen sample_joins1, sedan laddning och visning av dess data, som visas på skärmbilden nedan.

Från skärmdumpen ovan kan vi observera följande:

1. Skapande av tabellen sample_joins1 med kolumnerna Orderid, Date1, Id och Amount
2. Laddar data till sample_joins1 från orders.txt
3. Visar poster som finns i sample_joins1

Framöver ska vi se de olika typerna av joins som kan utföras på de tabeller vi har skapat. Innan dess måste du överväga följande punkter om joins.

Några punkter att observera vid joins:

• Endast likhetskopplingar är tillåtna i kopplingar
• Fler än två tabeller kan sammanfogas i samma fråga
• LEFT-, RIGHT- och FULL OUTER-kopplingar finns för att ge mer kontroll över ON-klausulen 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

Likhetsbegränsningen återspeglar Hive som det var under många år. Från och med Hive 2.2.0 stöds komplexa uttryck i ON-klausulen (HIVE-15211), så ett villkor som inte är likhet accepteras i en aktuell utgåva. I äldre utgåvor måste villkoret vara ett likhetstest, och allt annat flyttas till en WHERE-klausul.

Olika typer av sammanfogningar

Det finns fyra typer av kopplingar. Dessa är:

• Inre koppling
• Vänster yttre skarv
• Höger yttre fog
• Full ytterskarv

Varje typ demonstreras nedan mot samma två tabeller, så det enda som ändras mellan exemplen är vilka omatchade rader som överlever.

Inre koppling

Posterna som är gemensamma för båda tabellerna hämtas av denna inre koppling. Resultatet i skärmbilden nedan innehåller endast de kunder som har en matchande order.

Från skärmdumpen ovan kan vi observera följande:

1. Här utför vi en join-fråga med hjälp av nyckelordet JOIN mellan tabellerna sample_joins och sample_joins1, med matchande villkor (c.Id = o.Id).
2. Utdata visar de gemensamma posterna som finns i båda tabellerna, valda genom att kontrollera villkoret som anges 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 anslutning

• HiveQL VÄNSTER UTRE 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-klausulen matchar noll poster i den högra tabellen returnerar kopplingen fortfarande en post i resultatet med NULL i varje kolumn från den högra tabellen.

Skärmdumpen nedan visar att alla kunder visas, inklusive de som inte har någon beställning.

Från skärmdumpen ovan kan vi observera följande:

1. Här utför vi en join-fråga med nyckelordet "LEFT OUTER JOIN" mellan tabellerna sample_joins och sample_joins1, med matchningsvillkoret (c.Id = o.Id). Till exempel använder vi här anställnings-ID:t som referens; det kontrollerar om id:t är gemensamt för både den högra och den vänstra tabellen. Det fungerar som matchningsvillkor.
2. Utdata visar de poster som valts av villkoret som nämns i frågan. NULL-värden i utdata ovan är kolumner utan värden från den högra tabellen, det vill säga 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 anslutning

• HiveQL 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-klausulen matchar noll poster i den vänstra tabellen returnerar kopplingen fortfarande en post i resultatet med NULL i varje kolumn från den vänstra tabellen.
• RIGHT-kopplingar returnerar alltid poster från den högra tabellen och matchande poster från den vänstra tabellen. Om den vänstra tabellen inte har något värde som motsvarar kolumnen, returnerar den NULL-värden på den platsen.

Skärmdumpen nedan visar en spegelbild av föregående resultat: varje beställning visas, oavsett om den matchar eller inte.

Från skärmdumpen ovan kan vi observera följande:

1. Här utför vi en join-fråga med hjälp av nyckelordet "RIGHT OUTER JOIN" mellan tabellerna sample_joins och sample_joins1, med matchande villkor (c.Id = o.Id).
2. Utdata visar de poster som valts genom att kontrollera villkoret som anges 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 anslutning

Den kombinerar poster från 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 de kolumner vars matchande värden saknas på någon av sidorna, som skärmdumpen nedan visar.

Från skärmdumpen ovan kan vi observera följande:

1. Här utför vi en join-fråga med hjälp av nyckelordet "FULL OUTER JOIN" mellan tabellerna sample_joins och sample_joins1, med matchande villkor (c.Id = o.Id).
2. Utdata visar alla poster som finns i båda tabellerna, valda genom att kontrollera villkoret som nämns i frågan. NULL-värden i utdata här anger de saknade värdena i 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

Joins placerar tabeller sida vid sida. En delfråga gör något annorlunda: den kapslar en fråga inuti en annan så att den yttre frågan kan arbeta utifrån ett resultat som redan har beräknats.

En fråga som finns inom en fråga kallas en delfråga. Huvudfrågan beror på de värden som returneras av delfrågan.

Delfrågor kan delas in i två typer:

• Delfrågor i FROM-klausulen
• Delfrågor i WHERE-klausulen

När du ska använda:

• För att få ett visst värde kombinerat från två kolumnvärden från olika tabeller
• Beroende av en tabells värden på andra tabeller
• Jämförande kontroll av en kolumns värden mot 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);

Exempel:

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

Här är t1 och t2 tabellnamn. Den inre satsen är delfrågan som utförs på tabell t1. Här är a och b kolumner som läggs till i delfrågan och tilldelas kolumn1. Kolumn1 är kolumnvärdet som finns i huvudtabellen. Kolumnen "kolumn1" som finns i delfrågan motsvarar huvudtabellfrågan i kolumn kolumn1.

Bädda in anpassade skript

Där en delfråga omformar data enbart med HiveQL, överför ett inbäddat skript rader till kod som skrivits utanför Hive.

Hive gör det möjligt att skriva användarspecifika skript för klientkrav. Användare kan skriva sina egna mappnings- och reduceringsskript för dessa krav. Dessa kallas inbäddade anpassade skript. Kodningslogiken definieras i det anpassade skriptet, och vi kan använda det skriptet vid ETL-tid.

När man ska välja inbäddade skript:

• Där klientspecifika krav innebär att utvecklare måste skriva och distribuera skript i Hive
• Där inbyggda Hive-funktioner inte fungerar för specifika domänkrav

För detta använder Hive TRANSFORM-klausulen för att bädda in både map- och reducer-skript.

I dessa inbäddade anpassade skript måste vi observera följande punkter:

• Kolumner kommer att omvandlas till strängar och avgränsas med 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 ett exempelskript för förståelse.

• pv_users är tabellen users, som har fält som userid och date som nämns i map_script.
• Reducer-skriptet definieras utifrån datumet och antalet i pv_users-tabellen.

Vanliga frågor