Joining topics

Understand how to join Kafka topic data when querying with Lenses SQL Snapshot concepts engine for Apache Kafka

Joins

Lenses allows you to combine records from two tables. A query can contain zero, one or multiple JOIN operations.

Create an orders table and insert some data into it:

CREATE TABLE orders(
    _key INT
    , orderDate STRING
    , customerId STRING
    , amount DOUBLE
) 
FORMAT(int, avro);

INSERT INTO orders (
    _key
    , orderDate
    , customerId
    , amount
)
VALUES
(1, '2018-10-01', '1', 200.50),
(2, '2018-10-11', '1', 813.00),
(3, '2018-10-11', '3', 625.20),
(4, '2018-10-11', '14', 730.00),
(5, '2018-10-11', '10', 440.00),
(6, '2018-10-11', '9', 444.80);

With these tables in place, join them to get more information about an order by combining it with the customer information found in the customer table:sq

SELECT 
    o._key AS orderNumber
    , o.amount AS totalAmount
    , c.firstName
    , c.lastName
    , c.city
    , c.country
FROM orders o INNER JOIN customer c
    ON o.customerId = c._key

/*
city        orderNumber     country     totalAmount     lastName    firstName
New York        1               USA         200.5       Smith       Craig
New York        2               USA         813         Smith       Craig
Leeds           3               UK          625.2       Anthony     William
Rio De Janeiro  6               Brazil      444.8       de Ellis    Marquis
Houston         5               USA         440         Milton      Joseph
London          4               UK          730         Wilde       C. J.
*/

Lateral Joins

With lateral joins, Lenses allows you to combine records from a table with the elements of an array expression.

We are going to see in more detail what lateral joins are with an example.

Create a batched_readings table and insert some data into it:

CREATE TABLE batched_readings(
  meter_id int
  , readings int[]
)
FORMAT(int, AVRO);

INSERT INTO batched_readings(
    meter_id
    , readings
) VALUES
(1, [100, 80, 95, 91]),
(2, [87, 93, 100]),
(1, [88, 89, 92, 94]),
(2, [81])

You now can use a LATERAL join to inspect, extract and filter the single elements of the readings array, as if they were a normal field:

SELECT
    meter_id
    , reading
 FROM
    batched_readings
    LATERAL readings AS reading
WHERE 
    reading > 90

Running that query returns:

meter_id	reading
1	100
1	95
1	91
2	93
1	92
1	94

You can use multiple LATERAL joins, one inside the other, if you want to extract elements from a nested array:

CREATE TABLE batched_readings_nested(
  sensor_id int
  , nested_readings int[][]
)
FORMAT(int, AVRO);

INSERT INTO batched_readings_nested(
    sensor_id
    , nested_readings
) VALUES
(1, [[100, 101], [103]]),
(2, [[80, 81], [82, 83, 82]]),
(1, [[100], [103, 102], [104]])

Running the following query we will obtain the same records of the previous example:

SELECT
    meter_id
    , reading
 FROM
    batched_readings
    LATERAL nested_readings AS readings
    LATERAL readings as reading
WHERE 
    reading > 90