Json数据进入蜂箱堆大小错误

Question

我正在使用nifi从facebook获得嵌套的json数据。我已经在hive中创建了一个表，并使用命令加载了数据。

CREATE TABLE abmediaanalysis (id string, posts struct< data:array<struct< message:string, shares:struct< count:int>, id:string, reactions:struct< data:array<struct< name:string,id:string>>, paging:struct< cursors:struct< before:string,after:string>, next:string>>, likes:struct< data:array<struct< id:string>>, paging:struct< cursors:struct< before:string,after:string>, next:string>>>>, paging:struct< previous:string,next:string>>, feed struct< data:array<struct< permalink_url:string,message:string,id:string>>, paging:struct< previous:string,next:string>>)ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

load data local inpath '/home/10879/facebook1480479682880.json' overwrite into table abmediaanalysis;

我还添加了jar文件json 1.3.8-jar-with-dependencies.jar，但是当我使用横向视图打印所有列时，我得到的是java堆大小错误。我也增加了堆的大小，但是仍然有相同的错误。

select id,posts_message,posts_share_count,posts_id,feed_data_permalink_url,feed_data_message,feed_data_id,reaction_data_name,reaction_data_id,posts_likes_data_id from abmediaanalysis
LATERAL VIEW explode(posts.data.message)MSG as posts_message
LATERAL VIEW explode(posts.data.shares.count)CT as posts_share_count
LATERAL VIEW explode(posts.data.id) I as posts_id
LATERAL VIEW explode(feed.data.permalink_url) PU as feed_data_permalink_url
LATERAL VIEW explode(feed.data.message) MSG as feed_data_message
LATERAL VIEW explode(feed.data.id) I as feed_data_id
LATERAL VIEW explode(posts.data.reactions) NM as posts_reactions_name
LATERAL VIEW explode(posts_reactions_name.data.name) NM as reaction_data_name
LATERAL VIEW explode(posts_reactions_name.data.id) NM as reaction_data_id
LATERAL VIEW explode(posts.data.likes) I as likes_data_id
LATERAL VIEW explode(likes_data_id.data.id) I as posts_likes_data_id;

当我试图打印两或三列而不是显示616条记录时，它显示了大约15625条记录。有人能帮忙解决这个问题吗?是否有机会将上述json数据直接从nifi加载到hive表中？如果是的话你能告诉我。

提前感谢

Answer 1

请允许我建议您采用另一种方法，将整个JSON字符串作为String数据类型加载到一个列中，作为外部表的数据类型。

例如：

CREATE EXTERNAL TABLE json_data_table (
    id String,
    json_data String
    )   
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001' LINES TERMINATED BY '\n' STORED AS TEXTFILE 
LOCATION '/path/to/json';

使用get_json_object提取单个列。例如。

如果json_data列在JSON字符串下面

{"store":
  {"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
   "bicycle":{"price":19.95,"color":"red"}
  },
 "email":"amy@only_for_json_udf_test.net",
 "owner":"amy"
}

下面的查询获取

SELECT get_json_object(json_data, '$.owner') FROM json_data_table;

返回amy

通过这种方式，您可以从表中提取每个json元素作为列。