All Products
Search
Document Center

Time Series Database:InfluxQL reference

Last Updated:Jan 08, 2024

Influx Query Language (InfluxQL) is an SQL-like query language that is supported by TSDB for InfluxDB. InfluxQL provides features to store and analyze time series data. This topic describes the basic concepts of InfluxQL.

Notations

InfluxQL uses Extended Backus-Naur Form (EBNF) to describe the syntax. You can use the notations that are specified in EBNF when you write code by using the Go programming language. For more information, see Go programming language specifications. TSDB for InfluxDB® is developed by using Go programming language and supports EBNF notations.

Production  = production_name "=" [ Expression ] "." .
Expression  = Alternative { "|" Alternative } .
Alternative = Term { Term } .
Term        = production_name | token [ "…" token ] | Group | Option | Repetition .
Group       = "(" Expression ")" .
Option      = "[" Expression "]" .
Repetition  = "{" Expression "}" .

The following notations are sorted in ascending order based on the level of priority:

|   alternation
()  grouping
[]  option (0 or 1 times)
{}  repetition (0 to n times)

Elements in query requests

Characters

InfluxQL supports Unicode text that is encoded in UTF-8.

newline             = /* the Unicode code point U+000A */ .
unicode_char        = /* an arbitrary Unicode code point except newline */ .

Letters and digits

Letters: InfluxSQL supports ASCII characters for letters and underscores. Underscores are used in the same manner as letters in InfluxQL. An underscore is indicated by U+005F in ASCII character set.

Digits: InfluxQL supports only decimal digits.

letter              = ascii_letter | "_" .
ascii_letter        = "A" … "Z" | "a" … "z" .
digit               = "0" … "9" .

Identifiers

Identifiers specify the names of databases, retention policies, users, measurements, tag keys, and field keys.

