Streaming data processing device, method, equipment and system in relational database

Abstract

The embodiment of the present invention provides a stream data processing device, method, equipment and system in a relational database, which relates to the field of data processing technology, wherein the device includes: an optimizer, which is used to receive an input SQL statement defining a stream calculation in a database, and convert the SQL statement into a stream execution plan that can be executed by an executor, wherein the SQL statement includes an upstream database table of incremental data, processing calculations performed on incremental data, and a downstream database table; a memory, which is used to persist the relevant meta-information of the stream calculation in the storage library, and the relevant meta-information includes the stream execution plan and the progress information of the stream execution plan; an executor, which is used to extract incremental data, process and calculate the incremental data, and reflect the processing and calculation results in the downstream database table during the process of repeatedly executing the stream execution plan. This solution enables the stream calculation process of incremental data to be implemented within the same database, which is conducive to reducing resource overhead and improving real-time performance.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a streaming data processing device, method, equipment and system in a relational database. Background Art

[0002] Relational databases are good at concurrent transaction processing (TP) and / or data analysis (AP), but there are no mature technical means for real-time analysis and processing of incremental data. Within each industry, the mainstream currently generally needs to carry out layered data warehouse construction, transforming and processing data from the source layer business library to the application layer data mart to meet the business's need for data adaptation. Typically, incremental data is captured and processed with the help of technologies such as CDC tools, flink, spark, and ETL tools. This traditional way of using data has a complex architecture and a complex technology stack, and involves high manpower, equipment, and energy costs, and a long construction cycle.

[0003] In the traditional data warehouse industry, or offline data warehouse, a concept often mentioned is ETL (Extract, Transform, Load). In the early days of data warehouse technology, ETL was usually a series of batch processing that extracted, cleaned, processed, and dumped the data of the day at a fixed time every day, which was the so-called "T+1" business model and was not real-time.

[0004] Today, in order to pursue better real-time performance, business applications have begun to adopt the concept of "real-time data warehouse", and the "stream-batch integration" technical solution is often used, which should be deeply rooted in various industries. In this technical solution, engineers need to use a variety of basic software such as databases, message pipelines (such as Kafka), stream processing engines (such as Flink), batch processing engines (such as Spark), data warehouse software, etc., and carry out a large amount of customized deployment, development, and operation and maintenance to build a low-latency processing link from raw transaction data to final data applications and reports.

[0005] Specific as Figure 1 As shown, in the "stream-batch integration" technical solution, data is generated from the source layer ODS and stored in the database. The extraction of source data is reflected in the capture of incremental data. The incremental data is usually extracted by performing CDC on the upstream TP database, and the incremental data is converted from the database internal format to the database external format. The extracted incremental data (micro-batch) is temporarily processed and calculated by applications outside the database (such as flink), and the processing results are converted from the database external format to the database internal format. Finally, the processing result status is persisted to another data warehouse, such as clickhouse, and updated to the database's DWD / AP layer, so as to serve downstream BI, visualization, various business applications and other data applications. By Figure 1 It can be seen from the dotted box that what is implemented is database outflow computing. The incremental data is formatted multiple times, enters and exits applications outside the database multiple times, and is transmitted between different servers. These operations all cause very high additional overhead, are difficult to achieve in real time, and also increase system complexity and maintenance costs. Summary of the invention

[0006] In view of this, an embodiment of the present invention provides a streaming data processing device in a relational database to solve the technical problems of high system complexity, large resource overhead, and high maintenance cost in streaming data processing in the prior art. The device includes:

[0007] An optimizer, configured to receive an input SQL statement defining an in-library stream computation, and convert the SQL statement into a stream execution plan executable by an executor, wherein the SQL statement includes an upstream database table of incremental data, a processing computation performed on the incremental data, and a downstream database table;

[0008] A memory, used for persistently storing meta information related to the stream computing in the library, wherein the meta information includes the stream execution plan and progress information of the stream execution plan;

