The INTO clause writes query results to the specified measurement.
Syntax
SELECT_clause INTO <measurement_name> FROM_clause [WHERE_clause] [GROUP_BY_clause]
Description
You can use one of the following formats to specify a measurement in the INTO
clause:
INTO <measurement_name>
writes data to the specified measurement. If you use the CLI to write data, the data is written to the measurement in the database specified by the USE statement. The DEFAULT
retention policy is used. If you use the HTTP API to write data, the data is written to the measurement in the database specified by the db
parameter. The DEFAULT
retention policy is used.
INTO <database_name>.<retention_policy_name>.<measurement_name>
writes data to a fully qualified measurement. To fully qualify a measurement, specify the database and retention policy.
INTO <database_name>..<measurement_name>
writes data to a measurement in the specified database that uses the DEFAULT retention policy.
INTO <database_name>.<retention_policy_name>.:MEASUREMENT FROM /<regular_expression>/
writes data to measurements that match the regular expression specified in the FROM
clause. The measurements are in the specified database that uses the specified retention policy. :MEASUREMENT
specifies a backreference to each measurement that matches the regular expression specified in the FROM
clause.
Examples
Rename a database
> SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *
name: result
time written
---- -------
0 76290
Databases cannot be renamed in TSDB for InfluxDB®. A common use case of the INTO
clause is to migrate data from one database to another. The preceding query writes all data in the NOAA_water_database
database that uses the autogen
retention policy to the copy_NOAA_water_database
database that also uses the autogen
retention policy.
The backreference syntax :MEASUREMENT
keeps the name of the measurement in the source database unchanged in the destination database. Make sure that the NOAA_water_database
database and the autogen
retention policy are available before you run the INTO
query. For more information about how to manage databases and retention policies, see the "Database management" topic.
The GROUP BY *
clause stores the tags in the source database as tags in the destination database. The following query does not maintain the context of time series for tags. The tags in the source database are stored as fields in the destination database copy_NOAA_water_database
.
SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/
When you migrate large amounts of data, we recommend that you run INTO
queries for different measurements in sequence and use the time boundaries specified in the WHERE
clause. This helps to prevent out of memory (OOM) errors. The following code block provides sample syntax for this type of query:
SELECT *
INTO <destination_database>.<retention_policy_name>.<measurement_name>
FROM <source_database>.<retention_policy_name>.<measurement_name>
WHERE time > now() - 100w and time < now() - 90w GROUP BY *
SELECT *
INTO <destination_database>.<retention_policy_name>.<measurement_name>
FROM <source_database>.<retention_policy_name>.<measurement_name>}
WHERE time > now() - 90w and time < now() - 80w GROUP BY *
SELECT *
INTO <destination_database>.<retention_policy_name>.<measurement_name>
FROM <source_database>.<retention_policy_name>.<measurement_name>
WHERE time > now() - 80w and time < now() - 70w GROUP BY *
Write query results to a measurement
> SELECT "water_level" INTO "h2o_feet_copy_1" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
name: result
------------
time written
1970-01-01T00:00:00Z 7604
> SELECT * FROM "h2o_feet_copy_1"
name: h2o_feet_copy_1
---------------------
time water_level
2015-08-18T00:00:00Z 8.12
[...]
2015-09-18T16:48:00Z 4
The query writes the results to a new measurement named h2o_feet_copy_1
. If you use the CLI to write data, the data is written to the database specified by the USE
statement. The DEFAULT
retention policy is used. If you use the HTTP API to write data, the data is written to the database specified by the db
parameter. The retention policy specified by the rp
parameter is used. If you do not specify the rp
parameter, the HTTP API automatically writes data to the database that uses the DEFAULT retention policy.
The returned result shows that TSDB for InfluxDB® writes 7604
points to the h2o_feet_copy_1
measurement. The timestamp in the returned result is invalid. TSDB for InfluxDB® uses epoch 0
(1970-01-01T00:00:00Z
) as a null timestamp.
Write query results to a fully qualified measurement
> SELECT "water_level" INTO "where_else"."autogen"."h2o_feet_copy_2" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
name: result
------------
time written
1970-01-01T00:00:00Z 7604
> SELECT * FROM "where_else"."autogen"."h2o_feet_copy_2"
name: h2o_feet_copy_2
---------------------
time water_level
2015-08-18T00:00:00Z 8.12
[...]
2015-09-18T16:48:00Z 4
The query writes the results to a new measurement named h2o_feet_copy_2
. TSDB for InfluxDB® writes data to the where_else
database that uses the autogen
retention policy. Make sure that the where_else
database and the autogen
retention policy are available before you run the INTO
query. For more information about how to manage databases and retention policies, see the "Database management" topic.
The returned result shows that TSDB for InfluxDB® writes 7604
points to the h2o_feet_copy_2
measurement. The timestamp in the returned result is invalid. TSDB for InfluxDB® uses epoch 0
(1970-01-01T00:00:00Z
) as a null timestamp.
Write aggregated results to a measurement (downsampling)
> SELECT MEAN("water_level") INTO "all_my_averages" FROM "h2o_feet" WHERE "location" = 'coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
name: result
------------
time written
1970-01-01T00:00:00Z 3
> SELECT * FROM "all_my_averages"
name: all_my_averages
---------------------
time mean
2015-08-18T00:00:00Z 8.0625
2015-08-18T00:12:00Z 7.8245
2015-08-18T00:24:00Z 7.5675
The query aggregates data by using an InfluxQL function and a GROUP BY time()
clause. Then, the query writes the results to the all_my_averages
measurement.
The returned result shows that TSDB for InfluxDB® writes 3
points to the all_my_averages
measurement. The timestamp in the returned result is invalid. TSDB for InfluxDB® uses epoch 0
(1970-01-01T00:00:00Z
) as a null timestamp.
The query retrieves data based on fine-grained time granularities and aggregates the data based coarse-grained time granularities. Then, the query stores the aggregated data in the database. The query is an example of downsampling. Downsampling is a common use case of the INTO
clause.
Write data
Write the aggregated results for multiple measurements to a different database. Backreferencing is used for downsampling.
> SELECT MEAN(*) INTO "where_else"."autogen".:MEASUREMENT FROM /.*/ WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:06:00Z' GROUP BY time(12m)
name: result
time written
---- -------
1970-01-01T00:00:00Z 5
> SELECT * FROM "where_else"."autogen"./.*/
name: average_temperature
time mean_degrees mean_index mean_pH mean_water_level
---- ------------ ---------- ------- ----------------
2015-08-18T00:00:00Z 78.5
name: h2o_feet
time mean_degrees mean_index mean_pH mean_water_level
---- ------------ ---------- ------- ----------------
2015-08-18T00:00:00Z 5.07625
name: h2o_pH
time mean_degrees mean_index mean_pH mean_water_level
---- ------------ ---------- ------- ----------------
2015-08-18T00:00:00Z 6.75
name: h2o_quality
time mean_degrees mean_index mean_pH mean_water_level
---- ------------ ---------- ------- ----------------
2015-08-18T00:00:00Z 51.75
name: h2o_temperature
time mean_degrees mean_index mean_pH mean_water_level
---- ------------ ---------- ------- ----------------
2015-08-18T00:00:00Z 63.75
The query aggregates data by using an InfluxQL function and a GROUP BY time()
clause. The query aggregates the data in each measurement that matches the regular expression specified in the FROM
clause. Then, the query writes the results to the measurements that have the same name as those measurements in the where_else
database that uses the autogen
retention policy. Make sure that the where_else
database and the autogen
retention policy are available before you run the INTO
query.
The returned result shows that TSDB for InfluxDB® writes 5
points to the where_else
database that uses the autogen
retention policy. The timestamp in the returned result is invalid. TSDB for InfluxDB® uses epoch 0
(1970-01-01T00:00:00Z
) as a null timestamp.
The query retrieves data from multiple measurements based on fine-grained time granularities and aggregates the data based coarse-grained time granularities. Then, the query stores the aggregated data in a different database. The query is an example of downsampling with backreferencing. Downsampling with backreferencing is a common use case of the INTO
clause.
FAQ about the INTO clause
How do I prevent a data loss?
If the SELECT
clause in an INTO
query contains tag keys, the query converts tags in the current measurement to fields in the destination measurement. TSDB for InfluxDB® may overwrite the points that are differentiated by tag values. This rule is not applicable to the queries that use the TOP()
or BOTTOM()
function.
To store the tags in the current measurement as tags in the destination measurement, add a GROUP BY
clause to the INTO
query, such as GROUP BY
the relevant tag key or GROUP BY *
.
How do I use the INTO clause to automate queries?
The "INTO
clause" topic shows how to use the INTO
clause to manually run queries. To automate queries that use the INTO
clause to query real-time data, use continuous queries. You can also use continuous queries to automate the downsampling process.
InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.