Method for updating materialized view and electronic device

Abstract

The application provides a materialized view updating method and an electronic device, relates to the technical field of data processing, and can determine the view value of a materialized view through the query record of the materialized view, update the materialized view according to the view value of the materialized view, and realize automatic updating of the materialized view. The application can not only reduce the configuration cost of the view and improve the configuration efficiency, but also can make the updated materialized view more accurate, meet the query demand of a user, improve the success rate of hitting the materialized view when the user queries, and achieve the purpose of query acceleration.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method for updating a materialized view and an electronic device. Background Technology With the development of communication technology, the number of mobile communication users has increased dramatically, leading to an explosive growth in the communication data handled by operators. As the types of services offered by operators increase and user needs become more diverse, many scenarios arise where users need to query data, requiring operators to provide stronger data query capabilities.

[0002] Currently, the industry commonly uses materialized views to improve query speed and efficiency. A materialized view is a table pre-aggregated from the results of a user's query. By configuring a materialized view, queries can retrieve data directly from the materialized view without accessing the base table, thus improving query speed. However, the current configuration process for materialized views requires manual selection from the base table according to specific needs. Since base table data changes rapidly, manual configuration of materialized views is slow to keep up with these changes. Therefore, manually configured materialized views often fail to meet user query requirements, and the manual configuration method is also costly. Summary of the Invention

[0003] This application provides a method and electronic device for updating a materialized view, which can automatically update the materialized view based on the user's query behavior, reduce the configuration cost of the materialized view, improve the configuration efficiency of the materialized view, and achieve the purpose of accelerating the query.

[0004] To achieve the above objectives, this application adopts the following technical solution: In a first aspect, a method for updating a materialized view is provided, comprising: obtaining a first query record of the materialized view, wherein the materialized view is stored in a first storage space; obtaining the view value of the materialized view based on the first query record; and updating the materialized view in the first storage space if the view value of the materialized view is less than a preset threshold.

[0005] For example, the view value of a materialized view is used to characterize the coverage of the materialized view to the user's query requirements.

[0006] For example, the preset threshold is related to business requirements.

[0007] The solution provided in the first aspect above determines the view value of a materialized view through query records and updates the materialized view based on its view value, thus achieving automatic updating of the materialized view. Compared with the existing method of manually configuring materialized views, this not only reduces the configuration cost and improves configuration efficiency, but also, because the view value of the materialized view is determined based on query records, the updated materialized view is more accurate, can meet the user's query needs, and improves the success rate of users hitting the materialized view when querying, thereby achieving the goal of query acceleration.

[0008] As one possible implementation, the view value of the materialized view is obtained based on the first query record, including: statistically analyzing the first query record and the second query record of the query view to obtain the query value of the materialized view and the query value of the query view, wherein the query view is stored in a second storage space; obtaining the first view value based on the query value of the materialized view and the query value of the query view, and obtaining the second view value corresponding to the materialized view based on the query value of the materialized view, wherein the first view value represents the total view value corresponding to the materialized view and the query view; and determining the ratio of the second view value to the first view value as the view value of the materialized view. Based on this, by introducing the second query record of the query view and determining the proportion of the second view value of the materialized view to the total view value corresponding to all views (query view and materialized view), the coverage of user query needs by the materialized view can be effectively reflected, thereby effectively improving the accuracy of the view value of the materialized view.

[0009] As one possible implementation, statistics are performed on the first query record and the second query record of the query view to obtain the query value of the materialized view and the query value of the query view. This includes: performing statistics on the first query record and the second query record within a period T to obtain the first query popularity of the materialized view and the second query popularity of the query view within a period T; obtaining the query value of the materialized view within a period T based on the first query popularity and the query value of the materialized view within a period T-1; and obtaining the query value of the query view within a period T based on the second query popularity and the query value of the query view within a period T-1.

[0010] Specifically, the query value of the materialized view within T period can be calculated by using the exponential smoothing method, based on the first query popularity of T period and the query value of the materialized view within T-1 period. Similarly, the query value of the query view within T period can be calculated by using the second query popularity of T period and the query value of the query view within T-1 period.

[0011] For example, the exponential smoothing method can be first-order exponential smoothing.

[0012] Among them, updating materialized views based on time periods can effectively update materialized views based on business characteristics, improve the success rate of users hitting materialized views when querying, and achieve the goal of query acceleration. At the same time, updating materialized views based on time periods can be adapted to both offline data analysis scenarios and real-time data analysis scenarios.

[0013] As one possible implementation, updating the materialized view in the first storage space includes: sorting the materialized view and the query view according to the query value to obtain a view sequence; and storing the views in the view sequence that meet the preset conditions in the first storage space.

[0014] Optionally, the preset condition can be to select the top N views with the highest query value from the view sequence and store them in the first storage space, where N is a positive integer. Based on this, storing the views that meet the preset condition in the first storage space allows high-query-value views to be stored in the first storage space, improving the success rate of users hitting materialized views when querying.

[0015] One possible implementation involves storing views that meet preset conditions in a view sequence in a first storage space. This includes: determining theoretical views from the view sequence based on preset conditions; comparing the theoretical views with materialized views to obtain newly added views, deleted views, and retained views. Newly added views are those not stored in the first storage space but included in the theoretical views; deleted views are those stored in the first storage space but not in the theoretical views; and retained views are those stored in the first storage space and included in the theoretical views. The status of retained views in the first storage space is updated, deleted views are removed from the first storage space, and newly added views are materialized and stored in the first storage space. Based on this, invalid materialized views are deleted from the first storage space, effectively freeing up storage space and avoiding waste. Simultaneously, by updating the status of retained views and materializing newly added views and storing them in the first storage space, views with high query value are stored in the first storage space, improving the hit rate of materialized views in subsequent queries and achieving query acceleration. Furthermore, by updating only the state of the retained view without rematerializing it, resource consumption can be reduced.

[0016] As one possible implementation, the difference between the total storage space of the first storage space and the sum of the storage spaces corresponding to the theoretical view is less than a preset space threshold. Based on this, it is possible to avoid exceeding the space limit of the first storage space when storing the materialized view.

[0017] As one possible implementation, after storing the theoretical view in the first storage space, the query value of the materialized view stored in the first storage space is greater than the query value of the query view stored in the second storage space. Based on this, the materialized view stored in the first storage space can have a higher query value, increasing the success rate of users hitting the materialized view during queries.

[0018] One possible implementation involves materializing the new view and storing it in the first storage space. This includes: allocating the new view to multiple engines based on their data processing capabilities, obtaining target new views that match the multiple engines, and materializing the target new views and storing them in the first storage space. Based on this, the engine with the optimal cost can be selected during materialized view generation, improving resource utilization. Simultaneously, the generation speed of materialized views is also increased through reasonable resource allocation.

[0019] One possible implementation involves allocating new views to multiple engines based on their data processing capabilities, resulting in target new views matching the various engines. This includes: determining the usable space of a first storage space based on the storage space occupied by deleted views and the storage space occupied by retained views after state updates; determining the view cost of the new view based on its query value and the storage space it occupies; and, with usable space as a constraint and maximum view cost as the objective, allocating new views to multiple engines based on their data processing capabilities, thus obtaining target new views matching the various engines. Based on this, materialized views with higher query value can be stored in limited storage space, thereby increasing the success rate of hitting materialized views during user queries and achieving query acceleration.

[0020] One possible implementation includes: determining a first ratio between the data volume of the materialized view and the data volume of the corresponding base table data, and determining a second ratio between the data volume of the query view and the data volume of the corresponding base table data; storing the base table data in a third storage space; obtaining the storage space occupied by the materialized view by multiplying the first ratio by the storage space occupied by the base table data; and obtaining the storage space occupied by the query view by multiplying the second ratio by the storage space occupied by the base table data. Based on this, by determining the ratio between the data volume of the view and the corresponding base table data, and by multiplying the ratio by the storage space occupied by the corresponding base table data, the storage space occupied by the view can be quickly obtained.

[0021] As one possible implementation, it also includes: responding to a query request, parsing the query request to obtain the corresponding request information; if the request information targets a materialized view in the first storage space, retrieving the query result corresponding to the request information from the first storage space; if the request information does not target a materialized view in the first storage space, retrieving the query result corresponding to the request information from the third storage space. Based on this, performing queries in two ways can improve the reliability of the query and ensure the correctness and consistency of the query results.

[0022] As one possible implementation, after retrieving the query results corresponding to the requested information from the third storage space, the method also includes updating the query view in the second storage space. Based on this, the accuracy of the query view can be improved.

[0023] In a second aspect, an electronic device is provided, comprising: a first storage space for storing a materialized view; and a processing unit for: acquiring a first query record of the materialized view; obtaining a view value of the materialized view based on the first query record; and updating the materialized view in the first storage space when the view value of the materialized view is less than a preset threshold.

[0024] The solution provided in the second aspect above allows electronic devices to determine the view value of a materialized view through query records and update the materialized view based on its view value, thus achieving automatic updating of the materialized view. Compared with the existing method of manually configuring materialized views, this not only reduces the configuration cost and improves configuration efficiency, but also ensures that the updated materialized view is more accurate because its view value is determined based on query records. This can meet the user's query needs, increase the success rate of the user's query hitting the materialized view, and achieve the goal of query acceleration.

