Influxdb: How to Use Literal Character Strings in InfluxQL SELECT

[zlq4863947]

__System info:__ macOS Sierra 10.12, InfluxDB 1.3.5

__Steps to reproduce:__

1. Create measurement And Insert data

 > insert test,mode=1 uuid="d79df0c0-c52a-11e7-929e-83a8856009b0" 
 > insert test,mode=1 uuid="d79df0c0-c52a-11e7-929e-83a8856009b0"

__Expected behavior:__
I want to put the “5min” fixed string into result set.

> select uuid, "5min" as unit from test
name: test
time                uuid                                 unit
----                ----                                 ----
1510279506236603945 d79df0c0-c52a-11e7-929e-83a8856009b0 5min
1510279507204696343 d79df0c0-c52a-11e7-929e-83a8856009b0 5min

__Actual behavior:__
But influxdb didn't put in 5min to result set

> select uuid, "5min" as unit from test
name: test
time                uuid                                 unit
----                ----                                 ----
1510279506236603945 d79df0c0-c52a-11e7-929e-83a8856009b0 
1510279507204696343 d79df0c0-c52a-11e7-929e-83a8856009b0 

[jsternberg]

I'm not entirely certain of the use case here. So you want to put a literal that you specify into the response?

If you can explain the use case, maybe I would understand what you want and why better.

[alanjcastonguay]

@jsternberg I was hoping to do something like the above in conjunction with SELECT ... INTO, to set a tag (eg the device that the sensor is attached to) that wasn't known at the time of the original ingestion. (And leaving out the import_job field at the same time.)

[antonbayer]

I have more or less the same use case: setting a tag in a continuous query.

[aspring]

I have a similar need -- my use case is using the functionality to add annotation data to the result -- this specifically is for Grafana annotations.

Doing something like this would remove the need for a CQ:

SELECT "host" as "tags", "state_candidate", "Election Started" as "title" FROM "consul_raft" WHERE $timeFilter

[memetb]

Same need.

For instance: Grafana has annotation queries that take "title" and "description" fields (and an optional tag field). It makes no sense to store a UI string in the database as a tag.

To clarify, grafana annotation query expects a query to return:

select "title", "description", [optional extra tag] from ...

It doesn't make sense to store two extra tags called "title" and "description" in every measurement in case I need to use it as a query for Grafana.

[palmerabollo]

It's also useful in node-red, to join the results from two different influxdb queries and still be able to distinguish each one.

[robie2011]

same need.

I like to reduce the output of a field value to specific range. E.g. 0 or 1. So I tried SQL-Style-Like min(fieldname, 1) but it didn't work.

[MarkusBe]

Same need here. I need this for Grafana-Annotations.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.

[timhallinflux]

Possible today with Flux technical preview with 1.7. Should be supported for production workloads with 1.8 -- Flux GA.

[elhenro]

@timhallinflux Can please describe how this is possible?

[rawadrifai]

@timhallinflux ?

[timhallinflux]

Here's an example in Flux, using the map() function:
https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/built-in/transformations/map/#add-new-columns-and-preserve-existing-columns

if you are using 1.x, the same example is described here: https://docs.influxdata.com/flux/v0.65/stdlib/built-in/transformations/map#sidebar

This allows you to dynamically add these columns on the fly. It does NOT store the new columns into the DB.

Does that help?

[timhallinflux]

Another, slightly different example... I want to inject some additional dynamic data into the result set. In this case, I'm trying to put a "wildcard" value into a list of tags. In my case, I have a set of tags for the devices I'm monitoring. I want a dashboard that allows me to see all the hosts in aggregate, but also drill-down into specific devices. In this case I use union() and dynamically add the tag like this:
```import "influxdata/influxdb/v1"
import "csv"

hosts = v1.tagValues(
bucket: "default",
tag: "host",
start: -72h
)

wildcard = csv.from(csv: "

datatype,string,long,string

group,false,true,false

default,,,

,result,table,_value
,,0,*
")

union(tables: [hosts,wildcard])

Then, in my dashboard queries, I use conditional logic to alter the filters.  Here's an example:

rom(bucket: v.bucket)
|> range(start: v.timeRangeStart)
|> filter(fn: (r) => r._measurement == "cpu")
|> filter(fn: (r) => r._field == "usage_user" or r._field == "usage_system" or r._field == "usage_idle")
|> filter(fn: (r) =>
if v.host != "*"
then r.cpu == "cpu-total" and r.host == v.host
else r.cpu == "cpu-total")
|> window(period: v.windowPeriod)
|> mean()
|> group(columns: ["_value", "_time", "_start", "_stop"], mode: "except")
|> yield(name: "mean")
```

[timhallinflux]

Using InfluxDB 2.0, along with Flux Tasks, I believe you could add the literals back into the DB by using the map() example. In this case you would use the to() function to write the resulting data back into the measurement using the same timestamps and other tag keys. Since InfluxDB is an append only system, it will overwrite the existing points (same timestamps and tag keys) with the new fields.

At the moment, there is no to() function in the 1.x version of Flux. This is being considered for a future 1.x release here: https://github.com/influxdata/influxdb/issues/17790