SQL Keywords and Operators in ksqlDB for Confluent Platform¶
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 |
!= or <> |
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 |
|| or + |
concatenation | string |
:= |
assignment | all |
-> |
struct field dereference | struct |
. |
source dereference | table, stream |
=> |
lambda expression | function |
E or e |
exponent | numeric |
NOT |
logical NOT | boolean |
AND |
logical AND | boolean |
OR |
logical OR | boolean |
BETWEEN |
test if value within range | numeric, string |
LIKE |
match a pattern | string |
IN |
short hand for multiple OR expressions |
all |
-- |
simple comment | string |
/* */ |
bracketed comment | string |
--@ |
directive comment | string |