How to load an input file which contains an array of Json objects in Hive?

Question

I have a usecase where I am getting an input json file. The file has an array of json -

[{json1},{json2},{json3},{json4}, .... 100 json responses]

The sample of structure of json 1,2,3,4.. is

{"AuthorisedSenderId": "1", "cid":"1", "id":"1" }

I created a table

CREATE EXTERNAL TABLE db1.sample_table(
authorisedsenderid string, 
cid string, 
id string)
ROW FORMAT SERDE 
  'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs:XXXX'

I could successfully load the input file if the file had only json1 (without the array).

LOAD DATA INPATH 'filelocation' OVERWRITE INTO TABLE db1.sample_table

but if the input file contains an array of json, unable to load.

Could you please help me define the CREATE TABLE command to ingest array of json?

Pre-process the file: remove square brackets and replace comma after each JSON with newlines — leftjoin
– leftjoin, Commented Nov 24, 2018 at 6:40

Gaurang Shah · Accepted Answer · 2018-11-30 00:31:34Z

You will have to make a small modification to your file in order to process using JSON Serde.

Current Content:

[{"AuthorisedSenderId": "1", "cid":"1", "id":"1" },{"AuthorisedSenderId": "2", "cid":"2", "id":"2" }]

Modified Content::

{"test":[{"AuthorisedSenderId": "1", "cid":"1", "id":"1" },,{"AuthorisedSenderId": "2", "cid":"2", "id":"2" }]}

added {"test": at the beginning and } added at the end.

And then you can create table as mentioned below.

Hive Table

CREATE TABLE x (
  test array<struct<authorisedsenderid:string, cid:string, id:string>>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

However, if you don't want to Modify File and if you can use spark, it would be much easier as you won't need to change anything in json file.

Code

df = spark.read.json("/tmp/sample_table/table/sample.json")
df.write.saveAsTable("db1.sample_table")

Data:

[{"AuthorisedSenderId": "1", "cid":"1", "id":"1" },{"AuthorisedSenderId": "2", "cid":"2", "id":"2" }]

Output

Collectives™ on Stack Overflow

How to load an input file which contains an array of Json objects in Hive?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related