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

# Query a Lightning Table with JDBC in Confluent Cloud

The Confluent Java Database Connectivity (JDBC) driver connects your
tools to Lightning Tables.

You can connect SQL clients, business
intelligence (BI) tools, and Java applications. You can browse
Lightning Tables and run Lightning Table queries from tools such as
DBeaver, DataGrip, and the `sqlline` shell.

The same driver also connects to Confluent Cloud for Apache Flink®. This page covers only what’s
specific to Lightning Tables. To get the driver and learn how to
configure it in a client, see [Connect to Confluent Cloud for Apache Flink with JDBC](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc).

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

## Prerequisites

Before you begin, make sure you have the following:

- The Confluent JDBC driver JAR file, added to your client. To get the
  driver, see [Step 1: Get the driver](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc-get-driver).
- Java 17 or later.
- A topic with a Lightning Table enabled. To enable a Lightning Table,
  see [Enable a Lightning Table for a topic](../manage.md#lightning-tables-manage-enable).
- A Global API key. To create one, see
  [Create a Global API key](../access-control.md#lightning-tables-create-global-api-key).
- Permission to query data. To browse Lightning Tables, such as in a
  schema browser, you also need permission to list topics and get
  metadata. For the roles that grant these permissions, see
  [Roles and permissions](../access-control.md#rtce-access-control-roles).

<a id="lightning-tables-query-jdbc-connect"></a>

## Connect to a Lightning Table

To connect to Lightning Tables, use a JDBC URL that starts with
`jdbc:confluent:lightning://`. The `lightning` segment tells the
driver to query Lightning Tables instead of Flink.

A Lightning Table connection needs the following settings:

- The API endpoint host,
  `sql.<region>.<cloud>.confluent.cloud`, such as
  `sql.us-west-2.aws.confluent.cloud`.
- Your Confluent Cloud environment ID, such as `env-abc123`.
- Your Apache Kafka® cluster ID, such as `lkc-abc123`.
- Your Global API key and secret.

Use IDs, not names, for the environment and Kafka cluster.

In a JDBC client’s schema browser, your Lightning Tables appear in the
following hierarchy:

| JDBC concept   | Confluent Cloud entity            |
|----------------|-----------------------------------|
| Catalog        | Environment ID                    |
| Schema         | Kafka cluster ID                  |
| Table          | Lightning Table for a Kafka topic |

You can give the driver its settings in a properties file or as query
parameters in the JDBC URL. Use a properties file when you can. Clients
often log connection URLs, so a URL can leak your API secret.

### Use a properties file

To use a properties file, complete the following steps:

1. Create a file named `lightning-jdbc.properties`, like the following
   example:
   ```properties
   client.lightning-endpoint=sql.<region>.<cloud>.confluent.cloud
   client.environment-id=<env_id>
   client.lkc-id=<lkc_id>
   client.global-api-key=<global_api_key>
   client.global-api-secret=<global_api_secret>
   ```
2. Because the file contains your API secret, restrict access to it:
   ```bash
   chmod 600 lightning-jdbc.properties
   ```
3. Reference the file in the JDBC URL with the `propertiesFile`
   parameter:
   ```text
   jdbc:confluent:lightning://?propertiesFile=/path/to/lightning-jdbc.properties
   ```

The driver ignores Flink-only properties in a properties file. As a
result, one file can hold the settings for both Flink and Lightning
Tables. For the order in which the driver applies settings from
different sources, see “Configure the connection” in Connect to
Confluent Cloud for Apache Flink with JDBC.

### Use URL query parameters

If your client can’t read a local file, put the host and IDs in the
JDBC URL:

```text
jdbc:confluent:lightning://sql.<region>.<cloud>.confluent.cloud?environment-id=<env_id>&lkc-id=<lkc_id>
```

Enter your Global API key as the username and your Global API secret as
the password. Most clients show these as separate fields in their
connection dialog. Don’t put credentials in the URL.

### Lightning Table connection properties

The following properties configure a Lightning Table connection:

| Property                                            | Description                                                                                                                                                                |
|-----------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| `user`, `password`                                  | Global API key and secret. These take precedence over<br/>`client.global-api-key` and `client.global-api-secret`. The<br/>driver doesn’t read them from a properties file. |
| `client.lightning-endpoint`                         | API endpoint host.                                                                                                                                                         |
| `client.environment-id`                             | Confluent Cloud environment ID. In the URL, use `environment-id`.                                                                                                          |
| `client.lkc-id`                                     | Kafka cluster ID. In the URL, use `lkc-id`.                                                                                                                                |
| `client.global-api-key`, `client.global-api-secret` | Global API key and secret.                                                                                                                                                 |

The properties for request and connection timeouts, proxies, and TLS also
apply to Lightning Table connections. The polling and retry properties,
`client.poll-interval-millis` and `client.retry-budget-millis`, apply
only to Flink connections. For descriptions and defaults, see
[Connection property reference](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc-properties).

If you pass a Flink-only property, such as `compute-pool-id`, in the
URL or as a client-supplied property, the connection fails.

### Connect over private networking

To connect over a private network, set `client.lightning-endpoint` to
the private API endpoint for your networking option. For the endpoint
formats, see [Private Networking for Lightning Tables in Confluent Cloud](../private-networking.md#lightning-tables-private-networking).

Enter the hostname only. The driver adds `https://` and the API path.
It doesn’t accept `http://` endpoints.

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

## Run a Lightning Table query with JDBC

The `sqlline` shell is a command-line JDBC client. It’s a quick way to
verify your connection before you configure a BI tool. To download
`sqlline`, see [Step 4: Run a query with sqlline](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc-sqlline).

To run a query with `sqlline`, complete the following steps:

1. Start `sqlline` with the `sqlline` JAR file and the driver JAR
   file on the classpath:
   ```bash
   CP=/path/to/<sqlline_jar_file>
   CP=$CP:/path/to/<driver_jar_file>
   java -cp "$CP" sqlline.SqlLine \
     -d io.confluent.jdbc.avatica.ConfluentJdbcDriver \
     -u "jdbc:confluent:lightning://?propertiesFile=$HOME/lightning-jdbc.properties"
   ```

   Replace `<sqlline_jar_file>` and `<driver_jar_file>` with the
   names of the JAR files that you downloaded. For the file names, see
   [Step 4: Run a query with sqlline](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc-sqlline).

   On Windows, separate classpath entries with `;` instead of `:`.
2. List the Lightning Tables that you can query:
   ```text
   !tables
   ```
3. Query a Lightning Table:
   ```sql
   SELECT ORDER_ID, STATUS, TOTAL
   FROM orders_topic
   WHERE STATUS = 'completed'
   LIMIT 5;
   ```

   The query returns up to five completed orders from the
   `orders_topic` Lightning Table.

   If a topic name contains a non-alphanumeric character, such as a
   hyphen, enclose it in backticks, such as
   `SELECT * FROM `stock-trades` LIMIT 10;`. Quoted names are case
   sensitive.

To connect from a BI tool, follow [Connect from a BI tool or SQL client](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc-bi-tools).
To connect from a Java application, follow
[Connect from a Java application](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc-java). In either case, use your JDBC URL for
Lightning Tables and Global API key instead of the Flink settings.

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

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

## How Lightning Table queries run with JDBC

Lightning Table queries that you run with JDBC behave as follows:

Read-only queries
: A Lightning Table connection runs read-only queries, such as
  `SELECT` statements. You can’t run `INSERT INTO` statements or
  data definition language (DDL) statements, such as `CREATE TABLE`.

Large results
: The driver returns most results right away. A query that returns a
  large result or takes a long time runs asynchronously. The driver
  waits for the query to finish, and then it downloads the result from
  cloud storage and returns it.

Result size
: If a result exceeds the result size limit, the query fails instead of
  returning a partial result. To reduce the result size, add a
  `LIMIT` clause or a narrower filter to your query.

Error reporting
: When a query fails, the driver raises a `SQLException` with a
  standard SQLState code and the error message. When available, the
  exception includes a request ID. Give the request ID to Confluent
  Support to help trace the query.

Some limitations apply to every driver connection. Connections are
autocommit only, and they don’t support batch execution. Result sets
are forward-only and read-only. For more information, see
[Limitations](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc-limitations).

## Related content

- [Query a Lightning Table in Confluent Cloud](overview.md#lightning-tables-query-overview)
- [Connect to Confluent Cloud for Apache Flink with JDBC](../../flink/operate-and-deploy/connect-with-jdbc.md#flink-connect-jdbc)
- [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)

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