Výukový program Hive Join & SubQuery s příklady
David Carter
Připojte se k dotazům
Spojovací dotazy se mohou provádět na dvou tabulkách přítomných v Hive. Pro pochopení Připojte se Concepts jasně zde vytváříme dvě tabulky,
- Sample_joins (souvisí s podrobnostmi o zákaznících)
- Sample_joins1 (Souvisí s podrobnostmi objednávek provedených zaměstnanci)
Krok 1) Vytvoření tabulky „sample_joins“ s názvy sloupců ID, Jméno, Věk, adresa a plat zaměstnanců
Krok 2) Načítání a zobrazování dat
Z výše uvedeného snímku obrazovky
- Načítání dat do sample_joins z Customers.txt
- Zobrazení obsahu tabulky sample_joins
Krok 3) Vytvoření tabulky sample_joins1 a načtení, zobrazení dat
Z výše uvedeného snímku obrazovky můžeme pozorovat následující
- Vytvoření tabulky sample_joins1 se sloupci Orderid, Date1, Id, Amount
- Načítání dat do sample_joins1 z orders.txt
- Zobrazení záznamů přítomných v sample_joins1
V budoucnu uvidíme různé typy spojení, které lze provést na tabulkách, které jsme vytvořili, ale předtím musíte zvážit následující body pro spojení.
Některé body, které je třeba v Joins sledovat:
- Ve spojeních jsou povolena pouze spojení Equality
- V jednom dotazu lze spojit více než dvě tabulky
- LEVÉ, PRAVÉ, ÚPLNÉ VNĚJŠÍ spojení existují za účelem poskytnutí větší kontroly nad klauzulí ON, pro kterou neexistuje žádná shoda
- Spojení nejsou komutativní
- Spojení jsou levá asociativní bez ohledu na to, zda se jedná o LEVÉ nebo PRAVÉ spojení
Různé typy spojů
Spoje jsou 4 typů, tyto jsou
- Vnitřní spojení
- Levý vnější spoj
- Pravé vnější spojení
- Úplné vnější připojení
Vnitřní spojení:
Záznamy společné pro oba stoly budou načteny tímto vnitřním spojením.
Z výše uvedeného snímku obrazovky můžeme pozorovat následující
- Zde provádíme spojovací dotaz pomocí klíčového slova JOIN mezi tabulkami sample_joins a sample_joins1 s podmínkou shody jako (c.Id= o.Id).
- Výstup zobrazující společné záznamy přítomné v obou tabulkách kontrolou podmínky uvedené v dotazu
Dotaz:
SELECT c.Id, c.Name, c.Age, o.Amount FROM sample_joins c JOIN sample_joins1 o ON(c.Id=o.Id);
Levý vnější spoj:
- Dotazovací jazyk Hive LEFT OUTER JOIN vrátí všechny řádky z levé tabulky, i když v pravé tabulce nejsou žádné shody
- Pokud klauzule ON odpovídá nule záznamů v pravé tabulce, spojení stále vracejí záznam ve výsledku s NULL v každém sloupci z pravé tabulky.
Z výše uvedeného snímku obrazovky můžeme pozorovat následující
- Zde provádíme spojovací dotaz pomocí klíčového slova „LEFT OUTER JOIN“ mezi tabulkami sample_joins a sample_joins1 s odpovídající podmínkou jako (c.Id= o.Id).Například zde používáme id zaměstnance jako referenci, kontroluje, zda je id společné v pravé i levé tabulce nebo ne. Funguje jako odpovídající podmínka.
- Výstup zobrazující společné záznamy přítomné v obou tabulkách kontrolou podmínky uvedené v dotazu. Hodnoty NULL ve výše uvedeném výstupu jsou sloupce bez hodnot z pravé tabulky, která je sample_joins1
Dotaz:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c LEFT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Pravé vnější spojení:
- Dotazovací jazyk podregistru RIGHT OUTER JOIN vrátí všechny řádky z pravé tabulky, i když v levé tabulce nejsou žádné shody
- Pokud klauzule ON odpovídá nule záznamů v levé tabulce, spojení stále vracejí záznam ve výsledku s NULL v každém sloupci z levé tabulky.
- PRAVÁ spojení vždy vrátí záznamy z pravé tabulky a odpovídající záznamy z levé tabulky. Pokud levá tabulka neobsahuje žádné hodnoty odpovídající danému sloupci, vrátí na tomto místě hodnoty NULL.
Z výše uvedeného snímku obrazovky můžeme pozorovat následující
- Zde provádíme spojovací dotaz pomocí klíčového slova „RIGHT OUTER JOIN“ mezi tabulkami sample_joins a sample_joins1 s odpovídající podmínkou jako (c.Id= o.Id).
- Výstup zobrazující společné záznamy přítomné v obou tabulkách kontrolou podmínky uvedené v dotazu
Dotaz:
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c RIGHT OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Úplné vnější spojení:
Kombinuje záznamy obou tabulek sample_joins a sample_joins1 na základě podmínky JOIN uvedené v dotazu.
Vrátí všechny záznamy z obou tabulek a doplní hodnoty NULL pro sloupce, kterým chybí hodnoty odpovídající na obou stranách.
Z výše uvedeného snímku obrazovky můžeme pozorovat následující:
- Zde provádíme spojovací dotaz pomocí klíčového slova „FULL OUTER JOIN“ mezi tabulkami sample_joins a sample_joins1 s podmínkou shody jako (c.Id= o.Id).
- Výstup zobrazující všechny záznamy přítomné v obou tabulkách kontrolou podmínky uvedené v dotazu. Hodnoty Null ve výstupu zde označují chybějící hodnoty ze sloupců obou tabulek.
Dotaz
SELECT c.Id, c.Name, o.Amount, o.Date1 FROM sample_joins c FULL OUTER JOIN sample_joins1 o ON(c.Id=o.Id)
Dílčí dotazy
Dotaz přítomný v dotazu se nazývá dílčí dotaz. Hlavní dotaz bude záviset na hodnotách vrácených dílčími dotazy.
Poddotazy lze rozdělit do dvou typů
- Poddotazy v klauzuli FROM
- Poddotazy v klauzuli WHERE
Kdy použít:
- Chcete-li získat konkrétní hodnotu kombinovanou ze dvou hodnot sloupců z různých tabulek
- Závislost hodnot jedné tabulky na jiných tabulkách
- Srovnávací kontrola hodnot jednoho sloupce z jiných tabulek
Syntaxe:
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);
Příklad:
SELECT col1 FROM (SELECT a+b AS col1 FROM t1) t2
Zde t1 a t2 jsou názvy tabulek. Barevný je Poddotaz provedený v tabulce t1. Zde aab jsou sloupce, které jsou přidány do poddotazu a přiřazeny col1. Sloupec1 je hodnota sloupce v hlavní tabulce. Tento sloupec „col1“ přítomný v poddotazu je ekvivalentní dotazu hlavní tabulky ve sloupci col1.
Vkládání vlastních skriptů
Úl poskytuje proveditelnost psaní uživatelských specifických skriptů pro požadavky klienta. Uživatelé mohou psát vlastní mapu a redukovat skripty podle požadavků. Tyto skripty se nazývají vložené vlastní skripty. Logika kódování je definována ve vlastních skriptech a můžeme tento skript použít v čase ETL.
Kdy zvolit vložené skripty:
- V požadavcích specifických pro klienta musí vývojáři psát a nasazovat skripty v Hive
- Kde vestavěné funkce Hive nebudou fungovat pro specifické požadavky domény
K tomu v Hive používá klauzuli TRANSFORM k vložení jak mapových, tak redukčních skriptů.
V těchto vložených vlastních skriptech musíme dodržovat následující body
- Sloupce budou transformovány na řetězec a odděleny klávesou TAB, než je předají uživatelskému skriptu
- Standardní výstup uživatelského skriptu bude zpracován jako řetězcové sloupce oddělené TAB
Ukázkový vložený skript,
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;
Z výše uvedeného skriptu můžeme vypozorovat následující
Toto je pouze ukázkový skript pro pochopení
- pv_users je tabulka uživatelů, která má pole jako userid a date, jak je uvedeno v map_script
- Redukční skript definovaný na datum a počet tabulek pv_users
