大数据之Hive：get_json_object函数使用

目录1.数据2.取出第一个json对象3.取出第一个json的age字段的值4.取出嵌套的json的city的值5.复杂案例1.数据with temp_1 as(select水浒传as book,大郎as name,男as sex,25as age union allselect水浒传as book,西门庆as name,男as sex,47as age)selectbook,concat([,concat_ws(,,collect_list(concat({name:,name, ,sex:,sex, ,age:,age,}))),])as js_strsfromtemp_1groupby book[{name:大郎,sex:男,age:25},{name:西门庆,sex:男,age:47}]2.取出第一个json对象hive(gmall)selectget_json_object([{name:大郎,sex:男,age:25},{name:西门庆,sex:男,age:47}],$[0]);结果是{name:大郎,sex:男,age:25}3.取出第一个json的age字段的值hive(gmall)SELECTget_json_object([{name:大郎,sex:男,age:25},{name:西门庆,sex:男,age:47}],$[0].age);结果是254.取出嵌套的json的city的值withtempas(select{name:John,age:30,address:{city:New York,zip:10001}}asstr)SELECTget_json_object(str,$.name)ASname,get_json_object(str,$.address.city)AScityFROMtemp;------John New York5.复杂案例1建表语句hive(gmall) drop tableifexists dwd_start_log;CREATE EXTERNAL TABLE dwd_start_log(area_code string COMMENT地区编码,brand string COMMENT手机品牌,channel string COMMENT渠道,model string COMMENT手机型号,mid_id string COMMENT设备id,os string COMMENT操作系统,user_id string COMMENT会员id,version_code string COMMENTapp版本号,entry string COMMENT icon手机图标 notice 通知 install 安装后启动,loading_time bigint COMMENT启动加载时间,open_ad_id string COMMENT广告页ID ,open_ad_ms bigint COMMENT广告总共播放时间,open_ad_skip_ms bigint COMMENT用户跳过广告时点,ts bigint COMMENT时间)COMMENT启动日志表PARTITIONED BY(dt string)--按照时间创建分区 stored as parquet--采用parquet列式存储 LOCATION/warehouse/gmall/dwd/dwd_start_log--指定在HDFS上存储位置 TBLPROPERTIES(parquet.compressionlzo)--采用LZO压缩;2数据导入hive(gmall)SEThive.input.formatorg.apache.hadoop.hive.ql.io.HiveInputFormat;insert overwrite table dwd_start_log partition(dt2021-03-20)selectget_json_object(line,$.common.ar),get_json_object(line,$.common.ba),get_json_object(line,$.common.ch),get_json_object(line,$.common.md),get_json_object(line,$.common.mid),get_json_object(line,$.common.os),get_json_object(line,$.common.uid),get_json_object(line,$.common.vc),get_json_object(line,$.start.entry),get_json_object(line,$.start.loading_time),get_json_object(line,$.start.open_ad_id),get_json_object(line,$.start.open_ad_ms),get_json_object(line,$.start.open_ad_skip_ms),get_json_object(line,$.ts)fromods_log where dt2021-03-20and get_json_object(line,$.start)is not null;