PANDAS-format not working

, ,
1

After I failed with my attemt to established a JDBC Connection to DateHub I try to use the datehub Microservice instead.

I try to use the datahub microservice to query the data.I use the “High-performance API” which have the format-option (as the doc say Cumulocity IoT DataHub - OpenAPI Specification). In reality the parameter do nothing. I send as format: "PANDAS" but the result I get back look like the default forma, what is awkward to parse.

My Query:
{"sql":"SELECT * FROM txxxxxxxxDataLake.\"pw-hmi\".txxxxxxxx.\"measurements_State_Offline\" WHERE source=241481197242","limit":1,"format":"PANDAS"}

The result have this format:

{
   "columns":[
      "dir0",
      "dir1",
      "dir2",
      "dir3",
      "id",
      "creationTime",
      "creationTimeOffset",
      "creationTimeWithOffset",
      "YEAR",
      "MONTH",
      "DAY",
      "time",
      "timeOffset",
      "timeWithOffset",
      "source",
      "type",
      "Activity_Idle.series_times.unit",
      "Activity_Idle.series_times.value"
   ],
   "data":[
      [
         "chunk_P1_598_618_1701302400000_1701561600000_46597706",
         "0_2023",
         "0_11",
         "0_30",
         "1343464808",
         "2023-11-30T05:53:44.532",
         0,
         "2023-11-30T05:53:44.532",
         "2023",
         "11",
         "30",
         "2023-11-30T05:53:13.966",
         0,
         "2023-11-30T05:53:13.966",
         "881342014107",
         "Activity_Idle",
         "sec",
         147915.0
      ]
   ]
}

The PANDAS-Format from the documentation looks much cleaner and better to parse with normal object mapper.

2

Hi @David.Richter As per the API documentation it providing the right result set.
If the format is “DREMIO” the result set must have “Schema”, “rows” & “rowcount” like below

{

  • “schema”:[

    • “status”,

    • “severity”],

  • “rows”:[

    • ,

    • ],

  • “rowCount”: 5

}

If format is “PANDAS” result set must have columns and data only

{

  • “columns”:[

    • {},

    • {}],

  • “data”:[

    • {}]

}

In your API response you are getting the columns and list of data (records).
Is your requirement is to do some additional data formatting?

1 Like
3

Hi @Vachaspati_Diwevedi2

as you can see in my example response I get the result in DREMIO-format even I send "format":"PANDAS" in my request.
I want use the PANDAS-format because its easier to use. When I see the example here Cumulocity IoT DataHub - OpenAPI Specification it look like I get a complete objects in data that I can parse with ObjectMapper in my code. And unter columns I get information about the data type. In The DREMIO-format I don’t have information about the data types.

I build now a complex code to working with the DREMIO-format but I don’t understand why I can’t use the better PANDAS-format.