Hive Join & SubQuery Tutorial med eksempler

Indholdsfortegnelse:

Anonim

I denne vejledning lærer du-

  • Deltag i forespørgsler
  • Forskellig type sammenføjninger
  • Underforespørgsler
  • Integrering af brugerdefinerede scripts
  • UDF'er (brugerdefinerede funktioner)

Deltag i forespørgsler:

Deltagelsesforespørgsler kan udføres på to tabeller, der findes i Hive. For at forstå Join Concepts klart her opretter vi to tabeller overalt,

  • Sample_joins (relateret til kundedetaljer)
  • Sample_joins1 (Relateret til ordredetaljer udført af ansatte)

Trin 1) Oprettelse af tabel "sample_joins" med kolonnenavne ID, navn, alder, adresse og løn for de ansatte

Trin 2) Indlæsning og visning af data

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-tabel og indlæsning, visning af data

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

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

Fremadrettet vil vi se forskellige typer sammenføjninger, der kan udføres på tabeller, vi har oprettet, men før det skal du overveje at følge følgende punkter for sammenføjninger.

Nogle punkter at observere i sammenføjninger:

  • Kun ligestillingsforbindelser er tilladt i tilslutninger
  • Mere end to tabeller kan sammenføjes i samme forespørgsel
  • VENSTRE, HØJRE, FULD OUTER-sammenføjninger findes for at give mere kontrol over ON-klausul, som der ikke er nogen match med
  • Forbindelser er ikke kommutative
  • Forbindelser er venstreassociative, uanset om de er VENSTRE eller HØJRE sammenføjninger

Forskellig type sammenføjninger

Samlinger er af 4 typer, disse er

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

Indvendig samling:

Optegnelserne, der er fælles for begge tabeller, hentes af denne indre deltagelse.

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

  1. Her udfører vi joinforespørgsel ved hjælp af JOIN-nøgleord mellem tabellerne sample_joins og sample_joins1 med matchende betingelse som (c.Id = o.Id).
  2. Outputtet, der viser almindelige poster, der findes i begge tabeller, ved at kontrollere den tilstand, 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 sammenføjning:

  • Hive-forespørgselssprog LEFT OUTER JOIN returnerer alle rækkerne fra venstre tabel, selvom der ikke er nogen matches i højre tabel
  • Hvis ON-klausul matcher nul poster i højre tabel, returnerer sammenføjningerne stadig en post i resultatet med NULL i hver kolonne fra højre tabel

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

  1. Her udfører vi joinforespørgsel ved hjælp af "LEFT OUTER JOIN" nøgleord mellem tabellerne sample_joins og sample_joins1 med matchende betingelse som (c.Id = o.Id).

    For eksempel bruger vi her medarbejder-id som reference, det kontrollerer, om id er almindeligt i højre såvel som venstre i tabellen eller ej. Det fungerer som matchende tilstand.

  2. Outputtet, der viser almindelige poster, der findes i begge tabeller, ved at kontrollere den tilstand, der er nævnt i forespørgslen.

    NULL-værdier i ovenstående output er kolonner uden værdier fra højre tabel, der er 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 sammenføjning:

  • Hive-forespørgselssprog RIGHT OUTER JOIN returnerer alle rækkerne fra højre tabel, selvom der ikke er nogen matches i venstre tabel
  • Hvis ON-klausul matcher nul poster i venstre tabel, returnerer sammenføjningerne stadig en post i resultatet med NULL i hver kolonne fra venstre tabel
  • RIGHT sammenføjninger returnerer altid poster fra en højre tabel og matchede poster fra den venstre tabel. Hvis den venstre tabel ikke har nogen værdier, der svarer til kolonnen, returnerer den NULL-værdier det sted.

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

  1. Her udfører vi joinforespørgsel ved hjælp af nøgleordet "RIGHT OUTER JOIN" mellem tabellerne sample_joins og sample_joins1 med matchende tilstand som (c.Id = o.Id).
  2. Outputtet, der viser almindelige poster, der findes i begge tabeller, ved at kontrollere den tilstand, 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 sammenføjning:

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

Det returnerer alle poster fra begge tabeller og udfylder NULL-værdier for de kolonner, der mangler værdier, der er matchet på begge sider.

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

  1. Her udfører vi joinforespørgsel ved hjælp af "FULL OUTER JOIN" nøgleord mellem tabellerne sample_joins og sample_joins1 med matchende tilstand som (c.Id = o.Id).
  2. Outputtet, der viser alle de poster, der er til stede i begge tabeller, ved at kontrollere tilstanden nævnt i forespørgslen. Nulværdier i output 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:

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

Underforespørgsler kan klassificeres i to typer

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

Hvornår skal man bruge:

  • For at få en bestemt værdi kombineret fra to kolonneværdier fra forskellige tabeller
  • Afhængighed af en tabelværdier på andre tabeller
  • Sammenligningskontrol af en kolonneværdier fra andre tabeller

Syntaks:

Subquery in FROM clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE col1 IN (SubQuery);

Eksempel:

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

Her er t1 og t2 tabelnavne. Den farvede er underforespørgsel udført på tabel t1. Her er a og b kolonner, der tilføjes i en underforespørgsel og tildeles col1. Col1 er den kolonneværdi, der findes i hovedtabellen. Denne kolonne "col1" til stede i underforespørgslen svarer til hovedtabelforespørgslen i kolonne col1.

Integrering af brugerdefinerede scripts:

Hive giver mulighed for at skrive brugerspecifikke scripts til kundens krav. Brugerne kan skrive deres eget kort og reducere scripts til kravene. Disse kaldes Embedded Custom scripts. Kodningslogikken er defineret i de brugerdefinerede scripts, og vi kan bruge dette script i ETL-tiden.

Hvornår skal man vælge Embedded Scripts:

  • I klientspecifikke krav skal udviklere skrive og implementere scripts i Hive
  • Hvor Hive-indbyggede funktioner ikke fungerer efter specifikke domænekrav

Til dette i Hive bruger det TRANSFORM-klausul til at indlejre både kort- og reduceringsskripter.

I denne integrerede brugerdefinerede scripts skal vi overholde følgende punkter

  • Kolonner omdannes til streng og afgrænses af TAB, inden de gives til bruger scriptet
  • Standardoutput fra brugerskriptet behandles som TAB-adskilte strengkolonner

Eksempel på indlejret script,

FROM (FROM pv_usersMAP pv_users.userid, pv_users.dateUSING 'map_script'AS dt, uidCLUSTER BY dt) map_outputINSERT OVERWRITE TABLE pv_users_reducedREDUCE map_output.dt, map_output.uidUSING 'reduce_script'AS date, count;

Fra ovenstående script kan vi observere følgende

Dette er kun eksemplets script til forståelse

  • pv_users er brugertabellen, som har felter som bruger-id og dato som nævnt i map_script
  • Reducer-script defineret på dato og antal af pv_users-tabellerne