1 of 100

Functions

This page describes how to use functions in Lenses SQL Processors.

Aggregate

This section describes how to use AGGREGATE functions in Lenses SQL.

AVG

This page describes the AVG function in Lenses SQL.

AVG(numExpr)

Returns the average of the values in a group. It ignores the null value. It can be used with numeric input only.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

CREATE TABLE order-events 
     (_key string, total int, orderId int) 
FORMAT 
     (string, json) 
PROPERTIES 
    (partitions=1, compacted=false); 

INSERT INTO order-events  
    (_key, name, total, orderId) 
VALUES 
   ("user1",23423,23423

sample code:

USE `kafka`;
SELECT AVG(total) 
FROM orders-events
LIMIT 1000;

Output:

{
  "value": {
    "AVG": "54.540900000000000000"
  }
}

BOTTOMK

This page describes the BOTTOMK function in Lenses SQL.

BOTTOMK(numExpr, N)

Returns the last K lowest ranked values. The ranking is based on how many times a value has been seen.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT BOTTOMK(total) 
FROM orders-events

Output:

{
  "value": {
    "BOTTOMK": [
      10.14
    ]
  }
}

COLLECT

This page describe the COLLECT function in Lenses SQL.

COLLECT(expr, maxN)

Returns an array in which each value in the input set is assigned to an element of the array.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT COLLECT(total, 5) 
FROM orders-events

Output:

{
  "value": {
    "COLLECT": [
      43.63,
      16.93,
      80.47,
      56.86,
      29.12
    ]
  }
}

COLLECT_UNIQUE

This page describes the COLLECT_UNIQUE function in Lenses SQL.

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.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT COLLECT_UNIQUE(total, 5) 
FROM orders-events

Output:

{
  "value": {
    "COLLECT_UNIQUE": [
      56.8,
      25.75,
      88.97,
      19.47,
      99.26
    ]
  }
}

COUNT

This page describes the COUNT function in Lenses SQL.

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.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT COUNT(id) 
FROM orders-events

Output:

{
  "value": {
    "COUNT": 1000
  }
}

FIRST

This page describes the FIRST function in Lenses SQL.

FIRST(expr)

Returns the first item seen in a group.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT FIRST(id) 
FROM orders-events

Output:

{
  "value": {
    "FIRST": "d4e60554-260d-4f14-873a-9c0352ad9387"
  }
}

LAST

This page describes the LAST function in Lenses SQL.

LAST(expr)

Returns the last item seen in a group.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT LAST(id) 
FROM orders-events

Output:

{
  "value": {
    "LAST": "9ce6a2c1-104f-49d8-818a-7fb632bb2f29"
  }
}

MAXK

This page describes the MAXK function in Lenses SQL.

MAXK(numExpr, N)

Returns the N largest values of a numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT MAXK(total) 
FROM orders-events

Output:

{
  "value": {
    "MAXK": [
      99.92
    ]
  }
}

MAXK_UNIQUE

This page describes the MAXK_UNIQUE function in Lenses SQL.

MAXK_UNIQUE(numExpr, N)

Returns the N smallest unique values of a numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT MAXK_UNIQUE(total) 
FROM orders-events

Output:

{
  "value": {
    "MAXK_UNIQUE": [
      99.97
    ]
  }
}

MINK

This page describes the MINK function in Lenses SQL.

MINK(numExpr, N)

Returns the N smallest values of an numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT MINK(total) 
FROM orders-events

Output:

{
  "value": {
    "MINK": [
      10.14
    ]
  }
}

MINK_UNIQUE

This page describes the MINK_UNIQUE function in Lenses SQL.

MINK_UNIQUE(numExpr, N)

Returns the N smallest unique values of a numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT MINK_UNIQUE(total) 
FROM orders-events

Output:

{
  "value": {
    "MINK_UNIQUE": [
      10.14
    ]
  }
}

SUM

This page describes the SUM function in Lenses SQL.

SUM(numExpr)

Returns the sum of all the values, in the expression. It can be used with numeric input only. Null values are ignored.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT SUM(total) 
FROM orders-events

Output:

{
  "value": {
    "SUM": "163053.270000000000000000"
  }
}

TOPK

This page describes the TOPK function in Lenses SQL.

TOPK(numExpr, N)

Returns the K highest ranked values. The ranking is based on how many times a value has been seen.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT TOPK(id) 
FROM orders-events

Output:

{
  "value": {
    "TOPK": [
      "ffd7c548-06bb-40e9-8aae-558be65c1e70"
    ]
  }
}

Array

This page describes how to use ARRAY functions in Lenses SQL Processors.

ELEMENT_OF

This page describes the ELEMENT_OF function in Lenses SQL.

ELEMENT_OF(array, index)

Return the element of array at index.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT ELEMENT_OF(products, 2) 
FROM orders-events
LIMIT 1;

Output:

{
  "value": {
    "ELEMENT_OF": {
      "product_id": "d3f085e0-c049-4e8b-9dd9-ea3ae124720b",
      "quantity": 1
    }
  }
}

FLATTEN

This page describes the FLATTEN function in Lenses SQL.

FLATTEN(array)

Flatten an array of arrays into an array.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    FLATTEN(_value), 
    _value 
FROM products-array-test-2
LIMIT 1;

Output:

{
  "value": {
    "FLATTEN": [
      "128279ca-5f8d-44ef-b340-b8054a6611ec",
      6,
      "5ae063ba-0c94-4b9c-ad40-2da08ad8bfd2",
      8,
      "41bb7268-fa7c-426f-b549-4bb215579280",
      9,
      "35df5368-8b77-4081-9aab-96bc8de16c23",
      10,
      "da6d69ce-bbd9-4e56-861d-5018f2d9b23a",
      10,
      "c9725591-e6b6-4928-a6e8-7e37707965ec",
      2,
      "ac821f76-2ff0-4d0c-9540-b775f0602332",
      3,
      "f1f98a2c-e66e-44f3-bba1-58169a425862",
      1,
      "78ea76f1-7381-4b38-a25f-ee487bae9749",
      10,
      "5b84f92d-d8b8-4532-b452-639ab44ae017",
      5
    ]
  }
}
{
  "value": [
    [
      "128279ca-5f8d-44ef-b340-b8054a6611ec",
      6
    ],
    [
      "5ae063ba-0c94-4b9c-ad40-2da08ad8bfd2",
      8
    ],
    [
      "41bb7268-fa7c-426f-b549-4bb215579280",
      9
    ],
    [
      "35df5368-8b77-4081-9aab-96bc8de16c23",
      10
    ],
    [
      "da6d69ce-bbd9-4e56-861d-5018f2d9b23a",
      10
    ],
    [
      "c9725591-e6b6-4928-a6e8-7e37707965ec",
      2
    ],
    [
      "ac821f76-2ff0-4d0c-9540-b775f0602332",
      3
    ],
    [
      "f1f98a2c-e66e-44f3-bba1-58169a425862",
      1
    ],
    [
      "78ea76f1-7381-4b38-a25f-ee487bae9749",
      10
    ],
    [
      "5b84f92d-d8b8-4532-b452-639ab44ae017",
      5
    ]
  ]
}

IN_ARRAY

This page describes the IN_ARRAY function in Lenses SQL.

IN_ARRAY(element, array)

Check if element is an element of array.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    IN_ARRAY('5ec69520-258f-4669-915f-9d842f96fa14', products), 
    products 
FROM orders-test-4
LIMIT 10;

Output:

{
  "value": {
    "IN_ARRAY": true,
    "products": [
      "5ec69520-258f-4669-915f-9d842f96fa14",
      "1946c4ac-ea7a-48c5-870c-1800c3982a14"
    ]
  }
}
{
  "value": {
    "IN_ARRAY": false,
    "products": [
      "49c537d5-078f-4bf8-a74d-49aa10e39c31",
      "c1eb9291-f292-4079-8957-88cce18c2739"
    ]
  }
}

REPEAT

This page describes the REPEAT function in Lenses SQL.

REPEAT(element, n)

Build an array repeating element n times.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    REPEAT(products, 5), 
    products 
FROM orders-topic
LIMIT 1;

Output:

{
  "value": {
    "REPEAT": [
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ],
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ],
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ],
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ],
      [
        {
          "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
          "quantity": 3
        }
      ]
    ],
    "products": [
      {
        "product_id": "4e774082-0c03-4751-b4dc-c3799da54f48",
        "quantity": 3
      }
    ]
  }
}

