topic Re: REST connection to specific JSON structure (Open TSDB) in Connectivity & Data Prep

REST connection to specific JSON structure (Open TSDB)

DavidŠtorek — Mon, 31 Jul 2017 09:34:47 GMT

Hello comunity,

I need to connect to Open TSDB (special timeseries database working with Hadoop - OpenTSDB - A Distributed, Scalable Monitoring System) using its api (HTTP API — OpenTSDB 2.3 documentation). Unfortunately i get JSON response in folowing format:

[{"metric":"volt_l3","tags":{"device":"dev4tech2_sm5"},"aggregateTags":[],"dps":{"1501452319":230.60000610351562,"1501452349":230.6999969482422,"1501452379":230.6999969482422,"1501452409":230.6999969482422,...,"1501492010":227.10000610351562}}]

and it is interpreted by Qlik Sense as :

..."dps":{"1501452319":230.60000610351562,...

..."table name”:{„field name“ : value,

but it should be something like:

..."table name”:{"field named Time value" : metric value,...

So if I connect data I get a thousands of fields with one value. Output is like this:

LIB CONNECT TO 'test';

RestConnectorMasterTable:

SQL SELECT

"__KEY_root",

(SELECT

"1501192815",

...(thousands of other fields)...

"1501245766",

"__FK_dps"

FROM "dps" FK "__FK_dps")

FROM JSON (wrap on) "root" PK "__KEY_root";

The main issue is that there are new records in database (time and some value) ~every 10 seconds and with this output I am not able to load them withouth complete changing of load script. Do you have any sugestions how to handle this? How to force Qlik to read data in a required way?

Thank you in advance.

I am using Qlik Sense June 2017 and its REST connector

Re: REST connection to specific JSON structure (Open TSDB)

gladi-cz — Wed, 02 Aug 2017 07:48:37 GMT

I have same issue. Could mto‌‌‌ help us?

Thanks,

Petr

Re: REST connection to specific JSON structure (Open TSDB)

Michael_Tarallo — Wed, 02 Aug 2017 14:03:27 GMT

Hi Guys - thanks for the notification - I have added daz‌ on this thread - he may be able to provide some more insight.

Re: REST connection to specific JSON structure (Open TSDB)

gladi-cz — Wed, 09 Aug 2017 11:42:45 GMT

Hi mto‌ & daz‌,

Do you have any ideas?

Thanks for replay,

Petr

Re: REST connection to specific JSON structure (Open TSDB)

Michael_Tarallo — Wed, 09 Aug 2017 14:21:42 GMT

Hey Petr - let me directly follow up with him.

Re: REST connection to specific JSON structure (Open TSDB)

Anonymous — Thu, 28 Sep 2017 22:51:41 GMT

Did you guys ever solve this problem? I'm running into the same thing.

Re: REST connection to specific JSON structure (Open TSDB)

DavidŠtorek — Thu, 08 Feb 2018 13:18:44 GMT

Hi,

in meantime i found kind of work around. Just add to url address to the end ...&arrays=true.

The result will be that you get output formated as array of arrays ant than it is a little bit better for data load.

Documented on JSON Serializer — OpenTSDB 2.3 documentation

Re: REST connection to specific JSON structure (Open TSDB)

Anonymous — Thu, 08 Feb 2018 17:16:00 GMT

Ended up using this too! Thanks for the tip.