[0025] As one possible implementation, the electronic device further includes a second storage space for storing query views. The process of obtaining the view value of a materialized view based on a first query record includes: statistically analyzing the first query record and the second query record of the query view to obtain the query value of the materialized view and the query value of the query view; obtaining the first view value based on the query value of the materialized view and the query value of the query view; and obtaining the second view value corresponding to the materialized view based on the query value of the materialized view, where the first view value represents the total view value corresponding to the materialized view and the query view; and determining the ratio of the second view value to the first view value as the view value of the materialized view. Based on this, by introducing the second query record of the query view and determining the proportion of the second view value of the materialized view to the total view value corresponding to all views (query views and materialized views), the coverage of user query needs by the materialized view can be effectively reflected, thereby effectively improving the accuracy of the view value of the materialized view.

[0026] As one possible implementation, statistics are performed on the first query record and the second query record of the query view to obtain the query value of the materialized view and the query value of the query view. This includes: statistically analyzing the first and second query records within a period T to obtain the first query popularity and the second query popularity of the materialized view within period T; obtaining the query value of the materialized view within period T based on the first query popularity and the query value of the materialized view within period T-1; and obtaining the query value of the query view within period T based on the second query popularity and the query value of the query view within period T-1. Based on this, the electronic device updates the materialized view according to a time period, which can effectively update the materialized view based on business characteristics, improve the success rate of users hitting the materialized view during queries, and achieve the goal of query acceleration. Furthermore, updating the materialized view based on a time period is adaptable to both offline and real-time data analysis scenarios.

[0027] As one possible implementation, updating the materialized views in the first storage space includes: sorting the materialized views and query views according to query value to obtain a view sequence; and storing the views in the view sequence that meet preset conditions in the first storage space. Based on this, storing the views in the view sequence that meet preset conditions in the first storage space ensures that views with high query value are stored in the first storage space, thus improving the success rate of users hitting materialized views during queries.

[0028] One possible implementation involves storing views that meet preset conditions in a view sequence in a first storage space. This includes: determining theoretical views from the view sequence based on preset conditions; comparing the theoretical views with materialized views to obtain newly added views, deleted views, and retained views. Newly added views are those not stored in the first storage space but included in the theoretical views; deleted views are those stored in the first storage space but not in the theoretical views; and retained views are those stored in the first storage space and included in the theoretical views. The status of retained views in the first storage space is updated, deleted views are removed from the first storage space, and newly added views are materialized and stored in the first storage space. Based on this, invalid materialized views are deleted from the first storage space, effectively freeing up storage space and avoiding waste. Simultaneously, by updating the status of retained views and materializing newly added views and storing them in the first storage space, views with high query value are stored in the first storage space, improving the hit rate of materialized views in subsequent queries and achieving query acceleration. Furthermore, by updating only the state of the retained view without rematerializing it, resource consumption can be reduced.

[0029] As one possible implementation, the difference between the total storage space of the first storage space and the sum of the storage spaces corresponding to the theoretical view is less than a preset space threshold. Based on this, it is possible to avoid exceeding the space limit of the first storage space when storing the materialized view.

[0030] As one possible implementation, after storing the theoretical view in the first storage space, the query value of the materialized view stored in the first storage space is greater than the query value of the query view stored in the second storage space. Based on this, the materialized view stored in the first storage space can have a higher query value, increasing the success rate of users hitting the materialized view during queries.

[0031] One possible implementation involves materializing the new view and storing it in the first storage space. This includes: allocating the new view to multiple engines based on their data processing capabilities, obtaining target new views that match the multiple engines, and materializing the target new views and storing them in the first storage space. Based on this, the engine with the optimal cost can be selected during materialized view generation, improving resource utilization. Simultaneously, the generation speed of materialized views is also increased through reasonable resource allocation.

[0032] One possible implementation involves allocating new views to multiple engines based on their data processing capabilities, resulting in target new views matching the various engines. This includes: determining the usable space of a first storage space based on the storage space occupied by deleted views and the storage space occupied by retained views after state updates; determining the view cost of the new view based on its query value and the storage space it occupies; and, with usable space as a constraint and maximum view cost as the objective, allocating new views to multiple engines based on their data processing capabilities, thus obtaining target new views matching the various engines. Based on this, materialized views with higher query value can be stored in limited storage space, thereby increasing the success rate of hitting materialized views during user queries and achieving query acceleration.

[0033] One possible implementation includes: determining a first ratio between the data volume of the materialized view and the data volume of the corresponding base table data, and determining a second ratio between the data volume of the query view and the data volume of the corresponding base table data; storing the base table data in a third storage space; obtaining the storage space occupied by the materialized view by multiplying the first ratio by the storage space occupied by the base table data; and obtaining the storage space occupied by the query view by multiplying the second ratio by the storage space occupied by the base table data. Based on this, by determining the ratio between the data volume of the view and the corresponding base table data, and by multiplying the ratio by the storage space occupied by the corresponding base table data, the storage space occupied by the view can be quickly obtained.

[0034] As one possible implementation, it also includes: responding to a query request, parsing the query request to obtain the corresponding request information; if the request information targets a materialized view in the first storage space, retrieving the query result corresponding to the request information from the first storage space; if the request information does not target a materialized view in the first storage space, retrieving the query result corresponding to the request information from the third storage space. Based on this, performing queries in two ways can improve the reliability of the query and ensure the correctness and consistency of the query results.

[0035] As one possible implementation, after retrieving the query results corresponding to the requested information from the third storage space, the method also includes updating the query view in the second storage space. Based on this, the accuracy of the query view can be improved.

[0036] Thirdly, an electronic device is provided, comprising: a communication interface for communicating with other electronic devices; a memory for storing computer program instructions; and a processor for executing the computer program instructions to support the electronic device in implementing the methods of any possible implementation of the first aspect.

[0037] Fourthly, a computer-readable storage medium is provided, on which computer program instructions are stored, which, when executed by a processing circuit, implement the method as in any possible implementation of the first aspect.

[0038] Fifthly, a chip system is provided, comprising processing circuitry and a storage medium storing computer program instructions; when executed by the processing circuitry, the computer program instructions implement the method as described in any possible implementation of the first aspect. The chip system may be composed of chips or may include chips and other discrete devices. Attached Figure Description

[0039] Figure 1 This is a schematic diagram of the materialized view technology provided in the embodiments of this application.

[0040] Figure 2 This is a schematic diagram of the interface for constructing a materialized view in a conventional way.

[0041] Figure 3 This is a schematic diagram of the typical materialized view construction process.

[0042] Figure 4 This is a schematic diagram of the architecture of a materialized view update system provided in an embodiment of this application.

[0043] Figure 5 The system functional architecture diagram provided for the embodiments of this application.

[0044] Figure 6 This is one of the flowcharts illustrating the method for updating a materialized view provided in an embodiment of this application.

[0045] Figure 7 This is a second schematic flowchart illustrating the method for updating a materialized view as provided in an embodiment of this application.

[0046] Figure 8 This is the third flowchart illustrating the method for updating a materialized view provided in an embodiment of this application.

[0047] Figure 9 This is the fourth flowchart illustrating the method for updating a materialized view as provided in the embodiments of this application.

[0048] Figure 10 The fifth flowchart illustrates the method for updating a materialized view as provided in the embodiments of this application.

[0049] Figure 11 This is the sixth flowchart illustrating the method for updating a materialized view as provided in the embodiments of this application.

[0050] Figure 12 This is a schematic diagram illustrating the update process of the materialized view provided in an embodiment of this application. Detailed Implementation

[0051] The technical solutions of the embodiments of this application will be described below with reference to the accompanying drawings. In the description of the embodiments of this application, unless otherwise stated, " / " means "or," for example, A / B can mean A or B; "and / or" in this text is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. Furthermore, in the description of the embodiments of this application, "multiple" refers to two or more than two.

[0052] Hereinafter, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of this embodiment, unless otherwise stated, "a plurality of" means two or more.

[0053] As described in the background section, the industry currently commonly uses materialized view technology to improve query speed. For materialized view technology, data is imported and stored in the database using base tables. When a user queries, the query data (i.e., the materialized view) is pre-constructed to accelerate the query and improve efficiency. Because the materialized view extracts and pre-computes the user's query data in advance, pre-computation time is saved during the query, thus improving query performance.

[0054] like Figure 1 As shown, Figure 1 This is a schematic diagram illustrating the principle of materialized view technology. After data is written to the database from an external source, the database can store the written data in the form of base tables. When constructing a materialized view, data can be selected based on the base tables in the database to build the materialized view. The process of building from the base tables to the materialized view involves pre-computation / data aggregation of the data. When a user issues a query request, the query routing analyzes the user's query request. If the user's requested data is in the materialized view table, the route is routed to the materialized view, and the request result is read directly from the materialized view; otherwise, the route is routed to the base table data, the base table data is read again, and calculations are performed on the base table data according to the user's request content to obtain the result.

