Hi,
New to influxdb. So far I am impressed, and quite easily set up a database to monitor my electrical system. A service polls a REST API every 15 seconds, adding the data to a local copy of influxdb. It does a nice job of providing data to an instance of grafana, and I’m very happy with what I’ve been able to achieve.
However, I wonder if anyone can help me track down a problem I’m having with integrated continuous queries? I just can’t get mine to work. What I’m trying to do is create hourly aggregates of the 15s data, by integrating them and adding them to an integrated_data measurement in an open-ended retention policy.
Everything seem to run. The log files shows nothing out of the ordinary. However, nothing has been added to integrated_data. Integrated_data does exist, but where I SELECT mean(*) FROM integrated_data I get nothing. Any idea what I’m doing wrong, or whether it’s a bug? Database details, retention policies, continuous queries, and logs for one round of them are below.
I’d be grateful for anyone’s insights.
–
DATABASE:
database: powerwall
measurements uploaded:
site (exported, imported, power, voltage), battery (exported, imported, power, voltage), load (exported, imported, power, voltage), solar (exported, imported, power, voltage) and battery_level (percentage)
all uploaded every 15 seconds, added to default retention policy “weekly”
RETENTION POLICIES:
name duration shardGroupDuration replicaN default
autogen 0s 168h0m0s 1 false
weekly 168h0m0s 24h0m0s 1 true
CONTINUOUS QUERIES:
name: powerwall
name query
site_hourly CREATE CONTINUOUS QUERY site_hourly ON powerwall RESAMPLE EVERY 15m BEGIN SELECT integral(power) AS grid_flow INTO powerwall.autogen.integrated_data FROM powerwall.weekly.site GROUP BY time(1h) END
battery_hourly CREATE CONTINUOUS QUERY battery_hourly ON powerwall RESAMPLE EVERY 15m BEGIN SELECT integral(power) AS battery_flow INTO powerwall.autogen.integrated_data FROM powerwall.weekly.battery GROUP BY time(1h) END
load_hourly CREATE CONTINUOUS QUERY load_hourly ON powerwall RESAMPLE EVERY 15m BEGIN SELECT integral(power) AS home_usage INTO powerwall.autogen.integrated_data FROM powerwall.weekly.load GROUP BY time(1h) END
solar_hourly CREATE CONTINUOUS QUERY solar_hourly ON powerwall RESAMPLE EVERY 15m BEGIN SELECT integral(power) AS solar_production INTO powerwall.autogen.integrated_data FROM powerwall.weekly.solar GROUP BY time(1h) END
battery_level_hourly CREATE CONTINUOUS QUERY battery_level_hourly ON powerwall RESAMPLE EVERY 15m BEGIN SELECT mean(percentage) AS battery_level INTO powerwall.autogen.integrated_data FROM powerwall.weekly.battery_level GROUP BY time(1h) END
LOGS:
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.890592Z lvl=info msg=“Continuous query execution (start)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fMW000 op_name=continuous_querier_execute op_event=start
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.890789Z lvl=info msg=“Executing continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fMW000 op_name=continuous_querier_execute name=site_hourly db_instance=powerwall start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.905462Z lvl=info msg=“Finished continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fMW000 op_name=continuous_querier_execute name=site_hourly db_instance=powerwall written=1 start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z duration=14ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.907726Z lvl=info msg=“Continuous query execution (end)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fMW000 op_name=continuous_querier_execute op_event=end op_elapsed=17.210ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.910954Z lvl=info msg=“Continuous query execution (start)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fRW000 op_name=continuous_querier_execute op_event=start
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.913124Z lvl=info msg=“Executing continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fRW000 op_name=continuous_querier_execute name=battery_hourly db_instance=powerwall start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.929966Z lvl=info msg=“Finished continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fRW000 op_name=continuous_querier_execute name=battery_hourly db_instance=powerwall written=1 start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z duration=19ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.932393Z lvl=info msg=“Continuous query execution (end)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fRW000 op_name=continuous_querier_execute op_event=end op_elapsed=21.462ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.935544Z lvl=info msg=“Continuous query execution (start)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fXl000 op_name=continuous_querier_execute op_event=start
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.937884Z lvl=info msg=“Executing continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fXl000 op_name=continuous_querier_execute name=load_hourly db_instance=powerwall start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.956453Z lvl=info msg=“Finished continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fXl000 op_name=continuous_querier_execute name=load_hourly db_instance=powerwall written=1 start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z duration=20ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.956669Z lvl=info msg=“Continuous query execution (end)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fXl000 op_name=continuous_querier_execute op_event=end op_elapsed=21.158ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.957466Z lvl=info msg=“Continuous query execution (start)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fcG000 op_name=continuous_querier_execute op_event=start
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.957531Z lvl=info msg=“Executing continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fcG000 op_name=continuous_querier_execute name=solar_hourly db_instance=powerwall start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.972456Z lvl=info msg=“Finished continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fcG000 op_name=continuous_querier_execute name=solar_hourly db_instance=powerwall written=1 start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z duration=14ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.972631Z lvl=info msg=“Continuous query execution (end)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fcG000 op_name=continuous_querier_execute op_event=end op_elapsed=15.185ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.976364Z lvl=info msg=“Continuous query execution (start)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fh0000 op_name=continuous_querier_execute op_event=start
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.976482Z lvl=info msg=“Executing continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fh0000 op_name=continuous_querier_execute name=battery_level_hourly db_instance=powerwall start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.992289Z lvl=info msg=“Finished continuous query” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fh0000 op_name=continuous_querier_execute name=battery_level_hourly db_instance=powerwall written=1 start=2018-03-16T16:00:00.000000Z end=2018-03-16T17:00:00.000000Z duration=15ms
Mar 16 09:45:00 firefly influxd[4898]: ts=2018-03-16T16:45:00.992475Z lvl=info msg=“Continuous query execution (end)” log_id=06p8~bdG000 service=continuous_querier trace_id=06sw2fh0000 op_name=continuous_querier_execute op_event=end op_elapsed=16.137ms