SIZEOF

This page describes the SIZEOF function in Lenses SQL.

SIZEOF(expr)

Returns the number of elements in an array.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
  SIZEOF(products) AS qty_products_array, 
  products 
FROM orders-events
LIMIT 5;

Output:

{
  "value": {
    "qty_products_array": 1,
    "products": [
      {
        "product_id": "0e719f35-e7fb-4717-9151-b4f3cd2017a2",
        "quantity": 5
      }
    ]
  }
}
{
  "value": {
    "qty_products_array": 5,
    "products": [
      {
        "product_id": "0847e631-150b-45e3-a2cd-c31b11afef3c",
        "quantity": 8
      },
      {
        "product_id": "707b30ad-a506-4583-acc8-95856d3bb88e",
        "quantity": 1
      },
      {
        "product_id": "efdfe7e2-fc01-4018-8d7f-ac0a130d0292",
        "quantity": 6
      },
      {
        "product_id": "2f9c5899-3df4-4090-ba8c-3b97f34e3afe",
        "quantity": 8
      },
      {
        "product_id": "e1c71c52-fa6f-47d7-a488-1eadc8a04e5d",
        "quantity": 1
      }
    ]
  }
}
{
  "value": {
    "qty_products_array": 2,
    "products": [
      {
        "product_id": "01fb793f-baad-44b2-8a52-55cdfbe062f1",
        "quantity": 8
      },
      {
        "product_id": "29a2914c-7f81-4c06-b3a4-a42124d5fdf3",
        "quantity": 10
      }
    ]
  }
}
{
  "value": {
    "qty_products_array": 5,
    "products": [
      {
        "product_id": "77ea0afb-3184-4a1a-bc08-d1cfdab445ad",
        "quantity": 1
      },
      {
        "product_id": "798e2412-ebc0-4549-a935-632f1dccc48c",
        "quantity": 7
      },
      {
        "product_id": "768e6a17-b5d9-4488-b4b4-62e54ea9e818",
        "quantity": 8
      },
      {
        "product_id": "3876af98-2b6c-4690-a3c2-ab7a9c4e2565",
        "quantity": 3
      },
      {
        "product_id": "23c12a75-7a16-46ef-a253-21aae8b705ba",
        "quantity": 2
      }
    ]
  }
}
{
  "value": {
    "qty_products_array": 4,
    "products": [
      {
        "product_id": "acd8758a-36b0-429c-9246-3e5cf9dc0545",
        "quantity": 6
      },
      {
        "product_id": "7262d1ce-f467-47f5-b835-061eb82593ca",
        "quantity": 1
      },
      {
        "product_id": "0751080a-d707-4c76-90fd-17b8724f37a2",
        "quantity": 3
      },
      {
        "product_id": "96630315-46d2-489a-9e2c-bb5ece7fab38",
        "quantity": 4
      }
    ]
  }
}

