Two Methods for Connecting to InfluxDB 3.0

By Jessica Wachtel / Product, Developer
May 26, 2023

Navigate to:

InfluxDB 3.0 has 10x better storage compression and performance, supports unlimited cardinality data, and delivers lightning-fast SQL queries compared to previous versions. These gains are the result of our new database engine built on top of Apache Arrow. Apache Arrow processes huge amounts of columnar data and provides a wide set of tools to operate effectively on that data. Arrow has a rich ecosystem, including libraries for Pandas and Neural Prophet, visualization with Grafana and Apache Superset, and it supports a wide range of programming languages.

Flight SQL provides a high-performance SQL interface for working with databases using the Arrow Flight RPC framework. It allows for faster data access and lower latencies without converting the data to Arrow format. This means that engaging the Flight SQL client is a necessary step before data is available for queries or analytics. To provide ease of access between Flight SQL and clients, the open source community created the FlightSQL driver. The driver is a lightweight wrapper around the Flight SQL client written in Go.

Before you can query InfluxDB, you must configure the FlightSQL driver using a data source name (DSN). There are many ways to build the DSN, and the remainder of this blog post focuses on two ways of connecting with Flight SQL – using the usql CLI and programmatically. To use InfluxDB with the FlightSQL driver, sign up for your free cloud account now.

Interact with InfluxDB using the sql CLI

The usql CLI is a universal, interactive SQL client that allows you to issue SQL queries against InfluxDB and other SQL databases. DSN configuration is mandatory before any queries can happen.

If you don’t have the CLI installed, you can download the binary from the usql releases page. You need version v0.14.3 or newer for Flight SQL support. Adding the DSN link to the CLI creates the connection to Flight SQL.

The format of the DSN connection link looks like this when using InfluxDB:

flightsql://"address:port"?token="your-token"[&param1=value1&...&paramN=valueN]

port is not optional and defaults to 443. For more details on how to use usql to execute queries and perform other functionality, check out their GitHub project page.

Query InfluxDB programmatically

Building the DSN programmatically requires additional functionality prior to starting. Please have the following installed on your computer:

  • Go 1.17+

  • FlightSQL Library Installation go get -u github.com/apache/arrow/go/v12/arrow/flight/flightsql

The code below is an example of creating a Flight SQL connection programmatically:

package main

import (
        	"database/sql"
        	"log"
        	"time"

        	"github.com/apache/arrow/go/v12/arrow/flight/flightsql"
)

func main() {
        	config := flightsql.DriverConfig{
        	Address: "localhost:12345",
        	Tls:  “enabled”,
        	Token:   "your token",
        	Timeout: 10 * time.Second,
        	Params: map[string]string{
       	"my-custom-parameter": "foobar",
        	},
        	}
        	db, err := sql.Open("flightsql", config.DSN())
        	if err != nil {
                    	log.Fatalf("open failed: %v", err)
        	}
        	defer db.Close()

        	...
}

This example uses the DriverConfig struct to build a DSN for the user. Users can customize the struct values to fit the requirements of their connection. This is passed to the SQL connection, and once connected, the user can use the database connection to complete their required tasks.

DSN configuration

Some additional configuration items include the following common case-sensitive parameters:

token

InfluxDB instances require access tokens of varying security levels. Our docs provide detailed information on how to create tokens.

timeout

The FlightSQL driver defaults to an infinite timeout. Set the timeout parameter with a duration string (e.g. <code class=”language-sql”code class=”language-sql”>timeout=5s</code>) as a safety precaution. This keeps the application from getting stuck in an infinite timeout if a query takes too long or the backend is down.

tls

The tls parameter enables and customizes Transport Layer Security settings. Special values for the parameters include:

  • disabled or false – This disables TLS for the server connection. All other settings are ignored in this case.

  • enabled or true – This forces TLS for the server connection. All system settings for trusted CAs are used in this case.

  • skip-verify – This is a security risk. Do not use it! This enables TLS for the server connection but doesn’t verify the server certificate.

Real-world examples

Connecting to InfluxDB locally requires different parameters than connecting to InfluxDB in the cloud. Cloud connection requires bucket-name specification while local connection requires the namespace-name.

Cloud connection

'flightsql://us-east-1-1.aws.cloud2.influxdata.com:443?timeout=10s&bucket-name=comany-sensors&token=xxxxxxxxxxxxxxxxxxx====&tls=enabled'

Local connection

'flightsql://127.0.0.1:8082?timeout=10s&iox-namespace-name=company_sensors'

Conclusion

The FlightSQL driver GitHub page shares more details about connecting but they are more general as this connection works with any database written in Go. The instructions aren’t InfluxDB specific.

You don’t need to write your whole project file in Go to connect to the FlightSQL Driver. To see the driver in action, check out our community project, Plant Buddy. The source code details how to connect to the FlightSQL driver in Python, includes a SQL query, and converts the data to Pandas DataFrame.

Check out the other exciting features and optimizations that come with InfluxDB 3.0. To get started, sign up for your free InfluxDB Cloud account.