Hive Deltagelse & SubQuery Selvstudium med eksempler

⚡ Smart opsummering

Hive-joins kombinerer rækker fra to eller flere tabeller i en matchende kolonne, og underforespørgsler indlejrer én forespørgsel i en anden, så begge demonstreres her på to eksempeltabeller, der er indlæst fra almindelige tekstfiler.

  • 🧱 To eksempeltabeller: sample_joins indeholder kundeoplysninger, og sample_joins1 indeholder ordreoplysninger, der er knyttet sammen i den delte id-kolonne.
  • 🔗 Fire sammenføjningstyper: Indre, venstre ydre, højre ydre og fulde ydre joinforbindelser beholder hver et forskelligt sæt af uoverensstemmende rækker.
  • ⬜ NULL markerer hullet: En outer join returnerer en række, selv uden match, og udfylder hver kolonne fra den manglende side med NULL.
  • 🔁 Orden er vigtig: Joins er ikke kommutative og er venstreassociative, så swapping tabellerne ændrer et outer join-resultat.
  • 🧮 Underforespørgsler indlejrer forespørgsler: En underforespørgsel skrives i FROM-klausulen eller WHERE-klausulen, og den ydre forespørgsel afhænger af den værdi, den returnerer.
  • 📜 TRANSFORM indlejrer scripts: Brugerdefinerede kort- og reduce-scripts kører gennem TRANSFORM-klausulen, når ingen indbygget funktion passer.

Eksempler på Hive-join og underforespørgsler

Deltag i forespørgsler

Join-forespørgsler kan udføres på to tabeller, der findes i HiveFor at forstå join-koncepter tydeligt opretter vi to tabeller her:

  • sample_joins (relateret til kundeoplysninger)
  • sample_joins1 (relateret til ordreoplysninger afgivet af medarbejdere)

Trin 1) Oprettelse af tabellen “sample_joins” med kolonnenavnene Id, Navn, Alder, adresse og løn for medarbejderne. Skærmbilledet nedenfor viser CREATE TABLE-sætningen og dens bekræftelse.

Hive CREATE TABLE-sætning for kundetabellen sample_joins

Trin 2) Indlæsning og visning af data. Det næste skærmbillede viser indlæsningskommandoen efterfulgt af indholdet af tabellen.

Indlæser Customers.txt i sample_joins og viser de indlæste rækker

Fra ovenstående skærmbillede:

  1. Indlæser data i sample_joins fra Customers.txt
  2. Viser sample_joins tabelindhold

Trin 3) Oprettelse af sample_joins1-tabellen, derefter indlæsning og visning af dens data, som vist på skærmbilledet nedenfor.

Oprettelse af sample_joins1, indlæsning af orders.txt og visning af ordrerækkerne

Fra ovenstående skærmbillede kan vi observere følgende:

  1. Oprettelse af tabellen sample_joins1 med kolonnerne Orderid, Date1, Id og Amount
  2. Indlæser data i sample_joins1 fra orders.txt
  3. Viser poster til stede i sample_joins1

Fremadrettet vil vi se på de forskellige typer joins, der kan udføres på de tabeller, vi har oprettet. Inden da skal du overveje følgende punkter om joins.

Nogle punkter at være opmærksom på ved joins:

  • Kun ligestillingsforbindelser er tilladt i forbindelser
  • Mere end to tabeller kan forbindes i samme forespørgsel
  • LEFT-, RIGHT- og FULL OUTER-joins findes for at give mere kontrol over ON-klausulen, som der ikke er noget match for.
  • Joins er ikke kommutative
  • Joins er venstre-associative, uanset om de er LEFT eller RIGHT joins

Lighedsbegrænsningen afspejler Hive, som det var i mange år. Fra Hive 2.2.0 og fremefter understøttes komplekse udtryk i ON-klausulen (HIVE-15211), så en ikke-lighedsbetingelse accepteres i en nuværende udgivelse. I ældre udgivelser skal betingelsen være en lighedstest, hvor alt andet flyttes til en WHERE-klausul.

Forskellige typer sammenføjninger

Der findes 4 typer joins. Disse er:

  • Indvendig sammenføjning
  • Venstre ydre samling
  • Højre ydre sammenføjning
  • Fuld ydre samling

Hver type demonstreres nedenfor mod de samme to tabeller, så det eneste, der ændrer sig mellem eksemplerne, er hvilke ikke-matchede rækker, der overlever.

Indvendig sammenføjning

De poster, der er fælles for begge tabeller, hentes af denne inner join. Resultatet i skærmbilledet nedenfor indeholder kun de kunder, der har en matchende ordre.

