Queries

• 1: ClickHouse Support
• 2: InfluxDB Support
• 3: Per-Query Time Series Instructions
• 4: Prometheus Support

1 - ClickHouse Support

Trickster will accelerate ClickHouse queries that return time series data normally visualized on a dashboard. Acceleration works by using the Time Series Delta Proxy Cache to minimize the number and time range of queries to the upstream ClickHouse server.

Scope of Support

Trickster is tested with the ClickHouse DataSource Plugin for Grafana v1.9.3 by Vertamedia, and supports acceleration of queries constructed by this plugin using the plugin’s built-in $timeSeries macro. Trickster also supports several other query formats that return “time series like” data.

Because ClickHouse does not provide a golang-based query parser, Trickster uses custom parsing code on the incoming ClickHouse query to deconstruct its components, determine if it is cacheable and, if so, what elements are factored into the cache key derivation. Trickster also determines the requested time range and step based on the provided absolute values, in order to normalize the query before hashing the cache key.

If you find query or response structures that are not yet supported, or providing inconsistent or unexpected results, we’d love for you to report those. We also always welcome any contributions around this functionality.

To constitute a cacheable query, the first column expression in the main or any subquery must be in one of two specific forms in order to determine the timestamp column and step:

Grafana Plugin Format

SELECT intDiv(toUInt32(time_col, 60) * 60) [* 1000] [as] [alias]

This is the approach used by the Grafana plugin. The time_col and/or alias is used to determine the requested time range from the WHERE or PREWHERE clause of the query. The argument to the ClickHouse intDiv function is the step value in seconds, since the toUInt32 function on a datetime column returns the Unix epoch seconds.

ClickHouse Time Grouping Function

SELECT toStartOf[Period](time_col) [as] [alias]

This is the approach that uses the following optimized ClickHouse functions to group timeseries queries:

toStartOfMinute
toStartOfFiveMinute
toStartOfTenMinutes
toStartOfFifteenMinutes
toStartOfHour
toDate

Again the time_col and/or alias is used to determine the request time range from the WHERE or PREWHERE clause, and the step is derived from the function name.

Determining the requested time range

Once the time column (or alias) and step are derived, Trickster parses each WHERE or PREWHERE clause to find comparison operations that mark the requested time range. To be cacheable, the WHERE clause must contain either a [timecol|alias] BETWEEN phrase or a [time_col|alias] >[=] phrase. The BETWEEN or >= arguments must be a parsable ClickHouse string date in the form 2006-01-02 15:04:05, a a ten digit integer representing epoch seconds, or the now() ClickHouse function with optional subtraction.

If a > phrase is used, a similar < phrase can be used to specify the end of the time period. If none is found, Trickster will still cache results up to the current time, but future queries must also have no end time phrase, or Trickster will be unable to find the correct cache key.

Examples of cacheable time range WHERE clauses:

WHERE t >= "2020-10-15 00:00:00" and t <= "2020-10-16 12:00:00"
WHERE t >= "2020-10-15 12:00:00" and t < now() - 60 * 60
WHERE datetime BETWEEN 1574686300 AND 1574689900

Note that these values can be wrapped in the ClickHouse toDateTime function, but ClickHouse will make that conversion implicitly and it is not required. All string times are assumed to be UTC.

Normalization and “Fast Forwarding”

Trickster will always normalize the calculated time range to fit the step size, so small variations in the time range will still result in actual queries for the entire time “bucket”. In addition, Trickster will not cache the results for the portion of the query that is still active – i.e., within the current bucket or within the configured backfill tolerance setting (whichever is greater)

2 - InfluxDB Support

Trickster provides support for accelerating InfluxDB queries that return time series data normally visualized on a dashboard. Acceleration works by using the Time Series Delta Proxy Cache to minimize the number and time range of queries to the upstream InfluxDB server.

Scope of Support

Trickster is tested with the built-in InfluxDB DataSource Plugin for Grafana v5.0.0.

Trickster uses InfluxDB-provided packages to parse and normalize queries for caching and acceleration. If you find query or response structures that are not yet supported, or providing inconsistent or unexpected results, we’d love for you to report those so we can further improve our InfluxDB support.

Trickster supports integrations with InfluxDB 1.x and 2.0, however, the Flux language is not currently supported.

3 - Per-Query Time Series Instructions

Beginning with Trickster v1.1, certain features like Fast Forward can be toggled a per-query basis, to assist with compatibility in your environment. This allows the drafters of a query to have some say over toggling these features on queries they find to have issues running through Trickster. This is done by adding directives via query comments. For example, in Prometheus, you can end any query with # any comment following a hashtag, so you can place the per-query instructions there.

Supported Per-Query Instructions

Fast Forward Disable

To disable fast forward, use the instruction trickster-fast-forward. You can only use this feature with Prometheus as other time series do not currently implement Fast Forward. Use as in the following example:

go_goroutines{job="trickster"}  # trickster-fast-forward:off

Backfill Tolerance

To set the backfill tolerance, use the instruction trickster-backfill-tolerance. Trickster supports setting the backfill tolerance for all time series backends. Use as in the following example:

SELECT time, count(*) FROM table  # trickster-backfill-tolerance:120

4 - Prometheus Support

Trickster fully supports accelerating Prometheus, which we consider our First Class backend provider. They work great together, so you should give it a try!

Most configuration options that affect Prometheus reside in the main Backend config, since they generally apply to all TSDB providers alike.

We offer one custom configuration for Prometheus, which is the ability to inject labels, on a per-backend basis to the Prometheus response before it is returned to the caller.

Injecting Labels

Here is the basic configuration for adding labels:

backends:
  prom-1a:
    provider: prometheus
    origin_url: http://prometheus-us-east-1a:9090
    prometheus:
      labels:
        datacenter: us-east-1a

  prom-1b:
    provider: prometheus
    origin_url: http://prometheus-us-east-1b:9090
    prometheus:
      labels:
        datacenter: us-east-1b