Hi Timo, If I understand correctly, the UDF only simplifies the query, but not doing anything functionally different. Please correct me if I am wrong, thank you!
Best, Yik San On Thu, Mar 4, 2021 at 8:34 PM Timo Walther <[email protected]> wrote: > Yes, implementing a UDF might be the most convenient option for some use > cases. The accumulator of such a UDF could take the two timestamps and > perform the two aggregations at once. > > The upsert-kafka connector can apply the updates to the Kafka log. If > you enable log compaction in Kafka, Kafka will clean up the log and make > sure to only keep the most recent one. > > Regards, > Timo > > On 04.03.21 11:59, Yik San Chan wrote: > > Hi Timo, > > > > Thanks for the reply! > > > > > You could filter the deletions manually in DataStream API before > writing > > them to Kafka. > > > > Yah I agree this helps the issue, though I will need to mix up SQL and > > DataStream API. > > > > > To simplify the query you could also investigate to implement your own > > aggregate function and combine the Top 2 and ListAgg into one operation. > > > > Do you mean implement an UDF to do so? > > > > Besides, is 'upsert-kafka' connector designed for this use case? > > > > Thank you. > > > > On Thu, Mar 4, 2021 at 4:41 PM Timo Walther <[email protected] > > <mailto:[email protected]>> wrote: > > > > Hi Yik, > > > > if I understand you correctly you would like to avoid the deletions > in > > your stream? > > > > You could filter the deletions manually in DataStream API before > > writing > > them to Kafka. Semantically the deletions are required to produce a > > correct result because the runtime is not aware of a key for > idempotent > > updates. > > > > To simplify the query you could also investigate to implement your > own > > aggregate function and combine the Top 2 and ListAgg into one > operation. > > > > Regards, > > Timo > > > > On 28.02.21 09:55, Yik San Chan wrote: > > > I define a `Transaction` class: > > > > > > ```scala > > > case class Transaction(accountId: Long, amount: Long, timestamp: > > Long) > > > ``` > > > > > > The `TransactionSource` simply emits `Transaction` with some time > > > interval. Now I want to compute the last 2 transaction timestamp > > of each > > > account id, see code below: > > > > > > ```scala > > > import org.apache.flink.streaming.api.scala.{DataStream, > > > StreamExecutionEnvironment, _} > > > import org.apache.flink.table.api.EnvironmentSettings > > > import > org.apache.flink.table.api.bridge.scala.StreamTableEnvironment > > > import org.apache.flink.walkthrough.common.entity.Transaction > > > import > org.apache.flink.walkthrough.common.source.TransactionSource > > > > > > object LastNJob { > > > > > > final val QUERY = > > > """ > > > |WITH last_n AS ( > > > | SELECT accountId, `timestamp` > > > | FROM ( > > > | SELECT *, > > > | ROW_NUMBER() OVER (PARTITION BY accountId > > ORDER BY > > > `timestamp` DESC) AS row_num > > > | FROM transactions > > > | ) > > > | WHERE row_num <= 2 > > > |) > > > |SELECT accountId, LISTAGG(CAST(`timestamp` AS STRING)) > > > last2_timestamp > > > |FROM last_n > > > |GROUP BY accountId > > > |""".stripMargin > > > > > > def main(args: Array[String]): Unit = { > > > val settings: EnvironmentSettings = > > > > > > EnvironmentSettings.newInstance().useBlinkPlanner().inStreamingMode().build() > > > val streamEnv: StreamExecutionEnvironment = > > > StreamExecutionEnvironment.getExecutionEnvironment > > > val tableEnv: StreamTableEnvironment = > > > StreamTableEnvironment.create(streamEnv, settings) > > > > > > val txnStream: DataStream[Transaction] = streamEnv > > > .addSource(new TransactionSource) > > > .name("transactions") > > > > > > tableEnv.createTemporaryView("transactions", txnStream) > > > > > > tableEnv.executeSql(QUERY).print() > > > } > > > } > > > ``` > > > > > > When I run the program, I get: > > > > > > ``` > > > +----+----------------------+--------------------------------+ > > > | op | accountId | last2_timestamp | > > > +----+----------------------+--------------------------------+ > > > | +I | 1 | 1546272000000 | > > > | +I | 2 | 1546272360000 | > > > | +I | 3 | 1546272720000 | > > > | +I | 4 | 1546273080000 | > > > | +I | 5 | 1546273440000 | > > > | -U | 1 | 1546272000000 | > > > | +U | 1 | 1546272000000,1546273800000 | > > > | -U | 2 | 1546272360000 | > > > | +U | 2 | 1546272360000,1546274160000 | > > > | -U | 3 | 1546272720000 | > > > | +U | 3 | 1546272720000,1546274520000 | > > > | -U | 4 | 1546273080000 | > > > | +U | 4 | 1546273080000,1546274880000 | > > > | -U | 5 | 1546273440000 | > > > | +U | 5 | 1546273440000,1546275240000 | > > > | -U | 1 | 1546272000000,1546273800000 | > > > | +U | 1 | 1546273800000 | > > > | -U | 1 | 1546273800000 | > > > | +U | 1 | 1546273800000,1546275600000 | > > > (to continue) > > > ``` > > > > > > Let's focus on the last transaction (from above) of accountId=1. > > When > > > there is a new transaction from account 1 that happens at > > > timestamp=1546275600000, there are 4 operations in total. > > > > > > ``` > > > +----+----------------------+--------------------------------+ > > > | op | accountId | last2_timestamp | > > > +----+----------------------+--------------------------------+ > > > | -U | 1 | 1546272000000,1546273800000 | > > > | +U | 1 | 1546273800000 | > > > | -U | 1 | 1546273800000 | > > > | +U | 1 | 1546273800000,1546275600000 | > > > ``` > > > > > > While I only want to emit the below "new status" to my downstream > > (let's > > > say another Kafka topic) via some sort of merging: > > > > > > ``` > > > +----------------------+--------------------------------+ > > > | accountId | last2_timestamp | > > > +----------------------+--------------------------------+ > > > | 1 | 1546273800000,1546275600000 | > > > ``` > > > > > > So that my downstream is able to consume literally "the last 2 > > > transaction timestamps of each account": > > > ``` > > > +----------------------+--------------------------------+ > > > | accountId | last2_timestamp | > > > +----------------------+--------------------------------+ > > > | 1 | 1546272000000 | > > > | 1 | 1546272000000,1546273800000 | > > > | 1 | 1546273800000,1546275600000 | > > > (to continue) > > > ``` > > > > > > What is the right way to do this? > > > >
