hive HQL 常用操作

资料

https://blog.csdn.net/u013411339/article/details/120795941

远程连接hive

https://blog.csdn.net/qq_44065303/article/details/105820435

服务端要开启 hiveserver2

建表方式

-- 1.正常建表
-- 建表后无数据
create table tableName(col1 int,col2 int);

-- 2.查询建表 as
-- 建表后有数据，数据为查询结果
-- 并不会带原表的分区（分区丢失），包扣一些字段的约束等
create table tableName as select * from tableA;

-- 3.结构建表 like
-- 建表后无数据，表结构从 tableA 中复制
create table tableName like tableA;

函数查询

-- 查看内置函数
SHOW FUNCTIONS;

-- 查看month相关的函数
SHOW FUNCTIONS LIKE '*month*';

-- 查看函数用法
DESC FUNCTION function_name;

-- 查看 add_months 函数的详细说明并举例
DESC FUNCTION EXTENDED add_months;

CTE

-- 公用表表达式
-- 创建一个临时结果集
with tableName as (select * from tableB)
select * from tableName;

时间转换函数

语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳
hive> select unix_timestamp() from tableName;
1616906976


语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
hive> select from_unixtime(1616906976,'yyyyMMdd') from tableName;
20210328

语法: unix_timestamp(string date)
返回值: bigint
说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15') from tableName;
1615184475

语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
hive>  select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName;
1615184475

语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
hive> select to_date('2021-03-28 14:03:01') from tableName;
2021-03-28

语法: year(string date)
返回值: int
说明: 返回日期中的年。
hive> select year('2021-03-28 10:03:01') from tableName;
2021
hive> select year('2021-03-28') from tableName;
2021

日期函数

语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
hive> select datediff('2020-12-08','2012-05-09') from tableName;
213

语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
hive> select date_add('2020-12-08',10) from tableName;
2020-12-18

语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。
hive> select date_sub('2020-12-08',10) from tableName;
2020-11-28

窗口函数

窗口聚合函数

sum

-- 共4种用法
sum(...) over() -- 相当于对全表进行求和
sum(...) over(order by ...) -- 连续累积求和，排序，不分组
sum(...) over(partition by ...) -- 同组内所行求和，只分组不排序
sum(...) over(partition by ... order by ...) -- 在每个分组内，连续累积求和，分组+排序

partition by

order by

窗口表达式

窗口表达式提供了一种控制行范围的能力如往前，往后n行

可以写多个列

-- 默认从第一行到当前行
sum(...) over(partition by ... order by ...) 
sum(...) over(partition by ... order by ... , ...)  -- 按多列分区
sum(...) over(partition by ... order by ... rows between unbounded preceding and current row) 

-- 向前3行至当前行
sum(...) over(partition by ... order by ... rows between 3 preceding and current row) 

-- 向前3行,向后1行
sum(...) over(partition by ... order by ... rows between 3 preceding and 1 following) 

-- 当前行到最后一行
sum(...) over(partition by ... order by ... rows between current row and unbounded following) 

-- 第一行至最后一行，即分组内所有行
sum(...) over(partition by ... order by ... rows between unbounded preceding and unbounded following) 

窗口排序函数

-- row_number家族，在每个分组中，为每行分配一个从1开始的唯一序号
row_number -- 不考虑重复
rank -- 考虑重复，挤占后续位置
dense_rank -- 考虑重复，不挤占后续位置

-- 窗口排序函数 ntile
将每个分组内的数据分为指定的若干个桶内，并且为每一个桶分配一个桶编号

select cookieid,createtime,pv
ntile(3) over(partition by cookieid order by createtime)
from website_pv_info
order by cookieid,createtime;

抽样函数

随机抽样 random

随机，但是速度慢

select * from website_pv_info order by rand() limit 2;
select * from website_pv_info distribute by rand() sort by rand() limit 2;

block 抽样

速度快，但是不随机

-- 按行数抽样
select * from website_pv_info tablesample (1 rows);
-- 根据数据大小百分比抽样
select * from website_pv_info tablesample (50 percent);
-- 根据数据大小抽样，支持b,k,m,g
select * from website_pv_info tablesample (1B);

bucket table 基于分桶表抽样

速度快+随机

tablesample(BUCKET x OUT OF y [ON colname])

-- 根据整行数据进行抽样
select  * from  website_pv_info tablesample ( bucket 1 out of 2 on rand());

类型转换

