How can I join two streams of data by id in Flink,...
# troubleshooting
r
How can I join two streams of data by id in Flink, to get a combined view of the latest data? For example say I have input tables with schema
Copy code
items_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
Copy code
out: {"id": <INT>, "ts": <TIMESTAMP(3)>, "a": <STRING>, "b": <STRING>}
where a new row in either input table produces a single output row
r
something like
Copy code
SELECT 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;
m
Or like this
Copy code
CREATE 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
Copy code
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
)
πŸ‘ 1
r
Thanks guys! I think the one from @Martijn Visser is what I'm looking for. Only problem with @rmoff’s is that
ts
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.
πŸ‘ 1
Thinking about this more, what if there are multiple updates from table a in a row, will the b value be lost? Here is where I had gotten, it's not pretty though, hoping there is a nicer solution like the SQL ones above
Copy code
from 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()