• 5.1 json 函数
  • 5.1.1 json_extract_value
  • 5.1.2 json_extract_array_value
  • 5.1.3 explode_json_array
  • 5.1.4 to_json
  • 5.1.5 reduce_json
  • 5.1.6 json_compress
  • 5.1.7 json_split
• 5.2 字符串函数
  • 5.2.1 encode_sha512
  • 5.2.2 get_idcard_info
  • 5.2.3 get_ip_info
  • 5.2.4 md5_hash
  • 5.2.5 user_agent_parser
  • 5.2.6 regexp_extract_all
• 5.3 时间函数
  • 5.3.1 dateformat
• 5.4 数字函数
  • 5.4.1 random_int
• 5.5 数组函数
  • 5.5.1 array_concat
  • 5.5.2 array_contains
  • 5.5.3 array_distinct
  • 5.5.4 array_element_at
  • 5.5.5 array_equals
  • 5.5.6 array_from_json
  • 5.5.7 array_intersect
  • 5.5.8 array_join
  • 5.5.9 array_max
  • 5.5.10 array_min
  • 5.5.11 array_position
  • 5.5.12 array_remove
  • 5.5.13 array_reverse
  • 5.5.14 array_slice
  • 5.5.15 array_sort
  • 5.5.16 array_value_count
  • 5.5.17 merge_list
  • 5.5.18 merge_set
• 5.6 数据脱敏函数
  • 5.6.1 mask
  • 5.6.2 mask_first_n
  • 5.6.3 mask_last_n
  • 5.6.4 mask_show_first_n
  • 5.6.5 mask_show_last_n
  • 5.6.6 mask_hash
• 5.7 加解密函数
  • 5.7.1 decode_base64
  • 5.7.2 des_gen_key
  • 5.7.3 des_encrypt
  • 5.7.4 des_decrypt
• 5.8 地理位置函数
  • 5.8.1 udf_sys_geo_distance
  • 5.8.2 udf_sys_geo_in_cycle
  • 5.8.3 udf_sys_geo_in_rectangle

5 公共函数

平台内置的公共函数默认挂载在bigdata项目下 其他项目要使用的话要加上bigdata.的前缀

5.1 json 函数

5.1.1 json_extract_value

说明 从json字符串数据中，获取key值，可以一次获取多个key的值返回 如果获取多个字段，优先json_extract_value函数，不要使用hive 自带的get_json_object，get_json_object每调用一次，需要解析一次，重复解析影响性能

bigdata.json_extract_value(jsondata, key1, ..., keyn) as (col1, ..., coln)

实例

select bigdata.json_extract_value('{"name":"hongyi.zhou","address":"hangzhou"}', "name", "address") as (name, address);
--output
name        address
hongyi.zhou hangzhou

--解析json数组
select bigdata.json_extract_value('[{"name":"hongyi.zhou"}, {"address":"hangzhou"}, {"address2":"hangzhou2"}]', "/0/name", "/1/address", "/2/address2") as (name, address, address2);
--output
name        address     address2
hongyi.zhou hangzhou    hangzhou2

5.1.2 json_extract_array_value

说明 从json字符串数据中，获取key值，可以一次获取多个key的值返回，类似json_extract_value，用于解析json 数组格式字符串，返回值类型为数组。

bigdata.json_extract_array_value(jsondata, key1, ..., keyn) as (col1, ..., coln)

实例

select bigdata.json_extract_array_value('[{"a": {"c": "val12"}}, {"a": {"c": "val2"}}]', "a/c") as (name);
--output
name
["val12","val2"]

5.1.3 explode_json_array

说明 从json字符串数组中获取key值，可以一次获取多个key的值返回，类似explode函数，数组值转为多行返回。

bigdata.explode_json_array(jsondata, key1, ..., keyn) as (col1, ..., coln)

实例

select bigdata.explode_json_array('[{"name":"hongyi.zhou", "address":{"name": "hangzhou"}}]', "name", "address/name") as (name, address);
--output
name        address
hongyi.zhou hangzhou

select bigdata.explode_json_array('[{"name":"hongyi.zhou", "address":"hangzhou"},{"name":"jian.tang", "address":"hangzhou"}]', "name", "address") as (name, address);
--output
name        address
hongyi.zhou hangzhou
jian.tang   hangzhou

5.1.4 to_json

