Hive-Spark sql常用语法

Hive-Spark sql常用语法

说明

Hive语法与Spark语法基本相同，不同之处会标明。

DDL/DML

数据类型

原生数据类型

• 数值类型：int、bigint；float、double、decimal

decimal(m,n)表示数字总长度为m位，小数位为n位。

• 时间类型：date、timestamp、interval

• 字符串类型：string

注：‘’和“”没有区别

英文字母大小写不敏感。

复杂数据类型

• 结构体(struct)：

  创建：① struct(val1, val2, val3, …)；② named_struct(name1, val1, name2, val2, …)

  SELECT struct('张三', 20, '男', '2022-09-01') AS student_info;
  ---结果
  student_info
  {"col1":"张三","col2":20,"col3":"男","col4":"2022-09-01"}
  
  SELECT named_struct('name', '张三', 'age', 20, 'gender', '男',) AS student_info;
  ---结果
  student_info
  {"name":"张三","age":20,"gender":"男"}
  
  

  获取：struct名.col_name 【col_name默认为col1、col2、…】

• 数组(array)：

  创建：array(1,2,3) >> [1,2,3]

  获取：arary名[索引号]，索引号从0开始。

• 字典(map)：基于key-value的数据结构形式。

  创建：map(‘a’,‘1’,‘b’,‘2’) >> {“a”:“1”,“b”:“2”}

  获取：map名[‘key名称’]。

Map和struct的区别: Map中的key是可变的，struct中的属性名不可变

表

建表

存在三种方式建表：

1、

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] db_name.table_name(
	col_name1 data_type [COMMENT 'col_comment_1'],
    col_name2 data_type [COMMENT 'col_comment_2'],
    ...
)[COMMENT 'tab_comment']
PARTITIONED BY (par_name data_type [COMMENT 'col_coment_x'],...)
[CLUSTERED BY (col_name,col_name,...) [SORTED BY (col_name[ASC|DESC],..)] INTO num_buckets BUCKETS]
[ROW FORMAT DELIMITED
	FIELDS TERMINATED BY '...'					-- 列分隔符
 	LINES TERMINATED BY '\n'  
 	-- 行分隔符
	COLLECTION ITEMS TERMINATED BY '...'		-- MAP、STRUCT和ARRAY等各基本元素的分隔符
	MAP KEYS TERMINATED BY '...']  				-- MAP中的key与value的分隔符
STORED AS file_format
LOCATION path
TBLPROPERTIES (property_name=property_value,...);

2、CTAS

通过AS 查询语句完成建表：将子查询的结果存在新表里。

create table
stored as orc
TBLPROPERTIES ('life_cycle' = '30d')
as select

该表在默认位置，即是内部表。

删除表

1、DROP TABLE

删除该表的元数据和数据

DROP TABLE [IF EXISTS] tab_name

2、TRUNCATE TABLE

清空表的所有数据但是保留表的元数据结构，各分区信息不会被删除

TRUNCATE TABLE [IF EXIST] tab_name

更改表

更改表属性、更改列。

cascade机制

cascade翻译为“级联”，即不仅变更新分区的表结构（metadata），同时也变更旧分区的表结构。

因此，在更改列属性以及增加字段时，要带上cascade，同步更改旧分区的元数据。

• 更改字段名

ALTER TABLE tab_name CHANGE org_col_name new_col_name data_type;

• 更改字段注释

ALTER TABLE tab_name CHANGE col_name col_name data_type COMMENT 'new_comment';

• 更改字段类型

ALTER TABLE tab_name CHANGE org_col_name org_col_name new_data_type cascade;
-- 新分区数据可正常显示，旧分区数据为0，无论insert overwrite重新导数据、把有问题的分区数据删掉都不能正常显示，因此在修改字段类型时要启动cascade机制；

• 增加字段

ALTER TABLE table_name ADD COLUMNS (col_name data_type comment '....') 【RESTRICT】; -- 默认模式为RESTRICT（即不修改元数据）

alter table table_name add columns(bonus varchar(255) comment '奖金') cascade; -- 同步修改元数据，适用于分区表
-- 在真实的业务中，我们会通过增加字段来解决一些表结构问题，如果我们使用增加字段信息语句进行字段增加，针对新分区表而言，后续更新数据的时候，可以直接更新完毕，但是对于老分区内数据而言默认是NULL，就算我们此时再次更新旧分区内的数据时，也无法将null值变成真实数据，则需要在我们添加字段的时候，在sql末尾添加cascade来启动联级机制，执行完毕后，老分区的内的数据就可以进行更新操作

参考：hive sqoo 增量更新 hive新增字段后刷数据

Hive的联级（cascade）- 修改分区表的字段类型或者增加新字段

• 删除字段

-- 删除字段的时候，就是不显示需要删除的字段信息，显示保留字段信息
ALTER TABLE table_name REPLACE COLUMNS(id BIGINT, name STRING,...); 

• 修改表的属性

alter table db_name.table_name set tblproperties ('life_cycle'='2Y');

视图/物化视图

视图（View）

Hive中的视图（view）是一种虚拟表，只保存定义，不实际存储数据，使用视图查询数据时，底层还是要从原来的表中取出对应的数据。

使用视图的好处：① 将真实表中特定的列数据提供给用户，保护数据隐式；② 降低查询语句的复杂度，优化查询语句；

