How-To

This section provides a set of answers to the most common questions on how to run a LSQL 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,
    , fieldA 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 it 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 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

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;