Výukový program Hive Join & SubQuery s příklady

Připojte se k dotazům

Dotazy spojení lze provádět na dvou tabulkách přítomných v ÚlAbychom jasně pochopili koncepty spojení, vytvoříme zde dvě tabulky:

• sample_joins (souvisí s údaji o zákazníkovi)
• sample_joins1 (souvisí s podrobnostmi o objednávkách zadaných zaměstnanci)

Krok 1) Vytvoření tabulky „sample_joins“ s názvy sloupců Id, Name, Age, address a salary zaměstnanců. Snímek obrazovky níže ukazuje příkaz CREATE TABLE a jeho potvrzení.

Krok 2) Načítání a zobrazení dat. Následující snímek obrazovky ukazuje příkaz load následovaný obsahem tabulky.

Z výše uvedeného snímku obrazovky:

1. Načítání dat do sample_joins z Customers.txt
2. Zobrazení obsahu tabulky sample_joins

Krok 3) Vytvoření tabulky sample_joins1, následné načtení a zobrazení jejích dat, jak je znázorněno na snímku obrazovky níže.

Z výše uvedeného snímku obrazovky můžeme pozorovat následující:

1. Vytvoření tabulky sample_joins1 se sloupci Orderid, Date1, Id a Amount
2. Načítání dat do sample_joins1 z orders.txt
3. Zobrazení záznamů přítomných v sample_joins1

Dále si ukážeme různé typy spojení, které lze provést s tabulkami, které jsme vytvořili. Předtím je třeba zvážit následující body týkající se spojení.

Některé body, které je třeba při spojení dodržovat:

• Ve spojeních jsou povoleny pouze spojení založené na rovnosti.
• V jednom dotazu lze spojit více než dvě tabulky
• Spojení LEFT, RIGHT a FULL OUTER existují proto, aby poskytla větší kontrolu nad klauzulí ON, pro kterou neexistuje shoda.
• Spojení nejsou komutativní
• Spojení jsou levá asociativní bez ohledu na to, zda se jedná o LEVÉ nebo PRAVÉ spojení

Omezení rovnosti odráží Hive tak, jak byl po mnoho let. Od verze Hive 2.2.0 jsou v klauzuli ON podporovány komplexní výrazy (HIVE-15211), takže podmínka nerovnosti je v aktuální verzi akceptována. Ve starších verzích musí být podmínkou test rovnosti a vše ostatní musí být přesunuto do klauzule WHERE.

Různé typy spojů

Spojení jsou 4 typů. Jsou to:

• Vnitřní spojení
• Levý vnější spoj
• Pravý vnější spoj
• Plné vnější spojení

Každý typ je níže demonstrován na stejných dvou tabulkách, takže jediné, co se mezi příklady mění, je to, které neshodné řádky přežijí.

Vnitřní spojení

Toto vnitřní spojení načte záznamy společné pro obě tabulky. Výsledek na snímku obrazovky níže obsahuje pouze zákazníky, kteří mají odpovídající objednávku.

Z výše uvedeného snímku obrazovky můžeme pozorovat následující:

1. Zde provádíme dotaz spojení pomocí klíčového slova JOIN mezi tabulkami sample_joins a sample_joins1 s podmínkou shody (c.Id = o.Id).
2. Výstup zobrazí společné záznamy přítomné v obou tabulkách, vybrané na základě 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

• HiveQL LEFT OUTER JOIN vrátí všechny řádky z levé tabulky, i když v pravé tabulce nejsou žádné shody.
• Pokud klauzule ON nenajde žádné záznamy v pravé tabulce, spojení stále vrátí ve výsledku záznam s hodnotou NULL v každém sloupci z pravé tabulky.

Níže uvedený snímek obrazovky ukazuje, že se zobrazí všichni zákazníci, včetně těch bez objednávky.

Z výše uvedeného snímku obrazovky můžeme pozorovat následující:

1. Zde provádíme dotaz na spojení pomocí klíčového slova „LEFT OUTER JOIN“ mezi tabulkami sample_joins a sample_joins1 s podmínkou shody (c.Id = o.Id). Například zde používáme ID zaměstnance jako referenci; kontroluje se, zda je ID společné pro pravou i levou tabulku. Funguje to jako podmínka shody.
2. Výstup zobrazuje záznamy vybrané podmínkou uvedenou v dotazu. Hodnoty NULL ve výše uvedeném výstupu představují sloupce bez hodnot z pravé tabulky, tedy 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í

