How to create HAWQ external table with complex json data

[#1]

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 }

[#2]

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

[#3]

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

| | | | | | | | |

| | | | | | | | | |

|

[#4]

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

[#5]

Hi Vineet, i don't have the ability.