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.

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.

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.
A Global API key. To create one, see Create a Global API key.
The DeveloperRead role, granted on both the Kafka topic and the Schema Registry cluster. For role requirements, see Roles and permissions.

API endpoint and authentication

Send requests to the following endpoint:

https://sql.<region>.<cloud>.confluent.cloud/query/v1

Replace the following placeholders:

<region>: The region of your cluster, for example, us-west-2.
<cloud>: 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.

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. To generate the Base64 token for authentication, see Generate the Base64 token.

Use the resulting token in the Authorization header for every request: Authorization: Basic <token>.

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.

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.

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:

curl -X POST \
  https://sql.<region>.<cloud>.confluent.cloud/query/v1 \
  -H "Content-Type: application/json" \
  -H "Authorization: Basic <token>" \
  -d '{
    "catalog_name": "<env_id>",
    "database_name": "<lkc_id>",
    "query": "<sql_statement>",
    "options": {
      "max_result_rows": <max_rows>,
      "result_format": "<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.

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.

A 200 response returns the result schema and rows inline, similar to the following example:

{
  "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.

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.

Discover the available tables:

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:

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:

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:

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:

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:

{
  "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": "<base64_encoded_arrow_ipc_stream>"
  }
}

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:

Run the query and receive a 202 Accepted response.
Poll the job status until the query finishes.
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:

{
  "api_version": "query/v1",
  "kind": "Statement",
  "result": {
    "statement_id": "<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:

Location: /query/v1/jobs/env-abc123/lkc-abc123/orders_topic/<statement_id>/status

Poll the job status

To check whether an asynchronous query has finished, send a GET request to the job status endpoint:

https://sql.<region>.<cloud>.confluent.cloud/query/v1/jobs/<environment_id>/<kafka_cluster_id>/<topic_name>/<statement_id>/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:

{
  "api_version": "query/v1",
  "kind": "JobStatus",
  "result": {
    "state": "RUNNING"
  }
}

When the query succeeds, the result_urls array contains the URL of the result:

{
  "api_version": "query/v1",
  "kind": "JobStatus",
  "result": {
    "state": "SUCCEEDED",
    "result_urls": [
      "https://s3.<region>.amazonaws.com/<path>?X-Amz-<signature_parameters>"
    ]
  }
}

When the query fails, the response usually includes an errors array that describes the failure:

{
  "api_version": "query/v1",
  "kind": "JobStatus",
  "result": {
    "state": "FAILED"
  },
  "errors": [
    {
      "code": "FE_STATEMENT_FAILED",
      "title": "Statement Failed",
      "detail": "<failure_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.

Run the query and save the response headers to a file:

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.

Poll the job status by using the Location header value from headers.txt:

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.

Download the result from the URL in the result_urls array without an Authorization header:
```
curl "<result_url>" -o result.arrow
```

Error responses

Every error response uses the same JSON structure:

{
  "errors": [
    {
      "id": "<uuid>",
      "status": "400",
      "title": "Bad Request",
      "code": "<error_code>",
      "detail": "<error_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.

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.

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.