Real-time materialized view system and method
By using a real-time materialized view solution that integrates stream and batch processing, and leveraging the Flink SQL engine and materialized view management module, batch query requests are materialized in real time, solving the problem of high performance consumption in batch query tasks and achieving resource savings and improved response speed.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- SHANGHAI BILIBILI TECH CO LTD
- Filing Date
- 2022-10-09
- Publication Date
- 2026-08-04
AI Technical Summary
Existing technologies suffer from severe performance consumption and read amplification in frequent batch query tasks, especially in applications that calculate data from the past hour every 5 minutes. In reality, only 5 minutes of data increments are needed, but 1 hour of data needs to be read, resulting in excessive resource consumption.
We adopt a real-time materialized view solution that integrates stream and batch processing. By using the Flink SQL engine and the materialized view management module, we can materialize batch query requests in real time through stream computing, create materialized views and save them in HUDI, and directly query the materialized table to return the results, thus reducing redundant calculations.
It significantly improves performance and resource utilization in frequent batch query tasks, solves the problem of low batch computing efficiency, significantly reduces resource consumption, and improves query response speed to the second or even millisecond level.
Smart Images
Figure CN116126950B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of computer technology, and in particular to a real-time materialized view method, apparatus, computer device and computer-readable storage medium, as well as a real-time materialized view system. Background Technology
[0002] Data warehouses and similar applications require responses to queries for data analysis. When dealing with frequent batch query tasks, the actual amount of data retrieved each time is often greater than the required amount. For example, in an application that calculates data from the past hour every 5 minutes, it needs to retrieve data from the past hour every 5 minutes for calculation, which is very performance-intensive and results in severe read amplification. In reality, each query only needs to retrieve data increments from 5 minutes, but it requires retrieving data from the past hour. Summary of the Invention
[0003] The purpose of this application is to provide a real-time materialized view system, method, apparatus, computer device, and computer-readable storage medium to solve the problems of severe performance consumption and extremely high read amplification caused by the aforementioned batch query tasks.
[0004] One aspect of this application provides a real-time materialized view method, the method comprising:
[0005] The entry module receives batch query requests and associates the Flink SQL engine based on the batch query requests;
[0006] The Flink execution module is configured to: parse the batch query requests to obtain SQL commands; and, if the SQL commands conform to preset rules, determine how to create a materialized view for the SQL commands; and
[0007] The materialized view management module performs stream computation on the SQL command and creates a materialized view based on the result of the stream computation.
[0008] Optionally, the entry module includes:
[0009] A service interface is provided for receiving the batch query request, wherein the batch query request includes a request type and a user identifier; and
[0010] The Flink SQL engine is an instance that serves the batch query requests and is used to call the Flink execution module.
[0011] Optionally, the Flink execution module includes:
[0012] Parser: In response to calls from the Flink SQL engine, parses the batch query request to obtain SQL commands; and
[0013] Optimizer: When the SQL command is associated with a materialized identifier, the optimizer provides the batch query request to the materialized view management module through a message queue so that the materialized view management module can create the materialized view; optimizes the SQL command to obtain a first execution plan, and returns the first execution plan to the Flink SQL engine.
[0014] Optionally, the optimizer is further configured to:
[0015] Based on the materialization rules and the catalog of the materialized table, rewrite the SQL command;
[0016] The rewritten SQL command is optimized to obtain a second execution plan, and the second execution plan is returned to the FlinkSQL engine.
[0017] Optionally, the optimizer is further configured to:
[0018] Based on the catalog of the materialized table, determine the rewrite for the SQL command;
[0019] If the time interval between the watermark in the materialization rule and the current time exceeds a preset threshold, the rewrite is rejected.
[0020] Optionally, the Flink execution module further includes an execution engine; wherein the execution engine is configured to: perform batch computation based on the first execution plan when the materialized table does not include a materialized view corresponding to the SQL command, and return the result of the batch computation to the Flink SQL engine; or, when the materialized table includes a materialized view corresponding to the SQL command, obtain and return the requested result from the materialized table based on the second execution plan.
[0021] Optionally, the materialized view management module includes:
[0022] Materialized View Observer: Obtains the batch query request through the message queue, initiates the creation task of the materialized view, performs stream computation based on the batch query request by calling the Flink stream computing cluster, updates the materialized view with the result of the stream computation, and saves the metadata of the materialized view in the database.
[0023] Optionally, the materialized view management module further includes:
[0024] Materialized view acquirer: Retrieves the metadata of the materialized view from the database for the optimizer to read.
[0025] Optionally, the materialized view management module is further configured to:
[0026] Recycle unused materialized views within a preset time period; and / or
[0027] Merge multiple materialized views with the same dimensions at preset time intervals.
[0028] Another aspect of this application provides a real-time materialized view method, the system comprising:
[0029] Receive batch query requests;
[0030] The batch query request is parsed to obtain the SQL command;
[0031] If the SQL command meets the preset rules, perform stream processing on the SQL command and create a materialized view based on the result of the stream processing.
[0032] Optionally, it also includes:
[0033] If the materialized table does not contain a materialized view corresponding to the SQL command, optimize the SQL command to obtain a first execution plan;
[0034] Batch computation is performed based on the first execution plan, and the result of the batch computation is the query result corresponding to the batch query request.
[0035] Optionally, the step of performing stream processing on the SQL command when the SQL command conforms to preset rules, and creating a materialized view based on the result of the stream processing, includes:
[0036] When the SQL command is associated with a materialized identifier, perform stream computation based on the SQL command; and
[0037] The materialized view is created and updated based on the results of the stream computation.
[0038] Optionally, it also includes:
[0039] If the materialized table includes a materialized view corresponding to the SQL command, the SQL command is rewritten based on the materialization rules and the catalog of the materialized table.
[0040] Optimize the rewritten SQL command to obtain a second execution plan;
[0041] Based on the second execution plan, the query results corresponding to the batch query request are obtained from the materialized table.
[0042] Another aspect of this application provides a real-time materialized view apparatus, the apparatus comprising:
[0043] The receiving module is used to receive batch query requests;
[0044] The parsing module is used to parse the batch query request to obtain SQL commands;
[0045] A module is created to perform stream processing on the SQL command when it conforms to preset rules, and to create a materialized view based on the result of the stream processing.
[0046] Another aspect of this application provides a computer device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the computer program to implement the steps of the real-time materialized view method as described above.
[0047] Another aspect of this application provides a computer-readable storage medium storing a computer program that can be executed by at least one processor to cause the at least one processor to perform the steps of the real-time materialized view method as described above.
[0048] The real-time materialized view method, apparatus, computer device, and computer-readable storage medium, as well as the real-time materialized view system provided in this application, have the following technical advantages:
[0049] When using Flink for relatively fixed batch processing, fully leverage Flink's advantage of integrating stream and batch processing—one codebase for two computations. Use stream processing to materialize batch computations in real time. When subsequent batch query requests with the same logic arrive, directly query the materialized table to return the results. For frequent batch query tasks, this stream-materialized batch approach delivers significant performance improvements and resource savings, solving the problem of low batch computation efficiency.
[0050] Compared to methods such as calculating batch data from the past hour every 5 minutes, this application uses stream computing. By leveraging the state, incremental calculations can be performed only by ingesting incremental data every 5 minutes, resulting in very low computational resource consumption. Attached Figure Description
[0051] Figure 1 A schematic diagram illustrating an environment according to an embodiment of this application is shown.
[0052] Figure 2 This schematic diagram illustrates the architecture of a real-time materialized view system according to Embodiment 1 of this application;
[0053] Figure 3 The diagram illustrates the operation flowchart of the real-time materialized view system according to Embodiment 1 of this application in an exemplary application.
[0054] Figure 4A flowchart illustrating a real-time materialized view method according to Embodiment 2 of this application is shown schematically;
[0055] Figure 5 A block diagram of a real-time materialized view apparatus according to Embodiment 3 of this application is schematically shown; and
[0056] Figure 6 The illustration shows a schematic diagram of the hardware architecture of a computer device suitable for implementing a real-time materialized view method according to Embodiment 4 of this application. Detailed Implementation
[0057] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the present application will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only for explaining the present application and are not intended to limit the present application. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the scope of protection of this application.
[0058] It should be noted that the descriptions involving "first," "second," etc., in the embodiments of this application are for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined with "first" or "second" may explicitly or implicitly include at least one of that feature. Furthermore, the technical solutions of the various embodiments can be combined with each other, but this must be based on the ability of those skilled in the art to implement them. If the combination of technical solutions is contradictory or impossible to implement, it should be considered that such a combination of technical solutions does not exist and is not within the scope of protection claimed in this application.
[0059] In the description of this application, it should be understood that the numerical labels before the steps do not indicate the order of the steps, but are only used to facilitate the description of this application and to distinguish each step, and therefore should not be construed as a limitation of this application.
[0060] The following are explanations of some terms used in this application:
[0061] Flink is a distributed real-time computing system used for stateful computation on unbounded and bounded data streams. Flink is designed to run in all common cluster environments, performing computations at memory speeds and any scale. Here, "state" generally refers to the state of a specific task / operator.
[0062] DDL (Data Definition Languages) is used to define different database objects such as data segments, databases, tables, columns, and indexes. Commonly used keywords include create, drop, and alter.
[0063] DML (Data Manipulation Language): Data manipulation statements used to add, delete, update, and query database records, and to check data integrity. Commonly used statement keywords include insert, delete, update, and select.
[0064] Materialized views (projection) refer to the projection operation performed when retrieving data from the underlying storage. Materialized views are an important query optimization technique, primarily improving query speed by avoiding redundant calculations. The principle behind materialized views is to store the results of intermediate queries on disk. When a related query is encountered, the stored results can be directly accessed instead of being repeatedly calculated. Database materialization reduces access to the underlying storage, thus speeding up access.
[0065] HDFS (Hadoop Distributed File System) is the Hadoop Distributed File System.
[0066] Stream processing is used to process (e.g., query) streaming data. Streaming data is typically online data and has no boundaries.
[0067] Batch processing is used to process batch data (large, static datasets). Batch data is typically offline data, measured in batches.
[0068] SQL (Structured Query Language) is a database language with multiple functions, including data manipulation and definition. This interactive language provides great convenience to users, and database management systems should fully utilize SQL to improve the quality and efficiency of computer application systems. SQL can not only be used independently in the terminal but also serve as a sub-language to effectively assist in other program design. In program applications, SQL can work with other programming languages to optimize program functionality, thereby providing users with more comprehensive information.
[0069] Kafka is a distributed, partitioned, multi-replica, multi-subscriber distributed logging system coordinated by ZooKeeper, and can also be used as a message queue system. Kafka can be used for web / Nginx logging, access logging, message services, etc. Kafka performs task computation and applications on a second-by-second basis, making it suitable for scenarios such as real-time recommendations and real-time computing.
[0070] HUDI (Apache HUDI) uses and manages large analytical datasets stored in DFS (HDFS or cloud storage), and supports update operations in the current data table.
[0071] Iceberg is an open table format designed for massive data analytics scenarios. Open table format is a way of organizing metadata and data files, situated beneath computing frameworks (Flink, Spark, etc.) and on top of the data files. Iceberg supports computing engines such as Spark, Trino, PrestoDB, Flink, and Hive.
[0072] A data lake is a repository or system that stores data in its raw format.
[0073] A data warehouse is a subject-oriented, integrated, non-volatile, and time-variant collection of data used to support management decisions and global information sharing. Its main function is to analyze the vast amounts of information accumulated by an organization through online transaction processing (OLTP) in its information systems, extracting valuable information through the unique information storage architecture characteristic of data warehouse theory.
[0074] Lakehouse provides a unified and shareable data foundation, avoiding data movement between traditional data lakes and data warehouses. It stores raw data, processed and cleaned data, and modeled data together in an integrated "lakehouse". It can provide high-concurrency, accurate, and high-performance query services for historical and real-time data for business purposes, and can also support analytical business such as analysis reports, batch processing, and data mining.
[0075] The Lambda architecture, proposed by Nathan Marz, the author of Storm, is a real-time big data processing framework. It integrates offline and real-time computing, incorporating architectural principles such as immutability, read-write separation, and complexity isolation. It can be integrated with various big data components such as Hadoop, Kafka, Storm, Spark, and HBase.
[0076] A parser is used for lexical analysis, semantic syntax analysis, optimization, and code generation, and the result is a syntax tree.
[0077] The optimizer, also known as the query optimizer, is a component responsible for generating an effective execution plan for SQL statements.
[0078] A watermark is a mechanism proposed by Flink for processing EventTime window calculations; it is essentially a timestamp.
[0079] The `commit` command moves the files to be committed to the actual target directory when committing a transaction.
[0080] A materialized table is a table defined based on the result of a single query.
[0081] To facilitate understanding of the technical solutions provided in the embodiments of this application by those skilled in the art, the relevant technologies are described below:
[0082] With the introduction of the lake warehouse concept based on HUDI / Iceberg, data lakes have essentially unified data storage for both streaming and batch processing scenarios, resolving the storage consistency issue under the Lamba architecture. Simultaneously, regarding the unification of streaming and batch processing in the computing engine, Flink SQL can be used to explore various scenarios. For example, can offline SQL achieve real-time data output efficiency, providing results in seconds with controllable resources, while real-time Flink SQL remains as flexible as offline SQL, requiring no attention to storage, eliminating maintenance, allowing for easy modification of SQL logic, and enabling free data backtracking?
[0083] To address this, we tracked real-time DQC (Data Quality Control) business scenarios. Real-time DQC involves monitoring data at the minute level and comparing year-on-year and month-on-month metrics. To avoid unevenness in short data intervals, a sliding window calculation method is used, such as calculating the past hour's data every 5 minutes. The raw data falls into HUDI. If offline DQC is used, it would require fetching the past hour's data every 5 minutes for calculation, which is very performance-intensive and causes severe read amplification. Each query only needs to retrieve 5 minutes of data increments, but it requires reading an hour's worth of data. Using stream computing, leveraging state, only incremental data needs to be ingested and calculated every 5 minutes, with the results written to user storage, resulting in very low computational resource consumption.
[0084] In view of this, this application proposes a real-time materialized view solution based on integrated streaming and batch processing. Specifically, it combines streaming and batch processing technologies to integrate the advantages of each. Drawing inspiration from ClickHouse's materialized view concept and relying on the integrated streaming and batch processing architecture of Flink+HUDI, a real-time materialized view solution based on Flink can be implemented. In some relatively fixed-scenario batch queries, the capabilities of streaming materialization can be used to accelerate batch queries.
[0085] When using Flink for relatively fixed batch processing, fully leverage Flink's advantage of integrating stream and batch processing, achieving two computations from a single codebase. Stream computing is used to materialize batch computations in real time, and the Flink batch query SQL optimizer has been modified to support rewriting queries based on materialization rules. When a batch query arrives, the results are directly retrieved from the materialized table. Simultaneously, unified storage for stream and batch processing utilizes a HUDI (Hub-and-Device) approach, supporting upgrades and updates, with materialized tables directly stored in HUDI. For scenarios with frequent window queries, this stream-materialized batch approach delivers significant performance improvements and resource savings, resolving the inefficiency of batch computations in window processing (especially hops).
[0086] The following provides an exemplary application environment for this application. Figure 1 The illustration shows an environmental application diagram according to an embodiment of this application.
[0087] Service platform 2 can provide services such as data querying, and it can be a single server, a server cluster, or a cloud computing service center. Service platform 2 has a built-in real-time materialized view system 10 for accelerating queries.
[0088] Terminal 4 can be configured to send query requests, etc. The terminal can be any type of computing device, such as a smartphone, tablet, laptop, smart TV, in-vehicle terminal, etc. The terminal and service platform 2 can be connected via a network. The network can include various network devices, such as routers, switches, multiplexers, hubs, modems, bridges, repeaters, firewalls, and / or proxy devices. The network can include physical links, such as coaxial cable links, twisted-pair cable links, fiber optic links, and combinations thereof and / or the like. The network can include wireless links, such as cellular links, satellite links, Wi-Fi links, and / or the like.
[0089] It should be noted that, Figure 1 The number of terminals and servers shown is merely illustrative and is not intended to limit the scope of patent protection in this application.
[0090] The following will provide several embodiments in the above exemplary application environment to illustrate the real-time materialized view scheme.
[0091] Example 1
[0092] Figure 2 The diagram illustrates the architecture of a real-time materialized view system according to Embodiment 1 of this application. The real-time materialized view system 10 may include an entry module 100, a Flink execution module 200, and a materialized view management module 300.
[0093] (1) Entry module 100 .
[0094] The entry module 100 is used to: receive batch query requests and associate the Flink SQL engine based on the batch query requests.
[0095] In an optional embodiment, the entry module 100 includes a service interface and a Flink SQL engine.
[0096] A service interface is used to receive the batch query request, which includes a request type and a user identifier.
[0097] The Flink SQL engine is an instance that serves the batch query requests and is used to call the Flink execution module.
[0098] The service interface receives batch query requests from the terminal. For example, a batch query request is sent every 5 minutes to query the data increment over the past 5 minutes. Each batch query request carries multiple pieces of information, such as the query command, request type (e.g., Flink type), and user identifier (username). The service interface can launch a specific SQL engine, such as the Flink SQL engine, based on the request type and the user identifier. The Flink SQL engine initializes and invokes the Flink execution module 200. It should be noted that the Flink SQL engine depends on the Flink execution module 200. In the above optional embodiments, launching the corresponding SQL engine based on different request types and user identifiers provides high adaptability.
[0099] (2) Flink Execution Module 200 .
[0100] The Flink execution module 200 is configured to: parse the batch query request to obtain an SQL command; and, if the SQL command conforms to a preset rule, determine to create a materialized view for the SQL command.
[0101] In an optional embodiment, the Flink execution module 200 may include a parser, an optimizer, and an execution engine.
[0102] The parser, in response to a call from the Flink SQL engine, parses the batch query request to obtain SQL commands.
[0103] The optimizer, when the SQL command is associated with a materialized identifier, provides the batch query request to the materialized view management module 300 through a message queue so that the materialized view management module 300 can create the materialized view; optimizes the SQL command to obtain a first execution plan, and returns the first execution plan to the Flink SQL engine.
[0104] Specifically:
[0105] The parser parses the batch query request to obtain SQL commands. The SQL commands can be in syntax tree form.
[0106] Next, the optimizer optimizes the SQL command. During the optimization process, the SQL command may trigger some rules. The optimizer has multiple functional components or loads some data, such as projection-hook (materialization hook), hive-catalog (materialized view metadata), and materialize-rules (materialization rules).
[0107] Firstly, the optimizer checks whether the SQL command triggers a projection-hook rule. If the SQL command contains a materialization hint, it indicates that the SQL command requires the creation of a materialized view, thus triggering the projection-hook rule and causing the projection-hook to send the batch query request to a message queue. The message queue can be a message queue system such as Kafka, used to decouple it from the materialized view management module 300. The materialized view management module 300 reads the batch query request from the message queue and creates the materialized view.
[0108] Secondly, the optimizer will also query the hive-catalog for a materialized view that matches the SQL command. The hive-catalog stores metadata for materialized tables. If a materialized view is found, it can be retrieved directly without batch processing the raw data to obtain the corresponding result.
[0109] To obtain the corresponding materialized view, the SQL command needs to be rewritten.
[0110] The optimizer is further configured to: rewrite the SQL command based on materialization rules and the catalog of materialized tables; optimize the rewritten SQL command to obtain a second execution plan; and return the second execution plan to the Flink SQL engine. The rewriting of the SQL command can use preset projection DDL syntax and SQL query rewriting rules. In this embodiment, if there is a materialized view corresponding to the batch query request, the SQL command can be rewritten, optimized, and the second execution plan (QueryOperation) can be obtained and returned to the Flink SQL engine. Furthermore, the materialization rules can refer to Calcite materialization rules and support TVF syntax.
[0111] In other words: (1) If there is no materialized view corresponding to the SQL command, the SQL command is directly optimized to obtain the optimized product (first execution plan). This first execution plan is used for batch computation. (2) If there is a materialized view corresponding to the SQL command, the SQL command is rewritten, and the optimized product (second execution plan) is generated based on the rewritten SQL command. This second execution plan is used to obtain the materialized view, thereby accelerating the query. It can be seen that when using Flink to perform partially fixed batch queries, the optimizer in this embodiment can rewrite the query, and when the batch query request arrives, batch computation is not required; the materialized table is directly queried and the result is returned.
[0112] However, in some cases, even with a corresponding materialized view, it may not be suitable to rewrite the SQL command. The optimizer is also used to: determine the rewriting of the SQL command based on the catalog of the materialized table; and reject the rewriting if the time interval between the watermark in the materialization rule and the current time exceeds a preset threshold. Specifically, the watermark and other indicators can be used to avoid problems such as real-time task delays and failures of materialized views, ensuring the reliability of query results. Taking HUDI as an example, during the data writing and storage process, the corresponding commit records the watermark of the materialized view task when the current commit is completed. When performing materialization rule matching, the corresponding watermark is referenced for rule matching. If the time lag is too large, the current materialized view rewriting is rejected, and the query is directly downgraded to the original table to ensure the reliability of the results.
[0113] In an exemplary embodiment, the execution engine performs different operations based on different execution plans.
[0114] Operation 1: If the materialized table does not contain a materialized view corresponding to the SQL command, the execution engine performs batch computation based on the first execution plan and returns the results of the batch computation to the Flink SQL engine. Specifically, the execution engine calls the Flink-batch cluster to perform computation to obtain the batch computation results.
[0115] Operation 2: If the materialized table includes a materialized view corresponding to the SQL command, the execution engine retrieves and returns the requested result from the materialized table based on the second execution plan.
[0116] Therefore: for batch query requests, if there is no materialized view based on stream computing, batch computing is performed and the results are returned; if there is a materialized view based on stream computing, the materialized view is directly obtained and returned, and data can be read directly from the materialized table, which greatly speeds up the query and achieves a response time of seconds or even milliseconds.
[0117] (3) Materialized View Management Module 300 .
[0118] The materialized view management module 300 performs stream computing on the SQL command and creates a materialized view based on the result of the stream computing.
[0119] Materialized View Management Module 300 can:
[0120] (1) Provide management services for materialized views;
[0121] (2) Responsible for accessing the metadata of materialized views;
[0122] (3) Perform the creation of the materialized view task.
[0123] In an optional embodiment, the materialized view management module 300 includes a projection-watcher and a projection-fetcher.
[0124] Materialized View Observer: Obtains the batch query request through the message queue, initiates the creation task of the materialized view, performs stream computation based on the batch query request by calling the Flink stream computing cluster, updates the materialized view with the result of the stream computation, and saves the metadata of the materialized view in the database.
[0125] Materialized view acquirer: Retrieves the metadata of the materialized view from the database for the optimizer to read.
[0126] A materialized view is created by a materialized view observer, and a stream computation corresponding to the batch query request is initiated. In other words, stream computation is used to materialize batch computation in real time. This approach of materializing batches with streams can bring significant performance improvements and resource savings, solving the problem of low efficiency of batch computation windows (especially hops).
[0127] The materialized view acquirer updates the materialized view's metadata in real time and provides the metadata to the Flink execution module 200, so that when the next batch query request arrives, it can promptly know whether there is a corresponding materialized view.
[0128] In some optional embodiments, the materialized view management module 300 may further: reclaim unused materialized views within a preset time period; and / or merge multiple materialized views with the same dimensions at preset time intervals. In this optional embodiment, by reclaiming materialized views that have not been used for a long time; and / or periodically merging multiple materialized views with the same dimensions, the computational cost of materialized views can be effectively reduced.
[0129] The above real-time materialized view has at least the following advantages:
[0130] First, it provides a real-time materialized view based on integrated stream and batch processing. Specifically, it combines the advantages of both stream and batch computing technologies. The created materialized view is stored in a materialized table, which can reside in HUDI. This leverages the Flink + HUDI integrated stream and batch architecture to implement a real-time materialized view solution based on Flink. For batch queries in some relatively fixed scenarios, streaming materialization capabilities are used to accelerate batch queries.
[0131] The unified storage for stream and batch processing can use HUDI, which supports upgrades and updates, and materialized tables also reside directly in HUDI.
[0132] Second, when using Flink for relatively fixed batch processing, fully leverage Flink's advantage of integrating stream and batch processing, achieving two computations from a single codebase. Stream processing is used to materialize batch computations in real time, and the Flink batch query optimizer has been modified to support rewriting queries based on materialization rules. When batch queries arrive at their destination, the materialized table is directly queried and the results are returned. For frequent window query scenarios, this stream-materialized batch approach delivers significant performance improvements and resource savings, resolving the inefficiency of batch computations on windows (especially hops).
[0133] Compared to batch data such as calculating the past hour's data every 5 minutes, this embodiment uses stream computing. By leveraging the state, incremental data is ingested and incrementally calculated every 5 minutes, resulting in very low computational resource consumption.
[0134] For ease of understanding, the following is combined with Figure 3 An example application is provided.
[0135] Step S300: The service interface receives batch query requests.
[0136] In step S302, the service interface provides batch query requests to the Flink SQL engine, and the Flink SQL engine initializes the Fink execution module.
[0137] In step S304, the Flink SQL engine provides batch query requests to the parser.
[0138] Step S306: The parser parses the batch query request, obtains the SQL command, and provides the SQL command to the optimizer.
[0139] In step S308, the optimizer determines whether the SQL command includes a materialized identifier.
[0140] If yes, the batch query request is sent to Kafka, which then provides the batch query request to the materialized view observer, proceeding to steps S310 and S324. If not, proceed to step S310.
[0141] In step S310, the optimizer determines whether the materialized table exists in the materialized view corresponding to the SQL command.
[0142] If yes, proceed to step S312; otherwise, proceed to step S314.
[0143] In step S312, the optimizer rewrites the SQL command. Proceed to step S314.
[0144] Step S314: Optimize the SQL command to obtain the optimized output (i.e., the first execution plan or the second execution plan).
[0145] If we optimize the SQL command, we get the first execution plan.
[0146] If the rewritten SQL command is optimized, a second execution plan is obtained.
[0147] In step S316, the optimizer sends the optimized output back to the Flink SQL engine.
[0148] In step S318, the Flink SQL engine sends the optimized artifacts to the execution engine.
[0149] Step S320: The execution engine obtains the query results corresponding to the batch query request based on the optimized output.
[0150] Step S322: Return the query results. End of process.
[0151] If it is the first execution plan, the execution engine calls the Flink-batch cluster to perform batch computation, obtains and returns the batch computation results. If it is the second execution plan, the execution engine retrieves and returns the corresponding materialized view from the materialized table.
[0152] In step S324, the materialized view observer obtains batch query requests from Kafka, calls the Flink-streaming cluster to perform stream computing based on the batch query requests, creates a materialized view, and saves the metadata of the materialized view to MySQL.
[0153] It should be noted that the materialized view acquirer periodically retrieves metadata from MySQL and provides it to the optimizer.
[0154] Example 2
[0155] This embodiment provides a real-time materialized view method; for details and effects, please refer to the above.
[0156] Figure 4 A flowchart illustrating a real-time materialized view method according to Embodiment 2 of this application is shown schematically.
[0157] like Figure 4 As shown, the real-time materialized view method may include steps S400 to S404, wherein:
[0158] Step S400: Receive batch query request;
[0159] Step S402: Parse the batch query request to obtain SQL commands;
[0160] Step S404: If the SQL command meets the preset rules, perform stream processing on the SQL command and create a materialized view based on the result of the stream processing.
[0161] In an optional embodiment, the method further includes:
[0162] If the materialized table does not contain a materialized view corresponding to the SQL command, optimize the SQL command to obtain a first execution plan;
[0163] Batch computation is performed based on the first execution plan, and the result of the batch computation is the query result corresponding to the batch query request.
[0164] In an optional embodiment, the step of performing stream processing on the SQL command when the SQL command conforms to preset rules, and creating a materialized view based on the result of the stream processing, includes:
[0165] When the SQL command is associated with a materialized identifier, perform stream computation based on the SQL command; and
[0166] The materialized view is created and updated based on the results of the stream computation.
[0167] In an optional embodiment, the method further includes:
[0168] If the materialized table includes a materialized view corresponding to the SQL command, the SQL command is rewritten based on the materialization rules and the catalog of the materialized table.
[0169] Optimize the rewritten SQL command to obtain a second execution plan;
[0170] Based on the second execution plan, the query results corresponding to the batch query request are obtained from the materialized table.
[0171] Example 3
[0172] Figure 5A block diagram of a real-time materialized view apparatus according to Embodiment 3 of this application is shown. This real-time materialized view apparatus can be divided into one or more program modules. One or more program modules are stored in a storage medium and executed by one or more processors to complete the embodiments of this application. The program modules referred to in the embodiments of this application are a series of computer program instruction segments capable of performing specific functions. The following description will specifically introduce the functions of each program module in this embodiment.
[0173] like Figure 5 As shown, the real-time materialized view device 500 may include a receiving module 510, a parsing module 520, and a creation module 530, wherein:
[0174] The receiving module 510 is used to receive batch query requests;
[0175] The parsing module 520 is used to parse the batch query request to obtain SQL commands;
[0176] A creation module 530 is used to perform stream computing on the SQL command when the SQL command meets preset rules, and create a materialized view based on the result of the stream computing.
[0177] In an optional embodiment, the apparatus further includes a batch computing module (unidentified) for:
[0178] If the materialized table does not contain a materialized view corresponding to the SQL command, optimize the SQL command to obtain a first execution plan;
[0179] Batch computation is performed based on the first execution plan, and the result of the batch computation is the query result corresponding to the batch query request.
[0180] In an optional embodiment, the creation module 530 is further configured to:
[0181] When the SQL command is associated with a materialized identifier, perform stream computation based on the SQL command; and
[0182] The materialized view is created and updated based on the results of the stream computation.
[0183] In an optional embodiment, the apparatus further includes an acquisition module (unidentified), configured to:
[0184] If the materialized table includes a materialized view corresponding to the SQL command, the SQL command is rewritten based on the materialization rules and the catalog of the materialized table.
[0185] Optimize the rewritten SQL command to obtain a second execution plan;
[0186] Based on the second execution plan, the query results corresponding to the batch query request are obtained from the materialized table.
[0187] Example 4
[0188] Figure 6 This diagram schematically illustrates the hardware architecture of a computer device suitable for implementing a real-time materialized view method according to Embodiment 4 of this application. The computer device 10000 can be a Flink-based service platform 2 or a part thereof. The computer device 10000 is a device capable of automatically performing numerical calculations and / or information processing according to pre-set or stored instructions. For example, it can be a rack server, blade server, tower server, or cabinet server (including standalone servers or server clusters composed of multiple servers), etc. Figure 6 As shown, the computer device 10000 includes, but is not limited to, at least the following: a memory 10010, a processor 10020, and a network interface 10030 that can communicate and be linked to each other via a system bus. Wherein:
[0189] The memory 10010 includes at least one type of computer-readable storage medium, including flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the memory 10010 may be an internal storage module of the computer device 10000, such as the hard disk or memory of the computer device 10000. In other embodiments, the memory 10010 may also be an external storage device of the computer device 10000, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the computer device 10000. Of course, the memory 10010 may also include both the internal storage module and the external storage device of the computer device 10000. In this embodiment, the memory 10010 is typically used to store the operating system and various application software installed on the computer device 10000, such as the program code for the real-time materialized view method. In addition, the memory 10010 can also be used to temporarily store various types of data that have been output or will be output.
[0190] In some embodiments, processor 10020 may be a central processing unit (CPU), controller, microcontroller, microprocessor, or other data processing chip. Processor 10020 is typically used to control the overall operation of computer device 10000, such as performing control and processing related to data interaction or communication with computer device 10000. In this embodiment, processor 10020 is used to run program code stored in memory 10010 or process data.
[0191] Network interface 10030 may include a wireless network interface or a wired network interface, which is typically used to establish communication connections between computer device 10000 and other computer devices. For example, network interface 10030 is used to connect computer device 10000 to an external terminal via a network, establishing a data transmission channel and communication connection between computer device 10000 and the external terminal. The network may be an intranet, the Internet, Global System for Mobile Communication (GSM), Wideband Code Division Multiple Access (WCDMA), 4G network, 5G network, Bluetooth, Wi-Fi, or other wireless or wired networks.
[0192] It should be pointed out that, Figure 6 Only computer devices with components 10010-10030 are shown; however, it should be understood that it is not required to implement all of the shown components, and more or fewer components may be implemented instead.
[0193] In this embodiment, the real-time materialized view method stored in memory 10010 can also be divided into one or more program modules and executed by one or more processors (processor 10020 in this embodiment) to complete this application.
[0194] Example 5
[0195] This embodiment also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the real-time materialized view method in this embodiment.
[0196] In this embodiment, the computer-readable storage medium includes flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the computer-readable storage medium may be an internal storage unit of a computer device, such as the hard disk or memory of the computer device. In other embodiments, the computer-readable storage medium may also be an external storage device of the computer device, such as a plug-in hard disk, smart media card (SMC), secure digital (SD) card, flash card, etc., equipped on the computer device. Of course, the computer-readable storage medium may include both the internal storage unit and the external storage device of the computer device. In this embodiment, the computer-readable storage medium is typically used to store the operating system and various application software installed on the computer device, such as the program code of the real-time materialized view method in the embodiment. In addition, the computer-readable storage medium can also be used to temporarily store various types of data that have been output or will be output.
[0197] Obviously, those skilled in the art should understand that the modules or steps of the embodiments of this application described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. Optionally, they can be implemented using computer-executable program code, thereby storing them in a storage device for execution by a computing device. In some cases, the steps shown or described can be performed in a different order than those presented here, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the embodiments of this application are not limited to any particular combination of hardware and software.
[0198] The above are merely preferred embodiments of this application and do not limit the patent scope of this application. Any equivalent structural or procedural transformations made using the content of this application's specification and drawings, or direct or indirect applications in other related technical fields, are similarly included within the patent protection scope of this application.
Claims
1. A real-time materialized view system, characterized in that, The system includes: The entry module receives batch query requests and associates the Flink SQL engine based on the batch query requests; The Flink execution module is configured to: parse the batch query requests to obtain SQL commands; and, if the SQL commands conform to preset rules, determine how to create a materialized view for the SQL commands; and The materialized view management module performs stream processing on the SQL command and creates a materialized view based on the results of the stream processing. The Flink execution module includes: Parser: In response to calls from the Flink SQL engine, parses the batch query request to obtain SQL commands; and Optimizer: When the SQL command is associated with a materialized identifier, the optimizer provides the batch query request to the materialized view management module through a message queue so that the materialized view management module can create the materialized view; optimizes the SQL command to obtain a first execution plan, and returns the first execution plan to the Flink SQL engine; The optimizer is also used for: Based on the catalog of the materialized table, determine the rewrite for the SQL command; If the time interval between the watermark in the materialization rule and the current time exceeds a preset threshold, the rewrite is rejected. The optimizer is also used for: Based on the materialization rules and the catalog of the materialization table, rewrite the SQL command; The rewritten SQL command is optimized to obtain a second execution plan, and the second execution plan is returned to the Flink SQL engine. The Flink execution module further includes an execution engine; wherein the execution engine is configured to: perform batch computation based on the first execution plan when the materialized table does not include a materialized view corresponding to the SQL command, and return the result of the batch computation to the Flink SQL engine; or, when the materialized table includes a materialized view corresponding to the SQL command, obtain and return the request result from the materialized table based on the second execution plan.
2. The system according to claim 1, characterized in that, The entry module includes: A service interface is provided for receiving the batch query request, wherein the batch query request includes a request type and a user identifier; and The Flink SQL engine is an instance that serves the batch query requests and is used to call the Flink execution module.
3. The system according to claim 1, characterized in that, The materialized view management module includes: Materialized View Observer: Obtains the batch query request through the message queue, initiates the creation task of the materialized view, performs stream computation based on the batch query request by calling the Flink stream computing cluster, updates the materialized view with the result of the stream computation, and saves the metadata of the materialized view in the database.
4. The system according to claim 3, characterized in that, The materialized view management module also includes: Materialized view acquirer: Retrieves the metadata of the materialized view from the database for the optimizer to read.
5. The system according to any one of claims 1 to 3, characterized in that, The materialized view management module is also used for: Recycle unused materialized views within a preset time period; and / or Merge multiple materialized views with the same dimensions at preset time intervals.
6. A real-time materialized view method, characterized in that, The method includes: Receive batch query requests; The batch query request is parsed to obtain the SQL command; If the SQL command meets the preset rules, perform stream processing on the SQL command and create a materialized view based on the result of the stream processing; In the case where the SQL command is associated with a materialized identifier, the batch query request is provided to the materialized view management module through a message queue so that the materialized view management module can create the materialized view; the SQL command is optimized to obtain a first execution plan, and the first execution plan is returned to the Flink SQL engine; Based on the catalog of the materialized table, determine the rewrite for the SQL command; If the time interval between the watermark in the materialization rule and the current time exceeds a preset threshold, the rewrite is rejected. Based on the materialization rules and the catalog of the materialization table, rewrite the SQL command; The rewritten SQL command is optimized to obtain a second execution plan, and the second execution plan is returned to the Flink SQL engine. If the materialized table does not include a materialized view corresponding to the SQL command, batch computation is performed based on the first execution plan, and the results of the batch computation are returned to the Flink SQL engine; or, if the materialized table includes a materialized view corresponding to the SQL command, the requested results are obtained and returned from the materialized table based on the second execution plan.
7. The method according to claim 6, characterized in that, Also includes: If the materialized table does not contain a materialized view corresponding to the SQL command, optimize the SQL command to obtain a first execution plan; Batch computation is performed based on the first execution plan, and the result of the batch computation is the query result corresponding to the batch query request.
8. The method according to claim 7, characterized in that, The step of performing stream processing on the SQL command when it conforms to preset rules, and creating a materialized view based on the result of the stream processing, includes: When the SQL command is associated with a materialized identifier, perform stream computation based on the SQL command; and The materialized view is created and updated based on the results of the stream computation.
9. The method according to claim 6, characterized in that, Also includes: If the materialized table includes a materialized view corresponding to the SQL command, the SQL command is rewritten based on the materialization rules and the catalog of the materialized table. Optimize the rewritten SQL command to obtain a second execution plan; Based on the second execution plan, the query results corresponding to the batch query request are obtained from the materialized table.
10. A real-time materialized view device, characterized in that, The device includes: The receiving module is used to receive batch query requests; The parsing module is used to parse the batch query request to obtain SQL commands; A module is created to perform stream processing on the SQL command when the SQL command meets preset rules, and to create a materialized view based on the result of the stream processing. In the case where the SQL command is associated with a materialized identifier, the batch query request is provided to the materialized view management module through a message queue so that the materialized view management module can create the materialized view; the SQL command is optimized to obtain a first execution plan, and the first execution plan is returned to the Flink SQL engine; Based on the catalog of the materialized table, determine the rewrite for the SQL command; If the time interval between the watermark in the materialization rule and the current time exceeds a preset threshold, the rewrite is rejected. The optimizer is also used for: Based on the materialization rules and the catalog of the materialization table, rewrite the SQL command; The rewritten SQL command is optimized to obtain a second execution plan, and the second execution plan is returned to the Flink SQL engine. The Flink execution module further includes an execution engine; wherein the execution engine is configured to: perform batch computation based on the first execution plan when the materialized table does not include a materialized view corresponding to the SQL command, and return the result of the batch computation to the Flink SQL engine; or, when the materialized table includes a materialized view corresponding to the SQL command, obtain and return the requested result from the materialized table based on the second execution plan.
11. A computer device, the computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the real-time materialized view method according to any one of claims 6 to 9.
12. A computer-readable storage medium storing a computer program thereon, characterized in that, The computer program may be executed by at least one processor to cause the at least one processor to perform the steps of the real-time materialized view method according to any one of claims 6 to 9.
13. A computer program product comprising computer instructions, characterized in that, When executed by a processor, the computer instructions implement the steps of the real-time materialized view method according to any one of claims 6 to 9.