question Re: Getting parse exception while using get_json_object in Hive in Archives of Support Questions (Read Only)

Getting parse exception while using get_json_object in Hive

mrizvi — Tue, 12 Jul 2016 01:27:26 GMT

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

Re: Getting parse exception while using get_json_object in Hive

SQLShaw — Tue, 12 Jul 2016 01:32:26 GMT

@mrizvi could you please attach your code? Thanks.

Re: Getting parse exception while using get_json_object in Hive

mrizvi — Tue, 12 Jul 2016 01:33:34 GMT

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;

Re: Getting parse exception while using get_json_object in Hive

cstanca — Tue, 12 Jul 2016 01:52:20 GMT

@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.

Re: Getting parse exception while using get_json_object in Hive

mrizvi — Tue, 12 Jul 2016 01:54:43 GMT

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

Re: Getting parse exception while using get_json_object in Hive

mrizvi — Tue, 12 Jul 2016 01:57:41 GMT

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

Re: Getting parse exception while using get_json_object in Hive

cstanca — Tue, 12 Jul 2016 02:31:06 GMT

@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.

Re: Getting parse exception while using get_json_object in Hive

mrizvi — Tue, 12 Jul 2016 03:56:14 GMT

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