[0055] Currently, the construction of materialized views typically involves manually selecting data from a base table according to specific requirements. For example... Figure 2 As shown, Figure 2This is a schematic diagram of the interface for constructing a materialized view using conventional techniques. When a user needs to construct a materialized view, they can select dimension attributes from the dimension options in the interface, and select measure attributes from the measure options. Dimension attributes describe the properties of columns in the base table, while measure attributes are pre-calculated based on different calculation methods, such as sum, max, min, and average. Figure 2 In the example, the user selected three dimension attributes: "Dimension Attribute_3", "Dimension Attribute_4", and "Dimension Attribute_5". They chose to perform calculations on these three dimension attributes using either maximum or summation. Assuming that maximum calculation yields the result "Result_1" and summation yields the result "Result_2", after the user clicks the "OK" button, the query engine constructs and stores the materialized view based on the user's selections. If a subsequent user query is a subset of {Dimension Attribute_3, Dimension Attribute_4, Dimension Attribute_5} and {Result_1, Result_2}, for example, a query request for {Dimension Attribute_3, Dimension Attribute_4, Result_1} or a query request for {Dimension Attribute_5, Result_1}, then the materialized view will be hit. The query engine will then attempt to rewrite the query execution plan, provided that the logical semantics are satisfied. The new execution plan can directly access the calculated results data of the materialized view, avoiding recalculation of metric values ​​and thus accelerating the query speed.

[0056] Please refer to Figure 3 , Figure 3 This is a schematic diagram illustrating the materialized view construction process in conventional techniques. For example... Figure 3 As shown, conventionally, when building a materialized view, the user needs to manually select columns from the base table to construct the view, and then construct the materialized view based on the selected view; the materialized view is then loaded into the cache of the specified query engine. When a query request is received, if the query request hits the materialized view, the materialized view query is triggered, and the queried calculation result data is retrieved from the materialized view.

[0057] Conventional techniques have the following problems when constructing materialized views: (1) Since it is necessary to manually filter column options one by one from the base table, it is difficult to locate the required columns when the base table has hundreds of columns or the table definition is unclear, resulting in a large amount of time consumption, high configuration cost and low configuration efficiency. (2) Since the selection of views is entirely based on experience, inexperienced personnel or developers who are not familiar with the business cannot configure high-value materialized views, that is, the success rate of users' queries hitting the configured materialized views is low. At the same time, when the query behavior changes or the system environment changes, the materialized views in the cache are invalid views, and it is necessary to reselect and configure the materialized views, resulting in the delay of materialized views and failing to achieve the purpose of query acceleration. (3) Conventional techniques only support single-engine offline data analysis scenarios processed by Spark and cannot support real-time data analysis scenarios. Real-time data primarily refers to data with low latency, such as milliseconds, seconds, minutes, or hours. This is also known as "streaming" data processing. Data is generated continuously like water, and is immediately extracted, cleaned, transformed, and loaded (ETL). Examples include early warning and monitoring analysis, and fault diagnosis, which require real-time performance and fall under the category of real-time data analysis scenarios. Offline data generally refers to data from a date T-1. For example, if today's date T=2023-06-15, the data results will only include business data from the previous day, 2023-06-14. Offline data processing is also known as "batch processing." Examples include business operations analysis and financial analysis where accuracy is more important than timeliness, which fall under the category of offline data analysis scenarios. Offline data analysis scenarios only support large-granularity data processing methods such as daily, weekly, and monthly data; real-time online data analysis scenarios support small-granularity data processing methods such as 5-minute, 15-minute, and 1-hour data. (4) Conventional techniques require deleting invalid materialized views from the cache before reconfiguring and loading new materialized views into the cache, resulting in high configuration costs. Furthermore, since the deletion of invalid materialized views is done manually, failures or omissions in deleting invalid materialized views can lead to a backlog of invalid materialized views. Figure 1 Storing materialized views directly in the cache will waste cache space, while the effective materialized views cannot be loaded into the cache, thus failing to achieve the goal of accelerating queries.

[0058] Based on the above considerations, this application provides a method for updating materialized views, which can automatically update materialized views according to user query behavior. Compared with the existing method of manually configuring materialized views, this not only reduces the configuration cost and improves configuration efficiency, but also, because the update is driven by user query behavior, the updated materialized view is more accurate, can meet user query needs, and increases the success rate of users hitting the materialized view during queries, thus achieving the goal of query acceleration. Furthermore, this application's method of automatically updating materialized views based on user query behavior can adapt to both offline and real-time data analysis scenarios, and can also automatically replace invalid materialized views in the cache, improving cache utilization.

[0059] To facilitate understanding, explanations of the relevant terms are provided first.

[0060] Materialized views are tables that are pre-aggregated from the results of a query and belong to the category of materialized views.

[0061] A query view is a virtual table defined by a query consisting of SELECT statements; it does not contain any data itself.

[0062] Service Level Agreement (SLA): This outlines the level of service that a supplier commits to provide to a customer. The agreement specifies metrics such as uptime, delivery time, response time, and resolution time.

[0063] Base table: The original table loaded into the database.

[0064] Please see Figure 4 , Figure 4 This is a schematic diagram of the architecture of a materialized view updating system provided in an embodiment of this application. Figure 4 As shown, the materialized view update system includes electronic device 100 and terminal device 200.

[0065] In this system, users can send data to electronic devices 100 via terminal device 200. For example, terminal device 200 can provide an interactive interface for users to send query requests to electronic devices 100. Upon receiving the query request, electronic device 100 performs a data query, obtains the query results, and returns the results to terminal device 200. After receiving the query results, terminal device 200 can display them to the user through the interactive interface. In some embodiments, terminal device 200 can also deploy an interactive query tool. Users can send query requests to electronic devices through this tool. Upon receiving the query request, electronic device 100 performs a data query, obtains the query results, and returns the results to terminal device 200, thus providing the user with the query results.

[0066] In the embodiments of this application, the terminal device 200 may be, but is not limited to, personal computers, servers, tablet computers, mobile phones, and other devices.

[0067] In this embodiment, the electronic device 100 may include a processor 111, a memory 112, and a communication interface 113. The communication interface 113 is used to communicate with other electronic devices. For example, it can receive query requests sent by the terminal device 200 and send query results to the terminal device 200.

[0068] The processor can be a central processing unit (CPU) or other specific integrated circuits. Processor 111 can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. In practical applications, electronic device 100 may also include multiple processors. Processor 111 may include one or more processor cores. An operating system and other software programs are installed in processor 111, enabling processor 111 to access memory 112 and various peripheral interconnect devices.

[0069] Processor 111 is connected to memory 112 via a double data rate (DDR) bus or other type of bus. Memory 112 is typically used to store executable program code for a computer program. Exemplarily, the computer program may include operating system programs and application programs. The operating system may include, but is not limited to, Symbian®, Android®, Microsoft® Windows®, Apple iOS®, Blackberry®, Harmony® OS, etc. The executable program code includes instructions. Processor 111 executes various functional applications and data processing of the electronic device by running the instructions stored in memory 112. Memory 112 may include a program storage area and a data storage area. The program storage area may store the operating system, applications required for at least one function, etc. The data storage area may store data created during the use of the electronic device (such as input events).

[0070] Furthermore, memory 112 may include high-speed random access memory, and may also include non-volatile memory, such as at least one disk storage device, flash memory device, universal flash storage (UFS), etc. Processor 111 executes various functional applications and data processing of the electronic device by running instructions stored in memory 112.

[0071] In this embodiment, memory 112 further includes a cache for storing instructions or data that the processor 111 has just used or is using repeatedly, such as the materialized view described in this embodiment. If the processor 111 needs to use the instruction or data again, it can directly retrieve it from the cache, avoiding repeated access, reducing the processor 111's waiting time, and thus improving the efficiency of the query.

[0072] In this embodiment of the application, the electronic device 100 is also equipped with a variety of engines, such as the Spark computing engine, the Flink computing engine, and a database engine, through which data querying and materialized view construction are realized.

[0073] It is understood that this application Figure 4The structures shown do not constitute a specific limitation on the electronic device. In other embodiments of this application, the electronic device may include more or fewer components than shown, or combine some components, or split some components, or arrange different components. The components may be implemented in hardware, software, or a combination of both. For example, the electronic device in the embodiments of this application may also include a charging management module, a power management module, a sensor module, an audio module, etc. For details on the specific working principles and functions of these modules, please refer to the descriptions in conventional technology.

[0074] In this embodiment, the electronic device 100 may be a server or a server cluster built by multiple servers. This embodiment does not impose any restrictions on the specific type of electronic device.

[0075] In this embodiment, the materialized view update system also provides a first database 300 and a second database 400. The first database stores base table data, and the electronic device 100 can access the first database to retrieve data from the base table data. The second database stores user query records and other configuration data (such as user information, device information, etc.), and the electronic device 100 can access the second database to retrieve query records. Understandably, one query request corresponds to one query record.

[0076] Optionally, in this embodiment, the materialized view is stored in a first storage space, the query view is stored in a second storage space, the base table data is stored in a third storage space, and the query records are stored in a fourth storage space. The first storage space may be a cache memory located in the electronic device 100, the second and third storage spaces may be located in a first database, and the fourth storage space may be located in a second database. In some embodiments, the second storage space may also be located in the memory of the electronic device 100, which can be specifically set according to business needs; this embodiment does not impose specific limitations.

[0077] based on Figure 4 Please refer to the system architecture shown below. Figure 5 , Figure 5This is a system functional architecture diagram provided for embodiments of this application. When the materialized view update system provided in this application is running, it can implement the following functions: view selection, view generation, view routing, and query requests. View selection refers to analyzing the views produced by each base table based on the user's query records and identifying high-value views from among them. View generation refers to pre-aggregating and calculating the identified high-value views using various engines (such as Spark computing engine, Flink computing engine, etc.), i.e., materializing them to obtain materialized views. View routing refers to storing the calculated materialized views in a first storage space. When a query request is received from a user, if the materialized view is matched, the result data can be directly read from the materialized view. For query requests that do not match a materialized view, the result data is obtained from the base table, and a query view is generated based on the query request and stored in a second storage space. Query requests refer to queries sent by the user through a terminal device.