# 建视图语法
create view view_name [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ...;

① 如果没有提供列名，视图列的名称将自动从定义 SELECT 表达式中派生；
② 注释不会自动从底层列继承；
③ 视图的架构在创建时就已冻结；对基础表的后续更改（例如添加列）将不会反映在视图的架构中。如果基础表以不兼容的方式被删除或更改，则后续查询无效视图的尝试将失败

物化视图（Materialized View）

物化视图是一个包含查询结果的数据库对象，可以用于预先计算并保存表连接或聚集等耗时较多的结果；

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name
  [DISABLE REWRITE]
  [COMMENT materialized_view_comment]
  [PARTITIONED ON (col_name, ...)]
  [CLUSTERED ON (col_name, ...) | DISTRIBUTED ON (col_name, ...) SORTED ON (col_name, ...)]
  [
    [ROW FORMAT row_format]
    [STORED AS file_format]
      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
AS
<query>;

当创建物化视图时，其内容将自动由执行语句中的查询的结果填充;

物化视图创建语句是原子的，这意味着在所有查询结果填充之前，物化视图不会被其他用户看到。

分区

加载分区数据

1、静态加载

定义：分区的字段值是由用户在加载数据的时候手动指定的

方式：

# 方式一：load
load data [local] inpath '路径' [OVERWRITE] into table table_name partition(分区字段1='分区值1',...)
# 有无local区别：
# ① 如果是从Hive服务器所在机器本地文件系统上(local)加载数据，则本质上是执行了hdfs dfs –put上传文件复制操作；
# ② 如果是从HDFS加载数据，则本质上进行了hdfs dfs –mv移动文件操作

# 方式二：insert+select
INSERT INTO|OVERWRITE TABLE table_name [PARTITION (par_1=val_1, par_2=val_2,...)]
select col_1,col_2,... from table_name
# 1、insert into与insert overwrite区别：
# insert into ：是将数据追加到表的末尾，不是覆盖，是追加
# insert overwrite : 重写表中的内容，即将原来的hive表中的数据删除掉，再进行插入数据操作；如果hive 表示分区表的话，insert overwrite 操作只是会重写当前分区的数据，是不会重写其他分区的数据的。

# 2、注意：① 要保证查询结果列的数目和需要插入数据表格的列数目一致
# 		② 若数据类型不一样，要保证一定可以转换成功，否则转换失败的数据将会为NULL

2、动态加载

定义：分区的字段值是基于查询结果中字段的位置自动推断出来的

方式：

INSERT INTO|OVERWRITE TABLE table_name PARTITION(par_1,par_2,...)
SELECT col_1,col_2,... FROM table_name

# 注意：
# 启用hive动态分区，需要在hive会话中设置两个参数：
# ① set hive.exec.dynamic.partition=true; 表示开启动态分区功能
# ② hive.exec.dynamic.partition.mode：指定动态分区的模式。有strict和nonstrict两种，strict严格模式要求至少有一个分区为静态分区，即至少需要指定一个静态分区列。（select子句中需要把动态分区列按照分区的顺序写出来，静态分区列不用写出来。）

增加分区

#1、增加分区
ALTER TABLE tab_name ADD PARTITION(par_name='...') LOCATION '...'
# 一次添加多个分区
ALTER TABLE tab_name ADD PARTITION(par_name='...') location
PARTITION(par_name='...') location

注：① 路径须是一个文件夹路径；

② alter table add partition 命令会改变表的元数据，但是不会加载数据，因此如果文件夹路径下没有数据文件则查不到数。

删除分区

命令：alter table Drop partition

#2、删除分区
ALTER TABLE table_name DROP PARTITION(par_name='...')
# 一次删除单个分区字段的多个分区
ALTER TABLE table_name DROP PARTITION(par_name='...'),PARTITION(par_name='...')
ALTER TABLE table_name DROP PARTITION(dt>='20200609',dt<='20210905')
# 一次删除多个分区字段中单个分区字段的单个分区数据
ALTER TABLE table_name DROP PARTITION(par_name1='...',par_name2='...')
# 一次删除多个分区字段中单个分区字段的多个分区数据
ALTER TABLE table_name DROP PARTITION(par_name1='...',dt>='20200609',dt<='20210905')
# 一次删除多个分区字段中多个分区字段的多个分区数据
ALTER TABLE table_name DROP PARTITION(month>=1,month<=4,dt>='20200609',dt<='20210905')

注：alter table drop partition 会把元数据和数据都删除

删除外部表与外部分区

对于外部表，使用drop table删除表或用drop partition删除分区后数据还是会存在。要彻底删除数据，有两种方法：

① 通过hdfs命令直接删除文件；

② 先将外部表变为内部表再删除：

alter table table_name set tblproperties (‘EXTERNAL’=‘False’)

分区数据元数据刷新

如果是通过 hadoop fs -put或hdfs api写入hive分区、hdfs dfs -rm或hdfs api删除hive分区的数据，这只是在路径中写入或删除数据文件，但是hive的metastore元数据并没有进行相应的更新。此时需要用到msck命令更新元数据。

MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

msck table table_name默认是msck add partitions。添加文件到hdfs分区路径后更新metastore的元数据

msck table table_name drop partitions。删除hdfs分区路径的文件后更新metastore的元数据。

msck tabel table_name sync partitions。同时调用add partitions和drop paritions命令。

HIVE常用命令之MSCK REPAIR TABLE命令简述

hive.msck.repair.batch.size可以控制每次扫描的分区数。默认至为0，会检查所有分区的目录是否在元数据中存在。

因此如果是每次新增一个分区的任务（daily的)，那么使用这个语句将会越来越耗费时间，MSCK适合一次导入很多分区，需要将这些分区都更新到元数据信息中。

__HIVE_DEFAULT_PARTITION__分区

使用的是动态分区，并且hive启用动态分区时，对于指定的分区键如果存在空值时，会对空值部分创建一个默认分区(__HIVE_DEFAULT_PARTITION__分区)用于存储该部分数据。

① 查看分区数据

SELECT * FROM my_table WHERE partition_name IS NULL;

② 删除分区

ALTER TABLE my_table DROP IF EXISTS PARTITION (partition_name='__HIVE_DEFAULT_PARTITION__');

虚拟字段

INPUT__FILE__NAME: 文件全路径
BLOCK__OFFSET__INSIDE__FILE: 数据块在文件内的偏移量
ROW__OFFSET__INSIDE__BLOCK: 记录在数据块内的偏移量

注意：中间用两个_ 分割。如果查询，需要设置以下参数为 true。

<property>
    <name>hive.exec.rowoffset</name>
    <value>true</value>
    <description>Whether to provide the row offset virtual column</description>
  </property>

参考：Hive虚拟字段及解释

SHOW与DESC

# show 查看创建语句和查看数据库和表
1、SHOW DATABASES;显示数据库
2、SHOW TABLES [like '*xx*']；显示当前数据库所有表
3、SHOW VIEWS # 显示当前数据库下所有视图
3、SHOW create table tab_name；显示建表语句
4、SHOW partitions tab_name；显示表的所有分区

# desc 只查看表结构信息
1、desc formatted table_name;以表格格式显示表的所有元数据

# 查看sql查询执行计划
explain formatted select ....

注释

-- 单行注释  

/*
 * 多行注释
 */

DQL（DATA QUERY Language）

基础查询完整语法

[with with_query]
SELECT [ hint ] [DISTINCT] sel_expr,sel_exper,... 
FROM tab_name
[LEFT|RIGHT|FULL OUTER|CROSS] JOIN tab_name
on join_condition
WHERE where_condition
GROUP BY col_list
HAVING 聚合函数
ORDER BY col_list
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ]
LIMIT [offset,] rows -- OFFSET的起始值为0

