Search code examples
jsonhiveemrexternal-tables

External table with nested value json

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.

Solution

  • 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';