Учебники

9) Присоединиться и SubQuery

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

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

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

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

Шаг 1) Создание таблицы «sample_joins» с именами столбцов ID, ФИО, возраст, адрес и зарплата сотрудников

Запросы и реализация Hive

Шаг 2) Загрузка и отображение данных

Запросы и реализация Hive

Из приведенного выше снимка экрана

  1. Загрузка данных в sample_joins из Customers.txt
  2. Отображение содержимого таблицы sample_joins

Шаг 3) Создание таблицы sample_joins1 и загрузка, отображение данных

Запросы и реализация Hive

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

  1. Создание таблицы sample_joins1 со столбцами Orderid, Date1, Id, Amount
  2. Загрузка данных в sample_joins1 из orders.txt
  3. Отображение записей, присутствующих в sample_joins1

В дальнейшем мы увидим различные типы объединений, которые можно выполнять на таблицах, которые мы создали, но перед этим вы должны рассмотреть следующие моменты для объединений.

Некоторые моменты, которые нужно соблюдать в Joins:

  • В соединениях разрешено только равенство
  • В одном запросе можно объединить более двух таблиц
  • Существуют соединения LEFT, RIGHT, FULL OUTER, чтобы обеспечить больший контроль над предложением ON, для которого нет соответствия
  • Объединения не коммутативны
  • Объединения являются левоассоциативными независимо от того, являются ли они левыми или правыми объединениями

Различные типы соединений

Соединения имеют 4 типа, это

  • Внутреннее соединение
  • Левое внешнее соединение
  • Правое внешнее соединение
  • Полное внешнее соединение

Внутреннее соединение:

Записи, общие для обеих таблиц, будут извлечены этим внутренним соединением.

Запросы и реализация Hive

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

  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 в каждом столбце правой таблицы

Запросы и реализация Hive

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

  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.

Запросы и реализация Hive

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

  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 для столбцов, пропущенные значения которых совпадают с обеих сторон.

Запросы и реализация Hive

Из приведенного выше снимка экрана мы можем наблюдать следующее:

  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 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.

Встраивание пользовательских скриптов:

Hive предоставляет возможность написания пользовательских скриптов для требований клиента. Пользователи могут написать свою собственную карту и сократить количество сценариев в соответствии с требованиями. Они называются встроенными пользовательскими сценариями. Логика кодирования определяется в пользовательских сценариях, и мы можем использовать этот сценарий во время ETL.

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

  • В конкретных клиентских требованиях разработчики должны писать и развертывать скрипты в Hive.
  • Где встроенные функции Hive не будут работать для определенных требований домена

Для этого в Hive он использует предложение TRANSFORM для встраивания как сценариев карты, так и сценариев редуктора.

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

  • Столбцы будут преобразованы в строку и разделены символом 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