Ryan Goldenberg
03/06/2025, 3:42 AMitems_a: {"id": <INT>, "ts": <TIMESTAMP(3)>, "a": <STRING>}
items_b: {"id": <INT>, "ts": <TIMESTAMP(3)>, "b": <STRING>}
and would like to join on id using the latest record by timestamp ts in each table, to get the output table
out: {"id": <INT>, "ts": <TIMESTAMP(3)>, "a": <STRING>, "b": <STRING>}
where a new row in either input table produces a single output rowAntonio Davide Cali
03/06/2025, 12:19 PMrmoff
03/06/2025, 12:24 PMSELECT COALESCE(a.id,b.id) AS id,
COALESCE(a.ts,b.ts) AS ts,
a.a AS a,
b.b AS b
FROM items_a a
FULL OUTER JOIN
items_b b
ON a.id=b.id
AND a.ts=b.ts;Martijn Visser
03/06/2025, 3:28 PMCREATE VIEW combined_data AS
SELECT id, ts, a, CAST(null AS STRING) AS b, 'a' AS source FROM items_a
UNION ALL
SELECT id, ts, CAST(null AS STRING), b, 'b' AS source FROM items_b;
And
SELECT
id,
MAX(ts) OVER w AS ts,
LAST_VALUE(a) OVER w AS a,
LAST_VALUE(b) OVER w AS b
FROM combined_data
WINDOW w AS (
PARTITION BY id -- Partition by id
ORDER BY ts -- Order by timestamp
ROWS BETWEEN 1 PRECEDING -- Consider only previous row
AND CURRENT ROW -- and current row
)Ryan Goldenberg
03/06/2025, 4:19 PMts don't match for this use case.
The motivation is that separate systems for our company are producing updates for products (inventory vs catalog information for example), and we'd like to have a unified view to query.Ryan Goldenberg
03/06/2025, 4:23 PMfrom pyflink.datastream import StreamExecutionEnvironment
from pyflink.table import StreamTableEnvironment
from pyflink.table import EnvironmentSettings, TableEnvironment
from pyflink.table.expressions import col
from pyflink.table.types import DataTypes
from pyflink.common import Row
from pyflink.table import AggregateFunction, DataTypes, TableEnvironment, EnvironmentSettings
from pyflink.table.expressions import call
from pyflink.table.udf import udaf
from pyflink.table.expressions import col, lit
from pyflink.table.window import Tumble
from pyflink.table.changelog_mode import ChangelogMode
from pyflink.common.types import RowKind
class Latest(AggregateFunction):
def create_accumulator(self):
return Row(0, None)
def get_value(self, accumulator):
return accumulator[1]
def accumulate(self, accumulator, ts, value):
if ts >= accumulator[0]:
accumulator[0] = ts
accumulator[1] = value
def get_result_type(self):
return 'STRING'
def get_accumulator_type(self):
return 'ROW<f0 INT, f1 STRING>'
env = StreamExecutionEnvironment.get_execution_environment()
t_env = StreamTableEnvironment.create(env)
t_a = t_env.from_elements([
(1, 1, "a-1-1"),
(2, 1, "a-2-1"),
(1, 2, "a-1-2"),
(2, 3, "a-2-3"),
(2, 2, "a-2-2"),
],
DataTypes.ROW([
DataTypes.FIELD("id", DataTypes.INT()),
DataTypes.FIELD("ts", DataTypes.INT()),
DataTypes.FIELD("a", DataTypes.STRING()),
]))
t_b = t_env.from_elements([
(1, 2, "b-1-2"),
(2, 1, "b-2-1"),
(1, 1, "b-1-1"),
(2, 3, "b-2-3"),
(1, 3, "b-1-3"),
],
DataTypes.ROW([
DataTypes.FIELD("id", DataTypes.INT()),
DataTypes.FIELD("ts", DataTypes.INT()),
DataTypes.FIELD("b", DataTypes.STRING()),
]))
t_env.create_temporary_function("latest", Latest())
t_a_latest = t_a.group_by(col("id")).select(col("id"), call("latest", col("ts"), col("a")).alias("a"))
t_b_latest = t_b.group_by(col("id")).select(col("id"), call("latest", col("ts"), col("b")).alias("b"))
t_join = t_a_latest.rename_columns(col("id").alias("id_a")).full_outer_join(t_b_latest, col("id_a") == col("id")).select(col("id_a"), col("id"), col("a"), col("b"))
def my_filter(row):
return row.get_row_kind() == RowKind.INSERT
ds = t_env.to_changelog_stream(t_join)
ds = ds.filter(my_filter)
t_out = t_env.from_data_stream(ds)
t_out.execute().print()