Grzegorz Kołakowski
07/18/2024, 1:00 PMWITH password_logins AS (SELECT * FROM events WHERE `action` = 'login')
• The right side is a changelog stream of user metadata updates. The problem is that those events contains only partial information - only fields that have been modified have non-empty value:
{"user_id":1} {"ts":"2024-07-18 12:00:00","user_id":1,"city":"Warszawa","phone_number":""}
{"user_id":1} {"ts":"2024-07-18 12:01:00","user_id":1,"city":"","phone_number":"+48 123456789"}
• To perform proper temporal join I need to filter the right side first.
location_changes AS (SELECT * FROM user_data WHERE city <> '')
• Surprisingly, when the changelog stream (right side) is filtered just before temporal join, all columns from the right side are always null (see screenshots).
WITH password_logins AS (SELECT * FROM events WHERE `action` = 'login'),
location_changes AS (SELECT * FROM user_data WHERE city <> '')
SELECT password_logins.ts, password_logins.user_id, location_changes.city
FROM password_logins
LEFT JOIN location_changes
FOR SYSTEM_TIME AS OF password_logins.`ts`
ON password_logins.`user_id` = location_changes.`user_id`;
All the queries that I use are added in the thread. The screenshots below show results when WHERE city <> '' is present or not. I wonder what I did wrong. Or is it a Flink issue?Grzegorz Kołakowski
07/18/2024, 1:03 PMcreate table user_data (
`ts` TIMESTAMP(3),
`user_id` INTEGER,
`city` VARCHAR,
`phone_number` VARCHAR,
watermark for `ts` AS `ts`,
PRIMARY KEY(`user_id`) NOT ENFORCED
) with (
'connector' = 'upsert-kafka',
'value.format' = 'json',
'key.format' = 'json',
'properties.bootstrap.servers' = 'kafka:9092',
'properties.group.id' = 'test-group-id-1',
'topic' = 'user-cdc'
);
INSERT INTO user_data VALUES
(CAST('2024-07-18 12:00:00.000' AS TIMESTAMP(3)), 1, 'Warszawa', ''),
(CAST('2024-07-18 12:01:00.000' AS TIMESTAMP(3)), 1, '', '+48 123456789'),
(CAST('2024-07-18 12:02:00.000' AS TIMESTAMP(3)), 1, 'Kraków', ''),
(CAST('2024-07-18 12:03:00.000' AS TIMESTAMP(3)), 1, '', '+48 987654321'),
(CAST('2024-07-18 12:04:00.000' AS TIMESTAMP(3)), 1, 'Gdańsk', ''),
(CAST('2024-07-18 12:05:00.000' AS TIMESTAMP(3)), 1, 'Poznań', '');
Second table:
%%flink_execute_sql
create table events (
`ts` TIMESTAMP(3),
`user_id` INTEGER,
`action` VARCHAR,
watermark for `ts` AS `ts`
) with (
'connector' = 'kafka',
'format' = 'json',
'properties.bootstrap.servers' = 'kafka:9092',
'properties.group.id' = 'test-group-id-1',
'scan.startup.mode' = 'earliest-offset',
'topic' = 'events'
);
INSERT INTO events VALUES
(CAST('2024-07-18 11:59:59.000' AS TIMESTAMP(3)), 1, 'login'),
(CAST('2024-07-18 12:00:01.000' AS TIMESTAMP(3)), 1, 'login'),
(CAST('2024-07-18 12:01:01.000' AS TIMESTAMP(3)), 1, 'login'),
(CAST('2024-07-18 12:02:01.000' AS TIMESTAMP(3)), 1, 'login'),
(CAST('2024-07-18 12:03:01.000' AS TIMESTAMP(3)), 1, 'login'),
(CAST('2024-07-18 12:04:01.000' AS TIMESTAMP(3)), 1, 'login'),
(CAST('2024-07-18 12:05:01.000' AS TIMESTAMP(3)), 1, 'login');
Query:
WITH password_logins AS (SELECT * FROM events WHERE `action` = 'login'),
location_changes AS (SELECT * FROM user_data WHERE city <> '')
SELECT password_logins.ts, password_logins.user_id, location_changes.city
FROM password_logins
LEFT JOIN location_changes
FOR SYSTEM_TIME AS OF password_logins.`ts`
ON password_logins.`user_id` = location_changes.`user_id`;
Query plan:
| == Abstract Syntax Tree ==
LogicalProject(ts=[$0], user_id=[$1], city=[$5])
+- LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0, 1}])
:- LogicalProject(ts=[$0], user_id=[$1], action=[$2])
: +- LogicalFilter(condition=[=($2, _UTF-16LE'login')])
: +- LogicalWatermarkAssigner(rowtime=[ts], watermark=[$0])
: +- LogicalTableScan(table=[[default_catalog, default_database, events]])
+- LogicalFilter(condition=[=($cor0.user_id, $1)])
+- LogicalSnapshot(period=[$cor0.ts])
+- LogicalProject(ts=[$0], user_id=[$1], city=[$2], phone_number=[$3])
+- LogicalFilter(condition=[<>($2, _UTF-16LE'')])
+- LogicalWatermarkAssigner(rowtime=[ts], watermark=[$0])
+- LogicalTableScan(table=[[default_catalog, default_database, user_data]])
== Optimized Physical Plan ==
Calc(select=[ts, user_id, city])
+- TemporalJoin(joinType=[LeftOuterJoin], where=[AND(=(user_id, user_id0), __TEMPORAL_JOIN_CONDITION(ts, ts0, __TEMPORAL_JOIN_CONDITION_PRIMARY_KEY(user_id0), __TEMPORAL_JOIN_LEFT_KEY(user_id), __TEMPORAL_JOIN_RIGHT_KEY(user_id0)))], select=[ts, user_id, ts0, user_id0, city])
:- Exchange(distribution=[hash[user_id]])
: +- Calc(select=[ts, user_id], where=[=(action, _UTF-16LE'login':VARCHAR(2147483647) CHARACTER SET "UTF-16LE")])
: +- TableSourceScan(table=[[default_catalog, default_database, events, watermark=[ts], watermarkEmitStrategy=[on-periodic]]], fields=[ts, user_id, action])
+- Exchange(distribution=[hash[user_id]])
+- Calc(select=[ts, user_id, city], where=[<>(city, _UTF-16LE'':VARCHAR(2147483647) CHARACTER SET "UTF-16LE")])
+- ChangelogNormalize(key=[user_id])
+- Exchange(distribution=[hash[user_id]])
+- Calc(select=[ts, user_id, city])
+- TableSourceScan(table=[[default_catalog, default_database, user_data, watermark=[ts], watermarkEmitStrategy=[on-periodic]]], fields=[ts, user_id, city, phone_number])
== Optimized Execution Plan ==
Calc(select=[ts, user_id, city])
+- TemporalJoin(joinType=[LeftOuterJoin], where=[((user_id = user_id0) AND __TEMPORAL_JOIN_CONDITION(ts, ts0, __TEMPORAL_JOIN_CONDITION_PRIMARY_KEY(user_id0), __TEMPORAL_JOIN_LEFT_KEY(user_id), __TEMPORAL_JOIN_RIGHT_KEY(user_id0)))], select=[ts, user_id, ts0, user_id0, city])
:- Exchange(distribution=[hash[user_id]])
: +- Calc(select=[ts, user_id], where=[(action = 'login')])
: +- TableSourceScan(table=[[default_catalog, default_database, events, watermark=[ts], watermarkEmitStrategy=[on-periodic]]], fields=[ts, user_id, action])
+- Exchange(distribution=[hash[user_id]])
+- Calc(select=[ts, user_id, city], where=[(city <> '')])
+- ChangelogNormalize(key=[user_id])
+- Exchange(distribution=[hash[user_id]])
+- Calc(select=[ts, user_id, city])
+- TableSourceScan(table=[[default_catalog, default_database, user_data, watermark=[ts], watermarkEmitStrategy=[on-periodic]]], fields=[ts, user_id, city, phone_number])
|D. Draco O'Brien
07/18/2024, 1:11 PMGrzegorz Kołakowski
07/18/2024, 1:12 PMD. Draco O'Brien
07/18/2024, 1:13 PMD. Draco O'Brien
07/18/2024, 1:13 PMGrzegorz Kołakowski
07/18/2024, 1:15 PMD. Draco O'Brien
07/18/2024, 1:20 PMD. Draco O'Brien
07/18/2024, 1:21 PMGrzegorz Kołakowski
07/18/2024, 1:21 PMD. Draco O'Brien
07/18/2024, 1:22 PMGrzegorz Kołakowski
07/18/2024, 1:25 PM{"ts":"2024-07-18 12:00:00","user_id":1,"city":"Warszawa","phone_number":""}
{"ts":"2024-07-18 12:01:00","user_id":1,"city":"","phone_number":"+48 123456789"}
If filtering is applied BEFORE temporal join, the right side should "remember" only the first event. If a login event with ts=2024-07-18 120200 comes, it should be joined with city=Warszawa. Here I get 1 result.
If filtering is applied AFTER temporal join, the right side should "remember" the second event. If a login event with ts=2024-07-18 120200 comes, it should be joined with city=''. Now if I apply city <> "", then I get 0 results.Grzegorz Kołakowski
07/18/2024, 1:31 PMD. Draco O'Brien
07/18/2024, 1:38 PMD. Draco O'Brien
07/18/2024, 1:38 PMD. Draco O'Brien
07/18/2024, 1:40 PMD. Draco O'Brien
07/18/2024, 1:41 PMD. Draco O'Brien
07/18/2024, 1:44 PMGrzegorz Kołakowski
07/18/2024, 1:44 PMcould it be related to this issue: https://stackoverflow.com/questions/74723140/flink-temporal-join-not-emitting-dataAs far I understand it refers to processing time temporal join. I need event time temporal join.
this seems to address some meta-data requirements for the table to use temporal joinIt seems that my table definition satisfies requirements needed. I wonder if adding the filter "breaks" anything, but I cant see what.
D. Draco O'Brien
07/18/2024, 1:45 PMD. Draco O'Brien
07/18/2024, 1:45 PMGrzegorz Kołakowski
07/18/2024, 1:45 PMwell you probably want to enable full flink tracing to compareI'll try.
Grzegorz Kołakowski
07/19/2024, 9:58 AMlocation_changes AS (SELECT * FROM user_data WHERE city <> '') is used, then:
• in org.apache.flink.table.planner.plan.nodes.physical.stream.StreamPhysicalChangelogNormalize#translateToExecNode variable generateUpdateBefore is true.
• In org.apache.flink.table.runtime.operators.join.temporal.TemporalRowTimeJoinOperator#processElement2 first comes UPDATE_AFTER then UPDATE_BEFORE (why?). In consequence, rightState.put(rowTime, row); rightState contains UPDATE_BEFORE event for that timestamp. Then in org.apache.flink.table.runtime.operators.join.temporal.TemporalRowTimeJoinOperator#emitResultAndCleanUpState we always emit collectJoinedRow(leftRow, rightNullRow); because RowDataUtil._isAccumulateMsg_(rightRow.get())) for UPDATE_BEFORE is false.
When location_changes AS (SELECT * FROM user_data) is used (NO FILTERING), then:
• in org.apache.flink.table.planner.plan.nodes.physical.stream.StreamPhysicalChangelogNormalize#translateToExecNode variable generateUpdateBefore is false.
• In org.apache.flink.table.runtime.operators.join.temporal.TemporalRowTimeJoinOperator#processElement2 we get only INSERT and UPDATE_AFTER events. Then in org.apache.flink.table.runtime.operators.join.temporal.TemporalRowTimeJoinOperator#emitResultAndCleanUpState we emit collectJoinedRow(leftRow, rightRow.get()); because RowDataUtil._isAccumulateMsg_(rightRow.get())) for UPDATE_AFTER is true.
I have no idea why UPDATE_BEFORE comes after UPDATE_AFTER in org.apache.flink.table.runtime.operators.join.temporal.TemporalRowTimeJoinOperator#processElement2 🤔Grzegorz Kołakowski
07/19/2024, 12:01 PMFilter is not allowed for right changelog input of event time temporal join, it will corrupt the versioning of data. Please consider removing the filter before joining.
It seems that the check which throws this error does not handle my use case.D. Draco O'Brien
07/19/2024, 1:33 PMGrzegorz Kołakowski
07/19/2024, 2:14 PM