Functions

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

AS_NON_NULLABLE (expr)

AS_NULLABLE (expr)

CAST (dt AS int)

COALESCE (value, prevValue)

DUMP (expr)

EXISTS (field)

ISNOTNULL (expr)

ISNULL (expr)

SIZEOF (expr)

TYPEOF ()

String functions 

ABBREVIATE (expr, lengthExpr)

BASE64 (expr)

CAPITALIZE (expr)

CENTER (target,size,padExpr)

CHOP (expr)

CONCATENATE (expr1, expr2, expr3)

CONCAT (expr1, expr2, expr3)

CONTAINS (sourceExpr,targetExpr)

DECODE64 (expr)

DELETEWHITESPACE (expr)

DIGITS (expr)

DROPLEFT (expr, lengthExpr)

DROPRIGHT (expr, lengthExpr)

ENDSWITH (sourceExpr, targetExpr)

INDEXOF (expr, substringExpr)

LENGTH expr

LEN expr

LOWERCASE (strExpr)

LOWER (strExpr)

LEFTPAD (strExpr, lengthExpr, padExpr)

LPAD (strExpr, lengthExpr, padExpr)

REGEXP (strExpr, regexExpr)

REGEX (strExpr, regexExpr)

REPLACE (sourceExpr, targetExpr, replaceExpr)

REVERSE (expr)

RIGHTPAD (strExpr, lengthExpr, padExpr)

RPAD (strExpr, lengthExpr, padExpr)

STARTSWITH (exprSource, exprTarget)

STRIPACCENTS (expr)

SUBSTRING (expr, startIndexExpr, endIndexExpr)

SUBSTR (expr, startIndexExpr)

SWAPCASE (expr)

TAKELEFT (expr, lengthExpr)

TAKERIGHT (expr, lengthExpr)

TRIM (expr)

TRUNCATE (strExpr, nExpr)

UNCAPITALIZE (expr)

UPPER (strExpr)

UPPERCASE (strExpr)

UUID ()

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.

ANONYMIZE (strExpr)

MASK (strExpr)

EMAIL (emailExpr)

FIRST1 (strExpr)

FIRST2 (strExpr)

FIRST3 (strExpr)

FIRST4 (strExpr)

LAST1 (strExpr)

LAST2 (strExpr)

LAST3 (strExpr)

LAST4 (strExpr)

INITIALS (strExpr)

Numeric functions 

ABS (numExpr)

ACOS (numExpr)

ASIN (numExpr)

ATAN (numExpr)

CBRT (numExpr)

CEIL (numExpr)

COSH (numExpr)

COS (numExpr)

DEGREES (numExpr)

DISTANCE (x1,y1,x2,y2)

FLOOR (numExpr)

MAX (numExpr1,numExpr2,numExpr3)

MIN (numExpr1,numExpr2,numExpr3)

MOD (numExpr, numExpr)

NEG (numExpr)

POW (numExpr1, numExpr2)

RADIANS (numExpr)

RANDINT ()

ROUND (numExpr)

SIGN (numExpr)

SINH (numExpr)

SIN (numExpr)

SQRT (numExpr)

TANH (numExpr)

TAN (numExpr)

Date and time functions 

DATE ()

CONVERT_DATETIME (strExpr, fromPattern, toPattern)

CONVERTDATETIME (strExpr, fromPattern, toPattern)

DATETIME ()

DATE_TO_STR (strExpr, pattern)

DAY (expr)

HOUR (expr)

MINUTE (dataExpr)

MONTH_TEXT (dataExpr)

MONTH (dataExpr)

SECOND (dataExpr)

TOMORROW ()

TO_DATETIME (strExpr, pattern)

TO_DATE (strExpr, pattern)

TO_TIMESTAMP (longExpr)

TO_TIMESTAMP (strExpr, pattern)

YEAR (expr)

YESTERDAY ()

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.

AVG (numExpr)

BOTTOMK (numExpr, N)

COLLECT (expr, maxN)

COLLECT_UNIQUE (expr, maxN)

COUNT (*) AS total

FIRST (expr)

LAST (expr)

MAXK (numExpr, N)

MAXK_UNIQUE (numExpr, N)

MINK (numExpr, N)

MINK_UNIQUE (numExpr, N)

SUM (numExpr)

TOPK (numExpr, N)

Record headers functions 

HEADERASSTRING (keyStr)

HEADERASINT (keyStr)

HEADERASLONG (keyStr)

HEADERASDOUBLE (keyStr)

HEADERASFLOAT (keyStr)

HEADERKEYS ()

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>