Use Flux and SQL to query data

InfluxDB Cloud Serverless supports both Flux and SQL query languages. Flux is a full-featured data scripting language that provides a wide range of functionality and flexibility. SQL is a proven and performant relational query language.

This guide walks through leveraging the performance of SQL and the flexibility of Flux when querying your time series data.

• Performance and flexibility
• What to do in SQL versus Flux?
• Use SQL and Flux together
  • Helper functions for SQL in Flux
  • SQL results structure
• Process SQL results with Flux
  • Group by tags
  • Rename the time column to _time
  • Unpivot your data
  • Example SQL query with further Flux processing

Performance and flexibility

Flux was designed and optimized for the TSM data model, which is fundamentally different from IOx. Because of this, Flux is less performant when querying an IOx-powered bucket. However, as a full-featured scripting language, Flux gives you the flexibility to perform a wide range of data processing operations such as statistical analysis, alerting, HTTP API interactions, and other operations that aren’t supported in SQL. By using Flux and SQL together, you can benefit from both the performance of SQL and the flexibility of Flux.

What to do in SQL versus Flux?

We recommend doing as much of your query as possible in SQL for the most performant queries. Do any further processing in Flux.

For optimal performance, the following chain of Flux functions can and should be performed in SQL:

from(...)
    |> range(...)
    |> filter(...)
    |> aggregateWindow(...)

SELECT
  DATE_BIN(...) AS _time,
  avg(...) AS ...,
FROM measurement
WHERE
  time >= ...
  AND time < ...
GROUP BY _time
ORDER BY _time

Example Flux versus SQL queries

from(bucket: "get-started")
    |> range(start: 2022-01-01T08:00:00Z, stop: 2022-01-01T20:00:00Z)
    |> filter(fn: (r) => r._measurement == "home")

SELECT *
FROM home
WHERE time >= '2022-01-01T08:00:00Z' AND time < '2022-01-01T20:00:00Z'

from(bucket: "get-started")
    |> range(start: 2022-01-01T08:00:00Z, stop: 2022-01-01T20:00:00Z)
    |> filter(fn: (r) => r._measurement == "home")
    |> filter(fn: (r) => r._field == "temp" or r._field == "hum")
    |> aggregateWindow(every: 2h, fn: mean)

SELECT
  DATE_BIN(INTERVAL '2 hours', time, '1970-01-01T00:00:00Z'::TIMESTAMP) AS _time,
  room,
  avg(temp) AS temp,
  avg(hum) AS hum,
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T20:00:00Z'
GROUP BY room, _time
ORDER BY _time

Use SQL and Flux together

To use SQL and Flux together and benefit from the strengths of both query languages, build a Flux query that uses the iox.sql() function to execute a SQL query. The SQL query should return the base data set for your query. If this data needs further processing that can’t be done in SQL, those operations can be done with native Flux.

1. Import the experimental/iox package.

2. Use iox.sql() to execute a SQL query. Include the following parameters:

   • bucket: InfluxDB bucket to query
   • query: SQL query to execute

import "experimental/iox"

query = "
SELECT *
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T20:00:00Z'
"

iox.sql(bucket: "get-started", query: query)

import "experimental/iox"

query = "
SELECT *
FROM \"home\"
WHERE
  \"time\" >= '2022-01-01T08:00:00Z'
  AND \"time\" < '2022-01-01T20:00:00Z'
"

iox.sql(bucket: "get-started", query: query)

Helper functions for SQL in Flux

The Flux experimental/iox package provides the following helper functions for use with SQL queries in Flux:

• iox.sqlInterval()

iox.sqlInterval()

iox.sqlInterval() converts a Flux duration value to a SQL interval string. For example, 2d12h converts to 2 days 12 hours. This is especially useful when using a Flux duration to downsample data in SQL.

import "experimental/iox"

windowPeriod = 2h

query = "
SELECT
  DATE_BIN(INTERVAL '${iox.sqlInterval(d: windowPeriod)}', time, 0::TIMESTAMP) AS _time,
  room,
  avg(temp) AS temp,
  avg(hum) AS hum
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T20:00:00Z'
GROUP BY room, _time
ORDER BY room, _time
"

iox.sql(bucket: "get-started", query: query)

SQL results structure

iox.sql() returns a single table containing all the queried data. Each row has a column for each queried field, tag, and time. In the context of Flux, SQL results are ungrouped. This is important to understand if you further process SQL results with Flux.

The example query above returns:

Process SQL results with Flux

With your base data set returned from iox.sql(), you can further process your data with Flux to perform actions such as complex data transformations, alerting, HTTP requests, etc.

1. Group by tags
2. Rename the time column to _time
3. Unpivot your data

Group by tags

The Flux from() functions returns results grouped by measurement, tag, and field key and much of the Flux language is designed around this data model. Because SQL results are ungrouped, to structure results the way many Flux functions expect, use group() to group by all of your queried tag columns.

The Get started sample data only includes one tag: room.

import "experimental/iox"

iox.sql(...)
    |> group(columns: ["room"])

group() does not guarantee sort order, so you likely need to use sort() to re-sort your data time after performing other transformations.

Rename the time column to _time

Many Flux functions expect or require a column named _time (with a leading underscore). The IOx storage engine stores each point’s timestamp in the time column (no leading underscore). Depending on which Flux functions you use, you may need to rename the time column to _time.

Rename the time column in your SQL query with an AS clause (recommended for performance) or in Flux with the rename() function.

SELECT time AS _time
FROM "get-started"

// ...
    |> rename(columns: {time: "_time"})

Unpivot your data

In the context of Flux, data is considered “pivoted” when each field has its own column. Flux generally expects a _field column that contains the the field key and a _value column that contains the field. SQL returns each field as a column. Depending on your use case and the type of processing you need to do in Flux, you may need to “unpivot” your data.

To unpivot SQL results:

1. Import the experimental package.
2. Ensure you have a _time column.
3. Use experimental.unpivot() to unpivot your data.

import "experimental"
import "experimental/iox"

iox.sql(...)
    |> group(columns: ["room"])
    |> experimental.unpivot()

Example SQL query with further Flux processing

import "experimental"
import "experimental/iox"

query = "
SELECT
  time AS _time,
  room,
  temp,
  hum,
  co
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z' 
"

iox.sql(bucket: "get-started", query: query)
    |> group(columns: ["room"])
    |> experimental.unpivot()

With the SQL results restructured into the Flux data model, you can do any further processing with Flux. For more information about Flux, see the Flux documentation.