Hive Join & SubQuery Урок с примери

Присъединете се към заявки

Заявките за свързване могат да се изпълняват върху две таблици, присъстващи в КошерЗа да разберем ясно концепциите за съединение, създаваме две таблици тук:

• sample_joins (свързани с данни за клиента)
• sample_joins1 (свързано с подробности за поръчките, направени от служители)

Стъпка 1) Създаване на таблица „sample_joins“ с имената на колоните Id, Name, Age, address и salary на служителите. Снимката на екрана по-долу показва командата CREATE TABLE и нейното потвърждение.

Стъпка 2) Зареждане и показване на данни. Следващата екранна снимка показва командата за зареждане, последвана от съдържанието на таблицата.

От горната екранна снимка:

1. Зареждане на данни в sample_joins от Customers.txt
2. Показване на съдържанието на таблицата sample_joins

Стъпка 3) Създаване на таблицата sample_joins1, след което зареждане и показване на данните от нея, както е показано на екранната снимка по-долу.

От горната екранна снимка можем да наблюдаваме следното:

1. Създаване на таблица sample_joins1 с колоните Orderid, Date1, Id и Amount
2. Зареждане на данни в sample_joins1 от orders.txt
3. Показване на записи, налични в sample_joins1

В бъдеще ще видим различните видове съединения, които могат да се извършват върху таблиците, които сме създали. Преди това трябва да вземете предвид следните точки относно съединенията.

Някои точки, които трябва да се имат предвид при съединенията:

• В съединенията са разрешени само съединения за равенство
• Повече от две таблици могат да бъдат обединени в една и съща заявка
• Съществуват LEFT, RIGHT и FULL OUTER съединения, за да осигурят по-голям контрол върху клаузата ON, за която няма съвпадение.
• Съединенията не са комутативни
• Съединенията са ляво-асоциативни, независимо дали са ЛЯВИ или ДЕСНИ съединения

Ограничението за равенство отразява Hive такъв, какъвто е бил в продължение на много години. От Hive 2.2.0 нататък, сложните изрази в клаузата ON се поддържат (HIVE-15211), така че условие за неравенство се приема в текущата версия. В по-старите версии условието трябва да бъде тест за равенство, като всичко останало се премества в клауза WHERE.

Различни видове присъединявания

Съединенията са от 4 вида. Те са:

• Вътрешно присъединяване
• Ляво външно съединение
• Десен външен съединител
• Пълно външно съединяване

Всеки тип е демонстриран по-долу спрямо едни и същи две таблици, така че единственото нещо, което се различава между примерите, е кои несъответстващи редове оцеляват.

Вътрешно присъединяване

Общите записи за двете таблици ще бъдат извлечени чрез това вътрешно съединение. Резултатът на екранната снимка по-долу съдържа само клиентите, които имат съответстваща поръчка.

От горната екранна снимка можем да наблюдаваме следното:

1. Тук изпълняваме заявка за съединение, използвайки ключовата дума JOIN между таблиците sample_joins и sample_joins1, с условието за съвпадение (c.Id = o.Id).
2. Резултатът показва общите записи, присъстващи в двете таблици, избрани чрез проверка на условието, посочено в заявката.

Запитване:

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

Ляво външно съединение

• HiveQL LEFT OUTER JOIN връща всички редове от лявата таблица, въпреки че няма съвпадения в дясната таблица
• Ако клаузата ON не съвпада с нула записи в дясната таблица, join-ът все пак връща запис в резултата с NULL във всяка колона от дясната таблица.

Екранната снимка по-долу показва, че се появяват всички клиенти, включително тези без поръчка.

От горната екранна снимка можем да наблюдаваме следното:

1. Тук изпълняваме заявка за съединение, използвайки ключовата дума „LEFT OUTER JOIN“ между таблиците sample_joins и sample_joins1, с условие за съвпадение (c.Id = o.Id). Например, тук използваме идентификатора на служителя като отправна точка; проверява се дали идентификаторът е общ за дясната таблица, както и за лявата таблица. Той действа като условие за съвпадение.
2. Резултатът показва записите, избрани от условието, посочено в заявката. NULL стойностите в горния резултат са колони без стойности от дясната таблица, т.е. sample_joins1.

Запитване:

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

Дясно външно присъединяване

