Hive的查询、数据加载和交换、聚合、排序、优化

Hive笔记02--查询、数据加载和交换、聚合、排序、优化

Hive的数据查询

基础查询

1.基本结构
SELECT 字段列表|表达式|子查询
FROM 表|子查询|视图|临时表
WHERE NOT 条件A AND | OR 条件B -- 先 面向原始行进行筛选
GROUP BY 字段A[,字段B,...] -- 不能用 DISTINCT
HAVING 聚合条件(非原始字段) -- 后 针对聚合后的字段进行二次筛选
ORDER BY 字段A[,字段B,...] -- 再 全局排序
LIMIT N(前N条记录)|M(行号偏移量),N(记录数) -- 数据截取

2.条件的写法
字段 ？值
关系：>,>=,<,<=,=,<>
BETWEEN (>=) SMALL_VALUE AND (<=)BIG_VALUE
逻辑：NOT EXPR1 AND|OR EXPR2：
EXPR1 为假 并且|或者 EXPR2为真才成立(NOT对最近的条件进行处理)
IF(BOOL_EXPR,VALUE_IF_TRUE,VALUE_IF_FALSE_OR_NULL)
NVL(VALUE,VALUE_IF_NULL)
ISNULL(VALUE) <=> VAL IS NULL => 0 不是NULL 1 是NULL
CASE EXPR WHEN V1 THEN VAL1 WHEN V2 THEN VAL2 ... ELSE VALN END <=> SWITCH ... CASE
CASE WHEN C1 THEN VAL1 WHEN C2 THEN VAL2 ... ELSE VALN END <=> IF ... ELSE IF ...
🔺 WHERE NOT EXISTS() 用于只在某个特定条件不存在时执行某个操作，它经常与INSERT,UPDATE,DELETE一起使用。
确保在执行操作之前数据库中不存在特定的记录(通常用于避免表中出现重复目录)
INSERT INTO TABLE_NAME(COL1,COL2,...)
SELECT val1,val2
WHERE NOT EXISTS (
SELECT 1
FROM TABLE_NAME
WHERE CONDITION
);

通配符：like '%|'
% 表示任意个任意符号 '%txt' 以txt为结尾的 表示一个任意符号 '__op' 以op为结尾并且限定4个字符
rlike 'REGEX_EXPR'

SQL中的=如何在不同场景下区分不同的含义？
由语法来决定，WHERE中只能作为关系运算符，UPDATE中只能作为赋值运算符。

** 例1 ：2016年之后不同性别用户订单总额在20000元以上的用户(目标文件名为hive_internal_par_csv_test1w)
代码：

SELECT user_id,sum(order_amount) total
    FROM hive_internal_par_csv_test1w
    WHERE year>=2016
    GROUP BY user_gender,user_id
    HAVING total >= 20000

高级查询

** 连接查询和子查询的效率对比
在多表数据量大的情况下，建议采用连接查询。子查询会多次遍历数据，而连接查询只遍历依次。

** 优先级
JOIN > 子查询 > HAVING

1.子查询
SELECT 和主查询存在逻辑主外键关系的表中的非本表字段值
FROM 表|子查询|视图|临时表
WHERE 字段(条件判断的字段类型相同)
WHERE FIELD IN|=|>= (SELECT ONE_FIELD FROM TABLE)
GROUP BY 字段或字段的一部分
FIELD|SUBSTR(FIELD,0,4)

子查询核心：缩小数据规模，提取关键数据
SELECT(列裁剪)+WHERE(行筛选)+GROUP BY(行聚合)

2.CTE：
WITH VIEW_01
AS(
SELECT ...
),VIEW_02 AS(
SELECT ...
)

** 例2 ：2016年之后不同性别用户订单总额在20000元以上的用户，按1万一个档，统计各档位的用户人数，并生成柱状图。
注意档位的设置
代码：

WITH total_amount
        AS (
            SELECT sum(order_amount) total
            FROM hive_internal_par_csv_test1w
            WHERE year>=2016
            GROUP BY user_gender,user_id
            HAVING total >= 20000
           ),
         level_amount
         AS (
             SELECT ROUND(total/10000) AS level
             FROM total_amount
            )
    SELECT level,COUNT(*) AS level_count
    FROM level_amount
    GROUP BY level;

