Getting parse exception while using get_json_object in Hive

[#1]

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

[#2]

@mrizvi could you please attach your code? Thanks.

[#3]

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;

[#4]

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"}

[#5]

@mrizvi

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.

[#6]

Thanks for pointing that out, but still getting the same exception

[#7 · Accepted solution]

@mrizvi

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.

[#8]

It ran, I replaced quotes with a single apostrophe and it worked. Thanks a lot.