All Products
Search
Document Center

INFO Statement

Last Updated: Aug 05, 2020

The INTO clause writes query results to the specified measurement.

Syntax

  1. SELECT_clause INTO <measurement_name> FROM_clause [WHERE_clause] [GROUP_BY_clause]

Description

You can use the following formats to specify a measurement in the INTO clause:

INTO <measurement_name>
Writes data to the specified measurement. If you use the command-line interface (CLI), TSDB for InfluxDB® writes the data to the measurement that uses the DEFAULT retention policy in the database specified by the USE statement. If you use the HTTP API, TSDB for InfluxDB® writes the data to the measurement that uses the DEFAULT retention policy in the database specified by the db parameter.

INTO <database_name>.<retention_policy_name>.<measurement_name>
Writes data to a fully qualified measurement. To fully qualify a measurement, specify its database and retention policy.

INTO <database_name>..<measurement_name>
Writes data to a measurement that uses the DEFAULT retention policy in the specified database.

INTO <database_name>.<retention_policy_name>.:MEASUREMENT FROM /<regular_expression>/
Writes data to the measurements that match the specified regular expression in the FROM clause. The measurements use the specified retention policy in the specified database. :MEASUREMENT indicates a backreference to each measurement that matches the regular expression specified in the FROM clause.

Examples

Rename a database

  1. > SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *
  2. name: result
  3. time written
  4. ---- -------
  5. 0 76290

TSDB for InfluxDB® does not allow you to rename a database. A typical use case of the INTO clause is to migrate data from one database to another database. The preceding query writes all data that uses the autogen retention policy in the NOAA_water_database database to the copy_NOAA_water_database database. The autogen retention policy remains unchanged.

The backreference syntax :MEASUREMENT retains the source measurement names 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 series context for tags. The tags in the source database are stored as fields in the destination database copy_NOAA_water_database.

  1. 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 you prevent out of memory (OOM) errors. The following code block provides examples to explain the syntax for the INTO queries:

  1. SELECT *
  2. INTO <destination_database>.<retention_policy_name>.<measurement_name>
  3. FROM <source_database>.<retention_policy_name>.<measurement_name>
  4. WHERE time > now() - 100w and time < now() - 90w GROUP BY *
  5. SELECT *
  6. INTO <destination_database>.<retention_policy_name>.<measurement_name>
  7. FROM <source_database>.<retention_policy_name>.<measurement_name>}
  8. WHERE time > now() - 90w and time < now() - 80w GROUP BY *
  9. SELECT *
  10. INTO <destination_database>.<retention_policy_name>.<measurement_name>
  11. FROM <source_database>.<retention_policy_name>.<measurement_name>
  12. WHERE time > now() - 80w and time < now() - 70w GROUP BY *

Write query results to a measurement

  1. > SELECT "water_level" INTO "h2o_feet_copy_1" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
  2. name: result
  3. ------------
  4. time written
  5. 1970-01-01T00:00:00Z 7604
  6. > SELECT * FROM "h2o_feet_copy_1"
  7. name: h2o_feet_copy_1
  8. ---------------------
  9. time water_level
  10. 2015-08-18T00:00:00Z 8.12
  11. [...]
  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 command-line interface (CLI), TSDB for InfluxDB® writes the data to the database specified by the USE statement based on the DEFAULT retention policy. If you use the HTTP API, TSDB for InfluxDB® writes the data to the database specified by the db parameter based on the retention policy specified by the rp parameter. If you do not set the rp parameter, the HTTP API automatically writes the data to the database based on the DEFAULT retention policy.

The returned result shows that 7604 points are written to the h2o_feet_copy_1 measurement. In the query result, 1970-01-01T00:00:00Z is returned as the timestamp. TSDB for InfluxDB® uses this timestamp (epoch 0) as a null timestamp.