3.关联查询( JOIN )
1.异同：
传统关系型数据库倾向于将数据拆分为多个相互关联的表
目的：维护数据一致性（例如，防止因重复输入或错误更新造成的不一致），并且通常使更新、插入和删除操作更有效率。
大数据则倾向于使用非规范化的方法，即将所有相关数据存储在一个大的“宽表”中。 目的：通过减少表连接操作，可以加快数据处理速度。
2.Hive join仅支持等值连接。
内连接：INNER JOIN
外连接：OUTER JOIN
RIGHT JOIN , LEFT JOIN , FULL JOIN
交叉连接：CROSS JOIN
应用场景：将好友关系表的两份副本进行CROSS JOIN，获得双向关系表，由此进行关系网的建模。
代码：
SELECT Person1, Person2 FROM Friendship
UNION -- 合并并去除重复项，获得双向关系表
SELECT f1.Person1, f2.Person2
FROM Friendship f1
CROSS JOIN Friendship f2
WHERE f1.Person1 = f2.Person2 AND f1.Person2 = f2.Person1 -- 获取符合双向关系的组合。
作用：在没有明确预先定义关系的情况下，将两个表的所有行进行组合，创建了一种新的关联形式。
隐式连接：IMPLICIT JOIN
select A.,B.
from A,B
where A.k=B.fk

** 例3：使用关联查询获取没有订单的所有顾客
代码：

with no_order_customer as (
        select cbc.customer_id
        from cb_orders cbo
        right join cb_customers cbc
        on cbo.order_customer_id = cbc.customer_id
        group by cbc.customer_id
        having count(cbo.order_customer_id) = 0
    )
    select count(B.customer_id) / count(A.customer_id) AS no_order_rate
    from cb_customers A
    left join no_order_customer B
    on A.customer_id = B.customer_id

注意：要先确定原始表达式的计算结果，才能够用round进行优化

** 例4： 使用关联查询查询所有顾客的成交订单总金额
注意：原始数据中存在subtotal字段由quantity和price相乘而来，计算订单表中某一商品的总金额。
order_item_quantity
order_item_subtotal
order_item_product_price
对于数据库设计，要求除了主键依赖之外，不包含任何传递依赖性。而subtotal显然依赖了其他两个字段，
但是对于大数据来说是允许的，因为subtotal可能是在DW层所做的进一步数据汇总。

UNION

UNION ALL：合并后保留重复项
UNION：合并后删除重复项
** UNION 只适用于不允许出现重复项的情况，如果两个待合并的数据集不可能存在重复数据记录，还是选择UNION ALL。

Hive的数据加载和交换

INSERT

常用程度：写表 > 写盘
写表操作 单次插入

INSERT OVERWRITE|INTO TABLE [DB_NAME].TBL_NAME1 [PARTITION(pf1,...pfn)]
SELECT ... FROM ...

** 多次插入

FROM SOURCE_TABLE
    INSERT OVERWRITE|INTO TABLE [DB_NAME].TBL_NAME1 [PARTITION(pf1,...pfn)]
    SELECT ...
    WHERE ...
    INSERT OVERWRITE|INTO TABLE [DB_NAME].TBL_NAME2 [PARTITION(pf1,...pfn)]
    SELECT ...
    WHERE ...

... TABLE_N1(X,Y,Z) 语法支持向指定列插入数据，但不建议如此做。 原因：低效 可能破坏数据完整性

** 写表操作中SELECT子句的字段列表的列数、列数据类型，必须与目标表中的列数，列数据类型保持一致。

写盘操作

FROM SOURCE_TABLE
    ROW FORMAT SERDE|DELIMITED        
    (
    fields terminated by '|'
    collection items terminated by ','
    map keys terminated by ':'
    )
    STORED AS ... 
    INSERT OVERWRITE [local] directory 'ABS_PATH1'
    SELECT ...
    [ROW FORMAT SERDE|DELIMITED ...]
    INSERT OVERWRITE [local] directory 'ABS_PATH2'
    SELECT ... ;

** 对每一次写盘操作，都需要指定表格式和数据存储格式，不建议按默认配置。

** 注意：

1. 写表操作支持覆盖|追加
   写盘操作只支持覆盖，执行前需要先创建一个目标目录。
2. 支持来自同一个数据源/表的多次插入
3. 无LOCAL：HDFS
   有LOCAL：本地文件系统(Linux)
4. 默认数据以TEXT格式写入，列由^A分隔
5. 支持自定义分隔符导出文件为不同格式,CSV,JSON等
   (需要在INSERT之前指定导出文件的格式)

Hive数据交换 IMPORT / EXPORT

