Data warehouse management method and device and data warehouse system

Abstract

The invention provides a data warehouse management method and device and a data warehouse system.The method comprises the steps that the CPU utilization rate, unit network delay and single-time memory read-write time consumption of an execution node are obtained, and the execution node is used for conducting data storage and data query on a data warehouse; obtaining the data volume of read-write data of each read-write task to obtain the read-write data volume of each read-write task; according to the CPU utilization rate of the execution node corresponding to each read-write task, the unit network delay, the single memory read-write time consumption and the read-write data volume of each read-write task, calculating to obtain the predicted execution time consumption of each read-write task; the read-write tasks are subjected to priority ranking according to the predicted execution time consumption, the priority of each read-write task is obtained, the execution node is controlled to execute the read-write tasks according to the priority sequence of each read-write task, the priority of the read-write task with the minimum predicted execution time consumption is the highest, and the priority of the read-write task with the minimum predicted execution time consumption is the highest. The problem of data deadlock or resource contention easily occurring in multi-task scheduling in the prior art is solved.

Description

Technical Field

[0001] This application relates to the field of data warehousing, and more specifically, to a data warehouse management method, apparatus, computer-readable storage medium, and data warehouse system. Background Technology

[0002] Data warehouse systems are used for data storage and resource management, and are suitable for massive data analysis scenarios such as finance, e-commerce, and the Internet of Things.

[0003] Existing data warehouse systems have the following problems:

[0004] 1. Execution plans implemented using a static optimizer cannot be dynamically adjusted, leading to performance degradation in complex queries (such as multi-table JOINs). Therefore, it is difficult to meet the requirements for high performance.

[0005] 2. Existing solutions implement configuration through external calls, which can easily lead to deadlocks or resource contention due to multiple task calls. Therefore, it is difficult to meet the requirements for high performance. Summary of the Invention

[0006] The main objective of this application is to provide a data warehouse management method, apparatus, computer-readable storage medium, and data warehouse system to at least solve the problems of data deadlock or resource contention that easily occur in multi-task scheduling in the prior art.

[0007] To achieve the above objectives, according to one aspect of this application, a data warehouse management method is provided, comprising: obtaining the CPU utilization, unit network latency, and single memory read / write time of an execution node, wherein the unit network latency is the network latency for the execution node to transmit a unit amount of data, and the single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory in a single operation, wherein the execution node is used for data storage and data querying of the data warehouse; obtaining the data volume of each read / write task to obtain the read / write data volume of each read / write task; calculating the estimated execution time of each read / write task based on the CPU utilization, unit network latency, single memory read / write time, and read / write data volume of the execution node corresponding to each read / write task; prioritizing the read / write tasks according to the estimated execution time to obtain the priority of each read / write task; controlling the execution node to execute the read / write tasks in the priority order of each read / write task, wherein the read / write task with the shortest estimated execution time has the highest priority.

[0008] Optionally, the estimated execution time of each read / write task is calculated based on the CPU utilization, unit network latency, single memory read / write time, and read / write data volume of the execution node corresponding to each read / write task. This includes: calculating the unit processing time of the execution node using Cost = I / O Cost × (1 + 100CPUusage) + Network Latency × γ, where Cost is the unit processing time, I / O Cost is the single memory read / write time, CPUusage is the CPU utilization, γ is the network latency weight, and the unit processing time is the processing time for a unit data volume; and calculating the product of the read / write data volume and the unit processing time for each read / write task to obtain the estimated execution time of each read / write task.

[0009] Optionally, prioritizing the read / write tasks based on the estimated execution time to obtain the priority of each read / write task, and controlling the execution node to execute the read / write tasks in the order of their priorities, includes: establishing a directed acyclic graph (DAG) based on the dependencies of each read / write task, where the nodes of the DAG are the read / write tasks and the edges of the DAG are the dependencies; generating multiple task paths based on the DAG, each task path including at least one read / write task; calculating the sum of the estimated execution times of the read / write tasks in each task path to obtain the path time of each task path; prioritizing the task paths based on their path times to obtain the priority of each task path; and controlling the execution node to execute the read / write tasks of each task path in the order of their priorities.

[0010] Optionally, controlling the execution node to execute the read and write tasks in order of priority includes: adjusting the priority of the read and write tasks according to the execution duration of the read and write tasks, so that the priority of the read and write tasks decreases by a predetermined number for each predetermined execution duration.

[0011] Optionally, controlling the execution node to execute the read and write tasks according to their priority order includes: when there is one execution node, controlling the execution node to execute the read and write tasks according to their priority order; an allocation step, when there are multiple execution nodes, allocating the highest priority read and write task to the execution node with the highest resource score, wherein the resource score is negatively correlated with CPU utilization, memory usage, disk usage, and unit network latency; a calculation step, calculating the standard deviation of the resource scores of the multiple execution nodes after allocation; a secondary allocation step, when the standard deviation is greater than or equal to a predetermined value, reallocating the highest priority read and write task so that the standard deviation of the resource score of the execution node is less than the predetermined value; repeating the allocation step, the calculation step, and the secondary allocation step at least once in sequence until all read and write tasks are allocated.

[0012] Optionally, controlling the execution node to execute the read and write tasks in order of priority includes: determining the initial number of threads by the minimum value of twice the number of CPU cores of the execution node and the number of data shards of the data warehouse; starting threads of the initial number of threads and executing the read and write tasks in order of priority.