ZIP_ALL

This page describes the ZIP_ALL function in Lenses SQL.

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 }]

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    ZIP_ALL([1, 2], 'x', [3, 4, 5], 'y') 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "ZIP_ALL": [
      {
        "x": 1,
        "y": 3
      },
      {
        "x": 2,
        "y": 4
      },
      {
        "x": null,
        "y": 5
      }
    ]
  }
}

ZIP

This page describes the ZIP function in Lenses SQL.

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 }]

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    ZIP([1, 2], 'x', [3, 4, 5], 'y') 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "ZIP": [
      {
        "x": 1,
        "y": 3
      },
      {
        "x": 2,
        "y": 4
      }
    ]
  }
}

Conditions

This page describes how to use Conditions in Lenses SQL Processors.

EXISTS

EXISTS (field)

Returns true if the given field is present false otherwise.

Available in:

Processors

SQL Studio

✓

Returns true if the given field is present false otherwise.

Sample code:

USE `kafka`;
SELECT `EXISTS`(id) 
FROM users-events
LIMIT 1;

Output:

{
  "value": {
    "EXISTS": true
  }
}

Conversion

This page describes how to use conversion or CAST functions in Lenses SQL Processors.

CAST

CAST (dt AS int)

Enables conversion of values from one data type to another.

Available in:

Processors

SQL Studio

✓

Sample code

USE `kafka`;
SELECT 
    CAST(price as INT),
    price 
FROM products-events
LIMIT 1;

Output:

{
  "value": {
    "alias": 2, # new value
    "price": 2.46 # old value
  }
}

Date & Time

This page describes how to use date and time functions in Lenses SQL Processors.

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.

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)

CONVERT_DATETIME

This page describes the CONVERT_DATETIME function in Lenses SQL.

CONVERT_DATETIME (strExpr, fromPattern, toPattern)

Converts the string format of a date [and time] to another using the pattern provided.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
 CONVERT_DATETIME(tpep_pickup_datetime, 'yyyy-MM-dd HH:mm:ss', 'dd-MM-yyyy HH:mm'), 
 tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "CONVERT_DATETIME": "01-01-2016 00:00",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

DATE