[0009] The executor is used to extract the incremental data, process and calculate the incremental data, and reflect the processing and calculation results in the downstream database table during the process of repeatedly executing the stream execution plan.

[0010] The embodiment of the present invention also provides a method for processing streaming data in a relational database to solve the technical problems of high system complexity, large resource overhead, and high maintenance cost in the existing streaming data processing technology. The method includes:

[0011] The optimizer receives an input SQL statement defining the in-library stream computation, and converts the SQL statement into a stream execution plan executable by the executor, wherein the SQL statement includes an upstream database table of incremental data, processing computations performed on the incremental data, and a downstream database table;

[0012] Persistently storing meta information related to the stream computing in the library, the related meta information including the stream execution plan and progress information of the stream execution plan;

[0013] In the process of repeatedly executing the stream execution plan, the executor extracts the incremental data, processes and calculates the incremental data, and reflects the processing and calculation results in the downstream database table.

[0014] An embodiment of the present invention further provides a computer device, including a streaming data processing device in any of the above-mentioned relational databases, to solve the technical problems of high system complexity, large resource overhead, and high maintenance cost in the streaming data processing in the prior art.

[0015] An embodiment of the present invention further provides a relational database system, including a streaming data processing device in any of the above-mentioned relational databases, to solve the technical problems of high system complexity, large resource overhead and high maintenance cost in the streaming data processing in the prior art.

[0016] Compared with the prior art, the beneficial effects that can be achieved by at least one of the above-mentioned technical solutions adopted in the embodiments of this specification include at least: proposing an SQL statement that defines the in-library stream calculation received as input through the optimizer of the database, converting the SQL statement into a stream execution plan that can be executed by the executor, and then persistently storing the relevant meta-information of the in-library stream calculation through the memory of the database, the relevant meta-information including the stream execution plan and the progress information of the stream execution plan; finally, in the process of repeatedly executing the stream execution plan through the executor of the database, extracting the incremental data, processing and calculating the incremental data, and reflecting the processing and calculation results in the downstream database table. It implements the definition of in-library stream computing based on SQL statements, so that after the user submits the SQL statement defining the in-library stream computing, the SQL statement can be converted into a stream execution plan based on the original optimizer, storage, and executor in the database, and the stream execution plan can be repeatedly executed to extract incremental data, and the incremental data can be processed and calculated, and the processed calculation results can be reflected in the downstream database table, so that the stream computing process of the incremental data is implemented within the same database, and the streaming data processing in the relational database (also called stream computing in the relational database) is realized, avoiding the incremental data from being converted into a format multiple times, entering and exiting applications outside the database multiple times, and being transmitted between different servers. This is conducive to reducing resource overhead, improving real-time performance, and avoiding the deployment of additional message pipelines (such as Kafka) and stream processing engines (such as Flink) outside the database, which is further conducive to reducing system complexity and maintenance costs. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0018] Figure 1 It is a schematic diagram of an existing database outflow computing architecture provided by an embodiment of the present invention;

[0019] Figure 2 It is a structural block diagram of a stream data processing device in a relational database provided by an embodiment of the present invention;

[0020] Figure 3 It is a schematic diagram of a flow chart of implementing stream processing by a stream data processing device in the above relational database provided by an embodiment of the present invention;

[0021] Figure 4 It is a schematic diagram comparing the flow of stream processing implemented by a stream data processing device in the relational database provided by an embodiment of the present invention and the existing stream processing outside the database;

[0022] Figure 5 It is a flow chart of a method for processing streaming data in the above relational database provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0023] The embodiments of the present application are described in detail below with reference to the accompanying drawings.

[0024] The following describes the implementation methods of the present application through specific examples, and those skilled in the art can easily understand other advantages and effects of the present application from the contents disclosed in this specification. Obviously, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments. The present application can also be implemented or applied through other different specific implementation methods, and the details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present application. It should be noted that, in the absence of conflict, the following embodiments and the features in the embodiments can be combined with each other. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work belong to the scope of protection of the present application.