Write query results to a fully qualified measurement

  1. > SELECT "water_level" INTO "where_else"."autogen"."h2o_feet_copy_2" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
  2. name: result
  3. ------------
  4. time written
  5. 1970-01-01T00:00:00Z 7604
  6. > SELECT * FROM "where_else"."autogen"."h2o_feet_copy_2"
  7. name: h2o_feet_copy_2
  8. ---------------------
  9. time water_level
  10. 2015-08-18T00:00:00Z 8.12
  11. [...]
  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 the data to the where_else database based on 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 7604 points are written to the h2o_feet_copy_2 measurement. In the query result, 1970-01-01T00:00:00Z is returned as the timestamp. TSDB for InfluxDB® uses this timestamp (epoch 0) as a null timestamp.

Write aggregated results to a measurement (downsampling)

  1. > 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)
  2. name: result
  3. ------------
  4. time written
  5. 1970-01-01T00:00:00Z 3
  6. > SELECT * FROM "all_my_averages"
  7. name: all_my_averages
  8. ---------------------
  9. time mean
  10. 2015-08-18T00:00:00Z 8.0625
  11. 2015-08-18T00:12:00Z 7.8245
  12. 2015-08-18T00:24:00Z 7.5675

The query aggregates data by using an InfluxQL function and a GROUP BY time() clause. The query also writes the results to the all_my_averages measurement.

The returned result shows that 3 points are written to the all_my_averages measurement. In the query result, 1970-01-01T00:00:00Z is returned as the timestamp. TSDB for InfluxDB® uses this timestamp (epoch 0) as a null timestamp.

The query is an example of downsampling. The query obtains data based on fine-grained time granularities, aggregates the data based coarse-grained time granularities, and stores the aggregated data in the database. Downsampling is a typical use case of the INTO clause.

Write aggregated results for multiple measurements to a different database (downsampling based on backreferencing)

  1. > 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)
  2. name: result
  3. time written
  4. ---- -------
  5. 1970-01-01T00:00:00Z 5
  6. > SELECT * FROM "where_else"."autogen"./.*/
  7. name: average_temperature
  8. time mean_degrees mean_index mean_pH mean_water_level
  9. ---- ------------ ---------- ------- ----------------
  10. 2015-08-18T00:00:00Z 78.5
  11. name: h2o_feet
  12. time mean_degrees mean_index mean_pH mean_water_level
  13. ---- ------------ ---------- ------- ----------------
  14. 2015-08-18T00:00:00Z 5.07625
  15. name: h2o_pH
  16. time mean_degrees mean_index mean_pH mean_water_level
  17. ---- ------------ ---------- ------- ----------------
  18. 2015-08-18T00:00:00Z 6.75
  19. name: h2o_quality
  20. time mean_degrees mean_index mean_pH mean_water_level
  21. ---- ------------ ---------- ------- ----------------
  22. 2015-08-18T00:00:00Z 51.75
  23. name: h2o_temperature
  24. time mean_degrees mean_index mean_pH mean_water_level
  25. ---- ------------ ---------- ------- ----------------
  26. 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 data in each measurement that matches the regular expression specified in the FROM clause. Then, the query writes the results to the specified measurement in the where_else database based on the autogen retention policy. The specified measurement has the same name as the source measurement. 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 5 points are written to the where_else database based on the autogen retention policy. In the query result, 1970-01-01T00:00:00Z is returned as the timestamp. TSDB for InfluxDB® uses this timestamp (epoch 0) as a null timestamp.

The query is an example of downsampling that is implemented based on backreferencing. The query obtains data based on fine-grained time granularities from multiple measurements, aggregates the data based coarse-grained time granularities, and stores the aggregated data in the database. Downsampling based on backreferencing is a typical use case of the INTO clause.

FAQ about the INTO clause

How do I prevent a data loss?

If the SELECT clause includes a tag key in an INTO query, the query converts tags in the current measurement to fields in the destination measurement. As a result, TSDB for InfluxDB® may overwrite the points that are differentiated by tag values. Note that this rule does not apply 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 in the INTO query, such as, GROUP BY (tag key) or GROUP BY *.

How do I use the INTO clause to automate queries?

The INTO clause section describes how to use an INTO clause to manually implement queries. To automate the queries that uses the INTO clause to query real-time data, use continuous queries (CQ). Continuous queries also automate the downsampling process.


InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.