Functions

A collection of built-in SQL functions of Lenses. You can also enrich with additional user defined functions and user defined aggregation functions .

FunctionDescriptionStreamingSnapshot
AS_NON_NULLABLE (expr)
Returns the provided value with its type changed from the original type to its non nullable version✓✓
AS_NULLABLE (expr)
Returns the provided value with its type changed from the original type to its nullable version✓✓
CAST (dt AS int)
Enables conversion of values from one data type to another✓✓
COALESCE (value, prevValue)
Returns the first non-null expression in the expression list✓✓
DUMP (expr)
Show the internal representation of a value✓✓
EXISTS (field)
Returns true if the given field is present false otherwise✓✓
ISNOTNULL (expr)
Returns true if the input is not null; false otherwise✓✓
ISNULL (expr)
Returns true if the input is null; false otherwise✓✓
SIZEOF (expr)
Returns the number of elements in an array✓✓
TYPEOF ()
Returns the object type of a complex expression. This can only be used when the format on the wire includes the details of the objects full nameno✓

String functions 

FunctionDescriptionStreamingSnapshot
ABBREVIATE (expr, lengthExpr)
Abbreviates the expression to the given length and appends ellipses✓✓
BASE64 (expr)
Returns the input string using base64 algorithm✓✓
CAPITALIZE (expr)
Capitalizes the first letter of the expression✓✓
CENTER (target,size,padExpr)
Centers a String in a larger String of size N✓✓
CHOP (expr)
Returns the last character from an expression of type string✓✓
CONCATENATE (expr1, expr2, expr3)
Returns the string representation of concatenating each expression in the list. Null fields are left out✓✓
CONCAT (expr1, expr2, expr3)
Alias for CONCATENATE✓✓
CONTAINS (sourceExpr,targetExpr)
Returns true if an expression contains the given substring✓✓
DECODE64 (expr)
Decodes a Base64 encrypted string✓✓
DELETEWHITESPACE (expr)
Removes all whitespace from an expression of type string✓✓
DIGITS (expr)
Retains only the digits from a string expression✓✓
DROPLEFT (expr, lengthExpr)
Removes the left most ’length’ characters from a string expression✓✓
DROPRIGHT (expr, lengthExpr)
Removes the left most ’length’ characters from a string expression✓✓
ENDSWITH (sourceExpr, targetExpr)
Returns true if an expression ends with the given substring✓✓
INDEXOF (expr, substringExpr)
Returns the index of a substring in an expression✓✓
LENGTH expr
Returns the length of a string. Calculates length using characters as defined by UTF-16✓✓
LEN expr
Alias for LENGTH✓✓
LOWERCASE (strExpr)
Returns the expression in lowercase✓✓
LOWER (strExpr)
Alias for LOWERCASE✓✓
LEFTPAD (strExpr, lengthExpr, padExpr)
Prepends the value of padExpr to the value of strExpr until the total length is lengthExpr✓✓
LPAD (strExpr, lengthExpr, padExpr)
Alias for LEFTPAD✓✓
MKSTRING (anyType)
Returns a string representation of any given value✓✓
REGEXP (strExpr, regexExpr)
Returns the matched groups otherwise null✓✓
REGEX (strExpr, regexExpr)
Alias for REGEXP✓✓
REGEX_MATCHES (strExpr, regexExpr)
Returns an array of captured groups.✓✓
REPLACE (sourceExpr, targetExpr, replaceExpr)
Returns a new string in which all occurrences of a specified String in the current string are replaced with another specified String✓✓
REVERSE (expr)
Reverses the order of the elements in the input✓✓
RIGHTPAD (strExpr, lengthExpr, padExpr)
Appends the value of padExpr to the value of strExpr until the total length is lengthExpr✓✓
RPAD (strExpr, lengthExpr, padExpr)
Alias for RIGHTPAD✓✓
STARTSWITH (exprSource, exprTarget)
Returns true if an expression starts with the given substring✓✓
STRIPACCENTS (expr)
Removes diacritics (approximately the same as accents) from an expression. The case will not be altered✓✓
SUBSTRING (expr, startIndexExpr, endIndexExpr)
Returns a new string that is a substring of this string✓✓
SUBSTR (expr, startIndexExpr)
Alias for SUBSTRING✓✓
SWAPCASE (expr)
Swaps the case of a string expression✓✓
TAKELEFT (expr, lengthExpr)
Returns the left most ’length’ characters from a string expression✓✓
TAKERIGHT (expr, lengthExpr)
Returns the right most ’length’ characters from a string expression✓✓
TRIM (expr)
Removes leading and trailing spaces from the input expression✓✓
TRUNCATE (strExpr, nExpr)
Truncates a string so that it has at most N characters✓✓
UNCAPITALIZE (expr)
Changes the first letter of each word in the expression to lowercase✓✓
UPPER (strExpr)
UPPERCASE (strExpr)
Returns the expression in uppercase✓✓
UUID ()
Returns an universally unique identifier✓✓
JSON_EXTRACT_FIRST (json_string,pattern)
Interprets ‘pattern’ as a Json path pattern and applies it to ‘json_string’, returning the first match, as a string containing valid json. Examples for the pattern parameter: “$.a”, “$[‘a’]”, “$[0]”, “$.points[?(@[‘id’]==‘i4’)].x”, “$[‘points’][?(@[‘y’] >= 3)].id”, “$.conditions[?(@ == false)]”✓✓
JSON_EXTRACT_ALL (json_string,pattern)
Interprets ‘pattern’ as a Json path pattern and applies it to ‘json_string’, returning all matches, as an array of strings containing valid json. Examples for the pattern parameter: “$.a”, “$[‘a’]”, “$[0]”, “$.points[?(@[‘id’]==‘i4’)].x”, “$[‘points’][?(@[‘y’] >= 3)].id”, “$.conditions[?(@ == false)]”✓✓

