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.

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.
Java 17 or later.
A topic with a Lightning Table enabled. To enable a Lightning Table, see Enable a Lightning Table for a topic.
A Global API key. To create one, see Create a 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.

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:

Create a file named lightning-jdbc.properties, like the following example:

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>

Because the file contains your API secret, restrict access to it:
```
chmod 600 lightning-jdbc.properties
```

Reference the file in the JDBC URL with the propertiesFile parameter:

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:

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 `client.global-api-key` and `client.global-api-secret`. The 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.

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.

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

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.

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

Start sqlline with the sqlline JAR file and the driver JAR file on the classpath:
```
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.

On Windows, separate classpath entries with ; instead of :.
List the Lightning Tables that you can query:
```
!tables
```
Query a Lightning Table:
```
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. To connect from a Java application, follow Connect from a Java application. 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.

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.