Use Flux and SQL to query data
InfluxDB Cloud powered by InfluxDB IOx 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.
The query examples below use the Get started sample data.
- Performance and flexibility
- What to do in SQL versus Flux?
- Use SQL and Flux together
- Process SQL results with Flux
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
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
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
iox.sql()to execute a SQL query. Include the following parameters:
- bucket: InfluxDB bucket to query
- query: SQL query to execute
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.
Helper functions for SQL in Flux
experimental/iox package provides the following helper functions for
use with SQL queries in Flux:
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.
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.
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.
Group by tags
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:
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.
time column to
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 in your SQL query with an
AS clause (recommended for performance)
or in Flux with the
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
_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:
- Import the
- Ensure you have a
experimental.unpivot()to unpivot your data.
import "experimental" import "experimental/iox" iox.sql(...) |> group(columns: ["room"]) |> experimental.unpivot()
unpivot() treats columns not in the group key
_measurement) as fields. Be sure to group by tags
before unpivoting data.
Example SQL query with further Flux processing
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!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
InfluxDB Cloud and InfluxDB Enterprise customers can contact InfluxData Support.