[0025] In an embodiment of the present invention, a streaming data processing device in a relational database is provided, which is applied to the relational database. In the case of distinguishing between an upstream relational database and a downstream relational database, the device is applied to the upstream relational database. Figure 2 As shown, the device comprises:

[0026] The optimizer 201 is used to receive an input SQL statement defining an in-library stream computation, and convert the SQL statement into a stream execution plan executable by an executor, wherein the SQL statement includes an upstream database table of incremental data, a processing computation performed on the incremental data, and a downstream database table;

[0027] The memory 202 is used to persistently store the meta information related to the stream computing in the library, wherein the meta information includes the stream execution plan and the progress information of the stream execution plan;

[0028] The executor 203 is used to extract the incremental data, process and calculate the incremental data, and reflect the processing and calculation results in the downstream database table during the process of repeatedly executing the stream execution plan.

[0029] Depend on Figure 2 As shown, in an embodiment of the present invention, the in-library stream computing is defined based on SQL statements, so that after the user submits the SQL statement defining the in-library stream computing, the SQL statement can be converted into a stream execution plan based on the original optimizer, memory, and executor in the database, and the stream execution plan can be repeatedly executed to extract incremental data, and the incremental data can be processed and calculated, and the processed calculation results are reflected in the downstream database table, so that the stream computing process of the incremental data is implemented within the same database, and the streaming data processing in the relational database (also called the stream computing in the relational database) is realized, avoiding the incremental data from being formatted multiple times, entering and exiting the application outside the database multiple times, and being transmitted between different servers. This is beneficial to reducing resource overhead, improving real-time performance, and avoiding the deployment of additional message pipelines (such as kafka) and stream processing engines (such as flink) outside the database, which is further beneficial to reducing system complexity and maintenance costs.

[0030] In specific implementation, the above-mentioned SQL statement can be a statement in the form of an SQL data structure, the content of the SQL statement includes the upstream database table of the incremental data (i.e., the upstream database table where the incremental data comes from), the processing calculations performed on the incremental data (calculation type, etc.), and the downstream database table (i.e., which downstream database table the results of the processing calculation need to be synchronized to). The specific form of the SQL statement is not specifically limited in this application.

[0031] In specific implementation, in order to realize the process of extracting incremental data, processing and calculating the incremental data, and reflecting the processing and calculation results in the downstream database table based on the stream execution plan converted from the SQL statement, in this embodiment, the SQL statement includes a first preset function and a second preset function, the first preset function is used to indicate the pre-write log of the upstream database table that needs to be scanned to extract the incremental data, and the second preset function is used to indicate the downstream database table to which the incremental data needs to be synchronized.

[0032] For example, the traditional SQL statement is as follows:

[0033]

[0034] This traditional SQL statement defines a table t2, whose data comes from the existing table t1. The conversion calculation logic from table t1 to table t2 is count(), avg(v1) and GROUP BY kind. The meaning of this traditional SQL statement definition is to use the current stock data of table t1 to create table t2, but it does not describe how to apply the subsequent new data of table t1 to table t2, that is, it is impossible to define how to apply or synchronize the incremental data of table t1 to table t2.

[0035] In order to implement the ability to define the application or synchronization of the incremental data of table t1 to table t2 through an SQL statement in a streaming data processing device in the above-mentioned relational database, it is proposed that the SQL statement includes a first preset function and a second preset function (the first preset function and the second preset function are used as keywords in the SQL statement), the first preset function is used to indicate the pre-write log of the upstream database table that needs to be scanned to extract the incremental data, and the second preset function is used to indicate the downstream database table to which the incremental data needs to be synchronized, that is, the first preset function and the second preset function are different from the functions used in the traditional SQL statement to define the use of the current stock data of table t1 to create table t2. The first preset function may be any function that can be used to indicate the write-ahead log of the upstream database table that needs to be scanned to extract the incremental data. For example, the first preset function may be a STREAMING function, that is, when the optimizer reads the first preset function, it will know that the first preset function indicates the data processing process of scanning the write-ahead log of the upstream database table to extract the incremental data, and then the data processing process indicated by the first preset function is reflected in the stream execution plan, which is subsequently executed by the executor; the second preset function may be any function that can be used to indicate the downstream database table to which the incremental data needs to be synchronized, that is, when the optimizer reads the second preset function, it will know that the second preset function indicates the data processing process of which downstream database table the incremental data is synchronized to, and then the data processing process indicated by the second preset function is reflected in the stream execution plan, which is subsequently executed by the executor. For example, the second preset function may be a STREAM function.

