Серия Hive (8) — подробное объяснение запроса данных Hive

Apache Hive

1. Подготовка данных

Чтобы продемонстрировать операцию запроса, необходимо заранее создавать три таблицы и загрузить тестовые данные.

Файлы данных Emp.txt и Dept.txt с этого складаresourcesСкачать каталог.

1.1 Штатный стол

 -- 建表语句
 CREATE TABLE emp(
     empno INT,     -- 员工表编号
     ename STRING,  -- 员工姓名
     job STRING,    -- 职位类型
     mgr INT,   
     hiredate TIMESTAMP,  --雇佣日期
     sal DECIMAL(7,2),  --工资
     comm DECIMAL(7,2),
     deptno INT)   --部门编号
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

  --加载数据
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp;

1.2 Таблица отдела

 -- 建表语句
 CREATE TABLE dept(
     deptno INT,   --部门编号
     dname STRING,  --部门名称
     loc STRING    --部门所在的城市
 )
 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
 
 --加载数据
 LOAD DATA LOCAL INPATH "/usr/file/dept.txt" OVERWRITE INTO TABLE dept;

1.3 Таблица разделов

Здесь необходимо создать дополнительную таблицу разделов, в основном для демонстрации запроса раздела:

CREATE EXTERNAL TABLE emp_ptn(
      empno INT,
      ename STRING,
      job STRING,
      mgr INT,
      hiredate TIMESTAMP,
      sal DECIMAL(7,2),
      comm DECIMAL(7,2)
  )
 PARTITIONED BY (deptno INT)   -- 按照部门编号进行分区
 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";


--加载数据
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=20)
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=30)
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=40)
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=50)

2. Запрос одной таблицы

2.1 SELECT

-- 查询表中全部数据
SELECT * FROM emp;

2.2 WHERE

-- 查询 10 号部门中员工编号大于 7782 的员工信息 
SELECT * FROM emp WHERE empno > 7782 AND deptno = 10;

2.3 DISTINCT

Hive поддерживает дедупликацию с помощью ключевого слова DISTINCT.

-- 查询所有工作类型
SELECT DISTINCT job FROM emp;

2.4 Запрос на раздел

Запросы на основе разделов можно указать раздел или диапазон разделов.

-- 查询分区表中部门编号在[20,40]之间的员工
SELECT emp_ptn.* FROM emp_ptn
WHERE emp_ptn.deptno >= 20 AND emp_ptn.deptno <= 40;

2.5 LIMIT

-- 查询薪资最高的 5 名员工
SELECT * FROM emp ORDER BY sal DESC LIMIT 5;

2.6 GROUP BY

Hive поддерживает групповые операции агрегации с помощью GROUP BY.

set hive.map.aggr=true;

-- 查询各个部门薪酬综合
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;

hive.map.aggrУправляет агрегацией программы. Значение по умолчанию неверно. Если установлено значение true, Hive выполнит агрегацию на этапе сопоставления. Это повышает эффективность агрегации, но потребляет больше памяти.

2.7 ORDER AND SORT

Вы можете использовать Order By или Sort By для сортировки результатов запроса, поле сортировки может быть интегрировано или строка: если это целое число, следуйте размеру размера; если это строка, сортируйте по словам. Разница между ORDER BY и SORT BY заключается в следующем:

  • При использовании ORDER BY будет редьюсер для сортировки всех результатов запроса, что может обеспечить глобальный порядок данных;
  • При использовании SORT BY сортировка выполняется только внутри каждого редуктора, что гарантирует упорядоченность выходных данных каждого редуктора, но не гарантирует упорядочение в глобальном масштабе.

Поскольку ORDER BY может быть очень длинным, если вы устанавливаете строгий режим (hive.mapred.mode = strict), за ним должен следовать другойlimitпункт.

Примечание. Значение по умолчанию для hive.mapred.mode — нестрогий, то есть нестрогий режим.

-- 查询员工工资，结果按照部门升序，按照工资降序排列
SELECT empno, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;

2.8 HAVING

Сгруппированные данные можно фильтровать с помощью HAVING.

-- 查询工资总和大于 9000 的所有部门
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

2.9 DISTRIBUTE BY

Если вы хотите распространять данные с одним и тем же значением ключа на один и тот же редьюсер для обработки, вы можете использовать предложение DISTRIBUTE BY. Следует отметить, что хотя DISTRIBUTE BY может распространять данные с одним и тем же значением ключа на один и тот же редуктор, он не может гарантировать, что данные будут упорядочены на редюсере. детали следующим образом:

Отправьте следующие 5 данных двум редьюсерам для обработки:

k1
k2
k4
k3
k1

Редуктор1 получает следующие данные не по порядку:

k1
k2
k1

Редуктор2 получает данные следующим образом:

k4
k3

Если вы хотите, чтобы данные на редюсере были упорядочены, вы можете комбинироватьSORT BYИспользуйте (пример ниже) или используйте CLUSTER BY, который мы рассмотрим ниже.

-- 将数据按照部门分发到对应的 Reducer 上处理
SELECT empno, deptno, sal FROM emp DISTRIBUTE BY deptno SORT BY deptno ASC;

2.10 CLUSTER BY

еслиSORT BYиDISTRIBUTE BYУказаны те же поля, а сортировка SORT BY — ASC, вы можете использоватьCLUSTER BYзаменить, покаCLUSTER BYДанные могут быть гарантированно упорядочены глобально.

SELECT empno, deptno, sal FROM emp CLUSTER  BY deptno ;

3. Запрос соединения нескольких таблиц

