Influx Query Language (InfluxQL) 2.x specification

InfluxQL is a SQL-like query language used to interact with InfluxDB and work with your times series data.

Find Influx Query Language (InfluxQL) definitions and details, including:

To learn more about InfluxQL, browse the following topics:

Notation

The syntax is specified using Extended Backus-Naur Form (“EBNF”). EBNF is the same notation used in the Go programming language specification, which can be found here.

  1. Production  = production_name "=" [ Expression ] "." .
  2. Expression  = Alternative { "|" Alternative } .
  3. Alternative = Term { Term } .
  4. Term        = production_name | token [ "…" token ] | Group | Option | Repetition .
  5. Group       = "(" Expression ")" .
  6. Option      = "[" Expression "]" .
  7. Repetition  = "{" Expression "}" .

Notation operators in order of increasing precedence:

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

Query representation

Characters

InfluxQL is Unicode text encoded in UTF-8.

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

Letters and digits

Letters are the set of ASCII characters plus the underscore character _ (U+005F) is considered a letter.

Only decimal digits are supported.

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

Identifiers

Identifiers are tokens which refer to database names, retention policy names, user names, measurement names, tag keys, and field keys.

The rules:

  • double quoted identifiers can contain any unicode character other than a new line
  • double quoted identifiers can contain escaped " characters (i.e., \")
  • double quoted identifiers can contain InfluxQL keywords
  • unquoted identifiers must start with an upper or lowercase ASCII character or “_”
  • unquoted identifiers may contain only ASCII letters, decimal digits, and “_”
  1. identifier          = unquoted_identifier | quoted_identifier .
  2. unquoted_identifier = ( letter ) { letter | digit } .
  3. quoted_identifier   = `"` unicode_char { unicode_char } `"` .

Examples

  1. cpu
  2. _cpu_stats
  3. "1h"
  4. "anything really"
  5. "1_Crazy-1337.identifier>NAME👍"

Keywords

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

If you use an InfluxQL keywords as an identifier you will need to double quote that identifier in every query.

The keyword time is a special case. time can be a database name, measurement name, retention policy name, subscription name, and user name. In those cases, time does not require double quotes in queries. time cannot be a field key or tag key; InfluxDB rejects writes with time as a field key or tag key and returns an error. See Frequently Asked Questions for more information.

Literals

Integers

InfluxQL supports decimal integer literals. Hexadecimal and octal literals are not currently supported.

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

Floats

InfluxQL supports floating-point literals. Exponents are not currently supported.

  1. float_lit           = int_lit "." int_lit .

Strings

String literals must be surrounded by single quotes. Strings may contain ' characters as long as they are escaped (i.e., \').

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

Durations

Duration literals specify a length of time. An integer literal followed immediately (with no spaces) by a duration unit listed below is interpreted as a duration literal. Durations can be specified with mixed units.

Duration units
UnitsMeaning
nsnanoseconds (1 billionth of a second)
u or µmicroseconds (1 millionth of a second)
msmilliseconds (1 thousandth of a second)
ssecond
mminute
hhour
dday
wweek
  1. duration_lit        = int_lit duration_unit .
  2. duration_unit       = "ns" | "u" | "µ" | "ms" | "s" | "m" | "h" | "d" | "w" .

Dates & Times

The date and time literal format is not specified in EBNF like the rest of this document. It is specified using Go’s date / time parsing format, which is a reference date written in the format required by InfluxQL. The reference date time is:

InfluxQL reference date time: January 2nd, 2006 at 3:04:05 PM

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

Booleans

  1. bool_lit            = TRUE | FALSE .

Regular Expressions

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

Comparators: =~ matches against !~ doesn’t match against

NOTE: InfluxQL supports using regular expressions when specifying:

Currently, InfluxQL does not support using regular expressions to match non-string field values in the WHERE clause, databases, and retention polices.

Queries