Obfuscation functions 

They allow to redact the value to avoid providing the full content details. Useful when querying data containing customers personal identifiable information like credit card.

NameDescriptionStreamingSnapshot
ANONYMIZE (strExpr)
Obfuscates the entire string input✓✓
MASK (strExpr)
Alias for ANONYMIZE✓✓
EMAIL (emailExpr)
Anonymize the value and obfuscates an email address✓✓
FIRST1 (strExpr)
Anonymize the value and only keeps the first character✓✓
FIRST2 (strExpr)
Anonymize the value and only keeps the first two characters✓✓
FIRST3 (strExpr)
Anonymize the value and only keeps the first three characters✓✓
FIRST4 (strExpr)
Anonymize the value and only keeps the first four characters✓✓
LAST1 (strExpr)
Anonymize the value and only keeps the last character✓✓
LAST2 (strExpr)
Anonymize the value and only keeps the last two characters✓✓
LAST3 (strExpr)
Anonymize the value and only keeps the last three characters✓✓
LAST4 (strExpr)
Anonymize the value and only keeps the last four characters✓✓
INITIALS (strExpr)
Anonymize the value and only keeps the initials of all the words in the input✓✓

Numeric functions 

NameDescriptionStreamingSnapshot
%The remainder operator (%) computes the remainder after dividing its first operand by its second i.e. numExpr % numExpr✓✓
/Divides one number by another (an arithmetic operator) i.e. numExpr / numExpr✓✓
-Subtracts one number from another (an arithmetic operator) i.e. numExpr - numExpr✓✓
*Multiplies one number with another (an arithmetic operator) i.e. numExpr * numExpr✓✓
+Adds one number to another (an arithmetic operator) i.e. numExpr + numExpr✓✓
- (negative)Returns the negative of the value of a numeric expression (a unary operator) i.e. -numExpr✓✓
ABS (numExpr)
Returns the absolute value of an expression that evaluates to a number type✓✓
ACOS (numExpr)
Returns the trigonometric arc cosine of an expression✓✓
ASIN (numExpr)
Returns the trigonometric arc sine of an expression✓✓
ATAN (numExpr)
Returns the trigonometric arc tangent of an expression✓✓
CBRT (numExpr)
Returns the cube root of numExpr✓✓
CEIL (numExpr)
Returns the absolute value of an expression✓✓
COSH (numExpr)
Returns the hyperbolic cosine of an expression✓✓
COS (numExpr)
Returns the trigonometric cosine of an expression✓✓
DEGREES (numExpr)
Converts the input expression to degrees✓✓
DISTANCE (x1,y1,x2,y2)
Calculates the distance between two points using the haversine method✓✓
FLOOR (numExpr)
Returns the largest value not greater than the argument✓✓
MAX (numExpr1,numExpr2,numExpr3)
Returns the maximum element from an arbitrary number of given elements✓✓
MIN (numExpr1,numExpr2,numExpr3)
Returns the minimum element from an arbitrary number of given elements✓✓
MOD (numExpr, numExpr)
Alias for %✓✓
NEG (numExpr)
Returns the negative value of an expression it has to evaluate to a number type✓✓
POW (numExpr1, numExpr2)
Returns numExp1 raised to the numExp2 power✓✓
RADIANS (numExpr)
Converts the input expression to radians✓✓
RANDINT ()
Returns a random integer✓✓
ROUND (numExpr)
Returns the closest integer of an expression, with ties rounding towards positive infinity✓✓
SIGN (numExpr)
Returns +1 if a value is positive or -1 if a value is negative✓✓
SINH (numExpr)
Returns the hyperbolic sine of an expression✓✓
SIN (numExpr)
Returns the trigonometric sine of an expression✓✓
SQRT (numExpr)
Returns the square root of numExpr✓✓
TANH (numExpr)
Returns the hyperbolic tangent of an expression✓✓
TAN (numExpr)
Returns the trigonometric tangent of an expression✓✓