# 执行顺序：
# from ..  join .. on ..where .. select .. group by .. select .. having .. distinct .. order by .. limit .. union/union all

/* ## 注意
 ① select 新的字段不能被select后面的字段用
 ① 使用group by + having，select 聚合计算后的新字段可以出现在having；
 ② 窗口函数不能出现在where中
 
 */

Group by

出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段

Distinct

① distinct只能放到所有字段的前面，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个字段；

② 当distinct 后的多个字段任一为NULL时, distinct结果即为NULL；

select COUNT(distinct name,age) ,count(1)
from(
    select NULL name,10 age
    union all
    select 'wbh' name , NULL age
    union all
    select 'wbh' name , 50 age
    union all
    select 'wbh' name , 13 age
      union all
    select 'cc' name , 10 age
     union all
    select NULL name , 2 age
    union all
    select 'ZZ' name , NULL age
)a

-- 结果：3	7

Hive之distinct多字段中出现null问题

Union联合查询

select_statement 
UNION [DISTINCT | ALL] 
select_statement 

# 注：① 默认为DISTINCT
#  ② hive sql每个select_statement返回的列的数量和名称必须相同。
#  在spark sql中也要求每个select_statement返回的列的数量和名称必须相同，但是在OPPO查询平台中列的名称也可以不一样，最后列的名称取第一个select_statement的字段名称。

排序

• order by

① 作用：对输出的结果进行全局排序；

② 如果底层使用MapReduce引擎执行，只会有一个reducetask执行

③ 通常和limit搭配使用

• distribut by

① 作用：若底层使用MapReduce引擎执行，控制map的输出在reducer是如何划分的，根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法。【个人理解：即在Shuffle阶段对<key,value>进行分区，分区字段即是distribute by 字段，分区规则是hash算法】

注意：shuffle分区字段默认由Hive自己选择，可以通过distribute by自己指定，通过rank函数随机值实现随机分区，避免数据倾斜。

• sort by

在每个reducer端根据sort by 字段进行排序，可以保证局部有序。

• cluster by

作用：根据指定的字段对数据分组，每组内再根据这个字段升序排序，即根据同一个字段，分组且排序

若 DISTRIBUTE BY 与 SORT BY的字段一样 ，则CLUSTER BY=DISTRIBUTE BY +SORT BY

JOIN 连接

[inner] join
left join
left semi join
right join
full join
cross join

不带on的join相当于笛卡尔连接

select A.age Aage
,B.age Bage
from(
    select age
    from(
        select 100 age
    )a
    where age > 100
)A
full outer join(

        select 100 age
        union all
        select 80 age

)B
on A.age = B.age
-- 结果 full join
Aage	Bage
NULL	100
NULL	80
-- left join
NULL

• full join

一般在合并数据、校验两表完成相同等场景使用。

select coalesce(a.ad_id,b.ad_id) ad_id,
coalesce(a.ad_name,b.ad_name) ad_name
from(select xx from dual) a
full join (select xx from dual)b
on a.xx=b.xx

• left semi join

left semi join实现效果等同于inner join，但只能查询左表字段，不能查询右表字段

select t1.cn
from t1 
left semi join t2 
on t1.cn = t2.cn;
-- 等同于
select t1.cn
from t1 
where t1.cn in (select t2.cn from t2);

有2个注意点：

（1）left semi join 子句中右边表的过滤条件只能在on子句中，在where子句等其他地方是不行的。且过滤条件只能是等于号，不能是其他的。

（2）如果关联时遇到右表有重复记录，左表会跳过。

CTE

公用表表达式（Common Table Expression，CTE）是一个临时结果集，该结果集是从WITH子句中指定的简单查询派生而来的。

一个或多个CTE可以在Hive SELECT，INSERT， CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用

# 使用一：with + select
# with关键字在同级中只能使用一次，允许跟多个子句，用逗号隔开
with q1 as (select ....),
q2 as(select ...),
...
select ...
from q1 ..q2
# 前面的with子句定义的查询在后面的with子句中可以使用，但一个with子句内部不能嵌套with子句
with q1 as (select * from firstTable),
q2 as (select q1.id from q1)  
select * from q2

# 使用二：with + insert
with q1 as (select ....),
q2 as(select ...),
...
insert into|overwrite table tab_1
select ...
from q1 ..q2

# 使用三：create + with
create table t_cre as
with t1 as (
    select outid, name
    from t_sel1
),
t2 as (
    select outid, name
           from t_sel2
)
select * from t1 full join t2 on bzks.outid = lxs.outid

