Dec 4, 2021

influxdb: copying data with SELECT INTO - pay attention to the TAGS (or they are transformed to fields)

 If you are using influxdb, one usecase could be, copy the data from a measurement ("table") to another.

This can be done with this statement:

select * into testtable2 from testtable1

By the way: the CLI is opened with

/usr/bin/influx -unsafeSsl -ssl -database telegraf
(if your database is named telegraf)

In my case (zigbee / mqtt / telegraf) the layout of mqtt_consumer measurement was like this:

> show tag keys from mqtt_consumer
name: mqtt_consumer
> show field keys from mqtt_consumer
name: mqtt_consumer
fieldKey    fieldType
--------    ---------
battery     float
contact     boolean
current     float
But after copying this to a testtable, the tags where gone and everything was a field. 

This is not a big problem - you can work with that data without a problem. BUT if you want to copy it back or merge it to the original table, you will get a table with the additional columns host_1 and topic_1.

This is because for influx you already had a column host. So it added a column field host_1. 

If a query in this new table (with host + host_1) spans over a time where both of this columns are in, you only select the data, with the entry host. If the time spans only entries with host_1, it is shown as host and you get your data. Really a unpredictable way to get data.

What is the solution? Easy:

select * into table1 from mqtt_consumer group by host,topic
The "group by" does not group anything. It just tells influx: host & topic are tags and not fields. Please do not transform them...

1 comment:

  1. 25% OFF on Oracle Apps R12 Financials Self Paced Course along with 11 Additional Add On Courses (321 Session Videos of 120 Hours Recordings). Our Top Trending Course with 1700 Enrolled Udemy Students

    Please Check for details