tobycoleman tobycoleman -4 years ago 366
JSON Question

Mapping column names in Hive with JSON serde

I'm using the built-in JSON serde in Hive to create an external table, i.e.

org.apache.hive.hcatalog.data.JsonSerDe
. My input JSON contains a field called
last
, which I would like to map to a different column name in my table, since
last
is a reserved keyword.

Is this possible to do with
SERDEPROPERTIES
? I can find examples of how to do this with the OpenX Json serde, but not the hive one.

Currently I'm creating my table like this

CREATE EXTERNAL TABLE my_table (
a string,
b string,
last string)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 's3://my-bucket/my-folder/data'

Answer Source

last is a non-reserved keyword.
No issue here.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Keywords,Non-reservedKeywordsandReservedKeywords

hive> dfs -cat /user/hive/warehouse/my_table/*;
{"a":"hello","b":"world","last":"!"}

create external table my_table 
(
    a       string
   ,b       string
   ,last    string
)
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    location '/user/hive/warehouse/my_table'
;

select * from my_table
;

+------------+------------+---------------+
| my_table.a | my_table.b | my_table.last |
+------------+------------+---------------+
| hello      | world      | !             |
+------------+------------+---------------+

For reserved keywords, qualification with ` (Grave accent) solves the issue.

hive> dfs -cat /user/hive/warehouse/my_table_2/*;
{"and":"X","or":"Mix","not":"Drix"}

create external table my_table_2
(
    `and`   string
   ,`or`    string
   ,`not`   string
)
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    location '/user/hive/warehouse/my_table_2'
;

select * from my_table_2
;

+----------------+---------------+----------------+
| my_table_2.and | my_table_2.or | my_table_2.not |
+----------------+---------------+----------------+
| X              | Mix           | Drix           |
+----------------+---------------+----------------+
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download