[0078] Based on the functions achievable by the update system, the memory in the electronic device provided in this application embodiment may include the following software functional modules: a query behavior awareness module, a view engine generation awareness module, and a view data volume awareness module. Understandably, each software functional module is composed of executable program code. Specifically, the view data volume awareness module describes changes in view data volume and determines high-value views based on user query records. The view engine generation awareness module describes changes in engine load. During the view generation stage, it selects the most cost-effective engine to materialize the determined high-value views, obtaining new materialized views. These new materialized views are then stored in a first storage space, updating the original materialized views stored in the first storage space, thereby updating the materialized views. The query behavior awareness module records and describes user query behavior, such as which tables are queried and the query distribution of columns in each table.

[0079] Based on the above description of the materialized view update system, the materialized view update in this embodiment can also be achieved through the following process: First, based on the user's query records, high-value views are identified. Then, based on the load changes of each engine, the engine with the optimal cost is selected to materialize the identified high-value views, resulting in new materialized views. These new materialized views are then stored in a first storage space, and the original materialized views stored in the first storage space are updated, thereby achieving the materialized view update. When the electronic device receives a query request sent by the user through a terminal device, it parses the query request, determines the request information corresponding to the query request, and if the request information matches a materialized view in the first storage space, the result data corresponding to the request information is retrieved from the materialized view in the first storage space. If the request information does not match a materialized view in the first storage space, the result data corresponding to the request information is retrieved from the base table data, and a query view is generated based on the query request and stored in a second storage space.

[0080] The data in the base table is obtained by ETL of the original data and is stored in the third storage space. Each query request sent by the user is stored in the fourth storage space, and each query request is a query record. This allows for the identification of high-value views by analyzing the query records of each view.

[0081] based on Figure 4 and Figure 5 The system architecture shown in this application embodiment provides a method for updating a materialized view. This method is applied to a materialized view updating system and is executed by an electronic device 100 within the system. Figure 6 As shown, Figure 6 This is a flowchart illustrating a method for updating a materialized view according to an embodiment of this application. The method for updating a materialized view according to an embodiment of this application may include the following steps.

[0082] S310: Retrieve the first query record of the materialized view.

[0083] In this embodiment, when a user performs a data query, the query request sent includes request information such as the requested table name, dimension information, and metric information. For example, a query request might indicate a request for data in columns 3 and 4 of table A; in this case, the request information would be {table A, dimension attribute_3, dimension attribute_4}. By parsing the query request, the requested information can be obtained, and then a query record is generated based on that information. Understandably, the generated query record also includes the requested information.

[0084] To facilitate the statistics of query records, in this embodiment of the application, the generated query records also include the request time of the corresponding query request.

[0085] In this embodiment, each query record corresponds to a view, and the corresponding view contains the request information requested by that query record. For example, if a query record contains the request information {Table A, Dimension Attribute_3, Dimension Attribute_4}, then the view corresponding to that query record also contains the information {Table A, Dimension Attribute_3, Dimension Attribute_4}. It is understood that the request information is only used to indicate which specific data is to be requested, or in other words, to indicate the storage location of the requested data, and is not the data itself.

[0086] Each query record can correspond to either a materialized view or a query view. Understandably, a materialized view contains the actual data corresponding to the requested information, while a query view does not contain any actual data.

[0087] In this application embodiment, different query records can correspond to the same view. When a view corresponds to multiple query records, it indicates that the view has been queried multiple times.

[0088] In this embodiment, the first query record is a query record for the materialized view. Obtaining the first query record of the materialized view can be based on a trigger mechanism. For example, it can be triggered according to set conditions; when the set conditions are met, the first query record of the materialized view can be obtained. The set conditions can be set based on business requirements and are not specifically limited.

[0089] In this embodiment of the application, the triggering mechanism can be a time mechanism, which retrieves the first query record of the materialized view according to a time period.

[0090] Optionally, in this embodiment, retrieving the first query record of the materialized view according to a time period can be done by retrieving all the first query records of the materialized view at set time intervals, or by retrieving the first query record of the materialized view for the current period at set time intervals. For example, at the end of period t, the first query record of the materialized view within period t can be retrieved, or at the end of period t, the first query record of the materialized view within period t and all periods before period t can be retrieved. The time period can be set based on business needs. For example, in real-time data scenarios, the time period can be set according to small-granularity metrics such as seconds, minutes, and hours. In batch data scenarios, the time period can be set according to large-granularity metrics such as days, weeks, and months.

[0091] In an alternative implementation, the first query record of the materialized view can also be obtained in response to an instruction sent by another electronic device. For example, upon receiving an instruction from another electronic device, the first query record of the materialized view can be obtained in response to that instruction.

[0092] To facilitate data management, in this embodiment, the materialized view and the query record can be stored in different storage spaces. For example, the materialized view can be stored in a first storage space, and the query record can be stored in a fourth storage space different from the first storage space.

[0093] In an optional implementation, when obtaining the first query record of the materialized view, the stored query record can be retrieved from the fourth storage space first. The request information contained in the query record is compared with the request information of the materialized view to obtain the query record that matches the request information of the materialized view. Understandably, the query record that matches the request information of the materialized view is the first query record of the materialized view.

[0094] Since the query records include the request time, if you need to select query records for a specific time period, you can directly select them based on the request time and the corresponding time period. For example, the materialized view_View1 has query records X1, X2, and X3. Assuming the request time for record X1 is T1, the request time for record X2 is T2, and the request time for record X3 is T3, if you want to select query records for the time period t1, T1 and T2 are within the t1 time period, while T3 is not. Therefore, records X1 and X2 are the first query records selected by materialized view_View1.

[0095] S320: Based on the first query record, obtain the view value of the materialized view.

[0096] The view value of a materialized view is used to characterize the coverage of user query requirements by the materialized view. The higher the view value of a materialized view, the higher the coverage of user query requirements by the materialized view. The lower the view value of a materialized view, the lower the coverage of user query requirements by the materialized view.

[0097] In an optional implementation, a corresponding query record interval can be set for each view value. After obtaining the first query record of the materialized view, the first query record of the materialized view can be counted to obtain the total number of records of the first query record. Then, the query record interval in which the total number of records is located is determined, and the view value corresponding to the query record interval in which the total number of records is located is determined as the view value of the materialized view.

[0098] In an optional implementation, the first query record of the materialized view can be directly counted to obtain the total record data of the first query record, and the total number of records of the first query record can be determined as the view value of the materialized view.

[0099] In an optional implementation, considering that there are multiple materialized views in the first storage space, in addition to obtaining the view value of the materialized view based on the first query record, it is also possible to accumulate the first query records of all materialized views and calculate the average number of query records for the materialized view, and then determine the average number of query records as the view value of the materialized view.

[0100] It is understood that the above-described embodiments are merely examples and are not intended to limit the specific value of the materialized view obtained from the first query record in the embodiments of this application.

[0101] S330: If the view value of the materialized view is less than the preset threshold, update the materialized view in the first storage space.

[0102] In this embodiment, a higher view value indicates that the materialized view can better cover the user's query needs, and most user queries can hit the materialized view, directly obtaining query results from it, thus achieving query acceleration. Conversely, a lower view value indicates that the materialized view cannot cover the user's query needs, and most user queries cannot hit the materialized view, meaning query results cannot be directly obtained from it, thus failing to achieve query acceleration.

[0103] After obtaining the value of the materialized view, it can be compared with a preset threshold. If the value of the materialized view is greater than the preset threshold, it indicates that the materialized view currently stored in the first storage space can effectively cover the user's query needs, and query acceleration can be achieved using the materialized view currently stored in the first storage space, thus eliminating the need to update the materialized view in the first storage space. Conversely, if the value of the materialized view is less than the preset threshold, it indicates that the materialized view currently stored in the first storage space cannot cover the user's query needs, and query acceleration cannot be achieved using the materialized view currently stored in the first storage space, thus requiring an update of the materialized view in the first storage space.

[0104] In an optional implementation, the materialized view in the first storage space may be updated when the view value of the materialized view is equal to or less than a preset threshold.

[0105] In this embodiment, the preset threshold is related to business requirements. The size of the preset threshold can be set according to business needs. The smaller the preset threshold, the less frequently the materialized view is updated, and the less significant the query acceleration effect. Conversely, the larger the preset threshold, the more frequently the materialized view is updated, and the more significant the query acceleration effect.

[0106] This application embodiment determines the view value of a materialized view by querying its value, and updates the materialized view based on that value, thus achieving automatic updating of the materialized view. Compared to the existing method of manually configuring materialized views, this not only reduces the configuration cost and improves configuration efficiency, but also ensures that the updated materialized view is more accurate because its value is determined based on query records. This can meet the user's query needs, increase the success rate of the user's query hitting the materialized view, and achieve the goal of accelerating the query process.

