Hive常见用法：get_json_object，case...when

1 hive

// hive
-- 删除表
DROP TABLE [IF EXISTS] table_name;

 

1.1 get_json_object(string json_string, string path) 用法

// json 格式数据
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"}

1.2 case … when … end

-- 方法1
case 
when tb1.os = 'android' then 'android'
when tb1.os = 'ios' then 'iPhone'
else 'PC'
end as os,

-- 方法2
case tb1.os
when 'android' then 'android'
when 'ios' then 'iPhone'
else 'PC'
end as os,

2 时间相关函数

-- 日期加1
SELECT date_add('2021-01-21', 1)