Date and time functions 

NameDescriptionStreamingSnapshot
TIME_MILLIS(millis)
Builds a time-millis value from a long or int value✓✓
TIME_MICROS(micros)
Builds a time-micros value from a long or int value✓✓
DATE(days)
Builds a local date value from a long or int value. This function can also be used with no parameters (see below)✓✓
TIMESTAMP_MILLIS(millis)
Builds a timestamp-millis value from a long or int value✓✓
TIMESTAMP_MICROS(micros)
Builds a timestamp-micros value from a long or int value✓✓
PARSE_TIME_MILLIS(millis, pattern)
Builds a time-millis value given a time string representation and a time pattern✓✓
PARSE_TIME_MICROS(micros, pattern)
Builds a time-micros value given a time string representation and a time pattern✓✓
PARSE_DATE(string, pattern)
Builds a date value given a date string representation and a date pattern✓✓
PARSE_TIMESTAMP_MILLIS(string, input_pattern)
Builds a timestamp-millis value given a datetime string representation and a date time pattern✓✓
PARSE_TIMESTAMP_MICROS(string, input_pattern)
Builds a timestamp-micros value given a datetime string representation and a date time pattern✓✓
EXTRACT_TIME(timestamp)
Extracts the time portion of a timestamp-micros or timestamp-millis returning a time-millis or time-micros value depending on the timestamp precision✓✓
EXTRACT_DATE(timestamp)
Extracts the dat portion of a timestamp-micros or timestamp-millis returning a date value✓✓
TIMESTAMP(date, time, zoneStr)
Returns a timestamp for a given date and time at a specific zone id✓✓
FORMAT_TIMESTAMP(timestamp,output_pattern)
Returns a string representation of a timestamp value according to a given a pattern✓✓
FORMAT_DATE(date,output_pattern)
Returns a string representation of a date value according to a given a pattern✓✓
FORMAT_TIME(time, output_pattern)
Returns a string representation of a time value according to a given a pattern✓✓
DATE ()
Provides the current ISO date value✓✓
CONVERT_DATETIME (strExpr, fromPattern, toPattern)
Converts the string format of a date [and time] to another using the pattern provided✓✓
CONVERTDATETIME (strExpr, fromPattern, toPattern)
Alias for CONVERT_DATETIME✓✓
DATETIME ()
Provides the current ISO date and time✓✓
DATE_TO_STR (strExpr, pattern)
Converts a date time value (epoch in milliseconds) to a string using the pattern provided✓✓
DAY (expr)
Extracts the day component of an expression that is of type timestamp✓✓
HOUR (expr)
Extracts the hour component of an expression that is of type timestamp✓✓
MINUTE (dataExpr)
Extracts the minute component of an expression that is of type timestamp✓✓
MONTH_TEXT (dataExpr)
Returns the month name✓✓
MONTH (dataExpr)
Extracts the month component of an expression that is of type timestamp✓✓
SECOND (dataExpr)
Extracts the second component of an expression that is of type timestamp✓✓
TOMORROW ()
Returns the current date time plus 1 day✓✓
TO_DATETIME (strExpr, pattern)
Converts a string representation of a datetime into epoch value using the pattern provided✓✓
TO_DATE (strExpr, pattern)
Converts a string representation of a date into epoch value using the pattern provided✓✓
TO_TIMESTAMP (longExpr)
Converts a long (epoch) to a date and time type✓✓
TO_TIMESTAMP (strExpr, pattern)
Converts a string using a pattern to a date and time type✓✓
YEAR (expr)
Extracts the year component of an expression that is of type timestamp✓✓
YESTERDAY ()
Returns the current date time minus 1 day✓✓

Array functions 

NameDescriptionStreamingSnapshot
ELEMENT_OF (array, index)
Return the element of array at index✓✓
FLATTEN (array)
Flatten an array of arrays into an array✓✓
IN_ARRAY (element, array)
Check if element as an element of array✓✓
REPEAT (element, n)
Build an array repeating element n times✓✓
ZIP (array1, field1, array2, field2, …)
Zip two or more arrays into a single one.
Example: ZIP([1, 2], 'x', [3, 4, 5], 'y') will be evaluated to [{ x: 1, y: 3 }, { x: 2, y: 4 }]		✓✓
ZIP_ALL (array1, field1, array2, field2, …)
Zip two or more arrays into a single one, returning nulls when an array is not long enough.
Example: ZIP_ALL([1, 2], 'x', [3, 4, 5], 'y') will be evaluated to [{ x: 1, y: 3 }, { x: 2, y: 4 }, { x: null, y: 5 }]		✓✓