This page describes the DATE function in Lenses SQL.

DATE(days)

Builds a local date value from a long or int value. This function can also be used with no parameters to return the current ISO date.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT DATE() 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "DATE": "2023-11-29T00:00:00Z"
  }
}

DATETIME

This page describes the DATETIME function in Lenses SQL.

DATETIME ()

Provides the current ISO date and time.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT DATETIME() 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "DATETIME": "2023-11-29T16:30:41.146Z"
  }
}

EXTRACT_TIME

This page describes the EXTRACT_TIME function in Lenses SQL.

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.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    EXTRACT_TIME(_meta.timestamp), 
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "EXTRACT_TIME": "20:28:45.41",
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

EXTRACT_DATE

This page describes the EXTRACT_DATE function in Lenses SQL.

EXTRACT_DATE(timestamp)

Extracts the date portion of a timestamp-micros or timestamp-millis returning a date value.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    EXTRACT_DATE(_meta.timestamp), 
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "EXTRACT_DATE": "2023-11-28",
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

FORMAT_DATE

This page describes the FORMAT_DATE function in Lenses SQL.

FORMAT_DATE(date,output_pattern)

Returns a string representation of a date value according to a given pattern.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    FORMAT_DATE(_meta.timestamp,'dd-MM-yyyy HH:mm'), 
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "FORMAT_DATE": "28-11-2023 20:28",
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

FORMAT_TIME

This page describes the FORMAT_TIME function in Lenses SQL.

FORMAT_TIME(time, output_pattern)

Returns a string representation of a time value according to a given pattern.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    FORMAT_TIME(_meta.timestamp,'K:mm a, z'), 
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "FORMAT_TIME": "8:28 PM, UTC",
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

FORMAT_TIMESTAMP

This page describes the FORMAT_TIMESTAMP function in Lenses SQL.

FORMAT_TIMESTAMP(timestamp,output_pattern)

Returns a string representation of a timestamp value according to a given pattern.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    FORMAT_TIMESTAMP(_meta.timestamp,'yyyy-MM-dd HH:mm:ss'), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "FORMAT_TIMESTAMP": "2023-11-28 20:28:45",
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

HOUR

This page describes the HOUR function in Lenses SQL.

HOUR (expr)

Extracts the hour component of an expression that is of type timestamp.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    HOUR(_meta.timestamp), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "HOUR": 20,
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

MONTH_TEXT

This page describes the MONTH_TEXT function in Lenses SQL.

MONTH_TEXT(dataExpr)

Returns the month name.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    MONTH_TEXT(_meta.timestamp), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "MONTH_TEXT": "November",
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

MINUTE

This page describes the MINUTE function in Lenses SQL.

MINUTE(dataExpr)

Extracts the minute component of an expression that is of type timestamp.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    MINUTE(_meta.timestamp), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "MINUTE": 28,
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

MONTH

This page describes the MONTH function in Lenses SQL.

MONTH(dataExpr)

Builds a timestamp-millis value from a long or int value.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    MONTH(_meta.timestamp), 
    _meta.timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "MONTH": 11,
    "timestamp": "2023-11-28T20:28:45.337Z"
  }
}

PARSE_DATE

This page describes the PARSE_DATE function in Lenses SQL.

PARSE_DATE(string, pattern)