[0036] In specific implementation, from the user's perspective, the stream data processing device in the above relational database only requires the user to submit an SQL statement that defines the stream calculation in the database, such as Figure 3As shown in steps 1 to 3, you only need to submit a simple SQL to convert the SQL statement into a stream execution plan based on the optimizer in the database, and then execute the stream execution plan based on the executor to perform streaming data processing, so that there is no need to perform computational processing of stream data processing outside the database. In traditional off-database stream data processing, taking flink as an example, in order to implement processing calculations, users are required to write the processing algorithm logic in java code, provide or submit the calculation process code, and ensure that the written calculation process code can obtain the expected results, and the corresponding upstream input and output to the downstream table also requires the user to build the IT infrastructure and tools to implement it. Compared with the existing off-database stream data processing, this application requires the use of different off-database computing tools and programming languages, which not only simplifies the development work, but also makes the business model easier to record, reuse and disseminate, reducing the complexity of the system.

[0037] In specific implementation, the above SQL statement follows the expression habits of SQL data structure, but proposes a new syntax to describe the semantics of reflecting the incremental changes (or enhanced data) of table t1 to table t2 after processing and calculation. The above SQL statement does not describe the data processing and calculation process, but focuses on reflecting the semantic relationship between data input and output (that is, the upstream database table of incremental data (such as Figure 3 The source layer ODS shown in the figure), processing calculations performed on incremental data (for example, aggregation, connection, and other calculation types), and downstream database tables (such as Figure 3 The analysis library DWD / AP shown in the figure) can be used to realize the process of processing and calculation based on the original optimizer and executor inside the database, which is one of the advantages of SQL language. Compared with writing stream processing code in high-level programming languages, SQL language is easier to learn and master.

[0038] In specific implementation, after receiving the input SQL statement defining the stream calculation in the database, the optimizer converts the SQL statement into a stream execution plan that can be executed by the executor. In order to realize that the executor in the database can be based on the stream execution plan, it is proposed that the stream execution plan includes a scanning extraction step for extracting the incremental data, a processing calculation step for the incremental data, and a processing action for reflecting the processing calculation result to the downstream database table. The executor can repeat the execution according to the relevant steps in the stream execution plan.

[0039] In specific implementation, the traditional execution plan only includes storing the existing data of table t1 into the target table t2 after calculation and processing, and does not include the content related to incremental data. The present application proposes a stream execution plan including the scanning and extraction steps for incremental data, the processing and calculation steps, and the processing actions of reflecting the processing and calculation results to the downstream database table. These are all steps for streaming data processing based on incremental data, which are different from the traditional execution plan.

[0040] In specific implementation, the above optimizer is one of the core components inside the database. It can convert the semantics described by the SQL statement into a stream execution plan that can be executed by the executor. The stream execution plan can be in the form of a plan tree, composed of various operators, corresponding to the input, processing, and output processes described by the SQL statement. The core function of the optimizer is to convert the semantics of the description of the expected result expressed by SQL into corresponding executable process steps (data structures) as the input of the subsequent executor.

[0041] Generally speaking, the optimizer generates an execution plan each time the database executes an SQL. Therefore, the execution plan generated by the optimizer is a one-time operation, which is a one-time operation process of scanning, processing and outputting the existing data in the database. The above-mentioned stream execution plan is based on the working principle of the optimizer itself, and has been appropriately improved to generate an incremental execution plan for a round of processing for future incremental changes (or incremental data), which is used for subsequent repeated execution of incremental data synchronization. Therefore, the stream execution plan in the present invention is repeatedly used and executed by the executor, and each execution takes a batch of change increments (or incremental data) of the upstream table. This stream execution plan can calculate the updated results for the incremental part of the data.

[0042] In specific implementation, the stream execution plan needs to be saved because it will be executed repeatedly periodically. The progress information needs to ensure that after the stream synchronization starts, each round of incremental data processing cannot repeat the processed change data and cannot skip some changes, so that the upstream changes can be reflected in the downstream results truthfully. Therefore, Figure 3 As shown in step 4, the relevant meta information of the stream computing in the library is persistently stored, and the relevant meta information mainly includes the stream execution plan and the progress information of the stream (the progress information may include the meta information table or file, log and other locations of the database itself).

[0043] In the specific implementation, the steps of parsing, planning, and executing SQL statements are executed in the backend process corresponding to the user session. From the design point of view, the creation process of the aforementioned in-library stream computing, from the user inputting and submitting SQL statements to generating stream execution plans and saving related meta information, can refer to the SQL operation process of existing databases.

[0044] However, as an independent feature of in-database stream computing, once it is created, it requires an independent execution mechanism (process, thread, coroutine, etc.) to exist in the background for a long time and repeatedly execute the stream execution plan, so as to achieve the purpose of continuously updating the changes of the upstream table to the downstream. Figure 3 As shown in step 5, this execution mechanism can be a background work process (thread, co-process) of the flow. For example, it is proposed that the above-mentioned device also includes: a background work module, which is used to repeatedly execute the flow execution plan through a background work process, extract the incremental data, process and calculate the incremental data, and reflect the processing and calculation results in the downstream database table. This background work process can be started or awakened after the in-library flow calculation is created. Figure 3 Steps 6, 7, and 8 are executed in a loop within this background working process.

[0045] In specific implementation, the progress information of the stream is to ensure that the changes made in the upstream can be synchronized to the downstream in sequence without duplication or omission. Since the changes in the upstream (that is, the incremental data) are obtained by logically decoding the WAL log, the progress in the WAL log, or the Log Sequence Number (LSN) position, needs to be updated during each round of processing of the incremental data, such as Figure 3 As shown in step 6, before the background work process starts the next round of incremental data synchronization, it is necessary to bind the progress position of the previous WAL log to the context of the current stream execution plan.

[0046] In specific implementation, the above-mentioned background work process realizes the synchronization of the stream (i.e., incremental data) from upstream to downstream by periodically executing the stream execution plan. This interval is very short and can be determined specifically according to the synchronization requirements. For example, it can be once per second, which ensures that the upstream and downstream synchronization intervals are very short and the scale of data processed for synchronization each time is small.

[0047] In specific implementation, the database executor is one of the components of the regular database, and the stream execution plan is generated by the optimizer, so it can be executed by the database executor. When the executor executes the above stream execution plan, Figure 3 As shown in step 7, it includes step 7.1 scanning and extracting incremental data; step 7.2 calculation processing; and step 7.3 insertion and updating of calculation results.

[0048] In the specific implementation, during the scanning and extraction of incremental data, each incremental data scan returns the data changes from the previous incremental data scan to the current scan. This is different from the execution plan of general queries. General queries use full table scans to read table data (files) on disk and return these tuples, while incremental data scans are not based on the data stored in the data table, but scan the database's WAL (write-ahead) log, parse and restore the records related to this table in the WAL log into tuples of this table and return them.

[0049] In specific implementation, in order to achieve the ability to extract incremental data in sequence without duplication or omission so as to synchronize it to the downstream table, in this embodiment, the executor is used to determine the scan end position of the scan of the write-ahead log of the upstream database table when the incremental data was extracted last time based on the progress information of the stream execution plan in the relevant meta-information, logically decode the write-ahead log following the scan end position, extract the incremental data in the internal format of the database, and update the scan end position of the scan of the write-ahead log when extracting the incremental data this time to the progress information of the stream execution plan so as to be bound to the context of the next stream execution plan.

[0050] In the specific implementation, during the processing and calculation process, the executor is used to process and calculate the incremental data in the internal format of the extracted database, that is, the original executor inside the database can process and calculate based on the incremental data in the internal format of the extracted database, such as Figure 4 As shown in the dotted box, unlike the off-database streaming data processing, the on-database streaming data processing scheme of the present invention does not require conversion between internal and external formats. The incremental data in the database internal format obtained by logical decoding can be directly used for the calculation and processing of 7.2, and there is no need to convert the incremental data into an out-database format, thereby saving considerable calculation and transmission costs. The off-database streaming data processing scheme needs to convert the incremental data into an out-database format and use out-database tools for processing and calculation, so that the processes of data format conversion, data transmission to out-database tools, and building out-database tools will generate additional system overhead and increase system complexity.

[0051] In specific implementation, the processing calculations in the above SQL statements may include the following types: sorting, aggregation, hashing, association, projection, materialization and other data processing capabilities possessed by the database, and may also include data redistribution steps unique to distributed databases. The stream execution plan is generated by the optimizer, which directly converts the processing calculation types in the SQL statement into the calculation steps for data execution processing, and is executed by the executor. Therefore, the data processing capability range of the in-library stream data processing of this application is basically the same as the query semantics that can be expressed by the SQL language, and has a wide range of business adaptation capabilities.

[0052] In specific implementation, for in-library flow calculation, the calculation processing results will be saved in the downstream data table. By reflecting the processing calculation results of each round of incremental data execution to the downstream table, the user can get a continuously updated result table. The executor is used to reflect the processing calculation results in the database internal format to the downstream database table through any of the following processing actions: insert, update and delete.

[0053] In specific implementation, the processing of calculation results is different according to the different semantics of SQL statements in the database flow calculation. For example, when the upstream table is only appended, and the downstream table obtains the downstream result based on the upstream table in a 1:1 manner, the final impact on the downstream table is only the insertion of new tuples; when the calculation process includes aggregation, such as summation, it involves updating the data in the downstream table:

[0054] If the user inserts 1, 2, 3, and 4 into the upstream table in sequence, and the flow is defined to calculate the sum of these columns, then the downstream table will only have one row of data.

[0055] When 1 and 2 are inserted into the upstream table, if the in-library flow is processing an incremental data round at this time, the two rows of data 1 and 2 will be pulled from the upstream table, and after summing, the calculation result 3 will be inserted into the downstream table. Subsequently, 3 and 4 are inserted into the upstream table in sequence, and another round of incremental data processing is performed. The two rows of data 3 and 4 are pulled from the WAL log of the upstream table. The two rows of data are first summed to 7 during the calculation process, and then 7 is summed with the existing data 3 in the downstream table, and finally 3 is updated to 10 in the downstream table.

[0056] In-database stream computing completes data processing within the database through rounds of incremental data extraction, processing, and merging results.

[0057] When implementing it, Figure 3 As shown in step 8, Figure 3 In step 7, after each round of micro-batch incremental data processing and update results, we want to save the position and progress of the pre-write log for this scan. The next round of incremental data processing needs to start from the position of the pre-write log where the last scan ended, without repeated processing and without missing the intermediate data. This is the basic requirement to ensure the correctness of stream processing.

[0058] In this regard, in-library stream computing is naturally convenient because databases usually include transaction capabilities, which is to package a series of related changes into an atomic operation that succeeds or fails at the same time. Figure 3Step 8 and step 7 are wrapped in the same transaction so that they succeed or fail together. In this way, when the database restarts, crashes, loses power, etc. at any time, after recovery, it can continue from the previously saved interruption position without worrying about data being processed incorrectly.

[0059] When the database is restarted or something similar happens, we restore the execution structure and progress information of the stream from the metadata in step 4, so that the stream computing in the database can be resumed.

[0060] During specific implementation, the upstream database table and the downstream database table may be different data tables in the same relational database, or may be data tables in different relational databases.

[0061] In specific implementation, the streaming data processing device in the above-mentioned relational database can define the stream calculation within the database through a SQL statement syntax. By rationally utilizing and transforming the existing optimizer, executor and other components of the database itself, the database can complete real-time incremental data processing at a low cost and obtain calculation results that can be updated asynchronously in real time.

[0062] Compared with the current database system, it realizes the data processing capability that the database is not good at, and further develops the data storage and query capabilities that the database is good at. Compared with the out-of-database data processing system commonly used today, it has the advantages of simple learning and operation and low construction and operation costs.

[0063] In this embodiment, a computer device is provided, including any of the above-mentioned streaming data processing devices in the relational database.

[0064] Specifically, the computer device may be a computer terminal, a server or a similar computing device.

[0065] In this embodiment, a relational database system is provided, including a stream data processing device in any of the relational databases described above.

[0066] Based on the same inventive concept, the embodiments of the present invention also provide a method for processing streaming data in a relational database, as described in the following embodiments. Since the principle of solving the problem by the method for processing streaming data in a relational database is similar to that of the device for processing streaming data in a relational database, the implementation of the method for processing streaming data in a relational database can refer to the implementation of the device for processing streaming data in a relational database, and the repeated parts will not be repeated.

[0067] Figure 5 is a flow chart of a method for processing streaming data in a relational database according to an embodiment of the present invention, which is applied to an upstream relational database, such as Figure 5 As shown, the method includes:

[0068] Step S501: receiving an input SQL statement defining the in-library stream computation through the optimizer, and converting the SQL statement into a stream execution plan executable by the executor, wherein the SQL statement includes an upstream database table of incremental data, processing computations performed on the incremental data, and a downstream database table;

[0069] Step S502: persistently storing the meta information related to the stream computing in the library, the related meta information including the stream execution plan and the progress information of the stream execution plan;

[0070] Step S503: extracting the incremental data in the process of repeatedly executing the stream execution plan through the executor, processing and calculating the incremental data, and reflecting the processing and calculation results in the downstream database table.

[0071] The embodiments of the present invention achieve the following technical effects: it proposes receiving an SQL statement that defines an in-library stream calculation as input through a database optimizer, converting the SQL statement into a stream execution plan that can be executed by an executor, and then persistently storing relevant meta-information of the in-library stream calculation through a database memory, wherein the relevant meta-information includes the stream execution plan and progress information of the stream execution plan; finally, in the process of repeatedly executing the stream execution plan through the database executor, the incremental data is extracted, the incremental data is processed and calculated, and the processed and calculated results are reflected in the downstream database table. It implements the definition of in-library stream computing based on SQL statements, so that after the user submits the SQL statement defining the in-library stream computing, the SQL statement can be converted into a stream execution plan based on the original optimizer, storage, and executor in the database, and the stream execution plan can be repeatedly executed to extract incremental data, and the incremental data can be processed and calculated, and the processed calculation results can be reflected in the downstream database table, so that the stream computing process of the incremental data is implemented within the same database, and the streaming data processing in the relational database (also called stream computing in the relational database) is realized, avoiding the incremental data from being converted into a format multiple times, entering and exiting applications outside the database multiple times, and being transmitted between different servers. This is conducive to reducing resource overhead, improving real-time performance, and avoiding the deployment of additional message pipelines (such as Kafka) and stream processing engines (such as Flink) outside the database, which is further conducive to reducing system complexity and maintenance costs.

[0072] Obviously, those skilled in the art should understand that the modules or steps of the above-mentioned embodiments of the present invention can be implemented by a general computing device, they can be concentrated on a single computing device, or distributed on a network composed of multiple computing devices, and optionally, they can be implemented by a program code executable by a computing device, so that they can be stored in a storage device and executed by the computing device, and in some cases, the steps shown or described can be executed in a different order from that here, or they can be made into individual integrated circuit modules, or multiple modules or steps therein can be made into a single integrated circuit module for implementation. In this way, the embodiments of the present invention are not limited to any specific combination of hardware and software.

[0073] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the embodiments of the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A streaming data processing device in a relational database, applied to an upstream relational database, characterized in that: include: An optimizer, used for receiving an input SQL statement defining an in-library stream computation, and converting the SQL statement into a stream execution plan executable by an executor, wherein the SQL statement includes an upstream database table of incremental data, a processing computation performed on the incremental data, and a downstream database table, and the stream execution plan includes a scanning extraction step for extracting the incremental data, a processing computation step for performing the incremental data, and a processing action for reflecting the processing computation result to the downstream database table, and the upstream database table and the downstream database table are different data tables in the same relational database; A memory, used for persistently storing meta information related to the stream computing in the library, wherein the meta information includes the stream execution plan and progress information of the stream execution plan; An executor is used to extract the incremental data, process and calculate the incremental data, and reflect the processing and calculation results in the downstream database table during the repeated execution of the stream execution plan, wherein the optimizer, the memory and the executor belong to the same upstream relational database; the executor is used to determine the scan end position of the scan of the pre-write log of the upstream database table when the incremental data was extracted last time based on the progress information of the stream execution plan in the relevant meta-information, logically decode the pre-write log following the scan end position, extract the incremental data in the internal format of the database, and update the scan end position of the scan of the pre-write log when the incremental data is extracted this time as the progress information of the stream execution plan.

2. The stream data processing device in a relational database according to claim 1, characterized in that: The executor is used to process and calculate the extracted incremental data in the internal format of the database.

3. The stream data processing device in a relational database according to claim 1, characterized in that: The executor is used to reflect the processing calculation result to the processing action of the downstream database table through any one of the following processing actions: insert, update and delete.

4. The stream data processing device in a relational database according to any one of claims 1 to 3, characterized in that: Also includes: The background work module is used to repeatedly execute the flow execution plan through the background work process, extract the incremental data, process and calculate the incremental data, and reflect the processing and calculation results in the downstream database table.

5. The stream data processing device in a relational database according to any one of claims 1 to 3, characterized in that: The SQL statement includes a first preset function and a second preset function, the first preset function is used to indicate the write-ahead log of the upstream database table that needs to be scanned to extract the incremental data, and the second preset function is used to indicate the downstream database table to which the incremental data needs to be synchronized.

6. A method for processing streaming data in a relational database, applied to an upstream relational database, characterized in that: include: The optimizer receives an input SQL statement defining the in-library stream computation, and converts the SQL statement into a stream execution plan executable by the executor, wherein the SQL statement includes an upstream database table of incremental data, processing computations performed on the incremental data, and a downstream database table, and the stream execution plan includes a scanning extraction step for extracting the incremental data, a processing computation step for performing the incremental data, and a processing action for reflecting the processing computation result to the downstream database table, and the upstream database table and the downstream database table are different data tables in the same relational database; Persistently storing meta information related to the stream computing in the library, the related meta information including the stream execution plan and progress information of the stream execution plan; In the process of repeatedly executing the stream execution plan, the executor extracts the incremental data, processes and calculates the incremental data, and reflects the processing and calculation results in the downstream database table. The optimizer, the memory and the executor belong to the same upstream relational database. The executor determines the scan end position of the scan of the pre-write log of the upstream database table when the incremental data was extracted last time based on the progress information of the stream execution plan in the relevant meta-information, logically decodes the pre-write log based on the scan end position, extracts the incremental data in the internal format of the database, and updates the scan end position of the scan of the pre-write log when the incremental data is extracted this time as the progress information of the stream execution plan.

7. A computer device, characterized in that: include: A stream data processing device in a relational database according to any one of claims 1 to 5.

8. A relational database system, characterized in that: A streaming data processing device in a relational database comprising any one of claims 1 to 5.

Images