[0013] Optionally, before obtaining the CPU utilization, unit network latency, and single memory read / write time of the execution node, the method further includes: if the read / write task is a query task, comparing the SQL of the query task with a pre-stored SQL, wherein the pre-stored SQL is the SQL in the memory of the execution node that stores the query results; if the SQL of the query task is the same as the target pre-stored SQL, reading the query result corresponding to the target pre-stored SQL from the memory of the execution node, wherein the target pre-stored SQL is any one of the pre-stored SQLs.

[0014] According to another aspect of this application, a data warehouse management device is provided, comprising: a first acquisition unit, configured to acquire the CPU utilization rate, unit network latency, and single memory read / write time of an execution node, wherein the unit network latency is the network latency for the execution node to transmit a unit amount of data, and the single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory in a single operation, wherein the execution node is used to store and query data in the data warehouse; a second acquisition unit, configured to acquire the data volume of each read / write task, thereby obtaining the read / write data volume of each read / write task; a calculation unit, configured to calculate the estimated execution time of each read / write task based on the CPU utilization rate, unit network latency, single memory read / write time, and read / write data volume of the execution node corresponding to each read / write task; and an execution unit, configured to prioritize the read / write tasks according to the estimated execution time, thereby obtaining the priority of each read / write task, and controlling the execution node to execute the read / write tasks in the order of their priorities, wherein the read / write task with the shortest estimated execution time has the highest priority.

[0015] According to another aspect of this application, a computer-readable storage medium is provided, the computer-readable storage medium including a stored program, wherein, when the program is executed, it controls the device where the computer-readable storage medium is located to perform any of the data warehouse management methods described above.

[0016] According to another aspect of this application, a data warehouse system is provided, comprising: a data warehouse, an execution node, one or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs including a method for performing any of the data warehouse management methods described herein.

[0017] By applying the technical solution of this application, the data warehouse management method described above can determine the data processing time per unit amount of data by obtaining the CPU utilization, unit network latency, and single memory read / write time of the execution node. Then, the estimated execution time of the read / write task is calculated based on the amount of data read / written. The priority of the read / write task is configured according to the estimated execution time, with the read / write task having a higher priority for the task with the shorter estimated execution time. This ensures that the read / write task with shorter execution time is completed quickly, while the read / write task with longer execution time is executed later. This avoids the problem of long-term data locking or resource occupation by long-term read / write tasks, which prevents other read / write tasks from being executed. This solves the problem of data deadlock or resource contention that is prone to occur in multi-task scheduling in the prior art. Attached Figure Description

[0018] The accompanying drawings, which form part of this application, are used to provide a further understanding of this application. The illustrative embodiments and descriptions of this application are used to explain this application and do not constitute an undue limitation of this application. In the drawings:

[0019] Figure 1 A hardware structure block diagram of a mobile terminal for performing a data warehouse management method according to an embodiment of this application is shown;

[0020] Figure 2 A flowchart illustrating a data warehouse management method according to an embodiment of this application is shown.

[0021] Figure 3 A structural block diagram of a data warehouse management apparatus provided according to an embodiment of this application is shown.

[0022] The above figures include the following reference numerals:

[0023] 102. Processor; 104. Memory; 106. Transmission device; 108. Input / output device. Detailed Implementation

[0024] It should be noted that, unless otherwise specified, the embodiments and features described in this application can be combined with each other. This application will now be described in detail with reference to the accompanying drawings and embodiments.

[0025] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0026] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate for the embodiments of this application described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0027] As described in the background section, existing multi-task scheduling is prone to data deadlock or resource contention. To solve this problem, embodiments of this application provide a data warehouse management method, apparatus, computer-readable storage medium, and data warehouse system.

[0028] The technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention.

