gpt4 book ai didi

json - 为嵌套的 JSON 数据创建 Hive 表

转载 作者:可可西里 更新时间:2023-11-01 16:24:36 26 4
gpt4 key购买 nike

我无法将嵌套的 JSON 数据加载到 Hive 表中。有人可以帮我吗?以下是我尝试过的:

示例输入:

{"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 语句面临以下错误:

FAILED: Error in metadata: Cannot validate serde: org.apache.hive.hcatalog.data.JsonSerDe FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

有人可以帮帮我吗?提前感谢您的帮助。

最佳答案

你可以使用org.openx.data.jsonserde.JsonSerDe类来获取json数据

你可以从http://www.congiu.net/hive-json-serde/1.3.6-SNAPSHOT/cdh4/下载jar文件

并执行以下步骤

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

关于json - 为嵌套的 JSON 数据创建 Hive 表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/45233084/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com