-- CAST可以转换的类型
-- 1．隐式类型转换规则如下
-- （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT。
-- （2）所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
-- （3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
-- （4）BOOLEAN 类型不可以转换为任何其它的类型。
CAST('1' AS INT) -- 可以正常执行
CAST('X' AS INT) -- 执行失败返回 NULL

concat 函数

-- CONCAT 函数用于将多个字符串连接成一个字符串。
-- CONCAT(str1,str2,…)     
SELECT CONCAT(id,',',name) AS con FROM info LIMIT 1;返回结果为
+----------+
| con      |
+----------+
| 1,BioCyc |
+----------+

-- CONCAT_WS() 指定参数之间的分隔符
SELECT CONCAT_WS('_',id,name) AS con_ws FROM info LIMIT 1;
+----------+
| con_ws   |
+----------+
| 1_BioCyc |
+----------+

case when

-- 简单 case
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
-- 搜索 case
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END

UDTF（explode）和侧视图

-- explode 
-- 接收 array 和 map 转换成多行
select explode(`array`(1,2,3,4,5));
-- Result:
-- col
-- 1
-- 2
-- 3
-- 4
-- 5
select explode(`map`("name","xxx",'sex','男','age','13'));
-- Result:
-- key     value
-- name    xxx
-- sex     男
-- age     13

-- 侧视图结合 explode
-- 侧视图语法
select ... from tableA lateral view UDTF(xxx) 表别名 as 列别名1,列别名2,列别名3;

行列转换

-- 多行转多列
-- 利用case when
-- 原表
-- students.id     students.name   students.subject        students.score
-- 1       			小明    			语文    					87
-- 2       			张三    			语文    					27
-- 3       			王五    			语文    					69
-- 4       			李四    			语文    					99
-- 5       			小明    			数学    					86
-- 6       			马六    			数学    					33
-- 7       			李四    			数学    					44
-- 8       			小红    			数学    					50
-- 转换后
-- name        chinese 	   math
-- 小明    		87        86
-- 小红    		0         50
-- 张三    		27        0
-- 李四    		99        44
-- 王五    		69        0
-- 马六    		0         33

select name,
max(case subject when '语文' then score else 0 end) chinese,
max(case subject when '数学' then score else 0 end) math
from students group by name;

-- 多行转单列
-- 转换前
-- cookie.cookieid cookie.createtime       cookie.pv
-- cookie1 2015-04-11      5
-- cookie1 2015-04-12      7
-- cookie1 2015-04-10      1
-- cookie1 2015-04-13      3
-- cookie1 2015-04-14      2
-- cookie1 2015-04-15      4
-- cookie1 2015-04-16      4
-- cookie2 2015-04-10      2
-- cookie2 2015-04-11      3
-- cookie2 2015-04-12      5
-- cookie2 2015-04-13      6
-- cookie2 2015-04-14      3
-- cookie2 2015-04-15      9
-- cookie2 2015-04-16      7
-- 转换后
-- cookieid        createtimes     pvs
-- cookie1 ["2015-04-10","2015-04-11","2015-04-12","2015-04-13","2015-04-14","2015-04-15","2015-04-16"]    [1,5,7,3,2,4,4]
-- cookie2 ["2015-04-10","2015-04-11","2015-04-12","2015-04-13","2015-04-14","2015-04-15","2015-04-16"]    [2,3,5,6,3,9,7]


select cookieid,collect_list(createtime) createtimes,collect_list(pv) pvs from cookie group by cookieid;
-- 其他函数
COLLECT_LIST(col) -- 多行合并成一行，不去重
COLLECT_SET(col) -- 多行合并成一行，将某字段的值进行去重汇总
CONCAT([string|col],[string|col],[string|col],....) -- 返回输入字符串连接后的结果，支持任意个输入字符串
CONCAT_WS('分隔符',[array|map]) -- 指定分隔符


-- 多列转多行
-- 主要使用 union 关键字
-- 转换前
-- rows2cols.col1  rows2cols.col2  rows2cols.col3  rows2cols.col4
-- a       1       2       3
-- b       4       5       6
-- 转换后
-- _u1.col1        _u1.col2        _u1.col3
-- a       c       1
-- a       d       1
-- a       e       1
-- b       c       4
-- b       d       4
-- b       e       4

select col1,'c' as col2,col2 as col3 from rows2cols
union all
select col1,'d' as col2,col2 as col3 from rows2cols
union all
select col1,'e' as col2,col2 as col3 from rows2cols;

