hive 增加字段 sql hive中增加字段

        昨天的推文中介绍了几个常用的hive查询函数，今天在整理工作中写的SQL，又发现几个比较常用到的函数，这里分享给大家

get_json_object()

get_json_object(STRING json_string, STRING path)

使用场景：一般用于对APP进行埋点后，埋点所带的参数以json的形式存储在字段中，可以使用get_json_object进行解析，通过不断解析json的层级关系，获得我们需要的值

例：有一段json数据，需要获取_ldate，以及uuid字段

{
    "_ldate":"2020-08-05 00:00:03",
    "_lkey":"data-get",
    "_lval":{
        "_hdata":{
            "channo":"118",
            "uuid":"12345678",
            "mac":"00:00:00:00:00:00"
        },
        "ip":"127.0.0.1"
    },
    "_ltag":"xx"
}

select 
    get_json_object(json_text,'$._ldate') as _ldate,
    get_json_object( get_json_object(get_json_object( text,'$._lval' ), '$._hdata') , '$.uuid') as uuid
from sdklog

结果如下：

json_tuple()

json_tuple(text,'column1')

使用场景：可以理解为更加高效的get_json_object(),将json数据中的多个key-value取出来作为字段，在层级关系多的json数据中，可以搭配get_json_object()使用

例：将上面的json数据中的channel,uuid,mac同时取出作为字段使用

SELECT 
       b.channo,
       b.uuid,
       b.mac
FROM sdklog a
LATERAL VIEW json_tuple(get_json_object(get_json_object(a.json_text,'$._lval'), '$._hdata'),'channo','uuid','mac') b AS  channo,uuid,mac

结果如下：

split()

split(STRING str, STRING pat)

使用场景：通过特定符号切割字段，返回一个数组，可以理解为Excel中的分列或者python中的str.split()

例：

select split('hello&World','&')
-- >>> ["hello","World"]

regexp_extract()

regexp_extract(str, regexp[, idx])

使用场景：使用正则表达式来匹配目标字符串。

其中：

• str是被解析的字符串，可以是字段名
• regexp是正则表达式
• idx是返回结果 取表达式的哪一部分  默认值为1。
• 0表示把整个正则表达式对应的结果全部返回
• 1表示返回正则表达式中第一个() 对应的结果 以此类推

例：

select regexp_extract("userid=1234;ip=127.0.0.1;mac=f:2:2:x","userid=(.*?);") as userid from table
-- >>> 1234

NVL()

NVL(T value, T default_value)

使用场景：如果E1为NULL，则函数返回E2，否则返回E1本身,如果两个参数都为NULL ，则返回NULL。

例：表中有imei(安卓设备号)、idfv(IOS设备号)两个字段，当os=IOS时，imei为NULL，当os=android时，idfv为NULL，需要新建一个字段统一存储设备号

有两种写法：

select os,NVL(imei,idfv) from table;
select os,if(os='android',imei,idfv) from talbe;

parse_url()

parse_url(STRING urlString, STRING partToExtract [, STRING keyToExtract])

使用场景：主要是针对URL进行解析

select 
  parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') --返回'facebook.com',
  parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') --返回'query=1',
  parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY','query') --返回'1',
  parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'REF') --返回'Ref',
  parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'PROTOCOL') --返回'http'
from table;

cast()

cast(a as T)

使用场景：修改字段的类型

• CHAR[(N)] 字符型
• DATE 日期型
• DATETIME 日期和时间型
• DECIMAL float型
• SIGNED int
• TIME 时间型

例：

SELECT cast(12345 as STRING) as str1,
  cast('123321' as int) as int1,
  cast(123.12 as FLOAT) as float1,
  cast('2020-08-05' as DATE) as date1,
  cast('12.3633463' AS DECIMAL) as decima1
from table

结果如下：

round()

round(DOUBLE a [, INT d])

使用场景：保留小数点后N位，可搭配cast()一起使用

例：将下列数字保留两位小数：500.12345

SELECT round(1235.23452, 2),
       round(cast('1235.23452' AS float),2);

结果如下：

以上就是工作中常用到的hive SQL函数，更多高阶的用法后期持续更新~