作用：用于表数据与HDFS之间的数据迁移。
表数据导出
EXPORT TABLE TABLE_NAME [PARTITION(pf1=v1,...,pf2=v2)] TO 'HDFS_PATH'
表数据导入
IMPORT TABLE TABLE_NAME [PARTITION(pf1=v1,...,pf2=v2)] FROM 'HDFS_PATH'

** IMPORT TABLE 与 INSERT OVERWRITE DIRECTORY 的区别
IMPORT TABLE ：适用于数据的完整备份和迁移，会将整个HIVE表的数据和元数据导入到HDFS路径下，
可以使用IMPORT命令将从HDFS路径下将完整的该表导入到一个HIVE实例中。
INSERT OVERWRITE DIRECTORY ：只导入数据(表查询数据)，不导入任何元数据。适用于数据处理。
不可以使用IMPORT命令将从HDFS路径下将完整的该表导入到一个HIVE实例中。

Hive的聚合运算

Hive分组

GROUP BY EXPR(field|func|case...when)
作用：为了聚合而分组或去重
GROUP BY A,B,C GROUPING SETS(B,(A,C),(A,B,C)) ✔ GROUPING SETS相当于多个GROUP BY分组统计后再UNION的逻辑
GROUP BY CUBE(A,B,C) -- A,B,C,(A,B),(A,C),(B,C),(A,B,C)
GROUP BY ROLLUP(A,B,C) -- A,(A,B),(A,B,C)

GROUP BY + CASE WHEN ...
SELECT
CASE
WHEN score >= 60 AND pass = 'Yes' THEN 'Passed'
WHEN score < 60 OR pass = 'No' THEN 'Failed'
ELSE 'Under review'
END AS status,
COUNT(*) AS count
FROM exams
GROUP BY status;

三种分区方式都会产生一个groupingid
先查找出所有的groupingid及其对应的组合的关系，并作为筛选条件。

DISTRIBUTE BY EXPR(field|func|case...when)
作用：
类似Partitioner，确定Reducer
规则：
指定按哪个字段的Hashcode分区，配合预先设定Reducer数量。

HAVING

-having使用
select sex_age.age from employee group by sex_age.age
having count(*)<1;

-使用子查询代替having
select a.age from (select count(*)as cnt,sex_age.age
from employee group by sex_age.age) a where a.cnt <1;

** HAVING之后 需要用子查询代替表达式 的原因
SQL语句的本质是MR!!!

1. 层次结构更加清晰，更容易做stage的划分，抽象语法树的构建更加容易。
2. 先进行子查询可以先降低数据规模，避免在主查询中一直对全量数据直接做处理。

基础聚合

-- 基础聚合
max min count avg sum —— MMCAS
COUNT(NULL)=0 SUM(NULL)=NULL
COUNT(FIELD | * | 1) : 计算所有数据行，不管是否为NULL
-- 集合类型聚合
collect_set (F|func|case...when) -- 去重后的列表
collect_list (F|func|case...when) -- 列表
格式为["11599","265","5462"]

eg：查询每个区域销售的所有商品
SELECT region, collect_set(product) as products
FROM sales
GROUP BY region;

Hive的数据排序

数据排序的作用：便于开窗

ORDER BY

ORDER BY EXPR(FIELD|FUNC|CASE...WHEN)

1. 执行全局数据排序，至少会起两个stage
2. 尽量不要使用(特别是在大查询语句最后排序的情况)，性能很差
   如果要使用，应提前做好数据过滤

3. NULL 值在排序时最小

   排序时如何将排序字段值为NULL的行后置？
   ORDER BY IF(NAME IS NULL,1,0)：排序时不是NULL的字段都为0，是NULL的字段都为1，实现后置。
   NULL 后置有哪些业务场景？

   1. 首先展示完整或更相关的信息。产品列表中没有名称(名称为NULL)的产品作为不完整的信息，应该在列表的末尾展示。
   2. 一个任务列表中，其中未指定截止日期（即日期为 NULL）的任务可能被认为是不紧急的，应该排在有明确截止日期的任务之后。
4. ORDER BY CASE WHEN ... 通常用于自定义排序规则
   SELECT * FROM table_name
   ORDER BY
   CASE
   WHEN F1=V1|CONDITION1 THEN 1
   WHEN F1=V2|CONDITION2 THEN 2
   ELSE 3
   END;

DISTRIBUTE BY + SORT BY

SORT BY 对每个Reducer中的数据进行排序
当Reducer数量设置为1时，SORT BY 等同于 ORDER BY
排序列必须出现在 SELECT 的字段列表中，即排序列必须为原始字段
可以指定 ASC|DESC

