Data Package for the Sensors Example

This data package is used by the Sensors (Getting Started) example which demonstrates DataSQRL's capabilities by building a sensor metrics data pipeline. It serves as an ideal use case for real-time data aggregation, showcasing how sensor readings can be processed to monitor environmental changes such as temperature and humidity. In this example, we will compute the maximum temperature from sensors in just a few SQL queries. All of this is achieved in less than 15 lines of code, including imports!

Data Overview

The data used in this example represents sensor readings and is provided in CSV format. Each record contains a sensorid, a timestamp (time), and corresponding temperature and humidity readings. Here is a sample of the input data:

How to use the data package with an example

We are going to build a data pipeline that aggregates sensor metrics using DataSQRL. With DataSQRL, you can implement the entire data pipeline in a single SQL script.

1. Create a new folder for the data pipeline:

mkdir metrics; cd metrics

2. Create a new file called metrics.sqrl and copy-paste the following SQL code:

IMPORT datasqrl.examples.sensors.SensorReading; -- Import sensor readings
IMPORT time.endOfSecond;  -- Import time function

/* Aggregate sensor readings per second */
SecReading := SELECT sensorid, endOfSecond(time) as timeSec,
                     avg(temperature) as temp
              FROM SensorReading
              GROUP BY sensorid, timeSec;

/* Get max temperature in the last minute for each sensor */
SensorMaxTemp := SELECT sensorid, max(temp) as maxTemp
                 FROM SecReading
                 WHERE timeSec >= now() - INTERVAL 1 MINUTE
                 GROUP BY sensorid;

3. Compile the SQL script to an integrated data pipeline:

docker run -it --rm -v $PWD:/build datasqrl/cmd:v0.5.5 compile metrics.sqrl

4. By default, DataSQRL uses Docker to run data pipelines locally. Start the pipeline with Docker Compose:

(cd build/deploy; docker compose up --build)

5. Once you are done, hit CTRL-C and take down the pipeline containers with:

docker compose down -v 

Exploring the Pipeline

Once the pipeline is up and running, you can explore it by querying the data through a GraphQL API.

How to check the maximum temperature for a sensor?

Navigate to http://localhost:8888/graphiql/ and execute GraphQL queries to interact with the pipeline.

Query Example: Maximum Temperature for a Sensor

The following GraphQL query retrieves the maximum temperature recorded in the last minute for sensor id: 1:

{
  SensorMaxTemp(sensorid: 1) {
    maxTemp
  }
}

Expected Response

{
  "data": {
    "SensorMaxTemp": [
      {
        "maxTemp": 28.03122008
      }
    ]
  }
}

Query Example: Sensor Readings Aggregated by Time

To fetch the average temperature for sensor readings aggregated by second:

{
  SecReading(limit: 5) {
    sensorid,
    timeSec,
    temp
  }
}

Expected Response

The response will provide a list of sensor readings aggregated by time:

{
    "data": {
        "SecReading": [
            {
                "sensorid": 0,
                "timeSec": "2024-04-04T00:08:19.999Z",
                "temp": 21.10395002
            },
            {
                "sensorid": 1,
                "timeSec": "2024-04-04T00:08:19.999Z",
                "temp": 28.03122008
            },
            {
                "sensorid": 2,
                "timeSec": "2024-04-04T00:08:19.999Z",
                "temp": 3.18097443
            },
            {
                "sensorid": 3,
                "timeSec": "2024-04-04T00:08:19.999Z",
                "temp": 19.08475761
            },
            {
                "sensorid": 4,
                "timeSec": "2024-04-04T00:08:19.999Z",
                "temp": 18.77942311
            }
        ]
    }
}

Additional Resources

For further details on how DataSQRL works and additional tutorials, check out the official Sensor Data Tutorial.