Aggregated functions 

An aggregate function performs a calculation on a set of values, and returns a single value. They cannot be used without a GROUP BY statement.

Lenses SQL engine Snapshot and Streaming modes have different semantics. Thus, some functions which are available for the Streaming and do not apply to Snapshot.

NameDescription
Streaming (stateless)
Streaming (stateful)
Snapshot
AVG (numExpr)
Returns the average of the values in a group. It ignores null value. It can be used with numeric input only✓✓✓
BOTTOMK (numExpr, N)
Returns the last K lowest ranked values. The ranking is based on how many times a value has been seennono✓
COLLECT (expr, maxN)
Returns an array in which each value in the input set is assigned to an element of the array✓✓✓
COLLECT_UNIQUE (expr, maxN)
Returns an array of unique values in which each value in the input set is assigned to an element of the array✓no✓
COUNT (*) AS total
Returns the number of records returned by a query or the records in a group as a result of a GROUP BY statement✓✓✓
FIRST (expr)
Returns the first item seen in a groupnono✓
LAST (expr)
Returns the last item seen in a groupnono✓
MAXK (numExpr, N)
Returns the N largest values of an numExpr✓nono
MAXK_UNIQUE (numExpr, N)
Returns the N largest unique values of an numExpr✓nono
MINK (numExpr, N)
Returns the N smallest values of an numExpr✓nono
MINK_UNIQUE (numExpr, N)
Returns the N smallest unique values of an numExpr✓nono
SUM (numExpr)
Returns the sum of all the values, in the expression. It can be used with numeric input only. Null values are ignored✓✓✓
TOPK (numExpr, N)
Returns the K highest ranked values. The ranking is based on how many times a value has been seennono✓

Record headers functions 

NameDescriptionStreamingSnapshot
HEADERASSTRING (keyStr)
Returns the value of the record header key as a STRING valueno✓
HEADERASINT (keyStr)
Returns the value of the record header key as an INT valueno✓
HEADERASLONG (keyStr)
Returns the value of the record header key as a LONG valueno✓
HEADERASDOUBLE (keyStr)
Returns the value of the record header key as a DOUBLE valueno✓
HEADERASFLOAT (keyStr)
Returns the value of the record header key as a FLOAT valueno✓
HEADERKEYS ()
Returns all the header keys for the current recordno✓

Record offset functions 

NameDescriptionStreamingSnapshot
LAST_OFFSET ()
Returns the last offset of the current record’s partitionno✓

Date math 

Lenses offers direct support for manipulating dates, times, and timestamps. This functionality is named Date Math. The expressions can be used in any SQL statement where a general-purpose expression is allowed - for example as part of a where filter, in a group by clause, a select projection, and so on.

Every Date Math expression starts with a base date or time followed by the addition or subtraction of one or more durations. Most of the time, the base would come from a field, but sometimes the base date may need to be modified - for example extracting the minutes component only from a time value. In addition to this, the base can be generated using a function unrelated to any field in the the data record. Check Date and Time functions

Consider a scenario where it is required to compute a month from the value of subscription_date field. A naive implementation might be to add (1000 * 60 * 60 * 24 * 31) milliseconds to the subscription_date. But what is a month - a month can have 28 days, 30 days or 31 days (in the Gregorian calendar). In daylight savings timezones, a month may include an extra hour or might miss an hour. It is hard to handle the uneven nature of date units in a query.

Syntax 

Every Date Math expression starts with a base date or time followed by the addition or subtraction of one or more durations.

The base date or time (from here onward) is derived from a field in a table or a function such as now() or yesterday() that generates datetime values.

Duration operations translate the base date by either adding or subtracting a duration defined using the special Date Math shorthand - this is where the math part of the Date Math name comes from.

The shorthand syntax is a unit value followed by a unit symbol. The symbols are:

  • y (year)
  • M (month)
  • w (week)
  • d (day)
  • h (hour)
  • m (minute)
  • s (second)

For example, 60s would constitute 60 seconds, which is equivalent to 1m, and 48h would be equivalent to 2d.

A full Date Math expression would look like the following examples:

SELECT [STREAM/TABLE]
    now() + 1d as tomorrow
FROM <source>


SELECT [STREAM/TABLE]
    customers.age + '1y' as next_year
FROM <source>

SELECT [STREAM/TABLE]
    orders.delivery.date + '1d' - '1h' as in23Hours
FROM <source>

    //adding a month to a subscription date
SELECT [STREAM/TABLE]
    subscription_date + '1M' AS renewal
FROM <subscriptions>

    //adding a month and a day to a subscription date
SELECT [STREAM/TABLE]
    subscription_date + '1M' + '1d' AS renewal
FROM <subscriptions>