说明 数据转为JSON 字符串，支持数据类型：map,list,struct,array,基本类型（string, int, bigint等），第二参数定义key 缩写名称，有一些key名称比较长，为了减少存储大小，提高计算效率有帮助, 可以缺省不填写

bigdata.to_json(data, key所有字名称格式：key=simplekey,...)

实例

--map转json
select bigdata.to_json(map("k1", "v1", "k2", "212"));
--output
{"k1":"v1","k2":"212"}

--array转json
select bigdata.to_json(array("e1", "e2", "e3"));
--output
["e1","e2","e3"]

todo 更复杂的后续补充。。

5.1.5 reduce_json

说明 reduce_json 与 to_json 比较类似，也是把复杂数据类型转为json 字符串，但支持抽取部分字段，例如map 字段很多，但计算只需要部分字段，只需要部分字段，通过第二参数定义抽取字段。也可以对字段名和类型进行转换，具体参考实例。

bigdata.reduce_json(data, key所有字名称格式：key=simplekey[:number],...)

实例

select bigdata.reduce_json(map("k1", "hongyi.zhou", "k2", "hangzhou", "k3", "male"), 'k1=name,k2=address:number')
--output
{"name":"hongyi.zhou","address":hangzhou}

5.1.6 json_compress

说明 json元素连接字符串。去掉json 中key,使用特定字符拼接json中key对应值，默认拼接符为 ~~， 如果为json array，每个item拼接字符为 ;,

bigdata.json_compress(json)
bigdata.json_compress(json, fields)
bigdata.json_compress(json, recordSplitChars, fieldSplitChars)
bigdata.json_compress(json, fields, recordSplitChars, fieldSplitChars)

实例

select bigdata.json_compress('[{"name":"zhangsan", "age": 18, "address": "hangzhou"}, {"name":"wangwu", "age": 17, "address": "hangzhou"}]')
--output
hangzhou~~zhangsan~~18;hangzhou~~wangwu~~17

--添加过滤列
select bigdata.json_compress('[{"name":"zhangsan", "age": 18, "address": "hangzhou"}, {"name":"wangwu", "age": 17, "address": "hangzhou"}]','name,address')
--output
zhangsan~~hangzhou;wangwu~~hangzhou

--自定义分割符
select bigdata.json_compress('[{"name":"zhangsan", "age": 18, "address": "hangzhou"}, {"name":"wangwu", "age": 17, "address": "hangzhou"}]',',','-')
--output
hangzhou-zhangsan-18,hangzhou-wangwu-17


--添加过滤列以及自定义分隔符
select bigdata.json_compress('[{"name":"zhangsan", "age": 18, "address": "hangzhou"}, {"name":"wangwu", "age": 17, "address": "hangzhou"}]','name,address',',','-')
--output
zhangsan-hangzhou,wangwu-hangzhou

5.1.7 json_split

说明 字符串分割后转json。 字符串json 数组格式，split 返回数组类型

bigdata.json_split(jsonStr)

实例

select json_split('[{"name":"test"}, {"name":"test1"}]');

5.2 字符串函数

5.2.1 encode_sha512

说明 sha_512算法加密文本数据，加密结果为字节数组，base64编码为字符串返回

bigdata.encode_sha512(加密数据, 加密salt)

实例

select bigdata.encode_sha512("hello", "salt");
--output
acc3054d59588aeff201e46474f717f84cf925e71123acb5ac83bd1b002c4345c266330d86e79e1dba142e51abfcde0bec4b363d39664d883f08c33584ede7ba

5.2.2 get_idcard_info

说明 解析身份证ID，获取省份、城市、县、年龄、性别、出生日期、出生年、出生月、出生日，校验idcard 无效，返回null

bigdata.get_idcard_info(idcard, key1, key2, ...)

在线校验身份证有效：https://shenfen.supfree.net/search.asp?id=430304197802031322

实例

-- 省份、城市、县、年龄、性别、出生日期、出生年、出生月、出生日、身份证是否合法 对应key为：
-- province、city、county、age、gender、birthdate、year、month、day、valid
-- 对应数据类型：string, string, string, int, string, date, int, int, int  
select bigdata.get_idcard_info('330225199002240354', 'province', 'year', 'gender', 'valid') as (province, birthdate, gender, valid);
--output
province    birthdate   gender  valid
浙江          1990        男

5.2.3 get_ip_info