[0029] The methods and embodiments provided in this application can be executed on a mobile terminal, a computer terminal, or a similar computing device. Taking running on a mobile terminal as an example, Figure 1 This is a hardware structure block diagram of a mobile terminal for a data warehouse management method according to an embodiment of the present invention. Figure 1 As shown, a mobile terminal may include one or more ( Figure 1 Only one is shown in the diagram. A processor 102 (which may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.) and a memory 104 for storing data are also shown. The mobile terminal may further include a transmission device 106 for communication functions and an input / output device 108. Those skilled in the art will understand that... Figure 1 The structure shown is for illustrative purposes only and does not limit the structure of the mobile terminal described above. For example, the mobile terminal may also include components that are more... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.

[0030] The memory 104 can be used to store computer programs, such as application software programs and modules, like the computer program corresponding to the data warehouse management method in this embodiment of the invention. The processor 102 executes various functional applications and data processing by running the computer program stored in the memory 104, thereby implementing the above-described method. The memory 104 may include high-speed random access memory and non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include memory remotely located relative to the processor 102, and these remote memories can be connected to the mobile terminal via a network. Examples of the aforementioned networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof. The transmission device 106 is used to receive or send data via a network. Specific examples of the aforementioned networks may include wireless networks provided by the mobile terminal's communication provider. In one example, the transmission device 106 includes a network interface controller (NIC), which can be connected to other network devices via a base station to communicate with the Internet. In one example, the transmission device 106 may be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0031] This embodiment provides a method that runs on a mobile terminal, computer terminal, or similar computing device. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Also, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0032] Figure 2 This is a flowchart of a data warehouse management method according to an embodiment of this application. Figure 2 As shown, the method includes the following steps:

[0033] Step S201: Obtain the CPU utilization, unit network latency, and single memory read / write time of the execution node. The unit network latency is the network latency for the execution node to transmit a unit amount of data. The single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory once. The execution node is used to store data and query data in the data warehouse.

[0034] Specifically, Prometheus is used to collect CPU / memory / network metrics of the execution node, namely CPU utilization, single memory read / write time, and unit network latency, with a sampling frequency of 100ms.

[0035] Step S202: Obtain the data volume of each read / write task to get the data volume of each of the above read / write tasks;

[0036] Specifically, different read / write tasks involve different amounts of data, resulting in different task times.

[0037] Step S203: Calculate the estimated execution time of each read / write task based on the CPU utilization of the execution node corresponding to each read / write task, the unit network latency, the single memory read / write time, and the amount of read / write data for each read / write task.

[0038] Specifically, the data processing time per unit of data volume can be determined based on the CPU utilization of the execution node, the network latency per unit, and the time consumed per single memory read / write operation. Then, the estimated execution time of the read / write task can be calculated based on the amount of data read / written by the read / write task.

[0039] Step S204: Prioritize the read and write tasks according to the estimated execution time to obtain the priority of each read and write task, and control the execution node to execute the read and write tasks in the order of their priorities, with the read and write task with the shortest estimated execution time having the highest priority.

[0040] Specifically, read and write tasks with shorter expected execution times have higher priority and are executed first, while tasks with longer expected execution times are executed later.

[0041] In the aforementioned data warehouse management method, the data processing time per unit of data volume can be determined by obtaining the CPU utilization, unit network latency, and single memory read / write time of the execution node. Then, the estimated execution time of the read / write task is calculated based on the amount of data read / written. The priority of the read / write tasks is configured according to the estimated execution time, with higher priority for read / write tasks that have shorter estimated execution times. This ensures that read / write tasks with shorter execution times are completed quickly, while those with longer execution times are executed later. This avoids long-term data locking or resource occupation by long-time read / write tasks, which prevents other read / write tasks from being executed. This solves the problem of data deadlock or resource contention that easily occurs in multi-task scheduling in existing technologies.

[0042] In an optional implementation, to calculate the estimated execution time, step S203 includes:

[0043] Step S2031: The unit processing time of the above execution node is calculated using Cost = I / O Cost × (1 + 100 CPU usage) + Network Latency × γ, where Cost is the unit processing time, I / O Cost is the single memory read / write time, CPU usage is the CPU utilization, γ is the network latency weight, and the unit processing time is the processing time of a unit amount of data.

[0044] Step S2032: Calculate the product of the amount of data read / written for each of the above-mentioned read / write tasks and the unit processing time to obtain the estimated execution time for each of the above-mentioned read / write tasks.

[0045] In the above implementation, a cost model is established: Cost = I / O Cost × (1 + 100 CPU usage) + Network Latency × γ. The unit processing time of the execution node is calculated, and the product of the read / write data volume of each read / write task and the unit processing time is calculated to obtain the expected execution time of each read / write task.

[0046] To account for task dependencies, in an optional implementation, step S204 above includes:

[0047] Step S2041: Establish a directed acyclic graph based on the dependencies of the above-mentioned read and write tasks. The nodes of the directed acyclic graph are the above-mentioned read and write tasks, and the edges of the directed acyclic graph are the above-mentioned dependencies.

[0048] Step S2042: Generate multiple task paths based on the above directed acyclic graph, wherein each task path includes at least one of the above read / write tasks.

[0049] Step S2043: Calculate the sum of the estimated execution times of the read and write tasks of each of the above task paths to obtain the path time of each of the above task paths.

[0050] Step S2044: Sort the task paths by priority according to the path time to obtain the priority of each task path.

[0051] Step S2045: Control the execution node to execute the read and write tasks of the task paths according to the priority order of each task path.

[0052] In the above implementation, the read / write task can be an ETL task. The ETL task is parsed into a directed acyclic graph (such as Apache Atlas metadata). Multiple task paths are generated by dynamic programming based on the directed acyclic graph. The task paths are prioritized according to their execution time using topological sorting. The execution time of a path is the sum of the expected execution times of the read / write tasks in the task path. The execution nodes are controlled to execute the read / write tasks of the task paths according to their priority order. The read / write tasks within a task path are executed in the order of the path to avoid tasks failing to execute due to task dependencies.

[0053] To prevent low-priority tasks from starving, in one optional implementation, step S204 further includes:

[0054] Step S2046: Adjust the priority of the read and write tasks according to the execution duration of the read and write tasks, so that the priority of the read and write tasks decreases by a predetermined number for each predetermined execution duration.

[0055] In the above implementation, a task priority decay factor is set so that the priority of the read and write tasks decreases by a predetermined number of times every predetermined time interval, thereby preventing low-priority tasks from being unable to execute for a long time.

[0056] To ensure load balancing, in an optional implementation, step S204 further includes:

[0057] Step S2047: If there is one execution node, control the execution node to execute the read and write tasks in the order of priority of each read and write task;

[0058] Step S2048, allocation step: when there are multiple execution nodes, the highest priority read / write task is allocated to the execution node with the highest resource score. The resource score is positively correlated with CPU utilization, memory usage, disk usage and unit network latency.

[0059] Step S2049, Calculation step: Calculate the standard deviation of the resource scores of the multiple execution nodes after allocation;

[0060] Step S2050, secondary allocation step: if the standard deviation is greater than or equal to the predetermined value, the highest priority read and write task is reallocated so that the standard deviation of the resource score of the execution node is less than the predetermined value.

[0061] Step S2051: Repeat the above allocation step, the above calculation step, and the above secondary allocation step at least once in sequence until all the above read and write tasks are allocated.

[0062] In the above implementation, the resource score is calculated as 0.4*(1-CPUusage)+0.3*(1-memory utilization)+0.2*(1-disk utilization)+0.1*(1-unit network latency / predetermined network latency). The execution node with the highest resource score is determined as the optimal node, and the highest priority read / write task is assigned to the execution node with the highest resource score. If the cluster load is unbalanced after assignment (standard deviation > 15%), the process is rolled back and a suboptimal solution is attempted.

[0063] To improve execution efficiency, in an optional implementation, step S204 further includes:

[0064] Step S2052: Determine the initial number of threads by the minimum value of twice the number of CPU cores of the above execution node and the number of data shards of the above data warehouse;

[0065] Step S2053: Start the threads of the initial number of threads mentioned above, and execute the read and write tasks in the priority order of each read and write task.

[0066] In the above implementation, the data is automatically fragmented according to the HDFS block size (128MB) to obtain the number of data fragments. The initial number of threads is min(number of CPU cores × 2, number of data fragments). The threads of the above initial number of threads are started, and the above read and write tasks are executed in order of priority to achieve parallel execution of tasks. The task queue is dynamically expanded and shrunk according to the length of the task queue (e.g., adding threads when the queue is >100).

[0067] To improve query efficiency, in one optional implementation, before obtaining the CPU utilization of the execution node, the unit network latency, and the time consumed by a single memory read / write operation, the method further includes:

[0068] Step S301: When the above read / write task is a query task, compare the SQL of the above query task with the pre-stored SQL, where the pre-stored SQL is the SQL in the memory of the above execution node that stores the query results.

[0069] Step S302: If the SQL of the above query task is the same as the target pre-stored SQL, read the query result corresponding to the target pre-stored SQL from the memory of the above execution node. The target pre-stored SQL can be any of the above pre-stored SQL.

[0070] In the above implementation, memoized search essentially stores the results of the data query in the content. When the same query content is encountered during execution, it is directly read from memory without re-execution in the data table. The implementation requires the use of a hash table to store the query SQL, the query results need to be stored in memory, and an eviction mechanism is used to evict SQL queries that have not been referenced for a long time. The Top-3 execution plan is an algorithm to optimize the query, such as the order of SQL joins. It needs to store conditions such as the field length, indexes, and number of tables of all tables, and prioritize joins fields with fewer indexed field lengths.

[0071] This application also provides a data warehouse management device. It should be noted that the data warehouse management device of this application can be used to execute the data warehouse management method provided in this application. This device is used to implement the above embodiments and preferred embodiments; details already described will not be repeated. As used below, the term "module" can refer to a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.

[0072] The following describes the data warehouse management device provided in the embodiments of this application.

[0073] Figure 3 This is a schematic diagram of a data warehouse management device according to an embodiment of this application. Figure 3 As shown, the device includes:

[0074] The first acquisition unit 10 is used to acquire the CPU utilization rate, unit network latency, and single memory read / write time of the execution node. The unit network latency is the network latency for the execution node to transmit a unit amount of data, and the single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory once. The execution node is used to store data and query data in the data warehouse.

[0075] Specifically, Prometheus is used to collect CPU / memory / network metrics of the execution node, namely CPU utilization, single memory read / write time, and unit network latency, with a sampling frequency of 100ms.

[0076] The second acquisition unit 20 is used to acquire the amount of data read and written by each read and write task, and obtain the amount of data read and written by each of the above-mentioned read and write tasks.

[0077] Specifically, different read / write tasks involve different amounts of data, resulting in different task times.

[0078] The calculation unit 30 is used to calculate the estimated execution time of each of the above-mentioned read and write tasks based on the CPU utilization rate of the execution node corresponding to each of the above-mentioned read and write tasks, the unit network latency, the single memory read and write time, and the read and write data volume of each of the above-mentioned read and write tasks.

[0079] Specifically, the data processing time per unit of data volume can be determined based on the CPU utilization of the execution node, the network latency per unit, and the time consumed per single memory read / write operation. Then, the estimated execution time of the read / write task can be calculated based on the amount of data read / written by the read / write task.

[0080] The execution unit 40 is used to prioritize the read and write tasks according to the estimated execution time, obtain the priority of each read and write task, and control the execution node to execute the read and write tasks in the order of priority of each read and write task, with the read and write task with the shortest estimated execution time having the highest priority.

[0081] Specifically, read and write tasks with shorter expected execution times have higher priority and are executed first, while tasks with longer expected execution times are executed later.

[0082] In the aforementioned data warehouse management device, the data processing time per unit of data volume can be determined by acquiring the CPU utilization, unit network latency, and single memory read / write time of the execution node. Then, the estimated execution time of the read / write task is calculated based on the amount of data read / written. The priority of the read / write tasks is configured according to the estimated execution time, with higher priority for read / write tasks that have shorter estimated execution times. This ensures that read / write tasks with shorter execution times are completed quickly, while those with longer execution times are executed later. This avoids long-term data locking or resource occupation by long-time read / write tasks, which prevents other read / write tasks from being executed. This solves the problem of data deadlock or resource contention that easily occurs in multi-task scheduling in existing technologies.

[0083] In one optional implementation, to calculate the estimated execution time, the calculation unit includes:

[0084] The first calculation module is used to calculate the unit processing time of the above execution node using Cost = I / O Cost × (1 + 100 CPU usage) + Network Latency × γ, where Cost is the unit processing time, I / O Cost is the single memory read / write time, CPU usage is the CPU utilization, γ is the network latency weight, and the unit processing time is the processing time of a unit amount of data.

[0085] The second calculation module is used to calculate the product of the amount of data read and written for each of the above-mentioned read and write tasks and the unit processing time, so as to obtain the estimated execution time of each of the above-mentioned read and write tasks.

[0086] In the above implementation, a cost model is established: Cost = I / O Cost × (1 + 100 CPU usage) + Network Latency × γ. The unit processing time of the execution node is calculated, and the product of the read / write data volume of each read / write task and the unit processing time is calculated to obtain the expected execution time of each read / write task.

[0087] To account for task dependencies, in one optional implementation, the execution unit includes:

[0088] A module is established to build a directed acyclic graph based on the dependencies of the aforementioned read and write tasks. The nodes of the directed acyclic graph are the aforementioned read and write tasks, and the edges of the directed acyclic graph are the aforementioned dependencies.

[0089] The generation module is used to generate multiple task paths based on the above directed acyclic graph, wherein the task paths include at least one of the above read / write tasks.

[0090] The third calculation module is used to calculate the sum of the estimated execution times of the read and write tasks of each of the above task paths, and to obtain the path time of each of the above task paths.

[0091] The sorting module is used to prioritize the task paths based on their time consumption, thereby obtaining the priority of each task path.

[0092] The first control module is used to control the execution nodes to execute the read and write tasks of the task paths according to the priority order of each task path.

[0093] In the above implementation, the read / write task can be an ETL task. The ETL task is parsed into a directed acyclic graph (such as Apache Atlas metadata). Multiple task paths are generated by dynamic programming based on the directed acyclic graph. The task paths are prioritized according to their execution time using topological sorting. The execution time of a path is the sum of the expected execution times of the read / write tasks in the task path. The execution nodes are controlled to execute the read / write tasks of the task paths according to their priority order. The read / write tasks within a task path are executed in the order of the path to avoid tasks failing to execute due to task dependencies.

[0094] To prevent low-priority tasks from being starved, in one optional implementation, the execution unit further includes:

[0095] The adjustment module is used to adjust the priority of the read and write tasks according to the execution time of the read and write tasks, so that the priority of the read and write tasks is reduced by a predetermined number for each predetermined execution time.

[0096] In the above implementation, a task priority decay factor is set so that the priority of the read and write tasks decreases by a predetermined number of times every predetermined time interval, thereby preventing low-priority tasks from being unable to execute for a long time.

[0097] To ensure load balancing, in one optional implementation, the execution unit further includes:

[0098] The second control module is used to control the execution node to execute the read and write tasks in the order of priority of each read and write task when there is one execution node.

[0099] The first allocation module is used to perform allocation steps. When there are multiple execution nodes, the highest priority read / write task is allocated to the execution node with the highest resource score. The resource score is positively correlated with CPU utilization, memory usage, disk usage and unit network latency.

[0100] The fourth calculation module is used to perform calculation steps, calculating the standard deviation of the resource scores of the multiple execution nodes mentioned above after allocation;

[0101] The second allocation module is used to perform a secondary allocation step. When the standard deviation is greater than or equal to a predetermined value, the highest priority read and write task is reallocated so that the standard deviation of the resource score of the execution node is less than the predetermined value.

[0102] The repeat module is used to repeat the above allocation steps, the above calculation steps, and the above secondary allocation steps at least once in sequence until all the above read and write tasks are allocated.

[0103] In the above implementation, the resource score is calculated as 0.4*(1-CPUusage)+0.3*(1-memory utilization)+0.2*(1-disk utilization)+0.1*(1-unit network latency / predetermined network latency). The execution node with the highest resource score is determined as the optimal node, and the highest priority read / write task is assigned to the execution node with the highest resource score. If the cluster load is unbalanced after assignment (standard deviation > 15%), the process is rolled back and a suboptimal solution is attempted.

[0104] To improve execution efficiency, in one optional implementation, the execution unit further includes:

[0105] The determination module is used to determine the initial number of threads by taking twice the number of CPU cores of the above execution node and the minimum value of the number of data shards of the above data warehouse;

[0106] The startup module is used to start the threads of the initial number mentioned above, and execute the read and write tasks in the order of priority of each read and write task.

[0107] In the above implementation, the data is automatically fragmented according to the HDFS block size (128MB) to obtain the number of data fragments. The initial number of threads is min(number of CPU cores × 2, number of data fragments). The threads of the above initial number of threads are started, and the above read and write tasks are executed in order of priority to achieve parallel execution of tasks. The task queue is dynamically expanded and shrunk according to the length of the task queue (e.g., adding threads when the queue is >100).

[0108] To improve query efficiency, in one optional embodiment, the above-mentioned apparatus further includes:

[0109] The comparison module is used to compare the SQL of the above query task with the pre-stored SQL before obtaining the CPU utilization, unit network latency and single memory read / write time of the execution node, in the case that the above read / write task is a query task. The pre-stored SQL is the SQL that stores the query results in the memory of the above execution node.

[0110] The reading module is used to read the query result corresponding to the target pre-stored SQL from the memory of the execution node when the SQL of the above query task is the same as the target pre-stored SQL. The target pre-stored SQL can be any of the above pre-stored SQL.

[0111] In the above implementation, memoized search essentially stores the results of the data query in the content. When the same query content is encountered during execution, it is directly read from memory without re-execution in the data table. The implementation requires the use of a hash table to store the query SQL, the query results need to be stored in memory, and an eviction mechanism is used to evict SQL queries that have not been referenced for a long time. The Top-3 execution plan is an algorithm to optimize the query, such as the order of SQL joins. It needs to store conditions such as the field length, indexes, and number of tables of all tables, and prioritize joins fields with fewer indexed field lengths.

[0112] The aforementioned data warehouse management device includes a processor and a memory. The first acquisition unit, the second acquisition unit, the calculation unit, and the execution unit are all stored as program units in the memory. The processor executes the program units stored in the memory to achieve the corresponding functions. All of the above modules are located in the same processor; alternatively, the modules may be located in different processors in any combination.

[0113] The processor contains a kernel, which retrieves the corresponding program units from memory. One or more kernels can be configured, and adjusting kernel parameters can address issues like data deadlocks or resource contention that often occur in multitasking scheduling in existing technologies.

[0114] The memory may include non-permanent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0115] This invention provides a computer-readable storage medium that includes a stored program, wherein the program, when running, controls the device containing the computer-readable storage medium to execute the data warehouse management method.

