Hive Join & SubQuery-tutorial met voorbeelden

Sluit je aan bij zoekopdrachten

Join-query's kunnen worden uitgevoerd op twee tabellen die aanwezig zijn in BijenkorfOm de concepten van joins duidelijk te begrijpen, maken we hier twee tabellen aan:

• sample_joins (gerelateerd aan klantgegevens)
• sample_joins1 (gerelateerd aan orderdetails geplaatst door medewerkers)

Stap 1) Het aanmaken van de tabel "sample_joins" met de kolomnamen Id, Naam, Leeftijd, adres en salaris van de werknemers. De onderstaande schermafbeelding toont de CREATE TABLE-instructie en de bevestiging ervan.

Stap 2) Gegevens laden en weergeven. De volgende schermafbeelding toont de laadopdracht, gevolgd door de inhoud van de tabel.

Uit de bovenstaande schermafbeelding:

1. Gegevens laden in sample_joins vanuit Customers.txt
2. Tabelinhoud sample_joins weergeven

Stap 3) Het aanmaken van de tabel sample_joins1, het laden ervan en het weergeven van de gegevens, zoals te zien is in de onderstaande schermafbeelding.

Uit de bovenstaande schermafbeelding kunnen we het volgende afleiden:

1. Aanmaken van tabel sample_joins1 met de kolommen Orderid, Date1, Id en Amount
2. Gegevens laden in sample_joins1 vanuit orders.txt
3. Records weergeven die aanwezig zijn in sample_joins1

In het vervolg bekijken we de verschillende soorten joins die kunnen worden uitgevoerd op de tabellen die we hebben gemaakt. Voordat we dat doen, moet u rekening houden met de volgende punten over joins.

Enkele aandachtspunten bij joins:

• Alleen joins op basis van gelijkheid zijn toegestaan.
• Er kunnen meer dan twee tabellen in dezelfde query worden samengevoegd
• De LEFT-, RIGHT- en FULL OUTER-joins bestaan ​​om meer controle te bieden over de ON-clausule waarvoor geen overeenkomst bestaat.
• Joins zijn niet commutatief.
• Joins zijn links-associatief, ongeacht of het LINKS- of RECHTS-joins zijn

De gelijkheidsbeperking weerspiegelt de werking van Hive zoals die jarenlang was. Vanaf Hive 2.2.0 worden complexe expressies in de ON-clausule ondersteund (HIVE-15211), waardoor een niet-gelijkheidsvoorwaarde in de huidige release wordt geaccepteerd. In oudere releases moet de voorwaarde een gelijkheidstest zijn, waarbij al het andere in een WHERE-clausule wordt geplaatst.

Verschillende soorten joins

Er zijn vier soorten verbindingen. Deze zijn:

• Innerlijke join
• Linker buitenste join
• Rechter buitenste verbinding
• Volledige outer join

Elk type wordt hieronder gedemonstreerd aan de hand van dezelfde twee tabellen, dus het enige verschil tussen de voorbeelden is welke niet-overeenkomende rijen overblijven.

Innerlijke verbinding

De records die in beide tabellen voorkomen, worden via deze inner join opgehaald. Het resultaat in de onderstaande schermafbeelding bevat alleen de klanten met een overeenkomende bestelling.

Uit de bovenstaande schermafbeelding kunnen we het volgende afleiden:

1. Hier voeren we een join-query uit met het trefwoord JOIN tussen de tabellen sample_joins en sample_joins1, met de overeenkomende voorwaarde (c.Id = o.Id).
2. De uitvoer toont de records die in beide tabellen voorkomen, geselecteerd op basis van de voorwaarde die in de query is opgegeven.

Query:

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

Linker Outer Join

• HiveQL LEFT OUTER JOIN retourneert alle rijen uit de linkertabel, zelfs als er geen overeenkomsten in de rechtertabel zijn.
• Als de ON-clausule nul records in de rechtertabel vindt, retourneert de join nog steeds een record in het resultaat met NULL in elke kolom van de rechtertabel.

De onderstaande schermafbeelding laat zien dat alle klanten worden weergegeven, ook degenen zonder bestelling.

Uit de bovenstaande schermafbeelding kunnen we het volgende afleiden:

1. Hier voeren we een join-query uit met het trefwoord "LEFT OUTER JOIN" tussen de tabellen sample_joins en sample_joins1, met de overeenkomstvoorwaarde (c.Id = o.Id). We gebruiken hier bijvoorbeeld het werknemers-ID als referentie; er wordt gecontroleerd of het ID zowel in de rechtertabel als in de linkertabel voorkomt. Dit fungeert als de overeenkomstvoorwaarde.
2. De uitvoer toont de records die zijn geselecteerd op basis van de voorwaarde in de query. NULL-waarden in de bovenstaande uitvoer geven kolommen aan zonder waarden uit de juiste tabel, namelijk sample_joins1.

Query:

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

Rechts buitenste verbinding