• HiveQL RIGHT OUTER JOIN връща всички редове от дясната таблица, въпреки че няма съвпадения в лявата таблица
• Ако клаузата ON не съвпада с нула записи в лявата таблица, join-ът все пак връща запис в резултата с NULL във всяка колона от лявата таблица.
• RIGHT join-овете винаги връщат записи от дясната таблица и съответстващи записи от лявата таблица. Ако лявата таблица няма стойност, съответстваща на колоната, тя ще върне NULL стойности на това място.

Снимката на екрана по-долу показва огледален образ на предишния резултат: появява се всяка поръчка, независимо дали е съвпадаща или не.

От горната екранна снимка можем да наблюдаваме следното:

1. Тук изпълняваме заявка за съединение, използвайки ключовата дума „RIGHT OUTER JOIN“ между таблиците sample_joins и sample_joins1, с условието за съвпадение (c.Id = o.Id).
2. Резултатът показва записите, избрани чрез проверка на условието, посочено в заявката.

Запитване:

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

Пълно външно присъединяване

Той комбинира записите от таблиците sample_joins и sample_joins1 въз основа на условието JOIN, дадено в заявката.

Връща всички записи от двете таблици и попълва NULL стойности за колоните, чиито съответстващи стойности липсват от двете страни, както е показано на екранната снимка по-долу.

От горната екранна снимка можем да наблюдаваме следното:

1. Тук изпълняваме заявка за съединение, използвайки ключовата дума „FULL OUTER JOIN“ между таблиците sample_joins и sample_joins1, с условието за съвпадение (c.Id = o.Id).
2. Резултатът показва всички записи, налични в двете таблици, избрани чрез проверка на условието, посочено в заявката. NULL стойностите в резултата тук показват липсващите стойности от колоните на двете таблици.

Запитване:

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

Подзапитвания

Съединенията поставят таблиците една до друга. Подзаявката прави нещо различно: тя влага една заявка в друга, така че външната заявка да може да работи с резултат, който вече е изчислен.

Заявка, присъстваща в заявка, се нарича подзаявка. Главната заявка ще зависи от стойностите, върнати от подзаявката.

Подзаявките могат да бъдат класифицирани в два вида:

• Подзаявки в клаузата FROM
• Подзаявки в клаузата WHERE

Кога да използвате:

• За да получите конкретна стойност, комбинирана от две стойности на колони от различни таблици
• Зависимост на стойностите на една таблица от други таблици
• Сравнителна проверка на стойностите на една колона спрямо други таблици

Синтаксис:

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

Пример:

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

Тук t1 и t2 са имената на таблиците. Вътрешният оператор е подзаявката, изпълнена върху таблица t1. Тук a и b са колони, които се добавят в подзаявката и се присвояват на колона col1. Col1 е стойността на колоната, присъстваща в главната таблица. Тази колона „col1“, присъстваща в подзаявката, е еквивалентна на заявката към главната таблица в колона col1.

Вграждане на персонализирани скриптове

Докато подзаявка преоформя данните само с HiveQL, вграден скрипт предава редовете на код, написан извън Hive.

Hive позволява писането на специфични за потребителя скриптове за клиентски изисквания. Потребителите могат да пишат свои собствени map и reduce скриптове за тези изисквания. Те се наричат ​​вградени персонализирани скриптове. Логиката на кодиране е дефинирана в персонализирания скрипт и ние можем да използваме този скрипт по време на ETL.

Кога да изберете вградени скриптове:

• Където специфичните за клиента изисквания означават, че разработчиците трябва да пишат и внедряват скриптове в Hive
• Къде вградените функции на Hive няма да работят за специфични изисквания на домейна

За тази цел Hive използва клаузата TRANSFORM, за да вгради както map, така и reducer скриптове.

В тези вградени персонализирани скриптове трябва да спазваме следните точки:

• Колоните ще бъдат трансформирани в низ и разделени с TAB, преди да бъдат предоставени на потребителския скрипт.
• Стандартният изход на потребителския скрипт ще се третира като колони с низове, разделени с TAB.

Примерен вграден скрипт:

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;

От горния скрипт можем да наблюдаваме следното. Това е само примерен скрипт за разбиране.

• pv_users е таблицата с потребители, която съдържа полета като потребителско име и дата, както е посочено в map_script
• Редукторният скрипт е дефиниран на базата на датата и броя на таблицата pv_users.

Въпроси и Отговори