Hive-JSON 原始数据映射 Hive 表结构

最新推荐文章于 2022-01-06 16:01:31 发布

转载最新推荐文章于 2022-01-06 16:01:31 发布 · 1.4k 阅读

hive 专栏收录该内容

56 篇文章

订阅专栏

本文介绍如何在Hive中创建JSON格式的数据表，并演示了如何使用Hive SQL进行数据查询。通过添加依赖jar包解决SerDe验证错误，同时展示了从JSON表中选取特定字段的方法。

2017年08月18日 15:29:35 阅读数：1446 标签： hive json hadoop 大数据更多

个人分类：大数据-Hive

参考文档

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-RowFormats&SerDe

创建 HIVE-JSON 表

-- 添加依赖 jar
ADD JAR /data/tmp/liwei/hive-hcatalog-core-2.1.1.jar;
DROP TABLE IF EXISTS `tmp.liwei_json_table`;
CREATE TABLE IF NOT EXISTS tmp.liwei_json_table (
`data_param` map<string, string>,
`account_param` map<string, string>,
`role_param` map<string, string>,
`event` map<string, string>
)
comment 'liwei_json_table'
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE
;
--报错 return code 1 from org.apache.hadoop.hive.ql.exec.ddltask. cannot validate serde: org.apache.hive.hcatalog.data.jsonserde
--解决方案，添加对应 jar ，并对添加的 jar 授执行权限

创建测试 JSON 文件数据

添加测试JSON数据文件 hive_json_table.json（需要压缩为一行）

{
"data_param": {
"game_key_s": "sbkcq",
"game_version_name_s": "1.23",
"game_version_code_s": "1.23b",
"data_from_s": "s",
"create_unix": "1501641267",
"platform_s": "ios",
"region_s": "101",
"server_s": "1011",
"data_unix": "1501641267",
"index": "event",
"type": "progression_8_l",
"data_date": "2017-08-02",
"data_time": "10:34:27",
"data_date_partition": "20170802"
},
"account_param": {
"use_id_s": "1100",
"account_id_s": "1",
"account_name_s": "lw",
"channel_s": "baidu"
},
"role_param": {
"role_id_s": "baidu|110023|1|1011",
"role_name_s": "傻大姐",
"level_i": "90",
"role_class_s": "FaShi",
"role_race_s": "RZ",
"fighting_i": "201289",
"remain_currency_l": "198",
"vip_level_i": 2
},
"event": {
"t0": "world",
"t1": "world_z1",
"t2": "world_z1-2",
"t3": "world_z1-2-killbos",
"t4": "difficult",
"t5": "succeed",
"r_l": "123000"
}
}

{"data_param":{"game_key_s":"sbkcq","game_version_name_s":"1.23","game_version_code_s":"1.23b","data_from_s":"s","create_unix":"1501641267","platform_s":"ios","region_s":"101","server_s":"1011","data_unix":"1501641267","index":"event","type":"progression_8_l","data_date":"2017-08-02","data_time":"10:34:27","data_date_partition":"20170802"},"account_param":{"use_id_s":"1100","account_id_s":"1","account_name_s":"lw","channel_s":"baidu"},"role_param":{"role_id_s":"baidu|110023|1|1011","role_name_s":"傻大姐","level_i":"90","role_class_s":"FaShi","role_race_s":"RZ","fighting_i":"201289","remain_currency_l":"198","vip_level_i":2},"event":{"t0":"world","t1":"world_z1","t2":"world_z1-2","t3":"world_z1-2-killbos","t4":"difficult","t5":"succeed","r_l":"123000"}}

上传数据到 HDFS

hadoop fs -D speed.limit.kb=10240 -copyFromLocal  /data/tmp/liwei/hive_tmp/hive_json_table.json /user/hive/warehouse/tmp.db/liwei_json_table/

执行测试查询操作

hive> select * from tmp.liwei_json_table;
OK
{"game_key_s":"sbkcq","game_version_name_s":"1.23","game_version_code_s":"1.23b","data_from_s":"s","create_unix":"1501641267","platform_s":"ios","region_s":"101","server_s":"1011","data_unix":"1501641267","index":"event","type":"progression_8_l","data_date":"2017-08-02","data_time":"10:34:27","data_date_partition":"20170802"}{"use_id_s":"1100","account_id_s":"1","account_name_s":"lw","channel_s":"baidu"} {"role_id_s":"baidu|110023|1|1011","role_name_s":"傻大姐","level_i":"90","role_class_s":"FaShi","role_race_s":"RZ","fighting_i":"201289","remain_currency_l":"198","vip_level_i":"2"} {"t0":"world","t1":"world_z1","t2":"world_z1-2","t3":"world_z1-2-killbos","t4":"difficult","t5":"succeed","r_l":"123000"}
Time taken: 1.292 seconds, Fetched: 1 row(s)
hive>
>
>
> select data_param from tmp.liwei_json_table;
OK
{"game_key_s":"sbkcq","game_version_name_s":"1.23","game_version_code_s":"1.23b","data_from_s":"s","create_unix":"1501641267","platform_s":"ios","region_s":"101","server_s":"1011","data_unix":"1501641267","index":"event","type":"progression_8_l","data_date":"2017-08-02","data_time":"10:34:27","data_date_partition":"20170802"}
Time taken: 0.121 seconds, Fetched: 1 row(s)
hive> select data_param["game_key_s"] from tmp.liwei_json_table;
OK
sbkcq