Учебное пособие по соединению Hive и подзапросам с примерами

Автор: Дэвид Картер Дэвид Картер
Hours обновлено

Объединение запросов

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

  • 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 из order.txt
  3. Отображение записей, присутствующих в sample_joins1

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

Некоторые моменты, на которые следует обратить внимание при объединении:

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

Вы могли бы: