Comparing queries: InfluxDB 2.0 (Flux) vs CrateDB (SQL)

In this blog post, we are going to look at the differences between the query syntax of InfluxDB 2.0 (Flux) and CrateDB (SQL). This query comparison is done using a public time series dataset consisting of around 70K rows of weather station measurements. The data includes timestamped records with various meteorological parameters such as temperature, humidity, wind speed, wind direction, precipitation, pressure, and general weather conditions.

InfluxDB Terminology and Data Types

First, let’s review some InfluxDB terminology. InfluxDB stores time series data in buckets and measurements, while CrateDB stores data in schemas and tables.

• A bucket is a named location with a retention policy where time series data is stored.
• A series is a logical grouping of data defined by shared measurement, tag, and field.
• A measurement is like an SQL database table.
• A tag is like an indexed column in an SQL database.
• A field is like an un-indexed column in an SQL database.
• A point is like an SQL row.

Now, let's have a look at the supported data types both in InfluxDB and CrateDB. We can see that CrateDB covers all InfluxDB data types and offers larger capabilities around object and array types.

Query Examples

Now, let's compare Flux and SQL queries for typical time-series scenarios.

Aggregations:

Example: Computing the average temperature for each location.

This query type helps analyze temperature trends across different locations in environmental monitoring systems, smart cities, or agriculture.

Flux:

SQL:

Transformation:

Example: Converting temperature from Celsius to Fahrenheit.

This type of transformation query is proper when integrating data with systems or reports requiring different units or formats, ensuring application consistency and compatibility.

Flux:

SQL:

Windowing:

Example: Computing the average temperature in 1-hour windows

This query type is useful for analyzing temperature trends over time in manageable chunks, allowing for detailed temporal insights in monitoring and reporting applications.

Flux:

SQL:

Interpolation:

Example: Interpolating temperature values using linear interpolation.

This interpolation is useful for ensuring data continuity and accuracy in analyses. It's worth noting that the formatting and sorting here is a bit different between Flux and CrateDB. This is because Influx uses microsecond precision and CrateDB milliseconds, offering different levels of accuracy for different needs.

Flux:

SQL:

Downsampling:

Example: Computing the max temperature per week for each location in the last 12 months.

This query type is useful for long-term trend analysis and reporting, allowing you to focus on significant temperature peaks and reduce data volume while preserving critical information about extreme weather conditions.

Flux:

SQL:

Grouping multiple columns:

Example: Computing the average temperature and humidity for each location.

This query helps gain comprehensive insights into environmental conditions at different locations, which can be applied in climate studies, innovative city management, and agricultural monitoring.

Flux:

SQL:

Filtering and Conditional Logic:

Example: Getting all temperature readings above 30 degrees Celsius over the past 11 months.

This type of query is useful for identifying periods of high temperature, which can be crucial for heatwave analysis, monitoring climate change impacts, and managing energy consumption in response to extreme weather conditions.

Flux:

SQL:

Combining Queries:

Example: Computing both the average and maximum temperature for each location.

Flux:

SQL:

Moving Average:

Example: Listing the average of the last 5 values for each timestamp.

This type of query is helpful for smoothing temperature data to reveal longer-term trends, which can be valuable in time-series analysis for fields such as climate monitoring, finance, and performance metrics.

Flux:

SQL: