Timestream DB Query Cookbook

Timestream DB Query Cookbook

Overview

Helpful queries that can be used to gain various insights from metrics published by Firefly.

Queries

Grafana query explorer link: https://tiny.amazon.com/133jjqcy9/ga10grafusweamazexpl

General query format with macros

You will notice that in the Timestream query explorer, there are Macros on the left side as well as a time range (possibly says something like “last 1 hour”) in the top right of the eplorer.

If you want to make use of those dropdowns instead of manually specifying values, you can write a query like so:

SELECT *
FROM $__database.$__table
WHERE $__timeFilter
  AND measure_name='$__measure'
LIMIT 10

Errors across all clients

WITH binned_errors AS (
    SELECT ErrorType, BIN(time, 1m) AS binned_timestamp, SUM(measure_value::bigInt) AS TotalErrors
    FROM "FireFlyMetric-Timestream"."FireFly-Clients"
    WHERE $__timeFilter
        AND Region IN ('us-east-1','us-west-2','eu-west-1')
        AND measure_name='Error'
    GROUP BY 1, 2
)

SELECT
  ErrorType,
  CREATE_TIME_SERIES(binned_timestamp, TotalErrors) AS "[ErrorCount]"
FROM binned_errors
GROUP BY 1

Error rates for each region

WITH binned_errors AS (
    SELECT Region, BIN(time, 1m) AS binned_timestamp, SUM(measure_value::bigInt) AS TotalErrors
    FROM "FireFlyMetric-Timestream"."FireFly-Clients"
    WHERE $__timeFilter
        AND measure_name='Error'
    GROUP BY 1, 2
),

binned_total_requests AS (
  SELECT BIN(time, 1m) AS binned_timestamp, SUM(measure_value::bigInt) AS TotalRequests
  FROM "FireFlyMetric-Timestream"."FireFly-Clients"
  WHERE $__timeFilter
      AND measure_name='Count'
  GROUP BY 1
)

SELECT
  e.Region,
  CREATE_TIME_SERIES(e.binned_timestamp, CAST(e.TotalErrors AS DOUBLE) / r.TotalRequests) AS "[ErrorRate]"
FROM binned_errors AS e
INNER JOIN binned_total_requests AS r
  ON e.binned_timestamp = r.binned_timestamp
GROUP BY 1