Hands-on session: Time Series Database (TSBD) 

################################################################################
# InfluxDB 3
################################################################################

    InfluxDB 3 represents a significant architectural shift compared to InfluxDB 2: rather than a specialized time-series engine with a custom query language (Flux), it adopts a columnar, analytics-oriented design based on Apache Arrow and Parquet, and reintroduces SQL as the primary query interface.
    
    This choice reflects a broader trend in data systems: leveraging a standard language enables interoperability, richer analytics (e.g., joins, window functions), and easier integration with existing data ecosystems.
    
    While InfluxDB 2 provides convenient built-in time-series operators (e.g., DERIVATIVE), InfluxDB 3 trades some of this domain-specific ergonomics for greater flexibility and expressive power, allowing users to implement custom transformations using standard SQL constructs.
    
    Compared to systems like Redis (including RedisTimeSeries), which prioritize low-latency ingestion and real-time aggregation, InfluxDB 3 is better suited for analytical workloads over large historical datasets, where complex queries and integration with data pipelines are required.

################################################################################

1. Let's retrieve the Docker image of InfluxDB
	docker pull influxdb:3-core

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 8181 port (8086);

    docker run -it -p 8181:8181 --name influxdb3-container \
        --volume ~/.influxdb3_data:/.data --volume ~/.influxdb3_plugins:/plugins \
        influxdb:3-core \
        influxdb3
3. To interact with influxdb, we can use different options, including an SDK or a webUI. 
    During this lecture, we will use the webUI provided by another Influx container. 
    To connect to influxdb we need to create an authentication token beforehand: 

    docker exec -it influxdb3-container influxdb3 create token --admin

4. We can start the influxdb3-ui container with: 

    docker run \
    --name influxdb3-explorer \
    --publish 8888:80 \
    --publish 8889:8888 \
    influxdata/influxdb3-ui \
    --mode=admin

5. We are ready to configure and use InfluxDB. 
    Let's open the brower at: http://localhost:8888/

    First, configure the server. Using docker we can use the following parameters: 

    Server name: influxdb3 (or just a fancy name to remember this configuration)
    Server url: http://172.17.0.1:8181
    Token: [paste here the auth token obtained in step 3]

    Second, we can get some sample data using the "Write Data" menu, e.g., we will 
    use the "air-sensor" data. 

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

6. A query example - Get raw temperature data for the 'sample-air-sensors' table

    Go to Query Data, select the database "sample-air-sensor", and write the query:

    SELECT * FROM air

    // We can specify a specific data range, for example:

    SELECT * FROM air 
    WHERE time >= '2026-04-08T07:14:53.558Z'
         AND time <= '2026-04-08T09:14:53.558Z'

7. Get data of a specific sensor ("TLM0204")

    SELECT * FROM air WHERE sensor_id = 'TLM0204'
  

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

    SELECT avg(temperature) FROM air
    WHERE 
        sensor_id = 'TLM0204'
        AND time >= now() - interval '1 hour'


7. Get the average temperature registered by all sensors; we need to group by sensor_id

    SELECT sensor_id, avg(temperature) FROM air
    WHERE time >= now() - interval '1 hour'
    GROUP BY sensor_id

    Note that GROUP BY work on tags and on time() only. 
    Moreover, if a query includes WHERE and GROUP BY, the GROUP BY clause must appear after the WHERE clause.

8. Example of average temperature over 5-minutes time windows

    We use "data_bin", which calculates time intervals and returns the start of the interval nearest to the specified timestamp. 
    Use date_bin to downsample time series data by grouping rows into time-based "bins" 
    and applying an aggregate/selector function to each window.

    SELECT
    date_bin(INTERVAL '5 minutes', time) AS window,
    AVG(temperature) AS avg_temp
    FROM air
    GROUP BY window
    ORDER BY window