用以下文本举例:
{"name":"Yin", "address":{"city":"Columbus","state":"Ohio"}}
{"name":"Michael", "address":{"city":null, "state":"California"}}
hdfs路径为:/user/maokaimin/jsontest/json.json
方法1:
建表
create external table maokaimin.jsontest (jsonObject string) location '/user/maokaimin/jsontest/json.json';
查询
SELECT
v1.name, v2.city, v2.state
FROM maokaimin.jsontest
LATERAL VIEW json_tuple(jsontest.jsonObject, 'name', 'address') v1
as name, address
LATERAL VIEW json_tuple(v1.address, 'city', 'state') v2
as city, state;
方法2:
使用json-serde包
hadoop fs -put json-serde-1.1.9.9-Hive1.2-jar-with-dependencies.jar hdfs:///xxxxx/script/hive_udf/
修改thrift-server启动参数
--jars hdfs:///xcardata/script/hive_udf/json-serde-1.1.9.9-Hive1.2-jar-with-dependencies.jar
注:以上环境为ambari,CDH也类似。分别在spark、Hive对应路径copy这个jar包或者在clouderamanager、ambari对应路径添加jar包即可,详细不在描述。
建表:
CREATE EXTERNAL table maokaimin.jsontest2(
name string, address STRUCT<city:String,state:string> )ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION '/user/maokaimin/jsontest/json.json';
或者
CREATE EXTERNAL TABLE maokaimin.jsontest2
(`name` STRING,
`address` STRING)
partitioned by(day string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE LOCATION '/user/maokaimin/jsontest/json.json';
查询
select name,address from maokaimin.jsontest2 limit 10 ;