[0116] Specifically, data warehouse management methods include:

[0117] Step S201: Obtain the CPU utilization, unit network latency, and single memory read / write time of the execution node. The unit network latency is the network latency for the execution node to transmit a unit amount of data. The single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory once. The execution node is used to store data and query data in the data warehouse.

[0118] Specifically, Prometheus is used to collect CPU / memory / network metrics of the execution node, namely CPU utilization, single memory read / write time, and unit network latency, with a sampling frequency of 100ms.

[0119] Step S202: Obtain the data volume of each read / write task to get the data volume of each of the above read / write tasks;

[0120] Specifically, different read / write tasks involve different amounts of data, resulting in different task times.

[0121] Step S203: Calculate the estimated execution time of each read / write task based on the CPU utilization of the execution node corresponding to each read / write task, the unit network latency, the single memory read / write time, and the amount of read / write data for each read / write task.

[0122] Specifically, the data processing time per unit of data volume can be determined based on the CPU utilization of the execution node, the network latency per unit, and the time consumed per single memory read / write operation. Then, the estimated execution time of the read / write task can be calculated based on the amount of data read / written by the read / write task.

[0123] Step S204: Prioritize the read and write tasks according to the estimated execution time to obtain the priority of each read and write task, and control the execution node to execute the read and write tasks in the order of their priorities, with the read and write task with the shortest estimated execution time having the highest priority.

