Query data in InfluxDB 3 Enterprise

InfluxDB 3 Enterprise supports both native SQL and InfluxQL for querying data. InfluxQL is an SQL-like query language designed for InfluxDB v1 and customized for time series queries.

• Query data with the influxdb3 CLI
  • Example queries
• Other tools for executing queries
• SQL vs InfluxQL
  • SQL
  • InfluxQL
• Optimize queries
  • Last values cache
  • Distinct values cache
• File indexes

Query data with the influxdb3 CLI

To get started querying data in InfluxDB 3 Enterprise, use the influxdb3 query command and provide the following:

• -H, --host: The host URL of the server (default is http://127.0.0.1:8181)
• -d, --database: (Required) The name of the database to query
• -l, --language: The query language of the provided query string
  • sql (default)
  • influxql
• SQL or InfluxQL query as a string

To query the home sensor sample data you wrote in Write data to InfluxDB 3 Enterprise, run the following command:

influxdb3 query \
  --database 
DATABASE_NAME
 \
  "SELECT * FROM home ORDER BY time"

influxdb3 query \
  --database 
DATABASE_NAME
 \
  --language influxql \
  "SELECT * FROM home"

Replace DATABASE_NAME with the name of the database to query.

To query from a specific time range, use the WHERE clause to designate the boundaries of your time range.

influxdb3 query \
  --database 
DATABASE_NAME
 \
  "SELECT * FROM home WHERE time >= now() - INTERVAL '7 days' ORDER BY time"

influxdb3 query \
  --database 
DATABASE_NAME
 \
  --language influxql \
  "SELECT * FROM home WHERE time >= now() - 7d"

Example queries

SHOW TABLES

SHOW MEASUREMENTS

SELECT avg(temp) AS avg_temp FROM home

SELECT MEAN(temp) AS avg_temp FROM home

SELECT avg(temp) AS avg_temp FROM home WHERE room = 'Kitchen'

SELECT MEAN(temp) AS avg_temp FROM home WHERE room = 'Kitchen'

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

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

SELECT
  *
FROM
  home
WHERE
  time >= now() - INTERVAL '7 days'

SELECT
  *
FROM
  home
WHERE
  time >= now() - 7d

SELECT
  date_bin(INTERVAL '3 hours', time) AS time,
  room,
  avg(hum) AS avg_hum
FROM
  home
GROUP BY
  1,
  room
ORDER BY
  room,
  1

SELECT
  MEAN(hum) AS avg_hum
FROM
  home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T20:00:00Z'
GROUP BY
  time(3h),
  room

Other tools for executing queries

Other tools are available for querying data in InfluxDB 3 Enterprise, including the following:

curl -G "http://localhost:8181/api/v3/query_sql" \
  --header 'Authorization: Bearer 
AUTH_TOKEN
' \
  --data-urlencode "db=
DATABASE_NAME
" \
  --data-urlencode "q=select * from cpu limit 5"

curl http://localhost:8181/api/v3/query_sql \
  --data '{"db": "
DATABASE_NAME
", "q": "select * from cpu limit 5"}'

pip install influxdb3-python

from influxdb_client_3 import InfluxDBClient3

client = InfluxDBClient3(
    token='
AUTH_TOKEN
',
    host='http://localhost:8181',
    database='
DATABASE_NAME
'
)

from influxdb_client_3 import InfluxDBClient3
import os

client = InfluxDBClient3(
    token=os.environ.get('INFLUXDB3_AUTH_TOKEN'),
    host='http://localhost:8181',
    database='servers'
)

# Execute the query and return an Arrow table
table = client.query(
    query="SELECT * FROM cpu LIMIT 10",
    language="sql"
)

print("\n#### View Schema information\n")
print(table.schema)

print("\n#### Use PyArrow to read the specified columns\n")
print(table.column('usage_active'))
print(table.select(['host', 'usage_active']))
print(table.select(['time', 'host', 'usage_active']))

print("\n#### Use PyArrow compute functions to aggregate data\n")
print(table.group_by('host').aggregate([]))
print(table.group_by('cpu').aggregate([('time_system', 'mean')]))

SQL vs InfluxQL

InfluxDB 3 Enterprise supports two query languages–SQL and InfluxQL. While these two query languages are similar, there are important differences to consider.

SQL

The InfluxDB 3 SQL implementation provides a full-featured SQL query engine powered by Apache DataFusion. InfluxDB extends DataFusion with additional time series-specific functionality and supports the complex SQL queries, including queries that use joins, unions, window functions, and more.

• SQL query guides
• SQL reference
• Apache DataFusion SQL reference

InfluxQL

InfluxQL is a SQL-like query language built for InfluxDB v1 and supported in InfluxDB 3 Enterprise. Its syntax and functionality is similar SQL, but specifically designed for querying time series data. InfluxQL does not offer the full range of query functionality that SQL does.

If you are migrating from previous versions of InfluxDB, you can continue to use InfluxQL and the established InfluxQL-related APIs you have been using.

• InfluxQL query guides
• InfluxQL reference
• InfluxQL feature support

Optimize queries

InfluxDB 3 Enterprise provides the following optimization options to improve specific kinds of queries:

• Last values cache
• Distinct values cache
• File indexes

Last values cache

The InfluxDB 3 Enterprise last values cache (LVC) stores the last N values in a series or column hierarchy in memory. This gives the database the ability to answer these kinds of queries in under 10 milliseconds. For information about configuring and using the LVC, see:

• Manage a last values cache
• Query the last values cache

Distinct values cache

The InfluxDB 3 Enterprise distinct values cache (DVC) stores distinct values for specified columns in a series or column hierarchy in memory. This is useful for fast metadata lookups, which can return in under 30 milliseconds. For information about configuring and using the DVC, see:

• Manage a distinct values cache
• Query the distinct values cache

File indexes

InfluxDB 3 Enterprise lets you customize how your data is indexed to help optimize query performance for your specific workload, especially workloads that include single-series queries. Define custom indexing strategies for databases or specific tables. For more information, see Manage file indexes.