Builds a date value given a date string representation and a date pattern.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    PARSE_DATE(tpep_pickup_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "PARSE_DATE": "2016-01-01",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

PARSE_TIME_MILLIS

This page describes the PARSE_TIME_MILLIS function in Lenses SQL.

PARSE_TIME_MILLIS(millis, pattern)

Builds a time-millis value given a time string representation and a time pattern.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    PARSE_TIME_MILLIS(tpep_pickup_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "PARSE_TIME_MILLIS": "00:00:02",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

PARSE_TIME_MICROS

This page describes the PARSE_TIME_MICROS function in Lenses SQL.

PARSE_TIME_MICROS(micros, pattern)

Builds a time-micros value given a time string representation and a time pattern.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    PARSE_TIME_MICROS(tpep_pickup_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "PARSE_TIME_MICROS": "00:00:02",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

PARSE_TIMESTAMP_MILLIS

This page describes the PARSE_TIMESTAMP_MILLIS function in Lenses SQL.

PARSE_TIMESTAMP_MILLIS(string, input_pattern)

Builds a timestamp-millis value given a datetime string representation and a date time pattern.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    PARSE_TIMESTAMP_MILLIS(tpep_pickup_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "PARSE_TIMESTAMP_MILLIS": "2016-01-01T00:00:02Z",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

PARSE_TIMESTAMP_MICROS

This page describes the PARSE_TIMESTAMP_MICROS function in Lenses SQL.

PARSE_TIMESTAMP_MICROS(string, input_pattern)

Builds a timestamp-micros value given a datetime string representation and a date time pattern.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    PARSE_TIMESTAMP_MICROS(tpep_pickup_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "PARSE_TIMESTAMP_MICROS": "2016-01-01T00:00:02Z",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

SECOND

This page describes the SECOND function in Lenses SQL.

SECOND(dataExpr)

Extracts the second component of an expression that is of type timestamp.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    SECOND(_meta.timestamp), 
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "SECOND": 45,
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

TIMESTAMP

This page describes the TIMESTAMP function in Lenses SQL.

TIMESTAMP(date, time, zoneStr)

Returns a timestamp for a given date and time at a specific zone id.

Available in:

Processors

SQL Studio

✓

Sample code:

Output:

TIME_MICROS

This page describes the TIME_MICROS function in Lenses SQL.

TIME_MICROS(micros)

Builds a time-micros value from a long or int value.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TIME_MICROS(Timestamp) 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "TIME_MICROS": "20:47:13.67939",
    "Timestamp": "1503158676433679390"
  }
}

TIMESTAMP_MICROS

This page describes the TIMESTAMP_MICROS function in Lenses SQL.

TIMESTAMP_MICROS(micros)

Builds a timestamp-micros value from a long or int value.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TIMESTAMP_MICROS(Timestamp), 
    Timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "TIMESTAMP_MICROS": "+49603-03-15T20:47:13.67939Z",
    "Timestamp": "1503158676433679390"
  }
}

TIME_MILLIS

This page describes the TIME_MILLIS function in Lenses SQL.

TIME_MILLIS(millis)

Builds a time-millis value from a long or int value.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TIME_MILLIS(Timestamp), 
    Timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "TIME_MILLIS": "03:07:59.39",
    "Timestamp": "1503158676433679390"
  }
}

TIMESTAMP_MILLIS

This page describes the TIMESTAMP_MILLIS function in Lenses SQL.

TIMESTAMP_MILLIS(millis)

Builds a timestamp-millis value from a long or int value.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TIMESTAMP_MILLIS(Timestamp), 
    Timestamp 
FROM sea-vessel-position-reports
LIMIT 1;

Output:

{
  "value": {
    "TIMESTAMP_MILLIS": "+47635172-03-26T03:07:59.39Z",
    "Timestamp": "1503158676433679390"
  }
}

TO_DATE

This page describes the TO_DATE function in Lenses SQL.

TO_DATE(strExpr, pattern)

Converts a string representation of a date into epoch value using the pattern provided.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TO_DATE(tpep_dropoff_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_dropoff_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "TO_DATE": "2016-01-01T00:00:00Z",
    "tpep_dropoff_datetime": "2016-01-01 00:11:14"
  }
}

TO_DATETIME

This page describes the TO_DATETIME function in Lenses SQL.

TO_DATETIME(strExpr, pattern)

Converts a string representation of a datetime into epoch value using the pattern provided.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TO_DATETIME(tpep_dropoff_datetime, 'yyyy-MM-dd HH:mm:ss'), 
    tpep_dropoff_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "TO_DATETIME": "2016-01-01T00:11:14Z",
    "tpep_dropoff_datetime": "2016-01-01 00:11:14"
  }
}

TOMORROW

This page describes the TOMORROW function in Lenses SQL.

TOMORROW()

Returns the current date time plus 1 day.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT TOMORROW() 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "TOMORROW": "2023-11-30T18:49:25.182Z"
  }
}

TO_TIMESTAMP

This page describes the TO_TIMESTAMP function in Lenses SQL.

TO_TIMESTAMP(longExpr)

Converts a string representation of a date into epoch value using the pattern provided.

TO_TIMESTAMP (strExpr, pattern)

Converts a string using a pattern to a date and time type.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TO_TIMESTAMP(tpep_pickup_datetime),
    tpep_pickup_datetime 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "TO_TIMESTAMP": "2016-01-01T00:00:02Z",
    "tpep_pickup_datetime": "2016-01-01 00:00:02"
  }
}

YEAR

This page describes the YEAR function in Lenses SQL.

YEAR(expr)

Extracts the year component of an expression that is of type timestamp.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    YEAR(_meta.timestamp),
    _meta.timestamp 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "YEAR": 2023,
    "timestamp": "2023-11-28T20:28:45.41Z"
  }
}