[0124] Specifically, read and write tasks with shorter expected execution times have higher priority and are executed first, while tasks with longer expected execution times are executed later.

[0125] This invention provides a processor for running a program, wherein the program executes the data warehouse management method.

[0126] Specifically, data warehouse management methods include:

[0127] Step S201: Obtain the CPU utilization, unit network latency, and single memory read / write time of the execution node. The unit network latency is the network latency for the execution node to transmit a unit amount of data. The single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory once. The execution node is used to store data and query data in the data warehouse.

[0128] Specifically, Prometheus is used to collect CPU / memory / network metrics of the execution node, namely CPU utilization, single memory read / write time, and unit network latency, with a sampling frequency of 100ms.

[0129] Step S202: Obtain the data volume of each read / write task to get the data volume of each of the above read / write tasks;

[0130] Specifically, different read / write tasks involve different amounts of data, resulting in different task times.

[0131] Step S203: Calculate the estimated execution time of each read / write task based on the CPU utilization of the execution node corresponding to each read / write task, the unit network latency, the single memory read / write time, and the amount of read / write data for each read / write task.

[0132] Specifically, the data processing time per unit of data volume can be determined based on the CPU utilization of the execution node, the network latency per unit, and the time consumed per single memory read / write operation. Then, the estimated execution time of the read / write task can be calculated based on the amount of data read / written by the read / write task.

