小记--------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 sql函数及hive 命令行简单操作-2
小记--------hive sql函数及hive 命令行简单操作-2
注:可以通过设置 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个数。

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

上一篇:Android 系统内置App JNI


下一篇:分布式文件存储FastDFS(一)初识FastDFS