Running queries

This section provides a set of answers to the most common questions on how to run a Lenses SQL engine Snapshot query.

If the answer you are looking for is not present, you can always reach to us via our community slack channel.

Select a nested field

Below is a sample of how to select nested fields:

SELECT
    fieldC.fieldD.fieldE
FROM table
WHERE fieldA.fieldB LIKE '%Lenses%'

Create an array field

You can create array fields using the ..[] syntax:

CREATE TABLE table(
                      _key INT,
    , fialdA INT[]            -- Simple array
    , fieldB.fieldC STRING[]) -- Array in a subfield
    , fieldD INT[][]          -- Nested array
FORMAT(avro, avro);

Select an array field

Tables can store data containing arrays. Here is a SQL statement for querying an array item:

SELECT
    fieldA[1]
     , fieldB.fieldC[2].x
FROM table
WHERE fieldA[1] LIKE '%Lenses%'

When working with arrays is good to check the array bounds. See the SIZEOF function in the list of supported functions.

Limit the number of records read

Use LIMIT syntax should be used to restrict the number of entries returned. Here is an example:

SELECT ...
    FROM table
    LIMIT 1000

Limit the number of records based on data size

To limit the records return SET max.size construct is required. Here is the code to limit the records to a maximum of 100 megabytes

SET max.size = '100m';

SELECT ...
    FROM table
    LIMIT 1000

Set a time limit for a query

To restrict the time to run the query, use SET max.query.time:

SET  max.query.time = '1h';

SELECT ...
    FROM table
    LIMIT 1000

Filter on text

If your field is of type string, any of the operators below can be used as part of a WHERE condition.

SELECT *
FROM audit
WHERE username LIKE '%Alex%'

Date math

A Kafka message has a timestamp. It can be event time (when it was created) or received time (when it was received by the Kafka brokers to store it). You can use SQL to filter the records timestamp metadata or use them as one of the fields returned:

-- All records since yesterday
SELECT ...
FROM table
WHERE _meta.timestamp > YESTERDAY()

-- All records since 2 days ago
SELECT ...
FROM table
WHERE _meta.timestamp > YESTERDAY()- "2d"

-- All records since 1h ago
SELECT ...
FROM table
WHERE _meta.timestamp > now()- "1h"

-- All records since 15 minutes ago
SELECT ...
FROM table
WHERE _meta.timestamp > now()- "15m"

-- All records since 10 seconds ago
SELECT ...
FROM table
WHERE _meta.timestamp > now()- "10s"

-- All records since 1 week ago
SELECT ...
FROM table
WHERE _meta.timestamp > now()- "1w"

-- All records since 2 months ago
SELECT ...
FROM table
WHERE _meta.timestamp > now()- "2M"

-- All records since a year ago
SELECT ...
FROM table
WHERE _meta.timestamp > now()- "1y"

Filter on true/false

Here is an example of returning the records that have the value of fieldA set to true and the value of the fieldB field set to false:

SELECT ...
    FROM table
WHERE fieldA = true
  AND fieldB = false

Query for a null value

To filter your records when a field is null you can run the following kind of queries:

SELECT ...
    FROM table
WHERE fieldA IS NULL;

SELECT ...
    FROM table
WHERE fieldA.subfieldB IS NULL;

SELECT *
FROM visitorTracking
WHERE location.country IS NOT NULL

Query for array size

Sometimes you want to find out how many items are in your array. To do so you can run:

SELECT
    SIZEOF(arrayFieldA)
FROM table

Filter on numbers

With the SQL code, the user can apply filters for fields of numbers. A number can be integer, short, byte, long, double, float or decimal.

SELECT *
FROM topic
WHERE location.latitude < -10

Filter on Avro fields with a union of many types

The Avro payload is the standard. Sometimes the payload contains a field which is a UNION of multiple types. At runtime, the fields take the value of one of the types in question.

SELECT
    TYPEOF(fieldA) AS typeofA
     , TYPEOF(fieldA.fieldB) AS typeOfB
FROM table;

-- filter on the type
SELECT ...
    FROM table
WHERE TYPEOF(fieldC) = 'io.lenses.domain.LensesIsGreat'

The IF Function

Sometimes it is required to pick a value based on a specific condition being met.

The IF function supports this scenario and here is an example of its use:

SELECT
    IF((field1 + field2)/2 > 10 AND field3 IS NULL, field4 *10, field4+field6 * field8)
FROM table

CASE WHEN

Sometimes it is required to pick a value based on a specific condition being met.

The CASE WHEN function supports this scenario and here is an example of its use:


SELECT 
    CASE 
        WHEN field1 + field2)/2 > 10 AND field3 IS NULL THEN field4 *10
        WHEN field1 + field2)/2 < 10                    THEN field3 *2
        ELSE field4+field6 * field8
    END AS value
FROM table

Insert complex key

There are scenarios where a record key is a complex type. Regardless of the storage format, JSON or Avro, the SQL engine allows the insertion of such entries:

-- creates a smart_devices table where the key is a type with one field {deviceId:100}
CREATE TABLE smart_devices(
                              _key.deviceId INT
    , room INT
    , temperature double)
    FORMAT(avro, avro);

INSERT INTO smart_devices(
                           _key.deviceId
                         , room
                         , temperature)
VALUES(11223, 99, 22.1);

SELECT *
FROM smart_devices;

Last modified: November 18, 2024