Подзапросы в Hive
Подзапрос в Hive — это вложенное выражение HiveQL, которое возвращает набор данных при выполнении родительского (внешнего) запроса. На момент выхода версии ADH 3.1.2 Hive поддерживает использование подзапросов внутри предложений FROM и WHERE.
При обработке запросов с вложенным подзапросом сначала выполняется подзапрос, а затем его результаты могут быть использованы в родительском запросе. Механизм подзапросов включен по умолчанию и не требует ручной активации.
Тестовая база данных
В данной статье для демонстрации подзапросов используются Hive-таблицы employees и departments, структура которых описана ниже.
SELECT * FROM employees; +--------------+----------------+---------------------+----------------------------+---------------+-------------------+ | employees.id | employees.name | employees.last_name | employees.email | employees.age | employees.dept_id | +--------------+----------------+---------------------+----------------------------+---------------+-------------------+ | 1 | Ivan | Ivanov | ivan_ivanov123@mail.ru | 30 | 1 | | 2 | Sarah | Connor | sarah_connor123@yahoo.com | 35 | 2 | | 3 | Rick | Sanchez | rick_123@mail.ru | 29 | 2 | | 4 | John | Smith | john_smith123@gmail.com | 50 | 3 | +--------------+----------------+---------------------+----------------------------+---------------+-------------------+ SELECT * FROM departments; +-----------------+-----------------------+-------------------------+------------------------+ | departments.id | departments.dep_name | departments.dep_office | departments.dep_phone | +-----------------+-----------------------+-------------------------+------------------------+ | 1 | sales | 110 | 9379992 | | 2 | it | 115 | 12345678 | | 3 | support | 125 | 880080080 | +-----------------+-----------------------+-------------------------+------------------------+
Для создания и наполнения тестовых таблиц Hive выполните следующий SQL с помощью /bin/beeline.
CREATE DATABASE hr;
USE hr;
CREATE TABLE IF NOT EXISTS hr.employees (id int, name string, last_name string, email string, age int, salary int, dept_id int)
COMMENT 'Employee Table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
INSERT INTO hr.employees VALUES
(1, 'Ivan', 'Ivanov', 'ivan_ivanov123@mail.ru', 30, 1000, 1),
(2, 'Sarah', 'Connor', 'sarah_connor123@yahoo.com', 35, 1500, 2),
(3, 'Rick', 'Sanchez', 'rick_123@mail.ru', 29, 1300, 2),
(4, 'John', 'Smith', 'john_smith123@gmail.com', 29, 2000, 3);
CREATE TABLE IF NOT EXISTS hr.departments (id int, dep_name string, dep_office int, dep_phone string)
COMMENT 'Departments Table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
INSERT INTO hr.departments VALUES
(1, 'sales', 110, '9379992'),
(2, 'it', 115, '12345678'),
(3, 'support', 125, '880080080');Подзапросы в предложении WHERE
Внутри предложения WHERE допускается только один подзапрос.
Подзапрос считается коррелированным (correlated), если он содержит предикат с оператором = и одна сторона оператора ссылается хотя бы на один столбец из родительского запроса, а другая сторона — хотя бы на один столбец из подзапроса.
При использовании внутри WHERE результат выполнения подзапроса часто передается операторам IN/EXIST для проверки на вхождение/наличие.
Подзапросы с IN
В примере ниже подзапрос получает идентификатор департамента по номеру офиса, а затем внешним запросом извлекаются данные о всех сотрудниках, работающих в этом департаменте.
SELECT e.name, e.last_name, e.email FROM employees e
WHERE e.dept_id IN
(SELECT id FROM departments
WHERE dep_office=110);Результат:
+---------+--------------+-------------------------+ | e.name | e.last_name | e.email | +---------+--------------+-------------------------+ | Ivan | Ivanov | ivan_ivanov123@mail.ru | +---------+--------------+-------------------------+
Подзапросы с NOT IN
В следующем примере подзапрос возвращает внутренний идентификатор департамента по номеру офиса. Далее внешний запрос возвращает информацию о всех сотрудниках, которые не находятся в данном офисе.
SELECT e.name, e.last_name, e.email
FROM employees e
WHERE e.dept_id NOT IN
(SELECT d.id
FROM departments d
WHERE d.dep_office=110)Результат:
+---------+--------------+----------------------------+ | e.name | e.last_name | e.email | +---------+--------------+----------------------------+ | Sarah | Connor | sarah_connor123@yahoo.com | | Rick | Sanchez | rick_123@mail.ru | | John | Smith | john_smith123@gmail.com | +---------+--------------+----------------------------+
Подзапросы с EXISTS
Следующий запрос возвращает номера офисов, в которых находятся сотрудники определенного возраста.
Сначала подзапрос получает список сотрудников с указанным возрастом, а затем полученный результат передается оператору EXISTS.
Для каждой строки, возвращаемой подзапросом, внешний запрос извлекает соответствующий номер офиса из таблицы departments.
SELECT d.dep_office
FROM departments d
WHERE EXISTS (
SELECT * FROM employees e
WHERE e.dept_id=d.id AND e.age=29
);Результат:
+---------------+ | d.dep_office | +---------------+ | 115 | | 125 | +---------------+
Подзапросы с агрегатными функциями
Hive предоставляет ограниченную поддержку агрегатных функций в подзапросах. Следующий пример возвращает список сотрудников, чья заработная плата выше среднего значения.
SELECT e.last_name, e.salary FROM employee e
WHERE salary > (
SELECT AVG(salary)
FROM employee);Результат:
+--------------+-----------+ | e.last_name | e.salary | +--------------+-----------+ | Smith | 2000 | | Connor | 1500 | +--------------+-----------+
Подзапросы в предложении FROM
Количество подзапросов, вложенных в предложение FROM, может быть произвольным.
Подзапросы SELECT в предложениях FROM должны быть именованными, как и таблицы внутри предложения FROM имеют свое имя.
Все столбцы в выборке подзапроса должны иметь уникальные имена; данные столбцов, полученные в результате выполнения подзапроса, доступны во внешнем запросе под этими именами как обычные столбцы таблицы.
Например:
SELECT t.name, t.email
FROM (SELECT e.name, e.email
FROM employees e
WHERE e.age=35) t; (1)| 1 | Алиас (t) необходим для идентификации подзапроса SELECT.
По этому идентификатору можно получить значение столбца подзапроса из внешнего запроса. |
Пример вложенного подзапроса SELECT
В следующем примере подзапрос выполняет выборку данных из явно соединенных таблиц employees и departments.
Затем во внешнем запросе формируется новый почтовый адрес для каждого сотрудника на основе данных, полученных в подзапросе.
SELECT t.old_email, concat(t.name, t.last_name, '_', t.department_name, '@new.domain.org')
FROM
(SELECT e.name, e.last_name, e.email AS old_email, d.dep_name AS department_name
FROM employees e
JOIN departments d ON e.dept_id=d.id
) t;Результат:
+----------------------------+-----------------------------------+ | t.old_email | new_email | +----------------------------+-----------------------------------+ | sarah_connor123@yahoo.com | SarahConnor_it@new.domain.org | | rick_123@mail.ru | RickSanchez_it@new.domain.org | | john_smith123@gmail.com | JohnSmith_support@new.domain.org | | ivan_ivanov123@mail.ru | IvanIvanov_sales@new.domain.org | +----------------------------+-----------------------------------+