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 |