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.

Sample data

The query examples below use the Get started sample data.

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:


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


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

Example Flux versus SQL queries

View example basic queries

View example aggregate queries

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.

Supported by any InfluxDB 2.x client

The process below uses the /api/v2/query endpoint and can be used to execute SQL queries against an InfluxDB IOx-powered bucket with an HTTP API request or with all existing InfluxDB 2.x clients including, but not limited to, the following:

  • InfluxDB 2.x client libraries
  • Grafana and Grafana Cloud InfluxDB data source
  • Flux VS code extensions
  • InfluxDB OSS 2.x dashboards
  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 = "
FROM home
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T20:00:00Z'

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

Escape double quotes in your SQL query

If your SQL query uses double-quoted ("") identifiers, you must escape the double quotes in your SQL query string.

View example

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() 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.

View iox.sqlInterval() example

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:

035.9Living Room21.12022-01-01T08:00:00Z
035.9Living Room21.42022-01-01T09:00:00Z
036Living Room21.82022-01-01T10:00:00Z
036Living Room22.22022-01-01T11:00:00Z

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.

For the best performance, limit SQL results

All data returned by iox.sql() is loaded into memory and processed there. To maximize the overall performance of your Flux query, try to return as little data as possible from your SQL query. This can by done by downsampling data in your SQL query or by limiting the queried time range.

  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.

Measurements are not stored as a column in the InfluxDB IOx storage engine and are not returned by SQL.

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

import "experimental/iox"

    |> 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.

View examples of pivoted and unpivoted data

Unpivoting data may not be necessary

Depending on your use case, unpivoting the SQL results may not be necessary. For Flux queries that already pivot fields into columns, using SQL to return pivoted results will greatly improve the performance of your query.

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"

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

unpivot() treats columns not in the group key (other than _time and _measurement) as fields. Be sure to group by tags before unpivoting data.

Example SQL query with further Flux processing

import "experimental"
import "experimental/iox"

query = "
  time AS _time,
FROM home
  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()

View processed query results

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.

Was this page helpful?

Thank you for your feedback!

Introducing InfluxDB 3.0

The new core of InfluxDB built with Rust and Apache Arrow. Available today in InfluxDB Cloud Dedicated.

Learn more

State of the InfluxDB Cloud Serverless documentation

The new documentation for InfluxDB Cloud Serverless is a work in progress. We are adding new information and content almost daily. Thank you for your patience!

If there is specific information you’re looking for, please submit a documentation issue.

InfluxDB Cloud Serverless powered by IOx