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

MAXK

This page describes the MAXK function in Lenses SQL.

Returns the N largest values of a numExpr.

Available in:

Sample code:

Output:

MINK

This page describes the MINK function in Lenses SQL.

Returns the N smallest values of an numExpr.

Available in:

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:

Sample code:

Output:

Array

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

Headers

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

JSON

This page describes the JSON functions in Lenses SQL.

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.