Select Into problems

chronograf

#1

Hello,
I’ve a problem aggregation data in the Influxdb.

There is a table with massive measurments within a resolution about 5 seconds. And I want to aggregate the data so i will get a resolution of 1 minute.
This is the query:

“SELECT mean(value) AS avg, min(value) AS min, max(value) AS max, count(value) AS count, sum(value) AS sum
INTO monitor.rp_minute.facts
FROM monitor.rp_sekunde.facts
WHERE wert_id = ‘2117’
AND time >= ‘2019-02-23 12:00:00’
AND time <= ‘2019-02-23 13:00:00’
GROUP BY time(1m), wert_id
fill(none)”

The strange problem ist that if i send the query with the chronograph it works. But if i send it from the python application there is no data in rp_minute.facts. I can see that the query which is send by python arrives at the influxdb (seen with “show queries”) and the syntax is correct.
I’ve checked the database connection twice.

I use the " InfluxDB-Python" Library. On an Ubuntu LTS 18.04.
Influxdb-Version: 1.7.4

And at this point i’ve no clue how to debug this problem.

Thank you in advance


#2

Hi ,

to help you debug I have some questions :slight_smile:

do you have some more details from your python script ,
what is the syntax that you found with show queries ?
I assume you have to execute this query only once and then you will use
a continuous query to do the aggregation of new data ?
So you can achieve the same result without using python ?
best regards


#3

Hi Marc,

Thank you for your answer!

Here are my answers:

  • Python assembles the query you can see in my first post. I think python isn’t the problem because the query arrives in finlux as shown in the first post.
  • A continous query doesn’t fit on my conditions. Some times the data that is imported is a few days or weeks old. So i need to aggregate the data which was imported before.

regards


#4

Maybe some silly questions now …
How did you check if it worked or not after running the python script ?
Did you check in the same way after running it with chronograf ?
What is the result when you execute the query in influx cli ?


#5

I look with the chronograph if there is any data in the “rp_minute.facts”. And yes I check it in the same way.
I didn’t test it with the cli, I think that would be the same result as the way with the chronograph.


#6

Do you have errors in the logfiles ?
Do you find something in journalctl -u influxdb ?


#7

I didn’t find any specific influx log file.

These are examples from the syslog:

Mar 1 14:55:09 el-smart-influx influxd[1656]: [httpd] 172.16.3.51 - influx [01/Mar/2019:14:55:09 +0000] “GET /query?db=monitor&q=SELECT+mean%28%22value%22%29+AS+%22avg%22%2C+min%28%22value%22%29+AS+%22min%22%2C+max%28%22value%22%29+AS+%22max%22%2Ccount%28%22value%22%29+AS+%22count%22%2C+sum%28%22value%22%29+AS+%22sum%22+%0AINTO+%22monitor%22.%22rp_minute%22.%22facts%22+%0AFROM+%22monitor%22.%22rp_sekunde%22.%22facts%22+%0AWHERE+%22wert_id%22%3D%272575%27+AND+time+%3E%3D+%272018-12-08+02%3A16%3A00%27+AND+time+%3C%3D+%272018-12-08+02%3A32%3A00%27+%0AGROUP+BY+time%281m%29%2C+wert_id+FILL%28none%29 HTTP/1.1” 200 377 “-” “python-requests/2.9.1” 01fa4a27-3c32-11e9-96a5-005056bd2003 7933

Mar 1 14:58:31 el-smart-influx influxd[1792]: ts=2019-03-01T14:58:31.972109Z lvl=info msg=“Executing query” log_id=0DvS11pG000 service=query query=“SELECT mean(value) AS avg, min(value) AS min, max(value) AS max, count(value) AS count, sum(value) AS sum INTO monitor.rp_minute.facts FROM monitor.rp_sekunde.facts WHERE wert_id = ‘2986’ AND time >= ‘2019-03-01 12:04:00’ AND time <= ‘2019-03-01 12:16:00’ GROUP BY time(1m), wert_id fill(none)”


#8

is it possible that you use a different wert_id in the were clause in chronograf and python ?


#9

Thats not possible, I’ve checked that several times.


#10

or a different date ? because in the output you showed :
wert_id%22%3D%272575%27+AND+time+%3E%3D+%272018-12-08+02%3A16%3A00%27+AND+time+%3C%3D+%272018-12-08+02%3A32%3A00%27+%0AGROUP+BY+time%281m%29%2C+wert_id+FILL%28none%29 HTTP/1.1” 200 377 “-” “python-requests/2.9.1” 01fa4a27-3c32-11e9-96a5-005056bd2003 7933

I see wert_id= 2575 , and the date is 2018 …