Documentation

InfluxDB Cloud Serverless

Get started querying data

  • 3 / 3

InfluxDB Cloud Serverless supports multiple query languages:

  • SQL: Traditional SQL powered by the Apache Arrow DataFusion query engine. The supported SQL syntax is similar to PostgreSQL.
  • Flux: A functional scripting language designed to query and process data from InfluxDB and other data sources.

This tutorial walks you through the fundamentals of querying data in InfluxDB and focuses on using SQL to query your time series data. The InfluxDB SQL implementation is built using Arrow Flight SQL, a protocol for interacting with SQL databases using the Arrow in-memory format and the Flight RPC framework. It leverages the performance of Apache Arrow with the simplicity of SQL.

The examples in this section of the tutorial query the get-started bucket for data written in the Get started writing data section.

Tools to execute queries

InfluxDB Cloud Serverless supports many different tools for querying data, including:

* Covered in this tutorial

SQL query basics

The InfluxDB SQL implementation is powered by the Apache Arrow DataFusion query engine which provides a SQL syntax similar to PostgreSQL.

This is a brief introduction to writing SQL queries for InfluxDB. For more in-depth details, see Query data with SQL.

InfluxDB SQL queries most commonly include the following clauses:

* Required
  • * SELECT: Identify specific fields and tags to query from a measurement or use the wild card alias (*) to select all fields and tags from a measurement.
  • * FROM: Identify the measurement to query. If coming from a SQL background, an InfluxDB measurement is the equivalent of a relational table.
  • WHERE: Only return data that meets defined conditions such as falling within a time range, containing specific tag values, etc.
  • GROUP BY: Group data into SQL partitions and apply an aggregate or selector function to each group.
-- Return the average temperature and humidity within time bounds from each room
SELECT
  avg(temp),
  avg(hum),
  room
FROM
  home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY
  room
Example SQL queries

Select all data in a measurement

Select all data in a measurement within time bounds

Select a specific field within relative time bounds

Select specific fields and tags from a measurement

Select data based on tag value

Select data based on tag value within time bounds

Downsample data by applying interval-based aggregates

Execute an SQL query

Get started with one of the following tools for querying data stored in an InfluxDB Cloud Serverless bucket:

  • InfluxDB UI: View your schema, build queries using the query editor, and generate data visualizations.
  • Flight SQL clients: Use language-specific (Python, Go, etc.) clients to execute queries in your terminal or custom code.
  • influx3 CLI: Send SQL queries from your terminal command-line.
  • Grafana: Query InfluxDB v3 with the FlightSQL Data Source plugin and connect and visualize data.
  • influx CLI, InfluxDB API, and InfluxDB API v2 client libraries: Use the InfluxDB /api/v2/query endpoint and Flux to execute SQL.

For this example, use the following query to select all the data written to the get-started bucket between 2022-01-01T08:00:00Z and 2022-01-01T20:00:00Z.

SELECT
  *
FROM
  home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'

InfluxDB UI influx3 CLI Python Go influx CLI InfluxDB API

  1. Go to cloud2.influxdata.com in a browser to log in and access the InfluxDB UI.

  2. In the side navigation menu, click Data Explorer.

      <div class="nav-items-v4">
    <div class="nav-item-v4">
      <div class="nav-icon-v4">
        <span class="v4 cf-icon GraphLine_New"></span>
      </div>
    </div>
    <div class="nav-item-v4">
      <div class="nav-icon-v4">
        <span class="v4 cf-icon GraphLine_New"></span>
      </div>
      <p class="nav-label-v4">Data Explorer</p>
    </div>
  </div>

  3. In the schema browser on the left, select the get-started bucket from the bucket drop-down menu. The displayed measurements and fields are read-only and are meant to show you the schema of data stored in the selected bucket.

  4. Enter the SQL query in the text editor.

  5. Click Run.

Results are displayed under the query editor.

See Query in the Data Explorer to learn more.

Query InfluxDB v3 using SQL and the influx3 CLI, part of the InfluxCommunity/pyinflux3 community repository. The following steps include setting up a Python virtual environment already covered in Get started writing data. If your project’s virtual environment is already running, skip to step 3.

  1. Setup your Python virtual environment. Inside of your project directory:

    python -m venv envs/virtual-env

  2. Activate the virtual environment.

    source ./envs/virtual-env/bin/activate

  3. Install the following dependencies:

    * Already installed in the Write data section

    • pyarrow *
    • flightsql-dbapi *
    • pyinflux3 *

  4. Create the config.json configuration.

    influx3 config \
  --name="my-config" \
  --database="BUCKET_NAME" \
  --host="cloud2.influxdata.com" \
  --token="INFLUX_API_READ_TOKEN" \
  --org="INFLUX_ORG_ID"

    Replace the following:

    • BUCKET_NAME: the name of the InfluxDB Cloud Serverless bucket to query
    • INFLUX_API_READ_TOKEN: InfluxDB API token with read access to the get-started bucket
    • INFLUX_ORG_ID: InfluxDB organization ID

  5. Enter the influx3 sql command and your SQL query statement.

