You can use a SELECT statement to query data in one or more measurements.
Syntax
SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]
Syntax description
Each SELECT
statement includes a SELECT
clause and a FROM
clause.
SELECT clauses
SELECT
clauses support the following formats:
SELECT *
: returns all fields and tags.SELECT "<field_key>"
: returns a specified field.SELECT "<field_key>","<field_key>"
: returns multiple fields.SELECT "<field_key>","<tag_key>"
: returns a specified field and tag. If you specify tags in aSELECT
clause, you must specify one or more fields.SELECT "<field_key>"::field,"<tag_key>"::tag
: returns a specified field and tag. The::[field | tag]
syntax defines the type of an identifier to differentiate the field key and tag key that have the same name.
SELECT
clauses also support arithmetic operators, functions, conversion operators, and regular expressions.
FROM clauses
FROM
clauses support the following measurement formats:
FROM <measurement_name>
: returns data from a measurement. If you use the command-line interface (CLI) that is provided by TSDB for InfluxDB®, the measurement belongs to the database that is specified by the USE
parameter. In this case, the default retention policy is used. If you call an HTTP API operation, the measurement belongs to the database that is specified by the db
parameter. In this case, the default retention policy is used.
FROM <measurement_name>,<measurement_name>
: returns data from multiple measurements.
FROM <database_name>.<retention_policy_name>.<measurement_name>
: returns data from a measurement that is fully qualified. The measurement is fully qualified by specifying a database and retention policy.
FROM <database_name>..<measurement_name>
: returns data from a measurement that belongs to a specified database and uses the default retention policy.
FROM
clauses also support regular expressions.
Quotations
If an identifier includes characters other than [A-z,0-9,_], or start with a digit or an InfluxQL keyword, the identifier must be enclosed in double quotation marks("). We recommend that you use double quotation marks(") for identifiers.
Note: The syntax of quotation marks (") for identifiers is different from the syntax that is defined in the InfluxDB line protocol.
Examples
Query all fields and tags in a single measurement
> SELECT * FROM "h2o_feet"
name: h2o_feet
--------------
time level description location water_level
2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
[...]
2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938
This statement is used to query all fields and tags in the h2o_feet
measurement.
If you use the CLI, enter the USE NOAA_water_database
before the statement. The queried measurement belongs to the database that is specified by the USE
parameter and uses the default retention policy. If you call an HTTP API operation, set the db
parameter to NOAA_water_database
. If you do not specify the rp
parameter, the default retention policy of the database is used.
Query specified fields and tags in a single measurement
> SELECT "level description","location","water_level" FROM "h2o_feet"
name: h2o_feet
--------------
time level description location water_level
2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
[...]
2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938
In this statement, the level description
field, the water_level
field, and the location
tag are specified. If you specify tags in a SELECT
clause, you must specify one or more fields.
Query fields and tags with specified identifier types in a single measurement
> SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_feet"
name: h2o_feet
--------------
time level description location water_level
2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
[...]
2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938
In this statement, the level description
field, the water_level
field, and the location
tag are specified. The ::[field | tag]
syntax defines the type of each identifier. To differentiate the field key and tag key that have the same name, you must use the ::[field | tag]
syntax. If a statement does not contain the field key and tag key that have the same name, you do not need to use this syntax.
Query all fields in a single measurement
> SELECT *::field FROM "h2o_feet"
name: h2o_feet
--------------
time level description water_level
2015-08-18T00:00:00Z below 3 feet 2.064
2015-08-18T00:00:00Z between 6 and 9 feet 8.12
[...]
2015-09-18T21:36:00Z between 3 and 6 feet 5.066
2015-09-18T21:42:00Z between 3 and 6 feet 4.938
In this example, all fields in the h2o_feet
measurement are queried. SELECT
clauses supports a combination of the *
and ::
syntax.
Query a specified field in a single measurement and perform arithmetic operations
> SELECT ("water_level"*2)+4 from "h2o_feet"
name: h2o_feet
--------------
time water_level
2015-08-18T00:00:00Z20.24
2015-08-18T00:00:00Z8.128
[...]
2015-09-18T21:36:00Z14.132
2015-09-18T21:42:00Z13.876
Query all data in multiple measurements
> SELECT * FROM "h2o_feet","h2o_pH"
name: h2o_feet
--------------
time level description location pH water_level
2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
[...]
2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938
name: h2o_pH
------------
time level description location pH water_level
2015-08-18T00:00:00Z santa_monica 6
2015-08-18T00:00:00Z coyote_creek 7
[...]
2015-09-18T21:36:00Z santa_monica 8
2015-09-18T21:42:00Z santa_monica 7
In this example, all fields and tags in the h2o_feet
and h2o_pH
measurements are queried. You must separate multiple measurements with commas (,
).
Query all data in a fully qualified measurement
> SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet"
name: h2o_feet
--------------
time level description location water_level
2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
[...]
2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938
In this example, all data in the h2o_feet
measurement is queried. The h2o_feet
measurement belongs to the NOAA_water_database
database and uses the autogen
retention policy.
If you use the CLI, you can specify a fully qualified measurement instead of using the USE
and DEFAULT
parameters. If you call an HTTP API operation, you can specify a fully qualified measurement instead of using the db
and rp
parameters.
Query all data in a measurement that belongs to a specified database
> SELECT * FROM "NOAA_water_database".."h2o_feet"
name: h2o_feet
--------------
time level description location water_level
2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
[...]
2015-09-18T21:36:00Z between 3 and 6 feet santa_monica 5.066
2015-09-18T21:42:00Z between 3 and 6 feet santa_monica 4.938
In this example, all data in the h2o_feet
measurement is queried. The h2o_feet
measurement belongs to the NOAA_water_database
database and uses the default retention policy. ..
indicates the default retention policy of the database.
If you use the CLI, you can specify a database instead of using the USE
parameter. If you call an HTTP API operation, you can specify a database instead of using the db
parameter.
FAQ about SELECT
statements
How do I query tag keys by using a SELECT clause?
To query tag keys, you must specify at least one field key in the SELECT
clause. If the SELECT
clause contains only tag keys, an empty string is returned. This is caused by the data storage mechanism of TSDB.
Example
In the following example, no data is returned because only the location
tag key is specified in the SELECT
clause:
> SELECT "location" FROM "h2o_feet"
>
To query the location
tag key, you must specify at least one field key in the SELECT
clause. In the following example, the water_level
field key is specified:
> SELECT "water_level","location" FROM "h2o_feet" LIMIT 3
name: h2o_feet
time water_level location
-----------------------
2015-08-18T00:00:00Z8.12 coyote_creek
2015-08-18T00:00:00Z2.064 santa_monica
[...]
2015-09-18T21:36:00Z5.066 santa_monica
2015-09-18T21:42:00Z4.938 santa_monica
InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.