Hands-on session: Time Series Database (TSBD) 

################################################################################
# InfluxDB
################################################################################

1. Let's retrieve the Docker image of InfluxDB
	docker pull influxdb:2.0

2. We can now create an instance of InfluxDB; since we are interesting to use it 
    from our local machine, we need to forward the Chronograf port (8086);

	docker run -p 8086:8086 \
      -v $PWD:/var/lib/influxdb2 \
      influxdb:2.0

3. We are ready to use InfluxDB. Let's open the Web UI of Chronograf, and complete 
    the configuration wizard 

    https://localhost:8086 

    For example, we use the following parameters: 

        user: root
        password: adminadmin
        organization: org
        bucket: bucket

4. Using the Flux console, we can load some sample data to play around: 
    import "influxdata/influxdb/sample"
    sample.data(set: "airSensor")
    |> to(
        org: "org",
        bucket: "bucket"
    )

    ----
    // To continually download and write updated air sensor sample data to a bucket, we can write the following command: 
    
        import "influxdata/influxdb/sample"

        option task = {
            name: "Collect air sensor sample data",
            every: 15m,
        }

        sample.data(set: "airSensor")
            |> to(org: "org", bucket: "bucket")

    Explore other sample data here: 
    https://docs.influxdata.com/influxdb/cloud/reference/sample-data/    
    ---- 

    Explore the data we imported in the bucket "bucket" of our organization "org". 
    Air sensor sample data represents an "Internet of Things" use case by simulating temperature, humidity, and carbon monoxide levels for multiple rooms in a building.

    We have multiple sensors (having ID starting with TLM0*), each collecting CO, humidity, and temperature. 

    Each event has the following structure:
    _time,                    _value,       _field,     _measurement,sensor_id
    2025-04-11 02:59:01 GMT+2,71.1606171227,temperature,airSensors,TLM0100 
    

5. A query example - Get raw temperature data for the 'airSensors' measurement (table)

    from(bucket: "bucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "airSensors")
        |> filter(fn: (r) => r["_field"] == "temperature")
        |> yield(name: "raw values")

    // v.timeRangeStart and v.timeRangeStop get the time from the webinterface. Otherwise, we can specify the UTC time as follows: 

    from(bucket: "bucket")
        |> range(start: 2025-04-11T00:59:01Z, stop: 2025-04-11T01:59:01Z)
        |> filter(fn: (r) => r["_measurement"] == "airSensors")
        |> filter(fn: (r) => r["_field"] == "temperature")
        |> yield(name: "raw values")
        
6. Get data of a specific sensor ("TLM0100")

    from(bucket: "bucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "airSensors")
        |> filter(fn: (r) => r["_field"] == "temperature")
        |> filter(fn: (r) => r["sensor_id"] == "TLM0100")
        |> yield(name: "raw values")

6. Get the average temperature (in a specific range all measurements) registered by a specific sensor ("TLM0100")

    from(bucket: "bucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "airSensors")
        |> filter(fn: (r) => r["_field"] == "temperature")
        |> filter(fn: (r) => r["sensor_id"] == "TLM0100")
        |> mean()

7. Get the average temperature registered by all sensors

    from(bucket: "bucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "airSensors")
        |> filter(fn: (r) => r["_field"] == "temperature")
        |> group(columns: ["sensor_id"])
        |> mean()

    Note that it creates a single table for each sensor 

8. We can easily compute the derivative on our data. For example, consider the temperature measurement of a specific sensor. Then, compute the derivative; to avoid too much noise, we first aggregate data in windows of 10 minutes; we also compute the derivative every 10 minutes of (event time) data.

    from(bucket: "bucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "airSensors")
        |> filter(fn: (r) => r["_field"] == "temperature")
        |> filter(fn: (r) => r["sensor_id"] == "TLM0100")
        |> yield(name: "raw data")
    

    from(bucket: "bucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "airSensors")
        |> filter(fn: (r) => r["_field"] == "temperature")
        |> filter(fn: (r) => r["sensor_id"] == "TLM0100")
        |> aggregateWindow(every: 10m, fn: mean, createEmpty: false)
        |> derivative(unit: 10m,nonNegative: false)
        |> yield(name: "derivative")