A query is composed of one or more statements separated by a semicolon.

  1. query               = statement { ";" statement } .
  3. statement           = delete_stmt |
  4.                       drop_measurement_stmt |
  5.                       explain_stmt |
  6.                       explain_analyze_stmt |
  7.                       select_stmt |
  8.                       show_databases_stmt |
  9.                       show_field_key_cardinality_stmt |
  10.                       show_field_keys_stmt |
  11.                       show_measurement_exact_cardinality_stmt |
  12.                       show_measurements_stmt |
  13.                       show_series_exact_cardinality_stmt |
  14.                       show_series_stmt |
  15.                       show_tag_key_cardinality_stmt |
  16.                       show_tag_key_exact_cardinality_stmt |
  17.                       show_tag_keys_stmt |
  18.                       show_tag_values_with_key = stmt |
  19.                       show_tag_values_cardinality_stmt .

Statements

DELETE

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

Examples

  1. DELETE FROM "cpu"
  2. DELETE FROM "cpu" WHERE time < '2000-01-01T00:00:00Z'
  3. DELETE WHERE time < '2000-01-01T00:00:00Z'

DROP MEASUREMENT

  1. drop_measurement_stmt = "DROP MEASUREMENT" measurement .

Examples

  1. -- drop the cpu measurement
  2. DROP MEASUREMENT "cpu"

EXPLAIN

Parses and plans the query, and then prints a summary of estimated costs.

Many SQL engines use the EXPLAIN statement to show join order, join algorithms, and predicate and expression pushdown. Since InfluxQL does not support joins, the cost of a InfluxQL query is typically a function of the total series accessed, the number of iterator accesses to a TSM file, and the number of TSM blocks that need to be scanned.

The elements of EXPLAIN query plan include:

  • expression
  • auxiliary fields
  • number of shards
  • number of series
  • cached values
  • number of files
  • number of blocks
  • size of blocks
  1. explain_stmt = "EXPLAIN" select_stmt .

Example

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

EXPLAIN ANALYZE

Executes the specified SELECT statement and returns data on the query performance and storage during runtime, visualized as a tree. Use this statement to analyze query performance and storage, including execution time and planning time, and the iterator type and cursor type.

For example, executing the following statement:

  1. > explain analyze select mean(usage_steal) from cpu where time >= '2018-02-22T00:00:00Z' and time < '2018-02-22T12:00:00Z'

