Учебное пособие по соединению Hive и подзапросам с примерами
Дэвид Картер
⚡ Умное резюме
Объединение таблиц с помощью оператора Hive объединяет строки из двух или более таблиц по соответствующему столбцу, а подзапросы вкладывают один запрос в другой, поэтому здесь оба метода демонстрируются на двух примерах таблиц, загруженных из текстовых файлов.
Объединение запросов
Запросы на объединение таблиц могут выполняться над двумя таблицами, присутствующими в HiveДля более ясного понимания концепции объединения таблиц мы создадим здесь две таблицы:
- sample_joins (связано с данными о клиенте)
- sample_joins1 (связано с деталями заказа, размещенными сотрудниками)
Шаг 1) Создание таблицы «sample_joins» со столбцами Id, Name, Age, address и salary сотрудников. На скриншоте ниже показан оператор CREATE TABLE и его подтверждение.
Шаг 2) Загрузка и отображение данных. На следующем снимке экрана показана команда загрузки, за которой следует содержимое таблицы.
На скриншоте выше:
- Загрузка данных в sample_joins из Customers.txt
- Отображение содержимого таблицы sample_joins
Шаг 3) Создание таблицы sample_joins1, затем загрузка и отображение ее данных, как показано на скриншоте ниже.
На приведенном выше скриншоте можно заметить следующее:
- Создание таблицы sample_joins1 со столбцами Orderid, Date1, Id и Amount.
- Загрузка данных в sample_joins1 из order.txt
- Отображение записей, присутствующих в sample_joins1
Далее мы рассмотрим различные типы объединений, которые можно выполнить над созданными нами таблицами. Прежде чем это сделать, необходимо учесть следующие моменты, касающиеся объединений.
Некоторые моменты, на которые следует обратить внимание при использовании операций объединения таблиц:
- В операциях объединения разрешены только объединения на основе равенства.
- В одном запросе можно объединить более двух таблиц.
- Левое, правое и полное внешнее соединение существуют для обеспечения большего контроля над предложением ON, для которого нет соответствия.
- Соединения не коммутативны.
- Соединения являются левоассоциативными независимо от того, являются ли они ЛЕВЫМИ или ПРАВЫМИ соединениями.
Ограничение равенства отражает особенности Hive, существовавшие на протяжении многих лет. Начиная с версии Hive 2.2.0, поддерживаются сложные выражения в предложении ON (HIVE-15211), поэтому в текущей версии допускается условие неравенства. В более старых версиях условие должно быть проверкой на равенство, а все остальное перемещается в предложение WHERE.
Различные типы соединений
Существует 4 типа объединений (Join). Это:
- Внутреннее соединение
- Левое внешнее соединение
- Правое внешнее соединение
- Полное внешнее соединение
Ниже каждый тип демонстрируется на примере одних и тех же двух таблиц, поэтому единственное различие между примерами заключается в том, какие несовпадающие строки сохраняются.
Внутреннее соединение
В результате этого внутреннего соединения будут получены записи, общие для обеих таблиц. На скриншоте ниже отображаются только клиенты, у которых есть соответствующий заказ.
На приведенном выше скриншоте можно заметить следующее:
- Здесь мы выполняем запрос с объединением таблиц sample_joins и sample_joins1 с использованием ключевого слова JOIN, с условием соответствия (c.Id = o.Id).
- В результате отображаются общие записи, присутствующие в обеих таблицах, выбранные на основе условия, указанного в запросе.
Запрос:
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 не соответствует ни одной записи в правой таблице, объединение все равно вернет в результате запись со значением NULL в каждом столбце правой таблицы.
На скриншоте ниже показано, что отображаются все покупатели, включая тех, кто не сделал заказ.
На приведенном выше скриншоте можно заметить следующее:
- Здесь мы выполняем запрос с объединением таблиц sample_joins и sample_joins1 с помощью ключевого слова “LEFT OUTER JOIN” и условием соответствия (c.Id = o.Id). Например, здесь мы используем идентификатор сотрудника в качестве ссылки; проверяется, является ли этот идентификатор общим для правой и левой таблиц. Это выступает в качестве условия соответствия.
- В выходных данных отображаются записи, выбранные в соответствии с условием, указанным в запросе. Значения 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 не соответствует ни одной записи в левой таблице, объединение все равно вернет в результате запись со значением NULL в каждом столбце левой таблицы.
- При использовании RIGHT-соединений записи всегда возвращаются из правой таблицы, а соответствующие записи — из левой. Если в левой таблице нет значения, соответствующего столбцу, в этом месте будут возвращены значения NULL.
На скриншоте ниже показано зеркальное отображение предыдущего результата: отображаются все заказы, совпавшие или нет.
На приведенном выше скриншоте можно заметить следующее:
- Здесь мы выполняем запрос с объединением таблиц sample_joins и sample_joins1 с использованием ключевого слова “RIGHT OUTER JOIN” и условием соответствия (c.Id = o.Id).
- В результате отображаются записи, выбранные в соответствии с условием, указанным в запросе.
Запрос:
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 столбцы, в которых отсутствуют совпадающие значения с обеих сторон, как показано на скриншоте ниже.
На приведенном выше скриншоте можно заметить следующее:
- Здесь мы выполняем запрос с объединением таблиц sample_joins и sample_joins1 с использованием ключевого слова “FULL OUTER JOIN” и условием соответствия (c.Id = o.Id).
- В выходных данных отображаются все записи, присутствующие в обеих таблицах, выбранные на основе условия, указанного в запросе. Значения 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)
Подзапросы
Объединение таблиц (JOIN) размещает таблицы рядом друг с другом. Подзапрос делает нечто другое: он вкладывает один запрос в другой, так что внешний запрос может работать с результатом, который уже был вычислен.
Вложенный запрос называется подзапросом. Основной запрос будет зависеть от значений, возвращаемых подзапросом.
Подзапросы можно разделить на два типа:
- Подзапросы в предложении 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.
В этих встроенных пользовательских скриптах необходимо соблюдать следующие правила:
- Перед передачей пользовательскому скрипту столбцы будут преобразованы в строки и разделены символом табуляции.
- Стандартный вывод пользовательского скрипта будет обрабатываться как столбцы строк, разделенных символом табуляции.
Пример встроенного скрипта:
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 — это таблица пользователей, которая содержит такие поля, как userid и date, как указано в map_script.
- Скрипт редуктора определяется на основе даты и количества записей в таблице pv_users.
