Row-based Operations

This page describes how to use row-based operations in PyFlink Table API.

Map

Performs a map operation with a python general scalar function or vectorized scalar function. The output will be flattened if the output type is a composite type.

  1. from pyflink.common import Row
  2. from pyflink.table import EnvironmentSettings, TableEnvironment
  3. from pyflink.table.expressions import col
  4. from pyflink.table.types import DataTypes
  5. env_settings = EnvironmentSettings.in_batch_mode()
  6. table_env = TableEnvironment.create(env_settings)
  7. table = table_env.from_elements([(1, 'Hi'), (2, 'Hello')], ['id', 'data'])
  8. @udf(result_type=DataTypes.ROW([DataTypes.FIELD("id", DataTypes.BIGINT()),
  9. DataTypes.FIELD("data", DataTypes.STRING())]))
  10. def func1(id: int, data: str) -> Row:
  11. return Row(id, data * 2)
  12. # the input columns are specified as the inputs
  13. table.map(func1(col('id'), col('data'))).to_pandas()
  14. # result is
  15. # _c0 _c1
  16. # 0 1 HiHi
  17. # 1 2 HelloHello

It also supports to take a Row object (containing all the columns of the input table) as input.

  1. @udf(result_type=DataTypes.ROW([DataTypes.FIELD("id", DataTypes.BIGINT()),
  2. DataTypes.FIELD("data", DataTypes.STRING())]))
  3. def func2(data: Row) -> Row:
  4. return Row(data.id, data.data * 2)
  5. # specify the function without the input columns
  6. table.map(func2).alias('id', 'data').to_pandas()
  7. # result is
  8. # id data
  9. # 0 1 HiHi
  10. # 1 2 HelloHello

Note The input columns should not be specified when using func2 in the map operation.

It also supports to use vectorized scalar function in the map operation. It should be noted that the input type and output type should be pandas.DataFrame instead of Row in this case.

  1. import pandas as pd
  2. @udf(result_type=DataTypes.ROW([DataTypes.FIELD("id", DataTypes.BIGINT()),
  3. DataTypes.FIELD("data", DataTypes.STRING())]),
  4. func_type='pandas')
  5. def func3(data: pd.DataFrame) -> pd.DataFrame:
  6. res = pd.concat([data.id, data.data * 2], axis=1)
  7. return res
  8. table.map(func3).alias('id', 'data').to_pandas()
  9. # result is
  10. # id data
  11. # 0 1 HiHi
  12. # 1 2 HelloHello

FlatMap

Performs a flat_map operation with a python table function.

  1. from pyflink.common import Row
  2. from pyflink.table.udf import udtf
  3. from pyflink.table import DataTypes, EnvironmentSettings, TableEnvironment
  4. env_settings = EnvironmentSettings.in_streaming_mode()
  5. table_env = TableEnvironment.create(env_settings)
  6. table = table_env.from_elements([(1, 'Hi,Flink'), (2, 'Hello')], ['id', 'data'])
  7. @udtf(result_types=[DataTypes.INT(), DataTypes.STRING()])
  8. def split(x: Row) -> Row:
  9. for s in x.data.split(","):
  10. yield x.id, s
  11. # use split in `flat_map`
  12. table.flat_map(split).to_pandas()
  13. # result is
  14. # f0 f1
  15. # 0 1 Hi
  16. # 1 1 Flink
  17. # 2 2 Hello

The python table function could also be used in join_lateral and left_outer_join_lateral.

  1. # use table function in `join_lateral` or `left_outer_join_lateral`
  2. table.join_lateral(split.alias('a', 'b')).to_pandas()
  3. # result is
  4. # id data a b
  5. # 0 1 Hi,Flink 1 Hi
  6. # 1 1 Hi,Flink 1 Flink
  7. # 2 2 Hello 2 Hello

Aggregate

