导语：HiveSQL各关键字详解，hive函数大全，类似于个人记录工具书，后续遇到其他的也会继续加进来。

有关hive库表操作请见上篇：漫谈大数据 - HiveSQL总结（一）库表操作_昊昊该干饭了的博客-CSDN博客针对hive各种数据库操作，内部表、外部表、分区表、分桶表的表属性查看修改操作以及hive数据的导入与导出详解。https://blog.csdn.net/qq_52213943/article/details/130287680?spm=1001.2014.3001.5501

hive简介：

        hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MR任务来执行。

目录

Hive查询语法

WHERE 条件筛选

GROUP BY 分组

join 连接

order by 排序

sort by 局部排序

distribute by  分区排序

cluster by

Hive常用函数

聚合函数

关系运算

REGEXP操作

数学运算

逻辑运算

数值运算

条件函数

日期函数

字符串函数

窗口函数

复杂数据类型操作

Map类型构建

Map类型访问

Map类型长度

Struct类型构建

Struct类型访问

Array类型构建

Array类型访问

Array类型长度

类型转换函数

---

Hive查询语法

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]

WHERE 条件筛选

select * from score where score < 60;

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

GROUP BY 分组

select s_id ,avg(s_score) 
from score 
group by s_id;



分组后对数据进行筛选，使用having

select s_id ,avg(s_score) as avgscore 
from score 
group by s_id 
having avgscore > 85;

注意
如果使用 group by 分组，则 select 后面只能写分组的字段或者聚合函数

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

order by 排序

SELECT * FROM student s 
LEFT JOIN score sco 
ON s.s_id = sco.s_id 
ORDER BY sco.s_score DESC;

注意

全局排序，只会有一个reduce
ASC（ascend）: 升序（默认）

DESC（descend）: 降序

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使用

注意

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

设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去
set mapreduce.job.reduces=7;

通过distribute by  进行数据的分区
select * from score distribute by s_id sort by s_score;

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() 等常用的聚合函数

• 指定列值的数目：count()
• 指定列值求和：sum()
• 指定列的最大值：max()
• 指定列的最小值：min()
• 指定列的平均值：avg()
• 非空集合总体变量函数：var_pop(col)
• 非空集合样本变量函数：var_samp (col)
• 总体标准偏离函数：stddev_pop(col)
• 分位数函数：percentile(BIGINT col, p)
• 中位数函数：percentile(BIGINT col, 0.5)

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

关系运算

支持：等值(=)、不等值(!= 或 <>)、小于(<)、小于等于(<=)、大于(>)、大于等于(>=)

空值判断(is null)、非空判断(is not null)

• A LIKE B： LIKE比较，如果字符串A符合表达式B 的正则语法，则为TRUE
• A RLIKE B：JAVA的LIKE操作，如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE
• A REGEXP B：功能与RLIKE相同

REGEXP操作

语法: A REGEXP B
操作类型: strings
描述: 功能与RLIKE相同
示例：select 1 from tableName where 'footbar' REGEXP '^f.*r$';
结果：1

数学运算

支持所有数值类型：加(+)、减(-)、乘(*)、除(/)、取余(%)、位与(&)、位或(|)、位异或(^)、位取反(~)

逻辑运算

逻辑与(and)、逻辑或(or)、逻辑非(not)

数值运算

• 取整函数：round(double a)
• 指定精度取整函数：round(double a, int d)
• 向下取整函数：floor(double a)
• 向上取整函数：ceil(double a)
• 取随机数函数：rand(),rand(int seed)
• 自然指数函数：exp(double a)
• 以10为底对数函数：log10(double a)
• 以2为底对数函数：log2()
• 对数函数：log()
• 幂运算函数：pow(double a, double p)
• 开平方函数：sqrt(double a)
• 二进制函数：bin(BIGINT a)
• 十六进制函数：hex()
• 绝对值函数：abs()
• 正取余函数：pmod()

条件函数

• if
• case when
• coalesce(c1,c2,c3)
• nvl(c1，c2)

日期函数

