- Numeric Functions
- ABS
- CEIL
- ENTRIES
- EXP
- FLOOR
- GENERATE_SERIES
- GENERATE_SERIES
- GEO_DISTANCE
- LN
- RANDOM
- ROUND
- SIGN
- SQRT
- Collections
- ARRAYCONTAINS
- ARRAY
- MAP
- AS_MAP
- ELT
- FIELD
- SLICE
- Strings
- CONCAT
- EXTRACTJSONFIELD
- IFNULL
- INITCAP
- LCASE
- LEN
- MASK
- MASK_KEEP_LEFT
- MASK_KEEP_RIGHT
- MASK_LEFT
- MASK_RIGHT
- REPLACE
- REGEXP_EXTRACT
- SPLIT
- SUBSTRING
- TRIM
- UCASE
- Date and Time
- UNIX_DAT
- UNIX_TIMESTAMP
- DATETOSTRING
- STRINGTODATE
- STRINGTOTIMESTAMP
- TIMESTAMPTOSTRING
- URL
- URL_DECODE_PARAM
- URL_ENCODE_PARAM
- URL_EXTRACT_FRAGMENT
- URL_EXTRACT_HOST
- URL_EXTRACT_PARAMETER
- URL_EXTRACT_PATH
- URL_EXTRACT_PORT
- URL_EXTRACT_PROTOCOL
- URL_EXTRACT_QUERY
Numeric Functions¶
ABS¶
ABS(col1)
The absolute value of a value.
CEIL¶
CEIL(col1)
The ceiling of a value.
ENTRIES¶
ENTRIES(map MAP, sorted BOOLEAN)
Constructs an array of structs from the entries in a map. Each struct has
a field named K
containing the key, which is a string, and a field named
V
, which holds the value.
If sorted
is true, the entries are sorted by key.
EXP¶
EXP(col1)
The exponential of a value.
FLOOR¶
FLOOR(col1)
The floor of a value.
GENERATE_SERIES¶
GENERATE_SERIES(start, end)
Constructs an array of values between start
and end
(inclusive).
Parameters can be INT
or BIGINT
.
GENERATE_SERIES¶
GENERATE_SERIES(start, end, step)
Constructs an array of values between start
and end
(inclusive)
with a specified step size. The step can be positive or negative.
Parameters start
and end
can be INT
or BIGINT
. Parameter step
must be an INT
.
GEO_DISTANCE¶
GEO_DISTANCE(lat1, lon1, lat2, lon2, unit)
The great-circle distance between two lat-long points, both specified
in decimal degrees. An optional final parameter specifies KM
(the default) or miles
.
LN¶
LN(col1)
The natural logarithm of a value.
RANDOM¶
RANDOM()
Return a random DOUBLE value between 0.0 and 1.0.
ROUND¶
ROUND(col1)
or ROUND(col1, scale)
Round a value to the number of decimal places as specified by scale to the right of the decimal point. If scale is negative then value is rounded to the right of the decimal point.
Numbers equidistant to the nearest value are rounded up (in the positive direction). If the number of decimal places is not provided it defaults to zero.
SIGN¶
SIGN(col1)
The sign of a numeric value as an INTEGER:
- -1 if the argument is negative
- 0 if the argument is zero
- 1 if the argument is positive
null
argument is null
SQRT¶
SQRT(col1)
The square root of a value.
Collections¶
ARRAYCONTAINS¶
ARRAYCONTAINS('[1, 2, 3]', 3)
Given an array in JSON, AVRO, or PROTOBUF format, checks if a search value is contained in the array.
ARRAY¶
ARRAY[col1, col2, ...]
Construct an array from a variable number of inputs.
MAP¶
MAP(key VARCHAR := value, ...)
Construct a map from specific key-value tuples.
AS_MAP¶
AS_MAP(keys, vals)
Construct a map from a list of keys and a list of values.
ELT¶
ELT(n INTEGER, args VARCHAR[])
Returns element n
in the args
list of strings, or NULL if n
is less than
1 or greater than the number of arguments. This function is 1-indexed. ELT is
the complement to FIELD.
FIELD¶
FIELD(str VARCHAR, args VARCHAR[])
Returns the 1-indexed position of str
in args
, or 0 if not found.
If str
is NULL, the return value is 0, because NULL is not considered
to be equal to any value. FIELD is the complement to ELT.
SLICE¶
SLICE(col1, from, to)
Slices a list based on the supplied indices. The indices start at 1 and include both endpoints.
Strings¶
CONCAT¶
CONCAT(col1, '_hello')
Concatenate two strings.
EXTRACTJSONFIELD¶
EXTRACTJSONFIELD(message, '$.log.cloud')
Given a string column in JSON format, extract the field that matches. For example EXTRACTJSONFIELD is necessary in the following JSON:
{"foo": \"{\"bar\": \"quux\"}\"}
In cases where the column is really an object but declared as a STRING,
you can use the STRUCT
type, which is easier to work with. For example,
STRUCT
works in the following case:
{"foo": {"bar": "quux"}}.
IFNULL¶
IFNULL(col1, retval)
If the provided VARCHAR is NULL, return retval
, otherwise, return the
value. Only VARCHAR values are supported for the input. The return value
must be a VARCHAR.
INITCAP¶
INITCAP(col1)
Capitalize the first letter in each word and convert all other letters to lowercase. Words are delimited by whitespace.
LCASE¶
LCASE(col1)
Convert a string to lowercase.
LEN¶
LEN(col1)
The length of a string.
MASK¶
MASK(col1, 'X', 'x', 'n', '-')
Convert a string to a masked or obfuscated version of itself. The optional arguments following the input string to be masked are the characters to be substituted for upper-case, lower-case, numeric, and other characters of the input, respectively.
If the mask characters are omitted then the default values, shown in the following example, are applied.
Set a given mask character to NULL to prevent any masking of that character
type. For example: MASK("My Test $123")
will return Xx-Xxxx--nnn
, applying
all default masks. MASK("My Test $123", '*', NULL, '1', NULL)
will yield
*y *est $111
.
MASK_KEEP_LEFT¶
MASK_KEEP_LEFT(col1, numChars, 'X', 'x', 'n', '-')
Similar to the MASK
function above, except
that the first or left-most numChars
characters will not be masked in any way.
For example: MASK_KEEP_LEFT("My Test $123", 4)
will return My Txxx--nnn
.
MASK_KEEP_RIGHT¶
MASK_KEEP_RIGHT(col1, numChars, 'X', 'x', 'n', '-')
Similar to the MASK
function above, except
that the last or right-most numChars
characters will not be masked in any way.
For example:MASK_KEEP_RIGHT("My Test $123", 4)
will return Xx-Xxxx-$123
.
MASK_LEFT¶
MASK_LEFT(col1, numChars, 'X', 'x', 'n', '-')
Similar to the MASK
function above, except
that only the first or left-most numChars
characters will have any masking applied to them.
For example, MASK_LEFT("My Test $123", 4)
will return Xx-Xest $123
.
MASK_RIGHT¶
MASK_RIGHT(col1, numChars, 'X', 'x', 'n', '-')
Similar to the MASK
function above, except
that only the last or right-most numChars
characters will have any masking applied to them.
For example: MASK_RIGHT("My Test $123", 4)
will return My Test -nnn
.
REPLACE¶
REPLACE(col1, 'foo', 'bar')
Replace all instances of a substring in a string with a new string.
REGEXP_EXTRACT¶
REGEXP_EXTRACT('.*', col1)
REGEXP_EXTRACT('(([AEIOU]).)', col1, 2)
Extract the first subtring matched by the regex pattern from the input.
A capturing group number can also be specified in order to return that specific group. If a number isn't specified, the entire substring is returned by default.
For example, REGEXP_EXTRACT("(.*) (.*)", 'hello there', 2)
returns "there".
SPLIT¶
SPLIT(col1, delimiter)
Splits a string into an array of substrings based on a delimiter. If the delimiter is not found, then the original string is returned as the only element in the array. If the delimiter is empty, then all characters in the string are split. If either, string or delimiter, are NULL, then a NULL value is returned.
If the delimiter is found at the beginning or end of the string, or there are contiguous delimiters, then an empty space is added to the array.
SUBSTRING¶
SUBSTRING(col1, 2, 5)
SUBSTRING(str, pos, [len])
Returns a substring of str
that starts at
pos
(first character is at position 1) and
has length len
, or continues to the end of
the string.
For example, SUBSTRING("stream", 1, 4)
returns "stre".
TRIM¶
TRIM(col1)
Trim the spaces from the beginning and end of a string.
UCASE¶
UCASE(col1)
Convert a string to uppercase.
Date and Time¶
UNIX_DAT¶
UNIX_DATE()
Gets an integer representing days since epoch. The returned timestamp may differ depending on the local time of different ksqlDB Server instances.
UNIX_TIMESTAMP¶
UNIX_TIMESTAMP()
Gets the Unix timestamp in milliseconds, represented as a BIGINT. The returned timestamp may differ depending on the local time of different ksqlDB Server instances.
DATETOSTRING¶
DATETOSTRING(START_DATE, 'yyyy-MM-dd')
Converts an integer representation of a date into a string representing the
date in the given format. Single quotes in the timestamp format can be escaped
with two successive single quotes, ''
, for example: 'yyyy-MM-dd''T'''
.
The integer represents days since epoch matching the encoding used by
Connect dates.
STRINGTODATE¶
STRINGTODATE(col1, 'yyyy-MM-dd')
Converts a string representation of a date in the
given format into an integer representing days
since epoch. Single quotes in the timestamp
format can be escaped with two successive single
quotes, ''
, for example: 'yyyy-MM-dd''T'''
.
STRINGTOTIMESTAMP¶
STRINGTOTIMESTAMP(col1, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE])
Converts a string value in the given
format into the BIGINT value
that represents the millisecond timestamp. Single
quotes in the timestamp format can be escaped with
two successive single quotes, ''
, for
example: 'yyyy-MM-dd''T''HH:mm:ssX'
.
TIMEZONE is an optional parameter and it is a
java.util.TimeZone
ID format, for example: "UTC",
"America/Los_Angeles", "PDT", "Europe/London". For
more information on timestamp formats, see
DateTimeFormatter.
TIMESTAMPTOSTRING¶
TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss.SSS' [, TIMEZONE])
Converts a BIGINT millisecond timestamp value into
the string representation of the timestamp in
the given format. Single quotes in the
timestamp format can be escaped with two
successive single quotes, ''
, for example:
'yyyy-MM-dd''T''HH:mm:ssX'
.
TIMEZONE is an optional parameter and it is a
java.util.TimeZone ID format, for example: "UTC",
"America/Los_Angeles", "PDT", "Europe/London". For
more information on timestamp formats, see
DateTimeFormatter.
URL¶
URL_DECODE_PARAM¶
URL_DECODE_PARAM(col1)
Unescapes the URL-param-encoded
_ value in col1
. This is the inverse of
URL_ENCODE_PARAM
.
- Input:
'url%20encoded
- Output:
url encoded
URL_ENCODE_PARAM¶
URL_ENCODE_PARAM(col1)
Escapes the value of col1
such that it can
safely be used in URL query parameters. Note that
this is not the same as encoding a value for use
in the path portion of a URL.
- Input:
url encoded
- Output:
'url%20encoded
URL_EXTRACT_FRAGMENT¶
URL_EXTRACT_FRAGMENT(url)
Extract the fragment portion of the specified
value. Returns NULL if url
is not a valid URL
or if the fragment does not exist. Any encoded
value will be decoded.
- Input:
http://test.com#frag
, -
Output:
frag
-
Input:
http://test.com#frag%20space
, - Output:
frag space
URL_EXTRACT_HOST¶
URL_EXTRACT_HOST(url)
Extract the host-name portion of the specified
value. Returns NULL if the url
is not a valid
URI according to RFC-2396.
- Input:
http://test.com:8080/path
, - Output:
test.com
URL_EXTRACT_PARAMETER¶
URL_EXTRACT_PARAMETER(url, parameter_name)
Extract the value of the requested parameter from
the query-string of url
. Returns NULL
if the parameter is not present, has no value
specified for it in the query-string, or url
is not a valid URI. Encodes the param and decodes
the output (see examples).
To get all of the parameter values from a
URL as a single string, see URL_EXTRACT_QUERY.
- Input:
http://test.com?a%20b=c%20d
,a b
-
Output:
c d
-
Input:
http://test.com?a=foo&b=bar
,b
- Output:
bar
URL_EXTRACT_PATH¶
URL_EXTRACT_PATH(url)
Extracts the path from url
.
Returns NULL if url
is not a valid URI but
returns an empty string if the path is empty.
- Input:
http://test.com/path/to#a
- Output:
path/to
URL_EXTRACT_PORT¶
URL_EXTRACT_PORT(url)
Extract the port number from url
.
Returns NULL if url
is not a valid URI or does
not contain an explicit port number.
- Input:
http://localhost:8080/path
- Output:
8080
URL_EXTRACT_PROTOCOL¶
URL_EXTRACT_PROTOCOL(url)
Extract the protocol from url
. Returns NULL if
url
is an invalid URI or has no protocol.
- Input:
http://test.com?a=foo&b=bar
- Output:
http
URL_EXTRACT_QUERY¶
URL_EXTRACT_QUERY(url)
Extract the decoded query-string portion of
url
. Returns NULL if no query-string is
present or url
is not a valid URI.
- Input:
http://test.com?a=foo%20bar&b=baz
- Output:
a=foo bar&b=baz
Note
All ksqlDB URL functions assume URI syntax defined in
RFC 39386. For more information on the
structure of a URI, including definitions of the various components, see
Section 3 of the RFC. For encoding/decoding, the
application/x-www-form-urlencoded
convention is followed.