- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Getting parse exception while using get_json_object in Hive
- Labels:
-
Apache Hive
Created 07-11-2016 06:27 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using HDP2.5 TP Sandbox, I created a Hive table using HBaseStorageHandler. Created an another table json_staging and load the json file. Now I want to insert that json file into the first table using simple get_json_object UDF. Getting the following exception. Has anyone experienced this error before, please help
java.lang.Exception: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 5:2 Failed to recognize predicate 'get_json_object'. Failed rule: 'regularBody' in statement
Created 07-11-2016 07:31 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your SQL statement uses a reserved word, key. In general, it is a good practice to use back ticks, e.g. `key` to avoid special keywords or special characters issues, ... These special apostrophes (back ticks) that you can find on upper left of your keyboard. Could you rewrite your query to use at least `key` instead of just key? There may be a similar problem with the use of "$.key"...
Additionally, I would use '$.Foo' instead of "$.Foo", use single apostrophes instead quotes ... Just as a good practice.
Created 07-11-2016 06:32 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@mrizvi could you please attach your code? Thanks.
Created 07-11-2016 06:33 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am doing insert like this:
INSERT OVERWRITE TABLE hbase_table_json SELECT get_json_object(json_staging.json, "$.key") AS key, get_json_object(json_staging.json, "$.driverId") AS driverId, get_json_object(json_staging.json, "$.driverName") AS driverName get_json_object(json_staging.json, "$.eventTime") AS eventTime, get_json_object(json_staging.json, "$.eventType") AS eventType, get_json_object(json_staging.json, "$.latitudeColumn") AS latitudeColumn, get_json_object(json_staging.json, "$.longitudeColumn") AS longitudeColumn, get_json_object(json_staging.json, "$.routeId") AS routeId, get_json_object(json_staging.json, "$.routeName") AS routeName, get_json_object(json_staging.json, "$.truckId") AS truckId FROM json_staging;
Created 07-11-2016 06:54 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
json_staging table has the following data:
{"key": 1, "driverId": "123", "driverName":"James", "eventTime":"2015-08-21 12:23:45.231", "eventType":"Normal", "latitudeColumn":"38.440467", "longitudeColumn":"-122.714431", "routeId":"345", "routeName":"San Francisco to San Diego", "truckId":"67"} |
{"key": 2, "driverId": "352", "driverName":"John", "eventTime":"2015-09-24 10:45:56.289", "eventType":"Abnormal", "latitudeColumn":"33.19587", "longitudeColumn":"-117.379483", "routeId":"315", "routeName":"San Jose to Los Angeles", "truckId":"23"} |
{"key": 3, "driverId": "657", "driverName":"Tim", "eventTime":"2016-05-02 05:45:11.009", "eventType":"Normal", "latitudeColumn":"34.44805", "longitudeColumn":"-119.242889", "routeId":"169", "routeName":"San Mateo to Fremont", "truckId":"29"} |
Created 07-11-2016 06:52 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You are missing a comma after driverName right before get_json_object(json_staging.json, "$.eventTime")
if this resolves this problem, please accept this response.
Created 07-11-2016 06:57 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for pointing that out, but still getting the same exception
Created 07-11-2016 07:31 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your SQL statement uses a reserved word, key. In general, it is a good practice to use back ticks, e.g. `key` to avoid special keywords or special characters issues, ... These special apostrophes (back ticks) that you can find on upper left of your keyboard. Could you rewrite your query to use at least `key` instead of just key? There may be a similar problem with the use of "$.key"...
Additionally, I would use '$.Foo' instead of "$.Foo", use single apostrophes instead quotes ... Just as a good practice.
Created 07-11-2016 08:56 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It ran, I replaced quotes with a single apostrophe and it worked. Thanks a lot.