hive常用函数--concat_ws,cast,collect_set,row_number,get_json_object

1.concat_ws(seperator, string s1, string s2...)
功能：制定分隔符将多个字符串连接起来，实现“列转行”
例子：常常结合group by与collect_set使用

有表结构a string , b string , c int
数据为
c d 1
c d 2
c d 3
e f 4
e f 5
e f 6
想要得到
c d 1,2,3
e f 4,5,6
语句如下
select a, b, concat_ws(',' , collect_set(cast(c as string)))
from table group by a,b;

2.cast  

CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。以下例子用于将文本字符串'12'转换为整型:

SELECT CAST('12' AS int)

3.collect_set 函数，有两个作用，第一个是去重，去除group by后的重复元素，
第二个是形成一个集合，将group by后属于同一组的第三列集合起来成为一个集合。与contact_ws
结合使用就是将这些元素以逗号分隔形成字符串。

 

4.row_number

用法：row_number() OVER (PARTITION BY COL1 ORDERBY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（该编号在组内是连续并且唯一的) 。

举个例子：employee表，先按照部门进行分组，部门内部按照工资降序排列

SELECT empno,WORKDEPT,SALARY, Row_Number() OVER (partition by workdept ORDER BY salary desc) rank FROM employee

-------------------------------------- 

 A00 152750  1 

 A00 66500   2 

 A00 49250   3 

 A00 46500   4 

 A00 39250   5 

 B01 94250   1 

 C01 98250   1 

 C01 73800   2

5.case..when   可以用来处理列转行的情况

举个例子：

原本数据

经处理后：

 

6.get_json_object：

 

第一个参数填写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" 
}

get单层值

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

结果：amy

get多层值.

hive> select  get_json_object(data, '$.store.bicycle.price') from test;
结果：19.95

get数组值[]

hive> select  get_json_object(data, '$.store.fruit[0]') from test;
结果：{"weight":8,"type":"apple"}