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 76290Databases 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 4The 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 4The 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.5675The 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.75The 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®.