create写在with查询之前；select 、insert into|overwrite写在with之后

hive sql

hive中有一个参数

hive.optimize.cte.materialize.threshold

默认情况下是-1（关闭的），此时with as 只是定义了一个SQL片段，相当于View；当开启（大于0），比如设置为2，则如果with…as语句被引用2次及以上时，会把with…as语句生成的table物化，从而做到with…as语句只执行一次，来提高效率。

spark sql

spark对cte的操作比较少，在spark侧还没有相关的优化参数，因此spark sql 中使用with只会简化代码并不会提升效率；

参考：https://zhuanlan.zhihu.com/p/365007398

multiple inserts多重插入

对表一次扫描，多次插入。

-- 对student表扫描一次，将数据插入student_insert1和student_insert2中。
from student
insert overwrite table student_insert1
select num
insert overwrite table student_insert2
select name;

Hints（Spark）

语法

/*+ hint[ , ... ] */

Partitioning Hints

支持COALESCE、REPARTITION和REPARTITION_BY_RANGE提示，分别相当于DataSet 中的coalesce、repartition和 repartitionByRange ，可用于控制spark sql最后输出文件的数量。

① coalesce(3)

将分区数减少到指定的分区数

② REPARTITION©/REPARTITION(3)/PARTITION(3, c)

使用指定的分区表达式重新分区到指定的分区数。它以分区数、列名或两者作为参数。

③ REPARTITION_BY_RANGE©/REPARTITION_BY_RANGE(3,c)

可用于使用指定的分区表达式重新分区到指定的分区数。它以列名和可选的分区数作为参数。

SELECT /*+ REPARTITION_BY_RANGE(3, c) */ * FROM t;

coalesce和repartition区别

coalesce不会执行shuffle操作，‌只是在已有的分区之间进行合并，用于减少分区数；

repartition可以进行shuffle操作，‌会进行数据的重新分配，‌从而可以增加或减少分区数。

coalesce(1) 的作用是将数据合并到尽可能少的分区中，而 repartition(1) 则是将数据随机重分区为一个分区。

说明：在RDD中，repartition算子实际上是调用的coalesce进行Shuffle过程，而coalesce算子默认不shuffle。

Join Hints

建议 Spark 应使用的连接策略。当有多个连接策略时，优先级：BROADCAST > MERGE > SHUFFLE_HASH > SHUFFLE_REPLICATE_NL

① BROADCAST

选择BROADCAST Hash join 策略。被BROADCAST提示的表不受autoBroadcastJoinThreshold限制，都会被广播；如果左右表都设置成BROADCAST ，那么更小的表会被广播；

/* +broadcastjoin(a,b,c) */

② MERGE

如果连接键是可排序的，选择shuffle sort merge join策略。

③ SHUFFLE_HASH

选择Shuffle Hash join 策略。如果左右表都设置成SHUFFLE_HASH，那么更小的表会被构建hash表；

④ SHUFFLE_REPLICATE_NL

如果连接类型是内部连接，选择Cartesian Product Join策略

SELECT /*+ SHUFFLE_HASH(t1) */ * FROM t1 INNER JOIN t2 ON t1.key = t2.key;

如果对于指定的连接提示不满足连接条件，则hints不生效。

运算符和函数

运算

算术运算

•加法操作: +
•减法操作: -
•乘法操作: *
•除法操作: /
•取整操作: div # select 17 div 3 >> 5
•取余操作: %
•位与操作: &
•位或操作: |
•位异或操作: ^
•位取反操作: ~

关系运算

•等值比较: = 、==
•不等值比较: <> 、!=
•小于比较: <
•小于等于比较: <=
•大于比较: >
•大于等于比较: >=
•空值判断: IS NULL 
•非空判断: IS NOT NULL
•LIKE比较: LIKE
•REGEXP操作: REGEXP 正则表达式

1、like、rlike

• like

格式：A like B，其中A是字符串，B是表达式。表示能否用B去表示A的全部内容，返回的结果是True/False

① B只能使用简单匹配符号 "和%“，”"表示任意单个字符，字符”%”表示任意数量的字符（不支持正则表达）

② 如果字符串A或者字符串B为NULL，则返回NULL

• rlike

格式：A rlike B，其中A是字符串，B是表达式。表示B是否在A里面即可，返回的结果是True/False

① B中的表达式可以使用JAVA中全部正则表达式；

② 如果字符串A或者字符串B为NULL，则返回NULL；

regexp与rlike作用一样。

逻辑运算

与操作: A AND B
•或操作: A OR B
•非操作: NOT A 、!A
•在:A IN (val1, val2, ...) 
•不在:A NOT IN (val1, val2, ...)
•逻辑是否存在: [NOT] EXISTS (subquery)

注：Hive不支持where子句中的子查询，SQL常用的exist、in子句需要改写，可通过join实现

-- 不支持
SELECT a.key, a.value
FROM a
WHERE a.key in (SELECT b.key FROM B);

# 可改写为：
SELECT a.key, a.value
FROM a LEFT JOIN b ON (a.key = b.key)
WHERE b.key IS NOT NULL;

函数

可以使用show functions查看当下版本支持的函数，并且可以通过describe function extended func_name来查看函数的使用方式。

函数分类—输入输出关系

普通函数

输入一行输出一行

聚合函数

输入多行输出一行。

count();
max();
min();
sum();
avg();
grouping sets();

1、collect_set(col_name)/collect_list(col_name)

将分组中的某列转为一个数组返回，collect_list函数不去重而collect_set函数会去重。

2、count(1)、count(*)、count(col_name)、count(distinct col_name)

① count(*)会把所有行都进行统计，包括 null 行，但count(*)不会读取表中的数据，只会使用到hdfs文件中每一行的行偏移量;

② count(1) 会把所有行都进行统计，包括 null 行，但count(1)比count(*)的效率高；