Hive поддерживает внутренние соединения, внешние соединения, левые внешние соединения, правые внешние соединения и декартовы соединения, которые соответствуют концепциям традиционных баз данных, как показано на рисунке ниже.

Это требует особого внимания: условие ассоциации оператора JOIN должно быть указано с помощью ON и не может быть указано с помощью WHERE, иначе сначала будет выполнено декартово произведение, а затем отфильтровано, что приведет к тому, что вы не получите ожидаемого результата (т. следующая демонстрация объяснит).

https://github.com/heibaiying

3.1 INNER JOIN

-- 查询员工编号为 7369 的员工的详细信息
SELECT e.*,d.* FROM 
emp e JOIN dept d
ON e.deptno = d.deptno 
WHERE empno=7369;

--如果是三表或者更多表连接，语法如下
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

3.2 LEFT OUTER JOIN

LEFT OUTER JOIN и LEFT JOIN эквивалентны.

-- 左连接
SELECT e.*,d.*
FROM emp e LEFT OUTER  JOIN  dept d
ON e.deptno = d.deptno;

3.3 RIGHT OUTER JOIN

--右连接
SELECT e.*,d.*
FROM emp e RIGHT OUTER JOIN  dept d
ON e.deptno = d.deptno;

После выполнения правильного соединения, поскольку в отделе № 40 нет сотрудников, информация о сотруднике в это время равна NULL. Этот запрос может быть хорошим повторением вышеупомянутого - условия ассоциации оператора JOIN должны быть указаны с ON, а не с WHERE. Вы можете изменить ON на WHERE, и вы обнаружите, что данные отдела 40 все равно не могут быть найдены, потому что декартова операция не будет иметь (NULL, 40).

https://github.com/heibaiying

3.4 FULL OUTER JOIN

SELECT e.*,d.*
FROM emp e FULL OUTER JOIN  dept d
ON e.deptno = d.deptno;

3.5 LEFT SEMI JOIN

LEFT SEMI JOIN — это более эффективная реализация подзапросов IN/EXISTS.

  • Таблица справа в предложении JOIN может устанавливать условия фильтрации только в предложении ON;
  • Результат запроса содержит только данные левой таблицы, поэтому можно выбрать только столбцы в левой таблице.
-- 查询在纽约办公的所有员工信息
SELECT emp.*
FROM emp LEFT SEMI JOIN dept 
ON emp.deptno = dept.deptno AND dept.loc="NEW YORK";

--上面的语句就等价于
SELECT emp.* FROM emp
WHERE emp.deptno IN (SELECT deptno FROM dept WHERE loc="NEW YORK");

3.6 JOIN

Декартово соединение продукта, это соединение может редко встречаться в повседневной разработке, а потребление производительности относительно велико.По этой причине, если в строгом режиме (hive.mapred.mode = strict), Hive не позволит пользователям выполнять эту операцию.

SELECT * FROM emp JOIN dept;

В-четвертых, оптимизация JOIN

4.1 STREAMTABLE

При объединении нескольких таблиц, если каждое предложение ON использует общий столбец (например, следующийb.key), в это время Hive оптимизирует и выполнит JOIN для нескольких таблиц на одной и той же карте/уменьшите работу. В то же время предполагается, что последняя таблица запроса (такая как таблица c ниже) является самой большой таблицей.При выполнении операции JOIN над каждой строкой записей она будет пытаться кэшировать другие таблицы, а затем сканировать последняя таблица для расчета. Поэтому пользователям необходимо следить за тем, чтобы размер таблицы запросов увеличивался последовательно слева направо.

`SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key) JOIN c ON (c.key = b.key)`

Затем пользователям не всегда нужно помещать самую большую таблицу в конец оператора запроса, Hive предоставляет/*+ STREAMTABLE() */Флаг, используемый для определения самой большой таблицы, пример выглядит следующим образом:

SELECT /*+ STREAMTABLE(d) */  e.*,d.* 
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE job='CLERK';

4.2 MAPJOIN

Если только одна из таблиц является маленькой, Hive загружает маленькую таблицу в память. В это время программа будет напрямую сопоставлять данные другой таблицы с данными таблицы в памяти на этапе карты.Поскольку операция JOIN выполняется на карте, процесс сокращения можно опустить, поэтому эффективность может быть повышена много. Доступно в улье/*+ MAPJOIN() */для разметки небольшой таблицы пример такой:

SELECT /*+ MAPJOIN(d) */ e.*,d.* 
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE job='CLERK';

5. Другие варианты использования SELECT

Просмотрите текущую базу данных:

SELECT current_database()

6. Локальный режим

В операторах, продемонстрированных выше, большинство из них будут запускать MapReduce, а некоторые не будут запускаться, напримерselect * from emp limit 5MR не сработает, в это время Hive просто считывает содержимое файла данных и форматирует его для вывода. В запросе, который необходимо выполнить MapReduce, вы обнаружите, что время выполнения может быть очень большим.В это время вы можете включить локальный режим.

--本地模式默认关闭，需要手动开启此功能
SET hive.exec.mode.local.auto=true;

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

  • Общий входной размер задания ниже: hive.exec.mode.local.auto.inputbytes.max (по умолчанию 128 МБ);
  • Общее количество Map-Tasks меньше: hive.exec.mode.local.auto.tasks.max (по умолчанию 4);
  • Общее количество необходимых требований по снижению задач составляет 1 или 0.

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

использованная литература

  1. LanguageManual Select
  2. LanguageManual Joins
  3. LanguageManual GroupBy
  4. LanguageManual SortBy

Другие статьи серии больших данных можно найти в проекте с открытым исходным кодом GitHub.:Руководство для начинающих по большим данным

Категории