[0107] To improve the accuracy of the view value of a materialized view, this application embodiment considers introducing a second query record of the query view, combining the first query record of the materialized view and the second query record of the query view to obtain the view value of the materialized view. Please refer to [reference needed]. Figure 7 In this embodiment of the application, the step of obtaining the view value of the materialized view based on the first query record may further include: S321: Perform statistics on the first query record and the second query record of the query view respectively to obtain the query value of the materialized view and the query value of the query view.

[0108] Here, a query view refers to a view generated based on a query request after the request information corresponding to the query request does not match the materialized view in the first storage space. In this embodiment, the query view is stored in a second storage space, which is a different storage space from the first storage space.

[0109] Understandably, before performing statistics on the second query records of the query view, it is also necessary to obtain the second query records of the query view. Accordingly, in this embodiment, obtaining the second query records of the query view can also be based on a triggering mechanism, which can also be a time-based mechanism, i.e., obtaining the second query records of the query view according to a time period.

[0110] Optionally, the first query record of the materialized view and the second query record of the query view can be retrieved synchronously or asynchronously, without any specific restrictions.

[0111] Similar to the process of obtaining the first query record of the materialized view, in this embodiment of the application, for the query view, the stored query record can also be obtained from the fourth storage space first, and the request information contained in the query record can be compared with the request information of the query view to obtain the query record that matches the request information of the query view. It can be understood that the query record that matches the request information of the query view is the second query record of the query view.

[0112] It should be noted that, in order to ensure the accuracy of the view value of the obtained materialized view, in this embodiment of the application, the first query record of the materialized view and the second query record of the query view need to be from the same time period, that is, the first query record of the materialized view and the second query record of the query view need to be obtained from the same time period.

[0113] In this embodiment, the query value of a view can be used to characterize the activity level of the view being queried. A higher query value indicates higher query activity, meaning the view is queried more frequently. Conversely, a lower query value indicates lower query activity, meaning the view is queried less frequently. The view can be a materialized view or a query view.

[0114] In this embodiment of the application, statistics are performed on the first query record and the second query record of the query view to obtain the query value of the materialized view and the query value of the query view. This means that statistics are performed on the first query record to obtain the query value of the materialized view, and statistics are performed on the second query record to obtain the query value of the query view.

