Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to create HAWQ external table with complex json data

Highlighted

How to create HAWQ external table with complex json data

New Contributor

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

Re: How to create HAWQ external table with complex json data

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

Re: How to create HAWQ external table with complex json data

New Contributor

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

| | | | | | | | |

| | | | | | | | | |

|

Re: How to create HAWQ external table with complex json data

Contributor

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

Re: How to create HAWQ external table with complex json data

New Contributor

Hi Vineet, i don't have the ability.

Don't have an account?
Coming from Hortonworks? Activate your account here