Get started querying data
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 tutorialSQL 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.
Example SQL queries
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
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.
Setup your Python virtual environment. Inside of your project directory:
python -m venv envs/virtual-env
Activate the virtual environment.
source ./envs/virtual-env/bin/activate
Install the following dependencies:
* Already installed in the Write data section
pyarrow
*flightsql-dbapi
*pyinflux3
*
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 queryDATABASE_TOKEN
: Database token with read access to the get-started database.INFLUX_ORG_ID
: InfluxDB organization ID
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.
Setup your Python virtual environment. Inside of your project directory:
python -m venv envs/virtual-env
Activate the virtual environment.
source ./envs/virtual-env/bin/activate
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
Build your python script to query your InfluxDB database. These can be structured as a Python script or executed in a
python
shell.Import the
InfluxDBClient3
constructor from theinfluxdb_client_3
module.Use the
InfluxDBClient3
constructor to instantiate an InfluxDB Client. The example below assigns it to theclient
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
Provide the SQL query to execute. In the example below, it’s assigned to the
query
variable.Use the
client.query
method to query data in the get-started database and return an Arrow table. Assign the return value to thetable
variable. Provide the following:- sql_query SQL query string to execute
Use
read_all
to read the data from InfluxDB and return an Arrow table.Use
to_pandas
to convert the Arrow table to a pandas DataFrame.Use
to_markdown
to convert the DataFrame to a markdown table.Use
print
to print out the markdown table.
In the
influxdb_go_client
directory you created in the Write data section, create a new file namedquery.go
.In
query.go
: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
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
- url: InfluxDB Cloud Dedicated cluster URL (no protocol, include port
In the
dbQuery
function, create a gRPC transport to use to communicate with your InfluxDB Cloud Dedicated cluster over the gRPC protocol.Use
flightsql.NewClient
to create a new Flight SQL client.Append the following key-value pairs to the outgoing context:
- authorization: Bearer <INFLUX_TOKEN>
- database-name: Database name
Define the query to execute.
Create a reader to read the Arrow table returned by the query and print the results as JSON.
Create a
main
function that executes thedbQuery
function.
Install all the necessary packages and run the program to query your InfluxDB Cloud Dedicated cluster.
go get ./...
go run ./query.go
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!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
InfluxDB Cloud and InfluxDB Enterprise customers can contact InfluxData Support.