<a id="lightning-tables-query-cli"></a>

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

<a id="lightning-tables-query-cli-prerequisites"></a>

## 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](https://docs.confluent.io/confluent-cli/current/install.html).
- 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](../manage.md#lightning-tables-manage-enable).
- Permission to query data. For the roles that grant it, see
  [Roles and permissions](../access-control.md#rtce-access-control-roles).

<a id="lightning-tables-query-cli-run"></a>

## Run a Lightning Table query with the Confluent CLI

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

1. Sign in to Confluent Cloud with your user account:
   ```bash
   confluent login
   ```
2. Set the environment and the Kafka cluster that contain your Lightning
   Table:
   ```bash
   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.
3. List the Lightning Tables in the cluster by running the
   `SHOW TABLES` statement:
   ```bash
   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`.
4. Run a query against a Lightning Table, like the following example:
   ```bash
   confluent lightning query --sql "SELECT ORDERID, ITEMID FROM orders LIMIT 2"
   ```

   The command prints the result as a table, similar to the following
   output:
   ```text
   +---------+---------+
   | 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:

```bash
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](sql-reference.md#lightning-tables-sql-reference).

### 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:

1. Create a file named `orders.sql` with an SQL statement, like the
   following example:
   ```sql
   SELECT ORDERID, ITEMID, ORDERUNITS
   FROM orders
   WHERE ORDERUNITS > 5
   LIMIT 10
   ```
2. Run the SQL statement in the file:
   ```bash
   confluent lightning query --file orders.sql
   ```

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

<a id="lightning-tables-query-cli-output"></a>

## 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:
  <br/>
  ```bash
  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:
  <br/>
  ```bash
  confluent lightning query --sql "SELECT ORDERID, ITEMID FROM orders LIMIT 10" -o json
  ```
  <br/>
  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:
  <br/>
  ```bash
  confluent lightning query --sql "SELECT ORDERID FROM orders LIMIT 200" -o json | jq length
  ```
  <br/>
  The example prints `200`.

<a id="lightning-tables-query-cli-behavior"></a>

## 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](api.md#lightning-queries).

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](../private-networking.md#lightning-tables-private-networking).

Statement size
: The command accepts an SQL statement of up to about 1 MB.

## Related content

- [Query a Lightning Table in Confluent Cloud](overview.md#lightning-tables-query-overview)
- [Query a Lightning Table with the API in Confluent Cloud](api.md#lightning-queries)
- [SQL Reference for Lightning Table Queries in Confluent Cloud](sql-reference.md#lightning-tables-sql-reference)
- [Manage Access to Lightning Tables in Confluent Cloud](../access-control.md#rtce-access-control)
- `confluent lightning query` in the Confluent CLI Command Reference

#### NOTE
This website includes content developed at the [Apache Software Foundation](https://www.apache.org/)
under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).