说明 ip 地理位置信息，函数初始化时把所有IP信息全部加载到内存, 需要一点时间，批量计算非常快，IP库信息每天从afraudtech.elfin_geoip_zip表中更新一次

 bigdata.get_ip_info(ip, key1, key2, ...)

实例

-- 对应key为：country, province, city, isp, latitude, longitude
select bigdata.get_ip_info('221.123.137.11', 'country', 'province', 'city', 'isp', 'latitude', 'longitude')
as (country, province, city, isp, latitude, longitude)

5.2.4 md5_hash

说明 求字符串的md5

bigdata.md5_hash(str, encoding)

实例

select bigdata.md5_hash("目标字符串");
--output
97a8d00a8e4662000c3e0e45755f4d2e
--添加编码
select bigdata.md5_hash("目标字符串","utf-8");
--output
1d08ca7b42897730e6ea6e18f6b280e1

5.2.5 user_agent_parser

说明 解析浏览User Agent, 获取browser, browserType, browserVersion, os, osVersion, deviceType, kernel, kernelVersion, netWork, mobile信息

bigdata.user_agent_parser(userAgent, key1, key2, ...)

实例

-- 对应key为：browser, browserType, browserVersion, os, osVersion, deviceType, kernel, kernelVersion, netWork, mobile

select user_agent_parser('Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.110 Safari/537.36', 'browser', 'browserType', 'browserVersion', 'os', 'osVersion', 'deviceType', 'kernel', 'kernelVersion', 'netWork', 'mobile')
    as (browser, browserType, browserVersion, os, osVersion, deviceType, kernel, kernelVersion, netWork, mobile)

5.2.6 regexp_extract_all

说明 找出所有在字符串中匹配的模式

bigdata.regexp_extract_all(haystack, pattern, [index])

实例

select bigdata.regexp_extract_all("vwerasddger", "asd");
--output
asd

5.3 时间函数

5.3.1 dateformat

说明 时间格式转换

dateformat(时间字符串, 当前时间格式, 转换后时间格式)
dateformat(时间戳, 转换后时间格式)

实例

select dateformat('20170513', 'yyyyMMdd', 'yyyy-MM-dd');
--output
2017-05-13

--时间戳
select platformtool.dateformat(1496827831004, 'yyyy-MM-dd HH:mm:ss') from test_users_dt
--output
2017-06-07 17:30:31

5.4 数字函数

5.4.1 random_int

说明 生成整数随机数

bigdata.random_int()
bigdata.random_int(maxValue) #生成[0-maxValue)区间的的随机数

实例

select random_int();
select random_int(10);

5.5 数组函数

5.5.1 array_concat

说明 连接多个数组，返回一个数组

bigdata.array_concat(array1,array2,...)

实例

select bigdata.array_concat(array(1,2,3), array(1,2,3));
--output
[1,2,3,1,2,3]

5.5.2 array_contains

说明 判断元素是否存在于数组中

bigdata.array_contains(array,element)

实例

select array_contains(array(1,2,3), 4);
--false

select array_contains(array(1,2,3), 1);
--output
true

5.5.3 array_distinct

说明 数组去重

bigdata.array_distinct(array)

实例

select bigdata.array_distinct(array(1,2,3,3));
--output
[1,2,3]

5.5.4 array_element_at

说明 取得数组下标元素。正向从0开始，反向从-1开始，若不存在返回空

bigdata.array_distinct(array,index)

实例

--数组下标返回元素，支持正向（0开始）和反向（-1开始），元素不存在返回空
select bigdata.array_element_at(array(1,2,3), -1);
--output
3

select bigdata.array_element_at(array(1,2,3), 0);
--output
0

5.5.5 array_equals

说明 数组相等比较，只有长度相同且每个下标元素值相同才返回true，否则返回false

bigdata.array_equals(array1,array2)

实例

select bigdata.array_equals(array(1,2,3), array(1,2,3));
select
--output
true

bigdata.array_equals(array(1,2,3,4), array(1,2,3));
--output
false

select bigdata.array_equals(array(3,2,1), array(1,2,3));
--output
false

5.5.6 array_from_json

说明 json数组字符串转换为array，若非数组将报错

bigdata.array_from_json(json)

实例

select bigdata.array_from_json('[1, 2, 3]');
--output
['1','2','3']

5.5.7 array_intersect

