Query a Lightning Table with the Confluent CLI in Confluent Cloud

The confluent lightning query command runs a read-only SQL statement against the Lightning Tables in an Apache Kafka® cluster. It prints the result as a table, JSON, or YAML. Use it to explore a Lightning Table from your terminal or to read its data in a script.

For every flag, see the confluent lightning query page in the Confluent CLI Command Reference.

Prerequisites

Before you begin, make sure you have the following:

Confluent CLI version 4.82.0 or later. To install or update it, see Install the Confluent CLI.
A Confluent Cloud user account. You can sign in with a username and password or with single sign-on (SSO). The command doesn’t support signing in with an API key.
A topic with a Lightning Table enabled, in a cluster on AWS. To enable a Lightning Table, see Enable a Lightning Table for a topic.
Permission to query data. For the roles that grant it, see Roles and permissions.

Run a Lightning Table query with the Confluent CLI

To run a Lightning Table query, complete the following steps.

Sign in to Confluent Cloud with your user account:
```
confluent login
```
Set the environment and the Kafka cluster that contain your Lightning Table:
```
confluent environment use <env_id>
confluent kafka cluster use <lkc_id>
```
Replace the <env_id> placeholder with your environment ID, such as env-abc123. Replace the <lkc_id> placeholder with your cluster ID, such as lkc-abc123.

You can also pass the --environment and --cluster flags with each confluent lightning query command instead.
List the Lightning Tables in the cluster by running the SHOW TABLES statement:
```
confluent lightning query --sql "SHOW TABLES"
```
The output lists each Lightning Table in the cluster and its phase. You can query a Lightning Table only when its phase is ACTIVE.

Run a query against a Lightning Table, like the following example:

confluent lightning query --sql "SELECT ORDERID, ITEMID FROM orders LIMIT 2"

The command prints the result as a table, similar to the following output:

+---------+---------+
| ORDERID | ITEMID  |
+---------+---------+
| 4       | Item_35 |
| 4       | Item_3  |
+---------+---------+

The result shows unquoted column names in uppercase.

If a topic name contains a non-alphanumeric character, such as a hyphen, enclose the name in backticks. Then enclose the SQL statement in single quotes. If you enclose the statement in double quotes instead, your shell runs the text between the backticks as a command. Use quotes like the following example:

confluent lightning query --sql 'SELECT * FROM `stock-trades` LIMIT 10'

For the SQL that a Lightning Table query supports, see SQL Reference for Lightning Table Queries in Confluent Cloud.

Run a query from a file

For a long statement, or one that’s awkward to quote in your shell, save it in a file. Then pass the file with the --file flag, or -f for short:

Create a file named orders.sql with an SQL statement, like the following example:

SELECT ORDERID, ITEMID, ORDERUNITS
FROM orders
WHERE ORDERUNITS > 5
LIMIT 10

Run the SQL statement in the file:

confluent lightning query --file orders.sql

You can pass either the --sql flag or the --file flag, but not both.

Choose an output format

By default, the command prints the result as a table. To get JSON or YAML instead, use the --output flag, or -o for short.

Print a table

With the default human output format, the command prints the first 100 rows and a notice that it stopped there. To print more rows, set the --max-rows flag to a higher number, or to 0 for no limit, like the following example:

confluent lightning query --sql "SELECT ORDERID FROM orders" --max-rows 500

Print JSON or YAML

With the json or yaml output format, the command prints every row in the result as an array of row objects. If no rows match, it prints an empty array. To cap the number of rows, set the --max-rows flag. The following example prints JSON:

confluent lightning query --sql "SELECT ORDERID, ITEMID FROM orders LIMIT 10" -o json

If the result has two columns with the same name, the JSON and YAML formats fail because each row object needs unique keys. Rename one of the columns with the AS keyword, as in SELECT KEY AS ORDER_KEY.

Use the output in a script

The command prints the result to standard output and prints notices and warnings to standard error. When the --max-rows flag cuts a result short, the command still exits with code 0. When a query fails, the command exits with code 1 and prints nothing to standard output. As a result, you can pipe JSON output to a command-line tool, such as the jq JSON processor. In the following example, the jq tool counts the rows in the result:

confluent lightning query --sql "SELECT ORDERID FROM orders LIMIT 200" -o json | jq length

The example prints 200.

How Lightning Table queries run with the Confluent CLI

Lightning Table queries that you run with the Confluent CLI behave as follows:

Read-only queries: Each command runs one read-only statement, such as a SELECT, SHOW TABLES, or DESCRIBE statement. To run several statements, run the command once for each statement.
Queries wait for the whole result: The command waits for the whole result and then prints it. If a result is too large or takes too long to return this way, the query fails. To reduce the result, add a LIMIT clause or a narrower WHERE clause. To get a large result, use the API instead. For more information, see Query a Lightning Table with the API in Confluent Cloud.
Time limit: The command waits up to 2 minutes for a result, including retries, and then fails. The server might stop a long-running query sooner.
Retries: If Confluent Cloud limits your request rate or is temporarily unavailable, the command retries the query. If a query fails because of a server error, the command might print a request ID. Give the request ID to Confluent Support to help trace the query.
Public endpoint: The command connects to the public endpoint for your cluster’s cloud and region. To query a Lightning Table over a private network, use the API, a Model Context Protocol (MCP) client, or Java Database Connectivity (JDBC). Use the private endpoint for your networking option. For more information, see Private Networking for Lightning Tables in Confluent Cloud.
Statement size: The command accepts an SQL statement of up to about 1 MB.