③ count(col_name) 则会剔除null 值行后再统计计数（在map阶段会把null值数据给过滤掉）；

④ count(distinct col_name) 会剔除值为 null 的行后再去重计数。

参考：hive中count(*)、count(1)、count(某字段)的区别

HIve中count（1），count（*），count（字段名）三种统计的区别

3、sum(1)

统计表中行的数量。

select name,sum(1),count(1)
from(
    select 100 score,10 age,'wbh' name
    union all
    select 80 score , 50 age,'ww' name
        union all
    select 60 score , 10 age,'wbh' name
        union all
    select 100 score , 30 age,'dfas' name
            union all
    select 120 score , 0 age,'agda' name
)a
group by name
-- 结果
name	_col1	_col2
wbh		2		2
agda	1		1
dfas	1		1

4、Grouping sets()

与GROUP BY子句不同，GROUPING SETS()可以将多个分组条件组合在一起，从而得到多个分组结果

SELECT column1, column2, ..., SUM(columnN), ...
FROM table_name
GROUP BY column1, column2,....
GROUPING SETS ((), (column1),(column1,column2) ...);

① group by后面放的字段表示要分组聚合的全部字段；

② grouping sets 后面放的是 group by 后面各种字段的组合，根据实际需求进行组合，组合字段用小括号括起来。

③ 结果表会包含group by后面的所有字段，若第2组相对第3组没有column2，那么第2组生成的结果column2列为NULL

参考：Hive/Presto中函数grouping sets用法详解

④ grouping函数为超级分组记录所产生的null字段返回1，其他情况返回0。

select case when grouping(product_type) = 1
            then '商品种类 合计'
			else product_type end as product_type,
	   case when grouping(regist_date) = 1
	        then '登记日期 合计'
			else cast(regist_date as varchar(16)) end as regist_date,
	   sum(sale_price) as sum_price
from Product
group by 
grouping sets(product_type, regist_date);

参考：sql:grouping运算符

SQL grouping

⑤ 为防止指标的的重复计算，产运在使用报表时必须选中某一维度组合，而可能维度较大选择费时，因此底层处理时可使用一个字段代表某一个维度组合（需要和使用者沟通，达成使用共识）

CONCAT_WS( 
   '+' 
  ,'基础维度' 
  ,IF(GROUPING(is_brush) = 1 ,    NULL,'是否刷量' ) 
  ,IF(GROUPING(brand) = 1 ,       NULL,'品牌' ) 
  ,IF(GROUPING(entermod) = 1 ,    NULL,'用户渠道' ) 
)  AS group_type -- 维度组合

表生成函数（列转行）

输入一行输出多行。

• Explode()（爆炸函数）：把参数中的每个元素炸开变成一行数据，一个元素一行

① 参数接收array：array列表里的每个元素生成一行。

Select explode(array(1,2,3,4)) as num;

② 参数接收map：返回n行两列，map里的每一对元素作为一行，其中key为一列，value为一列；

select explode(map('A',10,'B',20,'C',30)) as (ch,num);

③ 参数接受sequence

序列函数sequence(start,stop,step)：生成指定返回的列表数据。 [start,stop]必须传入，step步长默认为1，当step为负数时，start<stop。

-- 生成一列数据
select explode(split("1,2,3,4,5",","));
select explode(sequence(1,5));

应用：生成固定间隔的日期序列

select date_add('2024-05-10',pos)
from(  
	select explode(sequence(0,10)) pos
)t

explode应用

Explode通常和lateral view侧视图一起使用。侧视图的原理是将爆炸函数的结果构建成一个类似于视图的表，然后将原表中的每一行和爆炸函数输出的每一行进行连接，生成一张新的虚拟表。

语法：select …… from tab_nam 表别名 lateral view explode(map) 表别名 as col_nam;

注意

① explode不能接收空参数，否则会丢失数据。

select t.col,t.num,a.new_col
from (
    select '' col,1 num
    union all
    select '2#3#4' col,2 num
    union all
    select NULL col,3 num
    union all
    select '0' col,4 num
    union all
    select '  ' col,5 num

)t
lateral view explode(split(col,'#')) a as new_col

-- 结果：
col		num		new_col
　		1
2#3#4	2		4
2#3#4	2		3
2#3#4	2		2
0		4		0
  		5

② Lateral View 与 lateral view outer的区别

如果在explode的列中有空值，Lateral View会将包含空值的行完全去掉；lateral view outer，它就会保留那些包含空值的行。因此，考虑代码的健壮性，建议使用lateral view outer。

• posexplode()

posexplode()函数与explode()函数用法是一样的，但会解析出数组元素的位置(从0开始)。

select
    class,student_index,student_name
from
    classinfo
    lateral view posexplode(split(student,',')) t as student_index,student_name;
    
    
/* classinfo
class	student		score
1班		小A,小B,小C	80,92,70
*/

/* 结果
class	student_index	student
1班		0				小A
1班		1				小B
1班		2				小C
*/

参考：Hive中的explode使用全解

• stack函数（不常用）

语法：stack(int n, v_1, v_2, …, v_k) as (col1,col2,…,col k/n)
功能：把k列数据转换成n行，共k/n列，n必须是正整数。其中v_1到v_k是要转化的列，col1到col k/n表示转化后的列名。

函数分类—功能

数学函数

ceiling(double) -- 向上取整
floor(double) -- 向下取整
round(double a) -- 取小数
abs -- 绝对值函数

• round

round(double a)：返回double类型的整数值部分 （遵循四舍五入）

round(double a, int d)：返回指定精度d的double类型

字符串函数

•字符串长度函数：length
•字符串反转函数：reverse
•字符串连接函数：concat
•带分隔符字符串连接函数：concat_ws
•字符串截取函数：substr/substring
•分割字符串函数: split
•去字符串首尾/前置/后置空格函数：trim、ltrim、rtrim
•左/右填充函数：lpad、rpad
字母转大写:upper
•正则表达式替换函数：regexp_replace
•正则表达式解析函数：regexp_extract
•URL解析函数：parse_url
•json解析函数：get_json_object
字符串重复函数：repeat
str_to_map函数

