Create Hive Table from JSON Files

[#1]

Hi All,

I am trying to create a HIVE table from JSON file. Here , JSON file is extracted from Cloudera Manager (JSON file Content: IMPALA query report). 

We use CDH5.9 

Could you please help me on how to create a hive/impala table which reads the data from JSON file as underlying file?

JSON file content will look like below,

{
"queries" : [ {
"queryId" : "75462e3e4c5d7694:37c50ff300000000",
"statement" : "select * from XXXXXXXXXXXXX",
"queryType" : "QUERY",
"queryState" : "EXCEPTION",
"startTime" : "2018-01-22T15:51:24.231Z",
"endTime" : "2018-01-22T16:33:27.962Z",
"rowsProduced" : 0,
"attributes" : {
"thread_storage_wait_time" : "1",
"session_id" : "fd49d2f82ac32576:bcb0c36d65a6d98f",
"hdfs_bytes_read_remote_percentage" : "0",
"stats_missing" : "false",
"thread_total_time" : "361",
"thread_network_send_wait_time_percentage" : "0",
"delegated_user" : "XXXXXX",
"thread_network_receive_wait_time_percentage" : "9",
"network_address" : "XXXXXXXXXXX:38571",
"pool" : "default-pool",
"hdfs_bytes_read_from_cache" : "0",
"hdfs_bytes_read_local_percentage" : "100",
"hdfs_scanner_average_bytes_read_per_second" : "0.0",
"hdfs_bytes_read_from_cache_percentage" : "0",
"bytes_streamed" : "12535",
"session_type" : "HIVESERVER2",
"cm_cpu_milliseconds" : "326.997635",
"thread_network_receive_wait_time" : "31",
"estimated_per_node_peak_memory" : "33554432",
"query_status" : "Query 75462e3e4c5d7694:37c50ff300000000 expired due to client inactivity (timeout is 10m)\n",
"thread_storage_wait_time_percentage" : "1",
"memory_aggregate_peak" : "574801.92",
"admission_result" : "Admitted immediately",
"hdfs_average_scan_range" : "229336.0",
"planning_wait_time" : "7",
"oom" : "false",
"memory_accrual" : "8.015872E7",
"memory_spilled" : "0",
"thread_cpu_time_percentage" : "90",
"admission_wait" : "0",
"file_formats" : "TEXT/NONE",
"hdfs_bytes_read_local" : "229336",
"hdfs_bytes_read_short_circuit" : "229336",
"hdfs_bytes_read_short_circuit_percentage" : "100",
"planning_wait_time_percentage" : "0",
"client_fetch_wait_time" : "2523686",
"client_fetch_wait_time_percentage" : "100",
"memory_per_node_peak_node" : "XXXXXX:22000",
"memory_per_node_peak" : "574801.92",
"connected_user" : "hue/XXX@xx",
"hdfs_bytes_read_remote" : "0",
"thread_network_send_wait_time" : "0",
"impala_version" : "impalad version 2.7.0-cdh5.9.2 RELEASE (build 2f7871169d894fab16f8a2fb99f2e34f0df8763d)",
"thread_cpu_time" : "326",
"hdfs_bytes_read" : "229336"
},
"user" : "XXXXXX",
"coordinator" : {
"hostId" : "XXXXXXXX"
},
"detailsAvailable" : true,
"database" : "XXXXX",
"durationMillis" : 2523731
} ],
"warnings" : [ ]
}

Thanks,

Venkatesh Kumar

[#2]

@Venkatesh_Kumar

Pls try this options

https://github.com/rcongiu/Hive-JSON-Serde#jsonserde---a-readwrite-serde-for-json-data

[#3]

We recommend using the JsonSerDe that comes with Hive.

https://github.com/apache/hive/blob/3972bf05159581d6aa515ba5dd9e75d59ac62a45/hcatalog/core/src/main/...

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-RowFormats&Ser...

You will have to install the JAR file into the Hive auxiliary directory.  The JAR file is hive-hcatalog-core.jar and can be found in several places within the CDH distribution.

https://www.cloudera.com/documentation/enterprise/5-13-x/topics/cm_mc_hive_udf.html

[#4]

This process will become easier in a future version of CDH.

https://issues.apache.org/jira/browse/HIVE-19899

[#5]

David @David M.,

Actually, I'm looking to get the Impala logs with a querytext, start time, end time, memory, username, etc.... for tracking the user queries and to create the live dashboards like Cloudera Navigator but with free of cost.

We have Spark or UDF to create the table from JSON in Hive.

>>> df = sqlContext.read.json ("/user/venkata/lineage.json")

>>> df.saveAsTable("secure.lineage")

Thanks,

Venkat

[#6]

Kumar @Venkatesh_Kumar, 

How did you get the IMPALA query report? I'm trying to download the Impala queries, So Could you help me.

Is there any location or way to download it?

I found the /var/log/impalad/lineage/ location for logs but I'm not able to stream those logs to table.

Thanks,

Venkat.

[#7]

@vratmuri

You can get it from Cloudera Manager.

Go to CM -> Impala -> Queries (tab) -> choose the time frame and click on 'export' button

[#8]

Saranvisa @saranvisa,

Thanks for the reply.

Not just for one time. Export is just manually thing but I would like to do streaming.

In my cluster, the minimum Impala queries : 300queries/30mints. huge. So....

Thanks,

Venkat

[#9]

@vratmuri

Oh then you can use cloudera API

Link for cloudera API reference:

https://www.cloudera.com/documentation/enterprise/5-9-x/topics/cm_intro_api.html

Link for specific to service properties (you may need to explore little for impala query). It may help you

https://www.cloudera.com/documentation/enterprise/5-9-x/topics/cm_intro_api.html#xd_583c10bfdbd326ba...