Appendix

Keywords

The following table shows all keywords in the language.

keyword	description	example
ADVANCE	hop size in hopping window	WINDOW HOPPING (SIZE 30 SECONDS, ADVANCE BY 10 SECONDS)
ALL	list hidden topics	SHOW ALL TOPICS
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
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
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) …
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
DEFINE	define a variable	DEFINE name = 'Tom Sawyer';
DELETE	remove a Kafka topic	DROP TABLE <table-name> DELETE TOPIC;
DESCRIBE	list details for object(s)	DESCRIBE PAGEVIEWS;
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
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	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
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	test whether object exists	DROP STREAM IF EXISTS <stream-name>;
IN	specify multiple values	WHERE name IN (value1, value2, ...)
INNER	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
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 INT PRIMARY KEY, …
LEFT	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	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
NULL	field with no value
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	specify OUTER JOIN	CREATE TABLE t AS SELECT * FROM l FULL OUTER JOIN r ON l.ID = r.ID;
PARTITION	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)
PRIMARY	specify primary key column	CREATE TABLE users (userId INT PRIMARY KEY, …
PRINT	output records in a topic	PRINT <topic-name> FROM BEGINNING;
PROPERTIES	list all properties	SHOW PROPERTIES;
QUERIES	list all queries	SHOW QUERIES;
REPLACE	string replace	REPLACE(col1, 'foo', 'bar')
RETENTION	time to retain past windows	WINDOW TUMBLING (SIZE 30 SECONDS, RETENTION 1000 DAYS)
RIGHT
RUN	execute queries from a file	RUN SCRIPT <path-to-query-file>;
SCRIPT	execute queries from a file	RUN SCRIPT <path-to-query-file>;
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
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' …
TIMESTAMP	timestamp data type
TIMESTAMP	specify a timestamp column	CREATE STREAM pageviews WITH (TIMESTAMP='viewtime', …
TOPIC	specify Kafka topic to delete	DROP TABLE <table-name> DELETE TOPIC;
TOPICS	list all streams	SHOW TOPICS;
TRUE	Boolean value of true
TUMBLING	specify a tumbling window	WINDOW TUMBLING (SIZE 5 SECONDS)
TYPE	alias a complex type declaration	CREATE TYPE <type_name> AS <type>;
TYPES	list all custom type aliases	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	SHOW VARIABLES;
WHEN	specify condition in a CASE block	SELECT CASE WHEN condition THEN result [ WHEN … THEN … ] …
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
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

---

Last update: 2021-03-12