[0133] Step S204: Prioritize the read and write tasks according to the estimated execution time to obtain the priority of each read and write task, and control the execution node to execute the read and write tasks in the order of their priorities, with the read and write task with the shortest estimated execution time having the highest priority.

[0134] Specifically, read and write tasks with shorter expected execution times have higher priority and are executed first, while tasks with longer expected execution times are executed later.

[0135] This invention provides a data warehouse system, which includes a data warehouse, execution nodes, a processor, a memory, and a program stored in the memory and executable on the processor. When the processor executes the program, it performs at least the following steps:

[0136] Step S201: Obtain the CPU utilization, unit network latency, and single memory read / write time of the execution node. The unit network latency is the network latency for the execution node to transmit a unit amount of data. The single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory once. The execution node is used to store data and query data in the data warehouse.

[0137] Specifically, Prometheus is used to collect CPU / memory / network metrics of the execution node, namely CPU utilization, single memory read / write time, and unit network latency, with a sampling frequency of 100ms.

[0138] Step S202: Obtain the data volume of each read / write task to get the data volume of each of the above read / write tasks;

[0139] Specifically, different read / write tasks involve different amounts of data, resulting in different task times.

[0140] Step S203: Calculate the estimated execution time of each read / write task based on the CPU utilization of the execution node corresponding to each read / write task, the unit network latency, the single memory read / write time, and the amount of read / write data for each read / write task.

