Присоединение к улью & Учебное пособие по SubQuery с примерами

Содержание:

Anonim

В этом руководстве вы узнаете:

  • Присоединиться к запросам
  • Различные типы соединений
  • Подзапросы
  • Встраивание пользовательских скриптов
  • UDF (функции, определяемые пользователем)

Присоединяйтесь к запросам:

Запросы на соединение могут выполняться для двух таблиц, присутствующих в Hive. Для ясного понимания концепций объединения здесь мы создаем две таблицы здесь,

  • Sample_joins (относится к деталям клиентов)
  • Sample_joins1 (относится к деталям заказов, выполненных сотрудниками)

Шаг 1) Создание таблицы sample_joins с ID имен столбцов, именем, возрастом, адресом и зарплатой сотрудников.

Шаг 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:

  • В объединениях разрешены только равноправные соединения
  • В одном запросе можно объединить более двух таблиц
  • LEFT, RIGHT, FULL OUTER соединения существуют для того, чтобы обеспечить больший контроль над предложением 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 возвращает все строки из таблицы Right, даже если в левой таблице нет совпадений
  • Если предложение ON соответствует нулю записей в левой таблице, объединения по-прежнему возвращают запись в результате с NULL в каждом столбце из левой таблицы.
  • RIGHT соединения всегда возвращают записи из правой таблицы и сопоставленные записи из левой таблицы. Если в левой таблице нет значений, соответствующих столбцу, она вернет в этом месте значения 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 для встраивания сценариев сопоставления и редуктора.

В этих встроенных пользовательских скриптах мы должны соблюдать следующие моменты

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

Пример встроенного скрипта,

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