I need to define a Hive external table onto a JSON file that has @ signs in its elements, e.g.
{ "data": { "@type": "person", "name": "Phil", "job": "Programmer" } }
This works:
create external table sandbox.test_table
( data STRUCT<name:STRING,job: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 's3a://bucket/DEV/data/raw/test/';
However this misses out the @type element, I've tried these:
data STRUCT<@type:STRING,name:STRING,job:STRING>
data STRUCT<`@type`:STRING,name:STRING,job:STRING>
Neither of them work. Any suggestions how I can do this, or do I need to preprocess the JSON to remove the @ from the element?