Hive Inner Join-output, der kun viser kunder, der har en matchende ordre

Fra ovenstående skærmbillede kan vi observere følgende:

  1. Her udfører vi en join-forespørgsel ved hjælp af JOIN-nøgleordet mellem tabellerne sample_joins og sample_joins1 med den matchende betingelse (c.Id = o.Id).
  2. Outputtet viser de fælles poster, der findes i begge tabeller, valgt ved at kontrollere den betingelse, der er nævnt i forespørgslen.

Forespørgsel:

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

Venstre ydre samling

  • HiveQL LEFT OUTER JOIN returnerer alle rækker fra venstre tabel, selvom der ikke er nogen match i højre tabel
  • Hvis ON-klausulen matcher nul poster i den højre tabel, returnerer join'en stadig en post i resultatet med NULL i hver kolonne fra den højre tabel.

Skærmbilledet nedenfor viser, at alle kunder vises, inklusive dem, der ikke har bestilt noget.

Hive left outer join output med NULL-værdier for kunder uden ordrer

Fra ovenstående skærmbillede kan vi observere følgende:

  1. Her udfører vi en join-forespørgsel ved hjælp af nøgleordet "LEFT OUTER JOIN" mellem tabellerne sample_joins og sample_joins1 med den matchende betingelse (c.Id = o.Id). For eksempel bruger vi her medarbejder-id'et som reference; det kontrollerer, om id'et er fælles for både den højre og den venstre tabel. Det fungerer som den matchende betingelse.
  2. Outputtet viser de poster, der er valgt af den betingelse, der er nævnt i forespørgslen. NULL-værdier i ovenstående output er kolonner uden værdier fra den højre tabel, det vil sige sample_joins1.

Forespørgsel:

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øjre ydre samling

  • HiveQL RIGHT OUTER JOIN returnerer alle rækker fra den højre tabel, selvom der ikke er nogen match i den venstre tabel
  • Hvis ON-klausulen matcher nul poster i den venstre tabel, returnerer join'en stadig en post i resultatet med NULL i hver kolonne fra den venstre tabel.
  • RIGHT-joins returnerer altid poster fra den højre tabel og matchende poster fra den venstre tabel. Hvis den venstre tabel ikke har nogen værdi, der svarer til kolonnen, returnerer den NULL-værdier på den plads.

Skærmbilledet nedenfor viser spejlbilledet af det forrige resultat: alle ordrer vises, uanset om de matcher eller ej.

Hive højre ydre join output keeping hver ordrerække fra sample_joins1

Fra ovenstående skærmbillede kan vi observere følgende:

  1. Her udfører vi en join-forespørgsel ved hjælp af nøgleordet "RIGHT OUTER JOIN" mellem tabellerne sample_joins og sample_joins1 med den matchende betingelse (c.Id = o.Id).
  2. Outputtet viser de poster, der er valgt ved at kontrollere den betingelse, der er nævnt i forespørgslen.

Forespørgsel:

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

Fuld ydre tilslutning

Den kombinerer poster fra både tabellerne sample_joins og sample_joins1 baseret på den JOIN-betingelse, der er angivet i forespørgslen.

Den returnerer alle poster fra begge tabeller og udfylder NULL-værdier for de kolonner, hvis matchende værdier mangler på begge sider, som skærmbilledet nedenfor viser.

Hive fuld outer join output kombinerer umatchede rækker fra begge tabeller

Fra ovenstående skærmbillede kan vi observere følgende:

  1. Her udfører vi en join-forespørgsel ved hjælp af nøgleordet "FULL OUTER JOIN" mellem tabellerne sample_joins og sample_joins1 med den matchende betingelse (c.Id = o.Id).
  2. Outputtet viser alle de poster, der findes i begge tabeller, valgt ved at kontrollere den betingelse, der er nævnt i forespørgslen. NULL-værdier i outputtet her angiver de manglende værdier fra kolonnerne i begge tabeller.

Forespørgsel:

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

Underforespørgsler

Joins placerer tabeller side om side. En underforespørgsel gør noget anderledes: den indlejrer én forespørgsel i en anden, så den ydre forespørgsel kan arbejde ud fra et resultat, der allerede er beregnet.

En forespørgsel, der er til stede i en forespørgsel, kaldes en underforespørgsel. Hovedforespørgslen afhænger af de værdier, der returneres af underforespørgslen.

Underforespørgsler kan opdeles i to typer:

  • Underforespørgsler i FROM-klausulen
  • Underforespørgsler i WHERE-klausulen