• De HiveQL RIGHT OUTER JOIN retourneert alle rijen uit de rechtertabel, zelfs als er geen overeenkomsten in de linkertabel zijn.
• Als de ON-clausule nul records in de linkertabel vindt, retourneert de join nog steeds een record in het resultaat met NULL in elke kolom van de linkertabel.
• Bij een RIGHT join worden altijd records uit de rechtertabel en overeenkomende records uit de linkertabel geretourneerd. Als de linkertabel geen waarde heeft die overeenkomt met de kolom, worden daar NULL-waarden geretourneerd.

De onderstaande schermafbeelding toont het spiegelbeeld van het vorige resultaat: elke bestelling verschijnt, ongeacht of deze overeenkomt of niet.

Uit de bovenstaande schermafbeelding kunnen we het volgende afleiden:

1. Hier voeren we een join-query uit met behulp van het trefwoord "RIGHT OUTER JOIN" tussen de tabellen sample_joins en sample_joins1, met de overeenkomende voorwaarde (c.Id = o.Id).
2. De uitvoer toont de records die zijn geselecteerd op basis van de voorwaarde die in de query is vermeld.

Query:

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

Volledige Outer Join

Het combineert records uit zowel de tabellen sample_joins als sample_joins1 op basis van de JOIN-voorwaarde in de query.

Het retourneert alle records uit beide tabellen en vult de kolommen waar de overeenkomende waarden aan een van beide zijden ontbreken in met NULL-waarden, zoals de onderstaande schermafbeelding laat zien.

Uit de bovenstaande schermafbeelding kunnen we het volgende afleiden:

1. Hier voeren we een join-query uit met het trefwoord "FULL OUTER JOIN" tussen de tabellen sample_joins en sample_joins1, met de overeenkomende voorwaarde (c.Id = o.Id).
2. De uitvoer toont alle records die in beide tabellen aanwezig zijn, geselecteerd op basis van de voorwaarde in de query. NULL-waarden in de uitvoer geven aan dat er ontbrekende waarden zijn in de kolommen van beide tabellen.

Query:

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

Subquery's

Een join plaatst tabellen naast elkaar. Een subquery doet iets anders: het nestelt de ene query in de andere, zodat de buitenste query kan werken met een resultaat dat al is berekend.

Een query die binnen een query voorkomt, wordt een subquery genoemd. De hoofdquery is afhankelijk van de waarden die door de subquery worden geretourneerd.

Subquery's kunnen in twee typen worden ingedeeld:

• Subquery's in de FROM-clausule
• Subquery's in de WHERE-clausule

Wanneer te gebruiken:

• Om een ​​bepaalde waarde te combineren uit twee kolomwaarden uit verschillende tabellen
• Afhankelijkheid van de waarden in de ene tabel van de waarden in andere tabellen
• Vergelijkende controle van de waarden in één kolom ten opzichte van waarden in andere tabellen.

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);

Voorbeeld:

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

Hier zijn t1 en t2 tabelnamen. De inner statement is de subquery die wordt uitgevoerd op tabel t1. Hier zijn a en b kolommen die in de subquery worden toegevoegd en toegewezen aan col1. Col1 is de kolomwaarde die aanwezig is in de hoofdtabel. Deze kolom "col1" in de subquery is equivalent aan de query van de hoofdtabel op kolom col1.

Aangepaste scripts insluiten

Waar een subquery gegevens uitsluitend met HiveQL herschikt, geeft een ingebed script rijen door aan code die buiten Hive is geschreven.

Met Hive is het mogelijk om gebruikersspecifieke scripts te schrijven die voldoen aan de eisen van de klant. Gebruikers kunnen hun eigen map- en reduce-scripts schrijven voor die specifieke eisen. Deze worden embedded custom scripts genoemd. De code is gedefinieerd in het custom script en we kunnen dat script gebruiken tijdens het ETL-proces.

Wanneer moet je kiezen voor ingebedde scripts?

• Wanneer klantspecifieke eisen betekenen dat ontwikkelaars scripts in Hive moeten schrijven en implementeren.
• Wanneer de ingebouwde functies van Hive niet voldoen aan de specifieke domeinvereisten, zijn er situaties waarin de ingebouwde functies van Hive niet geschikt zijn.

Hiervoor gebruikt Hive de TRANSFORM-clausule om zowel map- als reducer-scripts in te sluiten.

Bij deze ingebedde, aangepaste scripts moeten we de volgende punten in acht nemen:

• De kolommen worden omgezet naar tekenreeksen en gescheiden door tabs voordat ze aan het gebruikersscript worden doorgegeven.
• De standaarduitvoer van het gebruikersscript wordt behandeld als door tabs gescheiden tekenreekskolommen.

Voorbeeld van een ingesloten script:

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;

Uit het bovenstaande script kunnen we het volgende afleiden. Dit is slechts een voorbeeldscript ter verduidelijking.

• pv_users is de gebruikerstabel, die velden bevat zoals userid en date, zoals vermeld in map_script.
• Het reducer-script is gebaseerd op de datum en het aantal gebruikers in de tabel pv_users.

Veelgestelde vragen