YESTERDAY

This page describes the YESTERDAY function in Lenses SQL.

YESTERDAY()

Returns the current date time minus 1 day.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT YESTERDAY() 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "YESTERDAY": "2023-11-28T18:57:13.27Z"
  }
}

Headers

This page describes how to use HEADER functions in Lenses SQL Processors.

HEADERASSTRING

This page describes the HEADERASSTRING function in Lenses SQL.

HEADERASSTRING(keyStr)

Returns the value of the record header key as a STRING value.

Available in:

Processors

SQL Studio

✓

HEADERASINT

This page describes the HEADERASINT function in Lenses SQL.

HEADERASINT(keyStr)

Returns the value of the record header key as an INT value.

Available in:

Processors

SQL Studio

✓

HEADERASLONG

This page describes the HEADERASLONG function in Lenses SQL.

HEADERASLONG(keyStr)

Returns the value of the record header key as a LONG value.

Available in:

Processors

SQL Studio

✓

HEADERASDOUBLE

This page describes the HEADERASDOUBLE function in Lenses SQL.

HEADERASDOUBLE(keyStr)

Returns the value of the record header key as a DOUBLE value.

Available in:

Processors

SQL Studio

✓

HEADERASFLOAT

This page describes the HEADERASFLOAT function in Lenses SQL.

HEADERASFLOAT(keyStr)

Returns the value of the record header key as a FLOAT value.

Available in:

Processors

SQL Studio

✓

HEADERKEYS

This page describes the HEADERKEYS function in Lenses SQL.

HEADERKEYS()

Returns all the header keys for the current record.

Available in:

Processors

SQL Studio

✓

JSON

This page describes the JSON functions in Lenses SQL.

JSON_EXTRACT_FIRST

This page describes the JSON_EXTRACT_FIRST function in Lenses SQL.

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)]”

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    JSON_EXTRACT_FIRST(time_details, '$.hour'), 
    time_details 
FROM credit-card-transactions
LIMIT 2;

Output:

{
  "value": {
    "JSON_EXTRACT_FIRST": "1",
    "time_details": "{\"hour\": 1, \"minute\": 17, \"second\": 15, \"millisecond\": 36}"
  }
}
{
  "value": {
    "JSON_EXTRACT_FIRST": "16",
    "time_details": "{\"hour\": 16, \"minute\": 23, \"second\": 2, \"millisecond\": 255}"
  }
}

JSON_EXTRACT_ALL

This page describes the JSON_EXTRACT_ALL function in Lenses SQL.

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)]”

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    JSON_EXTRACT_ALL(time_details, '$.*'), 
    time_details 
FROM credit-card-transactions
LIMIT 2;

Output:

{
  "value": {
    "JSON_EXTRACT_ALL": [
      "1",
      "17",
      "15",
      "36"
    ],
    "time_details": "{\"hour\": 1, \"minute\": 17, \"second\": 15, \"millisecond\": 36}"
  }
}
{
  "value": {
    "JSON_EXTRACT_ALL": [
      "16",
      "23",
      "2",
      "255"
    ],
    "time_details": "{\"hour\": 16, \"minute\": 23, \"second\": 2, \"millisecond\": 255}"
  }
}

Numeric

This page describes how to use numeric functions in Lenses SQL Processors.

%

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

This page describes the ABS function in Lenses SQL.

ABS(numExpr)

Returns the absolute value of an expression that evaluates to a number type.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    ACOS(total/100.0) AS arccosine_total_ratio, 
    total 
FROM orders-events
LIMIT 1;

Output:

{
  "value": {
    "arccosine_total_ratio": "1.119313791750037",
    "total": 43.63
  }
}

ACOS

This page describes the ACOS function in Lenses SQL.

ACOS(numExpr)

Returns the trigonometric arc cosine of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT ACOS(extra) 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "ACOS": "1.0471975511965979"
  }
}

ASIN

This page describes the ASIN function in Lenses SQL.

ASIN(numExpr)

Returns the trigonometric arc sine of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT ASIN(extra) 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "ASIN": "0.5235987755982989"
  }
}

ATAN

This page describes the ATAN function in Lenses SQL.

ATAN(numExpr)

Returns the trigonometric arc tangent of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT ATAN(pickup_longitude) 
FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "ATAN": "-1.5572801259337674"
  }
}

CBRT

This page describes the CBRT function in Lenses SQL.

CBRT(numExpr)