总结：
对于Reducer数量为1的情况， ORDER BY 和 SORT BY 都能够实现全局排序，两者效率相差不大。
对于Reducer数量>1的情况， DISTRIBUTE BY + SORT BY 能够实现部分排序。
部分排序的需求通常比全局排序要多，例如出现"每个客户的最大消费金额"，只需要以每个客户为分组进行排序即可。
而对于Reducer数量>1的情况， ORDER BY 全局排序会存在 " 所有数据都需要通过单个Reducer " 的瓶颈，效率低。

CLUSTER BY

CLUSTER BY = DISTRIBUTE BY + SORT BY ：确定Reducer并对Reducer端进行排序
** CLUSTER BY 与 DISTRIBUTE BY + SORT BY 有什么异同？
异：

1. CLUSTER BY 必须针对同字段进行分区+排序， DISTRIBUTE BY + SORT BY 可以针对不同字段分别进行分区和排序。
2. 不支持 ASC|DESC ，默认为升序。
   CLUSTER BY 旨在简化， DISTRIBUTE BY + SORT BY 旨在灵活性控制。
   同：
3. 排序列必须出现在SELECT COLUMN列表中

** 如何理解"为了充分利用所有的Reducer来执行全局排序，可以先使用CLUSTER BY,然后使用ORDER BY"这句话？
直接使用 ORDER BY 只在单个Reducer上处理数据
使用 CLUSTER BY + ORDER BY 可以先将数据均匀分配到各个Reducer中进行局部排序后，再进行合并和整理。

** 如何理解SORT BY排序的字段必须是SELECT字段列表中的一员，而ORDER BY排序的字段不必要？
ORDER BY 使所有数据聚集在一个Reducer进行处理，这个Reducer会接收到整个数据集的完整记录，包括没有被SELECT指定的字段
SORT BY 没有被SELECT指定的字段在所有的Reducer中都不会出现。

** 全局排序方式选用
小数据集 ： ORDER BY
大数据集 ： CLUSTER BY + ORDER BY

优化

Hive JOIN - MAPJOIN

1. MAPJOIN配置
   set hive.auto.convert.join = true(默认开启) -- 开启mapjoin操作
   set hive.mapjoin.smalltable.filesize=25000000; -- 重新定义小表边界，如果内存空闲，则可以调大
   set hive.optimize.bucketmapjoin=false; -- 对分桶表不做MapJoin

** 总结：只有非分桶表的不超过最大表大小的小表，在开启MapJoin的情况下才会走MapJoin。

1. 不等值连接
   如果两张表符合 MAPJOIN 的应用场景，则在设置关联条件的时候可以设置为不等值连接。

原因：小表降低了不等值连接所带来的复杂性。
3.MAPJOIN不支持：在以下这些情况，即使符合MAPJOIN的配置条件，也不会走MAPJOIN
在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面
在UNION, JOIN 以及其他 MAPJOIN之前 记忆：联合(除UNION ALL)之前，分组排序之后
** 原因：MAPJOIN适用于"大表+小表"的情况，对于在多个表或者复杂排序的情况下，这种优化不再有效。

reducer优化

设置特定MapReduce作业的reducer数量
默认为-1，表示Hive将自动决定Reducer的数量
set mapreduce.job.reduces=; ✔
set mapred.reduce.tasks=; 单个Reducer最大处理的字节数
默认为256000000，为256兆 ≈ 2Block
set hive,exec.reducers.bytes.per.reducer=;
* 限制任何Hive查询可能使用的reducer的最大数量
默认为1009
Reducer数量距离最大机器数1024还有一定余量的原因：如果集群的机器数>1000台，那么每台机器不一定都有DN，则有一些机器单独跑NN，那么这些机器就没有NM，也就不会分配容器跑Reducer.
set hive.exec.reducers.max=; 面试：Reducer的数量为什么不是越多越好？
1.资源浪费，本应分配给其他任务和节点的资源被分配给Reducer。
2.会花费过多的时间在上下文切换上而非任务处理。
3.系统需要管理更多的Reducer，增加了调度和通信的开销。

简化ORDER BY的依据

set hive.groupby.orderby.position.alias=true|false
当该属性设置为true时，允许在GROUP BY存在时在ORDER BY子句使用在SELECT语句中的列位置替代字段，
实现简化查询的目的。

为了降低数据处理的规模，多写子查询|WITH，避免始终对全量数据做处理。