说明 两个数组取交集，若交集不存在返回空数组

bigdata.array_intersect(array1,array2)

实例

select bigdata.array_intersect(array(1,2,3), array(1,2,4,7,3));
--output
[1,2,3]

select bigdata.array_intersect(array(1,2,3), array(8));
--output
[]

5.5.8 array_join

说明 将数组合成一个字符串，支持对null元素的替换。

bigdata.array_join(array,delimiter)
bigdata.array_join(array,delimiter,null_replacement)

实例

select bigdata.array_join(array(1,2,3,4), '-');
--output
1-2-3-4

--对null元素替换
select bigdata.array_join(array(1,2,null,4), '-', 'x');
--output
1-2-x-4

5.5.9 array_max

返回数组中最大元素值 说明

bigdata.array_max(array)

实例

select bigdata.array_max(array(1,2,3,4));
--output
4

5.5.10 array_min

返回数组中最小元素值 说明

bigdata.array_min(array)

实例

select bigdata.array_min(array(1,2,3,4));
--output
1

5.5.11 array_position

说明 获取元素在数组中的位置，注：从1开始计算。若不存在，返回0

bigdata.array_position(array,element)

实例

select bigdata.array_position(array(0,1,2,3,4), 3);
--output
4

select bigdata.array_position(array(0,1,2,3,4), -1);
--output
0

5.5.12 array_remove

数组删除元素后并返回，若不存在则返回原始数组。 说明

bigdata.array_remove(array,element)

实例

select bigdata.array_remove(array(1,2,3,4,3), 3);
--output
[1,2,4]

--不存在删除元素，返回原始数组
select bigdata.array_remove(array(1,2,3,4), -1);
--output
[1,2,3,4]

5.5.13 array_reverse

说明 数组逆序输出

bigdata.array_reverse(array)

实例

select bigdata.array_reverse(array(1,2,3,4));
--output
[4,3,2,1]

5.5.14 array_slice

说明 数组切片，start从0开始，支持逆向遍历。

bigdata.array_slice(array,start,length)

实例

select bigdata.array_slice(array(1,2,3,4,5,6,7), 1, 3);
--output
[2,3,4]

--逆向遍历
select bigdata.array_slice(array(1,2,3,4,5,6,7), -3, 2);
--outpupt
[5,6]

5.5.15 array_sort

说明 数组正向排序并输出

bigdata.sort(array)

实例

select bigdata.array_sort(array(7,1,8,3,4));
-output
[1,3,4,7,8]

5.5.16 array_value_count

说明 返回数组中某元素值个数。

bigdata.array_value_count(array,element)

实例

select bigdata.array_value_count(array(1,2,2,4), 2);
--output
2

5.5.17 merge_list

说明 合并多个数组，返回二维数组，与merge_set区别是相同的元素不会去重

bigdata.merge_list(array1,array2,...,arrayn)

实例

select bigdata.merge_list(array(array(1,2),array(2,3),array(2,3)));
--output
[[1,2],[2,3],[2,3]]

5.5.18 merge_set

说明 合并多个数组，返回二维数组，与merge_list区别是相同的元素去重

bigdata.merge_set(array1,array2,...,arrayn)
--output
[[1,2],[2,3]]

实例

select bigdata.merge_set(array(array(1,2),array(2,3),array(2,3)));

5.6 数据脱敏函数

5.6.1 mask

说明 数据脱敏方法。

bigdata.mask(value, upperChar, lowerChar, digitChar, otherChar, numberChar, dayValue, monthValue, yearValue)

实例

select mask("abcd-EFGH-8765-4321", "U", "s", "*");
--output
ssss-UUUU-****-****

5.6.2 mask_first_n

说明 前n个字符脱敏

bigdata.mask_first_n(value, charCount, upperChar, lowerChar, digitChar, otherChar, numberChar)

实例

select mask_first_n("1234-5678-8765-4321", 4);
--output
nnnn-5678-8765-4321

5.6.3 mask_last_n

说明 后n个字符脱敏

bigdata.mask_last_n(value, charCount, upperChar, lowerChar, digitChar, otherChar, numberChar)

实例

select mask_last_n("1234-5678-8765-4321", 4);
--output
1234-5678-8765-nnnn

5.6.4 mask_show_first_n

说明 除了前n个字符，剩下的全脱敏

