Continuous Query Lab

• Login into your sandbox instance
• Verify influxdb service is running
• Download sample data:

  curl -O https://s3.us-east-2.amazonaws.com/tick-courseware/sample-data/NOAA_data.txt
  

• Launch influxdb cli
• Create database NOAA_water_database
• Exit the cli
• Import the data:

  influx -import -path=NOAA_data.txt -precision=s -database=NOAA_water_database
  

• Find the database we just created using InfluxQl
• Switch to the database with use <db-name> syntax
• List measurements in the database using InfluxQl
• Select data from different measurements to confirm import has been successful
• Now let's write some continuous queries
• Launch influx cli: influx -precision 'rfc3339'
• First take a look at the source data:

  select * from h2o_feet limit 10
  

• What are fields and what are tags?
• Let's write a group by query before we rush into a continuous query:

  SELECT max(water_level), min(water_level), max(water_level) - min(water_level) as "delta" FROM "h2o_feet" where time >= '2015-08-18' group by time(12h) limit 10
  

• Experiment with different functions, criteria, and time() settings
• Add a tag as an additional group by option
• Create a continuous query based on the group by statement of your choice (no line breaks) e.g.:

  CREATE CONTINUOUS QUERY "cq_h2o_feet_secondly" ON "NOAA_water_database"
  BEGIN
  SELECT max(water_level), min(water_level), max(water_level) - min(water_level) as "delta"
  into h2o_feet_secondly
  FROM "h2o_feet" 
  group by location, time(1s)
  END
  

• Using the show ... syntax confirm the query has been created
• How do you get to the data generated by continuous query?
• Did you get to see any data?
• Double check the common issues with continuous queries
• Let insert net new data:

  insert h2o_feet,location=coyote_creek,level\ description=between\ 6\ and\ 9\ feet water_level=8.12
  insert h2o_feet,location=santa_monica,level\ description=below\ 3\ feet water_level=2.064
  insert h2o_feet,location=coyote_creek,level\ description=between\ 6\ and\ 9\ feet water_level=8.005
  

• Do you have data in the continuous query output measurements
• What do you do for the historical data?
• Maybe we can execute the query to populate the new measurement:

  SELECT max(water_level), min(water_level), max(water_level) - min(water_level) as "delta" into h2o_feet_secondly FROM "h2o_feet" group by location, time(1s)
  

• What error did you get? How would you solve it?
• What is we were to add a dummy time where clause: where time > 0
• Let's see whether we have any data now:

  select * from h2o_feet_secondly limit 100
  

• Continuous queries are not instantaneous, see github issue
• Continuous query logging can be enabled in /etc/influxdb/influxdb.conf
• Continous queries have advanced resample option to improve dealing with a bit late data arrival