• split

语法：split(string str, string pat)

按照pat字符串分割str，会返回分割后的字符串数组。

① pat可以是普通字符，也可以是正则表达式

select col,split(col,'#') new_col
from (
    select '' col
    union all
    select '2#3#4' col
    union all
    select NULL col
)T
-- col		new_col
NULL		NULL
2#3#4		["2","3","4"]
　		    [""]

• concat(string str1, string str2, …)

将多个字符串连接成一个字符串。如果有一个参数为NULL，则返回的结果为NULL。

• concat_ws(string separa, string str1, string str2,…)

第一个参数是其它参数的分隔符。

数组转字符串：concat_ws(sep,array)

如果分割符为null，则返回null；参数为null，则忽略该参数。

• trim、ltrim、rtrim

① 去字符串首尾空格函数：trim(string)

② 去字符串前置空格函数：ltrim(string)

③ 去字符串后置空格函数：rtrim(string)

三个函数不会去除字符串中间的空格。

• lpad、rpad

lpad(str,len,pad)：按照指定的字符pad，向左补齐str直至总位数到达len。如果str总位数为len，则不会填充。

rpad(str,len,pad)：右补齐。

• upper

upper(string)：将字符串中的所有字母转换为大写字母

• substr/substring

substr(string A, int start)：返回字符串A从start位置到结尾的字符串

substr(string A, int start, int len)：返回字符串A从start位置开始，长度为len的字符串

注：索引从1开始

• regexp、regexp_extract/regexp_replace

  • regexp与rlike用法一样

  • regexp_extract

    语法：regexp_extract(string str , string regexp, int index)

    作用：将字符串str 按照regexp正则表达式的规则拆分，返回index指定的字符（0表示把整个正则表达式对应的结果全部返回；1表示返回正则表达式中第一个() 对应的结果）

  regexp_replace

  语法：regexp_replace(string A, string B, string C)

  作用：将字符串A中的符合java正则表达式B的部分替换为C，返回替换后的字符

  注：正则表达式中有些特殊符号需要转义，且如果在shell中运行，则可能需要多次转义。

注：hive不支持replace函数。spark sql支持replace函数

• repeat

REPEAT(str, count)：str是要重复的字符串，count是重复的次数。返回一个字符串。

• str_to_map

str_to_map(str, delimiter1, keyDelimiter)，返回map<string,string>

str：被转换的字符串

delimiter1：不同键值对之间的分隔符

keyDelimiter：键和值之间的分隔符

select str_to_map('60015_20:60014_1', ':', '_')

-- 结果：{"60014":"1","60015":"20"}


select str_to_map(regexp_replace(regexp_replace(regexp_replace(extinfo_new,'"',''),'\\{',''),'\\}',''),',',':') maps
from(
        select '{"versionId":"24140183","distributeCount":"1","appId":"2572","versionCode":"12289","instanceIp":"10.133.86.157","filterCount":"0"}' extinfo_new
)t
-- 结果：{"appId":"2572","distributeCount":"1","filterCount":"0","instanceIp":"10.133.86.157","versionCode":"12289","versionId":"24140183"}

select str_to_map(regexp_replace(regexp_replace(extinfo_new,'\\{',''),'\\}',''),',',':') maps
from(
        select '{"versionId":"24140183","distributeCount":"1","appId":"2572","versionCode":"12289","instanceIp":"10.133.86.157","filterCount":"0"}' extinfo_new
)t
-- 结果：{""appId"":""2572"",""distributeCount"":""1"",""filterCount"":""0"",""instanceIp"":""10.133.86.157"",""versionCode"":""12289"",""versionId"":""24140183""}

注：被转化的字符串中键和值不要包含符号，因为符号也会被识别成键和值的一部分。

json函数

json是一种轻量级的数据交换格式，具有简洁、易读、跨平台和语言的特点。JSON数据通过键值对的方式进行组织，其中键是字符串，值可以是字符串、数值、布尔值、数组、对象或者null

注：key必须有双引号，value如果是字符串也必须是双引号.

• get_json_object

get_json_object(json_string, '$.key')

解析 json 的字符串 json_string，以string形式返回 path 指定的内容。如果输入的 json 字符串无效，结果返回 NULL；若如果找不到该对象，则返回 NULL。

json数据解析get_json_object函数的用法

table0表中有一个字段为
test_data = '{"name": "zhangsan",
              "age": 18, 
              "preference": "music"}'
              
select get_json_object(test_data,'$.preference') from table;
-- 结果： music

select get_json_object(extinfo_new, '$.versionId')
from(
        select '{"versionId":"24140183","distributeCount":"1","appId":"2572","versionCode":"12289","instanceIp":"10.133.86.157","filterCount":"0"}' extinfo_new
)t
-- 结果：24140183

缺点：每次只能返回一个数据项。如果需要同时解析的字段很多，则需要多次使用get_json_object。

select get_json_object(test_data,'$.age'),get_json_object(test_data,'$.preference');

• json_tuple

json_tuple(json_string, column1, column2, column3 ...)

解析 json 的字符串 json_string，可同时指定多个 json 数据中的 column，返回对应的 value。如果输入的 json 字符串无效，结果返回 NULL。

优点：相比于get_json_object， json_tuple 函数一次可以解析多个 json 字段

select t1.name,
        t1.age,
        t1.prefer,
        t1.height,
        t1.nation
from (select test_data 
      from table
) t0
lateral view json_tuple(t0.test_data,'name', 'age','preference' ) t1 as name,age,preference

/*
--- name	 age		preferenct ----
  zhangsan	 18		  music
*/

超好用 Hive 内置的 json 解析函数

两个函数的共点：