The identifiers supported by InfluxQL conform to the following rules:

  • An identifier that is enclosed in double quotation marks (") can contain Unicode characters and cannot contain line feeds.

  • An identifier that is enclosed in double quotation marks (") can contain \" characters that are used to escape double quotation marks (").

  • An identifier that is enclosed in double quotation marks (") can contain InfluxQL keywords.

  • The first character of an identifier that is not enclosed in quotation marks (") must be an ASCII character for a letter or underscore (_).

  • An identifier that is not enclosed in quotation marks (") can contain only ASCII characters, digits, and underscores (_).

identifier          = unquoted_identifier | quoted_identifier .
unquoted_identifier = ( letter ) { letter | digit } .
quoted_identifier   = `"` unicode_char { unicode_char } `"` .

Example:

cpu
_cpu_stats
"1h"
"anything really"
"1_Crazy-1337.identifier>NAME"

Keywords

ALL           ALTER         ANY           AS            ASC           BEGIN
BY            CREATE        CONTINUOUS    DATABASE      DATABASES     DEFAULT
DELETE        DESC          DESTINATIONS  DIAGNOSTICS   DISTINCT      DROP
DURATION      END           EVERY         EXPLAIN       FIELD         FOR
FROM          GRANT         GRANTS        GROUP         GROUPS        IN
INF           INSERT        INTO          KEY           KEYS          KILL
LIMIT         SHOW          MEASUREMENT   MEASUREMENTS  NAME          OFFSET
ON            ORDER         PASSWORD      POLICY        POLICIES      PRIVILEGES
QUERIES       QUERY         READ          REPLICATION   RESAMPLE      RETENTION
REVOKE        SELECT        SERIES        SET           SHARD         SHARDS
SLIMIT        SOFFSET       STATS         SUBSCRIPTION  SUBSCRIPTIONS TAG
TO            USER          USERS         VALUES        WHERE         WITH
WRITE

If you use InfluxQL keywords as identifiers, you must enclose the identifiers in double quotation marks (") in query statements.

You can use the time keyword as an identifier without the need to enclose the identifier in double quotation marks (") in query statements. You can use the time keyword as the name of a continuous query, a database, a measurement, a retention policy, a subscription, or a user. In these cases, you do not need to use double quotation marks (") to enclose time. You cannot use the time keyword as a field key or a tag key. Data that contains the time keyword that is used as a field key or a tag key cannot be written to TSDB for InfluxDB. If you use the time keyword as a field key or tag key, TSDB for InfluxDB returns an error. For more information, see the FAQ topic.

Literals

Integers

InfluxQL supports decimal integers. InfluxQL does not support hexadecimal or octal integers.

int_lit             = ( "1" … "9" ) { digit } .

Floats

InfluxQL supports floating-point numbers. InfluxQL does not support exponents.

float_lit           = int_lit "." int_lit .

Strings

Strings must be enclosed in single quotation marks ('). Strings can contain \' characters that are used to escape single quotation marks (').

string_lit          = `'` { unicode_char } `'` .

Durations

A duration specifies the length of a period of time. A duration literal consists of an integer and a time unit. Spaces cannot be specified between the integer and the unit. You can use multiple units to describe a duration.

Valid units

Unit

Description

ns

Nanosecond (one billionth of a second)

u or µ

Microsecond (one millionth of a second)

ms

Millisecond (One thousandth of a second)

s

Second

m

Minute

h

Hour

d

Day

w

Week

duration_lit        = int_lit duration_unit .
duration_unit       = "ns" | "u" | "µ" | "ms" | "s" | "m" | "h" | "d" | "w" .

Date and time

The format of date and time in InfluxQL is not specified based on EBNF. Date and time is specified based on the formatting and parsing rules of Go. This way, the reference date and time is specified in the required format of InfluxQL. The following example shows the format of reference date and time.

If the local time is January 2, 2006, 15:04:05, the following code shows the reference date and time in InfluxQL:

time_lit            = "2006-01-02 15:04:05.999999" | "2006-01-02" .

Boolean values

bool_lit            = TRUE | FALSE .

Regular expressions

regex_lit           = "/" { unicode_char } "/" .

Comparators: =~ indicates that values are consistent. !~ indicates that values are inconsistent

Note

In InfluxQL queries, you can use regular expressions to specify field keys and tag keys in SELECT clauses, measurements in FROM clauses, tag values in WHERE clauses, and tag keys in GROUP BY clauses. You cannot use regular expressions to specify field values that are not of the STRING data type in WHERE clauses, databases, and retention policies.

Queries

A query consists of one or more statements that are separated with semicolons (;).

query               = statement { ";" statement } .

statement           = alter_retention_policy_stmt |
                      create_continuous_query_stmt |
                      create_database_stmt |
                      create_retention_policy_stmt |
                      create_subscription_stmt |
                      create_user_stmt |
                      delete_stmt |
                      drop_continuous_query_stmt |
                      drop_database_stmt |
                      drop_measurement_stmt |
                      drop_retention_policy_stmt |
                      drop_series_stmt |
                      drop_shard_stmt |
                      drop_subscription_stmt |
                      drop_user_stmt |
                      explain_stmt |
                      explain_analyze_stmt |
                      grant_stmt |
                      kill_query_statement |
                      revoke_stmt |
                      select_stmt |
                      show_continuous_queries_stmt |
                      show_databases_stmt |
                      show_diagnostics_stmt |
                      show_field_key_cardinality_stmt |
                      show_field_keys_stmt |
                      show_grants_stmt |
                      show_measurement_cardinality_stmt |
                      show_measurement_exact_cardinality_stmt |
                      show_measurements_stmt |
                      show_queries_stmt |
                      show_retention_policies_stmt |
                      show_series_cardinality_stmt |
                      show_series_exact_cardinality_stmt |
                      show_series_stmt |
                      show_shard_groups_stmt |
                      show_shards_stmt |
                      show_stats_stmt |
                      show_subscriptions_stmt |
                      show_tag_key_cardinality_stmt |
                      show_tag_key_exact_cardinality_stmt |
                      show_tag_keys_stmt |
                      show_tag_values_stmt |
                      show_tag_values_cardinality_stmt |
                      show_users_stmt .

Statements

DELETE

Important

TSDB for InfluxDB® supports delete operations. However, delete operations may result in deadlocks and lead to the failures of read and write operations due to the syntax of delete operations in InfluxDB®. Therefore, we recommend that you do not perform delete operations in TSDB for InfluxDB®.

delete_stmt = "DELETE" ( from_clause | where_clause | from_clause where_clause ) .

Example:

DELETE FROM "cpu"
DELETE FROM "cpu" WHERE time < '2000-01-01T00:00:00Z'
DELETE WHERE time < '2000-01-01T00:00:00Z'

EXPLAIN

You can use an EXPLAIN statement to parse and plan for a query, and then display a summary about the estimated resource overheads of the query.

Specific SQL engines allow you to use EXPLAIN statements to specify join orders and join algorithms, and push down predicates and expressions. InfluxQL does not support join operations. In most cases, a result that is returned by the specific function contains the overheads of an InfluxQL query. The result includes information such as the total number of time series that are accessed, the number of iterators that access Time-Structured Merge Tree (TSM) files, and the number of TSM blocks that are scanned.

The execution plan that is returned by an EXPLAIN query contains the following elements:

  • Expression

  • Auxiliary fields

  • The number of shards

  • The number of series

  • Cached values

  • The number of files

  • The number of blocks

  • The size of blocks

Example:

> explain select sum(pointReq) from "_internal"."monitor"."write" group by hostname;
> QUERY PLAN
------
EXPRESSION: sum(pointReq::integer)
NUMBER OF SHARDS: 2
NUMBER OF SERIES: 2
CACHED VALUES: 110
NUMBER OF FILES: 1
NUMBER OF BLOCKS: 1
SIZE OF BLOCKS: 931

EXPLAIN ANALYZE

You can use the EXPLAIN ANALYZE statement to execute an InfluxQL query and calculate the actual resource overheads during the execution.

explain_analyze_stmt = "EXPLAIN ANALYZE" select_stmt .

Example:

> explain analyze select sum(pointReq) from "_internal"."monitor"."write" group by hostname;
> EXPLAIN ANALYZE
-----------
.
└── select
├── execution_time: 242.167µs
├── planning_time: 2.165637ms
├── total_time: 2.407804ms
└── field_iterators
├── labels
│   └── statement: SELECT sum(pointReq::integer) FROM "_internal"."monitor"."write" GROUP BY hostname
└── expression
├── labels
│   └── expr: sum(pointReq::integer)
├── create_iterator
│   ├── labels
│   │   ├── measurement: write
│   │   └── shard_id: 57
│   ├── cursors_ref: 1
│   ├── cursors_aux: 0
│   ├── cursors_cond: 0
│   ├── float_blocks_decoded: 0
│   ├── float_blocks_size_bytes: 0
│   ├── integer_blocks_decoded: 1
│   ├── integer_blocks_size_bytes: 931
│   ├── unsigned_blocks_decoded: 0
│   ├── unsigned_blocks_size_bytes: 0
│   ├── string_blocks_decoded: 0
│   ├── string_blocks_size_bytes: 0
│   ├── boolean_blocks_decoded: 0
│   ├── boolean_blocks_size_bytes: 0
│   └── planning_time: 1.401099ms
└── create_iterator
├── labels
│   ├── measurement: write
│   └── shard_id: 58
├── cursors_ref: 1
├── cursors_aux: 0
├── cursors_cond: 0
├── float_blocks_decoded: 0
├── float_blocks_size_bytes: 0
├── integer_blocks_decoded: 0
├── integer_blocks_size_bytes: 0
├── unsigned_blocks_decoded: 0
├── unsigned_blocks_size_bytes: 0
├── string_blocks_decoded: 0
├── string_blocks_size_bytes: 0
├── boolean_blocks_decoded: 0
├── boolean_blocks_size_bytes: 0
└── planning_time: 76.192µs

KILL QUERY

You can use the KILL QUERY statement to stop a query.

kill_query_statement = "KILL QUERY" query_id .

In the statement, the query_id parameter specifies the ID of the query that you want to stop. You can use a SHOW QUERIES statement to query the IDs of your queries. In the returned result, the qid parameter indicates the ID of a query.

Example:

-- kill query with qid of 36 on the local host
KILL QUERY 36

SELECT

select_stmt = "SELECT" fields from_clause [ into_clause ] [ where_clause ]
              [ group_by_clause ] [ order_by_clause ] [ limit_clause ]
              [ offset_clause ] [ slimit_clause ] [ soffset_clause ] [ timezone_clause ] .

Example:

You can use the following statement to query data based on measurements that contain the cpu prefix, write the result data to the database based on the same measurements, and set the retention policy to cpu_1h.

SELECT mean("value") INTO "cpu_1h".:MEASUREMENT FROM /cpu.*/

You can use the following statement to query data based on the specified measurements and group the result data by day based on the specified time zone.

SELECT mean("value") FROM "cpu" GROUP BY region, time(1d) fill(0) tz('America/Chicago')

SHOW CARDINALITY

SHOW CARDINALITY is a set of commands that can be used to calculate estimated or exact cardinalities of measurements, series, tag keys, tag values, and field keys.

You can use SHOW CARDINALITY statements to query estimated or exact cardinalities. The system calculates an estimated cardinality based on a specific plan. This method can be used regardless of the size of the cardinality. The system consumes low overheads to calculate an estimated cardinality even if the estimated cardinality is a high value. The system calculates an exact cardinality based on TSM data. If the cardinality of the data that you want to process is high, the system consumes high overheads to calculate the exact cardinality. We recommend that you query estimated cardinalities unless your business scenarios require exact cardinalities.

You can filter data by time only when the Time Series Index (TSI) feature is enabled for your database.

For more information about SHOW CARDINALITY statements, see the following sections:

  • SHOW FIELD KEY CARDINALITY

  • SHOW MEASUREMENT CARDINALITY

  • SHOW SERIES CARDINALITY

  • SHOW TAG KEY CARDINALITY

  • SHOW TAG VALUES CARDINALITY

SHOW CONTINUOUS QUERIES

show_continuous_queries_stmt = "SHOW CONTINUOUS QUERIES" .

Example:

-- show all continuous queries
SHOW CONTINUOUS QUERIES

SHOW DATABASES

show_databases_stmt = "SHOW DATABASES" .

Example:

-- show all databases
SHOW DATABASES

SHOW DIAGNOSTICS

You can use the following statement to query node information such as the node build information, runtime, hostname, server configuration, memory usage, and Go runtime diagnostics.

show_diagnostics_stmt = "SHOW DIAGNOSTICS"

SHOW FIELD KEY CARDINALITY

If you do not use the ON <database> clause to specify a database, the estimated or exact cardinality of the field key set in the current database is returned.

Note

Optional clauses: ON <database>, FROM <sources>, WITH KEY = <key>, WHERE <condition>, GROUP BY <dimensions>, LIMIT, and OFFSET clauses. If you use these clauses, the exact cardinality is returned. You can filter data by time only when the TSI feature is enabled for your database. You cannot use the time filter in WHERE clauses.

show_field_key_cardinality_stmt = "SHOW FIELD KEY CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ]

show_field_key_exact_cardinality_stmt = "SHOW FIELD KEY EXACT CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ]

Example:

-- show estimated cardinality of the field key set of current database
SHOW FIELD KEY CARDINALITY

-- show exact cardinality on field key set of specified database
SHOW FIELD KEY EXACT CARDINALITY ON mydb

SHOW FIELD KEY

show_field_keys_stmt = "SHOW FIELD KEYS" [on_clause] [ from_clause ] .

Example:

-- show field keys and field value data types from all measurements
SHOW FIELD KEYS

-- show field keys and field value data types from specified measurement
SHOW FIELD KEYS FROM "cpu"

SHOW GRANTS

show_grants_stmt = "SHOW GRANTS FOR" user_name .

Example:

-- show grants for jdoe
SHOW GRANTS FOR "jdoe"

SHOW MEASUREMENT CARDINALITY

If you do not use the ON <database> clause to specify a database, the estimated or exact cardinality of the measurement set in the current database is returned.

Note

Optional clauses: ON <database>, FROM <sources>, WITH KEY = <key>, WHERE <condition>, GROUP BY <dimensions>, LIMIT, and OFFSET clauses. If you use these clauses, the exact cardinality is returned. You can filter data by time only when the TSI feature is enabled for your database. You cannot use the time filter in WHERE clauses.

show_measurement_cardinality_stmt = "SHOW MEASUREMENT CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ]

show_measurement_exact_cardinality_stmt = "SHOW MEASUREMENT EXACT CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ]

Example:

-- show estimated cardinality of measurement set on current database
SHOW MEASUREMENT CARDINALITY

-- show exact cardinality of measurement set on specified database
SHOW MEASUREMENT EXACT CARDINALITY ON mydb

SHOW MEASUREMENTS

show_measurements_stmt = "SHOW MEASUREMENTS" [on_clause] [ with_measurement_clause ] [ where_clause ] [ limit_clause ] [ offset_clause ] .

Example:

-- show all measurements
SHOW MEASUREMENTS

-- show measurements where region tag = 'uswest' AND host tag = 'serverA'
SHOW MEASUREMENTS WHERE "region" = 'uswest' AND "host" = 'serverA'

-- show measurements that start with 'h2o'
SHOW MEASUREMENTS WITH MEASUREMENT =~ /h2o.*/

SHOW QUERIES

show_queries_stmt = "SHOW QUERIES" .

Example:

-- show all currently-running queries
SHOW QUERIES

SHOW RETENTION POLICIES

show_retention_policies_stmt = "SHOW RETENTION POLICIES" [on_clause] .

Example:

-- show all retention policies on a database
SHOW RETENTION POLICIES ON "mydb"

SHOW SERIES

show_series_stmt = "SHOW SERIES" [on_clause] [ from_clause ] [ where_clause ] [ limit_clause ] [ offset_clause ] .

Example:

SHOW SERIES FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu8'

SHOW SERIES CARDINALITY

If you do not use the ON <database> clause to specify a database, the estimated or exact cardinality of the series set in the current database is returned.

The cardinality of the series set affects the random-access memory (RAM) usage.

Note

Optional clauses: ON <database>, FROM <sources>, WITH KEY = <key>, WHERE <condition>, GROUP BY <dimensions>, LIMIT, and OFFSET clauses. If you use these clauses, the exact cardinality is returned. You can filter data by time only when the TSI feature is enabled for your database. You cannot use the time filter in WHERE clauses.

show_series_cardinality_stmt = "SHOW SERIES CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ]

show_series_exact_cardinality_stmt = "SHOW SERIES EXACT CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ]

Example:

-- show estimated cardinality of the series on current database
SHOW SERIES CARDINALITY

-- show estimated cardinality of the series on specified database
SHOW SERIES CARDINALITY ON mydb

-- show exact series cardinality
SHOW SERIES EXACT CARDINALITY

-- show series cardinality of the series on specified database
SHOW SERIES EXACT CARDINALITY ON mydb

SHOW SHARD GROUPS

show_shard_groups_stmt = "SHOW SHARD GROUPS" .

Example:

SHOW SHARD GROUPS

SHOW SHARD

show_shards_stmt = "SHOW SHARDS" .

Example:

SHOW SHARDS

SHOW STATS

You can use a SHOW STATS statement to query the statistical information about a TSDB for InfluxDB node and available components. You can access components after you enable them.

show_stats_stmt = "SHOW STATS [ FOR '<component>' | 'indexes' ]"

SHOW STATS

  • The SHOW STATS statement does not query the memory usage of indexes. To query the memory usage of indexes, use the SHOW STATS FOR 'indexes' statement.

  • The statistical values that are returned by SHOW STATS are stored in the memory. The statistical values are reset to 0 after the node restarts. The SHOW STATS statement is automatically executed at intervals of 10 seconds to provide data for the _internal database.

SHOW STATS FOR <component>

  • You can use the SHOW STATS FOR <component> statement to query the statistical information about a specified component.

  • For the runtime component, a summary of the memory usage of the TSDB for InfluxDB system is returned based on the runtime package of Go. For more information, see Go runtime.

SHOW STATS FOR 'indexes'

  • You can use the SHOW STATS FOR 'indexes' statement to query the estimated memory usage of all indexes. The SHOW STATS statement does not query the memory usage of indexes because the calculation for the memory usage of indexes consumes large amounts of resources.

Example:

> show stats
name: runtime
-------------
Alloc   Frees   HeapAlloc       HeapIdle        HeapInUse       HeapObjects     HeapReleased    HeapSys         Lookups Mallocs NumGC   NumGoroutine    PauseTotalNs    Sys             TotalAlloc
4136056 6684537 4136056         34586624        5816320         49412           0               40402944        110     6733949 83      44              36083006        46692600        439945704


name: graphite
tags: proto=tcp
batches_tx      bytes_rx        connections_active      connections_handled     points_rx       points_tx
----------      --------        ------------------      -------------------     ---------       ---------
159             3999750         0                       1                       158110          158110

SHOW SUBSCRIPTIONS

show_subscriptions_stmt = "SHOW SUBSCRIPTIONS" .

Example:

SHOW SUBSCRIPTIONS

SHOW TAG KEY CARDINALITY

If you do not use the ON <database> clause to specify a database, the estimated or exact cardinality of the tag key set in the current database is returned.

Note

Optional clauses: ON <database>, FROM <sources>, WITH KEY = <key>, WHERE <condition>, GROUP BY <dimensions>, LIMIT, and OFFSET clauses. If you use these clauses, the exact cardinality is returned. You can filter data by time only when the TSI feature is enabled for your database. You cannot use the time filter in WHERE clauses.

show_tag_key_cardinality_stmt = "SHOW TAG KEY CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ]

show_tag_key_exact_cardinality_stmt = "SHOW TAG KEY EXACT CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ]

Example:

-- show estimated tag key cardinality
SHOW TAG KEY CARDINALITY

-- show exact tag key cardinality
SHOW TAG KEY EXACT CARDINALITY

SHOW TAG KEYS

show_tag_keys_stmt = "SHOW TAG KEYS" [on_clause] [ from_clause ] [ where_clause ]
                     [ limit_clause ] [ offset_clause ] .

Example:

-- show all tag keys
SHOW TAG KEYS

-- show all tag keys from the cpu measurement
SHOW TAG KEYS FROM "cpu"

-- show all tag keys from the cpu measurement where the region key = 'uswest'
SHOW TAG KEYS FROM "cpu" WHERE "region" = 'uswest'

-- show all tag keys where the host key = 'serverA'
SHOW TAG KEYS WHERE "host" = 'serverA'

SHOW TAG VALUES

show_tag_values_stmt = "SHOW TAG VALUES" [on_clause] [ from_clause ] with_tag_clause [ where_clause ]
                       [ limit_clause ] [ offset_clause ] .

Example:

-- show all tag values across all measurements for the region tag
SHOW TAG VALUES WITH KEY = "region"

-- show tag values from the cpu measurement for the region tag
SHOW TAG VALUES FROM "cpu" WITH KEY = "region"

-- show tag values across all measurements for all tag keys that do not include the letter c
SHOW TAG VALUES WITH KEY !~ /.*c.*/

-- show tag values from the cpu measurement for region & host tag keys where service = 'redis'
SHOW TAG VALUES FROM "cpu" WITH KEY IN ("region", "host") WHERE "service" = 'redis'

SHOW TAG VALUES CARDINALITY

If you do not use the ON <database> clause to specify a database, the estimated or exact cardinality of the tag values that correspond to the specified tag keys in the current database is returned.

Note

Optional clauses: ON <database>, FROM <sources>, WITH KEY = <key>, WHERE <condition>, GROUP BY <dimensions>, LIMIT, and OFFSET clauses. If you use these clauses, the exact cardinality is returned. You can filter data by time only when the TSI feature is enabled for your database.

show_tag_values_cardinality_stmt = "SHOW TAG VALUES CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ] with_key_clause

show_tag_values_exact_cardinality_stmt = "SHOW TAG VALUES EXACT CARDINALITY" [ on_clause ] [ from_clause ] [ where_clause ] [ group_by_clause ] [ limit_clause ] [ offset_clause ] with_key_clause

Example:

-- show estimated tag key values cardinality for a specified tag key
SHOW TAG VALUES CARDINALITY WITH KEY = "myTagKey"

-- show estimated tag key values cardinality for a specified tag key
SHOW TAG VALUES CARDINALITY WITH KEY = "myTagKey"

-- show exact tag key values cardinality for a specified tag key
SHOW TAG VALUES EXACT CARDINALITY WITH KEY = "myTagKey"

-- show exact tag key values cardinality for a specified tag key
SHOW TAG VALUES EXACT CARDINALITY WITH KEY = "myTagKey"

SHOW USERS

show_users_stmt = "SHOW USERS" .

Example:

-- show all users
SHOW USERS

Clauses

from_clause     = "FROM" measurements .

group_by_clause = "GROUP BY" dimensions fill(fill_option).

into_clause     = "INTO" ( measurement | back_ref ).

limit_clause    = "LIMIT" int_lit .

offset_clause   = "OFFSET" int_lit .

slimit_clause   = "SLIMIT" int_lit .

soffset_clause  = "SOFFSET" int_lit .

timezone_clause = tz(string_lit) .

on_clause       = "ON" db_name .

order_by_clause = "ORDER BY" sort_fields .

to_clause       = "TO" user_name .

where_clause    = "WHERE" expr .

with_measurement_clause = "WITH MEASUREMENT" ( "=" measurement | "=~" regex_lit ) .

with_tag_clause = "WITH KEY" ( "=" tag_key | "!=" tag_key | "=~" regex_lit | "IN (" tag_keys ")"  ) .

Expressions

binary_op        = "+" | "-" | "*" | "/" | "%" | "&" | "|" | "^" | "AND" |
                   "OR" | "=" | "!=" | "<>" | "<" | "<=" | ">" | ">=" .

expr             = unary_expr { binary_op unary_expr } .

unary_expr       = "(" expr ")" | var_ref | time_lit | string_lit | int_lit |
                   float_lit | bool_lit | duration_lit | regex_lit .

Others

alias            = "AS" identifier .

back_ref         = ( policy_name ".:MEASUREMENT" ) |
                   ( db_name "." [ policy_name ] ".:MEASUREMENT" ) .

db_name          = identifier .

dimension        = expr .

dimensions       = dimension { "," dimension } .

field_key        = identifier .

field            = expr [ alias ] .

fields           = field { "," field } .

fill_option      = "null" | "none" | "previous" | int_lit | float_lit . | "linear"

host             = string_lit .

measurement      = measurement_name |
                   ( policy_name "." measurement_name ) |
                   ( db_name "." [ policy_name ] "." measurement_name ) .

measurements     = measurement { "," measurement } .

measurement_name = identifier | regex_lit .

password         = string_lit .

policy_name      = identifier .

privilege        = "ALL" [ "PRIVILEGES" ] | "READ" | "WRITE" .

query_id         = int_lit .

query_name       = identifier .

retention_policy = identifier .

retention_policy_option      = retention_policy_duration |
                               retention_policy_replication |
                               retention_policy_shard_group_duration |
                               "DEFAULT" .

retention_policy_duration    = "DURATION" duration_lit .

retention_policy_replication = "REPLICATION" int_lit .

retention_policy_shard_group_duration = "SHARD DURATION" duration_lit .

retention_policy_name = "NAME" identifier .

series_id        = int_lit .

shard_id         = int_lit .

sort_field       = field_key [ ASC | DESC ] .

sort_fields      = sort_field { "," sort_field } .

subscription_name = identifier .

tag_key          = identifier .

tag_keys         = tag_key { "," tag_key } .

user_name        = identifier .

var_ref          = measurement .

Comments

You can add comments in InfluxQL statements to describe your queries.

  • The prefix of a single-line comment is two hyphens (--) and the suffix is a line feed that can be detected by TSDB for InfluxDB®. A single-line comment cannot cover multiple lines.

  • The prefix of a multi-line comment is /* and the suffix is */. A multi-line comment can cover multiple lines. InfluxQL does not support nested multi-line comments.

How queries are performed by the query engine?

We recommend that you learn the basic knowledge about InfluxQL and learn how InfluxQL queries are performed by the query engine. This way, you can learn how to use query results for your business scenarios and how to create queries in an efficient manner.

The following process describes the lifecycle of a query:

  1. The InfluxQL statement is converted into tokens and then parsed to an abstract syntax tree (AST). The result is the code representation of the query.

  2. The AST is passed to the query executor that assigns the query to the appropriate handler. For example, metadata-related queries are run by the metadata service and SELECT statements are executed in shards.

  3. The query engine finds the shards that match the specified time range. The time range is specified in a SELECT statement. In these shards, an iterator is created for each field that is specified in the statement.

  4. The iterators are passed to an emitter. The emitter releases the iterators and combines the result data points. The emitter converts simple time-based data points into complex objects and returns the objects to the client.

Iterators

Iterators are the core of the query engine. Iterators provide simple interfaces that can be used to loop through a set of data points. In the following example, an iterator is used to loop through the floating-point numbers:

type FloatIterator interface {
    Next() *FloatPoint
}

In the following example, the IteratorCreator interface is used to create an iterator:

type IteratorCreator interface {
    CreateIterator(opt *IteratorOptions) (Iterator, error)
}

You can use IteratorOptions to specify valid fields, a time range, and a dimension when you create an iterator. The IteratorCreator interface can be used at multiple levels such as the Shards, Shard, and Engine levels. This way, queries can be optimized. For example, data can be pre-processed before the COUNT() function returns results.

You can use iterators to read raw data from data nodes. You can also combine iterators to provide data for input iterators. For example, you can use DistinctIterator to calculate values in each time window for an input iterator. You can also use FillIterator to generate data to replace missing data points in an input iterator.

You can combine iterators to aggregate data. The following statement provides an example:

SELECT MEAN(value) FROM cpu GROUP BY time(10m)

In the preceding statement, MEAN(value) specifies a mean iterator that is generated based on the shards. In the following statement, an iterator that is used to calculate derivatives of average values is added:

SELECT DERIVATIVE(MEAN(value), 20m) FROM cpu GROUP BY time(10m)

Auxiliary fields

InfluxQL supports selector functions such as FIRST(), LAST(), MIN(), and MAX(). Therefore, the query engine must return the requested data points and the relevant data at the same time.

The following statement provides an example:

SELECT FIRST(value), host FROM cpu GROUP BY time(1h)

You can use the statement to query the value of the first data point that is generated per hour and the host that is associated with each data point. To make auxiliary fields more efficient, only one type of value is specified for data points. This way, the host parameter is used as an auxiliary field for data points. Auxiliary fields are appended to a data point until the data point is passed to the emitter. The emitter splits the auxiliary fields and allocates them to the corresponding iterators.

Built-in iterators

You can use the following helper iterators to perform queries:

  • Merge iterator: merges one or more iterators of the same type to generate a new iterator. Merge iterators ensure that all data points that are collected in the current time window are returned before the query engine scans data for the next time window. Merge iterators do not sort data points by time. Merge iterators aggregate queries that require fast access and have low requirements for sorting.

  • Sorted merge iterator: merges one or more iterators of the same type to generate a new iterator and sorts data points by time. Sorted merge iterators require longer periods of time to process data than merge iterators. Non-aggregate queries that query raw data points sort the data points by time and then return the data points. This way, sorted merge iterators can be used for non-aggregate queries.

  • Limit iterator: limits the number of data points for each name group or tag group. Limit iterators are created based on the LIMIT and OFFSET syntax.

  • Fill iterator: generates values to replace missing data points in input iterators. The generated values can be null values, the values of the previous data point, or specified values.

  • Buffered iterator: ensures that data points are in the Unread state and returns the data points to the buffer. In this case, the data points can be read again. The buffered iterator is commonly used to forecast data in a time window.

  • Reduce iterator: calls the reduction function to process each data point in a time window. After all operations in a time window are performed, all data points in the window are returned. Reduce iterators can be used for simple aggregate functions such as COUNT().

  • Reduce slice iterator: collects all data points in a time window and then transmits the data points to a reduction function. Requested results are returned from reduce slice iterators. Reduce slice iterators can be used for aggregate functions such as DERIVATIVE().

  • Transform iterator: calls a transform function to convert each data point that is obtained from an input iterator to a binary expression.

  • Dedupe iterator: deduplicates data points and returns unique data points. Dedupe iterators consume large amounts of resources and can be used only for queries that are executed on a small size of data, such as metadata queries.

Call iterators

InfluxQL functions can be executed at two levels: shard level and engine level.

To make iterators more efficient, specific function calls can be executed at multiple layers. For example, COUNT() can be executed at the shard level. A count iterator can include multiple count iterators to calculate the total number of shards. You can use the NewCallIterator() function to create count iterators.

Specific iterators are more complex and must be used to execute function calls at the engine level. For example, DERIVATIVE() obtains all data points in a time window and then calculates the data points. Iterators of this type are created by the query engine and cannot be created at the shard level.

InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.