Performs an aggregate operation with a python general aggregate function or vectorized aggregate function.

  1. from pyflink.common import Row
  2. from pyflink.table import DataTypes, EnvironmentSettings, TableEnvironment
  3. from pyflink.table.expressions import col
  4. from pyflink.table.udf import AggregateFunction, udaf
  5. class CountAndSumAggregateFunction(AggregateFunction):
  6. def get_value(self, accumulator):
  7. return Row(accumulator[0], accumulator[1])
  8. def create_accumulator(self):
  9. return Row(0, 0)
  10. def accumulate(self, accumulator, row):
  11. accumulator[0] += 1
  12. accumulator[1] += row.b
  13. def retract(self, accumulator, row):
  14. accumulator[0] -= 1
  15. accumulator[1] -= row.b
  16. def merge(self, accumulator, accumulators):
  17. for other_acc in accumulators:
  18. accumulator[0] += other_acc[0]
  19. accumulator[1] += other_acc[1]
  20. def get_accumulator_type(self):
  21. return DataTypes.ROW(
  22. [DataTypes.FIELD("a", DataTypes.BIGINT()),
  23. DataTypes.FIELD("b", DataTypes.BIGINT())])
  24. def get_result_type(self):
  25. return DataTypes.ROW(
  26. [DataTypes.FIELD("a", DataTypes.BIGINT()),
  27. DataTypes.FIELD("b", DataTypes.BIGINT())])
  28. function = CountAndSumAggregateFunction()
  29. agg = udaf(function,
  30. result_type=function.get_result_type(),
  31. accumulator_type=function.get_accumulator_type(),
  32. name=str(function.__class__.__name__))
  33. # aggregate with a python general aggregate function
  34. env_settings = EnvironmentSettings.in_streaming_mode()
  35. table_env = TableEnvironment.create(env_settings)
  36. t = table_env.from_elements([(1, 2), (2, 1), (1, 3)], ['a', 'b'])
  37. result = t.group_by(col('a')) \
  38. .aggregate(agg.alias("c", "d")) \
  39. .select(col('a'), col('c'), col('d'))
  40. result.to_pandas()
  41. # the result is
  42. # a c d
  43. # 0 1 2 5
  44. # 1 2 1 1
  45. # aggregate with a python vectorized aggregate function
  46. env_settings = EnvironmentSettings.in_batch_mode()
  47. table_env = TableEnvironment.create(env_settings)
  48. t = table_env.from_elements([(1, 2), (2, 1), (1, 3)], ['a', 'b'])
  49. pandas_udaf = udaf(lambda pd: (pd.b.mean(), pd.b.max()),
  50. result_type=DataTypes.ROW(
  51. [DataTypes.FIELD("a", DataTypes.FLOAT()),
  52. DataTypes.FIELD("b", DataTypes.INT())]),
  53. func_type="pandas")
  54. t.aggregate(pandas_udaf.alias("a", "b")) \
  55. .select(col('a'), col('b')).to_pandas()
  56. # the result is
  57. # a b
  58. # 0 2.0 3

Note Similar to map operation, if you specify the aggregate function without the input columns in aggregate operation, it will take Row or Pandas.DataFrame as input which contains all the columns of the input table including the grouping keys. Note You have to close the “aggregate” with a select statement and it should not contain aggregate functions in the select statement. Besides, the output of aggregate will be flattened if it is a composite type.

FlatAggregate

Performs a flat_aggregate operation with a python general Table Aggregate Function

Similar to GroupBy Aggregation, FlatAggregate groups the inputs on the grouping keys. Different from AggregateFunction, TableAggregateFunction could return 0, 1, or more records for a grouping key. Similar to aggregate, you have to close the flat_aggregate with a select statement and the select statement should not contain aggregate functions.

  1. from pyflink.common import Row
  2. from pyflink.table import DataTypes, TableEnvironment, EnvironmentSettings
  3. from pyflink.table.udf import udtaf, TableAggregateFunction
  4. class Top2(TableAggregateFunction):
  5. def emit_value(self, accumulator):
  6. yield Row(accumulator[0])
  7. yield Row(accumulator[1])
  8. def create_accumulator(self):
  9. return [None, None]
  10. def accumulate(self, accumulator, row):
  11. if row.a is not None:
  12. if accumulator[0] is None or row.a > accumulator[0]:
  13. accumulator[1] = accumulator[0]
  14. accumulator[0] = row.a
  15. elif accumulator[1] is None or row.a > accumulator[1]:
  16. accumulator[1] = row.a
  17. def get_accumulator_type(self):
  18. return DataTypes.ARRAY(DataTypes.BIGINT())
  19. def get_result_type(self):
  20. return DataTypes.ROW(
  21. [DataTypes.FIELD("a", DataTypes.BIGINT())])
  22. env_settings = EnvironmentSettings.in_streaming_mode()
  23. table_env = TableEnvironment.create(env_settings)
  24. # the result type and accumulator type can also be specified in the udtaf decorator:
  25. # top2 = udtaf(Top2(), result_type=DataTypes.ROW([DataTypes.FIELD("a", DataTypes.BIGINT())]), accumulator_type=DataTypes.ARRAY(DataTypes.BIGINT()))
  26. top2 = udtaf(Top2())
  27. t = table_env.from_elements([(1, 'Hi', 'Hello'),
  28. (3, 'Hi', 'hi'),
  29. (5, 'Hi2', 'hi'),
  30. (7, 'Hi', 'Hello'),
  31. (2, 'Hi', 'Hello')], ['a', 'b', 'c'])
  32. # call function "inline" without registration in Table API
  33. result = t.group_by(t.b).flat_aggregate(top2).select('*').to_pandas()
  34. # the result is:
  35. # b a
  36. # 0 Hi2 5.0
  37. # 1 Hi2 NaN
  38. # 2 Hi 7.0
  39. # 3 Hi 3.0