Hive 边做边记录 字符串处理函数与解析

近期校验数据，深感自己还有太多需要好好学习的地方，尤其是mentor师父写的hql和自己写的一对比，效率提升太多了。 将自己最近遇到的和看到相关的，先记录整理下。

查看表和字段信息

查看表的信息两种方式

1. show

hive -e " show create tableName ;"

3. desc

hive -e "desc tableName;"

两个都会显示相应字段、字段的类型和对应的注释；
查看当前用户有的表
show tables

hive 字符串处理

cast 类型转换

可以将属性的值的类型进行转换
如
使用 CAST 函数将 STRING 转为 BIGINT:

`SELECT CAST('00321' AS BIGINT) ,cast(video_dura as INT ) FROM table;

上面的 '00321’可用直接某个属性名去替换，得到转换类型后的值。但注意原表中的属性值类型不变。

get_json_object解析json字段

get_json_object(string json_string, string keyname)
解析表中的列为json 字符串的对象，第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL。每次只能返回一个数据项。

样例
data 为 test表中的字段，数据结构如下：

data =
{
 "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" 
}

• 拿第一层的值
  • `hive> select get_json_object(data, ‘$.owner’) from test;
    结果：amy
• get多层值.
  hive> select get_json_object(data, ‘$.store.bicycle.price’) from test;
  结果：19.95
  -拿到数组的值

	hive> select  get_json_object(data, '$.store.fruit[0]') from test;

结果：{“weight”:8,“type”:“apple”}

参考： https://blog.csdn.net/qq_34105362/article/details/80454697

字符串长度函数 length

语法: length(string A) ,返回值: int
说明：返回字符串A的长度
hive> select length(‘abced’) from dual;

字符串反转函数 reverse

语法: reverse(string A)
返回值: string
说明：返回字符串A的反转结果
hive> select reverse(‘abcedfg’) from dual;
gfdecba

字符串连接函数：concat

语法: concat(string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，支持任意个输入字符串
hive> select concat(‘abc’,‘def’) from dual;
abcdef

带分隔符字符串连接函数：concat_ws

语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
hive> select concat_ws(’,’,‘abc’,‘def’,‘gh’) from dual;
abc,def,gh

字符串截取函数：substr,substring

语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明：返回字符串A从start位置到结尾的字符串
hive> select substr(‘abcde’,3) from dual;
cde
hive> select substring(‘abcde’,3) from dual;
cde
hive> select substr(‘abcde’,-1) from dual;
e

分割字符串函数: split

跟底层MR中的split方法功能一样。
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组
hive> select split(‘abtcdtef’,‘t’) from dual;
[“ab”,“cd”,“ef”]
更多字符串函数参考：
https://zhuanlan.zhihu.com/p/82601425

collect_set 函数

      collect_set(abnormal_level)[0] as abnormal_level

Hive中collect相关的函数有collect_list和collect_set。，它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。
参考： https://www.cnblogs.com/cc11001100/p/9043946.html

if 条件语句：

If函数:if和case差不多，都是处理单个列的判断查询结果

语法: if(boolean testCondition, T valueTrue, T valueFalse Or Null)

hql中数据去重的三种方式

1.distinct
select distinct * from tableName
hive用时: 40.47秒
impala用时: 11.98秒

2.group by
select c1,c2,c3,c4,c5,max(c6) c6
from tableName
group by c1,c2,c3,c4,c5
hive用时: 22.8秒
impala用时: 2.4秒

3.窗口函数( 这里选用:row_number()over() )
select * from
(select c1,c2,c3,c4,c5,c6,
row_number()over(partition by c1,c2 order by c6 desc) ranking
from tableName) tmp
where ranking = 1
hive用时: 21.41秒
impala用时: 2.5秒
总结:
distinct 效率最低,不建议使用;
group by 和 窗口函数 的去重效率高,用时差不多,根据实际情况使用.

此处参考： https://blog.csdn.net/Thomson617/article/details/89145724

hive中ROW_NUMBER()函数

ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

row_number() over (partition by 字段a order by 计算项b desc ) rank

rank是排序的别名
partition by：类似hive的建表，分区的意思；
order by ：排序，默认是升序，加desc降序；
这里按字段a分区，对计算项b进行降序排序
2、hive的分组和组内排序 — 实例

要取top10品牌，各品牌的top10渠道，各品牌的top10渠道中各渠道的top10档期

1、取top10品牌

select “品牌” , sum/count/其他() as num from “table_name” order by num desc limit 10;

2、取top10品牌下各品牌的top10渠道

select a.* from (select “品牌”,“渠道”,sum/count() as num, row_number () over (partition by “品牌” order by num desc) rank from “table_name” where 品牌限制条件 group by “品牌”,“渠道” ) a having a.rank <= 10;

3、 取top10品牌下各品牌的top10渠道中各渠道的top10档期

select a.* from (select “品牌”,“渠道”,“档期”,sum/count/其他() as num row_number() over (partition by “档期” order by num desc) rank from “table_name” where 品牌限制条件 group by “品牌”,“渠道) a Having a.rank <= 10;

row_number的使用在hive和spark的实时计算中常常会用到计算分区中的排序问题，所以使用好row_number是很重要的。
注：我们可以使用ROW_NUMBER()函数进行去重，使用上述两个列名首先对其进行双重排序，最后在这个hql语句外层再添加一个判断row_number的值为1的条件语句获取分组排序后的第一条数据进行获取，即达到了去重的效果。

https://www.jianshu.com/p/51599bab0c00

参考目录：

https://blog.csdn.net/qq_26442553/article/details/79465417
https://cloud.tencent.com/developer/article/1063615
https://zhuanlan.zhihu.com/p/82601425
https://blog.csdn.net/Thomson617/article/details/89145724