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

Съдържание:

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 от orders.txt
  3. Показване на записи, налични в sample_joins1

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

Някои точки, които трябва да се спазват в Joins:

  • В присъединяванията са разрешени само присъединявания по равно
  • Повече от две таблици могат да бъдат обединени в една и съща заявка
  • Съществуват ЛЯВО, ДЯСНО, ПЪЛНО ВЪНШНО присъединяване, за да се осигури повече контрол върху клаузата 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).

    Например тук използваме идентификатор на служител като референция, той проверява дали 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 за вграждане на скриптове за карта и редуктор.

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

  • Колоните ще бъдат трансформирани в низ и разграничени от TAB, преди да бъдат предоставени на потребителския скрипт
  • Стандартният изход на потребителския скрипт ще се третира като разделени с 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