Returns the cube root of numExpr.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    CBRT(integer),
    integer 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "CBRT": "25.483906287370523",
    "integer": 16550
  }
}

CEIL

This page describes the CEIL function in Lenses SQL.

CEIL(numExpr)

Returns the absolute value of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    CEIL(float),
    float 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "CEIL": 96,
    "float": 95.95
  }
}

COSH

This page describes the COSH function in Lenses SQL.

COSH(numExpr)

Returns the hyperbolic cosine of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    COSH(float),
    float 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "COSH": "2.3416691959782676E41",
    "float": 95.95
  }
}

COS

This page describes the COS function in Lenses SQL.

COS(numExpr)

Returns the trigonometric cosine of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    COS(float),
    float 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "COS": "-0.13104605979102568",
    "float": 95.95
  }
}

DEGREES

This page describes the DEGRESS function in Lenses SQL.

DEGREES(numExpr)

Converts the input expression to degrees.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    DEGREES(integer),
    integer 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "DEGREES": "948245.1509415124",
    "integer": 16550
  }
}

DISTANCE

This page describes the DISTANCE function in Lenses SQL.

DISTANCE(x1,y1,x2,y2)

Calculates the distance between two points using the haversine method.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    DISTANCE(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) 
    FROM nyc-yellow-taxi-trip
LIMIT 1;

Output:

{
  "value": {
    "DISTANCE": "8579.658400770431"
  }
}

FLOOR

This page describes the FLOOR function in Lenses SQL.

FLOOR(numExpr)

Returns the largest value not greater than the argument.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    FLOOR(float),
    float 
FROM numbers-data
LIMIT 2;

Output:

{
  "value": {
    "FLOOR": 19,
    "float": 19.7
  }
}
{
  "value": {
    "FLOOR": 83,
    "float": 83.82
  }
}

MAX

This page describes the MAX function in Lenses SQL.

MAX(numExpr1,numExpr2,numExpr3)

Returns the maximum element from an arbitrary number of given elements.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT MAX(integer, float) 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "MAX": 16550
  }
}

MIN

This page describes the MIN function in Lenses SQL.

MIN(numExpr1,numExpr2,numExpr3)

Returns the minimum element from an arbitrary number of given elements.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT MIN(integer, float) 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "MIN": 19.7
  }
}

MOD

This page describes the MOD function in Lenses SQL.

MOD(numExpr, numExpr)

Alias for %.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT MOD(integer, 2) 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "MOD": 0
  }
}

NEG

This page describes the NEG function in Lenses SQL.

NEG(numExpr)

Returns the negative value of an expression it has to evaluate to a number type.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    NEG(float),
    float 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "NEG": -95.95,
    "float": 95.95
  }
}

POW

This page describes the POW function in Lenses SQL.

POW(numExpr1, numExpr2)

Returns numExp1 raised to the numExp2 power.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    POW(integer,2), 
    integer 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "POW": 2147483647,
    "integer": 16550
  }
}

RADIANS

This page describes the RADIANS function in Lenses SQL.

RADIANS(numExpr)

Converts the input expression to radians.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    RADIANS(integer), 
    integer 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "RADIANS": "1228.659433526448",
    "integer": 70397
  }
}

RANDINT

This page describes the RANDINT function in Lenses SQL.

RANDINT()

Returns a random integer.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT RANDINT() 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "RANDINT": 395069592
  }
}

ROUND

This page describes the ROUND function in Lenses SQL.

ROUND(numExpr)

Returns the closest integer of an expression, with ties rounding towards positive infinity.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    ROUND(float), 
    float 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "ROUND": 96,
    "float": 95.95
  }
}

SIGN

This page describes the SIGN function in Lenses SQL.

SIGN(numExpr)

Returns +1 if a value is positive or -1 if a value is negative.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    SIGN(integer) AS positive, 
    SIGN(NEG(integer)) AS negative 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "positive": 1,
    "negative": -1
  }
}

We use the NEG function to emulate a negative value in this function.

SINH

This page describes the SINH function in Lenses SQL.

SINH(numExpr)

Returns the hyperbolic sine of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    SINH(float), 
    float 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "SINH": "2.3416691959782676E41",
    "float": 95.95
  }
}

SIN

This page describes the SIN function in Lenses SQL.

SIN(numExpr)

Returns the hyperbolic sine of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    SIN(float), 
    float 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "SIN": "0.991376280840553",
    "float": 95.95
  }
}

SQRT

This page describes the SQRT function in Lenses SQL.

SQRT(numExpr)