-- 单列转多行
-- 转换前
-- aihao.id        aihao.name      aihao.age       aihao.favors
-- 1            huangbo                 45      a-c-d-f
-- 2            xuzheng                 36      b-c-d-e
-- 3            huanglei                41      c-d-e
-- 4            liushishi               22      a-d-e
-- 5            liudehua                39      e-f-d
-- 6            liuyifei                35      a-d-e
-- 转换后
-- id      name    age     favorslist
-- 1       huangbo 45      a
-- 1       huangbo 45      c
-- 1       huangbo 45      d
-- 1       huangbo 45      f
-- 2       xuzheng 36      b
-- 2       xuzheng 36      c
-- 2       xuzheng 36      d
-- 2       xuzheng 36      e
-- 3       huanglei        41      c
-- 3       huanglei        41      d
-- 3       huanglei        41      e
-- 4       liushishi       22      a
-- 4       liushishi       22      d
-- 4       liushishi       22      e
-- 5       liudehua        39      e
-- 5       liudehua        39      f
-- 5       liudehua        39      d
-- 6       liuyifei        35      a
-- 6       liuyifei        35      d
-- 6       liuyifei        35      e

select id,name,age,t.col1 as favorsList from aihao lateral VIEW explode(split(favors,'-')) t as col1;

-- lateral VIEW + explode

常见案例

查看前百分之N

/*
 NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型
 ntile(n)和where sorter = m 构成 n/m，如：ntile（2）和where sorted = 1 构成显示所有列的1/2
 20% = 1/5 ==> ntile(5),where sorted = 1
 */
SELECT *
FROM (
         SELECT name, orderdate, cost, NTILE(5) OVER (ORDER BY orderdate DESC) sorted
         FROM business
     ) t
WHERE sorted = 1;

TOPN 问题

select * from (
	select ROW_NUMBER() over(partition by ... order by ... DESC) as rank from tableName 
) t where t.rank < 3;

与前一天相比

LAG(列名,前几行,'默认值')
LAG(name, 1) 
LAG(name, 1,'default')
-- 表示取前一条记录的name的值。
-- LEAD()函数与此类似，不过它是查询某字段的后N条记录的值。

连续登录/连续问题

-- 原数据
1,2020-01-01
1,2020-01-02
1,2020-01-07
1,2020-01-08
1,2020-01-09
1,2020-01-10
2,2020-01-01
2,2020-01-02
2,2020-01-03
2,2020-01-04
3,2020-01-02
3,2020-01-03
3,2020-01-04

-- 两种方案
-- 1.自连接，构建笛卡尔积
-- 2. 窗口函数

-- 自连接
-- 两天的连续登录问题使用自连接比较好做，大于2天不适合用自连接
-- 1.自连接
select a.user_id a_id,a.login_date a_dt,
       b.user_id b_id,b.login_date b_dt
from login_log a,login_log b;

-- 2.查找用户id相同并且时间差为1的记录
select *
from (
     select a.user_id a_id,a.login_date a_dt,
            b.user_id b_id,b.login_date b_dt
     from login_log a,login_log b
) t where t.a_id=t.b_id and datediff(t.a_dt,t.b_dt)=1;
-- 提取id，并去重
select distinct t1.a_id
from (
    select *
    from (
         select a.user_id a_id,a.login_date a_dt,
                b.user_id b_id,b.login_date b_dt
         from login_log a,login_log b
    ) t where t.a_id=t.b_id and datediff(t.a_dt,t.b_dt)=1) t1;


-- 窗口函数
-- 适合于连续N天的问题

select distinct user_id from (
     select user_id,login_date,
            date_add(login_date,4-1) as nextday,
            lead(login_date,4-1) over (partition by user_id order by login_date) as nextlogin
     from login_log
) t where nextday = nextlogin;

-- nextday，手动算出 N 天后的时间
-- nextlogin，向下取 N 行进行验证

级联/累加求和

-- 编写Hive的HQL语句求出每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
-- 原表
usr,mon,num
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,11
-- 两种方法
-- 1.group by 加自连接
-- 2.窗口函数

-- 1.group by 加自连接
-- 过于复杂，不写了
-- https://www.bilibili.com/video/BV1L5411u7ae?p=126

-- 方法2
-- 窗口函数

-- 关键点是
sum(num) over (partition by usr order by mon) accPV

with t as (
    select usr,mon,num,
       row_number() over (partition by usr,mon order by num DESC) top,
       sum(num) over (partition by usr order by mon) accPV
    from fangwen
)select usr,mon,num,accPV from t where top=1;