I am trying to create Hive Table for for given multiline JSON. But actual result is not similar to expected result.
SAMPLE JSON FILE :
{
“name”: “Adil Abro”,
“language”: “Sindhi”,
“id”: “LJRIULRNJFCNZJAJ”,
“bio”: “Etiam malesuada blandit erat, eget ullamcorper risus ligula tristique libero.”,
“version”: “9.32”
}
{
“name”: “Afonso Vilarchán”,
“language”: “Galician”,
“id”: “JMCL0CXNXHPL1GBC”,
“bio”: “Fusce eu ultrices elit, Nunc tincidunt laoreet laoreet.”,
“version”: “5.21”
}
CREATE HIVE TABLE STATEMENT :
USING JAVA
createHQ = String.format(“CREATE EXTERNAL TABLE IF NOT EXISTS %s (name
string, language
string, id
string, bio
string, version
string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ESCAPED BY ‘\’ COLLECTION ITEMS TERMINATED BY ‘_’ MAP KEYS TERMINATED BY ‘:’ LINES TERMINATED BY ‘n’ NULL DEFINED AS ‘null’ STORED AS INPUTFORMAT ‘org.apache.hadoop.mapred.TextInputFormat’ OUTPUTFORMAT ‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’ LOCATION ‘%s’ TBLPROPERTIES (“serialization.null.format”=””)”, tableName, outputLocation.trim());
CREATED HIVE TABLE WITH ABOVE QUERY –
ACTUAL OUTPUT FROM ABOVE QUERY –
+————————————–+———————–+—-+—-+——-+
|name |language |id |bio |version|
+————————————–+———————–+—-+—-+——-+
|{ |null |null|null|null |
| “name”: “Adil Abro” |null |null|null|null |
| “language”: “Sindhi” |null |null|null|null |
| “id”: “LJRIULRNJFCNZJAJ” |null |null|null|null |
| “bio”: “Etiam malesuada blandit erat| eget ullamcorper…” |null|null|null |
| “version”: “9.32” |null |null|null|null |
|} |null |null|null|null |
|{ |null |null|null|null |
| “name”: “Afonso Vilarchán” |null |null|null|null |
| “language”: “Galician” |null |null|null|null |
| “id”: “JMCL0CXNXHPL1GBC” |null |null|null|null |
| “bio”: “Fusce eu ultrices elit | Nunc tincidunt …” |null|null|null |
| “version”: “5.21” |null |null|null|null |
|} |null |null|null|null |
+————————————–+———————–+—-+—-+——-+
EXCEPTED TABLE –
each field should be there in respective coloumn, but its not reading like that
+———————+——————+—————–+———————–+——-+
|name |language |id |bio |version|
+———————+——————+—————–+———————–+——-+
| Adil Abro |Sindhi |LJRIULRNJFCNZJAJ | Etiam ….. | 9.32 |
| Afonso Vilarchán |Galician |JMCL0CXNXHPL1GBC | Fusce eu ultric….. | 5.21 |
+———————+——————+—————–+———————–+——-+
I am trying to create hive table for multiline json data. where my expected table is not matching with actual table/output
Yadav Anil Contractor is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.