① 行转列；

② 如果被要求解析的 json 是一个 json 数组，无法完成解析;

• to_json

将STRUCT类型的数据转化为json格式字符串

示例：

id	name	age
1	John	25
2	Mary	30
3	Bob	35

select to_json(struct(id,name,age)) json_data
from table;

>> 
{"id":1,"name":"John","age":25}
{"id":2,"name":"Mary","age":30}
{"id":3,"name":"Bob","age":35}

类型转换函数

原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。

cast(数据 as 数据类型)：显式的数据类型转换。

时间函数

Hive 中，可以用String、Date和Timestamp表示日期时间，String和Date均用 yyyy-MM-dd 的形式表示，Timestamp 用 yyyy-MM-dd HH:mm:ss 的形式表示。

• 日期与string转换

cast(string as date)： string 需是 'YYYY-MM-DD’格式，否则返回NULL；

cast(date as string)： 返回的string 格式为 ‘YYYY-MM-DD’

to_date(string timestamp)： 返回日期时间字段中的日期部分。

select to_date('2019-02-16 14:02:03') >> 2019-02-16

• 日期格式化

date_format(date/string/timestamp，string fmt)

数据库的日期转换为对应格式的字符串输出

-- date_format后接时间字符串,连接符只能是-,别的识别不了
select date_format('2022-05-23 23:00:01','yyyy-MM-dd') #2022-05-23
select date_format('2022-05-23 23:00:01','yyyyMMdd') #20220523
select date_format('20220523','yyyy-MM-dd') # NULL

• 日期和时间运算

DATE_ADD(date/string,expr)：日期时间增加或减少

-- date_add后接时间字符串,连接符只能是-,别的识别不了
select date_add('2022-05-23',1) # 2022-05-24
select date_add('2022-05-23',-7)# 2022-05-16

DATEDIFF(date1,date2)：计算两个日期的天数差

select datediff('2022-01-08 00:00:01', '2022-02-08 00:02:00') #31
select datediff('2022-03-08', '2022-02-08') #28

• 获取时间戳(10位)

unix_timestamp() ：获取当前时间戳（精确到秒，10位）

select unix_timestamp()   -- 565858389

unix_timestamp(string timestamp) ：获取某个时间的时间戳。输入的时间戳格式必须为’yyyy-MM-dd HH:mm:ss’，如不符合则返回null。

select unix_timestamp('2019-08-15 16:40:00')   -- 1565858400
select unix_timestamp('2019-08-15')  -- null

unix_timestamp(string date,string pattern)：将指定时间字符串格式的字符串转化成unix时间戳，如不符合则返回null。

select unix_timestamp('2019-08-15','yyyy-MM-dd')   -- 1565798400
select unix_timestamp('20190815','yyyyMMdd')   -- 1565798400
select unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss')   -- 1565858400
select unix_timestamp('2019-08-15','yyyy-MM-dd HH:mm:ss')   -- null

• 10位时间戳和日期相互转换

from_unixtime(bigint unixtime,string format)： 将时间戳秒数转化为UTC时间，并按照指定输出的格式输出。

-- 2019-08-15 16:39:49(from_unixtime默认将时间戳转化为yyyy-MM-dd HH:mm:ss格式)
select from_unixtime(1565858389)
-- 2019-08-15 16:39:49
select from_unixtime(1565858389,'yyyy-MM-dd HH:mm:ss')  
-- 20190815
select from_unixtime(1565858389,'yyyyMMdd') 

-- 方法1: from_unixtime+ unix_timestamp
-- 20171205转成2017-12-05 
select from_unixtime(unix_timestamp('20171205','yyyyMMdd'),'yyyy-MM-dd');
-- 2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-MM-dd'),'yyyyMMdd') ;

-- 方法2: substr + concat
-- 20171205转成2017-12-05 
select concat(substr('20171205',1,4),'-',substr('20171205',5,2),'-',
substr('20171205',7,2)) from dual;
-- 2017-12-05转成20171205
select concat(substr('2017-12-05',1,4),substr('2017-12-05',6,2),
substr('2017-12-05',9,2)) from dual;

• 13位时间戳与日期转换

-- 十三位转为十位
cast(1644206709158/1000 as bigint)

SELECT FROM_UNIXTIME(1571709884123/1000,'yyyy-MM-dd HH:dd:ss.sss');
-- 2019-10-22 10:22:44.044
-- 13位的整型除以1000的时间戳单位才是毫秒

from_unixtime(cast(1644206709000 as bigint),'yyyy-MM-dd HH:mm:ss')
-- 结果：54072-11-01 13:50:00
-- 十三位时间戳不除1000 转化为日期会出错。可以理解为除了1000，才可以识别到这个歌毫秒时间戳。

注：毫秒值在转换时毫秒部分并不准确，可能是因为存在精度问题。

hive中如何将13位bigint类型的时间戳的转化为毫秒标准时间格式

大数据学习-hive（三：时间戳转换）

• 时间大小比较

数据源中关于 时间/日期类型 的字段格式有很多，例如 2022-05-10、2022-05-10 15:30:15、2022/05/10、2022/05/10 15:30:15，还有很多不规范的数据，例如 2022-5-10、2022-5-8 15:30:15等等 。在进行日期大小比较时，如果直接使用这些原始数据，就很容易出现问题。

# 返回结果为 false。字符串数值与字符串数值比较 ，从左到右按位比较
select '2022-5-10 15:30:15' > '2022-5-9 15:30:15';
# 返回结果为 true
select '2022-5-10 15:30:15' > '2022-5-09 15:30:15';

因此在进行时间/日期大小比较时，最好使用date_format函数转换为标准格式再进行比较，避免出现类似上述的问题。

条件函数

