Hive常用字符串函数（工作中总结整理）

【"Hive内置聚合函数"】https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inAggregateFunctions(UDAF)

substr

• substr(string A, int start), 返回字符串A从start位置到结尾的字符串

• substr(string A, int start, int len),返回字符串A从start位置开始，长度为len的字符串

substring

• substring(string A, int start), 返回字符串A从start位置到结尾的字符串

• substring(string A, intstart, int len),返回字符串A从start位置开始，长度为len的字符串

concat

使用concat（）函数将字符串和字符串连接起来

• eg: user表中phone为13578787878，需处理成1357878；

hive > SELECT concat(substr(phone,1,3),',',substr(phone,8)) AS phone FROM user

hive > 135****7878

---

1. seq1      seq2

2. 11111    CC0000

3. 11111    CC1111

4. 11111    CC2222

5. 22222   CC3333

6. 22222   CC4444

collect_set

collect_set 是 Hive 内置的一个聚合函数, 它返回一个消除了重复元素的对象集合, 其返回值类型是 array 。 将上面表中一个seq1可能会占用多行转换为每个seq1占一行的目标表格式，实际是“列转行”。

hive > select seq1,collect_set(seq2) from tab1 group by seq1

1. seq1     seq2

2. 11111    ["CC0000","CC1111","CC2222"]

3. 22222   ["CC3333","CC4444"]

hive > select seq1,SIZE(collect_set(seq2)) from tab1 group by seq1

hive > 22222 2

hive > 11111 3

 

concat_ws

hive > select seq1,concat_ws(',',collect_set(seq2)) from tab1 group by seq1

1. seq1 seq2
2. 11111 CC0000,CC1111,CC2222
3. 22222 CC3333,CC4444

collect_set的作用：

       （1）去重，对group by后面的seq1进行去重

       （2）对group by以后属于同一seq1的形成一个集合，结合concat_ws对集合中元素使用，进行分隔形成字符串

---

• eg: UserOrder表中user, order_type, order_number三列,需处理成行如：

user1 order_type1(order_number1),order_type2(order_number2)一行数据

hive > select user,concat_ws(',',collect_set(concat（order_type,'(',order_number,')'）)) items from UserOrder group by user

 

explode

explode（）接受一个数组（或一个map）作为输入，并将数组元素（map）作为单独的行输出。 UDTF可以在SELECT表达式列表中使用，也可以作为LATERAL VIEW的一部分使用。

• 数据格式如下：

  1. Array<int> myCol

  2. [100,200,300]

  • [400,500,600]

hive > SELECT explode(myCol) AS myNewCol FROM myTable;

 

• 结果如下：

  • (int)myNewCol

  • 100

  • 200

  • 300

  • 400

  • 500

  • 600

Map和array类似

hive >SELECT explode(myMap) AS (myMapKey, myMapValue) FROM myMapTable;

 

LATERAL VIEW

Lateral View用于UDTF(user-defined table generating functions)中将行转成列,Lateral View与explode等udtf就是天生好搭档，explode将复杂结构一行拆成多行，然后再用Lateral View做各种聚合。

• 现有基础表pageAds(表结构如下)：

  1. Column_name    Column_name

  2. pageid                 String

  3. add_list                Array<int>

• 数据格式如下：

  1. pageid              add_list

  2. front_page         [1,2,3]

  3. contact_page     [3,4,5]

• 并且用户希望计算广告在所有页面中显示的总次数。 使用lateral view explode()可以使用查询将adid_list转换为单独的行（记得写别名）：

hive >SELECT pageid, adid FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

结果如下：

• pageid(string) adid(int)

1. front_page       1
2. front_page       2
3. front_page       3
4. contact_page   3
5. contact_page   4
6. contact_page   5

• 为了统计每个页面的数量，需要对adid进行group by分组,然后count

hive >SELECT adid, count(1) FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid GROUP BY adid;

 

• 结果如下：

  1. int adid count(1)

  2. 1      1

  3. 2      1

  4. 3      2

  5. 4      1

  6. 5      1

---

但有些情况下，需要自己用split函数将拼接的字符串转换为分割array数组类型

hive >SELECT bb FROM user as aa lateral view explode(split(concat_ws(',',id,userid,phone),',') )b as bb

 

length

• length(String A)

• 返回值：int

  • hive > SELECT length('abcdefg') from tab

  • 7

解析json

现有表test中字段data如下json格式：

{"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.951,"color":"red1"}},"email":"amy@only_for_json_udf_test.net","owner":"amy1"}

1.get_json_object(string json_string, string path)

• 用来解析json字符串中的一个字段

• 说明：解析json的字符串json_string,返回path指定的内容。若json字符串无效，则返回NULL。

• get单层值

  • hive > select get_json_object(data, '$.owner') from test;

  • amy1

• get多层值

  • hive > select get_json_object(data, '$.store.bicycle.price') from test;

  • 19.951

• get数组值[]

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

  • {"weight":8,"type":"apple"}

• get多个字段

  • hive > SELECT get_json_object(data,'$.owner') as owner,get_json_object(data,'$.fruit[0].type')as type FROM test

2.json_tuple(jsonStr, k1, k2, ...)

• 用来解析json字符串中的多个字段

• 使用json_tuple对象时，可以一次获取多个对象并且可以被组合使用

  • hive > SELECT aa.owner,aa.email FROM test lateral view json_tuple(data,'owner','email')aa AS owner,email