用以下文本举例：

{"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 ;