Apache Druid – Ingesting multiple objects in flat JSON data returns only single row

Question

I'm aiming to ingest this JSON data into Apache Druid as multiple rows. The data (about 10x more rows than this example) is served from a proprietary HTTP server that I have no control over. I cannot change how the data is presented from the HTTP server. The data will be pulled periodically, as there is no notification from the HTTP server.

{
  "1": {
    "lastseen": "2021-02-15T05:02Z",
    "name": "Temperature Sensor A",
    "state": {
      "alert": "none",
      "level": 152,
      "on": true,
      "reachable": true
    }
  },
  "3": {
    "lastseen": "2021-02-15T05:01Z",
    "name": "Temperature Sensor B",
    "state": {
      "alert": "none",
      "level": 13,
      "on": false,
      "reachable": true
    }
  },
  "4": {
    "lastseen": "2021-02-15T05:00Z",
    "name": "Temperature Sensor Backup",
    "state": {
      "alert": "none",
      "level": 76,
      "on": true,
      "reachable": true
    }
  }
}

Note that object 2 is missing. This number is an internal record ID from the server, and if object 2 is deleted, this ID will disappear from subsequent requests.

I have created a data source that successfully pulls the data, but it ends up as as single row. As I understand, since the JSON object has no array at the root level, Druid sees these three objects as a single row.

I suspect that the answer lies somewhere in the flattenSpec area, but I have failed to get this going. The closest I have gotten so far is with the following flattenSpec, but this only returns the first row from the JSON data (though the row is represented correctly).

  "inputFormat": {
    "type": "json",
    "flattenSpec": {
      "fields": [
        {
          "type": "jq",
          "expr": ".[].name",
          "name": "name"
        },
        {
          "type": "jq",
          "expr": ".[].lastseen",
          "name": "lastseen"
        }
      ]
    }
  }

The above flattenSpec gives me a single row that looks like:

lastseen              | name
2021-02-15T05:02Z     | Temperature Sensor A

How can I get Druid to see all the rows? What is the right flattenSpec (or jq / JSONPath expression)?

I am not concerned about performance, but I would prefer not to have to spin up more proxy-like services between the HTTP server and Druid.

user_8291949 · Answer

Try a filter like this and no flattening should be required:

jq '.[] | {name,lastseen}'

Using your input, this results in:

{
  "name": "Temperature Sensor A",
  "lastseen": "2021-02-15T05:02Z"
}
{
  "name": "Temperature Sensor B",
  "lastseen": "2021-02-15T05:01Z"
}
{
  "name": "Temperature Sensor Backup",
  "lastseen": "2021-02-15T05:00Z"
}

user_5165605 · Answer

I think it's possible, but not easy.

I was in a similar situation, except the multi-record documents were in Kafka, not HTTP. Ultimately, the easiest solution was to change the format of the data going in, and you've made it clear that's not an option for you.

Batching support does seem to exist: https://github.com/apache/druid/issues/4373

Gian, one of the main committers for Druid said in 2019: https://github.com/apache/druid/issues/4373#issuecomment-489819616

However, at present, you do need to implement a custom extension to get it (none of the builtin parsers will split out messages into multiple rows). I expect we will add that functionality at some point, but have not yet.

It looks like the solution is to write your own extension. A sample extension can be found here: https://github.com/implydata/druid-example-extension

I know that's not a great solution, and it sounds like it might be possible in the future, so I'd keep an eye on releases as they come out.

Apache Druid – Ingesting multiple objects in flat JSON data returns only single row

2 Answers