bigdata.mask_show_first_n(value, charCount, upperChar, lowerChar, digitChar, otherChar, numberChar)

实例

select mask_show_first_n("1234-5678-8765-4321", 4);
--output
1234-nnnn-nnnn-nnnn

5.6.5 mask_show_last_n

说明 除了后n个字符，剩下的全脱敏

bigdata.mask_show_last_n(value, charCount, upperChar, lowerChar, digitChar, otherChar, numberChar)

实例

select mask_show_last_n("1234-5678-8765-4321", 4);
--output
nnnn-nnnn-nnnn-4321

5.6.6 mask_hash

说明 将输入转换成随机长度的字符串，当传入的不是字符串返回空

bigdata.mask_hash(value)

实例

select mask_hash('asdasf');
--output
c094f455d7c10c26ce5ef76e166c42391beaec917629a1236ed334da41006d0d

5.7 加解密函数

5.7.1 decode_base64

说明 base64解密

bigdata.decode_base64(str);

实例

select decode_base64('YXNkYXM=');
--output
asdas

5.7.2 des_gen_key

说明 des生成key。全套des加解密需要配合des_gen_key,des_encrypt和des_decrypt一起使用，且同一个key只能加解密同一份数据。

bigdata.des_gen_key();

实例

select des_gen_key();
--output
a/Qsnn9dlHw=

5.7.3 des_encrypt

说明 des加密。全套des加解密需要配合des_gen_key,des_encrypt和des_decrypt一起使用，且同一个key只能加解密同一份数据。

bigdata.des_encrypt(str,deskey)

实例

select des_encrypt('dfgsdf','a/Qsnn9dlHw=');
--output
l2EsEoQxZDY=

5.7.4 des_decrypt

说明 des解密。全套des加解密需要配合des_gen_key,des_encrypt和des_decrypt一起使用，且同一个key只能加解密同一份数据。

bigdata.des_decrypt(str,deskey)

实例

select des_decrypt('l2EsEoQxZDY=','a/Qsnn9dlHw=');
--output
dfgsdf

5.8 地理位置函数

5.8.1 udf_sys_geo_distance

说明

udf_sys_geo_distance(longitude, latitude, point)
（1）作用：用作一个经纬度列和一个固定的坐标点的距离计算
（2）参数说明:第一个参数为经度列名称, 类型double                     
 第二个参数为纬度列名称, 类型double              
 第三个参数为固定坐标点的经纬度，格式=>”经度，维度”, =>”120.85979,30.011984”                       
 返回：返回一个数值，单位为米(M)

实例

--查询（longitude,latitude),（120.85979,30.011984）两点之间的距离
select bigdata.udf_sys_geo_distance(121.85979,32.011984, "120.85979,30.011984");
--output
241933.56454198112

5.8.2 udf_sys_geo_in_cycle

说明

udf_sys_geo_in_cycle(longitude, latitude, point, radius)
（1）作用:做基于地理位置的经纬度画圈
（2）参数说明:第一个参数为经度列名称， 类型double                        
     第二个参数为纬度列名称， 类型double                       
     第三个参数为圆圈中心点的位置，格式=>”经度，维度”, =>”120.85979,30.011984”                        
     第四个参数为圆圈的半径，单位米                        
    返回：返回一个boolean值（3）

实例

--判断点121.85979，32.011984 是否在”120.85979,30.011984”为中心点，半径为5km的圆圈内
select udf_sys_geo_in_cycle(121.85979,32.011984, "120.85979,30.011984", 5000);
--output
false

5.8.3 udf_sys_geo_in_rectangle

说明

udf_sys_geo_in_rectangle(longitude, latitude, pointA, pointB)
（1）作用：用于做基于地理位置的经纬度画矩形
（2）参数说明:第一个参数为经度列名称, 类型double                       
第二个参数为纬度列名称, 类型double                    
第三个参数为矩形的左下角坐标，格式=>”经度，维度”, =>”120.85979,30.011984”                        
第四个参数为矩形的右上角坐标，格式=>”经度，维度”, =>”120.88450,31.21011”                        
返回：返回一个boolean值

实例

--判断点121.85979,32.011984 是否在”120.85979,30.011984”和””120.88450,31.21011””为2个斜角构成的矩形内
select udf_sys_geo_in_rectangle(121.85979,32.011984, "120.85979,30.011984", "120.88450,31.21011");
--output
false