Streaming SQL Multi-Way Join Method for Long State Streams

Abstract: Streaming computing effectively manages large-scale streaming data in real-time, making it ideal for applications such as real-time recommendations, anomaly detection, and monitoring, all of which require immediate processing. In this context, the multi-way stream join operator is crucial, as it combines multiple data streams into a single operator, providing deeper insights through the integration of information from various sources. However, challenges related to memory limitations can arise when processing long state-based data streams, particularly in the area of streaming SQL. In this paper, we propose a streaming SQL multi-way stream join method that utilizes the LSM-Tree to address this issue. We first introduce a multi-way stream join operator called UMJoin, which employs an LSM-Tree state backend to leverage disk storage, thereby increasing the capacity for storing multi-way stream states beyond what memory can accommodate. Subsequently, we develop a method for converting execution plans, referred to as TSC, specifically for the UMJoin operator. This method identifies binary join tree patterns and generates corresponding multi-way stream join nodes, enabling us to transform execution plans based on binary joins into those that incorporate UMJoin nodes. This transformation facilitates the application of the UMJoin operator in streaming SQL. Experiments with the TPC-DS dataset demonstrate that the UMJoin operator can effectively process long state-based data streams, even with limited memory. Furthermore, tests on execution plan conversion for multi-way stream join queries using the TPC-H benchmark confirm the effectiveness of the TSC method in executing these conversions.