• 获得当前时区的UNIX时间戳: unix_timestamp()
• 时间戳转日期函数：from_unixtime()
• 日期转时间戳：unix_timestamp(string date)
• 日期时间转日期函数：to_date(string timestamp)
• 日期转年函数：year(string date)
• 日期转月函数：month (string date)
• 日期转天函数: day (string date)
• 日期转小时函数: hour (string date)
• 日期转分钟函数：minute (string date)
• 日期转秒函数: second (string date)
• 日期转周函数: weekofyear (string date)
• 日期比较函数: datediff(string enddate, string startdate)
• 日期增加函数: date_add(string startdate, int days)
• 日期减少函数：date_sub (string startdate, int days)

字符串函数

• 字符串长度函数：length(string A)
• 字符串反转函数：reverse(string A)
• 字符串连接函数: concat(string A, string B…)
• 带分隔符字符串连接函数：concat_ws(string SEP, string A, string B…)
• 字符串截取函数: substr(string A, int start, int len)
• 字符串转大写函数: upper(string A)
• 字符串转小写函数：lower(string A)
• 去空格函数：trim(string A)
• 左边去空格函数：ltrim(string A)
• 右边去空格函数：rtrim(string A)
• 正则表达式替换函数： regexp_replace(string A, string B, string C)
• 正则表达式解析函数: regexp_extract(string subject, string pattern, int index)
• URL解析函数：parse_url(string urlString, string partToExtract [, string keyToExtract])
• 返回值: string
• json解析函数：get_json_object(string json_string, string path)
• 空格字符串函数：space(int n)
• 重复字符串函数：repeat(string str, int n)
• 首字符ascii函数：ascii(string str)
• 左补足函数：lpad(string str, int len, string pad)
• 右补足函数：rpad(string str, int len, string pad)
• 分割字符串函数: split(string str, string pat)
• 集合查找函数: find_in_set(string str, string strList)

窗口函数

• 分组求和函数：sum(pv) over(partition by cookieid order by createtime) 有坑，加不加 order by 差别很大，具体详情在下面第二部分。
• 分组内排序，从1开始顺序排：ROW_NUMBER() 如：1234567
• 分组内排序，排名相等会在名次中留下空位：RANK() 如：1233567
• 分组内排序，排名相等不会在名次中留下空位：DENSE_RANK() 如：1233456
• 有序的数据集合平均分配到指定的数量（num）个桶中：NTILE()
• 统计窗口内往上第n行值：LAG(col,n,DEFAULT)
• 统计窗口内往下第n行值：LEAD(col,n,DEFAULT)
• 分组内排序后，截止到当前行，第一个值：FIRST_VALUE(col)
• 分组内排序后，截止到当前行，最后一个值: LAST_VALUE(col)
• 小于等于当前值的行数/分组内总行数：CUME_DIST()

复杂数据类型操作

Map类型构建

语法: map (key1, value1, key2, value2, …)
说明：根据输入的key和value对构建map类型

Map类型访问

语法: M[key]
操作类型: M为map类型，key为map中的key值
说明：返回map类型M中，key值为指定值的value值。
比如，M是值为{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar'

Map类型长度

语法: size(Map<k .V>)
返回值: int
说明: 返回map类型的长度

Struct类型构建

语法: struct(val1, val2, val3, …)
说明：根据输入的参数构建结构体struct类型

Struct类型访问

语法: S.x
操作类型: S为struct类型
说明：返回结构体S中的x字段。
比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段

Array类型构建

语法: array(val1, val2, …)
说明：根据输入的参数构建数组array类型

Array类型访问

语法: A[n]
操作类型: A为array类型，n为int类型
说明：返回数组A中的第n个变量值。数组的起始下标为0。
比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar'

Array类型长度

语法: size(Array<T>)
返回值: int
说明: 返回array类型的长度

类型转换函数

类型转换函数: cast
语法: cast(expr as <type>)
返回值: Expected "=" to follow "type"

-- 将字符串类型1 转为数字1

select cast('1' as int) from tableName;

欢迎点赞收藏评论交流~

转载请标明出处