4.2

You are viewing documentation for an older version of Lenses.io View latest documentation here

Functions

A collection of built-in SQL functions of Lenses. You can also enrich with additional user defined 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
REGEXP (strExpr, regexExpr)
Returns the matched groups otherwise null
REGEX (strExpr, regexExpr)
Alias for REGEXP
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
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.

LSQL 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 arrayno
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 numExprnono
MAXK_UNIQUE (numExpr, N)
Returns the N largest unique values of an numExprnono
MINK (numExpr, N)
Returns the N smallest values of an numExprnono
MINK_UNIQUE (numExpr, N)
Returns the N smallest unique values of an numExprnono
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 as follows:

  • 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>