influx3 sql "SELECT * FROM home WHERE time >= '2022-01-01T08:00:00Z' AND time <= '2022-01-01T20:00:00Z'"

influx3 displays query results in your terminal.

To query data from InfluxDB Cloud Serverless using Python, use the pyinflux3 module. The following steps include setting up a Python virtual environment already covered in Get started writing data. If your project’s virtual environment is already running, skip to step 3.

  1. Setup your Python virtual environment. Inside of your project directory:

    python -m venv envs/virtual-env

  2. Activate the virtual environment.

    source ./envs/virtual-env/bin/activate

  3. Install the following dependencies:

    * Already installed in the Write data section

    • pyarrow *
    • flightsql-dbapi *
    • pyinflux3 *
    • pandas
    • tabulate (to return formatted tables)
    pip install pandas tabulate

  4. Build your python script to query your InfluxDB bucket. These can be structured as a Python script or executed in a python shell.

    1. Import the InfluxDBClient3 constructor from the influxdb_client_3 module.

    2. Use the InfluxDBClient3 constructor to instantiate an InfluxDB Client. The example below assigns it to the client variable. Provide the following credentials:

      • host: InfluxDB Cloud Serverless region hostname (URL without protocol or trailing slash)
      • token: InfluxDB API token with read access to the specified bucket. For security reasons, we recommend setting this as an environment variable rather than including the raw token string.
      • database: the name of the InfluxDB Cloud Serverless bucket to query

    3. Provide the SQL query to execute. In the example below, it’s assigned to the queryvariable.

    4. Use the client.query method to query data in the get-started database and return an Arrow table. Assign the return value to the table variable. Provide the following:

      • sql_query SQL query string to execute

    5. Use read_all to read the data from InfluxDB and return an Arrow table.

    6. Use to_pandas to convert the Arrow table to a pandas DataFrame.

    7. Use to_markdown to convert the DataFrame to a markdown table.

    8. Use print to print out the markdown table.

from influxdb_client_3 import InfluxDBClient3
import os

# INFLUX_TOKEN is an environment variable you created for your API token
TOKEN = os.getenv('INFLUX_TOKEN')

client = InfluxDBClient3(
    host="cloud2.influxdata.com",
    token=TOKEN,
    database="get-started",
)

sql = '''
SELECT
  *
FROM
  home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
'''

table = client.query(sql_query=sql)
reader = table.read_all()
print(reader.to_pandas().to_markdown())

