小记--------hive sql函数及hive 命令行简单操作-2

2.聚合函数

 

返回值类型	样式	描述
BIGINT	Count(*)	J计算总函数，包括含有NULL值的行
BIGINT	count(expr)	计算expr表达式的值非NULL的行数
BIGINT	count(DISTINCT expr）	计算expr表达式的值去重后非NULL行数
DOUBLE	sum(col)	计算指定行的值的和
DOUBLE	sum(DISTINCT col)	J计算去重后指定行的值
DOUBLE	avg(col)	计算指定行的平均值
DOUBLE	avg(DISTINCT col)	计算去重后指定行的平均值
DOUBLE	min(col)	计算指定行的最小值
DOUBLE	max(col）	计算指定行的最大值

注：可以通过设置 hive.map.aggr值为true 来提供聚合性能

 

3.其他内置函数

 

返回值类型	样式	描述
返回类型就是type定义的类型	cast(col as <type>)	将列转化为type类型，如果转换过程失败，则返回NULL
STRING	concat(BINARY s1 ，BINARY s2 , ...)	J将二进制字节码按次序拼接成一个字符串
STRING	concat(STRING s1 , STRING s2 , ...)	将字符串s1 ， s2等拼接成一个字符串。例如concat('ab',’cd’) = ‘abcd’
STRING	concat_ws(STRING separator , STRING  s1 , STRING s2 , …)	和concat类似，不过是使用指定的分隔符进行拼接的
BOOLEAN	in	例如：test in (val1, val2 , …) 如果test值鞥与后面列表中的任一值返回true
INT	instr(STRING str , STRING substr)	查找字符串str中的子字符串substr第一次出现的位置
INT	length(STRING s)	计算字符串s的长度
INT	locate(STRING substr，STRING str [,INT pos])	查找在字符串str中的pos位置后字符串substr第一次出现的位置
STRING	lower(STRING s)	将字符串中所有字母转换成小写字母。例如：upper(‘hlvE’)的结果是’hive'
STRING	Itrim(STRING s )	将字符串s前面的空格全部去除掉。例如trim(‘hive')
STRING	trim(STRING s)	将字符串s前后的空格全部去掉
STRING	substr(STRING s , STRING start_index  , STRING length)	将字符串s 从start位置开始截取length长度的字符串。例：substr(’sdfasd’ , 1 ,2)  结果为 sd
STRING	upper(STRING s )	将字符串s中所有字母转换为大写字母
STRING	from_unixtime(BIGINT  unixtime[ , STRING format])	将时间戳秒数转换为指定format格式
BIGINT	unix_timestamp()	获取当前本地时区下的当前时间戳
BIGINT	unix_timestamp(STRING date）	输入的时间字符串格式必须是yyyy-MM-dd HH:mm:ss 如果不符合格式则返回0，符合则转换为Unix时间戳
STRING	to_date(STRING timestamp)	返回时间字符串的日期部分，例：to_date("1970-01-01 00:00:00”) 返回1970-01-01
INT	year(STRING date)	返回时间字符串的年份并使用int类型表示。例：year(“1970-01-01 00:00:00”) 结果为1970
INT	month(STRING date)	返回时间字符串中的月份并使用int类型表示。例：mongth(“1970-01-01 00:00:00”)  结果为01
INT	day(STRING date)	返回时间字符串中的天，并使用int类型表示。例：day("1970-01-01 00:00:00”) 结果为01
INT	hour(STRING date)	返回时间戳字符串的小时并使用int表。例：hour("1970-01-01 00:00:00”)  结果为00
INT	minute(STRING date)	返回时间字符串中的分钟数
INT	second(STRING date)	返回时间字符串中的秒数
INT	weekofyear(STRING date)	返回时间字符串位于一年中的第几周内，例：weekofyear("1970-01-01 00:00:00”) 结果为1
INT	datediff(STRING enddate , STRING startdate)	计算开始时间startdate到结束时间enddate相差天数。例：datediff(“2020-09-09 , “2020-09-10”) j结果为 -1
INT	date_sub(STRING startdate , INT days)	从开始时间startdate中减去days天，例：date_sub(“2020-09-09” ， 1) 结果为：2020-09-08

 

RLIKE 语句 对like功能的扩展

案例： 从e表中查找出所有住址的街道名称中含有单词Chicago或Ontario的信息

SELECT name , address.street FROM e WHERE address.street RLIKE ‘.*(Chicago|Ontario).*’;

等价于： SELECT name ,address FROM e WHERE address.street LIKE ‘%Chicago%’ OR address.street LIKE ‘%Ontario%'

 

Hive排序关键词

 

ORDER BY 和 SORT BY

 

order by : 会对查询结果集执行一个全局排序，也就是说会有一个所有的数据都通过一个reducer进行处理的过程。  确定： 消耗时间太长。

 

sort by：其只会在每个reducer中对数据进行排序，也就是执行一个局部排序过程，这可以保证每个reducer的输出数据都是有序的（但并非全局有序），可以提高后面进行的全局排序的效率

 

注:如果reducer的个数是1的话，那么order by 和sort by的结果是一样的，只有多个reducer的时候order by 会按照每个reducer进行局部排序。

 

DISTRIBUTE BY

distribute by：  控制map的输出在reducer中是如何划分的，MapReduce job中传输的所有数据都是按照键-值对的方式进行组织的，默认情况下，MapReduce计算框架会依据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer中去，这也就意味着当我们使用sort by时，不同reducer的输出内容会有明显的重叠。

注：distribute by 会将特定的行到某个reducer，distribute by 要写在sort by 语句之前,distribute by 只是做  ‘分’  并不进行排序。

例：

根据年份和气温对气象数据进行排序，以确保所有具有相同年份的行最终都在一个reducer分区中

From record2

select year, temperature

distribute by year

sort by year asc, temperature desc;

因此，distribute by 经常和 sort by 配合使用

 

CLUSTER BY  是 distribute by 和 sort by 的结合体

cluster by id = distribute by id + sort by id 

也就是当分组字段和排序字段是一个字段的时候就使用cluster by  ，否则不要使用cluster by 

注： cluster by  不能和sort by同时使用。

 

创建分桶表 CLUSTERED BY (列名) INTO 分桶数 BUCKETS 

例： 

CREATE TABLE weblog (user_id INT , url STRING , source_ip STRING) PARTITIONED BY (dt STRING) CLUSTERED BY (user_id) INTO 96 BUCKETS;

上述只是仅仅定义了元数据，而不影响实际填充表的命令。

填充表使用INSERT  TABLE 

例：SET hive.enforce.bucketing = true ;   初始化过程设置正确的reducer个数

FROM raw_logs INSERT OVERWEITE TABLE weblog PARTITION (dt=‘20200909’)

SELECT user_id , url , source_ip WHERE dt=‘20200909’

注：如果不使用 hive.enforce.bucketing属性，那么就需要自己设置和分桶个数相匹配的reducer个数。