SQL Keywords and Operators in ksqlDB for Confluent Platform
This reference lists all valid keywords and operators in ksqlDB SQL.
Keywords
The following table shows all keywords in the language.
Keyword |
Description |
Example |
|---|---|---|
ADD |
Add a column to a stream or table |
ADD (COLUMN)? identifier type; |
ADVANCE BY |
Hop size in hopping window |
WINDOW HOPPING (SIZE 30 SECONDS, ADVANCE BY 10 SECONDS) |
ALL |
List hidden topics |
SHOW ALL TOPICS; |
ALTER |
Change a property, stream, or table |
ALTER ‘auto.offset.reset’=’latest’; |
AND |
Logical “and” operator |
WHERE userid<>’User_1’ AND userid<>’User_2’ |
ARRAY |
One-indexed array of elements |
SELECT ARRAY[1, 2] FROM s1 EMIT CHANGES; |
AS |
Alias a column, expression, or type |
No Example Provided |
ASSERT |
Assert value, stream, table, tombstone, topic, schema |
ASSERT NULL VALUES sourceName (columns)? KEY values; |
BEGINNING |
Print from start of topic |
PRINT <topic-name> FROM BEGINNING; |
BETWEEN |
Constrain a value to a range |
SELECT event FROM events WHERE event_id BETWEEN 10 AND 20 … |
BY |
Specify expression |
GROUP BY regionid, ADVANCE BY 10 SECONDS, PARTITION BY userid |
BYTES |
Bytes data type |
No Example Provided |
CASE |
Select a condition from expressions |
SELECT CASE WHEN condition THEN result [ WHEN … THEN … ] … END |
CAST |
Change expression type |
SELECT id, CONCAT(CAST(COUNT(*) AS VARCHAR), ‘_HELLO’) FROM views … |
CHANGES |
Specify incremental refinement type |
SELECT * FROM users EMIT CHANGES; |
CONNECTOR |
Manage a connector |
CREATE SOURCE CONNECTOR ‘jdbc-connector’ WITH( … |
CONNECTORS |
List all connectors |
SHOW CONNECTORS; |
CREATE |
Create an object |
CREATE STREAM rock_songs (artist VARCHAR, title VARCHAR) … |
DATE |
Date data type |
No Example Provided |
DAY |
Time unit of one day for a window |
WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1 DAY) |
DAYS |
Time unit of days for a window |
WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1000 DAYS) |
DECIMAL |
Decimal numeric type |
No Example Provided |
DEFINE |
Define a variable |
DEFINE name = ‘Tom Sawyer’; |
DELETE |
Remove a topic |
DROP TABLE <table-name> DELETE TOPIC; |
DESCRIBE |
List details for object(s) |
DESCRIBE PAGEVIEWS; |
DISTINCT |
Test for uniqueness |
CREATE STREAM output AS SELECT A, A IS NOT DISTINCT FROM 1, …); |
DROP |
Delete an object |
DROP CONNECTOR <connector-name>; |
ELSE |
Condition in WHEN statement |
CASE WHEN units<2 THEN ‘sm’ WHEN units<4 THEN ‘med’ ELSE ‘large’ … |
EMIT |
Specify push query |
SELECT * FROM users EMIT CHANGES; |
END |
Close a CASE block |
SELECT CASE WHEN condition THEN result [ WHEN … THEN … ] … END |
EXISTS |
Test whether object exists |
DROP STREAM IF EXISTS <stream-name>; |
EXPLAIN |
Show execution plan |
EXPLAIN <query-name>; or EXPLAIN <expression>; |
EXTENDED |
List details for object(s) |
DESCRIBE <stream-name> EXTENDED; |
FALSE |
Boolean value of false |
No Example Provided |
FINAL |
Suppress intermediate results on a windowed aggregation |
SELECT * FROM users EMIT FINAL; |
FROM |
Specify record source for queries |
SELECT * FROM users; |
FULL |
Specify FULL JOIN |
CREATE TABLE t AS SELECT * FROM l FULL OUTER JOIN r ON l.ID = r.ID; |
FUNCTION |
List details for a function |
DESCRIBE FUNCTION <function-name>; |
FUNCTIONS |
List all functions |
SHOW FUNCTIONS; |
GRACE PERIOD |
Grace period for a tumbling window |
WINDOW TUMBLING (SIZE 1 HOUR, GRACE PERIOD 2 HOURS) |
GROUP |
Group rows with the same values |
SELECT regionid, COUNT(*) FROM pageviews GROUP BY regionid |
HAVING |
Condition expression |
GROUP BY card_number HAVING COUNT(*) > 3 |
HEADER |
Specify a header column |
CREATE STREAM S (H BYTES HEADER(‘abc’)…) |
HEADERS |
Specify a headers column |
CREATE STREAM S (H ARRAY<STRUCT<key STRING, value BYTES>> HEADERS…) |
HOPPING |
Specify a hopping window |
WINDOW HOPPING (SIZE 30 SECONDS, ADVANCE BY 10 SECONDS) |
HOUR |
Time unit of one hour for a window |
WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 1 DAY) |
HOURS |
Time unit of hours for a window |
WINDOW TUMBLING (SIZE 2 HOURS, RETENTION 1 DAY) |
IF EXISTS |
Test whether object exists |
DROP STREAM IF EXISTS <stream-name>; |
IN |
Specify multiple values |
WHERE name IN (value1, value2, …) |
INNER JOIN |
Specify INNER JOIN |
CREATE TABLE t AS SELECT * FROM l INNER JOIN r ON l.ID = r.ID; |
INSERT |
Insert new records in a stream/table |
INSERT INTO <stream-name> … |
INTEGER |
Integer numeric type |
CREATE TABLE profiles (id INTEGER PRIMARY KEY, …) |
INTERVAL |
Number of messages to skip in PRINT |
PRINT <topic-name> INTERVAL 5; |
INTO |
Stream/table to insert values |
INSERT INTO stream_name … |
IS |
Use with keywords like NOT and DISTINCT |
CREATE STREAM output AS SELECT A, A IS NOT DISTINCT FROM 1, …); |
JOIN |
Match records in streams/tables |
CREATE TABLE t AS SELECT * FROM l INNER JOIN r ON l.ID = r.ID; |
KEY |
Specify key column |
CREATE TABLE users (userId INTEGER PRIMARY KEY, …) |
LEFT JOIN |
Specify LEFT JOIN |
CREATE TABLE t AS SELECT * FROM l LEFT JOIN r ON l.ID = r.ID; |
LIKE |
Match pattern |
WHERE UCASE(gender)=’FEMALE’ AND LCASE(regionid) LIKE ‘%_6’ |
LIMIT |
Number of records to output |
SELECT * FROM users EMIT CHANGES LIMIT 5; |
LIST |
List objects |
LIST STREAMS; or SHOW STREAMS; |
MAP |
map data type |
SELECT MAP(k1:=v1, k2:=v1*2) FROM s1 EMIT CHANGES; |
MILLISECOND |
Time unit of one ms for a window |
WINDOW TUMBLING (SIZE 1 MILLISECOND, RETENTION 1 DAY) |
MILLISECONDS |
Time unit of ms for a window |
WINDOW TUMBLING (SIZE 100 MILLISECONDS, RETENTION 1 DAY) |
MINUTE |
Time unit of one min for a window |
WINDOW TUMBLING (SIZE 1 MINUTE, RETENTION 1 DAY) |
MINUTES |
Time unit of mins for a window |
WINDOW TUMBLING (SIZE 30 MINUTES, RETENTION 1 DAY) |
MONTH |
Time unit of one month for a window |
WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 1 MONTH) |
MONTHS |
Time unit of months for a window |
WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 2 MONTHS) |
NOT |
Logical “not” operator |
(no example provided) |
NULL |
Field with no value |
CREATE STREAM s AS SELECT * FROM t WHERE (t.c1 IS NOT NULL); |
ON |
Specify join criteria |
LEFT JOIN users ON pageviews.userid = users.userid |
OR |
Logical “or” operator |
WHERE userid=’User_1’ OR userid=’User_2’ |
OUTER JOIN |
Specify OUTER JOIN |
CREATE TABLE t AS SELECT * FROM l FULL OUTER JOIN r ON l.ID = r.ID; |
PARTITION BY |
Repartition a stream |
PARTITION BY <key-field> |
PARTITIONS |
Partitions to distribute keys over |
CREATE STREAM users_rekeyed WITH (PARTITIONS=6) AS … |
PERIOD |
Grace period for a tumbling window |
WINDOW TUMBLING (SIZE 1 HOUR, GRACE PERIOD 2 HOURS) |
PLUGINS |
Show connector plugins |
LIST CONNECTOR PLUGINS; or SHOW CONNECTOR PLUGINS; |
PRIMARY KEY |
Specify primary key column |
CREATE TABLE users (userId INTEGER PRIMARY KEY, …) |
Output records in a topic |
PRINT <topic-name> FROM BEGINNING; |
|
PROPERTIES |
List all properties |
LIST PROPERTIES; or SHOW PROPERTIES; |
PAUSE |
Pause a persistent query |
PAUSE query_id; |
QUERIES |
List all queries |
LIST QUERIES; or SHOW QUERIES; |
REPLACE |
String replace |
REPLACE(col1, ‘foo’, ‘bar’) |
RESUME |
Resume a paused persistent query |
RESUME query_id; |
RETENTION |
Time to retain past windows |
WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1000 DAYS) |
RIGHT JOIN |
Specify RIGHT JOIN |
CREATE TABLE t AS SELECT * FROM l RIGHT JOIN r ON l.ID = r.ID; |
RUN SCRIPT |
Execute queries from a file |
RUN SCRIPT <path-to-query-file>; |
SAMPLE |
Number of messages to skip in PRINT |
PRINT <topic-name> SAMPLE 5; |
SCHEMA |
Specify a schema |
ASSERT SCHEMA SUBJECT ‘foo-value’ ID 5; |
SECOND |
Time unit of one sec for a window |
WINDOW TUMBLING (SIZE 1 SECOND, RETENTION 1 DAY) |
SECONDS |
Time unit of secs for a window |
WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1 DAY) |
SELECT |
Query a stream or table |
SELECT * FROM metrics EMIT CHANGES; |
SESSION |
Specify a session window |
WINDOW SESSION (60 SECONDS) |
SET |
Assign a property value |
SET ‘auto.offset.reset’=’earliest’; |
SHOW |
List objects |
SHOW FUNCTIONS; |
SINK |
Create a sink connector |
CREATE SINK CONNECTOR … |
SIZE |
Time length of a window |
WINDOW TUMBLING (SIZE 5 SECONDS) |
SOURCE |
Create a source connector |
CREATE SOURCE CONNECTOR … |
STREAM |
Register a stream on a topic |
CREATE STREAM users_orig AS SELECT * FROM users EMIT CHANGES; |
STREAMS |
List all streams |
SHOW STREAMS; |
STRUCT |
Struct data type |
SELECT STRUCT(f1 := v1, f2 := v2) FROM s1 EMIT CHANGES; |
TABLE |
Register a table on a topic |
CREATE TABLE users (id BIGINT PRIMARY KEY, …) |
TABLES |
List all tables |
SHOW TABLES; |
TERMINATE |
End a persistent query |
TERMINATE query_id; |
THEN |
Return expression in a CASE block |
CASE WHEN units<2 THEN ‘sm’ WHEN units<4 THEN ‘med’ ELSE ‘large’ … |
TIME |
Time data type |
No Example Provided |
TIMEOUT |
Specify the amount of time to wait for an assertion to succeed |
ASSERT SCHEMA ID 4 TIMEOUT 10 SECONDS; |
TIMESTAMP |
Timestamp data type |
No Example Provided |
TIMESTAMP |
Specify a timestamp column |
CREATE STREAM pageviews WITH (TIMESTAMP=’viewtime’, … |
TOPIC |
Specify a topic |
DROP TABLE <table-name> DELETE TOPIC; |
TOPICS |
List all topics |
LIST TOPICS; or SHOW TOPICS; |
TRUE |
Boolean value of true |
No Example Provided |
TUMBLING |
Specify a tumbling window |
WINDOW TUMBLING (SIZE 5 SECONDS) |
TYPE |
Alias a complex type |
CREATE TYPE <type_name> AS <type>; |
TYPES |
List all custom type aliases |
LIST TYPES; or SHOW TYPES; |
UNDEFINE |
Undefine a variable |
UNDEFINE name; |
UNSET |
Unassign a property value |
UNSET ‘auto.offset.reset’; |
VALUES |
List of values to insert |
INSERT INTO foo VALUES (‘key’, ‘A’); |
VARIABLES |
List all variables |
LIST VARIABLES; or SHOW VARIABLES; |
WHEN |
Specify condition in a CASE block |
SELECT CASE WHEN condition THEN result [ WHEN … THEN … ] … END |
WHERE |
Filter records by a condition |
SELECT * FROM pageviews WHERE pageid < ‘Page_20’ |
WINDOW |
Groups rows with the same keys |
SELECT userid, COUNT(*) FROM users WINDOW SESSION (60 SECONDS) … |
WITH |
Specify object creation params |
CREATE STREAM pageviews WITH (TIMESTAMP=’viewtime’, … |
WITHIN |
Time range in a windowed join |
SELECT * FROM impressions i JOIN clicks c WITHIN 1 minute … |
YEAR |
Time unit of one year for a window |
WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 1 YEAR) |
YEARS |
Time unit of years for a window |
WINDOW TUMBLING (SIZE 1 HOUR, RETENTION 2 YEARS) |
Operators
The following table shows all operators in the language.
operator |
meaning |
applies to |
|---|---|---|
|
is equal to |
string, numeric |
|
is not equal to |
string, numeric |
|
is less than |
string, numeric |
|
is less than or equal to |
string, numeric |
|
is greater than |
string, numeric |
|
is greater than or equal to |
string, numeric |
|
addition for numeric, concatenation for string |
string, numeric |
|
subtraction |
numeric |
|
multiplication |
numeric |
|
division |
numeric |
|
modulus |
numeric |
|
concatenation |
string |
|
assignment |
all |
|
struct field dereference |
struct |
|
source dereference |
table, stream |
|
lambda expression |
function |
|
exponent |
numeric |
|
logical NOT |
boolean |
|
logical AND |
boolean |
|
logical OR |
boolean |
|
test if value within range |
numeric, string |
|
match a pattern |
string |
|
short hand for multiple |
all |
|
simple comment |
string |
|
bracketed comment |
string |
|
directive comment |
string |