Time Series Database:INTO clause

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

This writes 7604 points from the water_level field in h2o_feet to a new measurement named h2o_feet_copy_1. The 1970-01-01T00:00:00Z timestamp in the result row is epoch 0, which TSDB for InfluxDB® uses as a null timestamp equivalent — it does not indicate actual write time.

If you use the HTTP API, the retention policy specified by the rp parameter is used. If you omit the rp parameter, the data is written to the DEFAULT retention policy.

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

This writes 7604 points to the h2o_feet_copy_2 measurement in the where_else database under the autogen retention policy. The where_else database and autogen retention policy must exist before running the query. For details, see the "Database management" topic.

Downsample query results to a measurement

> 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

This uses MEAN() with GROUP BY time(12m) to aggregate water_level into 12-minute intervals, then writes 3 points to all_my_averages. Querying fine-grained data and storing aggregated results at a coarser interval — known as downsampling — is a primary use case for the INTO clause.

Downsample across multiple measurements using backreferencing

> 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 :MEASUREMENT backreference maps each source measurement name to an identically named measurement in where_else.autogen. This writes 5 points across 5 measurements. The where_else database and autogen retention policy must exist before running the query.

Migrate data between databases

Databases cannot be renamed in TSDB for InfluxDB®. To effectively rename a database, migrate its data to a new database with INTO and the :MEASUREMENT backreference:

> SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *

name: result
time written
---- -------
0    76290

This copies all data from NOAA_water_database.autogen to copy_NOAA_water_database.autogen, preserving measurement names and — because of GROUP BY * — keeping all tags as tags in the destination. Without GROUP BY *, tags in the source become fields in the destination (see Missing data below).

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 *

Missing data

If the SELECT clause includes tag keys, the INTO query converts those tags to fields in the destination measurement. Because fields do not differentiate points the way tags do, TSDB for InfluxDB® may overwrite points that were previously unique due to different tag values. This behavior does not apply to queries that use the TOP() or BOTTOM() functions.

The following example shows how omitting GROUP BY * causes data loss. Starting data in french_bulldogs:

time                  color  name
2016-05-25T00:05:00Z  peach  nugget
2016-05-25T00:05:00Z  grey   rumple
2016-05-25T00:10:00Z  black  prince

Without GROUP BY *, color becomes a field and two points share the same timestamp, so nugget is overwritten:

SELECT * INTO "all_dogs" FROM "french_bulldogs"

Result — nugget is lost:

time                  color  name
2016-05-25T00:05:00Z  grey   rumple
2016-05-25T00:10:00Z  black  prince

With GROUP BY *, color is preserved as a tag and both points are retained:

SELECT "name" INTO "all_dogs" FROM "french_bulldogs" GROUP BY *

Result — all three points are present:

time                  color  name
2016-05-25T00:05:00Z  peach  nugget
2016-05-25T00:05:00Z  grey   rumple
2016-05-25T00:10:00Z  black  prince

To preserve tags as tags in the destination, add GROUP BY * (or GROUP BY <tag_key>) to the INTO query.

Automating queries with the INTO clause

The examples above show how to run INTO queries manually. To automate them against real-time data — including scheduled downsampling — use continuous queries. The following example creates a continuous query that aggregates water_level into 30-minute averages and writes the results to a separate retention policy:

CREATE CONTINUOUS QUERY "cq_30m" ON "my_database" BEGIN
  SELECT MEAN("water_level")
  INTO "long_term"."autogen"."water_level_30m"
  FROM "h2o_feet"
  GROUP BY time(30m)
END

For the complete workflow including retention policy setup, see the continuous queries documentation.

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