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.

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:

Output:

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.

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:

Output:

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.

Returns the last item seen in a group.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

Output:

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.

Returns the N smallest unique values of a numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

Output:

MINK

This page describes the MINK function in Lenses SQL.

Returns the N smallest values of an numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

Output:

MINK_UNIQUE

This page describes the MINK_UNIQUE function in Lenses SQL.

Returns the N smallest unique values of a numExpr.

Available in:

Processor (stateless)

Processors (stateful)

SQL Studio

Sample code:

Output:

SUM

This page describes the SUM function in Lenses SQL.

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:

Output:

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

Sample code:

Output:

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.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

MONTH_TEXT

This page describes the MONTH_TEXT function in Lenses SQL.

Returns the month name.

Available in:

Processors

SQL Studio

Sample code:

Output:

MINUTE

This page describes the MINUTE function in Lenses SQL.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

MONTH

This page describes the MONTH function in Lenses SQL.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

PARSE_DATE

This page describes the PARSE_DATE function in Lenses SQL.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

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.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

PARSE_TIMESTAMP_MILLIS

This page describes the PARSE_TIMESTAMP_MILLIS function in Lenses SQL.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

PARSE_TIMESTAMP_MICROS

This page describes the PARSE_TIMESTAMP_MICROS function in Lenses SQL.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

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.

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.

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.

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

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.

Calculates the distance between two points using the haversine method.

Available in:

Processors

SQL Studio

Sample code:

Output:

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.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

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.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

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.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

SIGN

This page describes the SIGN function in Lenses SQL.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

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

SINH

This page describes the SINH function in Lenses SQL.

Returns the hyperbolic sine of an expression.

Available in:

Processors

SQL Studio

Sample code:

Output:

SIN

This page describes the SIN function in Lenses SQL.

Returns the hyperbolic sine of an expression.

Available in:

Processors

SQL Studio

Sample code:

Output:

SQRT

This page describes the SQRT function in Lenses SQL.

Returns the square root of numExpr.

Available in:

Processors

SQL Studio

Sample code:

Output:

TANH

This page describes the TANH function in Lenses SQL.

Returns the hyperbolic tangent of an expression.

Available in:

Processors

SQL Studio

Sample code:

Output:

Nulls

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

Obfuscation

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