[0115] In an optional implementation, statistics are performed on the first query records and the second query records of the query view to obtain the query value of the materialized view and the query value of the query view. This can be achieved by determining the first query frequency of the materialized view within a set time period based on the first query records of the materialized view within that time period, and then using that first query frequency as the query value of the materialized view. Similarly, the second query frequency of the query view within a set time period is determined based on the second query records of the query view within that time period, and then used as the query value of the query view. The query frequency can be obtained by dividing the total number of query records within the set time period by the duration of the set time period. For example, materialized view V... i If the number of records in the first query within a given time period (let's say period t) is N, then the materialized view V... i The first query frequency in period t is N / t period duration.

[0116] Considering that both materialized views and query views retrieve query records within the same time period, to improve processing efficiency and reduce computational complexity, the number of the first retrieved query records can be accumulated to obtain a first cumulative sum, which is then used to determine the query value of the materialized view. Similarly, the number of the second retrieved query records can be accumulated to obtain a second cumulative sum, which is also used to determine the query value of the query view. For example, the retrieved materialized view V... i If the number of records in the first query is N, then the materialized view V i The query value is N.

[0117] S322: Obtain the first view value based on the query value of the materialized view and the query value of the query view, and obtain the second view value corresponding to the materialized view based on the query value of the materialized view.

[0118] Here, the first view value represents the total view value corresponding to the materialized view and the query view. To obtain the first view value, after obtaining the query values ​​of the materialized view and the query view, the query values ​​of all materialized views and all query views are summed.

[0119] The value of the second view is the sum of the query values ​​of all materialized views. After obtaining the query values ​​of the materialized views, summing the query values ​​of all materialized views yields the value of the second view.

[0120] First View Value ,in, For materialized view V i The value of the query For the value of the second view, For query view V l The query value, where m and k are integers.

[0121] S323: The ratio of the value of the second view to the value of the first view is determined as the view value of the materialized view.

[0122] The ratio of the value of the second view to the value of the first view represents the proportion of the value of the second view in the value of the first view.

[0123] View value of materialized views ,in, .

[0124] This application embodiment considers the second query record of the query view. By determining the proportion of the second view value of the materialized view to the total view value corresponding to all views (query view and materialized view), it can effectively reflect the coverage of user query needs by the materialized view, thereby effectively improving the accuracy of the view value of the materialized view.

[0125] Considering that in practical applications, user query frequency may vary with business needs—for example, query frequency may be higher during certain time periods and lower during others—this application embodiment can determine the query value of a view based on a time period to improve the success rate of users' queries hitting materialized views. Please refer to [reference needed]. Figure 8 The steps of statistically analyzing the first query record and the second query record of the query view to obtain the query value of the materialized view and the query value of the query view may further include: S3211: Statistically analyze the first and second query records within period T to obtain the first query popularity of the materialized view and the second query popularity of the query view within period T.

[0126] Among them, query popularity can be used to characterize the query statistics of a view. Therefore, in some embodiments, the first query popularity of period T can be obtained by accumulating the number of first query records in period T, and the second query popularity of period T can be obtained by accumulating the number of second query records in period T.

[0127] First search popularity Second search popularity ,in, For materialized view V i The first query popularity in period T is determined by analyzing a single first query record within period T. The sum is obtained by accumulation. For query view V l The second query popularity in period T is determined by a single second query record within period T. The sum is obtained by accumulation.

[0128] S3212: Based on the first query popularity and the query value of the materialized view within the T-1 period, obtain the query value of the materialized view within the T period.

[0129] S3213: Based on the second query popularity and the query value of the query view within period T-1, obtain the query value of the query view within period T.

[0130] In some embodiments, the query value of the materialized view within T periods can be calculated by exponential smoothing based on the first query popularity of T periods and the query value of the materialized view within T-1 periods.

[0131] For example, the materialized view V within period T. i Query value ,in, Materialized view V within period T i The value of the query Materialized view V within period T i The most popular search term Materialized view V within period T-1 i The value of the query a It is an impact factor.

[0132] Correspondingly, the query value of the query view within T period can also be calculated by exponential smoothing, based on the second query popularity of T period and the query value of the query view within T-1 period.

[0133] For example, query view V within period T. l Query value ,in, For querying view V within period T l The value of the query For querying view V within period T l The most popular search term For querying view V within period T-1 l The value of the query a It is an impact factor.

[0134] in, , a The larger the value, the greater the impact of the current period's query popularity on the current period's query value, and vice versa. Therefore... , The value of is a non-negative number.

[0135] For example, taking the query value of a materialized view as an example, in the first cycle, Materialized view V of the first cycle i The query value is equal to the first query popularity value in the first period; in the second period, Materialized view V of the second cycle i The query value is equal to the first query popularity value in the second period plus the query value in the first period.

[0136] Based on the above embodiments, it can be seen that in this application embodiment, when determining the query value of a view in the form of a time period, it is calculated by recursion. That is, when calculating the query value of period T, it is necessary to calculate the revenue value of period T-1.

[0137] Understandably, after obtaining the query values ​​of the materialized views and the query values ​​of the query views within period T, summing the query values ​​of all materialized views and the query values ​​of all query views within period T yields the first view value within period T. Summing the query values ​​of all materialized views within period T yields the second view value within period T.

[0138] That is, the first view value of the T-cycle. ,in, Materialized view V within period T i The value of the query The value of the second view of the materialized view within period T. For querying view V within period T l The query value.

[0139] Accordingly, the view value of the materialized view within period T is .

[0140] After obtaining the view value of the materialized view within period T, the view value of the materialized view within period T can be compared with a preset threshold. If the view value of the materialized view within period T is greater than the preset threshold, it indicates that the materialized view stored in the first storage space within period T can well cover the user's query needs, and therefore there is no need to update the materialized view in the first storage space within period T. If the view value of the materialized view within period T is less than or equal to the preset threshold, it indicates that the materialized view stored in the first storage space within period T cannot cover the user's query needs, and therefore it is necessary to update the materialized view in the first storage space within period T.

[0141] This application's embodiments update materialized views based on time periods, which can effectively update materialized views according to business characteristics, improving the success rate of user queries hitting the materialized views and achieving query acceleration. Furthermore, updating materialized views based on time periods is adaptable to both offline and real-time data analysis scenarios. For example, for offline data analysis scenarios, time periods can be set according to large-granularity time units such as days, weeks, and months; for real-time data analysis scenarios, time periods can be set according to small-granularity time units such as milliseconds, seconds, and hours.

[0142] To further improve the accuracy of user queries in hitting materialized views in the first storage space and accelerate queries, in this embodiment, the materialized views in the first storage space can be updated based on query value. Please refer to the following references. Figure 9 The steps for updating the materialized view in the first storage space may include: S331: Sort the materialized view and the query view according to the query value to obtain the view sequence.

[0143] The sorting of materialized views and query views based on query value can be done either by sorting them from largest to smallest query value, or by sorting them from smallest to largest query value. This embodiment of the application does not impose any specific limitations.

[0144] A view sequence is used to represent materialized views and query views sorted according to query value. The view sequence includes sorted materialized views and query views.

[0145] For example, by sorting the materialized view and query view of period T from largest to smallest query value, a view sequence can be obtained. ,in, For T-period view V n The value of the query Indicates to The elements in the view are sorted. The view Vn can be a materialized view or a query view. n is the sum of the number of materialized views in period T and the number of query views in period T, i.e., n = k + m, where k is the number of materialized views in period T and m is the number of query views in period T.

[0146] S332: Store the views in the view sequence that meet the preset conditions in the first storage space.

[0147] The preset conditions can be set according to business needs, and this application embodiment does not impose specific limitations. For example, the preset conditions can be to select the top N views with the highest query value from the view sequence and store them in the first storage space, where N is a positive integer.

[0148] Specifically, after obtaining a view that meets the preset conditions from the view sequence, the view that meets the preset conditions can be materialized and stored in the first storage space to replace the materialized view in the first storage space, thereby updating the materialized view in the first storage space.

[0149] This application embodiment stores views that meet preset conditions in a view sequence in a first storage space to update the materialized views stored in the first storage space. This allows high-value views to be stored in the first storage space, improving the success rate of users hitting materialized views during queries.

[0150] Considering that materializing all views that meet the preset conditions would consume a lot of resources and be inefficient, please refer to the following for improvement in update efficiency and resource utilization: Figure 10 The step of storing views that meet preset conditions in the view sequence in the first storage space may include: S3321: Determine the theoretical view from the view sequence based on preset conditions.

[0151] In some embodiments, in order to select a view with higher query value, the preset condition may be to determine the theoretical view from the view sequence according to the size of the query value, such that after the theoretical view is stored in the first storage space, the query value of the materialized view stored in the first storage space is greater than the query value of the query view stored in the second storage space.

[0152] In some embodiments, considering that the first storage space has space limitations, the preset condition may also be to determine the theoretical view from the view sequence based on the total storage space of the first storage space, such that the difference between the total storage space of the first storage space and the sum of the storage spaces corresponding to the determined theoretical view is less than a preset space threshold.

[0153] To quickly determine the storage space occupied by the view, please refer to [the relevant documentation / reference]. Figure 11 In this embodiment of the application, the storage space occupied by the view can be obtained through steps S410-S420.

[0154] S410: Determine the first ratio of the data volume of the materialized view to the data volume of the corresponding base table, and determine the second ratio of the data volume of the query view to the data volume of the corresponding base table.

[0155] The base table data corresponding to the materialized view refers to the data included in the base table used to construct the materialized view, while the base table data corresponding to the query view refers to the data included in the base table of the query view's corresponding request.

[0156] The base table data is stored in the third storage space, and the amount of data in the base table can be obtained by statistically analyzing the base table in the third storage space. For example, the amount of data in the base table can be obtained by statistically analyzing the columns and rows of the base table.

[0157] The amount of data in a materialized view can be obtained by statistically analyzing the data used when constructing the materialized view. For example, by statistically analyzing the column data and row data used, the amount of data in the materialized view can be obtained.

[0158] The amount of data in a query view can be obtained by statistically analyzing the data requested by the query request corresponding to the query view. For example, the amount of data in the query view can be obtained by statistically analyzing the column data and row data requested by the query request corresponding to the query view.

[0159] Determining the first ratio of the data volume of the materialized view to the data volume of the corresponding base table is equivalent to calculating the ratio of the data volume of the materialized view to the data volume of the corresponding base table. This ratio is the first ratio of the data volume of the materialized view to the data volume of the corresponding base table.

[0160] For example, the first proportion = the amount of data in the materialized view / the amount of data in the base table corresponding to the materialized view.

[0161] Determining the second ratio of the data volume of the query view to the data volume of the corresponding base table is equivalent to calculating the ratio of the data volume of the query view to the data volume of the corresponding base table. This ratio is the second ratio of the data volume of the query view to the data volume of the corresponding base table.

[0162] For example, the second percentage = the amount of data in the query view / the amount of data in the base table corresponding to the query view.

[0163] S420: The storage space occupied by the materialized view is obtained by multiplying the first proportion by the storage space occupied by the base table data corresponding to the materialized view, and the storage space occupied by the query view is obtained by multiplying the second proportion by the storage space occupied by the base table data corresponding to the query view.

[0164] The storage space occupied by the base table data can be obtained by accessing the third storage space.

[0165] The storage space occupied by a materialized view can be obtained by multiplying a first proportion by the storage space occupied by the corresponding base table data. For example, the storage space occupied by the materialized view = the first proportion. The storage space occupied by the base table data corresponding to the materialized view.

[0166] Accordingly, the storage space occupied by the query view can be obtained by multiplying the second proportion by the storage space occupied by the corresponding base table data. For example, the storage space occupied by the query view = the second proportion. The storage space occupied by the base table data corresponding to the query view.

[0167] In this embodiment of the application, the storage space occupied by the view can be quickly obtained by determining the ratio of the data volume of the view to the data volume of the corresponding base table, and by multiplying the ratio of the data volume by the storage space occupied by the corresponding base table data.

[0168] Understandably, since the theoretical view is determined based on the materialized view and the query view, and is a subset of the materialized view and the query view, the storage space corresponding to the theoretical view can be obtained after obtaining the storage space occupied by the materialized view and the query view.

[0169] In order to select views with high query value within a limited storage space, the preset condition in this embodiment may be to determine the theoretical view from the view sequence based on the magnitude of the query value and the total storage space of the first storage space, such that the difference between the total storage space of the first storage space and the sum of the storage spaces corresponding to the determined theoretical view is less than a preset space threshold, and after storing the theoretical view in the first storage space, the query value of the materialized view stored in the first storage space is greater than the query value of the query view stored in the second storage space.

[0170] For example, when determining a theoretical view from the view sequence based on preset conditions, theoretical views can be selected from the view sequence in order of query value, and the difference between the total storage space of the first storage space and the sum of the storage spaces corresponding to all selected theoretical views is less than a preset space threshold.

[0171] The following explanation uses a T-period view sequence as an example. In this sequence, the query values ​​of the views are sorted from largest to smallest, and the selected theoretical view can be denoted as: And satisfy: ; in, Represented as a set of theoretical views, The query value in the view sequence is The view, The query value in the view sequence is The view, , This is the total storage space of the first storage space. For query value The storage space occupied by the view, where D is a preset space threshold.

[0172] D can be set according to business needs. If the first storage space needs to have a large amount of extra space to store other data, the value of D can be set to a larger value. If the first storage space needs to store more materialized views, the value of D can be set to a smaller value.

[0173] In an optional implementation, in order to store more high-value materialized views, when determining a theoretical view from the view sequence based on preset conditions, theoretical views can be selected sequentially from the view sequence according to the query value until a view is selected, such that the sum of the storage space of the selected view and the storage space of all the previously selected theoretical views is just greater than or equal to the total storage space of the first storage space.

[0174] For example, a view sequence with a period of T is used as an example for illustration. In this sequence, the query values ​​of the views are sorted from largest to smallest, and the selected theoretical view can be denoted as: Then it needs to satisfy ,and .

[0175] In an optional implementation, after determining the theoretical view from the view sequence, the determined theoretical view can be materialized and stored in the first storage space, and the materialized view in the first storage space can be replaced to update the materialized view in the first storage space.

[0176] S3322: Compare the theoretical view with the materialized view to obtain the added view, deleted view, and retained view.

[0177] Since the theoretical view is selected from the view sequence, and the view sequence is obtained by sorting the query view and materialized view according to the query value, the selected theoretical view may contain both materialized view and query view, or it may contain only materialized view or query view.

[0178] To improve update efficiency and resource utilization, this application embodiment considers only materializing the theoretical views that need to be added to the first storage space, deleting invalid materialized views, and updating the status of the retained materialized views. Therefore, after obtaining the theoretical view, the theoretical view can be compared with the materialized views in the first storage space to determine the deleted views, the new views that need to be added to the first storage space, and the retained views.

[0179] Among them, a newly added view refers to a view that is not stored in the first storage space but is included in the theoretical view; a deleted view refers to a view that is stored in the first storage space but is not in the theoretical view; and a retained view refers to a view that is stored in the first storage space and is included in the theoretical view. Understandably, comparing the theoretical view with the materialized view in the first storage space can be done by comparing the request information of the theoretical view with the request information of the materialized view in the first storage space. For any materialized view in the first storage space, if a theoretical view with request information matching the request information of that materialized view can be obtained, then that materialized view is determined to be a retained view; if a theoretical view with request information matching the request information of that materialized view cannot be obtained, then that materialized view is determined to be a deleted view. Correspondingly, for any theoretical view, if a materialized view with request information matching the request information of that theoretical view cannot be obtained from the first storage space, then that theoretical view is determined to be a newly added view.

[0180] For example, the collection of deleted views can be represented as: ,in, For the set of deleted views in period T, The set of theoretical views represented as T-periods. This is the set of materialized views in the first storage space of period T. , This represents the materialized view V1 in the first storage space of cycle T. V represents the materialized view in the first storage space of cycle T. k The first storage space of cycle T contains k materialized views.

[0181] The set of reserved views can be represented as: ,in, A set of retained views for period T.

[0182] The collection of newly added views can be represented as: ,in, A collection of newly added views for period T.

[0183] S3323: Update the state of the reserved views in the first storage space, delete the deleted views from the first storage space, and materialize the newly added views and store them in the first storage space.

[0184] For deleted views, since their query value is low, the success rate of being hit by queries is low when they are stored in the first storage space, which cannot achieve the purpose of query acceleration. Therefore, deleted views need to be removed from the first storage space.

[0185] Since the newly added view is a view that does not exist in the first storage space, it needs to be materialized into a real materialized view before being stored in the first storage space.

[0186] Among them, the data corresponding to the requested information in the new view can be obtained from the base table through the configured query engine, such as Spark or Flink. The obtained data is pre-calculated to construct a materialized view, and the constructed materialized view is stored in the first storage space.

[0187] Retained views are materialized views that need to continue to be stored in the first storage space. Since retained views are already stored in the first storage space, the engine corresponding to the retained view can be used, and only the state is updated. There is no need to re-materialize and load the view into the first storage space.

[0188] For updating the state of a retained view, one could retrieve newly added data from the base table corresponding to the retained view and then update the data in the retained view based on this new data. When updating according to a time period, the state update of a retained view includes both data updates and time status updates. For example, for a retained view with period T... Retrieve the data added to the base table corresponding to the retained view in period T, and based on the added data, process the retained view. The data in the middle is updated, and the time status is changed from period T to period T+1, that is... This indicates that the T+1 period retains the view and it is still stored in the first storage space.

[0189] It should be noted that if the materialized views in the first storage space are updated based on a time period, then after completing step S3323, the time status of all materialized views in the first storage space has been modified to the T+1 period (from the T period to the T+1 period). At the end of the T+1 period, the first query records of the materialized views in the first storage space within the T+1 period will be re-counted. Based on the first query records counted within the T+1 period, the view value of the materialized views in the first storage space within the T+1 period will be re-determined. Based on the view value, it will be determined whether an update will be triggered, and this cycle will continue.

[0190] This embodiment of the application removes invalid materialized views from the first storage space by deleting the deleted view, effectively freeing up the first storage space and avoiding waste. Simultaneously, by updating the state of the retained views and materializing and storing newly added views in the first storage space, this embodiment ensures that views with high query value are stored in the first storage space, improving the hit rate of materialized views in subsequent queries and achieving query acceleration. Furthermore, by only updating the state of the retained views without rematerializing and loading them, this embodiment reduces resource consumption.

[0191] To improve the efficiency of view materialization and adapt to different application scenarios, embodiments of this application are configured with multiple engines. When materializing a new view, an engine matching the new view can be selected for materialization. Therefore, the step of storing the materialized new view in the first storage space may further include: Based on the data processing capabilities of multiple engines, new views are assigned to multiple engines to obtain target new views that match multiple engines.

[0192] After the target view is materialized, it is stored in the first storage space.

[0193] The data processing capacity of each engine represents the amount of data it can process per unit of time. This capacity is determined by the computing resources configured for each engine. These resources are configured according to business needs during engine deployment. The engine's data processing capacity can then be tested using these configured resources. Computing resources refer to factors such as memory size and the number of processor cores.

[0194] In an optional implementation, new views can be assigned to multiple engines based on the query value of the new views and the data processing capabilities of the multiple engines, resulting in target new views that match the multiple engines. Specifically, engines with higher data processing capabilities can be matched with new views that have higher query value. For example, given engines a, b, and c, and a new view V... a Added view V b Added view V c The engine processing power is sorted as Engine a > Engine b > Engine c, and the query value of newly added views is sorted as New View V. a <Add View V> b <Add View V> c This allows us to obtain the target new view matched by engine a as the new view V. c The target view matched by engine b is the newly added view V. b The target view matched by engine c is the newly added view V. a .

[0195] In an optional implementation, considering that different newly added views have different data volumes, and that different data volumes result in different processing times for the engines, to accelerate the generation of materialized views, new views can be assigned to multiple engines based on the data volume of the new views and the data processing capabilities of multiple engines, resulting in target new views matching multiple engines. For example, for each engine, the first data volume currently being processed by the engine is obtained. Based on the engine's data processing capability and a set time threshold, a second data volume that the engine can process under the time threshold is obtained. The difference between the second and first data volumes is used to obtain the target data volume that the engine can still process, i.e., target data volume = second data volume - first data volume. The time threshold can be obtained from the SLA.

[0196] After obtaining the target data volume that the engine can currently process, the target data volume can be matched with the data volume of the new view to obtain the new view that matches the target data volume. The new view that matches the target data volume is the target new view matched by the engine.

[0197] There can be multiple target new views, and the absolute value of the difference between the target data volume and the sum of the data volumes of all target new views is less than or equal to a preset data volume threshold.

[0198] The initial amount of data currently being processed by the engine can be obtained from the amount of data in the view that is loaded into the engine for pre-computation (materialization), for example, view V. a View V b View V c If the data is loaded into the engine for pre-computation, the first amount of data currently being processed by the engine can be view V. a View V b View V c The sum of the data volume of the three views.

[0199] Considering that when a view is deleted, the engine that processed it releases the computing resources consumed in processing the view deletion, and these released resources can then be used to process views with the same amount of data as the deleted view, it is necessary to update the first amount of data currently being processed by the engine after deleting the view from the first storage space. For example, the first amount of data processed by the engine in cycle T is view V. a View V b View V c The sum of the data volumes, if an update operation is performed on the materialized view in the first storage space, will result in view V being... a After being deleted from the first storage space, the first amount of data currently being processed by the engine is updated to view V. b View V c The sum of the data volume.

[0200] After obtaining the target new view matched by each of the multiple engines, the target new view can be added to the corresponding matched engine. The target new view is then materialized by the corresponding matched engine, that is, pre-calculation / pre-aggregation and other operations are performed on the target new view to obtain the new materialized view. The new materialized view is then stored in the first storage space, thereby realizing the update of the materialized view in the first storage space.

[0201] According to the embodiments of this application, the engine is allocated new views based on its data processing capabilities. When generating materialized views, the engine with the best cost can be selected to improve resource utilization. At the same time, the generation speed of materialized views is also improved through reasonable resource allocation.

[0202] Considering that the total storage space of the first storage space is limited, in order to store materialized views with higher query value within the limited storage space, in this embodiment of the application, new views can be allocated to engines based on the available space of the first storage space and the query value of the new views. Therefore, allocating new views to multiple engines based on the data processing capabilities of multiple engines to obtain target new views matching multiple engines may further include: The usable space of the first storage space is determined based on the storage space occupied by the deleted view and the storage space occupied by the retained view after the state update.

[0203] The view cost of a new view is determined by the query value of the new view and the storage space it occupies.

[0204] Constrained by available space and with the maximum view cost as the objective, new views are allocated to multiple engines based on their data processing capabilities, resulting in target new views that match multiple engines.

[0205] The storage space occupied by the retained view represents the space in the first storage space that has been used, while the storage space occupied by the deleted view represents the usable space released from the first storage space.

[0206] Considering that the storage space occupied by the retained view will change after the data of the retained view is updated, this application embodiment obtains the usable space of the first storage space based on the storage space occupied by the retained view after the state update and the storage space occupied by the deleted view.

[0207] The usable space of the first storage space is the total storage space of the first storage space minus the storage space occupied by the retained view after its state update, plus the storage space occupied by the deleted view. For example, .

[0208] in, The usable space in the first storage space during the T+1 cycle. This is the total storage space of the first storage space. To preserve the storage space occupied by the view after a state update, i.e., the storage space already used in the first storage space in cycle T+1, It is the storage space occupied by the deleted view calculated in cycle T, or it can be expressed as the storage space released in the first storage space in cycle T+1.

[0209] In this embodiment, the view cost of adding a new view can be represented as the ratio of the query value of the new view to the storage space occupied by the new view. For example, the view cost of adding a new view... , .in, The set of newly added views obtained from the calculation over period T. To enhance the query value of the newly added view, Storage space occupied by newly added views. The cost of adding a new view.

[0210] In an optional implementation, new view combinations that can be stored in the first storage space can be determined based on the available space of the first storage space and the storage space occupied by the new views. Each new view combination includes at least one new view. For each new view combination, the view costs of each new view in the new view combination are accumulated to obtain the cumulative view cost of the new view combination. Then, based on the data processing capabilities of multiple engines, the new views in the new view combination corresponding to the maximum cumulative view cost are allocated to obtain the target new views matched by each of the multiple engines.

[0211] The process of allocating new views in the new view combination corresponding to the maximum cumulative view cost based on the data processing capabilities of multiple engines to obtain the target new views matched by each engine can be referred to the above description and will not be repeated here.

[0212] It should be noted that in this embodiment of the application, the use space of the first storage space is used as a constraint, the maximum view cost is the objective, and the allocation of new views to multiple engines based on the data processing capabilities of multiple engines is a resource allocation problem. It can also be implemented by dynamic programming technology. The technical principle of dynamic programming technology can be referred to the introduction of conventional technology, and will not be elaborated here.

[0213] This application embodiment uses the usable space of the first storage space as a constraint and the maximum view cost as the objective to allocate new views to multiple engines. It can store materialized views with higher query value in a limited storage space, thereby improving the success rate of hitting materialized views when users query, and achieving the purpose of query acceleration.

[0214] Please see Figure 12 , Figure 12 This is a flowchart illustrating the update process of a materialized view provided in an embodiment of this application. After obtaining the deleted view, the added view, and the retained view, the retained view is updated using its corresponding engine. The deleted view is removed, the data processing volume of each engine is updated, the view cost of each added view is calculated, and based on the view cost and the data processing volume of each engine, the target added view matched by each engine is obtained. The matched target added view is materialized based on each engine, generating a materialized view and loading it into the first storage space.

[0215] Based on the constructed materialized view, the method provided in this application embodiment may further include the following steps: In response to a query request, the query request is parsed to obtain the corresponding request information.

[0216] If the requested information is a materialized view in the first storage space, retrieve the query results corresponding to the requested information from the first storage space.

[0217] If the requested information does not target a materialized view in the first storage space, retrieve the query results corresponding to the requested information from the third storage space.

[0218] The request information corresponding to the query request can be the query request information, such as table name, dimension information, and metric information.

[0219] When determining whether the request information matches the materialized view, it can be checked whether the materialized view includes the request information corresponding to the query request. If it includes the request information corresponding to the query request, it means that the request information is targeting the materialized view in the first storage space. Then, the result data corresponding to the request information can be obtained from the materialized view that includes the request information corresponding to the query request, and feedback can be provided.

[0220] If no materialized view is detected in the first storage space that includes the request information corresponding to the query request, it means that the request information is not targeting the materialized view in the first storage space. Therefore, the result data corresponding to the request information can be obtained from the base table data in the third storage space and feedback can be provided.

[0221] In this embodiment of the application, for each query request, after parsing the query request, a query record is generated based on the request information requested by the query request and stored in the fourth storage space.

[0222] In this embodiment, after obtaining the result data corresponding to the request information from the base table data in the third storage space, the query view in the second storage space can be updated based on the query request. Updating the query view in the second storage space can involve detecting whether the query view in the second storage space includes the request information from the query request. If a query view exists that includes the request information, it is retained. If no query view includes the request information, a query view is generated based on the request information and stored in the second storage space, thereby updating the query view in the second storage space.

[0223] The method provided in this application constructs a materialized view. When a user's query request hits the materialized view, the query result can be directly obtained from the materialized view; when the user's query request does not hit the materialized view, the query result can be obtained from the base table data. Thus, by using these two methods, the reliability of the query can be improved, ensuring the correctness and consistency of the query results.

[0224] The materialized view update method provided in this application, through self-driven updates, not only reduces view configuration costs and improves configuration efficiency, but also, because the update is driven by user query behavior, the updated materialized view is more accurate and adaptive, meeting user query needs and increasing the success rate of matching the materialized view during user queries, thus accelerating queries. Furthermore, this application embodiment, by matching engine differences and selecting an appropriate engine to construct the materialized view, can shield batch processing and stream processing engines, enabling simultaneous support for offline batch processing and real-time stream processing scenarios.

[0225] Based on the same inventive concept, this application provides an electronic device, which can be the electronic device described in the above embodiments. Specifically, the terminal device may include a communication interface, a memory, a processor, and one or more computer programs. These components can be connected via one or more communication buses. The one or more computer programs are stored in the memory and configured to be executed by the one or more processors. The one or more computer programs include instructions that can be used to perform the relevant steps executed by the electronic device in the above embodiments.

[0226] This application also provides a computer-readable storage medium storing computer program instructions, which, when executed by a processing circuit, implement the functions or steps in the above-described materialized view update method.

[0227] Furthermore, embodiments of this application may also provide a chip system, which includes a processing circuit and a storage medium. The storage medium stores computer program instructions that, when executed by the processing circuit, implement the functions or steps in the above-described method for updating the materialized view.

[0228] Through the above description of the embodiments, those skilled in the art will clearly understand that, for the sake of convenience and brevity, only the division of the above functional modules is used as an example. In practical applications, the above functions can be assigned to different functional modules as needed, that is, the internal structure of the device can be divided into different functional modules to complete all or part of the functions described above. The specific working process of the system, device, and unit described above can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0229] In the embodiments of this application, the functional units can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0230] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solutions of the embodiments of this application, essentially, or the parts that contribute to the prior art, or all or part of the technical solutions, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as flash memory, portable hard disk, read-only memory, random access memory, magnetic disk, or optical disk.

