Get started with TSQL to query time series data - Time Series Database

Use TSQL to query time series data from a simulated data center performance monitoring scenario. This guide walks you through generating sample data, loading it into TSDB, and running a progression of queries—from basic time range filters to window functions.

Prerequisites

Before you begin, ensure that you have:

A running TSDB instance with a known host and port
Go installed (required to build the benchmarking tool, if building from source)
Access to a shell environment

How it works

Generate sample data — use a benchmarking tool to produce OpenTSDB-format JSON
Load data into TSDB — pipe the JSON file into TSDB using bulk_load_opentsdb
Run queries — explore the data with TSQL queries ranging from basic filters to advanced aggregations

Generate sample data

The sample data comes from influxdb-comparisons, a benchmarking tool that generates realistic time series data for a simulated DevOps environment.

After installing and compiling the tool, run the following command to generate 10 minutes of performance metrics:

cd influxdb-comparisons/cmds
bulk_data_gen/bulk_data_gen --seed=123 --use-case=devops --scale-var=10 --format=opentsdb --timestamp-start="2019-03-01T00:00:00Z" --timestamp-end="2019-03-01T00:10:00Z" > tsdb_devops_sf10_10m_seed123.json

Sample data schema

Each record in the output file represents a single metric measurement:

Field	Type	Description	Example
`metric`	string	Metric name; used as the table name in TSQL	`redis.evicted_keys`
`timestamp`	integer	Unix timestamp in milliseconds	`1551398990000`
`tags`	object	Key-value pairs identifying the data source	`hostname`, `datacenter`, `region`, `os`, `arch`, `rack`
`value`	number	The numeric measurement	`2951`

Sample records:

{"metric":"redis.evicted_keys","timestamp":1551398990000,"tags":{"arch":"x86","datacenter":"us-east-1b","hostname":"host_9","os":"Ubuntu16.10","port":"1470","rack":"7","region":"us-east-1","server":"redis_29176","service":"14","service_environment":"production","service_version":"0","team":"LON"},"value":2951}
{"metric":"redis.keyspace_hits","timestamp":1551398990000,"tags":{"arch":"x86","datacenter":"us-east-1b","hostname":"host_9","os":"Ubuntu16.10","port":"1470","rack":"7","region":"us-east-1","server":"redis_29176","service":"14","service_environment":"production","service_version":"0","team":"LON"},"value":2945}
{"metric":"redis.keyspace_misses","timestamp":1551398990000,"tags":{"arch":"x86","datacenter":"us-east-1b","hostname":"host_9","os":"Ubuntu16.10","port":"1470","rack":"7","region":"us-east-1","server":"redis_29176","service":"14","service_environment":"production","service_version":"0","team":"LON"},"value":2944}
{"metric":"redis.instantaneous_ops_per_sec","timestamp":1551398990000,"tags":{"arch":"x86","datacenter":"us-east-1b","hostname":"host_9","os":"Ubuntu16.10","port":"1470","rack":"7","region":"us-east-1","server":"redis_29176","service":"14","service_environment":"production","service_version":"0","team":"LON"},"value":65}
{"metric":"redis.instantaneous_input_kbps","timestamp":1551398990000,"tags":{"arch":"x86","datacenter":"us-east-1b","hostname":"host_9","os":"Ubuntu16.10","port":"1470","rack":"7","region":"us-east-1","server":"redis_29176","service":"14","service_environment":"production","service_version":"0","team":"LON"},"value":58}

All queries in this guide use the cpu.usage_system metric, which records CPU system usage.

Load data

Load the generated file into TSDB using bulk_load_opentsdb with 5 parallel workers:

cat tsdb_devops_sf10_10m_seed123.json | bulk_load_opentsdb/bulk_load_opentsdb --urls=http://your_tsdb_host:port_num -workers=5

Replace your_tsdb_host and port_num with your TSDB instance's host and port.

Queries

All examples query tsdb.`cpu.usage_system`.

Metric names that contain dots must be wrapped in backticks in TSQL (for example, `cpu.usage_system`).

Basic time range queries

Select all columns for a 10-second window:

