4 Replies Latest reply: Aug 9, 2017 10:21 AM by Michael Tarallo RSS

    REST connection to specific JSON structure (Open TSDB)

    David Štorek

      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