Support Questions
Find answers, ask questions, and share your expertise
Announcements
Check out our newest addition to the community, the Cloudera Innovation Accelerator group hub.

How to create HAWQ external table with complex json data

Hi,

we want to access complex json data via a hawq external table.

JSON structure:

{ "result": { "step": 1800, "start_time": 1491174000, "end_time": 1491260400, "curves": [ { "color": "#f0f0f0", "rrddata": [ 33672800000, 33672800000, 33672800000, 33672800000 ], "line_type": "area", "title": "RAM installed" }, { "color": "#e0e0e0", "rrddata": [ 4302300000, 4302300000, 4302300000, 4302300000 ], "line_type": "stack", "title": "Swap installed" }, { "color": "#80ff40", "rrddata": [ 16683600000, 16690000000, 16690900000, 16696200000 ], "line_type": "area", "title": "RAM used" }, { "color": "#408f20", "rrddata": [ 1088450000, 1088480000, 1087960000, 1088710000 ], "line_type": "stack", "title": "Swap used" }

] }, "result_code": 0 }

4 REPLIES 4

Contributor

Hi Timo, have you tried this yet (need to be on HDB 2.0.1 or above)? Any challenges with it?

https://hdb.docs.pivotal.io/220/hawq/pxf/JsonPXF.html

Hi Vineet, We use HDB 2.2 and have tried without success the hints of this link.

We tried this without success:

CREATE EXTERNAL TABLE kum.ext_checkmk_memory ( result_code integer, "result.step" integer, "result.start_time" integer, "result.end_time" integer, "result.curves[0].color" text, "result.curves[0].rrddata[0]" integer, "result.curves[0].rrddata[1]" integer, "result.curves[0].rrddata[2]" integer, "result.curves[0].rrddata[3]" integer, "result.curves[0].line_type" text, "result.curves[0].title" text, "result.curves[1].color" text, "result.curves[1].rrddata[0]" integer, "result.curves[1].rrddata[1]" integer, "result.curves[1].rrddata[2]" integer, "result.curves[1].rrddata[3]" integer, "result.curves[1].line_type" text, "result.curves[1].title" text, "result.curves[2].color" text, "result.curves[2].rrddata[0]" integer, "result.curves[2].rrddata[1]" integer, "result.curves[2].rrddata[2]" integer, "result.curves[2].rrddata[3]" integer, "result.curves[2].line_type" text, "result.curves[2].title" text, "result.curves[3].color" text, "result.curves[3].rrddata[0]" integer, "result.curves[3].rrddata[1]" integer, "result.curves[3].rrddata[2]" integer, "result.curves[3].rrddata[3]" integer, "result.curves[3].line_type" text, "result.curves[3].title" text ) LOCATION ( 'pxf://hdw1:8020/user/nifi/check_mk/memory/*?PROFILE=JSON' ) FORMAT 'CUSTOM' (formatter='pxfwritable_import' ) the problem are the objects in the curves array ...

kum=# select * from kum.ext_checkmk_memory;

result_code | result.step | result.start_time | result.end_time | result.curves[0].color | result.curves[0].rrddata[0] | result.curves[0].rrddata[1] | result.curves[0].rrddata[2] | result.curves[0].rrddata[3] | result.curves[0].line_type | result.curves[0].title | result

.curves[1].color | result.curves[1].rrddata[0] | result.curves[1].rrddata[1] | result.curves[1].rrddata[2] | result.curves[1].rrddata[3] | result.curves[1].line_type | result.curves[1].title | result.curves[2].color | result.curves[2].rrddata[0] | result.curves[2].rrddata

[1] | result.curves[2].rrddata[2] | result.curves[2].rrddata[3] | result.curves[2].line_type | result.curves[2].title | result.curves[3].color | result.curves[3].rrddata[0] | result.curves[3].rrddata[1] | result.curves[3].rrddata[2] | result.curves[3].rrddata[3] | result.

curves[3].line_type | result.curves[3].title

-------------+-------------+-------------------+-----------------+------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+------------------------+-------

-----------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+----------------------------+------------------------+------------------------+-----------------------------+-------------------------

----+-----------------------------+-----------------------------+----------------------------+------------------------+------------------------+-----------------------------+-----------------------------+-----------------------------+-----------------------------+--------

--------------------+------------------------

0 | 1800 | 1491174000 | 1491260400 | | | | | | | |

| | | | | | | | |

| | | | | | | | | |

|

Contributor

This will need further test/ investigation. If you have the ability to log a support ticket, please do.

Hi Vineet, i don't have the ability.