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
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
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.
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.
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.
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 theSHOW 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. TheSHOW 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.
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.
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:
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.
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 andSELECT
statements are executed in shards.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.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
andOFFSET
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®.