Using SQL with InfluxDB
By Jason Myers / Oct 27, 2022 / InfluxDB, Community
One of the significant features announced with InfluxDB IOx is native SQL support. Even if SQL isn’t the lingua franca of the computing world, there are no doubt those that could make a case for it. There seems to be some dialect of SQL in virtually every corner of the internet.
Query languages in InfluxDB
Originally designed to work with relational databases, SQL wasn’t ideal for the time series workloads that InfluxDB handles. That’s one of the reasons why we created InfluxQL, our first query language. InfluxQL is an SQL-like query language for interacting with InfluxDB and providing features specific to storing and analyzing time series data. While InfluxQL is SQL-like, it’s not SQL. It shares some of the syntax of SQL, but, at the end of the day, isn’t SQL.
The next evolution of InfluxDB query languages came in the form of Flux. Flux is more than just a query language; it’s a full-fledged scripting language and can do all kinds of complex transformations and analyses of your time series data. Flux is extremely powerful, but with that power comes a bit of a learning curve. (Fortunately, there are plenty of resources to help accelerate learning Flux, including courses through InfluxDB University. Check out Beginner Flux and Intermediate Flux.)
Despite all of Flux’s power, SQL remains the elephant in the room. Until now.
SQL capabilities in InfluxDB
Starting with the IOx-powered version of InfluxDB, the platform supports native SQL queries. Specifically, InfluxDB supports the PostgresQL wire protocol dialect of SQL. At present, you can run SQL queries directly in the Cloud UI on IOx-enabled buckets by wrapping those queries in Flux. However, a native SQL script editor for the Cloud UI is coming soon so you can write and run SQL queries in InfluxDB.
There are several notable benefits with this update. One is the democratization of application development with InfluxDB. Now, not everyone needs to be a Flux expert to be able to query data in InfluxDB. SQL support significantly expands the number of people who can actively leverage time series data. For instance, a group may have one or two Flux experts and the rest of the team can use those scripts or create their own queries with SQL. In short, SQL support makes it easier for more people to use InfluxDB effectively.
Along similar lines, the PostgresQL wire protocol also means that users can integrate their InfluxDB instances with a wide range of third-party tools and solutions, like psql, Grafana, and others. This extends the capabilities of InfluxDB for SQL users and further promotes innovation, interoperability, and efficiency.
Limitations of SQL in InfluxDB
The most notable limitation of SQL in InfluxDB is that SQL support is query only. You can’t write data to InfluxDB using SQL. If you want to perform any sort of transformations on your time series data, or act on your data based on analysis of your data stream, you’ll still need to use Flux.
SQL support is just another example of InfluxData’s commitment to meeting developers where they are. By offering multiple query languages, the InfluxDB platform allows you to build applications the way you want. The addition of SQL opens InfluxDB and time series data to a whole new population that may not be familiar with time series workloads. This is an exciting evolution as we see the possibilities that emerge from combining the power of InfluxDB with the scale and reach of SQL.