Hive Join & SubQuery-tutorial met voorbeelden

Door: David Carter David Carter
Hours Bijgewerkt

Sluit je aan bij zoekopdrachten

Join-query's kunnen worden uitgevoerd op twee tabellen die aanwezig zijn in Hive. Voor begrip Doe mee Concepts duidelijk hier maken we hier twee tabellen,

  • Sample_joins (gerelateerd aan klantgegevens)
  • Sample_joins1 (Gerelateerd aan orderdetails die door werknemers zijn gedaan)

Stap 1) Aanmaak van tabel “sample_joins” met kolomnamen ID, naam, leeftijd, adres en salaris van de werknemers

Sluit u aan bij zoekopdrachten

Stap 2) Gegevens laden en weergeven

Sluit u aan bij zoekopdrachten

Uit de bovenstaande schermafdruk

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

Stap 3) Creatie van een sample_joins1 tabel en laden, weergave van gegevens

Sluit u aan bij zoekopdrachten

Uit de bovenstaande schermafbeelding kunnen we het volgende opmaken

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

In het vervolg zullen we de verschillende soorten joins bekijken die we kunnen uitvoeren op de tabellen die we hebben gemaakt. Maar voordat we dat doen, moet u rekening houden met de volgende punten voor joins.

Enkele aandachtspunten bij joins:

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

Verschillende soorten joins

Er zijn 4 typen joins, dit zijn

  • Innerlijke join
  • Linker buitenste verbinding
  • Rechts buitenste verbinding
  • Volledige Outer Join

Innerlijke verbinding:

De records die beide tabellen gemeen hebben, worden door deze Inner Join opgehaald.

Innerlijke verbinding

Uit de bovenstaande schermafbeelding kunnen we het volgende opmaken

  1. Hier voeren we een join-query uit met het JOIN-sleutelwoord tussen de tabellen sample_joins en sample_joins1 met overeenkomende voorwaarde als (c.Id= o.Id).
  2. De uitvoer toont gemeenschappelijke records die in beide tabellen aanwezig zijn door de voorwaarde te controleren die in de query wordt vermeld

Query:

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

Linker buitenste verbinding:

  • Hive-querytaal LEFT OUTER JOIN retourneert alle rijen uit de linkertabel, ook al zijn er geen overeenkomsten in de rechtertabel
  • Als de ON-clausule overeenkomt met nul records in de rechtertabel, retourneren de joins nog steeds een record in het resultaat met NULL in elke kolom uit de rechtertabel

Linker Outer Join

Uit de bovenstaande schermafbeelding kunnen we het volgende opmaken

  1. Hier voeren we een join-query uit met het trefwoord “LEFT OUTER JOIN” tussen de tabellen sample_joins en sample_joins1 met overeenkomende voorwaarde als (c.Id= o.Id).Bij voorbeeld hier gebruiken we de werknemers-ID als referentie, het controleert of de ID zowel rechts als links in de tabel gebruikelijk is of niet. Het fungeert als matchingvoorwaarde.
  2. De uitvoer toont gemeenschappelijke records die in beide tabellen aanwezig zijn door de voorwaarde te controleren die in de query wordt vermeld. NULL-waarden in de bovenstaande uitvoer zijn kolommen zonder waarden uit de rechtertabel, 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)

Rechter buitenste verbinding:

  • Hive-querytaal RIGHT OUTER JOIN retourneert alle rijen uit de rechtertabel, ook al zijn er geen overeenkomsten in de linkertabel
  • Als de ON-clausule overeenkomt met nul records in de linkertabel, retourneren de joins nog steeds een record in het resultaat met NULL in elke kolom uit de linkertabel
  • RIGHT-joins retourneren altijd records uit een rechtertabel en overeenkomende records uit de linkertabel. Als de linkertabel geen waarden bevat die overeenkomen met de kolom, retourneert deze op die plaats NULL-waarden.

Rechts buitenste verbinding

Uit de bovenstaande schermafbeelding kunnen we het volgende opmaken

  1. Hier voeren we een join-query uit met het trefwoord “RIGHT OUTER JOIN” tussen de tabellen sample_joins en sample_joins1 met overeenkomende voorwaarde als (c.Id= o.Id).
  2. De uitvoer toont gemeenschappelijke records die in beide tabellen aanwezig zijn door de voorwaarde te controleren die in de query wordt vermeld

Vraag:

  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 buitenvoeg:

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

Het retourneert alle records uit beide tabellen en vult NULL-waarden in voor de kolommen waarin aan beide zijden waarden ontbreken.

Volledige buitenste join

Uit de bovenstaande schermafbeelding kunnen we het volgende opmaken:

  1. Hier voeren we een join-query uit met het trefwoord "FULL OUTER JOIN" tussen de tabellen sample_joins en sample_joins1 met overeenkomende voorwaarde als (c.Id= o.Id).
  2. De uitvoer toont alle records die in beide tabellen aanwezig zijn door de voorwaarde te controleren die in de query wordt vermeld. Null-waarden in de uitvoer geven hier de ontbrekende waarden uit de kolommen van beide tabellen aan.

Vraag

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

GERELATEERDE ARTIKELEN

Subquery's

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

Subquery's kunnen in twee typen worden ingedeeld

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

Wanneer te gebruiken:

  • Om een ​​bepaalde waarde te combineren uit twee kolomwaarden uit verschillende tabellen
  • Afhankelijkheid van één tabelwaarden van andere tabellen
  • Vergelijkende controle van waarden uit één kolom uit 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 gekleurde is een subquery uitgevoerd op tabel t1. Hier zijn a en b kolommen die in een subquery worden toegevoegd en aan col1 worden toegewezen. Col1 is de kolomwaarde die aanwezig is in de hoofdtabel. Deze kolom “col1” in de subquery is equivalent aan de hoofdtabelquery in kolom col1.

Aangepaste scripts insluiten

Bijenkorf biedt de haalbaarheid van het schrijven van gebruikersspecifieke scripts voor de vereisten van de klant. De gebruikers kunnen hun eigen kaart schrijven en scripts voor de vereisten reduceren. Dit worden ingebedde aangepaste scripts genoemd. De coderingslogica is gedefinieerd in de aangepaste scripts en we kunnen dat script in de ETL-tijd gebruiken.

Wanneer kiest u voor ingebedde scripts:

  • In klantspecifieke vereisten moeten ontwikkelaars scripts in Hive schrijven en implementeren
  • Waar de ingebouwde functies van Hive niet zullen werken voor specifieke domeinvereisten

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

In deze Embedded aangepaste scripts moeten we de volgende punten in acht nemen

  • Kolommen worden omgezet in een tekenreeks en gescheiden door TAB voordat deze aan het gebruikersscript worden gegeven
  • Standaarduitvoer van het gebruikersscript wordt behandeld als door TAB gescheiden tekenreekskolommen

Voorbeeld van ingebed 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 waarnemen

Dit is slechts het voorbeeldscript om het te begrijpen

  • pv_users is de gebruikerstabel met velden als userid en date zoals vermeld in map_script
  • Reducer-script gedefinieerd op datum en aantal van de pv_users-tabellen

Je houdt misschien van: