1 of 100

SQL Reference

This page describes the SQL Reference for Lenses SQL Processors for data in Kafka.

Expressions

This page describes the expressions in Lenses SQL Processors.

Expressions are the parts of a Lenses SQL query that will be evaluated to single values.

Below is the complete list of expressions that Lenses SQL supports.

Literals

A literal is an expression that represents a concrete value of a given type. This means that there is no resolution needed for evaluating a literal and its value is simply what is specified in the query.

Integers

Integer numbers can be introduced in a Lenses SQL query using integer literals:

SELECT 1 + 2 FROM myTopic

In the above query 1, 2 are integer literals.

Decimals

Decimal number literals can be used to express constant floating-point numbers:

SELECT 3.14 as pi FROM myTopic

Strings

To express strings, string literals can be used. Single quotes (') and double quotes (") are both supported as delimiters:

SELECT CONCAT("hello ", 'world!') FROM myTopic

In the example above, "hello " and 'world!' are string literals.

Booleans

Boolean constant values can be expressed using the false and true boolean literals:

SELECT false, true FROM myTopic

Nulls

Sometimes it is necessary to the NULL literal in a query, for example to test that something is or is not null, or to put a NULL the value facet, useful to delete records in a compacted topic:

INSERT INTO cleanedTopic
SELECT NULL as _value FROM myTopic WHERE myField IS NULL

Arrays

An array is a collection of elements of the same type.

Array expressions

A new array can be defined with the familiar [...] syntax:

["a", "b", "c"", "d"]

You can use more complex expressions inside the array:

[1 + 1, 7 * 2, COS(myfield)]

and nested arrays as well:

[["a"], ["b", "c"]]

Note: empty array literals like [] are currently not supported by Lenses SQL. That will change in future versions.

Array selections

An element of an array can be extracted appending, to the array expression, a pair of square brackets containing the index of the element.

Some examples:

SELECT
  myArray[0],
  myNestedArray[1][1],
  [1, 2, 3][myIndex],
  complexExpression[0].inner[1]
FROM myTopic

Note how the expression on the left of the brackets can be of arbitrary complexity, like in complexExpression[0].inner[1] or [1, 2, 3][myIndex].

Structs

A Struct is a value that is composed by fields and sub-values assigned to those fields. It is similar to what an object is in JSON.

In Lenses SQL there are two ways of building new structs.

Nested aliases

In a SELECT projection, it is possible to use nested aliases to denote the fields of a struct.

In the next example, we are building a struct field called user, with two subfields, one that is a string, and another one that is a struct:

SELECT
    myName as user.name,
    "email" as user.contact.type,
    CONCAT(myName, "@lenses.io") as user.contact.value
FROM myTopic

When the projection will be evaluated, a new struct user will be built.

The result will be a struct with a name field, and a nested struct assigned to the contact field, containing type and value subfields.

Struct Expressions

While nested aliases are a quick way to define new structs, they have some limitations: they can only be used in the projection section of a SELECT, and they do not cover all the cases where a struct can potentially be used.

Struct expressions overcome these limitations.

With struct expressions one can explicitly build complex structs, specifying the name and the values of the fields, one by one, and as any other expression, they can be used inside other expressions and in any other part of the query where an expression is allowed.

The syntax is similar to the one used to define JSON objects:

SELECT
    {
        name: myName,
        contact: { type: "email", value: CONCAT(myName, "@lenses.io") }
    } as user,
    {
        name: myName,
        contacts: [
            { type: "email", value: myEmail },
            { type: "address", value: myAddress }
        ]
    } as userWithContacts 
FROM myTopic

Note how the first projection

{
    name: myName,
    contact: { type: "email", value: CONCAT(myName, "y") }
} as user

is equivalent to the three projections used in the previous paragraph:

myName as user.name,
"email" as user.contact.type,
CONCAT(myName, "@lenses.io") as user.contact.value

while the second projection userWithContacts is not representable with nested aliases, because it defines structs inside an array.

Struct Selections

A selection is an explicit reference to a field within a struct. The syntax for a selection is:

<expression>.<field_name>

Selections can be used to directly access a field of a facet, optionally specifying the topic and the facet:

SELECT
    myField,                 -- value facet field, with implicit topic and facet
    myTopic.myField,         -- value facet field, with explicit topic and implicit facet
    _value.myField,          -- value facet field, with implicit topic and explicit facet
    myTopic._value.myField,  -- value facet field, with explicit topic and facet
    _key.myKeyField,         -- key facet field, with implicit topic and explicit facet
    myTopic._key.myKeyField  -- key facet field, with explicit topic and facet
FROM
   myTopic

It is also possible to select a field from more complex expressions. Here we use selections to select fields from array elements, or to directly access a nested field of a struct expression:

SELECT
    anArrayWithObjects[1].field,
    anArrayWithNestedObjects[1].children[2].field,
    { a: { b: 123 } }.a.b
FROM
    myTopic

In general, a field selection can be used on any expression that returns a struct.

Special characters in field names

If there are special characters in the field names, backticks (`) can be used:

SELECT { `a field!`: "hi" }.`a field!` FROM myTopic

Binary Expressions

A binary expression is an expression that is composed of the left-hand side and right-hand side sub-expressions and an operator that describes how the results of the sub-expressions are to be combined into a single result.

Currently, supported operators are:

Logical operators: AND, OR
Arithmetic operators: +, -, *, /, % (mod)
Ordering operators: >, >=, <, <=
Equality operators: =, !=
String operators: LIKE, NOT LIKE
Inclusion operators: IN, NOT IN

A binary expression is the main way to compose expressions into more complex ones.

For example, 1 + field1 and LENGTH(field2) > 5 are binary expressions, using the + and the >= operator respectively.

Case statements

CASE expressions return conditional values, depending on the evaluation of sub-expressions present in each of the CASE’s branches. This expression is Lenses SQL version of what other languages call a switch-statement or if-elseif-else construct.

SELECT
    CASE
      WHEN field3 = "Robert" THEN "It's bobby"
      WHEN field3 = "William" THEN "It's willy"
      ELSE "Unknown"
    END AS who_is_it
FROM myTopic

Functions

A function is a predefined named operation that takes a number of input arguments and is evaluated into a result. Functions usually accept the result of other expressions as input arguments, so functions can be nested.

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

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.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

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.

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.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

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.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

TOMORROW

This page describes the TOMORROW function in Lenses SQL.

Returns the current date time plus 1 day.

Available in:

Processors

SQL Studio

Sample code:

Output:

TO_TIMESTAMP

This page describes the TO_TIMESTAMP function in Lenses SQL.

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

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

Available in:

Processors

SQL Studio

Sample code:

Output:

YEAR

This page describes the YEAR function in Lenses SQL.

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

Available in:

Processors

SQL Studio

Sample code:

Output:

YESTERDAY

This page describes the YESTERDAY function in Lenses SQL.

Returns the current date time minus 1 day.

Available in:

Processors

This page describes the JSON functions in Lenses SQL.

JSON_EXTRACT_FIRST

This page describes the JSON_EXTRACT_FIRST function in Lenses SQL.

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:

Output:

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 how to use NULL functions in Lenses SQL Processors.

Obfuscation

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