How to run an SQL query in InfluxDB 3 Core

Running an SQL query in InfluxDB 3 Core confirms that stored time series rows can be read from the intended database and table. It is a quick check after writing line protocol, connecting a dashboard, or investigating why an expected series is missing.

The influxdb3 query command sends SQL to a running Core server. A bearer token, server URL, and database name identify the target, and SQL is the default query language for the command.

Use the CLI for terminal checks before moving the same SQL into an application, Grafana panel, or the HTTP /api/v3/query_sql endpoint. Choose columns in SELECT, name the table in FROM, and use WHERE for tag, field, or time filters before querying a large production table.

Related: How to write line protocol with the influxdb3 CLI
Related: How to list InfluxDB 3 Core table schema
Related: How to aggregate time series data with SQL in InfluxDB 3 Core

Steps to run an SQL query in InfluxDB 3 Core:

  1. Set the token for the query session. 
    $ export INFLUXDB3_AUTH_TOKEN='INFLUXDB_ADMIN_TOKEN'

    Use a token with read access to the target database. Keep raw tokens out of shared logs, screenshots, and shell history.

  2. Set the host URL when the server is not on the default local listener. 
    $ export INFLUXDB3_HOST_URL=http://localhost:8181

    Skip this when querying the default local listener at http://127.0.0.1:8181. Use the proxy or TLS URL that the client should actually test.

  3. Run a focused SQL query against the database. 
    $ influxdb3 query --database sensors "SELECT time, room, temperature FROM weather WHERE room = 'lab' ORDER BY time"
+---------------------+------+-------------+
| time                | room | temperature |
+---------------------+------+-------------+
| 2024-03-09T16:00:00 | lab  | 21.8        |
| 2024-03-09T17:00:00 | lab  | 22.1        |
+---------------------+------+-------------+

    The --database option selects the InfluxDB database. The FROM clause selects the table, and the WHERE clause limits returned rows.

  4. Return CSV when another tool needs machine-readable rows. 
    $ influxdb3 query --database sensors --format csv "SELECT time, room, temperature FROM weather ORDER BY time"
time,room,temperature
2024-03-09T16:00:00,lab,21.8
2024-03-09T16:30:00,office,20.4
2024-03-09T17:00:00,lab,22.1

    Supported output formats include pretty, json, jsonl, csv, and parquet. Use --output with parquet because Parquet output is written to a file.