Hvornår skal du bruge:

  • For at få en bestemt værdi kombineret fra to kolonneværdier fra forskellige tabeller
  • Afhængighed af én tabels værdier af andre tabeller
  • Sammenlignende kontrol af værdierne i én kolonne mod andre tabeller

Syntaks:

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

Eksempel:

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

Her er t1 og t2 tabelnavne. Den indre sætning er den underforespørgsel, der udføres på tabel t1. Her er a og b kolonner, der tilføjes i underforespørgslen og tildeles til kolonne1. Kol1 er den kolonneværdi, der findes i hovedtabellen. Denne kolonne "kol1", der findes i underforespørgslen, svarer til hovedtabelforespørgslen i kolonne kol1.

Indlejring af brugerdefinerede scripts

Hvor en underforespørgsel omformer data alene med HiveQL, overfører et integreret script rækker til kode skrevet uden for Hive.

Hive gør det muligt at skrive brugerspecifikke scripts til klientkrav. Brugere kan skrive deres egne map- og reduce-scripts til disse krav. Disse kaldes indlejrede brugerdefinerede scripts. Kodningslogikken er defineret i det brugerdefinerede script, og vi kan bruge det script på ETL-tidspunktet.

Hvornår skal man vælge indlejrede scripts:

  • Hvor klientspecifikke krav betyder, at udviklere skal skrive og implementere scripts i Hive
  • Hvor Hive-indbyggede funktioner ikke fungerer til specifikke domænekrav

Til dette bruger Hive TRANSFORM-klausulen til at integrere både map- og reducer-scripts.

I disse indlejrede brugerdefinerede scripts skal vi være opmærksomme på følgende punkter:

  • Kolonner vil blive transformeret til strenge og afgrænset med TAB, før de gives til brugerscriptet.
  • Standardoutputtet fra brugerskriptet vil blive behandlet som TAB-separerede strengkolonner

Eksempel på indlejret 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;

Ud fra ovenstående script kan vi observere følgende. Dette er kun et eksempelscript til forståelse.

  • pv_users er tabellen "users", som indeholder felter som "userid" og "date", som nævnt i map_script.
  • Reducer-scriptet er defineret ud fra datoen og antallet i pv_users-tabellen.

Ofte Stillede Spørgsmål

Historisk set nej. Fra Hive 2.2.0 er komplekse udtryk tilladt i ON-klausulen (HIVE-15211), så uligheds- og intervalbetingelser fungerer. I tidligere versioner skal ON-klausulen være en lighedstest, og ethvert andet prædikat hører hjemme i WHERE.

En map-join indlæser den mindre tabel i hukommelsen og springer reduceringsfasen helt over. Hive vælger den automatisk, når hive.auto.convert.join er sand, og tabellen passer til den konfigurerede størrelsestærskel, hvilket gør small-to-large joins langt hurtigere.

Den returnerer rækker fra den venstre tabel, der har mindst ét ​​match til højre, uden at duplikere dem og uden at returnere kolonner i højre side. Den højre tabel må kun refereres til i ON-klausulen, ikke i SELECT eller WHERE.

Delvist. Fra Hive 0.13 accepterer operatorerne IN, NOT IN, EXISTS og NOT EXISTS underforespørgsler i WHERE-klausulen, inklusive korrelerede. Der er fortsat begrænsninger, så en ikke-understøttet korrelation omskrives normalt som en join.

Den indre forespørgsel bliver en afledt tabel, og hver tabel skal have et navn, før dens kolonner kan refereres til. Derfor slutter eksemplet med t2 efter den afsluttende parentes; udeladelse af aliaset forårsager en parse-fejl.

Når én join-nøgle indeholder en uforholdsmæssig stor andel af rækker, modtager en enkelt reducer det meste af arbejdet, mens andre er inaktive. At sætte hive.optimize.skewjoin, eller at opdele den tunge nøgle og samle resultaterne, spreder belastningen.

Maskinlæringsassistenter læser EXPLAIN-planen og markerer almindelige årsager, såsom et manglende partitionsfilter, en ukonverteret kortforbindelse eller en skæv nøgle. Betragt forslaget som et udgangspunkt, og bekræft det i forhold til planen og den faktiske kørselstid.

Den udarbejder standard join- og subquery-mønstre godt ud fra en kort kommentar. Bekræft alt, der er specifikt for programmet, fordi det nemt blandes ind. Spark SQL- eller Presto-syntaks, og Hive afviser konstruktioner såsom en ikke-aliaseret afledt tabel.

Opsummer dette indlæg med: