Hive SERDE

SERDE is a combination of Serializer and Deserializer i.e SERIALIZER + DESERIALIZER = SERDE. SERDE is popularly used to load from sources storing data in JSON format.

➠ SERDE Table Example: Below example is the table where JSON data will be loaded

CREATE TABLE order_json
(  
   order_id INT,
   order_date STRING,
   cust_id STRING,
   order_status STRING
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS TEXTFILE;

Load SERDE Table: Data file can be downloaded from here.

LOAD DATA local inpath '/Users/username/hive/data_files/order_json.txt' OVERWRITE INTO TABLE order_json;

--Sample data in JSON file
{"order_id":68784,"order_date":"2014-01-14 00:00:00.0","order_status":"COMPLETE"}
{"order_id":68785,"order_date":"2014-01-16 00:00:00.0","cust_id":"10778","order_status":"PENDING"}
{"order_id":68786,"order_date":"2014-01-19 00:00:00.0","cust_id":"1847","order_status":"COMPLETE"}
{"order_id":68787,"order_date":"2014-01-20 00:00:00.0","cust_id":"3219","order_status":"PROCESSING"}

Points to be noted:

Column names of table and attribute names of JSON must match for data to be populated in SERDE table otherwise 'NULL' will be populated.
SERDE load will work even if any of the field is missing from JSON message, example 'cust_id' field is missing in sample data (1st record). 'NULL' will be populated for missing fields.

➠ Common Error: Data load to the table containing numeric datatype will fail with below error if number field in JSON is defined as string (within double quote).

Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException: org.codehaus.jackson.JsonParseException: Current token (VALUE_STRING) not numeric, can not use numeric value accessors

For example: Note the difference between below 2 JSONs messages(marked in BOLD)

Data load containing JSONs like below will fail

{"order_id":68784,"order_date":"2014-01-14 00:00:00.0","cust_id":"10349","order_status":"COMPLETE"}

But Data load containing JSONs like below will work

{"order_id":68784,"order_date":"2014-01-14 00:00:00.0","cust_id":10349,"order_status":"COMPLETE"}

➠ SERDE Table Example(Numeric Data): Below example is the table where JSON data(contain numeric data) will be loaded

CREATE TABLE order_json_number
(  
   order_id INT,
   order_date STRING,
   cust_id STRING,
   order_status STRING
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS TEXTFILE;

Load SERDE Table(Numeric Data): Data file can be downloaded here.

LOAD DATA local inpath '/Users/username/hive/data_files/order_json_number.txt' OVERWRITE INTO TABLE order_json_number;

--Sample data in JSON file
{"order_id":68834,"order_date":"2014-05-01 00:00:00.0","cust_id":6938,"order_status":"COMPLETE"}
{"order_id":68835,"order_date":"2014-05-02 00:00:00.0","cust_id":764,"order_status":"COMPLETE"}
{"order_id":68836,"order_date":"2014-05-03 00:00:00.0","cust_id":8009,"order_status":"PENDING_PAYMENT"}
{"order_id":68837,"order_date":"2014-05-07 00:00:00.0","cust_id":1223,"order_status":"COMPLETE"}