Add data query with Hive-JSON-SerDe

Question

I'm working with hive and i need to add data in json-format. I use https://github.com/rcongiu/Hive-JSON-Serde library. It loads data in hive from file.

~$ cat test.json

{"text":"foo","number":123}
{"text":"bar","number":345}

$ hadoop fs -put -f test.json /user/data/test.json

$ hive

hive> CREATE DATABASE test;

hive> CREATE EXTERNAL TABLE test ( text string )
      ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
      LOCATION '/user/data';

hive> SELECT * FROM test;
OK

foo 123
bar 345

But i need load data from query, like:

insert into table test values {"text": "abc", number: 666}

Who knows how do this?

MMSA · Accepted Answer · 2019-03-28 08:30:54Z

1

The question seems old, however, in case someone looking for an answer:

I tried another approach as following:

Create table test (text string);
LOAD data inpath 'path/test.json' INTO TABLE test;
insert into table test values ("{'text':'abc','number':666}");

The only different is when you need to load values it will be something like:

select get_json_object(str,'$.text') as text1, get_json_object(str,'$.number') as number1 from test;

answered Mar 28, 2019 at 8:30

MMSA

9489 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kim Moritz · Accepted Answer · 2017-11-29 07:09:18Z

0

A SerDe is really intended for use with external tables which read the data from files containing the data. So it will not help you directly insert json data and the insert query you give as an example will not work as such. I suggest that you should either write the data to a file on your hdfs and create an external table on the folder containing the file, or parse incoming data such that you can insert it as columns.

answered Nov 29, 2017 at 7:09

Kim Moritz

1631 silver badge11 bronze badges

Collectives™ on Stack Overflow

Add data query with Hive-JSON-SerDe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related