Joins
This page describes joining data in Kafka with Lenses SQL Studio.
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:
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 we will get the values:
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
Last updated
Was this helpful?