Hive Join & Підручник із прикладів із підзапитів

Зміст:

Anonim

У цьому підручнику ви дізнаєтесь-

  • Приєднуйтесь до запитів
  • Різні типи об’єднань
  • Підзапити
  • Вбудовування власних сценаріїв
  • UDF (користувацькі функції)

Приєднуйтесь до запитів:

Запити на приєднання можна виконувати за двома таблицями, наявними в Hive. Для чіткого розуміння концепцій об’єднання тут ми створюємо дві таблиці,

  • Sample_joins (пов'язані з деталями клієнтів)
  • Sample_joins1 (пов'язані з деталями замовлень, зробленими працівниками)

Крок 1) Створення таблиці "sample_joins" з іменами стовпців, іменем, ім'ям, віком, адресою та зарплатою співробітників

Крок 2) Завантаження та відображення даних

З наведеного вище знімка екрана

  1. Завантаження даних у sample_joins з Customers.txt
  2. Відображення вмісту таблиці sample_joins

Крок 3) Створення таблиці sample_joins1 та завантаження, відображення даних

З наведеного вище скріншоту ми можемо спостерігати наступне

  1. Створення таблиці sample_joins1 зі стовпцями Orderid, Date1, Id, Amount
  2. Завантаження даних у sample_joins1 із замовлення.txt
  3. Відображення записів, присутніх у sample_joins1

Просуваючись далі, ми побачимо різні типи об’єднань, які можна виконувати в таблицях, які ми створили, але перед цим вам слід врахувати наступні пункти об’єднання.

Деякі моменти, на які слід звертати увагу в Joins:

  • У програмах Join допускаються лише приєднання "Рівність"
  • В одному запиті можна об’єднати більше двох таблиць
  • ЛІВА, ПРАВА, ПОВНА ЗОВНІШНІ об’єднання існують для того, щоб забезпечити більше контролю над реченням ON, для якого немає відповідності
  • Приєднання не є комутативними
  • Приєднання є ліво-асоціативними незалежно від того, ЛІВО або ПРАВО

Різні типи об’єднань

Приєднання бувають 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);

Ліве зовнішнє приєднання:

  • Мова запитів Hive LEFT OUTER JOIN повертає всі рядки з лівої таблиці, хоча в правій таблиці немає збігів
  • Якщо речення ON відповідає нульовим записам у правій таблиці, об'єднання все одно повертає запис у результаті з 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)

Праве зовнішнє з'єднання:

  • Мова запитів Hive RIGHT OUTER JOIN повертає всі рядки з правої таблиці, хоча в лівій таблиці немає збігів
  • Якщо речення ON відповідає нульовим записам у лівій таблиці, об'єднання все одно повертає запис у результаті з NULL у кожному стовпці з лівої таблиці
  • ПРАВІ об'єднання завжди повертають записи з правої таблиці та відповідні записи з лівої таблиці. Якщо ліва таблиця не має значень, що відповідають стовпцю, вона поверне значення 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. Вихідні дані, що відображають всі записи, присутні в обох таблицях, перевіряючи умову, згадану в запиті. Нульові значення у вихідних даних тут вказують на відсутні значення у стовпцях обох таблиць.

Запит

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 clauseSELECT From (SubQuery) Subquery in WHERE clauseSELECT  FromWHERE col1 IN (SubQuery);

Приклад:

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

Тут t1 і t2 - назви таблиць. Кольоровим є Підзапит, виконаний за таблицею t1. Тут a і b - це стовпці, які додаються в підзапит і присвоюються col1. Col1 - це значення стовпця, яке присутнє в головній таблиці. Цей стовпець "col1", присутній у підзапиті, еквівалентний запиту основної таблиці у стовпці col1.

Вбудовування власних сценаріїв:

Hive забезпечує можливість написання сценаріїв, призначених для користувача, відповідно до вимог клієнта. Користувачі можуть писати власну карту і зменшувати сценарії відповідно до вимог. Вони називаються вбудованими спеціальними сценаріями. Логіка кодування визначена у спеціальних сценаріях, і ми можемо використовувати цей сценарій під час ETL.

Коли вибрати вбудовані сценарії:

  • У специфічних для клієнта вимогах розробники повинні писати та розгортати сценарії в Hive
  • Там, де вбудовані функції Hive не працюють за певними вимогами домену

Для цього в Hive він використовує речення TRANSFORM для вбудовування як скриптів map, так і редукторів.

У цьому вбудованому користувацькому сценарії ми повинні дотримуватися наступних пунктів

  • Стовпці будуть перетворені в рядок і розділені TAB перед тим, як передавати їх користувацькому сценарію
  • Стандартний вивід сценарію користувача буде розглядатися як розділені табуляцією стовпці рядків

Зразок вбудованого сценарію,

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;

З наведеного сценарію ми можемо спостерігати наступне

Це лише зразок сценарію для розуміння

  • pv_users - це таблиця користувачів, яка має такі поля, як ідентифікатор користувача та дата, як зазначено у map_script
  • Сценарій редуктора визначений на дату та кількість таблиць pv_users