Returns the square root of numExpr.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    SQRT(integer), 
    integer 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "SQRT": "128.64680330268607",
    "integer": 16550
  }
}

TANH

This page describes the TANH function in Lenses SQL.

TANH(numExpr)

Returns the hyperbolic tangent of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TANH(integer), 
    integer 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "TANH": 1,
    "float": 95.95
  }
}

TAN

This page describes the TAN function in Lenses SQL.

TAN (numExpr)

Returns the trigonometric tangent of an expression.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    TAN(integer), 
    integer 
FROM numbers-data
LIMIT 1;

Output:

{
  "value": {
    "TAN": "0.09014387261790552",
    "integer": 16550
  }
}

Nulls

This page describes how to use NULL functions in Lenses SQL Processors.

ISNULL

This page describes the ISNULL function in Lenses SQL.

ISNULL(expr)

Returns true if the input is null; false otherwise.

Available in:

Processors

SQL Studio

✓

Sample code:

// Some code
USE `kafka`;
SELECT 
  ISNULL(modified_at) AS modified_at,
   modified_at AS current_field 
FROM users-events
LIMIT 5;

Output:

{
  "value": {
    "modified_at": true,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": true,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": false,
    "current_field": "2023-11-02T02:46:12.882863"
  }
}
{
  "value": {
    "modified_at": true,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": true,
    "current_field": null
  }
}

ISNOTNULL

This page describes the ISNOTNULL function in Lenses SQL.

ISNOTNULL(expr)

Returns true if the input is not null; false otherwise.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
  ISNOTNULL(modified_at) AS modified_at, 
  modified_at AS current_field 
FROM users-events
LIMIT 5;

Output:

{
  "value": {
    "modified_at": false,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": false,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": true,
    "current_field": "2023-11-05T10:50:25.201315"
  }
}
{
  "value": {
    "modified_at": false,
    "current_field": null
  }
}
{
  "value": {
    "modified_at": false,
    "current_field": null
  }
}

COALESCE

This page describes the COALESCE function in Lenses SQL.

COALESCE(value, prevValue)

Returns the first non-null expression in the expression list.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
    COALESCE(modified_at, "not updated") AS modified_at 
FROM users-events
WHERE modified_at IS NULL
LIMIT 1;

Output:

{
  "value": {
    "modified_at": "not updated"
  }
}

In case you want to know more about nullability scenarios, see here.

AS_NULLABLE

This page describes the AS_NULLABLE function in Lenses SQL.

AS_NULLABLE(expr)

Returns the provided value with its type changed from the original type to its nullable version.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT AS_NULLABLE(modified_at) AS null_value 
FROM users-events
LIMIT 100;

Output:

{
  "value": {
    "null_value": null
  }
}

In case you want to know more about nullability scenarios, see here.

AS_NON_NULLABLE

This page describes the AS_NON_NULLABLE function in Lenses SQL.

AS_NON_NULLABLE(expr)

Returns the provided value with its type changed from the original type to its non-nullable version.

Available in:

Processors

SQL Studio

✓

using case:

USE `kafka`;
SELECT 
CASE
       WHEN modified_at IS NULL THEN 0
       ELSE AS_NON_NULLABLE(modified_at)
END AS null_value FROM users-events
LIMIT 5;

Output:

{
  "value": {
    "null_value": "2023-11-07T05:27:04.740600"
  }
}
{
  "value": {
    "null_value": "2023-11-04T07:46:57.045631"
  }
}
{
  "value": {
    "null_value": 0
  }
}
{
  "value": {
    "null_value": 0
  }
}
{
  "value": {
    "null_value": 0
  }
}

In case you want to know more about nullability scenarios, see here.

Obfuscation

This page describes how to use obfuscation functions in Lenses SQL Processors.

ANONYMIZE

This page describes the ANONYMIZE function in Lenses SQL.

ANONYMIZE(strExpr)

Obfuscates the entire string input.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
  ANONYMIZE(first_name) AS first_name_hidden,
  first_name  
FROM users-events
LIMIT 1;

Output:

{
  "value": {
    "first_name_hidden": "*******",
    "first_name": "Charles"
  }
}

MASK

This page describes the MASK function in Lenses SQL.

MASK(strExpr)

Alias for ANONYMIZE.

Available in:

Processors

SQL Studio

✓

Sample code:

USE `kafka`;
SELECT 
  MASK(first_name) AS first_name_hidden,
  first_name  
FROM users-events
LIMIT 1;

Output:

{
  "value": {
    "first_name_hidden": "*******",
    "first_name": "Charles"
  }
}