一文学完所有的Hive SQL（两万字最全详解）

==================================================

进入主页，点击右上角“设为星标”

比别人更快接收好文章

Hive SQL 大全

本文基本涵盖了Hive日常使用的所有SQL，因为SQL太多，所以将SQL进行了如下分类：

一、DDL语句（数据定义语句）：

对数据库的操作：包含创建、修改数据库

对数据表的操作：分为内部表及外部表，分区表和分桶表

二、DQL语句（数据查询语句）：

单表查询、关联查询

hive函数：包含聚合函数，条件函数，日期函数，字符串函数等

行转列及列转行：lateral view 与 explode 以及 reflect

窗口函数与分析函数

其他一些窗口函数

Hive的DDL语法

对数据库的操作

说明：可以使用alter  database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

对数据表的操作

对管理表(内部表)的操作:

对decimal类型简单解释下

row format delimited fields terminated by ‘t’  指定字段分隔符，默认分隔符为 ’01’stored as 指定存储格式location 指定存储位置

对外部表操作

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉，只会删除表的元数据

对分区表的操作

注意：hive表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时，hive会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错当表是分区表时，比如 partitioned by (day string)， 则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123这种格式，然后使用：msck  repair   table  score; 修复表结构，成功之后即可看到数据已经全部加载到表当中去了

注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

对分桶表操作

将数据按照指定的字段进行分成多个桶中去，就是按照分桶字段进行哈希划分到多个文件当中去分区就是分文件夹，分桶就是分文件

分桶优点：1. 提高join查询效率2. 提高抽样效率

桶表的数据加载：由于桶表的数据加载通过hdfs  dfs  -put文件或者通过load  data均不可以，只能通过insert  overwrite 进行加载所以把文件加载到桶表中，需要先创建普通表，并通过insert  overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

修改表和删除表

注意：truncate 和 drop：如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表结构恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复truncate清空的表所以 truncate 一定慎用，一旦清空将无力回天

向hive表中加载数据

hive表中数据导出

Hive的DQL查询语法

注意：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

注意：小于某个值是不包含null的，如上查询结果是把 s_score 为 null 的行剔除的

注意：如果使用 group by 分组，则 select 后面只能写分组的字段或者聚合函数where和having区别：1 having是在 group by 分完组之后再对数据进行筛选，所以having 要筛选的字段只能是分组字段或者聚合函数2 where 是从数据表中的字段直接进行的筛选的，所以不能跟在gruopby后面，也不能使用聚合函数

注：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，也就是在一个节点执行，如果数据量太大，就会耗费较长时间

注意：Hive要求 distribute by 语句要写在 sort by 语句之前

Hive函数

聚合函数

注意：聚合操作时要注意null值count(*) 包含null值，统计所有行数count(id) 不包含null值min 求最小值是不包含null，除非所有值都是nullavg 求平均值也是不包含null

关系运算

数学运算

逻辑运算

数值运算

此外还有：以2为底对数函数: log2()、对数函数: log()

十六进制函数: 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()

条件函数

日期函数

注：以下SQL语句中的 from tableName 可去掉，不影响查询结果

字符串函数

复合类型构建操作

复杂类型访问操作

复杂类型长度统计函数

hive当中的lateral view 与 explode以及reflect和窗口函数

使用explode函数将hive表中的Map和Array字段数据进行拆分

lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。

其中explode还可以用于将hive一列中复杂的array或者map结构拆分成多行

需求：现在有数据格式如下

字段之间使用t分割，需求将所有的child进行拆开成为一列

将map的key和value也进行拆开，成为如下结果

使用explode拆分json字符串

需求: 需求：现在有一些数据格式如下：

其中字段与字段之间的分隔符是 |

我们要解析得到所有的monthSales对应的值为以下这一列（行转列）

4900

2090

6987

配合LATERAL  VIEW使用

配合lateral view查询多个字段

也可以多重使用

最终，我们可以通过下面的句子，把这个json格式的一行数据，完全转换成二维表的方式展现

总结：

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类型字段。

数据准备:

需求: 把星座和血型一样的人归类到一起。结果如下：

实现步骤:

列转行

所需函数:

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

数据准备:

需求: 将电影分类中的数组数据展开。结果如下：

实现步骤:

reflect函数

reflect函数可以支持在sql中调用java中的自带函数，秒杀一切udf函数。

需求1: 使用java.lang.Math当中的Max求两列中最大值

实现步骤:

需求2: 文件中不同的记录来执行不同的java的内置函数

实现步骤:

需求3: 判断是否为数字

实现方式:

使用apache commons中的函数，commons下的jar已经包含在hadoop的classpath中，所以可以直接使用。

窗口函数与分析函数

在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能。

partition byorder by。

over (partition by xxx order by xxx)

sum、avg、min、max

准备数据

SUM函数和窗口函数的配合使用：结果和ORDER BY相关,默认为升序。

如果不指定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

准备数据

ROW_NUMBER()使用

ROW_NUMBER()从1开始，按照顺序，生成分组内记录的序列。

RANK 和 DENSE_RANK使用

RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位 。

DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。

NTILE

有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。

ntile可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

然后可以根据桶号，选取前或后 n分之几的数据。数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。

其他重要窗口函数

lag,lead,first_value,last_value

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

FIRST_VALUE

取分组内排序后，截止到当前行，第一个值

取分组内排序后，截止到当前行，最后一个值

如果想要取分组内排序后最后一个值，则需要变通一下：

特别注意order  by

如果不指定ORDER BY，则进行排序混乱，会出现错误的结果

cume_dist,percent_rank

注意：序列函数不支持WINDOW子句

CUME_DIST  和order byd的排序顺序有关系

CUME_DIST 小于等于当前值的行数/分组内总行数  order 默认顺序 正序 升序比如，统计小于等于当前薪水的人数，所占总人数的比例

PERCENT_RANK

PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1

经调研 该函数显示现实意义不明朗 有待于继续考证

grouping sets,grouping__id,cube,rollup

这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数。

grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

等价于将不同维度的GROUP BY结果集进行UNION ALL。

GROUPING__ID

再如：

根据GROUP BY的维度的所有组合进行聚合。

是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

–END–