Does Kudu support conditions on the UPDATE
portion of UPSERT INTO
?
Can I provide a conditional clause to only update given values based on a comparison between the insert values and destination table? The actual use case is to update a timestamp column with the latest.
Here's the behavior as I imagine it.
CREATE TABLE my_first_table
(
id INT,
name STRING,
status INT,
PRIMARY KEY(id)
)
PARTITION BY HASH PARTITIONS 4
STORED AS KUDU;
INSERT INTO my_first_table VALUES (1, "lee", 101), (2 "shiv", 102), (3,"bob", 103);
--CONDITION FALSE, UPDATE NOT PERFORMED
UPSERT INTO my_first_table AS t
VALUES (3, "bobby", 100) AS v
WHERE v.status > t.status
+----+------+--------+
| id | name | status |
+----+------+--------+
| 1 | lee | 101 |
| 2 | shiv | 102 |
| 3 | bob | 103 |
+----+------+--------+
--CONDITION TRUE, UPDATE PERFORMED
UPSERT INTO my_first_table AS t
VALUES (3, "bobby", 100) AS v
WHERE v.status < t.status
+----+------+--------+
| id | name | status |
+----+------+--------+
| 1 | lee | 101 |
| 2 | shiv | 102 |
| 3 | bobby| 100 |
+----+------+--------+
In the case where 3 does not exist, it should insert.
Is there an elegant workaround if not?
A solution I found was to use a
LEFT JOIN
and filter in theSELECT
expression. So say we have an tableto_upsert
identical to the destination table with all our potential upserts...Thank you for watching this episode of watching me learn sql.