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