Appendix

Data Types

type examples
NULL NULL
INTEGER 1, -1, 1234567
FLOAT 2.3, -3.56, 232.4
NUMERIC 1, 2.3
BOOLEAN TRUE, FALSE
BYTEA ‘0xaa0xbb’
STRING “deadbeef”
DATE DATE 2020-06-10
TIME TIME 11:18:30
TIMESTAMP TIMESTAMP 2022-01-01 T 12:00:00 +08:00
INTERVAL INTERVAL 00:00:10
JSON {“a”: 1, “b”: 2}
ARRAY [1, 2, 3]
MAP {1: “aa”, 2: “bb”}

Keywords

keyword description
ABS absolute value
ACOS arccosine
ACOSH inverse hyperbolic cosine
AND logical and operator
ARRAY_CONTAIN given an array, checks if a search value is contained in the array
ARRAY_DISTINCT returns an array of all the distinct values
ARRAY_EXCEPT ARRAY_DISTINCT except for those also present in the second array
ARRAY_INTERSECT returns an array of all the distinct elements from the intersection of both input arrays
ARRAY_JOIN creates a flat string representation of all elements contained in the given array
ARRAY_LENGTH return the length of the given array
ARRAY_MAX returns the maximum value from the given array of primitive elements
ARRAY_MIN returns the minimum value from the given array of primitive elements
ARRAY_REMOVE removes all elements from the input array equal to the second argument
ARRAY_SORT sort the given array
ARRAY_UNION returns an array of all the distinct elements from the union of both input arrays
AS stream or field name alias
ASIN arcsine
ASINH inverse hyperbolic sine
ATAN arctangent
ATANH inverse hyperbolic tangent
AVG average function
BETWEEN range operator, used with AND
BY do something by certain conditions, used with GROUP or ORDER
CEIL rounds a number UPWARDS to the nearest integer
COS cosine
COSH hyperbolic cosine
COUNT count function
CREATE create a stream / connector
DATE prefix of date constant
DAY interval unit
DROP drop a stream
EXP exponent
FLOOR rounds a number DOWNWARDS to the nearest integer
FROM specify where to select data from
GROUP group values by certain conditions, used with BY
HAVING filter select values by a condition
HOPPING hopping window
IFNULL if the first argument is NULL returns the second, else the first
INSERT insert data into a stream, used with INTO
INTERVAL prefix of interval constant
INTO insert data into a stream, used with INSERT
IS_ARRAY to determine if the given value is an array of values
IS_BOOL to determine if the given value is a boolean
IS_DATE to determine if the given value is a date value
IS_FLOAT to determine if the given value is a float
IS_INT to determine if the given value is an integer
IS_MAP to determine if the given value is a map of values
IS_NUM to determine if the given value is a number
IS_STR to determine if the given value is a string
IS_TIME to determine if the given value is a time value
JOIN for joining two streams
LEFT joining type, used with JOIN
LEFT_TRIM trim spaces from the left end of a string
LOG logarithm with base e
LOG10 logarithm with base 10
LOG2 logarithm with base 2
MAX maximum function
MIN minimum function
MINUTE interval unit
MONTH interval unit
NOT logical not operator
NULLIF returns NULL if the first argument is equal to the second, otherwise the first
OR logical or operator
ORDER sort values by certain conditions, used with BY
OUTER joining type, used with JOIN
REVERSE reverse a string
RIGHT_TRIM trim spaces from the right end of a string
ROUND rounds a number to the nearest integer
SECOND interval unit
SELECT query a stream
SHOW show something to stdout
SIGN return the sign of a numeric value as an INTEGER
SIN sine
SINH hyperbolic sine
SLIDING sliding window
SQRT square root
STREAM specify a stream, used with CREATE
STRLEN get the length of a string
SUM sum function
TAN tangent
TANH hyperbolic tangent
TIME prefix of the time constant
TO_LOWER convert a string to lowercase
TO_STR convert a value to string
TO_UPPER convert a string to uppercase
TRIM trim spaces from both ends of a string
TUMBLING tumbling window
VALUES specify inserted data, used with INSERT INTO
WEEK interval unit
WHERE filter selected values by a condition
WITH specify properties when creating a stream
WITHIN specify time window when joining two streams
YEAR interval unit

Operators

operator description
= equal to
<> not equal to
< less than
> greater than
<= less than or equal to
>= greater than or equal to
+ addition
- subtraction
* multiplication
. access field of a stream
[] access item of an array
=> access item of a map
AND logical and operator
OR logical or operator
:: type casting
-> JSON access(as JSON) by key
->> JSON access(as text) by key
#> JSON access(as JSON) by path
#>> JSON access(as text) by path

Scalar Functions

function description
ABS absolute value
ACOS arccosine
ACOSH inverse hyperbolic cosine
ARRAY_CONTAIN given an array, checks if a search value is contained in the array
ARRAY_DISTINCT returns an array of all the distinct values
ARRAY_EXCEPT ARRAY_DISTINCT except for those also present in the second array
ARRAY_INTERSECT returns an array of all the distinct elements from the intersection of both input arrays
ARRAY_JOIN creates a flat string representation of all elements contained in the given array
ARRAY_LENGTH return the length of the given array
ARRAY_MAX returns the maximum value from the given array of primitive elements
ARRAY_MIN returns the minimum value from the given array of primitive elements
ARRAY_REMOVE removes all elements from the input array equal to the second argument
ARRAY_SORT sort the given array
ARRAY_UNION returns an array of all the distinct elements from the union of both input arrays
ASIN arcsine
ASINH inverse hyperbolic sine
ATAN arctangent
ATANH inverse hyperbolic tangent
CEIL rounds a number UPWARDS to the nearest integer
COS cosine
COSH hyperbolic cosine
EXP exponent
FLOOR rounds a number DOWNWARDS to the nearest integer
IFNULL if the first argument is NULL returns the second, else the first
NULLIF returns NULL if the first argument is equal to the second, otherwise the first
IS_ARRAY to determine if the given value is an array of values
IS_BOOL to determine if the given value is a boolean
IS_DATE to determine if the given value is a date value
IS_FLOAT to determine if the given value is a float
IS_INT to determine if the given value is an integer
IS_MAP to determine if the given value is a map of values
IS_NUM to determine if the given value is a number
IS_STR to determine if the given value is a string
IS_TIME to determine if the given value is a time value
LEFT_TRIM trim spaces from the left end of a string
LOG logarithm with base e
LOG10 logarithm with base 10
LOG2 logarithm with base 2
REVERSE reverse a string
RIGHT_TRIM trim spaces from the right end of a string
ROUND rounds a number to the nearest integer
SIGN return the sign of a numeric value as an INTEGER
SIN sine
SINH hyperbolic sine
SQRT square root
STRLEN get the length of a string
TAN tangent
TANH hyperbolic tangent
TO_LOWER convert a string to lowercase
TO_STR convert a value to string
TO_UPPER convert a string to uppercase
TOPK topk aggregate function
TOPKDISTINCT topkdistinct aggregate function
TRIM trim spaces from both ends of a string

Aggregate Functions

function description
AVG average
COUNT count
MAX maximum
MIN minimum
SUM sum
TOPK top k values as array
TOPKDISTINCT distinct top k values as array