SQL Reference for Lightning Table Queries in Confluent Cloud

A Lightning Table query runs read-only SQL on a single Lightning Table. The same SQL applies whether you query in the Confluent Cloud Console, through an HTTP API, with a Model Context Protocol (MCP) client, with the Confluent CLI, or with a Java Database Connectivity (JDBC) client.

Supported SQL capabilities

A Lightning Table query supports the following SQL capabilities:

  • Key lookups: Look up rows by primary key value.

  • Filters: Narrow results in the WHERE clause by using the following operators: equality (=), inequality (!=), comparison (>, <, >=, <=), set membership (IN, NOT IN), null checks (IS NULL, IS NOT NULL), pattern matching (LIKE, NOT LIKE), and range (BETWEEN).

  • Compound predicates: Combine multiple conditions with the AND and OR operators.

  • Column selection: Select specific columns to reduce the response size, or select all columns.

  • Ordering: Sort results in ascending or descending order with the ORDER BY clause.

  • Row limits: Control the number of rows returned with the LIMIT clause.

Example query

The following query reads the Lightning Table for the orders topic. It returns the 10 most recent orders with more than five units.

SELECT ORDERID, ITEMID, ORDERUNITS
FROM `orders`
WHERE ORDERUNITS > 5
ORDER BY ORDERTIME DESC
LIMIT 10;

The query uses the following supported capabilities:

  • Column selection: The SELECT clause returns only the ORDERID, ITEMID, and ORDERUNITS columns.

  • Filters: The WHERE clause uses the > operator to return only orders with more than five units.

  • Ordering: The ORDER BY clause sorts the orders by the ORDERTIME column, newest first.

  • Row limits: The LIMIT clause returns at most 10 rows.

Topic names

If a topic name contains any character other than letters and numbers, enclose it in backticks, as in the statement SELECT * FROM `web-orders` LIMIT 10;. Quoted names are case sensitive, but unquoted names aren’t.

Limits

Limits, such as the maximum number of rows a query returns, depend on the query interface. For the limits that apply when you query with MCP, see Query limits.

Unsupported queries

A Lightning Table query is read-only. It doesn’t support the following operations:

  • Aggregate functions (COUNT, SUM, AVG, MIN, MAX)

  • GROUP BY clauses

  • Joins

  • Subqueries

  • Set operators (UNION, EXCEPT, INTERSECT)

  • DDL statements (CREATE, ALTER, DROP)

  • DML statements (INSERT, UPDATE, DELETE)

Confluent Cloud rejects an unsupported query without running it.

Supported data types

You can enable a Lightning Table for a topic whose schema uses the following data types. Lightning Tables support Avro, Protobuf, and JSON Schema formats.

Numeric types

Lightning Tables support the following numeric types:

  • TINYINT

  • SMALLINT

  • INT

  • BIGINT

  • FLOAT

  • DOUBLE

  • DECIMAL

Boolean type

Lightning Tables support the BOOLEAN type.

String and binary types

Lightning Tables support the following string and binary types:

  • CHAR

  • VARCHAR

  • BINARY

  • VARBINARY

Temporal types

Lightning Tables support the following temporal types:

  • DATE

  • TIME (precision 0 only)

  • TIMESTAMP (precision 0 to 6)

  • TIMESTAMP_LTZ (precision 0 to 6)

Complex types

Lightning Tables support the following complex types:

  • ARRAY

  • MAP, except MAP types with nullable non-character keys

  • ROW (also known as STRUCT)

Unsupported data types

Lightning Tables don’t support the following data types:

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY TO SECOND

  • MULTISET

  • TIMESTAMP WITH TIME ZONE

  • RAW

If your topic schema uses an unsupported data type, you can’t enable a Lightning Table for that topic.