hive MaxCompute ( 原odps) 常用函数_odps编写create sql function

参数使用

   需要在任务配置里配置. 配置里 ${yyyyMMdd} 代表业务时间 $[yyyyMMdd] 代表运行时间. 参数里
    

控制

   if( , ,) 

函数

云上特有功能 

create sql function

时间函数

    to_date(gmt_modified,'yyyy-mm-dd HH:mi:ss')

   

1. select dateadd(date '2005-02-18', 1, 'mm');
2. --返回2005-03-18
3. select dateadd(timestamp '2005-02-18 00:00:00', 1, 'mm');
4. --返回2005-03-18 00:00:00

dt = TO_CHAR(DATEADD( GETDATE() , - 1,"dd"),"yyyymmdd")

字符串函数

  KEYVALUE('fund_operation_type=PAY;orderAmout=0.25;',';','=','orderAmout') = '0.25'
  split_part('a,b,c,d', ',', 1, 2) = 'a,b'

其他函数

   split 得到素组

    size(split("1;2;3",';'))=2

  split("1;2;3",';') 得到array ,然后 FROM_JSON ,然后通过get_json_object 获取item 元素

转json

  FROM_JSON

1. SELECT from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE');
2. {"a":1, "b":0.8}
3. SELECT from_json('{"time":"26/08/2015"}', 'time string');
4. {"time":"26/08/2015"}
5. SELECT from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE, c STRING');
6. {"a":1, "b":0.8, c: NULL}
7. SELECT from_json('[1, 2, 3, "a"]', 'array<BIGINT>');
8. [1, 2, 3]
9. SELECT from_json('{"a":1,"b":[1,2,3],"c":{},"d":"v"}', 'map<string, string>');
10. {a:1, b:[1,2,3], c:{}, d:v}

json提取 - get_json_object 

云上是自定义函数

示例1

1. +----+
2. json
3. +----+
4. {"store":
5. {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
6. "bicycle":{"price":19.95,"color":"red"}
7. },
8. "email":"amy@only_for_json_udf_test.net",
9. "owner":"amy"
10. }

通过以下查询，可以提取json对象中的信息：

1. odps> SELECT get_json_object(src_json.json, '$.owner') FROM src_json;
2. amy
4. odps> SELECT get_json_object(src_json.json, '$.store.fruit[0]') FROM src_json;
5. {"weight":8,"type":"apple"}
7. odps> SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json;
8. NULL

示例2

1. get_json_object('{"array":[[aaaa,1111],[bbbb,2222],[cccc,3333]]}','$.array[1][1]') = "2222"
3. get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]') = "["h0","h1","h2"]"
5. get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]') = "h1"

大小

  size()