Querying InfluxDB Cloud with the Go Flight SQL Client

By Anais Dotis-Georgiou / Product
Jun 07, 2023

InfluxDB Cloud 3.0 is a versatile time series database built on top of the Apache ecosystem. You can query InfluxDB Cloud with the Apache Arrow Flight SQL interface, which provides SQL support for working with time series data. In this tutorial, we will walk through the process of querying InfluxDB Cloud with Flight SQL, using Go. The Go Flight SQL Client is part of Apache Arrow Flight, a framework for building high-performance data services. It provides a way to efficiently transmit large datasets over gRPC, a modern high-performance RPC framework. Try out querying InfluxDB Cloud with the Go Flight SQL Client for yourself with this repo.

Requirements and setup

This tutorial assumes that you already have a free InfluxDB Cloud account and that you have Docker running on your machine.

Finally you’ll need to create or obtain the following InfluxDB resources:

• A bucket

• A token

• Your organization (usually the email you used to sign up for your account)

You’ll also need to write data to your InfluxDB account. The simplest way to do this is to write some line protocol manually through the UI. Navigate to Load Data > Buckets > +Add Data > Line Protocol > Enter Manually, select the bucket you want to write to, and write a point to InfluxDB. For example you could write measurementName,tagKey=tagValue fieldKey=1.0. Or, if you want real-world line protocol data, try the NOAA air sensor dataset. You can also check out the following documentation on writing data to InfluxDB Cloud 3.0 for other methods of writing data to InfluxDB Cloud.

Code walkthrough

Let’s break down the code into smaller pieces to understand what’s happening.

1. Import required classes: We start by importing the required classes from Apache Arrow Flight and other necessary libraries.

   package main
   
   import (
   	"context"
   	"crypto/x509"
   	"encoding/json"
   	"fmt"
   	"os"
   
   	"github.com/apache/arrow/go/v12/arrow/flight/flightsql"
   	"google.golang.org/grpc"
   	"google.golang.org/grpc/credentials"
   	"google.golang.org/grpc/metadata"
   	"os"
   )

2. Define the main function to call the dbQuery() function and handle errors.

   func main() {
   	fmt.Println("An example using Go Flight SQL Client to query InfluxDB")
   
   	if err := dbQuery(context.Background()); err != nil {
   		fmt.Fprintf(os.Stderr, "error: %v\n", err)
   		os.Exit(1)
   	}

3. Gather our authentication environment variables and set up the connection within the dbQuery() function after:

   func dbQuery(ctx context.Context) error {
   	url := os.Getenv("HOST")
   	token := os.Getenv("TOKEN")
   	bucket := os.Getenv("DATABASE_NAME")
   …
   // Create query client
   	client, err := flightsql.NewClient(url, nil, nil, opts...)
   	if err != nil {
   		return fmt.Errorf("flightsql: %s", err)
   	}
   
   	ctx = metadata.AppendToOutgoingContext(ctx, "authorization", "Bearer "+token)
   	ctx = metadata.AppendToOutgoingContext(ctx, "database", bucket)

4. Next, we define the query that we want to execute and execute it. Use the Execute method and pass in the auth and query to execute a query on the server.

   // Execute query
   	query := `SELECT * FROM 'measurementName'`
   
   	info, err := client.Execute(ctx, query)
   	if err != nil {
   		return fmt.Errorf("flightsql flight info: %s", err)
   	}
   	reader, err := client.DoGet(ctx, info.Endpoint[0].Ticket)
   	if err != nil {
   		return fmt.Errorf("flightsql do get: %s", err)
   	}

5. Finally we print the result as JSON.

   // Print results as JSON
   	for reader.Next() {
   		record := reader.Record()
   		b, err := json.MarshalIndent(record, "", "  ")
   		if err != nil {
   			return err
   		}
   		fmt.Println("RECORD BATCH")
   		fmt.Println(string(b))
   
   		if err := reader.Err(); err != nil {
   			return fmt.Errorf("flightsql reader: %s", err)
   		}
   	}

Query InfluxDB Cloud with Go Flight SQL

To run the example, first clone the corresponding repo, navigate to that directory, and follow the following steps as outlined in the README.md or:

1. In your terminal, set the following environment variables.

   # Set environment variables
   
   export INFLUX_DATABASE="your bucket" && \
   export INFLUX_HOST="your host url i.e. us-east-1-1.aws.cloud2.influxdata.com" && \
   export INFLUX_TOKEN="your token"

2. Run the following commands to utilize the shell script that builds an image with the name goflight:

   sh ./influxdb-build.sh build

3. To start the application, run docker run "IMAGE_NAME" in your terminal:

   docker run goflight

Resources and conclusion

Take a look at the following documentation. It helped me build this example, and it can help you on your journey with querying InfluxDB Cloud:

1. Reference documentation for Arrow Flight

2. InfluxDB Cloud documentation for Querying data with Arrow Flight SQL in Python

3. A blog post on InfluxDB, Flight SQL, Pandas, and Jupyter Notebooks Tutorial

4. A blog post on TL;DR InfluxDB Tech Tips: Downsampling with Flight SQL and AWS Lambda

I hope this blog post inspires you to explore InfluxDB Cloud and take advantage of Flight SQL to transport large datasets from InfluxDB for data processing with the tools of your choice. If you need any help, please reach out using our community site or Slack channel. I’d love to hear about what you’re trying to achieve and what features you’d like InfluxDB to have.