Documentation

InfluxDB Cloud Dedicated

Get started querying data

  • 3 / 3

InfluxDB Cloud Dedicated supports multiple query languages:

  • SQL: Traditional SQL powered by the Apache Arrow DataFusion query engine. The supported SQL syntax is similar to PostgreSQL.
  • InfluxQL: An SQL-like query language designed to query time series data from InfluxDB.

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 database for data written in the Get started writing data section.

Tools to execute queries

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

* Covered in this tutorial

SQL query basics

InfluxDB Cloud’s SQL implementation is powered by the Apache Arrow DataFusion query engine which provides an 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 an 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 Dedicated database:

  • 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.

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'

influx3 CLI Python Go

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="DATABASE_NAME" \
  --host="cluster-id.influxdb.io" \
  --token="DATABASE_TOKEN" \
  --org="INFLUX_ORG_ID"

    Replace the following:

    • DATABASE_NAME: the name of the InfluxDB Cloud Dedicated bucket to query
    • DATABASE_TOKEN: Database token with read access to the get-started database.
    • 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 Dedicated 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 database. 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 Dedicated cluster URL (without protocol or trailing slash)
      • token: Database token with read access to the get-started database.
      • database: Database name 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 database READ token
TOKEN = os.getenv('INFLUX_TOKEN')

client = InfluxDBClient3(
    host="cluster-id.influxdb.io",
    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 Dedicated cluster URL (no protocol, include port 443)
      • token: Database token with read access to the get-started database. For security reasons, we recommend setting this as an environment variable rather than including the raw token string.
      • database: Database name to query

    3. In the dbQuery function, create a gRPC transport to use to communicate with your InfluxDB Cloud Dedicated cluster over the gRPC 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 := "cluster-id.influxdb.io:443"
  // INFLUX_TOKEN is an environment variable you created for your database READ 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 your InfluxDB Cloud Dedicated cluster.

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

View program output

Congratulations! You’ve learned the basics of querying data in InfluxDB Cloud Dedicated 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.

What is your InfluxDB Cloud Dedicated cluster URL?

Enter your InfluxDB Cloud Dedicated cluster URL and we’ll update code examples for you.

Enter cluster URL

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.