[0231] The above description is merely a specific implementation of the embodiments of this application, but the protection scope of the embodiments of this application is not limited thereto. Any changes or substitutions within the technical scope disclosed in the embodiments of this application should be covered within the protection scope of the embodiments of this application. Therefore, the protection scope of the embodiments of this application should be determined by the protection scope of the claims.

Claims

1. A method for updating a materialized view, characterized in that, include: Obtain the first query record of the materialized view, which is stored in the first storage space; Based on the first query record, the view value of the materialized view is obtained, and the view value of the materialized view is used to characterize the coverage of the materialized view to the user's query needs. If the value of the materialized view is less than a preset threshold, the materialized view in the first storage space is updated. The preset threshold is related to business requirements. The step of obtaining the view value of the materialized view based on the first query record includes: The first query record and the second query record of the query view are statistically analyzed to obtain the query value of the materialized view and the query value of the query view, wherein the query view is stored in the second storage space; the query view is a view generated based on the query request after the request information corresponding to the query request does not match the materialized view in the first storage space. A first view value is obtained based on the query value of the materialized view and the query value of the query view, and a second view value corresponding to the materialized view is obtained based on the query value of the materialized view, wherein the first view value represents the total view value corresponding to the materialized view and the query view. The ratio of the second view value to the first view value is determined as the view value of the materialized view.

