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.