# Query a Lightning Table with the API in Confluent Cloud A Lightning Table query reads data from the Lightning Table for an Apache Kafka® topic. Use the API to run Lightning Table queries from your applications, dashboards, and services. Most queries return their result in the response. A query that returns a large result or takes a long time runs asynchronously, and you retrieve its result later. For more information, see [Retrieve the result of a large query](#lightning-queries-large-results). The API queries the same Lightning Tables as the other query interfaces, such as the Model Context Protocol (MCP) tools that AI agents use. To compare a Lightning Table query with other query types, and to compare the query interfaces, see [Query a Lightning Table in Confluent Cloud](overview.md#lightning-tables-query-overview). ## Prerequisites Before you begin, make sure you have the following: - 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). - The DeveloperRead role, granted on both the Kafka topic and the Schema Registry cluster. For role requirements, see [Roles and permissions](../access-control.md#rtce-access-control-roles). ## API endpoint and authentication Send requests to the following endpoint: ```text https://sql...confluent.cloud/query/v1 ``` Replace the following placeholders: `` : The region of your cluster, for example, `us-west-2`. `` : The cloud provider of your cluster, for example, `aws`. To query a Lightning Table over a private network, send requests to the private API endpoint for your networking option instead. For the endpoint formats, see [Private Networking for Lightning Tables in Confluent Cloud](../private-networking.md#lightning-tables-private-networking). Requests to this endpoint use HTTP Basic authentication with a Global API key. The Global API key is the same credential that you use to connect an MCP client. To create a Global API key, see [Create a Global API key](../access-control.md#lightning-tables-create-global-api-key). To generate the Base64 token for authentication, see [Generate the Base64 token](../access-control.md#rtce-generate-token). Use the resulting token in the `Authorization` header for every request: `Authorization: Basic `. The job status endpoint, which you use to retrieve the result of a large query, uses the same host and the same Global API key. The one exception is the download of a large query result from cloud storage, which doesn’t take the `Authorization` header. For more information, see [Retrieve the result of a large query](#lightning-queries-large-results). ## Run a Lightning Table query Running a Lightning Table query means sending a read-only SQL statement to the endpoint. If the query finishes quickly and its result is small, the endpoint returns the result inline, with a `200 OK` response. Otherwise, the endpoint returns a `202 Accepted` response with a statement ID. You poll the job status with that ID, and then you download the result from cloud storage. For more information, see [Retrieve the result of a large query](#lightning-queries-large-results). This endpoint exposes a single operation. To list topics or describe a schema, use the `SHOW TABLES` or `DESCRIBE` statement in the same request. ### Query request Send a POST request with a JSON body to the endpoint, like the following example: ```bash curl -X POST \ https://sql...confluent.cloud/query/v1 \ -H "Content-Type: application/json" \ -H "Authorization: Basic " \ -d '{ "catalog_name": "", "database_name": "", "query": "", "options": { "max_result_rows": , "result_format": "" } }' ``` The request body supports the following fields: `catalog_name` : Required. Your Confluent Cloud environment ID. `database_name` : Required. Your Kafka cluster ID. `query` : Required. The SQL statement to run. `options.max_result_rows` : Optional. The maximum number of rows to return. Use it to limit the size of a response. If you omit it, the API returns all rows. `options.result_format` : Optional. Controls how row data is encoded in the response:
| Format | Structure | Type fidelity | |------------------|--------------------------------------------------------------------------|-------------------------------| | `JSON` (default) | Rows as arrays of string-encoded values | Values encoded as strings | | `ARROW_STREAM` | Base64-encoded Apache Arrow inter-process communication
(IPC) stream | Preserves native column types | `client_info` : Optional. Free-form key-value metadata about the calling client, for your own telemetry and debugging. For the SQL that a Lightning Table query supports and the operations it rejects, see [SQL Reference for Lightning Table Queries in Confluent Cloud](sql-reference.md#lightning-tables-sql-reference). ### Query response A successful response has one of the following status codes: `200 OK` : The response contains the result inline. `202 Accepted` : The query returns a large result or takes a long time, so the response contains a statement ID instead of the result. For more information, see [Retrieve the result of a large query](#lightning-queries-large-results). A `200` response returns the result schema and rows inline, similar to the following example: ```json { "api_version": "query/v1", "kind": "Statement", "result": { "result_format": "JSON", "schema": { "columns": [ {"name": "ORDER_ID", "type": {"type": "VARCHAR", "nullable": false, "length": 2147483647}}, {"name": "STATUS", "type": {"type": "VARCHAR", "nullable": true, "length": 2147483647}}, {"name": "TOTAL", "type": {"type": "DECIMAL", "nullable": true, "precision": 10, "scale": 2}} ] }, "data": [ ["ORD-4521", "completed", "128.50"] ] } } ``` Under the `JSON` result format, column values are strings. `ROW`, `ARRAY`, and `MAP` values are nested JSON arrays. For the encoding of each type and the full result schema, see [Execute SQL Statement](https://docs.confluent.io/cloud/current/ccloud/execute-query-v-1-statement/). ### Query examples The following examples assume you’ve generated a Base64 token and stored it in the `TOKEN` environment variable, as described in [Generate the Base64 token](../access-control.md#rtce-generate-token). Discover the available tables: ```bash curl -X POST \ https://sql.us-west-2.aws.confluent.cloud/query/v1 \ -H "Content-Type: application/json" \ -H "Authorization: Basic $TOKEN" \ -d '{ "catalog_name": "env-abc123", "database_name": "lkc-abc123", "query": "SHOW TABLES" }' ``` Describe a table’s columns: ```bash curl -X POST \ https://sql.us-west-2.aws.confluent.cloud/query/v1 \ -H "Content-Type: application/json" \ -H "Authorization: Basic $TOKEN" \ -d '{ "catalog_name": "env-abc123", "database_name": "lkc-abc123", "query": "DESCRIBE orders_topic" }' ``` If a topic name contains a non-alphanumeric character, such as a hyphen, enclose it in backticks in the query string. For example, `SELECT * FROM `stock-trades` LIMIT 10`. Quoted names are case sensitive, but unquoted names aren’t. Look up a single order by key: ```bash curl -X POST \ https://sql.us-west-2.aws.confluent.cloud/query/v1 \ -H "Content-Type: application/json" \ -H "Authorization: Basic $TOKEN" \ -d '{ "catalog_name": "env-abc123", "database_name": "lkc-abc123", "query": "SELECT * FROM orders_topic WHERE ORDER_ID = '\''ORD-4521'\''" }' ``` Filter and sort results: ```bash curl -X POST \ https://sql.us-west-2.aws.confluent.cloud/query/v1 \ -H "Content-Type: application/json" \ -H "Authorization: Basic $TOKEN" \ -d '{ "catalog_name": "env-abc123", "database_name": "lkc-abc123", "query": "SELECT ORDER_ID, STATUS, TOTAL FROM orders_topic WHERE STATUS = '\''completed'\'' AND TOTAL > 100 ORDER BY TOTAL DESC LIMIT 10" }' ``` Request results as an Arrow stream that preserves native column types instead of string-encoded JSON: ```bash curl -X POST \ https://sql.us-west-2.aws.confluent.cloud/query/v1 \ -H "Content-Type: application/json" \ -H "Authorization: Basic $TOKEN" \ -d '{ "catalog_name": "env-abc123", "database_name": "lkc-abc123", "query": "SELECT ORDER_ID, TOTAL FROM orders_topic LIMIT 5", "options": {"result_format": "ARROW_STREAM"} }' ``` The response’s `data` field contains a Base64-encoded Arrow IPC stream instead of an array of rows: ```json { "api_version": "query/v1", "kind": "Statement", "result": { "result_format": "ARROW_STREAM", "schema": { "columns": [ {"name": "ORDER_ID", "type": {"type": "VARCHAR", "nullable": false, "length": 2147483647}}, {"name": "TOTAL", "type": {"type": "DECIMAL", "nullable": true, "precision": 10, "scale": 2}} ] }, "data": "" } } ``` ## Retrieve the result of a large query A query that returns a large result or takes a long time runs *asynchronously*. Instead of returning the result, the endpoint returns a statement ID, and the query keeps running in the background. When the query finishes, Confluent stores the result in cloud storage, and you download it from there. To retrieve the result of an asynchronous query, you do the following: 1. Run the query and receive a `202 Accepted` response. 2. Poll the job status until the query finishes. 3. Download the result. ### When a query runs asynchronously The server decides whether a query runs asynchronously, not your application. A query runs asynchronously when it returns a large result or takes a long time. Otherwise, the endpoint returns the result inline. You can’t predict or control which response you get, so your application must handle both `200` and `202` responses. A `202 Accepted` response contains the statement ID and the identifiers of the queried table: ```json { "api_version": "query/v1", "kind": "Statement", "result": { "statement_id": "", "environment_id": "env-abc123", "kafka_cluster_id": "lkc-abc123", "topic_name": "orders_topic" } } ``` The response also includes a `Location` header. It contains the path of the job status endpoint for this query, relative to the query endpoint’s host: ```text Location: /query/v1/jobs/env-abc123/lkc-abc123/orders_topic//status ``` ### Poll the job status To check whether an asynchronous query has finished, send a GET request to the job status endpoint: ```text https://sql...confluent.cloud/query/v1/jobs/////status ``` To build this URL, append the `Location` header value from the `202` response to the host that you sent the query to. The path contains the following parameters: `environment_id` : Your Confluent Cloud environment ID. `kafka_cluster_id` : Your Kafka cluster ID. `topic_name` : The name of the topic you queried. `statement_id` : The statement ID from the `202` response. Any user or service account that can read the topic, such as one with the DeveloperRead role, can poll the job status and get the result URL. This applies to every statement on that topic, not only the statements that the account ran. The response contains the job’s current state: | State | Meaning | |-------------|----------------------------------------------------------------------------------------------------------------------------------------| | `RUNNING` | The query is still running. Poll again later. | | `SUCCEEDED` | The query finished, and the result is ready to download. The
response contains the result URL. | | `FAILED` | The query or the result upload failed. In most cases, the
response also contains an `errors` array that describes the
failure. | Poll until the state is `SUCCEEDED` or `FAILED`. While the query runs, the response looks like the following example: ```json { "api_version": "query/v1", "kind": "JobStatus", "result": { "state": "RUNNING" } } ``` When the query succeeds, the `result_urls` array contains the URL of the result: ```json { "api_version": "query/v1", "kind": "JobStatus", "result": { "state": "SUCCEEDED", "result_urls": [ "https://s3..amazonaws.com/?X-Amz-" ] } } ``` When the query fails, the response usually includes an `errors` array that describes the failure: ```json { "api_version": "query/v1", "kind": "JobStatus", "result": { "state": "FAILED" }, "errors": [ { "code": "FE_STATEMENT_FAILED", "title": "Statement Failed", "detail": "" } ] } ``` ### Download the result The URL in the `result_urls` array is a presigned URL. The signature in the URL authorizes the download, and the URL is valid for a limited time. If the URL expires before you download the result, poll the job status again to get a fresh URL. Send a GET request to the URL without an `Authorization` header. If you include one, cloud storage rejects the request. The result file’s contents depend on the `result_format` option that you set in the query request: `JSON` : A JSON document with the same structure as an inline `JSON` result. `ARROW_STREAM` : A binary Apache Arrow IPC stream. Unlike an inline result, the stream isn’t Base64-encoded. Save it to a file. Read the file with an Arrow library. ### Asynchronous query example The following example retrieves the result of a large query. It assumes you’ve stored a Base64 token in the `TOKEN` environment variable. 1. Run the query and save the response headers to a file: ```bash curl -X POST \ https://sql.us-west-2.aws.confluent.cloud/query/v1 \ -H "Content-Type: application/json" \ -H "Authorization: Basic $TOKEN" \ -D headers.txt \ -d '{ "catalog_name": "env-abc123", "database_name": "lkc-abc123", "query": "SELECT * FROM orders_topic", "options": {"result_format": "ARROW_STREAM"} }' ``` If the status code is `202`, `headers.txt` contains the `Location` header. 2. Poll the job status by using the `Location` header value from `headers.txt`: ```bash LOCATION=$(awk 'tolower($1)=="location:" {print $2}' headers.txt | tr -d '\r') curl "https://sql.us-west-2.aws.confluent.cloud${LOCATION}" \ -H "Authorization: Basic $TOKEN" ``` Repeat this request until the state is `SUCCEEDED` or `FAILED`. 3. Download the result from the URL in the `result_urls` array without an `Authorization` header: ```bash curl "" -o result.arrow ``` ## Error responses Every error response uses the same JSON structure: ```json { "errors": [ { "id": "", "status": "400", "title": "Bad Request", "code": "", "detail": "", "source": {"pointer": "/query"} } ] } ``` The `id`, `status`, and `title` fields are always present. The `code`, `detail`, and `source` fields are present when the server can provide more specific information. For the error codes that the job status endpoint returns, see [Query Async Job Status](https://docs.confluent.io/cloud/current/ccloud/get-query-v-1-job-status/). Every response, including an error response, has an `X-Request-Id` header. If you contact Confluent Support about a failed request, include the request ID. The following table describes the error status codes that the query and job status endpoints return: | Status | When it occurs | |----------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 400 | The request has a syntax error, invalid SQL, or an unrecognized
`catalog_name` or `database_name`. | | 401 | The request is missing credentials, or the API key is invalid. | | 403 | The credentials are valid, but the principal (user or service
account) isn’t authorized for this cluster or statement. | | 404 | The Lightning Table isn’t available for querying, for example,
because it’s still being set up. Retry the request. The job
status endpoint also returns `404` when it can’t find the
statement ID. | | 429 | The request exceeds the rate limit. | | 500 | An unexpected server error occurs. | ## Rate limits and quotas Lightning Table queries through the API share a rate limit with the Real-Time Context Engine MCP tools (`listTopics`, `getMetadata`, and `queryData`). Requests from either count toward that limit. When you exceed the limit, the MCP tools report `RATE_LIMIT_EXCEEDED`, and a request to the query endpoint returns a `429` response. Requests to the job status endpoint don’t count toward this rate limit. A Lightning Table query that you run through the API has no row limit. The MCP tools have their own row limit. For more information, see [Query limits](mcp.md#rtce-query-limits). A query result has a maximum size. If a result exceeds it, the job fails. To reduce the result size, add a `LIMIT` clause or a narrower filter to your query. ## Related content - [Query a Lightning Table in Confluent Cloud](overview.md#lightning-tables-query-overview) - [SQL Reference for Lightning Table Queries in Confluent Cloud](sql-reference.md#lightning-tables-sql-reference) - [Query a Lightning Table with MCP in Confluent Cloud](mcp.md#rtce-query-data) - [Manage Access to Lightning Tables in Confluent Cloud](../access-control.md#rtce-access-control) - [Lightning Tables Support and Limitations in Confluent Cloud](../limitations.md#rtce-limitations) - [Execute SQL Statement](https://docs.confluent.io/cloud/current/ccloud/execute-query-v-1-statement/) - [Query Async Job Status](https://docs.confluent.io/cloud/current/ccloud/get-query-v-1-job-status/) #### 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).