Hive Join & SubQuery Tutorial med exempel

Innehållsförteckning:

Anonim

I den här handledningen lär du dig-

  • Gå med i frågor
  • Olika typer av kopplingar
  • Underfrågor
  • Bädda in anpassade skript
  • UDF: er (användardefinierade funktioner)

Gå med i frågorna:

Anslutningsfrågor kan utföras på två tabeller i Hive. För att förstå Join Concepts tydligt här skapar vi två tabeller överallt,

  • Sample_joins (relaterad till kundinformation)
  • Sample_joins1 (relaterad till orderinformation gjord av anställda)

Steg 1) Skapande av tabell "sample_joins" med kolumnnamn ID, namn, ålder, adress och lön för de anställda

Steg 2) Ladda och visa data

Från ovanstående skärmdump

  1. Läser in data i sample_joins från Customers.txt
  2. Visar sample_joins tabellinnehåll

Steg 3) Skapande av sample_joins1-tabell och laddning, visar data

Från ovanstående skärmdump kan vi observera följande

  1. Skapande av tabell sample_joins1 med kolumner Orderid, Date1, Id, Amount
  2. Läser in data i sample_joins1 från orders.txt
  3. Visar poster som finns i sample_joins1

Framåt ser vi olika typer av sammanfogningar som kan utföras på tabeller som vi har skapat men innan det måste du överväga att följa poäng för anslutningar.

Några punkter att observera i sammanfogar:

  • Endast jämställdhetsanslutningar är tillåtna i anslutningar
  • Mer än två tabeller kan sammanfogas i samma fråga
  • VÄNSTER, HÖGER, FULL OUTER-anslutningar finns för att ge mer kontroll över ON-klausul som det inte finns någon matchning för
  • Anslutningar är inte kommutativa
  • Anslutningar är vänsterassociativa oavsett om de är VÄNSTER eller HÖGER

Olika typer av kopplingar

Anslutningar är av fyra typer, dessa är

  • Inre koppling
  • Vänster yttre anslutning
  • Höger yttre anslutning
  • Fullständig yttre anslutning

Inre koppling:

De register som är gemensamma för båda tabellerna kommer att hämtas av denna Inner Join.

Från ovanstående skärmdump kan vi observera följande

  1. Här utför vi anslutningsfrågan med hjälp av JOIN-nyckelordet mellan tabellerna sample_joins och sample_joins1 med matchande tillstånd som (c.Id = o.Id).
  2. Utgången som visar vanliga poster som finns i båda tabellerna genom att kontrollera tillståndet som nämns 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:

  • Hive frågespråk VÄNSTER YTTRE JOIN returnerar alla rader från vänster tabell även om det inte finns några matchningar i höger tabell
  • Om ON-klausul matchar noll poster i höger tabell returnerar sammanfogningarna fortfarande en post i resultatet med NULL i varje kolumn från höger tabell

Från ovanstående skärmdump kan vi observera följande

  1. Här utför vi kopplingsfrågan med hjälp av "LEFT OUTER JOIN" nyckelord mellan tabellerna sample_joins och sample_joins1 med matchande tillstånd som (c.Id = o.Id).

    Till exempel här använder vi medarbetar-id som referens, det kontrollerar om id är vanligt i både höger och vänster tabell eller inte. Det fungerar som matchande tillstånd.

  2. Utgången som visar vanliga poster som finns i båda tabellerna genom att kontrollera tillståndet som nämns i frågan.

    NULL-värden i ovanstående utgång är kolumner utan värden från höger tabell som är 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:

  • Hive frågespråk RIGHT OUTER JOIN returnerar alla rader från höger tabell även om det inte finns några matchningar i vänster tabell
  • Om ON-klausul matchar nollposter i vänster tabell returnerar sammanfogningarna fortfarande en post i resultatet med NULL i varje kolumn från vänster tabell
  • RIGHT joins returnerar alltid poster från en höger tabell och matchade poster från den vänstra tabellen. Om den vänstra tabellen inte har några värden som motsvarar kolumnen returnerar den NULL-värdena på den platsen.

Från ovanstående skärmdump kan vi observera följande

  1. Här utför vi sökfrågan med hjälp av "RIGHT OUTER JOIN" nyckelord mellan tabellerna sample_joins och sample_joins1 med matchande tillstånd som (c.Id = o.Id).
  2. Utgången som visar vanliga poster som finns i båda tabellerna genom att kontrollera tillståndet som nämns 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 koppling:

Den kombinerar poster för både tabellerna sample_joins och sample_joins1 baserat på JOIN-tillståndet som ges i frågan.

Den returnerar alla poster från båda tabellerna och fyller i NULL-värden för de kolumner som saknar värden matchade på vardera sidan.

Från ovanstående skärmdump kan vi observera följande:

  1. Här utför vi kopplingsfrågan med hjälp av "FULL OUTER JOIN" nyckelord mellan tabellerna sample_joins och sample_joins1 med matchande tillstånd som (c.Id = o.Id).
  2. Utdata som visar alla poster som finns i båda tabellerna genom att kontrollera tillståndet som nämns i frågan. Nollvärden i utdata här anger de saknade värdena från 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:

En fråga som finns i en fråga kallas en underfråga. Huvudfrågan beror på de värden som delfrågorna returnerar.

Underfrågor kan delas in i två typer

  • Underfrågor i FROM-klausul
  • Underfrågor i WHERE-klausulen

När ska man använda:

  • För att få ett visst värde kombinerat från två kolumnvärden från olika tabeller
  • Beroende på en tabellvärden på andra tabeller
  • Jämförande kontroll av en kolumnvärden från andra tabeller

Syntax:

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

Exempel:

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

Här är t1 och t2 tabellnamn. Den färgade är Subquery utförd på tabell t1. Här är a och b kolumner som läggs till i en underfråga och tilldelas kol1. Kol1 är kolumnvärdet i huvudtabellen. Den här kolumnen "col1" i underfrågan motsvarar huvudtabelfrågan i kolumn col1.

Bädda in anpassade skript:

Hive ger möjligheten att skriva användarspecifika skript för klientkraven. Användarna kan skriva sin egen karta och minska skript för att uppfylla kraven. Dessa kallas Embedded Custom scripts. Kodningslogiken definieras i anpassade skript och vi kan använda det skriptet under ETL-tiden.

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

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

För detta i Hive använder den TRANSFORM-klausulen för att bädda in både kart- och reduceringsskript.

I detta inbyggda anpassade skript måste vi följa följande punkter

  • Kolumner kommer att omvandlas till sträng och avgränsas av TAB innan de ges till användarskriptet
  • Standardutdata från användarskriptet behandlas som TAB-separerade strängkolumner

Exempel på inbäddat skript,

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;

Från ovanstående skript kan vi observera följande

Detta är endast exempelskriptet för att förstå

  • pv_users är användartabellen som har fält som användar-ID och datum som nämns i map_script
  • Reduceringsskript definierat på datum och antal av pv_users-tabellerna