2. The method according to claim 1, characterized in that, The step of statistically analyzing the first query record and the second query record of the query view to obtain the query value of the materialized view and the query value of the query view includes: Statistical analysis is performed on the first query record and the second query record within the T-period to obtain the first query popularity of the materialized view and the second query popularity of the query view within the T-period. Based on the first query popularity and the query value of the materialized view within the T-1 period, the query value of the materialized view within the T period is obtained. The query value of the query view within the T period is obtained based on the second query popularity and the query value of the query view within the T-1 period.

3. The method according to claim 1 or 2, characterized in that, Updating the materialized view in the first storage space includes: The materialized view and the query view are sorted according to the query value to obtain a view sequence; Views that meet the preset conditions in the view sequence are stored in the first storage space.

4. The method according to claim 3, characterized in that, The step of storing views that meet preset conditions in the view sequence in the first storage space includes: The theoretical view is determined from the view sequence based on the preset conditions; The theoretical view is compared with the materialized view to obtain a new view, a deleted view, and a retained view. The new view is a view that is not stored in the first storage space but is included in the theoretical view. The deleted view is a view that is stored in the first storage space but is not in the theoretical view. The retained view is a view that is stored in the first storage space and is included in the theoretical view. The state of the retained view in the first storage space is updated, the deleted view is deleted from the first storage space, and the newly added view is materialized and stored in the first storage space.

5. The method of claim 4, wherein, The difference between the total storage space of the first storage space and the sum of the storage spaces corresponding to the theoretical view is less than a preset space threshold.

6. The method according to claim 4 or 5, characterized in that, After the theoretical view is stored in the first storage space, the query value of the materialized view stored in the first storage space is greater than the query value of the query view stored in the second storage space.

7. The method of claim 4, wherein, The step of materializing the newly added view and storing it in the first storage space includes: Based on the data processing capabilities of multiple engines, new views are assigned to the multiple engines to obtain target new views that match the multiple engines; The newly added view of the target is materialized and stored in the first storage space.

8. The method of claim 7, wherein, The step of assigning new views to the multiple engines based on their data processing capabilities, and obtaining target new views that match the multiple engines, includes: The usable space of the first storage space is determined based on the storage space occupied by the deleted view and the storage space occupied by the retained view after the state update. The view cost of the newly added view is obtained based on the query value of the newly added view and the storage space occupied by the newly added view. With the available space as a constraint and the maximum view cost as the objective, new views are allocated to the multiple engines based on their data processing capabilities, resulting in target new views that match the multiple engines.

9. The method according to claim 1, 2, 4, 5, 7, or 8, characterized in that, The method further includes: A first ratio is determined between the data volume of the materialized view and the data volume of the corresponding base table data, and a second ratio is determined between the data volume of the query view and the data volume of the corresponding base table data; the base table data is stored in a third storage space. The storage space occupied by the materialized view is obtained by multiplying the first proportion by the storage space occupied by the base table data corresponding to the materialized view. The storage space occupied by the query view is obtained by multiplying the second proportion by the storage space occupied by the base table data corresponding to the query view.

10. The method according to claim 1, 2, 4, 5, 7, or 8, characterized in that, The method further includes: In response to a query request, the query request is parsed to obtain the request information corresponding to the query request; If the request information is directed to a materialized view in the first storage space, retrieve the query result corresponding to the request information from the first storage space; If the requested information is not directed to a materialized view in the first storage space, the query result corresponding to the requested information is obtained from the third storage space.

11. The method according to claim 10, characterized in that, After obtaining the query result corresponding to the request information from the third storage space, the method further includes: Update the query view in the second storage space.

12. An electronic device, comprising: include: A communication interface used for communicating with other electronic devices; Memory is used to store computer program instructions; A processor for executing the computer program instructions to support the electronic device in implementing the method as described in any one of claims 1-11.

13. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer program instructions that, when executed by a processing circuit, implement the method as described in any one of claims 1-11.

14. A chip system, characterized by The chip system includes a processing circuit and a storage medium, wherein the storage medium stores computer program instructions; when the computer program instructions are executed by the processing circuit, they implement the method as described in any one of claims 1-11.

Images