Queries can either be "Direct" or the results of a query can be cached. Direct mode will always execute the query directly against the database live when an associated visualization is displayed. The associated load time is directly proportional to the time it takes for the query to execute.
Alternatively, the query results can be stored into the Knowi "ElasticStore" seamlessly. This is particularly useful in the following cases:
- Long running queries
- Reduce load on your database for reporting workloads by offloading it to the ElasticStore
- The results can be used as a parent dataset to other derived queries off this dataset. Useful in cases where the resulting dataset tracked in the ElasticStore runs into the millions of records
- In cases with large raw datasets in the underlying database, this can be used to incrementally update the ElasticStore for that dataset without running the entire historical query
In Warehousing Strategy section, check Direct Query for direct. By default, the results will be stored into the ElasticStore. If ElasticStore is used, specify a schedule for asynchronous query execution.
For more details, see Elastic Store documentation.
Once ready, click on Save to just save the query or hit Save & Run Now button to immediately execute the query.
Overwrite Strategy
With ElasticStore usage, Overwrite strategy provides you powerful control on how the data is updated for the dataset when the query is run. The values can be one of the following:
- All will replace the existing values in the store for this dataset with new incoming values during query execution.
- Empty will leave the existing results as is and append incoming data to it.
- One or more field names, comma separated: Will update the existing dataset with new values for the same values of the fields, insert new records when a match is not found and leave existing recrods as is. This is useful for incrementally updating the dataset. For example, let's say the raw data that a query operates on has 100's of millions of events, and say the query is doing a grouping by count, date and event type. In this case, trolling through the entire 100's of millions of records each time the query runs can induce loads into your system. You can structure the query in the to something like the following:
select count(*) as Totals, date(eventTime) as Date, EventType
where eventTime >= $c9_yesterday
group by EventType, date(eventTime)
with Overwrite Strategy of Date, EventType and scheduled every few hours. In this example, it'll query only data from yesterday and the results are then upserted into the dataset.
4. <date field name>-<retention period>: Retention overwrite strategy for a date type field can be defined by specifying how far back in time we would like to keep our data. The retention period can be defined in the Time Unit format. For example, Date-1m will remove all existing rows in our elastic-store which have Date field's value less than 1 month before the MIN value of the incoming data's Date field. This can also be used in conjunction with our existing field value overwrite (#3) above. For example, to add retention of 3 months to the example in #3 above, we just need to make the Overwrite Strategy to be Date, EventType, Date-3m
5. ForceAll : When a query returns no data, the default behaviour is that any previous data that exists in the ElasticStore is left as is. ForceAll is an alternative to All that removes all data associated to that dataset if no data was found for the most recent query.