Influx Query Language (InfluxQL) reference - Influxdb

Introduction

InfluxQL is a SQL-like query language for interacting with InfluxDB and providing features specific to storing and analyzing time 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.

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

Notation operators in order of increasing precedence:

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

Query representation

Characters

InfluxQL is Unicode text encoded in UTF-8.

newline             = /* the Unicode code point U+000A */ .
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.

letter              = ascii_letter | "_" .
ascii_letter        = "A" … "Z" | "a" … "z" .
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 "_"

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

Examples

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          FUTURE        GRANT         GRANTS        GROUP         GROUPS
IN            INF           INSERT        INTO          KEY           KEYS
KILL          LIMIT         SHOW          MEASUREMENT   MEASUREMENTS  NAME
OFFSET        ON            ORDER         PASSWORD      PAST          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 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 continuous query name, 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.

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

Floats

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

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., \').

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

Units	Meaning
ns	nanoseconds (1 billionth of a second)
u or µ	microseconds (1 millionth of a second)
ms	milliseconds (1 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" .

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

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

Booleans

bool_lit            = TRUE | FALSE .

Regular Expressions

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

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

Note: InfluxQL supports using regular expressions when specifying:

field keys and tag keys in the SELECT clause
measurements in the FROM clause
tag values and string field values in the WHERE clause.
tag keys in the GROUP BY clause

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.

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

ALTER RETENTION POLICY

alter_retention_policy_stmt  = "ALTER RETENTION POLICY" policy_name on_clause
                               retention_policy_option
                               [ retention_policy_option ]
                               [ retention_policy_option ]
                               [ retention_policy_option ] .

Examples

sql

-- Set default retention policy for mydb to 1h.cpu.
ALTER RETENTION POLICY "1h.cpu" ON "mydb" DEFAULT

-- Change duration and replication factor.
-- REPLICATION (replication factor) not valid for OSS instances.
ALTER RETENTION POLICY "policy1" ON "somedb" DURATION 1h REPLICATION 4

CREATE CONTINUOUS QUERY

create_continuous_query_stmt = "CREATE CONTINUOUS QUERY" query_name on_clause
                               [ "RESAMPLE" resample_opts ]
                               "BEGIN" select_stmt "END" .

query_name                   = identifier .

resample_opts                = (every_stmt for_stmt | every_stmt | for_stmt) .
every_stmt                   = "EVERY" duration_lit
for_stmt                     = "FOR" duration_lit

Examples

sql

-- selects from DEFAULT retention policy and writes into 6_months retention policy
CREATE CONTINUOUS QUERY "10m_event_count"
ON "db_name"
BEGIN
  SELECT count("value")
  INTO "6_months"."events"
  FROM "events"
  GROUP (10m)
END;

-- this selects from the output of one continuous query in one retention policy and outputs to another series in another retention policy
CREATE CONTINUOUS QUERY "1h_event_count"
ON "db_name"
BEGIN
  SELECT sum("count") as "count"
  INTO "2_years"."events"
  FROM "6_months"."events"
  GROUP BY time(1h)
END;

-- this customizes the resample interval so the interval is queried every 10s and intervals are resampled until 2m after their start time
-- when resample is used, at least one of "EVERY" or "FOR" must be used
CREATE CONTINUOUS QUERY "cpu_mean"
ON "db_name"
RESAMPLE EVERY 10s FOR 2m
BEGIN
  SELECT mean("value")
  INTO "cpu_mean"
  FROM "cpu"
  GROUP BY time(1m)
END;

CREATE DATABASE

create_database_stmt = "CREATE DATABASE" db_name
                       [ WITH
                           [ retention_policy_duration ]
                           [ retention_policy_replication ]
                           [ retention_past_limit ]
                           [ retention_future_limit ]
                           [ retention_policy_shard_group_duration ]
                           [ retention_policy_name ]
                       ] .

[!Warning] Replication factors do not serve a purpose with single node instances.

Examples

sql

-- Create a database called foo
CREATE DATABASE "foo"

-- Create a database called bar with a new DEFAULT retention policy and specify
-- the duration, replication, shard group duration, and name of that retention policy
CREATE DATABASE "bar" WITH DURATION 1d REPLICATION 1 SHARD DURATION 30m NAME "myrp"

-- Create a database called mydb with a new DEFAULT retention policy and specify
-- the name of that retention policy
CREATE DATABASE "mydb" WITH NAME "myrp"

-- Create a database called bar with a new retention policy named "myrp", and
-- specify the duration, past and future limits, and name of that retention policy
CREATE DATABASE "bar" WITH DURATION 1d PAST LIMIT 6h FUTURE LIMIT 6h NAME "myrp"

CREATE RETENTION POLICY

create_retention_policy_stmt = "CREATE RETENTION POLICY" policy_name on_clause
                               retention_policy_duration
                               retention_policy_replication
                               [ retention_policy_shard_group_duration ]
                               [ retention_past_limit ]
                               [ retention_future_limit ]
                               [ "DEFAULT" ] .

[!Warning] Replication factors do not serve a purpose with single node instances.

Examples

sql

-- Create a retention policy.
CREATE RETENTION POLICY "10m.events" ON "somedb" DURATION 60m REPLICATION 2

-- Create a retention policy and set it as the DEFAULT.
CREATE RETENTION POLICY "10m.events" ON "somedb" DURATION 60m REPLICATION 2 DEFAULT

-- Create a retention policy and specify the shard group duration.
CREATE RETENTION POLICY "10m.events" ON "somedb" DURATION 60m REPLICATION 2 SHARD DURATION 30m

-- Create a retention policy and specify past and future limits.
CREATE RETENTION POLICY "10m.events" ON "somedb" DURATION 12h PAST LIMIT 6h FUTURE LIMIT 6h

CREATE SUBSCRIPTION

Subscriptions tell InfluxDB to send all the data it receives to Kapacitor.

create_subscription_stmt = "CREATE SUBSCRIPTION" subscription_name "ON" db_name "." retention_policy "DESTINATIONS" ("ANY"|"ALL") host { "," host} .

Examples

sql

-- Create a SUBSCRIPTION on database 'mydb' and retention policy 'autogen' that send data to 'example.com:9090' via UDP.
CREATE SUBSCRIPTION "sub0" ON "mydb"."autogen" DESTINATIONS ALL 'udp://example.com:9090'

-- Create a SUBSCRIPTION on database 'mydb' and retention policy 'autogen' that round robins the data to 'h1.example.com:9090' and 'h2.example.com:9090'.
CREATE SUBSCRIPTION "sub0" ON "mydb"."autogen" DESTINATIONS ANY 'udp://h1.example.com:9090', 'udp://h2.example.com:9090'

CREATE USER

create_user_stmt = "CREATE USER" user_name "WITH PASSWORD" password
                   [ "WITH ALL PRIVILEGES" ] .

Examples

sql

-- Create a normal database user.
CREATE USER "jdoe" WITH PASSWORD '1337password'

-- Create an admin user.
-- Note: Unlike the GRANT statement, the "PRIVILEGES" keyword is required here.
CREATE USER "jdoe" WITH PASSWORD '1337password' WITH ALL PRIVILEGES

Note: The password string must be wrapped in single quotes.

DELETE

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

Examples

sql

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

DROP CONTINUOUS QUERY

drop_continuous_query_stmt = "DROP CONTINUOUS QUERY" query_name on_clause .

Example

sql

DROP CONTINUOUS QUERY "myquery" ON "mydb"

DROP DATABASE

drop_database_stmt = "DROP DATABASE" db_name .

Example

sql

DROP DATABASE "mydb"

DROP MEASUREMENT

drop_measurement_stmt = "DROP MEASUREMENT" measurement .

Examples

sql

-- drop the cpu measurement
DROP MEASUREMENT "cpu"

DROP RETENTION POLICY

drop_retention_policy_stmt = "DROP RETENTION POLICY" policy_name on_clause .

Example

sql

-- drop the retention policy named 1h.cpu from mydb
DROP RETENTION POLICY "1h.cpu" ON "mydb"

DROP SERIES

drop_series_stmt = "DROP SERIES" ( from_clause | where_clause | from_clause where_clause ) .

Note: Filtering by time is not supported in the WHERE clause.

Example

sql

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

DROP SHARD

drop_shard_stmt = "DROP SHARD" ( shard_id ) .

Example

sql

DROP SHARD 1

DROP SUBSCRIPTION

drop_subscription_stmt = "DROP SUBSCRIPTION" subscription_name "ON" db_name "." retention_policy .

Example

sql

DROP SUBSCRIPTION "sub0" ON "mydb"."autogen"

DROP USER

drop_user_stmt = "DROP USER" user_name .

Example

sql

DROP USER "jdoe"

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

explain_stmt = "EXPLAIN" select_stmt .

Example

sql

> 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

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:

sql

> 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:

sql

EXPLAIN ANALYZE
---------------
.
└── select
    ├── execution_time: 2.25823ms
    ├── planning_time: 18.381616ms
    ├── total_time: 20.639846ms
    └── field_iterators
        ├── labels
        │   └── statement: SELECT mean(usage_steal::float) FROM telegraf."default".cpu
        └── expression
            ├── labels
            │   └── expr: mean(usage_steal::float)
            └── create_iterator
                ├── labels
                │   ├── measurement: cpu
                │   └── shard_id: 608
                ├── cursors_ref: 779
                ├── cursors_aux: 0
                ├── cursors_cond: 0
                ├── float_blocks_decoded: 431
                ├── float_blocks_size_bytes: 1003552
                ├── 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: 14.805277ms```

Note: EXPLAIN ANALYZE ignores query output, so the cost of serialization to JSON or CSV is not accounted for.

execution_time

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.

planning_time

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.

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:

Select matching series keys from the index, filtered by tag predicates in the WHERE clause.
Group filtered series keys into tag sets based on the GROUP BY dimensions.
Enumerate each tag set and create a cursor and iterator for each series key.
Merge iterators and return the merged result to the query executor.

iterator type

EXPLAIN ANALYZE supports the following iterator types:

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.
(InfluxDB Enterprise only) remote_iterator node represents work done on remote machines.

For more information about iterators, see Understanding iterators.

cursor type

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:

cursor_ref: Reference cursor created for SELECT projections that include a function, such as last() or mean().
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.
cursor_cond: Condition cursor created for fields referenced in a WHERE clause.

For more information about cursors, see Understanding cursors.

block types

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:

For more information about storage blocks, see TSM files.

GRANT

NOTE: Users can be granted privileges on databases that do not yet exist.

grant_stmt = "GRANT" privilege [ on_clause ] to_clause .

Examples

sql

-- grant admin privileges
GRANT ALL TO "jdoe"

-- grant read access to a database
GRANT READ ON "mydb" TO "jdoe"

KILL QUERY

Stop currently-running query.

kill_query_statement = "KILL QUERY" query_id .

Where query_id is the query ID, displayed in the SHOW QUERIES output as qid.

InfluxDB Enterprise clusters: To kill queries on a cluster, you need to specify the query ID (qid) and the TCP host (for example, myhost:8088), available in the SHOW QUERIES output.
sql

KILL QUERY <qid> ON "<host>"


#### Examples

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

sql

-- kill query on InfluxDB Enterprise cluster
KILL QUERY 53 ON "myhost:8088"

REVOKE

sql

revoke_stmt = "REVOKE" privilege [ on_clause ] "FROM" user_name .

Examples

sql

-- revoke admin privileges from jdoe
REVOKE ALL PRIVILEGES FROM "jdoe"

-- revoke read privileges from jdoe on mydb
REVOKE READ ON "mydb" FROM "jdoe"

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 ] .

Examples

Select from all measurements beginning with cpu into the same measurement name in the cpu_1h retention policy

sql

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

Select from measurements grouped by the day with a timezone

sql

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

SHOW CARDINALITY

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.

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.

Filtering by time is only supported when Time Series Index (TSI) is enabled on a database.

See the specific SHOW CARDINALITY commands for details:

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

sql

-- show all continuous queries
SHOW CONTINUOUS QUERIES

SHOW DATABASES

show_databases_stmt = "SHOW DATABASES" .

Example

sql

-- show all databases
SHOW DATABASES

SHOW DIAGNOSTICS

Displays node information, such as build information, uptime, hostname, server configuration, memory usage, and Go runtime diagnostics.

For more information on using the SHOW DIAGNOSTICS command, see Using the SHOW DIAGNOSTICS command for monitoring InfluxDB.

sql

show_diagnostics_stmt = "SHOW DIAGNOSTICS"

SHOW FIELD KEY CARDINALITY

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.

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.

sql

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 ]

Examples

sql

-- 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 KEYS

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

Examples

sql

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

sql

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

SHOW MEASUREMENT CARDINALITY

Estimates or counts exactly the cardinality of the measurement set for the current database unless a database is specified using the ON <database> option.

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.

sql

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

sql

-- 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 ] .

Examples

sql

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

sql

-- show all currently-running queries
SHOW QUERIES
--

SHOW RETENTION POLICIES

show_retention_policies_stmt = "SHOW RETENTION POLICIES" [on_clause] .

Example

sql

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

sql

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

SHOW SERIES CARDINALITY

Estimates or counts exactly the cardinality of the series for the current database unless a database is specified using the ON <database> option.

Series cardinality is the major factor that affects RAM requirements. For more information, see:

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.

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 ]

Examples

sql

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

sql

SHOW SHARD GROUPS

SHOW SHARDS

show_shards_stmt = "SHOW SHARDS" .

Example

sql

SHOW SHARDS

name: telegraf
id  database   retention_policy shard_group start_time           end_time             expiry_time          owners
--  --------   ---------------- ----------- ----------           --------             -----------          ------
16  telegraf   autogen          6           2020-10-19T00:00:00Z 2020-10-26T00:00:00Z 2020-10-26T00:00:00Z 6,7,8
17  telegraf   autogen          6           2020-10-19T00:00:00Z 2020-10-26T00:00:00Z 2020-10-26T00:00:00Z 9,4,5
21  telegraf   autogen          8           2020-10-26T00:00:00Z 2020-11-02T00:00:00Z 2020-11-02T00:00:00Z 8,9,4
22  telegraf   autogen          8           2020-10-26T00:00:00Z 2020-11-02T00:00:00Z 2020-11-02T00:00:00Z 5,6,7
26  telegraf   autogen          10          2020-11-02T00:00:00Z 2020-11-09T00:00:00Z 2020-11-09T00:00:00Z 9,4,5
27  telegraf   autogen          10          2020-11-02T00:00:00Z 2020-11-09T00:00:00Z 2020-11-09T00:00:00Z 6,7,8
31  telegraf   autogen          12          2020-11-09T00:00:00Z 2020-11-16T00:00:00Z 2020-11-16T00:00:00Z 6,7,8

SHOW SHARDS outputs the following data:

id column: Shard IDs that belong to the specified database and retention policy.
shard_group column: Group number that a shard belongs to. Shards in the same shard group have the same start_time and end_time. This interval indicates how long the shard is active, and the expiry_time columns shows when the shard group expires. No timestamps will show under expiry_time if the retention policy duration is set to infinite.
owners column: Shows the data nodes that own a shard. The number of nodes that own a shard is equal to the replication factor. In this example, the replication factor is 3, so 3 nodes own each shard.

SHOW STATS

Returns detailed statistics on available components of an InfluxDB node and available (enabled) components.

Statistics returned by SHOW STATS are stored in memory and reset to zero when the node is restarted, but SHOW STATS is triggered every 10 seconds to populate the _internal database.

The SHOW STATS command does not list index memory usage -- use the SHOW STATS FOR 'indexes' command.

For more information on using the SHOW STATS command, see Using the SHOW STATS command to monitor InfluxDB.

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

Example

sql

> 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 STATS FOR <component>`

For the specified component (<component>), the command returns available statistics. For the runtime component, the command returns an overview of memory usage by the InfluxDB system, using the Go runtime package.

`SHOW STATS FOR 'indexes'`

Returns an estimate of memory use of all indexes. Index memory use is not reported with SHOW STATS because it is a potentially expensive operation.

SHOW SUBSCRIPTIONS

show_subscriptions_stmt = "SHOW SUBSCRIPTIONS" .

Example

sql

SHOW SUBSCRIPTIONS

SHOW TAG KEY CARDINALITY

Estimates or counts exactly the cardinality of tag key set on the current database unless a database is specified using the ON <database> option.

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 ]

Examples

sql

-- 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 ] .

Examples

sql

-- 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 ] .

Examples

sql

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

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.

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

Examples

sql

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

sql

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

Comments

Use comments with InfluxQL statements to describe your queries.

A single line comment begins with two hyphens (--) and ends where InfluxDB detects a line break. This comment type cannot span several lines.
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.

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 .

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 .