Hive Sql 大全
本文根本涵盖了 Hive 日常应用的所有 SQL，因为 SQL 太多，所以将 SQL 进行了如下分类：
一、DDL 语句（数据定义语句）：
对数据库的操作：蕴含创立、批改数据库
对数据表的操作：分为外部表及内部表，分区表和分桶表
二、DQL 语句（数据查问语句）：
单表查问、关联查问
hive 函数：蕴含聚合函数，条件函数，日期函数，字符串函数等
行转列及列转行：lateral view 与 explode 以及 reflect
窗口函数与剖析函数
其余一些窗口函数文章首发于公众号【五分钟学大数据】，大数据畛域原创技术号，每周更新大数据技术文及面试真题解析，关注后可支付精心制作大数据面试宝典！
hive 的 DDL 语法
对数据库的操作
- 创立数据库:
create database if not exists myhive;
阐明：hive 的表寄存地位模式是由 hive-site.xml 当中的一个属性指定的 :hive.metastore.warehouse.dir
创立数据库并指定 hdfs 存储地位 :
create database myhive2 location '/myhive2';-
批改数据库:
alter database myhive2 set dbproperties('createtime'='20210329');阐明：能够应用 alter database 命令来批改数据库的一些属性。然而数据库的元数据信息是不可更改的，包含数据库的名称以及数据库所在的地位
- 查看数据库详细信息
查看数据库根本信息
hive (myhive)> desc database myhive2;
查看数据库更多详细信息
hive (myhive)> desc database extended myhive2;-
删除数据库
删除一个空数据库，如果数据库上面有数据表，那么就会报错 drop database myhive2;
强制删除数据库，蕴含数据库上面的表一起删除
drop database myhive cascade;
### 对数据表的操作
#### 对治理表 (外部表) 的操作:
- 建外部表:
hive (myhive)> use myhive; — 应用 myhive 数据库
hive (myhive)> create table stu(id int,name string);
hive (myhive)> insert into stu values (1,”zhangsan”);
hive (myhive)> insert into stu values (1,”zhangsan”),(2,”lisi”); — 一次插入多条数据
hive (myhive)> select * from stu;
- hive 建表时候的字段类型:
| ** 分类 ** | ** 类型 ** | ** 形容 ** | ** 字面量示例 ** |
| -------- | ------------------------------------------ | ---------------------------------------------- | ------------------------------------------------------------ |
| 原始类型 | BOOLEAN | true/false | TRUE |
| | TINYINT | 1 字节的有符号整数 -128~127 | 1Y |
| | SMALLINT | 2 个字节的有符号整数，-32768~32767 | 1S |
| | **INT** | 4 个字节的带符号整数 | 1 |
| | BIGINT | 8 字节带符号整数 | 1L |
| | FLOAT | 4 字节单精度浮点数 1.0 | |
| | DOUBLE | 8 字节双精度浮点数 | 1.0 |
| | DEICIMAL | 任意精度的带符号小数 | 1.0 |
| | **STRING** | 字符串，变长 |“a”,’b’|
| | VARCHAR | 变长字符串 |“a”,’b’|
| | CHAR | 固定长度字符串 |“a”,’b’|
| | BINARY | 字节数组 | 无奈示意 |
| | TIMESTAMP | 工夫戳，毫秒值精度 | 122327493795 |
| | **DATE** | 日期 |‘2016-03-29’|
| | INTERVAL| 工夫频率距离 | |
| 简单类型 | ARRAY | 有序的的同类型的汇合 | array(1,2) |
| | MAP | key-value,key 必须为原始类型，value 能够任意类型 | map(‘a’,1,’b’,2) |
| | STRUCT | 字段汇合, 类型能够不同 | struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0) |
| | UNION | 在无限取值范畴内的一个值 | create_union(1,’a’,63) |
> ** 对 decimal 类型简略解释下 **：用法：decimal(11,2) 代表最多有 11 位数字，其中后 2 位是小数，整数局部是 9 位；如果整数局部超过 9 位，则这个字段就会变成 null；如果小数局部有余 2 位，则前面用 0 补齐两位，如果小数局部超过两位，则超出局部四舍五入
也可间接写 decimal，前面不指定位数，默认是 decimal(10,0) 整数 10 位，没有小数
- 创立表并指定字段之间的分隔符
create table if not exists stu2(id int ,name string) row format delimited fields terminated by ‘\t’ stored as textfile location ‘/user/stu2’;
> row format delimited fields terminated by '\t' 指定字段分隔符，默认分隔符为 '\001'
stored as 指定存储格局
location 指定存储地位
- 依据查问后果创立表create table stu3 as select * from stu2;
- 依据曾经存在的表构造创立表create table stu4 like stu2;
- 查问表的构造只查问表内字段及属性
desc stu2;
具体查问
desc formatted stu2;
- 查问创立表的语句show create table stu2;
#### 对外部表操作
> 内部表因为是指定其余的 hdfs 门路的数据加载到表当中来，所以 hive 表会认为本人不齐全独占这份数据，所以删除 hive 表的时候，数据依然寄存在 hdfs 当中，不会删掉，只会删除表的元数据
- 构建内部表create external table student (s_id string,s_name string) row format delimited fields terminated by ‘\t’;
- 从本地文件系统向表中加载数据追加操作
load data local inpath ‘/export/servers/hivedatas/student.csv’ into table student;
笼罩操作
load data local inpath ‘/export/servers/hivedatas/student.csv’ overwrite into table student;
- 从 hdfs 文件系统向表中加载数据load data inpath ‘/hivedatas/techer.csv’ into table techer;
加载数据到指定分区
load data inpath ‘/hivedatas/techer.csv’ into table techer partition(cur_date=20201210);
> - ** 留神 **：1. 应用 load data local 示意从本地文件系统加载，文件会拷贝到 hdfs 上
2. 应用 load data 示意从 hdfs 文件系统加载，文件会间接挪动到 hive 相干目录下，留神不是拷贝过来，因为 hive 认为 hdfs 文件曾经有 3 正本了，没必要再次拷贝了
3. 如果表是分区表，load 时不指定分区会报错
4. 如果加载雷同文件名的文件，会被主动重命名
#### 对分区表的操作
- 创立分区表的语法create table score(s_id string, s_score int) partitioned by (month string);
- 创立一个表带多个分区create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);
> ** 留神：hive 表创立的时候能够用 location 指定一个文件或者文件夹，当指定文件夹时，hive 会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错
当表是分区表时，比方 partitioned by (day string)，则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123
这种格局，而后应用：msck repair table score; 修复表构造，胜利之后即可看到数据曾经全副加载到表当中去了 **
- 加载数据到一个分区的表中load data local inpath ‘/export/servers/hivedatas/score.csv’ into table score partition (month=’201806′);
- 加载数据到一个多分区的表中去load data local inpath ‘/export/servers/hivedatas/score.csv’ into table score2 partition(year=’2018′,month=’06’,day=’01’);
- 查看分区show partitions score;
- 增加一个分区alter table score add partition(month=’201805′);
- 同时增加多个分区alter table score add partition(month=’201804′) partition(month = ‘201803’);
> 留神：增加分区之后就能够在 hdfs 文件系统当中看到表上面多了一个文件夹
- 删除分区alter table score drop partition(month = ‘201806’);
#### 对分桶表操作
> 将数据依照指定的字段进行分成多个桶中去，就是依照分桶字段进行哈希划分到多个文件当中去
分区就是分文件夹，分桶就是分文件
> 分桶长处：1. 进步 join 查问效率
2. 进步抽样效率
- 开启 hive 的捅表性能set hive.enforce.bucketing=true;
- 设置 reduce 的个数set mapreduce.job.reduces=3;
- 创立桶表create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;
> 桶表的数据加载：因为桶表的数据加载通过 hdfs dfs -put 文件或者通过 load data 均不能够，只能通过 insert overwrite 进行加载
所以把文件加载到桶表中，须要先创立一般表，并通过 insert overwrite 的形式将一般表的数据通过查问的形式加载到桶表当中去
- 通过 insert overwrite 给桶表中加载数据insert overwrite table course select * from course_common cluster by(c_id); — 最初指定桶字段
#### 批改表和删除表
- 批改表名称alter table old_table_name rename to new_table_name;
- 减少 / 批改列信息查问表构造
desc score5;
增加列
alter table score5 add columns (mycol string, mysco string);
更新列
alter table score5 change column mysco mysconew int;
- 删除表操作drop table score5;
- 清空表操作truncate table score6;
阐明：只能清空治理表，也就是外部表；清空内部表，会产生谬误
> ** 留神：truncate 和 drop：如果 hdfs 开启了回收站，drop 删除的表数据是能够从回收站复原的，表构造复原不了，须要本人从新创立；truncate 清空的表是不进回收站的，所以无奈复原 truncate 清空的表
所以 truncate 肯定慎用，一旦清空将无力回天 **
#### 向 hive 表中加载数据
- 间接向分区表中插入数据insert into table score partition(month =’201807′) values (‘001′,’002′,’100’);
- 通过 load 形式加载数据load data local inpath ‘/export/servers/hivedatas/score.csv’ overwrite into table score partition(month=’201806′);
- 通过查问形式加载数据insert overwrite table score2 partition(month = ‘201806’) select s_id,c_id,s_score from score1;
- 查问语句中创立表并加载数据create table score2 as select * from score1;
- 在创立表是通过 location 指定加载数据的门路create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ‘,’ location ‘/myscore’;
- export 导出与 import 导入 hive 表数据（外部表操作）create table techer2 like techer; – 根据已有表构造创立表
export table techer to ‘/export/techer’;
import table techer2 from ‘/export/techer’;
#### hive 表中数据导出
- insert 导出将查问的后果导出到本地
insert overwrite local directory ‘/export/servers/exporthive’ select * from score;
将查问的后果格式化导出到本地
insert overwrite local directory ‘/export/servers/exporthive’ row format delimited fields terminated by ‘\t’ collection items terminated by ‘#’ select * from student;
将查问的后果导出到 HDFS 上(没有 local)
insert overwrite directory ‘/export/servers/exporthive’ row format delimited fields terminated by ‘\t’ collection items terminated by ‘#’ select * from score;
- Hadoop 命令导出到本地dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;
- hive shell 命令导出根本语法：（hive -f/-e 执行语句或者脚本 > file）
hive -e “select * from myhive.score;” > /export/servers/exporthive/score.txt
hive -f export.sh > /export/servers/exporthive/score.txt
- export 导出到 HDFS 上export table score to ‘/export/exporthive/score’;
## hive 的 DQL 查问语法
### 单表查问SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
> 留神：1、order by 会对输出做全局排序，因而只有一个 reducer，会导致当输出规模较大时，须要较长的计算工夫。2、sort by 不是全局排序，其在数据进入 reducer 前实现排序。因而，如果用 sort by 进行排序，并且设置 mapred.reduce.tasks>1，则 sort by 只保障每个 reducer 的输入有序，不保障全局有序。3、distribute by(字段)依据指定的字段将数据分到不同的 reducer，且散发算法是 hash 散列。4、Cluster by(字段) 除了具备 Distribute by 的性能外，还会对该字段进行排序。因而，如果分桶和 sort 字段是同一个时，此时，cluster by = distribute by + sort by
- WHERE 语句select * from score where s_score < 60;
> 留神：小于某个值是不蕴含 null 的，如上查问后果是把 s_score 为 null 的行剔除的
- GROUP BY 分组select s_id ,avg(s_score) from score group by s_id;
分组后对数据进行筛选，应用 having
select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
> 留神：如果应用 group by 分组，则 select 前面只能写分组的字段或者聚合函数
where 和 having 区别：1 having 是在 group by 分完组之后再对数据进行筛选，所以 having 要筛选的字段只能是分组字段或者聚合函数
2 where 是从数据表中的字段间接进行的筛选的，所以不能跟在 gruop
by 前面，也不能应用聚合函数
- join 连贯INNER JOIN 内连贯：只有进行连贯的两个表中都存在与连贯条件相匹配的数据才会被保留下来
select * from techer t [inner] join course c on t.t_id = c.t_id; — inner 可省略
LEFT OUTER JOIN 左外连贯：右边所有数据会被返回，左边符合条件的被返回
select * from techer t left join course c on t.t_id = c.t_id; — outer 可省略
RIGHT OUTER JOIN 右外连贯：左边所有数据会被返回，右边符合条件的被返回、
select * from techer t right join course c on t.t_id = c.t_id;
FULL OUTER JOIN 满外 (全外) 连贯: 将会返回所有表中符合条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就应用 NULL 值代替。
SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;
> 注：1. hive2 版本曾经反对不等值连贯，就是 join on 条件前面能够应用大于小于符号了; 并且也反对 join on 条件后跟 or (早前版本 on 后只反对 = 和 and，不反对 \> \< 和 or)
2. 如 hive 执行引擎应用 MapReduce，一个 join 就会启动一个 job，一条 sql 语句中如有多个 join，则会启动多个 job
> 留神：表之间用逗号 (,) 连贯和 inner join 是一样的
select * from table_a,table_b where table_a.id=table_b.id;
它们的执行效率没有区别，只是书写形式不同，用逗号是 sql 89 规范，join 是 sql 92 规范。用逗号连贯前面过滤条件用 where，用 join 连贯前面过滤条件是 on。- order by 排序全局排序，只会有一个 reduce
ASC（ascend）: 升序（默认）DESC（descend）: 降序
SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;
> 留神：order by 是全局排序，所以最初只有一个 reduce，也就是在一个节点执行，如果数据量太大，就会消耗较长时间
- sort by 部分排序每个 MapReduce 外部进行排序，对全局后果集来说不是排序。
设置 reduce 个数
set mapreduce.job.reduces=3;
查看设置 reduce 个数
set mapreduce.job.reduces;
查问问题依照问题降序排列
select * from score sort by s_score;
将查问后果导入到文件中（依照问题降序排列）
insert overwrite local directory ‘/export/servers/hivedatas/sort’ select * from score sort by s_score;
- distribute by 分区排序distribute by：相似 MR 中 partition，进行分区，联合 sort by 应用
设置 reduce 的个数，将咱们对应的 s_id 划分到对应的 reduce 当中去
set mapreduce.job.reduces=7;
通过 distribute by 进行数据的分区
select * from score distribute by s_id sort by s_score;
> 留神：Hive 要求 distribute by 语句要写在 sort by 语句之前
- cluster by当 distribute by 和 sort by 字段雷同时，能够应用 cluster by 形式.
cluster by 除了具备 distribute by 的性能外还兼具 sort by 的性能。然而排序只能是正序排序，不能指定排序规定为 ASC 或者 DESC。
以下两种写法等价
select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;
## Hive 函数
### 聚合函数hive 反对 count(),max(),min(),sum(),avg() 等罕用的聚合函数
> 留神：聚合操作时要留神 null 值
count(*) 蕴含 null 值，统计所有行数
count(id) 不蕴含 null 值
min 求最小值是不蕴含 null，除非所有值都是 null
avg 求平均值也是不蕴含 null
- 非空集合总体变量函数: var_pop语法: var_pop(col)
返回值: double
阐明: 统计后果集中 col 非空集合的总体变量（疏忽 null）
- 非空集合样本变量函数: var_samp语法: var_samp (col)
返回值: double
阐明: 统计后果集中 col 非空集合的样本变量（疏忽 null）
- 总体规范偏离函数: stddev_pop语法: stddev_pop(col)
返回值: double
阐明: 该函数计算总体规范偏离，并返回总体变量的平方根，其返回值与 VAR_POP 函数的平方根雷同
- 中位数函数: percentile语法: percentile(BIGINT col, p)
返回值: double
阐明: 求精确的第 pth 个百分位数，p 必须介于 0 和 1 之间，然而 col 字段目前只反对整数，不反对浮点数类型
### 关系运算反对：等值(=)、不等值(!= 或 <>)、小于(<)、小于等于(<=)、大于(>)、大于等于(>=)
空值判断(is null)、非空判断(is not null)
- LIKE 比拟: LIKE语法: A LIKE B
操作类型: strings
形容: 如果字符串 A 或者字符串 B 为 NULL，则返回 NULL；如果字符串 A 合乎表达式 B 的正则语法，则为 TRUE；否则为 FALSE。B 中字符”_”示意任意单个字符，而字符”%”示意任意数量的字符。
- JAVA 的 LIKE 操作: RLIKE语法: A RLIKE B
操作类型: strings
形容: 如果字符串 A 或者字符串 B 为 NULL，则返回 NULL；如果字符串 A 合乎 JAVA 正则表达式 B 的正则语法，则为 TRUE；否则为 FALSE。
- REGEXP 操作: REGEXP语法: A REGEXP B
操作类型: strings
形容: 性能与 RLIKE 雷同
示例：select 1 from tableName where ‘footbar’ REGEXP ‘^f.*r$’;
后果：1
### 数学运算反对所有数值类型：加(+)、减(-)、乘(*)、除(/)、取余(%)、位与(&)、位或(|)、位异或(^)、位取反(~)
### 逻辑运算反对：逻辑与(and)、逻辑或(or)、逻辑非(not)
### 数值运算
- 取整函数: round 语法: round(double a)
返回值: BIGINT
阐明: 返回 double 类型的整数值局部（遵循四舍五入）
示例：select round(3.1415926) from tableName;
后果：3
- 指定精度取整函数: round语法: round(double a, int d)
返回值: DOUBLE
阐明: 返回指定精度 d 的 double 类型
hive> select round(3.1415926,4) from tableName;
3.1416
- 向下取整函数: floor语法: floor(double a)
返回值: BIGINT
阐明: 返回等于或者小于该 double 变量的最大的整数
hive> select floor(3.641) from tableName;
3
- 向上取整函数: ceil语法: ceil(double a)
返回值: BIGINT
阐明: 返回等于或者大于该 double 变量的最小的整数
hive> select ceil(3.1415926) from tableName;
4
- 取随机数函数: rand语法: rand(),rand(int seed)
返回值: double
阐明: 返回一个 0 到 1 范畴内的随机数。如果指定种子 seed，则会等到一个稳固的随机数序列
hive> select rand() from tableName; — 每次执行此语句失去的后果都不同
0.5577432776034763
hive> select rand(100) ; — 只有指定种子，每次执行此语句失去的后果一样的
0.7220096548596434
- 天然指数函数: exp语法: exp(double a)
返回值: double
阐明: 返回自然对数 e 的 a 次方
hive> select exp(2) ;
7.38905609893065
- 以 10 为底对数函数: log10语法: log10(double a)
返回值: double
阐明: 返回以 10 为底的 a 的对数
hive> select log10(100) ;
2.0
> 此外还有：以 2 为底对数函数: log2()、对数函数: log()
- 幂运算函数: pow语法: pow(double a, double p)
返回值: double
阐明: 返回 a 的 p 次幂
hive> select pow(2,4) ;
16.0
- 开平方函数: sqrt语法: sqrt(double a)
返回值: double
阐明: 返回 a 的平方根
hive> select sqrt(16) ;
4.0
- 二进制函数: bin语法: bin(BIGINT a)
返回值: string
阐明: 返回 a 的二进制代码示意
hive> select bin(7) ;
111
> 十六进制函数: hex()、将十六进制转化为字符串函数: unhex()
进制转换函数: conv(bigint num, int from_base, int to_base) 阐明: 将数值 num 从 from_base 进制转化到 to_base 进制
> 此外还有很多数学函数：绝对值函数: abs()、正取余函数: pmod()、正弦函数: sin()、反正弦函数: asin()、余弦函数: cos()、反余弦函数: acos()、positive 函数: positive()、negative 函数: negative()
### 条件函数
- If 函数: if 语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
阐明: 当条件 testCondition 为 TRUE 时，返回 valueTrue；否则返回 valueFalseOrNull
hive> select if(1=2,100,200) ;
200
hive> select if(1=1,100,200) ;
100
- 非空查找函数: coalesce语法: coalesce(T v1, T v2, …)
返回值: T
阐明: 返回参数中的第一个非空值；如果所有值都为 NULL，那么返回 NULL
hive> select coalesce(null,’100′,’50’) ;
100
- 条件判断函数：case when (两种写法，其一)语法: case when a then b [when c then d]* [else e] end
返回值: T
阐明：如果 a 为 TRUE, 则返回 b；如果 c 为 TRUE，则返回 d；否则返回 e
hive> select case when 1=2 then ‘tom’ when 2=2 then ‘mary’ else ‘tim’ end from tableName;
mary
- 条件判断函数：case when (两种写法，其二)语法: case a when b then c [when d then e]* [else f] end
返回值: T
阐明：如果 a 等于 b，那么返回 c；如果 a 等于 d，那么返回 e；否则返回 f
hive> Select case 100 when 50 then ‘tom’ when 100 then ‘mary’ else ‘tim’ end from tableName;
mary
### 日期函数
> 注：以下 SQL 语句中的 from tableName 可去掉，不影响查问后果
- 1) 获取以后 UNIX 工夫戳函数: unix_timestamp
语法: unix_timestamp()
返回值: bigint
阐明: 取得以后时区的 UNIX 工夫戳
hive> select unix_timestamp() from tableName;
1616906976
- 2) UNIX 工夫戳转日期函数: from_unixtime
语法: from_unixtime(bigint unixtime[, string format])
返回值: string
阐明: 转化 UNIX 工夫戳（从 1970-01-01 00:00:00 UTC 到指定工夫的秒数）到以后时区的工夫格局
hive> select from_unixtime(1616906976,’yyyyMMdd’) from tableName;
20210328
- 3) 日期转 UNIX 工夫戳函数: unix_timestamp
语法: 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
- 4) 指定格局日期转 UNIX 工夫戳函数: unix_timestamp
语法: 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
- 5) 日期工夫转日期函数: to_date
语法: to_date(string timestamp)
返回值: string
阐明: 返回日期工夫字段中的日期局部。
hive> select to_date(‘2021-03-28 14:03:01’) from tableName;
2021-03-28
- 6) 日期转年函数: year
语法: 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
- 7) 日期转月函数: month
语法: month (string date)
返回值: int
阐明: 返回日期中的月份。
hive> select month(‘2020-12-28 12:03:01’) from tableName;
12
hive> select month(‘2021-03-08’) from tableName;
8
- 8) 日期转天函数: day
语法: day (string date)
返回值: int
阐明: 返回日期中的天。
hive> select day(‘2020-12-08 10:03:01’) from tableName;
8
hive> select day(‘2020-12-24’) from tableName;
24
- 9) 日期转小时函数: hour
语法: hour (string date)
返回值: int
阐明: 返回日期中的小时。
hive> select hour(‘2020-12-08 10:03:01’) from tableName;
10
- 10) 日期转分钟函数: minute
语法: minute (string date)
返回值: int
阐明: 返回日期中的分钟。
hive> select minute(‘2020-12-08 10:03:01’) from tableName;
3
- 11) 日期转秒函数: second
语法: second (string date)
返回值: int
阐明: 返回日期中的秒。
hive> select second(‘2020-12-08 10:03:01’) from tableName;
1
- 12) 日期转周函数: weekofyear
语法: weekofyear (string date)
返回值: int
阐明: 返回日期在以后的周数。
hive> select weekofyear(‘2020-12-08 10:03:01’) from tableName;
49
- 13) 日期比拟函数: datediff
语法: datediff(string enddate, string startdate)
返回值: int
阐明: 返回完结日期减去开始日期的天数。
hive> select datediff(‘2020-12-08′,’2012-05-09’) from tableName;
213
- 14) 日期减少函数: date_add
语法: date_add(string startdate, int days)
返回值: string
阐明: 返回开始日期 startdate 减少 days 天后的日期。
hive> select date_add(‘2020-12-08’,10) from tableName;
2020-12-18
- 15) 日期缩小函数: date_sub
语法: date_sub (string startdate, int days)
返回值: string
阐明: 返回开始日期 startdate 缩小 days 天后的日期。
hive> select date_sub(‘2020-12-08’,10) from tableName;
2020-11-28
### 字符串函数
- 1) 字符串长度函数：length
语法: length(string A)
返回值: int
阐明：返回字符串 A 的长度
hive> select length(‘abcedfg’) from tableName;
7
- 2) 字符串反转函数：reverse
语法: reverse(string A)
返回值: string
阐明：返回字符串 A 的反转后果
hive> select reverse(‘abcedfg’) from tableName;
gfdecba
- 3) 字符串连贯函数：concat
语法: concat(string A, string B…)
返回值: string
阐明：返回输出字符串连贯后的后果，反对任意个输出字符串
hive> select concat(‘abc’,’def’,’gh’)from tableName;
abcdefgh
- 4) 带分隔符字符串连贯函数：concat_ws
语法: concat_ws(string SEP, string A, string B…)
返回值: string
阐明：返回输出字符串连贯后的后果，SEP 示意各个字符串间的分隔符
hive> select concat_ws(‘,’,’abc’,’def’,’gh’)from tableName;
abc,def,gh
- 5) 字符串截取函数：substr,substring
语法: substr(string A, int start),substring(string A, int start)
返回值: string
阐明：返回字符串 A 从 start 地位到结尾的字符串
hive> select substr(‘abcde’,3) from tableName;
cde
hive> select substring(‘abcde’,3) from tableName;
cde
hive> select substr(‘abcde’,-1) from tableName;（和 ORACLE 雷同）
e
- 6) 字符串截取函数：substr,substring
语法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
阐明：返回字符串 A 从 start 地位开始，长度为 len 的字符串
hive> select substr(‘abcde’,3,2) from tableName;
cd
hive> select substring(‘abcde’,3,2) from tableName;
cd
hive>select substring(‘abcde’,-2,2) from tableName;
de
- 7) 字符串转大写函数：upper,ucase
语法: upper(string A) ucase(string A)
返回值: string
阐明：返回字符串 A 的大写格局
hive> select upper(‘abSEd’) from tableName;
ABSED
hive> select ucase(‘abSEd’) from tableName;
ABSED
- 8) 字符串转小写函数：lower,lcase
语法: lower(string A) lcase(string A)
返回值: string
阐明：返回字符串 A 的小写格局
hive> select lower(‘abSEd’) from tableName;
absed
hive> select lcase(‘abSEd’) from tableName;
absed
- 9) 去空格函数：trim
语法: trim(string A)
返回值: string
阐明：去除字符串两边的空格
hive> select trim(‘ abc ‘) from tableName;
abc
- 10) 右边去空格函数：ltrim
语法: ltrim(string A)
返回值: string
阐明：去除字符串右边的空格
hive> select ltrim(‘ abc ‘) from tableName;
abc
- 11) 左边去空格函数：rtrim
语法: rtrim(string A)
返回值: string
阐明：去除字符串左边的空格
hive> select rtrim(‘ abc ‘) from tableName;
abc
- 12) 正则表达式替换函数：regexp\_replace
语法: regexp_replace(string A, string B, string C)
返回值: string
阐明：将字符串 A 中的合乎 java 正则表达式 B 的局部替换为 C。留神，在有些状况下要应用转义字符, 相似 oracle 中的 regexp_replace 函数。
hive> select regexp_replace(‘foobar’, ‘oo|ar’, ”) from tableName;
fb
- 13) 正则表达式解析函数：regexp\_extract
语法: regexp_extract(string subject, string pattern, int index)
返回值: string
阐明：将字符串 subject 依照 pattern 正则表达式的规定拆分，返回 index 指定的字符。
hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 1) from tableName;
the
hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) from tableName;
bar
hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 0) from tableName;
foothebar
strong> 留神，在有些状况下要应用转义字符，上面的等号要用双竖线本义，这是 java 正则表达式的规定。
select data_field,
regexp_extract(data_field,’.*?bgStart\=(1+)’,1) as aaa,
regexp_extract(data_field,’.*?contentLoaded_headStart\=(1+)’,1) as bbb,
regexp_extract(data_field,’.*?AppLoad2Req\=(1+)’,1) as ccc
from pt_nginx_loginlog_st
where pt = ‘2021-03-28’ limit 2;
- 14) URL 解析函数：parse\_url
语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
阐明：返回 URL 中指定的局部。partToExtract 的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
hive> select parse_url
(‘https://www.tableName.com/pat…’, ‘HOST’)
from tableName;
www.tableName.com
hive> select parse_url
(‘https://www.tableName.com/pat…’, ‘QUERY’, ‘k1’)
from tableName;
v1
- 15) json 解析函数：get\_json\_object
语法: get_json_object(string json_string, string path)
返回值: string
阐明：解析 json 的字符串 json_string, 返回 path 指定的内容。如果输出的 json 字符串有效，那么返回 NULL。
hive> select get_json_object(‘{“store”:{“fruit”:[{“weight”:8,”type”:”apple”},{“weight”:9,”type”:”pear”}], “bicycle”:{“price”:19.95,”color”:”red”} },”email”:”amy@only_for_json_udf_test.net”,”owner”:”amy”}’,’$.owner’) from tableName;
- 16) 空格字符串函数：space
语法: space(int n)
返回值: string
阐明：返回长度为 n 的字符串
hive> select space(10) from tableName;
hive> select length(space(10)) from tableName;
10
- 17) 反复字符串函数：repeat
语法: repeat(string str, int n)
返回值: string
阐明：返回反复 n 次后的 str 字符串
hive> select repeat(‘abc’,5) from tableName;
abcabcabcabcabc
- 18) 首字符 ascii 函数：ascii
语法: ascii(string str)
返回值: int
阐明：返回字符串 str 第一个字符的 ascii 码
hive> select ascii(‘abcde’) from tableName;
97
- 19) 左补足函数：lpad
语法: lpad(string str, int len, string pad)
返回值: string
阐明：将 str 进行用 pad 进行左补足到 len 位
hive> select lpad(‘abc’,10,’td’) from tableName;
tdtdtdtabc
留神：与 GP，ORACLE 不同，pad 不能默认
- 20) 右补足函数：rpad
语法: rpad(string str, int len, string pad)
返回值: string
阐明：将 str 进行用 pad 进行右补足到 len 位
hive> select rpad(‘abc’,10,’td’) from tableName;
abctdtdtdt
- 21) 宰割字符串函数: split
语法: split(string str, string pat)
返回值: array
阐明: 依照 pat 字符串宰割 str，会返回宰割后的字符串数组
hive> select split(‘abtcdtef’,’t’) from tableName;
[“ab”,”cd”,”ef”]
- 22) 汇合查找函数: find\_in\_set
语法: find_in_set(string str, string strList)
返回值: int
阐明: 返回 str 在 strlist 第一次呈现的地位，strlist 是用逗号宰割的字符串。如果没有找该 str 字符，则返回 0
hive> select find_in_set(‘ab’,’ef,ab,de’) from tableName;
2
hive> select find_in_set(‘at’,’ef,ab,de’) from tableName;
0
### 复合类型构建操作
- Map 类型构建: map
语法: map (key1, value1, key2, value2, …)
阐明：依据输出的 key 和 value 对构建 map 类型
hive> Create table mapTable as select map(‘100′,’tom’,’200′,’mary’) as t from tableName;
hive> describe mapTable;
t map<string ,string>
hive> select t from tableName;
{“100″:”tom”,”200″:”mary”}
- 2) Struct 类型构建: struct
语法: struct(val1, val2, val3, …)
阐明：依据输出的参数构建构造体 struct 类型
hive> create table struct_table as select struct(‘tom’,’mary’,’tim’) as t from tableName;
hive> describe struct_table;
t struct<col1:string ,col2:string,col3:string>
hive> select t from tableName;
{“col1″:”tom”,”col2″:”mary”,”col3″:”tim”}
- 3) array 类型构建: array
语法: array(val1, val2, …)
阐明：依据输出的参数构建数组 array 类型
hive> create table arr_table as select array(“tom”,”mary”,”tim”) as t from tableName;
hive> describe tableName;
t array<string>
hive> select t from tableName;
[“tom”,”mary”,”tim”]
### 简单类型拜访操作
- 1) array 类型拜访: A[n]
语法: A[n]
操作类型: A 为 array 类型，n 为 int 类型
阐明：返回数组 A 中的第 n 个变量值。数组的起始下标为 0。比方，A 是个值为 [‘foo’, ‘bar’] 的数组类型，那么 A[0]将返回 ’foo’, 而 A[1]将返回 ’bar’
hive> create table arr_table2 as select array(“tom”,”mary”,”tim”) as t
from tableName;
hive> select t[0],t[1] from arr_table2;
tom mary tim
- 2) map 类型拜访: M[key]
语法: M[key]
操作类型: M 为 map 类型，key 为 map 中的 key 值
阐明：返回 map 类型 M 中，key 值为指定值的 value 值。比方，M 是值为 {‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’} 的 map 类型，那么 M[‘all’]将会返回 ’foobar’
hive> Create table map_table2 as select map(‘100′,’tom’,’200′,’mary’) as t from tableName;
hive> select t[‘200’],t[‘100’] from map_table2;
mary tom
- 3) struct 类型拜访: S.x
语法: S.x
操作类型: S 为 struct 类型
阐明：返回构造体 S 中的 x 字段。比方，对于构造体 struct foobar {int foo, int bar}，foobar.foo 返回构造体中的 foo 字段
hive> create table str_table2 as select struct(‘tom’,’mary’,’tim’) as t from tableName;
hive> describe tableName;
t struct<col1:string ,col2:string,col3:string>
hive> select t.col1,t.col3 from str_table2;
tom tim
### 简单类型长度统计函数
- 1) Map 类型长度函数: size(Map<k .V>)
语法: size(Map<k .V>)
返回值: int
阐明: 返回 map 类型的长度
hive> select size(t) from map_table2;
2
- 2) array 类型长度函数: size(Array<T>)
语法: size(Array<T>)
返回值: int
阐明: 返回 array 类型的长度
hive> select size(t) from arr_table2;
4
- 3) 类型转换函数 ***
类型转换函数: cast
语法: cast(expr as <type>)
返回值: Expected “=” to follow “type”
阐明: 返回转换后的数据类型
hive> select cast(‘1’ as bigint) from tableName;
1
## hive 当中的 lateral view 与 explode 以及 reflect 和窗口函数
### 应用 explode 函数将 hive 表中的 Map 和 Array 字段数据进行拆分
lateral view 用于和 split、explode 等 UDTF 一起应用的，能将一行数据拆分成多行数据，在此基础上能够对拆分的数据进行聚合，lateral view 首先为原始表的每行调用 UDTF，UDTF 会把一行拆分成一行或者多行，lateral view 在把后果组合，产生一个反对别名表的虚构表。 其中 explode 还能够用于将 hive 一列中简单的 array 或者 map 构造拆分成多行
需要：当初有数据格式如下
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
字段之间应用 \t 宰割，需要将所有的 child 进行拆开成为一列
| mychild |
|---|
| child1 |
| child2 |
| child3 |
| child4 |
| child5 |
| child6 |
| child7 |
| child8 |
将 map 的 key 和 value 也进行拆开，成为如下后果
| mymapkey | mymapvalue |
|---|---|
| k1 | v1 |
| k2 | v2 |
| k3 | v3 |
| k4 | v4 |
- 1) 创立 hive 数据库
创立 hive 数据库
hive (default)> create database hive_explode;
hive (default)> use hive_explode;
- 2) 创立 hive 表，而后应用 explode 拆分 map 和 array
hive (hive_explode)> create table t3(name string,children array<string>,address Map<string,string>) row format delimited fields terminated by ‘\t’ collection items terminated by ‘,’ map keys terminated by ‘:’ stored as textFile;
- 3) 加载数据
node03 执行以下命令创立表数据文件
mkdir -p /export/servers/hivedatas/
cd /export/servers/hivedatas/
vim maparray内容如下:
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
hive 表当中加载数据
hive (hive_explode)> load data local inpath ‘/export/servers/hivedatas/maparray’ into table t3;
- 4) 应用 explode 将 hive 当中数据拆开
将 array 当中的数据拆离开
hive (hive_explode)> SELECT explode(children) AS myChild FROM t3;
将 map 当中的数据拆离开
hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;
### 应用 explode 拆分 json 字符串
需要: 需要：当初有一些数据格式如下：a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{“source”:”7fresh”,”monthSales”:4900,”userCount”:1900,”score”:”9.9″},{“source”:”jd”,”monthSales”:2090,”userCount”:78981,”score”:”9.8″},{“source”:”jdmart”,”monthSales”:6987,”userCount”:1600,”score”:”9.0″}]
其中字段与字段之间的分隔符是 |
咱们要解析失去所有的 monthSales 对应的值为以下这一列（行转列）4900
2090
6987
- 1) 创立 hive 表
hive (hive_explode)> create table explode_lateral_view
> (`area` string,
> `goods_id` string,
> `sale_info` string)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '|'
> STORED AS textfile;
- 2) 筹备数据并加载数据
筹备数据如下
cd /export/servers/hivedatas
vim explode_json
a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{“source”:”7fresh”,”monthSales”:4900,”userCount”:1900,”score”:”9.9″},{“source”:”jd”,”monthSales”:2090,”userCount”:78981,”score”:”9.8″},{“source”:”jdmart”,”monthSales”:6987,”userCount”:1600,”score”:”9.0″}]
加载数据到 hive 表当中去
hive (hive_explode)> load data local inpath ‘/export/servers/hivedatas/explode_json’ overwrite into table explode_lateral_view;
- 3) 应用 explode 拆分 Array
hive (hive_explode)> select explode(split(goods_id,’,’)) as goods_id from explode_lateral_view;
- 4) 应用 explode 拆解 Map
hive (hive_explode)> select explode(split(area,’,’)) as area from explode_lateral_view;
- 5) 拆解 json 字段
hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,’\[\{‘,”),’}]’,”),’},\{‘)) as sale_info from explode_lateral_view;
而后咱们想用 get_json_object 来获取 key 为 monthSales 的数据：
hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,’\[\{‘,”),’}]’,”),’},\{‘)),’$.monthSales’) as sale_info from explode_lateral_view;
而后挂了 FAILED: SemanticException [Error 10081]: UDTF’s are not supported outside the SELECT clause, nor nested in expressions
UDTF explode 不能写在别的函数内
如果你这么写，想查两个字段，select explode(split(area,’,’)) as area,good_id from explode_lateral_view;
会报错 FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF’s. Error encountered near token ‘good_id’
应用 UDTF 的时候，只反对一个字段，这时候就须要 LATERAL VIEW 出场了
### 配合 LATERAL VIEW 应用
配合 lateral view 查问多个字段
hive (hive_explode)> select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,’,’))goods as goods_id2;
其中 LATERAL VIEW explode(split(goods_id,’,’))goods 相当于一个虚构表，与原表 explode_lateral_view 笛卡尔积关联
也能够多重应用
hive (hive_explode)> select goods_id2,sale_info,area2
from explode_lateral_view
LATERAL VIEW explode(split(goods_id,','))goods as goods_id2
LATERAL VIEW explode(split(area,','))area as area2; 也是三个表笛卡尔积的后果
最终，咱们能够通过上面的句子，把这个 json 格局的一行数据，齐全转换成二维表的形式展示
hive (hive_explode)> select get_json_object(concat(‘{‘,sale_info_1,’}’),’$.source’) as source,get_json_object(concat(‘{‘,sale_info_1,’}’),’$.monthSales’) as monthSales,get_json_object(concat(‘{‘,sale_info_1,’}’),’$.userCount’) as monthSales,get_json_object(concat(‘{‘,sale_info_1,’}’),’$.score’) as monthSales from explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,’\[\{‘,”),’}]’,”),’},\{‘))sale_info as sale_info_1;
总结：Lateral View 通常和 UDTF 一起呈现，为了解决 UDTF 不容许在 select 字段的问题。Multiple Lateral View 能够实现相似笛卡尔乘积。Outer 关键字能够把不输入的 UDTF 的空后果，输入成 NULL，避免失落数据。### 行转列
相干参数阐明:
CONCAT(string A/col, string B/col…)：返回输出字符串连贯后的后果，反对任意个输出字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个非凡模式的 CONCAT()。第一个参数残余参数间的分隔符。分隔符能够是与残余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连贯的字符串之间;
COLLECT_SET(col)：函数只承受根本数据类型，它的次要作用是将某字段的值进行去重汇总，产生 array 类型字段。数据筹备:
| name | constellation | blood_type |
| ------ | ------------- | ---------- |
| 孙悟空 | 白羊座 | A |
| 老王 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 猪八戒 | 白羊座 | A |
| 凤姐 | 射手座 | A |
需要: 把星座和血型一样的人归类到一起。后果如下：射手座,A 老王 | 凤姐
白羊座,A 孙悟空 | 猪八戒
白羊座,B 宋宋
实现步骤:
- 1) 创立本地 constellation.txt，导入数据
node03 服务器执行以下命令创立文件，留神数据应用 \t 进行宰割
cd /export/servers/hivedatas
vim constellation.txt
数据如下:
孙悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
- 2) 创立 hive 表并导入数据
创立 hive 表并加载数据
hive (hive_explode)> create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
加载数据
hive (hive_explode)> load data local inpath ‘/export/servers/hivedatas/constellation.txt’ into table person_info;
- 3) 按需要查问数据
hive (hive_explode)> select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, "," , blood_type) base
from
person_info) t1
group by
t1.base;
### 列转行
所需函数:
EXPLODE(col)：将 hive 一列中简单的 array 或者 map 构造拆分成多行。 LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起应用，它可能将一列数据拆成多行数据，在此基础上能够对拆分后的数据进行聚合。数据筹备:
cd /export/servers/hivedatas
vim movie.txt
文件内容如下: 数据字段之间应用 \t 进行宰割
《疑犯追踪》悬疑, 动作, 科幻, 剧情
《Lie to me》悬疑, 警匪, 动作, 心理, 剧情
《战狼 2》和平, 动作, 劫难
需要: 将电影分类中的数组数据开展。后果如下：《疑犯追踪》悬疑
《疑犯追踪》动作
《疑犯追踪》科幻
《疑犯追踪》剧情
《Lie to me》悬疑
《Lie to me》警匪
《Lie to me》动作
《Lie to me》心理
《Lie to me》剧情
《战狼 2》和平
《战狼 2》动作
《战狼 2》劫难
实现步骤:
- 1) 创立 hive 表
create table movie_info(
movie string,
category array<string>) row format delimited fields terminated by “\t”
collection items terminated by “,”;
- 2) 加载数据
load data local inpath “/export/servers/hivedatas/movie.txt” into table movie_info;
- 3) 按需要查问数据
select
movie,
category_namefrom
movie_info lateral view explode(category) table_tmp as category_name;
### reflect 函数
reflect 函数能够反对在 sql 中调用 java 中的自带函数，秒杀所有 udf 函数。需要 1: 应用 java.lang.Math 当中的 Max 求两列中最大值
实现步骤:
- 1) 创立 hive 表
create table test_udf(col1 int,col2 int) row format delimited fields terminated by ‘,’;
- 2) 筹备数据并加载数据
cd /export/servers/hivedatas
vim test_udf
文件内容如下:
1,2
4,3
6,4
7,5
5,6
- 3) 加载数据
hive (hive_explode)> load data local inpath ‘/export/servers/hivedatas/test_udf’ overwrite into table test_udf;
- 4) 应用 java.lang.Math 当中的 Max 求两列当中的最大值
hive (hive_explode)> select reflect(“java.lang.Math”,”max”,col1,col2) from test_udf;
需要 2: 文件中不同的记录来执行不同的 java 的内置函数
实现步骤:
- 1) 创立 hive 表
hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ‘,’;
- 2) 筹备数据
cd /export/servers/hivedatas
vim test_udf2
文件内容如下:
java.lang.Math,min,1,2
java.lang.Math,max,2,3
- 3) 加载数据
hive (hive_explode)> load data local inpath ‘/export/servers/hivedatas/test_udf2’ overwrite into table test_udf2;
- 4) 执行查问
hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;
需要 3: 判断是否为数字
实现形式:
应用 apache commons 中的函数，commons 下的 jar 曾经蕴含在 hadoop 的 classpath 中，所以能够间接应用。select reflect(“org.apache.commons.lang.math.NumberUtils”,”isNumber”,”123″)
## 窗口函数与剖析函数
在 sql 中有一类函数叫做聚合函数, 例如 sum()、avg()、max()等等, 这类函数能够将多行数据依照规定汇集为一行, 一般来讲汇集后的行数是要少于汇集前的行数的。然而有时咱们想要既显示汇集前的数据, 又要显示汇集后的数据, 这时咱们便引入了窗口函数。窗口函数又叫 OLAP 函数 / 剖析函数，窗口函数兼具分组和排序功能。窗口函数最重要的关键字是 **partition by** 和 **order by。**
具体语法如下：**over (partition by xxx order by xxx)**
### sum、avg、min、max
筹备数据
建表语句:
create table test_t1(
cookieid string,
createtime string, –day
pv int
) row format delimited
fields terminated by ‘,’;
加载数据：
load data local inpath ‘/root/hivedata/test_t1.dat’ into table test_t1;
cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4
开启智能本地模式
SET hive.exec.mode.local.auto=true;
SUM 函数和窗口函数的配合应用：后果和 ORDER BY 相干, 默认为升序。select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1
from test_t1;
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from test_t1;
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as pv3
from test_t1;
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from test_t1;
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from test_t1;
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from test_t1;
pv1: 分组内从终点到以后行的 pv 累积，如，11 号的 pv1=10 号的 pv+11 号的 pv, 12 号 =10 号 +11 号 +12 号
pv2: 同 pv1
pv3: 分组内(cookie1) 所有的 pv 累加
pv4: 分组内以后行 + 往前 3 行，如，11 号 =10 号 +11 号，12 号 =10 号 +11 号 +12 号，
13 号 =10 号 +11 号 +12 号 +13 号，14 号 =11 号 +12 号 +13 号 +14 号pv5: 分组内以后行 + 往前 3 行 + 往后 1 行，如，14 号 =11 号 +12 号 +13 号 +14 号 +15 号 =5+7+3+2+4=21
pv6: 分组内以后行 + 往后所有行，如，13 号 =13 号 +14 号 +15 号 +16 号 =3+2+4+4=13，
14 号 =14 号 +15 号 +16 号 =2+4+4=10
如果不指定 rows between, 默认为从终点到以后行;
如果不指定 order by，则将分组内所有值累加;
要害是了解 rows between 含意, 也叫做 window 子句： preceding：往前
following：往后
current row：以后行
unbounded：终点
unbounded preceding 示意从后面的终点
unbounded following：示意到前面的起点
AVG，MIN，MAX，和 SUM 用法一样。### row_number、rank、dense_rank、ntile
筹备数据
cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4
cookie2,2020-04-10,2
cookie2,2020-04-11,3
cookie2,2020-04-12,5
cookie2,2020-04-13,6
cookie2,2020-04-14,3
cookie2,2020-04-15,9
cookie2,2020-04-16,7
CREATE TABLE test_t2 (
cookieid string,
createtime string, –day
pv INT
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
stored as textfile;
加载数据：
load data local inpath ‘/root/hivedata/test_t2.dat’ into table test_t2;
- ROW_NUMBER()应用
ROW_NUMBER()从 1 开始，依照程序，生成分组内记录的序列。SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
FROM test_t2;
- RANK 和 DENSE_RANK 应用
RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位。DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。SELECT
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM test_t2
WHERE cookieid = ‘cookie1’;
- NTILE
有时会有这样的需要: 如果数据排序后分为三局部，业务人员只关怀其中的一部分，如何将这两头的三分之一数据拿进去呢?NTILE 函数即能够满足。ntile 能够看成是：把有序的数据汇合平均分配到指定的数量（num）个桶中, 将桶号调配给每一行。如果不能平均分配，则优先调配较小编号的桶，并且各个桶中能放的行数最多相差 1。而后能够依据桶号，选取前或后 n 分之几的数据。数据会残缺展现进去，只是给相应的数据打标签；具体要取几分之几的数据，须要再嵌套一层依据标签取出。SELECT
cookieid,
createtime,
pv,
NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
NTILE(4) OVER(ORDER BY createtime) AS rn3
FROM test_t2
ORDER BY cookieid,createtime;
## 其余一些窗口函数
### lag,lead,first\_value,last\_value
- LAG
**LAG(col,n,DEFAULT) 用于统计窗口内往上第 n 行值 ** 第一个参数为列名，第二个参数为往上第 n 行（可选，默认为 1），第三个参数为默认值（当往上第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL）SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAG(createtime,1,’1970-01-01 00:00:00′) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
FROM test_t4;
last_1_time: 指定了往上第 1 行的值，default 为 ’1970-01-01 00:00:00′
cookie1 第一行，往上 1 行为 NULL, 因而取默认值 1970-01-01 00:00:00
cookie1 第三行，往上 1 行值为第二行值，2015-04-10 10:00:02
cookie1 第六行，往上 1 行值为第五行值，2015-04-10 10:50:01last_2_time: 指定了往上第 2 行的值，为指定默认值
cookie1 第一行，往上 2 行为 NULL
cookie1 第二行，往上 2 行为 NULL
cookie1 第四行，往上 2 行为第二行值，2015-04-10 10:00:02
cookie1 第七行，往上 2 行为第五行值，2015-04-10 10:50:01
- LEAD
与 LAG 相同
**LEAD(col,n,DEFAULT) 用于统计窗口内往下第 n 行值 **
第一个参数为列名，第二个参数为往下第 n 行（可选，默认为 1），第三个参数为默认值（当往下第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL）SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LEAD(createtime,1,’1970-01-01 00:00:00′) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time
FROM test_t4;
- FIRST_VALUE
取分组内排序后，截止到以后行，第一个值
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM test_t4;
- LAST_VALUE
取分组内排序后，截止到以后行，最初一个值
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM test_t4;
如果想要取分组内排序后最初一个值，则须要变通一下：SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2
FROM test_t4
ORDER BY cookieid,createtime;
** 特地留神 order by**
如果不指定 ORDER BY，则进行排序凌乱，会呈现谬误的后果
SELECT cookieid,
createtime,
url,
FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2
FROM test_t4;
### cume_dist,percent_rank
这两个序列剖析函数不是很罕用，** 留神：序列函数不反对 WINDOW 子句 **
- 数据筹备
d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
CREATE EXTERNAL TABLE test_t3 (
dept STRING,
userid string,
sal INT
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
stored as textfile;
加载数据：
load data local inpath ‘/root/hivedata/test_t3.dat’ into table test_t3;
------
- CUME_DIST 和 order byd 的排序程序有关系
CUME_DIST 小于等于以后值的行数 / 分组内总行数 order 默认程序 正序 升序
比方，统计小于等于以后薪水的人数，所占总人数的比例
SELECT
dept,
userid,
sal,
CUME_DIST() OVER(ORDER BY sal) AS rn1,
CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM test_t3;
rn1: 没有 partition, 所有数据均为 1 组，总行数为 5，
第一行：小于等于 1000 的行数为 1，因而，1/5=0.2
第三行：小于等于 3000 的行数为 3，因而，3/5=0.6rn2: 依照部门分组，dpet=d1 的行数为 3,
第二行：小于等于 2000 的行数为 2，因而，2/3=0.6666666666666666
- PERCENT_RANK
PERCENT_RANK 分组内以后行的 RANK 值 -1/ 分组内总行数 -1
经调研 该函数显示现实意义不明朗 有待于持续考据
SELECT
dept,
userid,
sal,
PERCENT_RANK() OVER(ORDER BY sal) AS rn1, – 分组内
RANK() OVER(ORDER BY sal) AS rn11, – 分组内 RANK 值
SUM(1) OVER(PARTITION BY NULL) AS rn12, – 分组内总行数
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM test_t3;
rn1: rn1 = (rn11-1) / (rn12-1)
第一行,(1-1)/(5-1)=0/4=0
第二行,(2-1)/(5-1)=1/4=0.25
第四行,(4-1)/(5-1)=3/4=0.75rn2: 依照 dept 分组，
dept=d1 的总行数为 3
第一行，(1-1)/(3-1)=0
第三行，(3-1)/(3-1)=1
### grouping sets,grouping__id,cube,rollup
这几个剖析函数通常用于 OLAP 中，不能累加，而且须要依据不同维度上钻和下钻的指标统计，比方，分小时、天、月的 UV 数。- 数据筹备2020-03,2020-03-10,cookie1
2020-03,2020-03-10,cookie5
2020-03,2020-03-12,cookie7
2020-04,2020-04-12,cookie3
2020-04,2020-04-13,cookie2
2020-04,2020-04-13,cookie4
2020-04,2020-04-16,cookie4
2020-03,2020-03-10,cookie2
2020-03,2020-03-10,cookie3
2020-04,2020-04-12,cookie5
2020-04,2020-04-13,cookie6
2020-04,2020-04-15,cookie3
2020-04,2020-04-15,cookie2
2020-04,2020-04-16,cookie1
CREATE TABLE test_t5 (
month STRING,
day STRING,
cookieid STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
stored as textfile;
加载数据：
load data local inpath ‘/root/hivedata/test_t5.dat’ into table test_t5;
------
- GROUPING SETS
grouping sets 是一种将多个 group by 逻辑写在一个 sql 语句中的便当写法。等价于将不同维度的 GROUP BY 后果集进行 UNION ALL。**GROUPING__ID**，示意后果属于哪一个分组汇合。SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM test_t5
GROUP BY month,day
GROUPING SETS (month,day)
ORDER BY GROUPING__ID;
grouping_id 示意这一组后果属于哪个分组汇合，
依据 grouping sets 中的分组条件 month，day，1 是代表 month，2 是代表 day
等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month UNION ALL
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day;
再如：SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM test_t5
GROUP BY month,day
GROUPING SETS (month,day,(month,day))
ORDER BY GROUPING__ID;
等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM test_t5 GROUP BY month,day;
- CUBE
依据 GROUP BY 的维度的所有组合进行聚合。SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM test_t5
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;
等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM test_t5
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM test_t5 GROUP BY month,day;
- ROLLUP
是 CUBE 的子集，以最左侧的维度为主，从该维度进行层级聚合。比方，以 month 维度进行层级聚合：
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM test_t5
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;
– 把 month 和 day 调换程序，则以 day 维度进行层级聚合：
SELECT
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM test_t5
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
（这里，依据天和月进行聚合，和依据天聚合后果一样，因为有父子关系，如果是其余维度组合的话，就会不一样）
- & ↩