Hi! I am trying to implement temporal join in Flin...
# troubleshooting
g
Hi! I am trying to implement temporal join in Flink SQL. Below I present a simple example which shows the problem I'm dealing with. • The left side of temporal join is a append-only stream of login actions.
Copy code
WITH 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:
Copy code
{"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.
Copy code
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).
Copy code
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?
First table:
Copy code
create 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:
Copy code
%%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:
Copy code
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:
Copy code
| == 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
Try performing the Temporal Join w/o filtering.
g
> Try performing the Temporal Join w/o filtering. Without filtering I get wrong results. If a user changes other properites in the meantime, "city" will be empty until next location update.
d
right but then you can materialize the result into a temporary table or view and then apply the filters.
See what results you get
g
> right but then you can materialize the result into a temporary table or view and then apply the filters. It does not matter if I use CTE of view, the outcome is the same. What I can do as a workaround, is to implement a job which filters the data and write the data into kafka again, so the main flink job can skip filtering. It will work, but what I wanted to point out that current behaviour that I shown is counter-intuitive and most likely there is a bug in flink.
d
Ok, what did you try in terms of filter after the join and why does that not work exactly?
because I think there should be a way to give Flink access to full changelog
g
I'll show you example explaining that it is important when the filtering is applied (before or after join).
d
ok
g
Copy code
{"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.
This is what I would expect. But what I get is something unexpected for me. Actually, if filtering is applied BEFORE temporal join, the login event is always joined with city=null, as if there was nothing "remembered" on the right side.
d
Could you restructure your query to avoid the need for pre-join filtering on the changelog stream? ie keep a separate stream/table that always holds the latest non-empty city for each user_id?
Also have you enabled debugging and tracing?
this seems to address some meta-data requirements for the table to use temporal join
Also there is a bit more documentation of how the processing occurs here: https://stackoverflow.com/questions/69393832/flink-temporal-join-works-only-for-a-few-seconds
g
could it be related to this issue: https://stackoverflow.com/questions/74723140/flink-temporal-join-not-emitting-data
As 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 join
It seems that my table definition satisfies requirements needed. I wonder if adding the filter "breaks" anything, but I cant see what.
d
well you probably want to enable full flink tracing to compare
I think the documentation of this needs to be improved in Flnk
g
well you probably want to enable full flink tracing to compare
I'll try.
I run the query with debugger. When
location_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
🤔
When I tried to implement an example to easily reproduce the problem (without kafka, input data is created in data stream api programatically) I got:
Copy code
Filter 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
I would check the time stamping and watermarking of all events to make sure they are generated properly and are valid. If you don’t see any issues there I would go ahead and file this as an Flink issue, especially the ordering of UPDATE_BEFORE coming after UPDATE_AFTER
g