[0141] Specifically, the data processing time per unit of data volume can be determined based on the CPU utilization of the execution node, the network latency per unit, and the time consumed per single memory read / write operation. Then, the estimated execution time of the read / write task can be calculated based on the amount of data read / written by the read / write task.

[0142] Step S204: Prioritize the read and write tasks according to the estimated execution time to obtain the priority of each read and write task, and control the execution node to execute the read and write tasks in the order of their priorities, with the read and write task with the shortest estimated execution time having the highest priority.

[0143] Specifically, read and write tasks with shorter expected execution times have higher priority and are executed first, while tasks with longer expected execution times are executed later.

[0144] This application also provides a computer program product, which, when executed on a data processing device, is suitable for executing an initialization program having at least the following method steps:

[0145] Step S201: Obtain the CPU utilization, unit network latency, and single memory read / write time of the execution node. The unit network latency is the network latency for the execution node to transmit a unit amount of data. The single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory once. The execution node is used to store data and query data in the data warehouse.

[0146] Specifically, Prometheus is used to collect CPU / memory / network metrics of the execution node, namely CPU utilization, single memory read / write time, and unit network latency, with a sampling frequency of 100ms.

[0147] Step S202: Obtain the data volume of each read / write task to get the data volume of each of the above read / write tasks;

[0148] Specifically, different read / write tasks involve different amounts of data, resulting in different task times.

[0149] Step S203: Calculate the estimated execution time of each read / write task based on the CPU utilization of the execution node corresponding to each read / write task, the unit network latency, the single memory read / write time, and the amount of read / write data for each read / write task.

[0150] Specifically, the data processing time per unit of data volume can be determined based on the CPU utilization of the execution node, the network latency per unit, and the time consumed per single memory read / write operation. Then, the estimated execution time of the read / write task can be calculated based on the amount of data read / written by the read / write task.

[0151] Step S204: Prioritize the read and write tasks according to the estimated execution time to obtain the priority of each read and write task, and control the execution node to execute the read and write tasks in the order of their priorities, with the read and write task with the shortest estimated execution time having the highest priority.

[0152] Specifically, read and write tasks with shorter expected execution times have higher priority and are executed first, while tasks with longer expected execution times are executed later.

[0153] It is obvious to those skilled in the art that the modules or steps of the present invention described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. They can be implemented using computer-executable program code, and thus can be stored in a storage device for execution by a computing device. In some cases, the steps shown or described can be performed in a different order than those described herein, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the present invention is not limited to any particular combination of hardware and software.

[0154] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program products. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product embodied on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0155] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this application. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart... Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0156] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0157] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0158] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0159] Memory may include non-persistent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0160] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0161] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0162] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element.

[0163] As can be seen from the above description, the embodiments of this application achieve the following technical effects:

[0164] 1) In the data warehouse management method of this application, the data processing time per unit amount of data can be determined by obtaining the CPU utilization, unit network latency and single memory read / write time of the execution node. Then, the expected execution time of the read / write task is calculated based on the amount of data read and written. The priority of the read / write task is configured according to the expected execution time. The read / write task with the shorter the expected execution time has the higher priority, so that the read / write task with short execution time is completed quickly and the read / write task with long execution time is executed later. This avoids the problem that the read / write task with long execution time locks data for a long time or occupies resources, which prevents other read / write tasks from being executed. This solves the problem that data deadlock or resource contention is easy to occur in the multi-task scheduling of the prior art.

[0165] 2) This application parses the ETL task into a directed acyclic graph (such as Apache Atlas metadata), and generates multiple task paths based on the directed acyclic graph using dynamic programming. Topological sorting is used to prioritize the task paths based on their execution time. The path execution time is the sum of the expected execution times of the read and write tasks within each task path. The execution nodes are controlled to execute the read and write tasks within each task path according to their priority order. Read and write tasks within a task path are executed in the order of the path to avoid task failures due to task dependencies. The execution node with the highest resource score is determined as the optimal node, and the highest priority read and write tasks are assigned to this node. If the cluster load is unbalanced after allocation (standard deviation > 15%), a rollback is performed, and a suboptimal solution is attempted.

[0166] The above description is merely a preferred embodiment of this application and is not intended to limit this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.

Claims