View returned markdown table

  1. In the influxdb_go_client directory you created in the Write data section, create a new file named query.go.

  2. In query.go:

    1. Import the following packages:

      • 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

    2. Create a dbQuery function. In `dbQuery, define the following variables:

      • url: InfluxDB Cloud Serverless region hostname and port (:443) (no protocol)
      • token: InfluxDB API token with read access to the specified bucket. For security reasons, we recommend setting this as an environment variable rather than including the raw token string.
      • database: the name of the InfluxDB Cloud Serverless bucket to query

    3. In the dbQuery function, create a gRPC transport for communicating with InfluxDB Cloud Serverless over the gRPC+TLS protocol.

    4. Use flightsql.NewClient to create a new Flight SQL client.

    5. Append the following key-value pairs to the outgoing context:

      • authorization: Bearer <INFLUX_TOKEN>
      • database-name: Database name

    6. Define the query to execute.

    7. Create a reader to read the Arrow table returned by the query and print the results as JSON.

    8. Create a main function that executes the dbQuery function.

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"
)

func dbQuery(ctx context.Context) error {
	url := "cloud2.influxdata.com:443"
  // INFLUX_TOKEN is an environment variable you created for your API token
	token := os.Getenv("INFLUX_TOKEN")
	database := "get-started"

	// Create a gRPC transport
	pool, err := x509.SystemCertPool()
	if err != nil {
		return fmt.Errorf("x509: %s", err)
	}
	transport := grpc.WithTransportCredentials(credentials.NewClientTLSFromCert(pool, ""))
	opts := []grpc.DialOption{
		transport,
	}

	// 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", database)

	// Execute query
	query := `SELECT
		*
	FROM
		home
	WHERE
		time >= '2022-01-01T08:00:00Z'
		AND time <= '2022-01-01T20:00:00Z'`

	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)
	}

	// 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)
		}
	}

	return nil
}

func main() {
	if err := dbQuery(context.Background()); err != nil {
		fmt.Fprintf(os.Stderr, "error: %v\n", err)
		os.Exit(1)
	}
}

Install all the necessary packages and run the program to query InfluxDB Cloud Serverless.

go get ./...
go run ./query.go

View program output

The influx query command uses the InfluxDB /api/v2/query endpoint to query InfluxDB. This endpoint only accepts Flux queries. To use SQL with the influx CLI, wrap your SQL query in Flux and use iox.sql() to query the InfluxDB IOx storage engine with SQL. Provide the following:

  • Bucket name with the bucket parameter
  • SQL query with the query parameter

View iox.sql() Flux example

  1. If you haven’t already, download, install, and configure the influx CLI.

  2. Use the influx query command to query InfluxDB using Flux.

    Provide the following:

influx query "
import \"experimental/iox\"

iox.sql(
    bucket: \"get-started\",
    query: \"
        SELECT
          *
        FROM
          home
        WHERE
          time >= '2022-01-01T08:00:00Z'
          AND time <= '2022-01-01T20:00:00Z'
    \",
)"

To query data from InfluxDB using SQL and the InfluxDB HTTP API, send a request to the InfluxDB API /api/v2/query endpoint using the POST request method.

POST http://localhost:8086/api/v2/query

The /api/v2/query endpoint only accepts Flux queries. To query data with SQL, wrap your SQL query in Flux and use iox.sql() to query the InfluxDB IOx storage engine with SQL. Provide the following:

  • Bucket name with the bucket parameter
  • SQL query with the query parameter

View iox.sql() Flux example

Include the following with your request:

  • Headers:
    • Authorization: Token <INFLUX_TOKEN>
    • Content-Type: application/vnd.flux
    • Accept: application/csv
    • (Optional) Accept-Encoding: gzip
  • Request body: Flux query as plain text. In the Flux query, use iox.sql() and provide your bucket name and your SQL query.

The following example uses cURL and the InfluxDB API to query data with Flux:

curl --request POST \
"$INFLUX_HOST/api/v2/query" \
  --header "Authorization: Token $INFLUX_TOKEN" \
  --header "Content-Type: application/vnd.flux" \
  --header "Accept: application/csv" \
  --data "
    import \"experimental/iox\"

    iox.sql(
        bucket: \"get-started\",
        query: \"
            SELECT
              *
            FROM
              home
            WHERE
              time >= '2022-01-01T08:00:00Z'
              AND time <= '2022-01-01T20:00:00Z'
        \",
    )"

The InfluxDB /api/v2/query endpoint returns query results in annotated CSV.

Query results

View query results

Congratulations! You’ve learned the basics of querying data in InfluxDB with SQL. For a deep dive into all the ways you can query InfluxDB, see the Query data in InfluxDB section of documentation.

Was this page helpful?

Thank you for your feedback!

© 2023 InfluxData, Inc.

Select your InfluxDB Cloud region

Select your InfluxDB Cloud region and cluster and we’ll customize code examples for you. Identify your InfluxDB Cloud cluster.

AWS

  • US West (Oregon)

GCP
Azure

For more information, see InfluxDB Cloud regions.

Thank you for your feedback!

Let us know what we can do better:

Thank you!

No thanks

Select a new date

Select a date in your bucket's retention period.

Update

Introducing InfluxDB 3.0

The new core of InfluxDB built with Rust and Apache Arrow. Available today in InfluxDB Cloud Dedicated.

Learn more

State of the InfluxDB Cloud Serverless documentation

The new documentation for InfluxDB Cloud Serverless is a work in progress. We are adding new information and content almost daily. Thank you for your patience!

If there is specific information you’re looking for, please submit a documentation issue.

InfluxDB Cloud Serverless powered by IOx

You are currently viewing documentation specific to InfluxDB Cloud Serverless powered by the IOx storage engine, which offers different functionality than InfluxDB Cloud powered by the TSM storage engine.

Are you using InfluxDB Cloud Serverless? How to find out?

Visit your organization's homepage and look for:

InfluxDB Cloud Serverless
Yes – I'm using InfluxDB Cloud Serverless No – I'm using InfluxDB Cloud (TSM)