SQL Keywords and Operators in ksqlDB for Confluent Platform

Keywords

The following table shows all keywords in the language.

SQL Keywords
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, …)
PRINT 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