1. A data warehouse management method, characterized in that, include: The CPU utilization, unit network latency, and single memory read / write time of the execution node are obtained. The unit network latency is the network latency for the execution node to transmit a unit amount of data, and the single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory once. The execution node is used to store and query data in the data warehouse. Obtain the amount of data read and written for each read and write task to obtain the amount of data read and written for each read and write task; The estimated execution time of each read / write task is calculated based on the CPU utilization of the execution node corresponding to each read / write task, the unit network latency, the single memory read / write time, and the amount of data read / written for each read / write task. The read and write tasks are prioritized according to the estimated execution time to obtain the priority of each read and write task. The execution node is controlled to execute the read and write tasks in the order of their priority, with the read and write task with the shortest estimated execution time having the highest priority.

2. The method according to claim 1, characterized in that, The estimated execution time of each read / write task is calculated based on the CPU utilization of the execution node corresponding to each read / write task, the unit network latency, the time consumed in a single memory read / write operation, and the amount of data read / written for each read / write task, including: The unit processing time of the execution node is calculated using Cost = I / O Cost × (1 + 100CPUusage) + Network Latency × γ, where Cost is the unit processing time, I / O Cost is the time for a single memory read / write operation, CPUusage is the CPU utilization rate, γ is the network latency weight, and the unit processing time is the processing time for a unit amount of data. The estimated execution time of each read / write task is obtained by multiplying the amount of data read / written by the unit processing time for each read / write task.

3. The method according to claim 1, characterized in that, Prioritizing the read / write tasks based on the estimated execution time to obtain the priority of each read / write task, and controlling the execution node to execute the read / write tasks in the order of their priorities, includes: A directed acyclic graph is constructed based on the dependencies of each read / write task, where the nodes of the directed acyclic graph are the read / write tasks and the edges of the directed acyclic graph are the dependencies. Multiple task paths are generated based on the directed acyclic graph, and each task path includes at least one of the read / write tasks. Calculate the sum of the estimated execution times of the read and write tasks for each of the task paths to obtain the path time for each task path; The task paths are prioritized according to their path duration to obtain the priority of each task path; The execution node is controlled to execute the read and write tasks of the task paths in order of priority of each task path.

4. The method according to claim 1, characterized in that, Controlling the execution node to execute the read and write tasks according to their priority order includes: The priority of the read and write tasks is adjusted according to the execution duration of the read and write tasks, so that the priority of the read and write tasks is reduced by a predetermined number of times for each predetermined execution duration.

5. The method according to claim 1, characterized in that, Controlling the execution node to execute the read and write tasks according to their priority order includes: If there is only one execution node, control the execution node to execute the read and write tasks in the order of priority of each read and write task; In the allocation step, when there are multiple execution nodes, the read / write task with the highest priority is allocated to the execution node with the highest resource score. The resource score is negatively correlated with the CPU utilization, memory usage, disk usage, and unit network latency. The calculation step involves calculating the standard deviation of the resource scores of the multiple execution nodes after allocation; In the secondary allocation step, if the standard deviation is greater than or equal to a predetermined value, the highest priority read / write task is reallocated so that the standard deviation of the resource score of the execution node is less than the predetermined value. The allocation step, the calculation step, and the secondary allocation step are repeated at least once in sequence until all the read and write tasks are allocated.

6. The method according to any one of claims 1 to 5, characterized in that, Controlling the execution node to execute the read and write tasks according to their priority order includes: The initial number of threads is determined by the minimum value of twice the number of CPU cores of the execution node and the number of data shards of the data warehouse; Start the threads of the initial number of threads, and execute the read and write tasks in the order of priority of each read and write task.

7. The method according to any one of claims 1 to 5, characterized in that, Before obtaining the CPU utilization of the execution node, the unit network latency, and the time consumed by a single memory read / write operation, the method further includes: When the read / write task is a query task, the SQL of the query task is compared with the pre-stored SQL, which is the SQL in the memory of the execution node that stores the query results. If the SQL of the query task is the same as the target pre-stored SQL, the query result corresponding to the target pre-stored SQL is read from the memory of the execution node, where the target pre-stored SQL is any one of the pre-stored SQLs.

8. A data warehouse management device, characterized in that, include: The first acquisition unit is used to acquire the CPU utilization rate, unit network latency, and single memory read / write time of the execution node. The unit network latency is the network latency for the execution node to transmit a unit amount of data, and the single memory read / write time is the time taken for the execution node to read or write the unit amount of data in memory once. The execution node is used to store data and query data in the data warehouse. The second acquisition unit is used to acquire the amount of data read and written by each read and write task, and obtain the amount of data read and written by each read and write task. The calculation unit is used to calculate the estimated execution time of each read and write task based on the CPU utilization of the execution node corresponding to each read and write task, the unit network latency, the single memory read and write time, and the amount of read and write data of each read and write task. An execution unit is configured to prioritize the read / write tasks based on the estimated execution time, obtain the priority of each read / write task, and control the execution node to execute the read / write tasks in the order of their priorities, wherein the read / write task with the shortest estimated execution time has the highest priority.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a stored program, wherein, when the program is executed, it controls the device on which the computer-readable storage medium is located to perform the data warehouse management method according to any one of claims 1 to 7.

10. A data warehouse system, characterized in that, include: A data warehouse, an execution node, one or more processors, a memory, and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs including a method for performing a data warehouse management procedure according to any one of claims 1 to 7.

Images