External table with nested value json

Question

I am trying to create an external table handle data like this:

{"Id":"1","att":{"value":0.5}}

I wrote the following query but it is not working:

CREATE EXTERNAL TABLE IF NOT EXISTS table_1 (Id bigint, att double), path)

but it gives null for the att. How can I refer to the value not to the att in my query ? I am doing this on Emr and Hive.

yes, and i found the solution just change the type of att to struct — Obadah Meslmani, Feb 07 '17 at 08:42

score 1 · Accepted Answer · answered Mar 23 '17 at 09:24

1

Here is an example using JsonSerde in order to give an answer for future users:

CREATE EXTERNAL TABLE IF NOT EXISTS table_1 (
    Id BIGINT, 
    att STRUCT<value:DOUBLE>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION '/location';

answered Mar 23 '17 at 09:24

katronai

540
1
8
25

You're welcome. If this has solved your question please consider [accepting it](https://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this.[*](https://meta.stackoverflow.com/a/251298/3388008) By the way, you could also [answer your own question](https://stackoverflow.blog/2011/07/01/its-ok-to-ask-and-answer-your-own-questions/). – katronai Mar 28 '17 at 05:44

External table with nested value json

1 Answers1