SELECT *
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01 00:00:00' AND '2019-03-01 00:00:10'

Select specific columns—value, timestamp, and two tag keys:

SELECT `value`, `timestamp`, hostname, datacenter
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01 00:00:00' AND '2019-03-01 00:00:10'

Filter by tag value to return data for specific hosts only:

SELECT `value`, `timestamp`, hostname, datacenter
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01 00:00:00' AND '2019-03-01 00:00:10'
  AND hostname IN ('host_0', 'host_2', 'host_4')

Sort results chronologically:

SELECT *
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01 00:00:00' AND '2019-03-01 00:00:10'
ORDER BY `timestamp`

Filter by a mathematical expression on the value—return only data points where sqrt(value) > 1.5:

SELECT *
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01 00:00:00' AND '2019-03-01 00:00:10'
  AND sqrt(`value`) > 1.5

Aggregation queries

Group by hostname and datacenter, and compute max, min, and average values per group:

SELECT
    hostname,
    datacenter,
    max(`value`) AS maxV,
    min(`value`) AS minV,
    avg(`value`) AS avgV
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01 00:00:00' AND '2019-03-01 00:00:10'
GROUP BY hostname, datacenter

Use tumble() to bucket data into 2-minute intervals, then compute aggregates per host per bucket. The tumble() function assigns each row to a fixed-width, non-overlapping time window:

SELECT
    hostname,
    datacenter,
    tumble(`timestamp`, INTERVAL '2' MINUTE) AS ts,
    max(`value`) AS maxV,
    min(`value`) AS minV,
    avg(`value`) AS avgV
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01 00:00:00' AND '2019-03-01 00:10:00'
GROUP BY hostname, datacenter, ts

Compute a composite metric from the aggregated values—max - min + 0.5 * avg—within each 2-minute window per host:

SELECT
    hostname,
    datacenter,
    tumble(`timestamp`, INTERVAL '2' MINUTE) AS ts,
    max(`value`) - min(`value`) + 0.5 * avg(`value`) AS compV
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01 00:00:00' AND '2019-03-01 00:10:00'
GROUP BY hostname, datacenter, ts

Window function queries

Use the lag() window function to compute the difference between consecutive readings for each host. lag() returns the value from the previous row within the same partition (per host), ordered by timestamp:

SELECT hostname, `timestamp`, `value`,
    `value` - lag(`value`) OVER (PARTITION BY hostname ORDER BY `timestamp`) AS diff
FROM tsdb.`cpu.usage_system`
WHERE `timestamp` BETWEEN '2019-03-01' AND '2019-03-01 00:10:00'

Clamp outliers by wrapping the above query as a subquery and applying a CASE statement: if the computed difference exceeds 50.0, replace it with 0.0:

SELECT hostname, `timestamp`, `value`,
    CASE WHEN diff > 50.0 THEN 0.0
         ELSE diff
    END
FROM (
    SELECT hostname, `timestamp`, `value`,
        `value` - lag(`value`) OVER (PARTITION BY hostname ORDER BY `timestamp`) AS diff
    FROM tsdb.`cpu.usage_system`
    WHERE `timestamp` BETWEEN '2019-03-01' AND '2019-03-01 00:10:00'
)

Compute the per-minute maximum for each host, then calculate how that maximum changes between consecutive minutes. The inner query produces one maxValue per host per 1-minute window; the outer query applies lag() to find the minute-over-minute delta:

SELECT hostname, ts, maxValue,
    maxValue - lag(maxValue) OVER (PARTITION BY hostname ORDER BY ts) AS diff
FROM (
    SELECT hostname,
        tumble(`timestamp`, INTERVAL '1' MINUTE) AS ts,
        max(`value`) AS maxValue
    FROM tsdb.`cpu.usage_system`
    WHERE `timestamp` BETWEEN '2019-03-01' AND '2019-03-01 00:10:00'
    GROUP BY hostname, ts
)

What's next

Explore additional TSQL syntax and supported functions in the TSQL reference documentation.
Connect TSDB to a visualization tool to chart the time series data you just queried.