为嵌套的json数据创建配置单元表

iezvtpos 于 2021-05-29 发布在 Hadoop

关注(0)|答案(2)|浏览(326)

我无法将嵌套的json数据加载到配置单元表中。有人能帮我吗？下面是我尝试过的：
样本输入：

{"DocId":"ABC","User1":{"Id":1234,"Username":"sam1234","Name":"Sam","ShippingAddress":{"Address1":"123 Main St.","Address2":null,"City":"Durham","State":"NC"},"Orders":[{"ItemId":6789,"OrderDate":"11/11/2012"},{"ItemId":4352,"OrderDate":"12/12/2012"}]}}

在Hive（cdh3）上：

ADD JAR /usr/lib/hive/lib/hive-serdes-1.0-SNAPSHOT.jar;

CREATE TABLE json_tab(
    DocId string,
    user1 struct<Id: int, Username: string, Name:string,ShippingAddress:struct<address1:string,address2:string,city:string,state:string>,orders:array<struct<ItemId:int,orderdate:string>>>
)
ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe'
STORED AS TEXTFILE;  

hive> select * from json_tab;
OK
NULL    null

我要走了 NULL 他在这儿。
还尝试了hcatalog jar：

ADD JAR /home/training/Desktop/hcatalog-core-0.11.0.jar;

 CREATE TABLE json_tab(
    DocId string,
    user1 struct<Id: int, Username: string, Name:string,ShippingAddress:struct<address1:string,address2:string,city:string,state:string>,orders:array<struct<ItemId:int,orderdate:string>>>
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe';

但面对我的错误 create table 声明：
失败：元数据错误：无法验证serde:org.apache.hive.hcatalog.data.jsonserde失败：执行错误，从org.apache.hadoop.hive.ql.exec.ddltask返回代码1
有人能帮我吗？提前谢谢你的帮助。

hadoop Hive hdfs JSON

来源：https://stackoverflow.com/questions/45233084/create-hive-table-for-nested-json-data

2条答案

按热度按时间

irtuqstp1#

您可以使用org.openx.data.jsonserde.jsonserde类来创建json数据
您可以从下载jar文件http://www.congiu.net/hive-json-serde/1.3.6-snapshot/cdh4/
并执行以下步骤

add jar /path/to/jar/json-serde-1.3.6-jar-with-dependencies.jar;

CREATE TABLE json_tab(
    DocId string,
    user1 struct<Id: int, Username: string, Name:string,ShippingAddress:struct<address1:string,address2:string,city:string,state:string>,orders:array<struct<ItemId:int,orderdate:string>>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

LOAD DATA LOCAL INPATH  '/path/to/data/nested.json' INTO TABLE json_tab;

SELECT DocId, User1.Id, User1.ShippingAddress.City as city,
User1.Orders[0].ItemId as order0id,
User1.Orders[1].ItemId as order1id from json_tab;

result
ABC     1234    Durham  6789    4352

赞(0）回复(0）举报 2021-05-29

yrdbyhpb2#

I was getting same exception.

我加了下面的jar，它对我有用。

ADD JAR /home/cloudera/Data/json-serde-1.3.7.3.jar;
ADD JAR /home/cloudera/Data/hive-hcatalog-core-0.13.0.jar;

赞(0）回复(0）举报 2021-05-29

我来回答

为嵌套的json数据创建配置单元表

2条答案

相关问题

热门标签

最新问答