•空判断函数: isnull( a )
•非空判断函数: isnotnull ( a )
•空值转换函数: nvl(value, default_value) 如果value为null则返回default_value，否则返回value
•非空查找函数: coalesce(T v1, T v2, ...) 返回参数中的第一个非空值，如果所有值都为null，则返回null
•nullif( a, b ): 如果a = b，则返回NULL；否则返回NULL。否则返回一个
•if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
•CASE 字段 WHEN b THEN c [WHEN d THEN e] [ELSE f] END
 	CASE WHEN 条件表达式 THEN 值 [WHEN 条件表达式 THEN 值] [ELSE 值] END

注：null值的判断是不能用 = , != 来判断的

集合函数

集合元素个数: size(Map<K.V>) size(Array<T>)
取map集合keys，返回数组: map_keys(Map<K.V>)
取map集合values，返回数组: map_values(Map<K.V>)
判断数组是否包含指定元素: array_contains(Array<T>, value)
数组升序排序，返回数组:sort_array(Array<T>)
提取指定范围内的数组元素：slice(array, start, length)

• array_intersect(array1, array2)

接受两个数组作为输入参数，并返回一个包含两个数组交集的新数组

• slice(array, start, length)

array：要操作的数组；

start：切片的起始位置(从1开始)；

length：要提取的长度

• map_concat(map<K,V> map1, map<K,V> map2)

将两个 Map 类型的列合并成一个新的 Map 类型的列，如果有重复的键，则保留 map1 中的值。

• map_filter(map<K, V> map, function<key, value> -> boolean condition)

接受两个参数：第一个是待过滤的Map；第二个是Lambda表达式，用于指定过滤条件。

-- 过滤出所有值大于 2 的键值对
select map_filter(kv_map, (k, v) -> v > 2)

窗口函数

Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

1、窗口函数

函数类别	窗口函数	含义
聚合函数
排序函数	row_number	每个分组内，为每行分配一个从1开始的递增序列号，不重复
排序函数	rank	每个分组内，为每行分配一个从1开始的递增序列号，考虑重复，挤占后续位置
排序函数	dense_rank	每个分组内，为每行分配一个从1开始的递增序列号，考虑重复，不挤占后续位置
排序函数	lag(col,N,default)	按照分组排序，显示窗口内第前N行的col值。
排序函数	lead(col,N,default)	按照分组排序，显示窗口内第后N行的col值。N为往下第 n 行（可选，默认为1），default为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL)
排序函数	first_value(col,false)	按照分组排序，显示排序第一的col值。第二个参数默认为false，如果设为true，则返回窗口中第一个非空值
排序函数	last_value(col,false)	按照分组排序，显示截止到该记录排序最后的col值。第二个参数默认为false，如果设为true，则返回窗口中最后一个非空值
	ntile()	将一个有序的数据集划分为多个桶(bucket)，并为每行分配一个桶号。如果分桶数不均匀，默认增加第一个桶的分布。

• sum+窗口函数

sum(…) over( )：对表中所有行求和；

sum(…) over( order by … ) ：连续累加求和；

sum(…) over( partition by… ) ：同组内所有行求和；

sum(…) over( partition by… order by … )：在每个分组内，连续累加求和

2、窗口定义

① PARTITION BY …ORDER BY

把数据分桶然后桶内排序，排好了序才能很好的定位出你需要向前或者向后取哪些数据来参与计算。

② Window_expression窗口表达式定义了窗口函数操作作用的范围，关键字是rows | range between … and …，包括下面这几个选项：

preceding：往前
	n preceding：往前n行/n值
following：往后
	n following：往后n行/n值
current row：当前行
unbounded：边界
	unbounded preceding 表示从前面的起点
	unbounded following：表示到后面的终点

• ROWS BETWEEN

按照距离来取

rows BETWEEN UNBOUNDED PRECEDING AND CURRENT row：从第一行到当前行（默认）
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING：代表取前面两条和后面两条数据参与计算，比如计算前后2天内的移动平均就可以这样算

• RANGE BETWEEN

当前值为锚点进行计算

RANGE BETWEEN 20 PRECEDING AND 10 FOLLOWING：当前值为50的话就去前后的值在30到60之间的数据

应用案例

行列转换

行转列：collect_list+group by

列转行：explode+lateral view

Hive的行列转换（行转多列、多列转行、行转单列、单列转行）

SQL进阶技巧：如何不使用union all进行行转列？【三种方法实现】

对NULL值处理

• 算术运算、比较运算对包含null的运算结果也为null
• 聚合函数（sum、count、avg、max、min）会忽略null

hive 中的max, min,first,sum,avg,count,比较,运算对null的处理

• 使用not in或不等于时，NULL值都不会进入该条件内而过滤掉，因此想保留为null一般会使用coalesce(xx,‘’)将空值转化为空字符串。

HIVESQL IN 和 NOT IN;

Hive的in与not in 值中有null的时候注意事项,join where条件等问题;

• concat拼接字符串遇到空值，结果会是空

json-map间转化

① 解析字符串，用正则匹配，然后用str_to_map函数

select str_to_map(regexp_replace(regexp_replace(regexp_replace(extinfo_new,'"',''),'\\{',''),'\\}',''),',',':') maps
from(
        select '{"versionId":"24140183","distributeCount":"1","appId":"2572","versionCode":"12289","instanceIp":"10.133.86.157","filterCount":"0"}' extinfo_new
)t
-- 结果：{"appId":"2572","distributeCount":"1","filterCount":"0","instanceIp":"10.133.86.157","versionCode":"12289","versionId":"24140183"}

② 自定义UDF

去重

distinct、group by、row_number()

hive sql 的三种去重方法总结

代码注意事项

① or不加括号，有可能带来灾难性的后果

# 在20240202的广告id是2或者广告位是8
select xx 
from dual
where dayno = 20240202
	and (ad_id=2 or pos_id = 8)
#取日期是20240202广告id是2，或者广告位是8的数据
select xx 
from dual
where dayno = 20240202
	and ad_id=2 or pos_id = 8