• HiveQL RIGHT OUTER JOIN vrací všechny řádky z pravé tabulky, i když v levé tabulce nejsou žádné shody.
• Pokud klauzule ON nenajde žádné záznamy v levé tabulce, spojení stále vrátí ve výsledku záznam s hodnotou NULL v každém sloupci z levé tabulky.
• Spoje RIGHT join vždy vracejí záznamy z pravé tabulky a odpovídající záznamy z levé tabulky. Pokud levá tabulka neobsahuje žádnou hodnotu odpovídající sloupci, vrátí na tomto místě hodnoty NULL.

Níže uvedený snímek obrazovky ukazuje zrcadlový obraz předchozího výsledku: zobrazí se každá objednávka, ať už spárovaná, nebo ne.

Z výše uvedeného snímku obrazovky můžeme pozorovat následující:

1. Zde provádíme dotaz spojení pomocí klíčového slova „RIGHT OUTER JOIN“ mezi tabulkami sample_joins a sample_joins1 s podmínkou shody (c.Id = o.Id).
2. Výstup zobrazí záznamy vybrané 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ší připojení

Kombinuje záznamy z tabulek sample_joins a sample_joins1 na základě podmínky JOIN zadané v dotazu.

Vrátí všechny záznamy z obou tabulek a doplní hodnoty NULL do sloupců, jejichž odpovídající hodnoty na obou stranách chybí, jak ukazuje snímek obrazovky níže.

Z výše uvedeného snímku obrazovky můžeme pozorovat následující:

1. Zde provádíme dotaz spojení pomocí klíčového slova „FULL OUTER JOIN“ mezi tabulkami sample_joins a sample_joins1 s podmínkou shody (c.Id = o.Id).
2. Výstup zobrazí všechny záznamy přítomné v obou tabulkách, vybrané na základě podmínky uvedené v dotazu. Hodnoty NULL ve výstupu zde označují chybějící hodnoty ve sloupcích 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

Spoje umisťují tabulky vedle sebe. Poddotaz dělá něco jiného: vnořuje jeden dotaz do druhého, aby vnější dotaz mohl pracovat s výsledkem, který již byl vypočítán.

Dotaz obsažený v dotazu se nazývá poddotaz. Hlavní dotaz bude záviset na hodnotách vrácených poddotazem.

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 hodnotách jiných tabulek
• Porovnání hodnot jednoho sloupce s hodnotami v jiných tabulkách

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. Vnitřní příkaz je poddotaz provedený na tabulce t1. Zde a a b jsou sloupce, které jsou přidány do poddotazu a přiřazeny sloupci col1. Col1 je hodnota sloupce přítomná v hlavní tabulce. Tento sloupec „col1“ přítomný v poddotazu je ekvivalentní dotazu na hlavní tabulku ve sloupci col1.

Vkládání vlastních skriptů

Tam, kde poddotaz přetváří data pouze pomocí HiveQL, vložený skript předává řádky kódu napsanému mimo Hive.

Hive umožňuje psát uživatelsky specifické skripty pro požadavky klientů. Uživatelé si mohou pro tyto požadavky napsat vlastní mapovací a redukční skripty. Tyto skripty se nazývají vložené vlastní skripty. Logika kódování je definována ve vlastním skriptu a tento skript můžeme použít v době ETL.

Kdy zvolit vložené skripty:

• Kde specifické požadavky klienta znamenají, že vývojáři musí psát a nasazovat skripty v Hive
• Kde vestavěné funkce Hive nebudou fungovat pro specifické požadavky domény

Pro tento účel Hive používá klauzuli TRANSFORM k vložení skriptů map i reducer.

V těchto vložených vlastních skriptech musíme dodržovat následující body:

• Sloupce budou před předáním uživatelskému skriptu transformovány na řetězec a odděleny klávesou TAB.
• Standardní výstup uživatelského skriptu bude považován za sloupce řetězců oddělené tabulací (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 pozorovat následující. Toto je pouze ukázkový skript pro pochopení.

• pv_users je tabulka uživatelů, která obsahuje pole jako userid a date, jak je uvedeno v map_script.
• Redukční skript je definován na základě data a počtu v tabulce pv_users.

Nejčastější dotazy