Query Problem - removing prefix to field name of into clause?

Store
1

When using into clause with mean(*), influxdb adds prefix “mean_” to all fieldnames.

> select mean(*) into cpu_data_2m from cpu_data where time > now() - 10m group by time(2m)
name: result
time written
---- -------
0    5

> select * from cpu_data_2m
name: cpu_data_2m
time                mean_sys mean_user mean_util mean_wait
----                -------- --------- --------- ---------
1495179480000000000 1        2.3       3.3       0.1
1495179600000000000 1        2.5       3.5       0.1
1495179720000000000 1        2.4       3.45      0.1

Is there any statement like select mean(*) as * into that makes new fieldnames same as the source measurement?

2

@tsunami42 This is a known issue. I’ve gone ahead and posted this report there, but if you could go chime in too it would be helpful. Thank you for sending this in!

3

The “known issue” links to current post, could you update the link? :slight_smile:

1 Like
4

InfluxDB issue 7332.

5

@tsunami42 Sorry about that! :speak_no_evil: Thanks @mark

6

A workaround to this issue is to specify the function on individual fields. In this case your query becomes

select mean(sys)as sys, mean(user) as user, mean(util) as util, mean(wait) as wait into cpu_data_2m from cpu_data where time > now() - 10m group by time(2m)

7

Thanks for your suggestion. I will just write more about my motivation for asking the question.

When I asked the question, I wanted to use CQ to downsample all my data with multiple precision level, like 1m -> 5m -> 20m -> 1h.

CQ Example:

CREATE CONTINUOUS QUERY "downsample" ON "telegraf"
RESAMPLE EVERY 5m FOR 10m 
BEGIN 
	SELECT mean(*) INTO "telegraf"."5m_sample".:MEASUREMENT FROM /.*/ GROUP BY time(5m), *
END

Multiple precision level means multiple prefix to the fieldname, which makes it more complicated for our API provider to auto choose the appropriate rp or db based on the query duration.

So I thought that will be great it I can solve the problem with 3~4 CQs. And I was too lazy to write the CQ management script to generate CQs for all measurement, Hoping to find a statement to do it for me, which the reason that I ask the question.

And now I find some performance issue with the regular_expression_measurement in the into clause, every time I execute something like “SELECT mean() INTO “telegraf”.“5m_sample”.:MEASUREMENT FROM /./ where time >= <start_time> and time < <end_time> GROUP BY time(5m), *”, It will stuck like, forever. (offline, about 900 measurement, 100,000 series in that time range I guess)

Finally, without the ability to duplicate the online work load and dig into it, I decide to write CQ management script. Wish me good luck.

8

That’s interesting I see you are looking to create CQs on data created by other CQs. Or this is what I would suggest doing if you are dealing with performance issues. Theoretically, dealing with lesser series should help with generating the data quicker.

Good luck with your CQ management script.