How to visualise nested time series data

[wfhartford (Wesley Hartford)]

I have an interesting data structure and I'm looking for advice on how to store it in Elasticsearch and visualise in kibana. The data is statistics related to queries to a separate time series database. Each document summarises all of the queries to a specific data set performed by users in one day; this includes a list of dates and the number of queries which accessed time series data for that date. Below is a sample document:

{
  "@timestamp": "2021-11-05",
  "configuration": { "name": "common" },
  "table": { "schema": "SCHEMA_NAME", "table": "TABLE_NAME" },
  "total_queries": 4,
  "min_query_date": "2021-10-14",
  "date_queries": [
    { "date": "2021-10-01" ,"count": 1 },
    { "date": "2021-11-01" ,"count": 4 },
    { "date": "2021-12-01" ,"count": 1 }
  ]
}

This document tells us that on November 5 (@timestamp), 4 queries were executed against this table (total_queries). The objects in date_queries tell us that 1 query accessed data from October, 4 queries accessed data from November, and 1 query accessed data from December (accessing future data is expected).

My goal is to visualise all of the queries executed against a table over a period of time, showing the total number of queries which accessed data from each month in the time series. Ideally, I should be able to aggregate together several documents like above for different days and visualise the sum for each day.

The data structure described above can be modified if a different layout would better support the visualisations I'm looking for.

Thanks for any advice or feedback you can provide.

[monfera (Robert Monfera)]

Hi Wesley, the more common data layout would be the creation of one document per event, where each has its own @timestamp and other descriptors such as schema and table, and can even be repetitions. If you have concerns about the size of the database, please note that Elasticsearch indexing and compression means that there's no 1:1 correspondence between space utilization and document count, and if it' space efficiency you are after, you can ask it in the Elasticsearch channel or one of the logging channels like Beats or Logstash. Filing a support question is also an option if you're participating in a support contract.

[wfhartford (Wesley Hartford)]

Thanks for the feedback. I'll evaluate how well splitting these documents up will satisfy our other use cases and discuss our priorities.