May produce an output similar to the following:

  1. EXPLAIN ANALYZE
  2. ---------------
  3. .
  4. └── select
  5.     ├── execution_time: 2.25823ms
  6.     ├── planning_time: 18.381616ms
  7.     ├── total_time: 20.639846ms
  8.     └── field_iterators
  9.         ├── labels
  10.         │   └── statement: SELECT mean(usage_steal::float) FROM telegraf."default".cpu
  11.         └── expression
  12.             ├── labels
  13.             │   └── expr: mean(usage_steal::float)
  14.             └── create_iterator
  15.                 ├── labels
  16.                 │   ├── measurement: cpu
  17.                 │   └── shard_id: 608
  18.                 ├── cursors_ref: 779
  19.                 ├── cursors_aux: 0
  20.                 ├── cursors_cond: 0
  21.                 ├── float_blocks_decoded: 431
  22.                 ├── float_blocks_size_bytes: 1003552
  23.                 ├── integer_blocks_decoded: 0
  24.                 ├── integer_blocks_size_bytes: 0
  25.                 ├── unsigned_blocks_decoded: 0
  26.                 ├── unsigned_blocks_size_bytes: 0
  27.                 ├── string_blocks_decoded: 0
  28.                 ├── string_blocks_size_bytes: 0
  29.                 ├── boolean_blocks_decoded: 0
  30.                 ├── boolean_blocks_size_bytes: 0
  31.                 └── planning_time: 14.805277ms
  2. > Note: EXPLAIN ANALYZE ignores query output, so the cost of serialization to JSON or CSV is not accounted for.
  4. ##### execution\_time
  6. Shows the amount of time the query took to execute, including reading the time series data, performing operations as data flows through iterators, and draining processed data from iterators. Execution time doesn’t include the time taken to serialize the output into JSON or other formats.
  8. ##### planning\_time
  10. Shows the amount of time the query took to plan. Planning a query in InfluxDB requires a number of steps. Depending on the complexity of the query, planning can require more work and consume more CPU and memory resources than the executing the query. For example, the number of series keys required to execute a query affects how quickly the query is planned and the required memory.
  12. First, InfluxDB determines the effective time range of the query and selects the shards to access (in InfluxDB Enterprise, shards may be on remote nodes). Next, for each shard and each measurement, InfluxDB performs the following steps:
  14. 1.  Select matching series keys from the index, filtered by tag predicates in the WHERE clause.
  15. 2.  Group filtered series keys into tag sets based on the GROUP BY dimensions.
  16. 3.  Enumerate each tag set and create a cursor and iterator for each series key.
  17. 4.  Merge iterators and return the merged result to the query executor.
  19. ##### iterator type
  21. EXPLAIN ANALYZE supports the following iterator types:
  23. -   `create_iterator` node represents work done by the local influxd instance──a complex composition of nested iterators combined and merged to produce the final query output.
  24. -   (InfluxDB Enterprise only) `remote_iterator` node represents work done on remote machines.
  26. For more information about iterators, see [Understanding iterators](#understanding-iterators).
  28. ##### cursor type
  30. EXPLAIN ANALYZE distinguishes 3 cursor types. While the cursor types have the same data structures and equal CPU and I/O costs, each cursor type is constructed for a different reason and separated in the final output. Consider the following cursor types when tuning a statement:
  32. -   cursor\_ref: Reference cursor created for SELECT projections that include a function, such as `last()` or `mean()`.
  33. -   cursor\_aux: Auxiliary cursor created for simple expression projections (not selectors or an aggregation). For example, `SELECT foo FROM m` or `SELECT foo+bar FROM m`, where `foo` and `bar` are fields.
  34. -   cursor\_cond: Condition cursor created for fields referenced in a WHERE clause.
  36. For more information about cursors, see [Understanding cursors](#understanding-cursors).
  38. ##### block types
  40. EXPLAIN ANALYZE separates storage block types, and reports the total number of blocks decoded and their size (in bytes) on disk. The following block types are supported:
  42. | `float` | 64-bit IEEE-754 floating-point number | | `integer` | 64-bit signed integer | | `unsigned` | 64-bit unsigned integer | | `boolean` | 1-bit, LSB encoded | | `string` | UTF-8 string |
  44. For more information about storage blocks, see [TSM files]($d957ba19b2e0c714.md#time-structured-merge-tree-tsm).
  46. ### SELECT

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

  2. #### Example
  4. Select from measurements grouped by the day with a timezone

SELECT mean(“value”) FROM “cpu” GROUP BY region, time(1d) fill(0) tz(‘America/Chicago’)

  2. ### SHOW CARDINALITY
  4. Refers to the group of commands used to estimate or count exactly the cardinality of measurements, series, tag keys, tag key values, and field keys.
  6. The SHOW CARDINALITY commands are available in two variations: estimated and exact. Estimated values are calculated using sketches and are a safe default for all cardinality sizes. Exact values are counts directly from TSM (Time-Structured Merge Tree) data, but are expensive to run for high cardinality data. Unless required, use the estimated variety.
  8. Filtering by `time` is only supported when Time Series Index (TSI) is enabled on a database.
  10. See the specific SHOW CARDINALITY commands for details:
  12. -   [SHOW FIELD KEY CARDINALITY](#show-field-key-cardinality)
  13. -   [SHOW SERIES CARDINALITY](#show-series-cardinality)
  14. -   [SHOW TAG KEY CARDINALITY](#show-tag-key-cardinality)
  15. -   [SHOW TAG VALUES CARDINALITY](#show-tag-values-cardinality)
  17. ### SHOW DATABASES

show_databases_stmt = “SHOW DATABASES” .

  2. #### Example

— show all databases SHOW DATABASES

  2. ### SHOW FIELD KEY CARDINALITY
  4. Estimates or counts exactly the cardinality of the field key set for the current database unless a database is specified using the `ON <database>` option.
  6. > **Note:** `ON <database>`, `FROM <sources>`, `WITH KEY = <key>`, `WHERE <condition>`, `GROUP BY <dimensions>`, and `LIMIT/OFFSET` clauses are optional. When using these query clauses, the query falls back to an exact count. Filtering by `time` is only supported when Time Series Index (TSI) is enabled and `time` is not supported in the `WHERE` clause.

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 ]

  2. #### Examples

— 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

  2. ### SHOW FIELD KEYS

show_field_keys_stmt = “SHOW FIELD KEYS” [on_clause] [ from_clause ] .

  2. #### Examples

— 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”

  2. ### SHOW MEASUREMENTS

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

  2. #### Examples

— 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.*/

  2. ### SHOW SERIES

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

  2. #### Example

SHOW SERIES FROM “telegraf”.”autogen”.”cpu” WHERE cpu = ‘cpu8’

  2. ### SHOW SERIES EXACT CARDINALITY
  4. Estimates or counts exactly the cardinality of the series for the current database unless a database is specified using the ON option.
  6. #### Example
  8. SHOW SERIES EXACT CARDINALITY" \[ on\_clause \] \[ from\_clause \] \[ where\_clause \] \[ group\_by\_clause \] \[ limit\_clause \] \[ offset\_clause \]

SHOW SERIES EXACT CARDINALITY ON mydb

  2. -   [Series cardinality]($a967b7d7e27c15ae.md#series-cardinality) is the major factor that affects RAM requirements. For more information, see:
  4. -   [Don’t have too many series]($bcdb5235e4a5c114.md#why-does-series-cardinality-matter). As the number of unique series grows, so does the memory usage. High series cardinality can force the host operating system to kill the InfluxDB process with an out of memory (OOM) exception.
  7. **NOTE:** `ON <database>`, `FROM <sources>`, `WITH KEY = <key>`, `WHERE <condition>`, `GROUP BY <dimensions>`, and `LIMIT/OFFSET` clauses are optional. When using these query clauses, the query falls back to an exact count. Filtering by `time` is not supported in the `WHERE` clause.
  9. ### SHOW TAG KEY CARDINALITY
  11. Estimates or counts exactly the cardinality of tag key set on the current database unless a database is specified using the `ON <database>` option.
  13. > **Note:** `ON <database>`, `FROM <sources>`, `WITH KEY = <key>`, `WHERE <condition>`, `GROUP BY <dimensions>`, and `LIMIT/OFFSET` clauses are optional. When using these query clauses, the query falls back to an exact count. Filtering by `time` is only supported when TSI (Time Series Index) is enabled and `time` is not supported in the `WHERE` clause.

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 ]

  2. #### Examples

— show estimated tag key cardinality SHOW TAG KEY CARDINALITY — show exact tag key cardinality SHOW TAG KEY EXACT CARDINALITY

  2. ### SHOW TAG KEYS

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

  2. #### Examples

— 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’

  2. ### SHOW TAG VALUES

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

  2. #### Examples

— 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’

  2. ### SHOW TAG VALUES CARDINALITY
  4. Estimates or counts exactly the cardinality of tag key values for the specified tag key on the current database unless a database is specified using the `ON <database>` option.
  6. **Note:** `ON <database>`, `FROM <sources>`, `WITH KEY = <key>`, `WHERE <condition>`, `GROUP BY <dimensions>`, and `LIMIT/OFFSET` clauses are optional. When using these query clauses, the query falls back to an exact count. Filtering by `time` is only supported when TSI (Time Series Index) is enabled.

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

  2. #### Examples

— 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”

  2. ## Clauses

from_clause = “FROM” measurements .

group_by_clause = “GROUP BY” dimensions fill(fill_option).

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 .

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 “)” ) .

  2. ## 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 .

  2. ## Comments
  4. Use comments with InfluxQL statements to describe your queries.
  6. -   A single line comment begins with two hyphens (`--`) and ends where InfluxDB detects a line break. This comment type cannot span several lines.
  7. -   A multi-line comment begins with `/*` and ends with `*/`. This comment type can span several lines. Multi-line comments do not support nested multi-line comments.
  9. ## Other

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 .

policy_name = identifier .

retention_policy = identifier .

retention_policy_name = “NAME” identifier .

series_id = int_lit .

sort_field = field_key [ ASC | DESC ] .

sort_fields = sort_field { “,” sort_field } .

tag_key = identifier .

tag_keys = tag_key { “,” tag_key } .

var_ref = measurement . ```