Database operation instruction execution method and device, computer equipment, readable storage medium and program product

Abstract

The invention relates to a database operation instruction execution method and device, computer equipment, a computer readable storage medium and a computer program product. The method comprises the following steps: receiving a database operation instruction; the database operation instruction is used for executing data operation on a distributed database; the distributed database stores data in a distributed table through a plurality of fragments; splitting the database operation instruction to obtain a plurality of subtasks; and according to the dependency relationship between the sub-tasks and the task attributes corresponding to the sub-tasks, executing the sub-tasks on the fragments corresponding to the sub-tasks. By adopting the method, the operation and maintenance cost can be reduced.

Description

Technical Field

[0001] The present application relates to the technical field of distributed databases, and in particular to a method, apparatus, computer equipment, computer-readable storage medium, and computer program product for executing database operation instructions. Background Art

[0002] With the development of big data technology, a distributed computing architecture for massively parallel processing, MPP (Massively Parallel Processing), has emerged. This technology supports horizontal expansion, high concurrent queries, and high availability by distributing data to multiple nodes, and performs outstandingly in the storage and processing of massive data. Currently, distributed data management has become the main choice for large-scale data processing.

[0003] As a representative database of the MPP architecture, ClickHouse has been widely used in business systems in the field of real-time data analysis with its excellent performance and reliability, and has gradually become the preferred solution for supporting complex queries and large data processing.

[0004] ClickHouse adopts a Shared Nothing architecture, where each node manages its own metadata independently. Although this design performs well in terms of scalability and concurrency, it also brings new challenges. Since the metadata of each node is independent and the entire cluster lacks a centralized coordination mechanism, node failure or network anomalies may lead to inconsistent metadata status. In addition, when the metadata operation of a node is blocked, it may affect the metadata change process of the entire cluster, thereby increasing the operation and maintenance costs of the system. Summary of the invention

[0005] Based on this, it is necessary to provide a database operation instruction execution method, device, computer equipment, computer-readable storage medium and computer program product that can reduce operation and maintenance costs in response to the above technical problems.

[0006] In a first aspect, the present application provides a method for executing a database operation instruction, comprising:

[0007] Receive a database operation instruction; the database operation instruction is used to perform data operations on a distributed database; the distributed database stores data in a distributed table through multiple shards;

[0008] Splitting the database operation instruction to obtain multiple subtasks;

[0009] According to the dependency relationship between the subtasks and the task attributes corresponding to the subtasks, the subtasks are executed on the slices corresponding to the subtasks.

[0010] In one embodiment, the database operation instruction is split into multiple subtasks, including:

[0011] Parsing the database operation instruction to obtain key information; the key information includes the operation type and the target distributed table;

[0012] Obtaining shard information corresponding to the target distributed table;

[0013] According to the sharding information and the operation type, the database operation instruction is split into a plurality of subtasks.

[0014] In one embodiment, executing each subtask on a slice corresponding to each subtask according to the dependency relationship between the subtasks and the task attributes corresponding to the subtasks includes:

[0015] Determine the execution status of each subtask according to the dependency relationship between the subtasks; the execution status includes an executable state and a suspended state; the executable state indicates that all the predecessor tasks of the subtask have been completed; the suspended state indicates that the subtask has an unfinished predecessor task;

[0016] If the execution state of the subtask is an executable state, the subtask is allocated to a corresponding resource pool according to a task attribute corresponding to the subtask;

[0017] According to the parallelism of the resource pool, each of the subtasks is executed in parallel on the slices corresponding to each of the subtasks.

[0018] In one embodiment, if the execution state of the subtask is an executable state, allocating the subtask to a corresponding resource pool according to a task attribute corresponding to the subtask includes:

[0019] If the execution state of the subtask is an executable state and the task attribute corresponding to the subtask is a lightweight task, allocating the subtask to a lightweight resource pool;

[0020] If the execution status of the subtask is executable and the task attribute corresponding to the subtask is a heavy task, the subtask is assigned to the heavy resource pool; wherein the concurrency of the lightweight resource pool is higher than the concurrency of the heavy resource pool; the heavy task and the lightweight task are determined according to a preset threshold, and the resources required for the lightweight task are less than those for the heavy task.

[0021] In one embodiment, it also includes:

[0022] If the execution state of the subtask is a suspended state, monitoring the predecessor task of the subtask;

[0023] If it is monitored that all the predecessor tasks of the subtask have been completed, the subtask is allocated to the heavy resource pool or the light resource pool according to the task attribute corresponding to the subtask.

[0024] In one embodiment, the shard includes a primary copy and a plurality of secondary copies; and executing each of the subtasks on the shard corresponding to each of the subtasks includes:

[0025] Each of the subtasks is executed on the primary replica corresponding to the subtask, and after completion of execution on the primary replica, the changed content is synchronized to each of the secondary replicas.

[0026] In a second aspect, the present application further provides a database operation instruction execution device, comprising:

[0027] An instruction receiving module, used for receiving a database operation instruction; the database operation instruction is used for performing an operation on a distributed database; the distributed database stores data in a distributed table through multiple shards;

[0028] An instruction splitting module is used to split the database operation instruction into multiple subtasks;

[0029] The instruction execution module is used to execute each subtask on the slice corresponding to each subtask according to the dependency relationship between the subtasks and the task attributes corresponding to each subtask.

[0030] In a third aspect, the present application further provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor implements the steps of the method in any one of the above embodiments when executing the computer program.

[0031] In a fourth aspect, the present application further provides a computer-readable storage medium having a computer program stored thereon, and when the computer program is executed by a processor, the steps of the method in any one of the above embodiments are implemented.

[0032] In a fifth aspect, the present application also provides a computer program product, including a computer program, which implements the steps of the method in any one of the above embodiments when executed by a processor.

[0033] The above-mentioned database operation instruction execution method, device, computer equipment, computer-readable storage medium and computer program product, after receiving the database operation instruction, splits the database operation instruction to obtain multiple subtasks, and finally independently executes each subtask on the shard corresponding to each subtask according to the dependency relationship between the subtasks and the task attributes corresponding to each subtask. Since the dependency relationship between the subtasks is clear and resources are reasonably allocated through task attributes, each subtask can be executed concurrently while ensuring the correctness of the task order. In addition, each subtask is executed independently on the corresponding shard, avoiding resource competition and mutual interference between shards. This method can effectively avoid performance bottlenecks or task blocking problems caused by centralized execution, thereby reducing system operation and maintenance costs and improving overall execution efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0035] Figure 1 A schematic diagram of a flow chart of a method for executing a database operation instruction in one embodiment;

[0036] Figure 2 A schematic diagram of instruction splitting in an embodiment;

[0037] Figure 3 A schematic diagram of a subtask execution flow in an embodiment;

[0038] Figure 4 A flowchart of subtask execution in one embodiment;

[0039] Figure 5 A schematic diagram of an architecture of a distributed database after a node crashes in one embodiment;

[0040] Figure 6 It is a structural block diagram of a method device for executing a database operation instruction in one embodiment;

[0041] Figure 7 FIG. 4 is a diagram showing the internal structure of a computer device in one embodiment. DETAILED DESCRIPTION

[0042] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0043] ClickHouse is a representative database of the MPP architecture. Its core design includes distributed tables, shards, and local tables. Through the collaborative work of these structures, efficient distributed storage and query capabilities are achieved.

[0044] Distributed tables are the logical entry point for user operations. Users submit queries or perform data operations through distributed tables, and distributed tables dynamically distribute requests to target shards based on defined sharding rules. Distributed tables do not store data themselves, but integrate the data views of shards to provide users with a globally consistent access experience.

[0045] Sharding is a horizontal splitting unit of distributed table data. The data in the distributed table is divided into multiple shards according to the sharding rules. Each shard stores a part of the data and is distributed on different nodes. In the design of ClickHouse, sharding not only undertakes the task of data division for distributed storage, but also supports distributed parallel processing. Each shard can run query tasks independently, thereby improving the throughput and response speed of the system.

[0046] The local table is the physical storage implementation of the shard. The data of each shard is actually stored in the local table of its corresponding node, and all queries and data operations ultimately need to be performed on the local table. If a shard has a primary copy and a secondary copy, each copy also stores data in the form of a local table. The primary copy handles write operations and maintains consistency with the secondary copy through a synchronization mechanism.

[0047] This architectural design enables ClickHouse to fully utilize the advantages of the MPP architecture when processing the storage and query of large-scale data: the query operations triggered by users through distributed tables are split into multiple subtasks, and finally executed in parallel in the local tables on each node. The query results are summarized in the distributed tables and returned to the user, thereby achieving efficient data processing capabilities.

[0048] For example, suppose there is a distributed table user_data that stores user region information. The fields of the table are as follows: user_id (user ID), name (user name), and region (user region).

[0049] For efficient storage and query, this table is sharded by the region field and is divided into three shards:

[0050] Shard 1: stores data for region = 'US'.

[0051] Shard 2: stores data for region = 'Europe'.

[0052] Shard 3: stores data for region = 'Asia'.

[0053] Each shard is stored in a local table on a different node, with the following distribution:

[0054] Shard 1: Primary copy local table (node ​​A), secondary copy local table (node ​​B)

[0055] Shard 2: Primary copy local table (node ​​C), secondary copy local table (node ​​D)

[0056] Shard 3: Primary copy local table (node ​​E), secondary copy local table (node ​​F)

[0057] In ClickHouse, metadata changes, such as adding a new column promotion_code, need to be synchronized to all shards and nodes. If a query task depends on the newly added column before the change is completed, the query may fail or return incomplete data. This is because ClickHouse uses the SharedNothing architecture, each node manages metadata independently, and there is a lack of a centralized coordination mechanism between nodes, which may lead to inconsistent metadata status. When the metadata update of a node is blocked, it not only affects the query results, but may also block the change process of the entire cluster, increase the system's operation and maintenance complexity, and thus increase the system's operation and maintenance costs.

[0058] Therefore, a method for executing a database operation instruction is proposed in this application, such as Figure 1 As shown, this embodiment uses the method applied to a terminal as an example for illustration. It is understandable that the method can also be applied to a server, and can also be applied to a system including a terminal and a server, and is implemented through the interaction between the terminal and the server. In this embodiment, the method includes the following steps:

[0059] Step 102, receiving a database operation instruction; the database operation instruction is used to perform an operation on a distributed database; the distributed database stores data in a distributed table through multiple shards.

[0060] Among them, database operation instructions refer to high-level operation commands used to execute on the database, which are used to implement metadata changes, such as adding new tables, adding columns, adjusting partitions, etc.

[0061] Among them, a distributed database refers to a database system that adopts a distributed storage architecture, which stores and manages data through multiple nodes, and the data is stored in shards between multiple nodes. A shard is a logical division unit of data in a distributed database, and each shard corresponds to a data storage instance of a node.

[0062] Step 104, split the database operation instruction into multiple subtasks.

[0063] Among them, subtasks are the fine-grained decomposition results of high-level database operation instructions. Subtasks are the basic operation units of database operation instructions, and usually correspond to specific operations applied to a single shard.

[0064] Since the data of a distributed database is stored on multiple shards, and each shard independently manages the data and metadata it stores, in order to efficiently execute database operation instructions, they must be split into multiple subtasks that can be processed in parallel and assigned to each shard for independent execution.

[0065] Optionally, the database operation instructions can be split into subtasks acting on a single shard according to the distributed sharding rules.

[0066] Optionally, you can refine the operation into specific subtasks for specific shards based on the operation type, such as adding columns or adjusting partitions.

[0067] For example, the operation of adding a promotion_code column to the user_events table can be divided into subtasks for shard 1, shard 2, etc. There may be dependencies between the subtasks, for example, a subtask needs to wait for the shard metadata synchronization to complete before it can be executed.

[0068] Step 106: Execute each subtask on the slice corresponding to each subtask according to the dependency relationship between the subtasks and the task attributes corresponding to each subtask.

[0069] The dependency relationship refers to the logical constraint relationship between subtasks. For example, the user_events table needs to adjust the partition structure first, and then add a new column promotion_code. In this case, the task of adjusting the partition must be completed before the task of adding the column.

[0070] Among them, task attributes refer to the execution characteristics of subtasks, which are used to determine task priority and resource allocation methods.

[0071] Furthermore, the task attributes include lightweight tasks and heavy tasks. Lightweight tasks are tasks that take up a short time to execute and occupy a large amount of computing resources, such as adding a new column or modifying a table structure. Heavy tasks, on the other hand, often require more computing resources and take up a long time to execute, such as data partition reconstruction or large-scale data migration.

[0072] Optionally, the dependency is determined based on the logical order of tasks and / or the association of task contents. For example, a metadata update task needs to be completed before a data query task. For example, if a task depends on the output of another task, then a dependency exists.

[0073] Optionally, executable subtasks can be determined based on the dependencies between the subtasks. For example, a subtask whose predecessor task has been completed can be used as an executable subtask. Then, corresponding resources are allocated to the subtasks based on the task attributes corresponding to the executable subtasks. Each subtask is executed on the corresponding shard using the corresponding resources.

[0074] Furthermore, multiple executable subtasks can be executed in a concurrent manner. Under the premise of ensuring the load balance of the resource pool and the stability of the system, resources can be allocated to multiple non-dependent subtasks for concurrent execution. For example, if some subtasks are lightweight tasks, threads in the lightweight resource pool will be preferentially allocated to support the high concurrent execution of these tasks, while heavy tasks will be executed at a lower concurrency in the heavy resource pool to avoid excessive resource contention.

[0075] Among them, lightweight tasks and heavy tasks are determined based on preset thresholds, where the preset thresholds can be set according to indicators such as task execution time, resource consumption (such as memory, CPU, I / O bandwidth) and the impact range of task operations to distinguish the types of tasks.

[0076] For example, if the estimated execution time of a task is less than a certain set time threshold, or its resource consumption is within a set range, and only involves metadata change operations, it is classified as a lightweight task. Lightweight tasks usually execute quickly, have low system resource requirements, and are suitable for high-concurrency execution scenarios, such as adding new columns, modifying table structures, etc.

[0077] In contrast, if the execution time of a task is greater than the preset time threshold, or the resource consumption exceeds a certain range, such as requiring high disk I / O or occupying a large amount of memory, and the operation affects a large amount of data or shards, such as partition reconstruction and large-scale data migration, it is classified as a heavy task. Heavy tasks require more system resource support, and in order to avoid putting pressure on the system, they are usually limited to concurrent execution.

[0078] In the above embodiment, after receiving the database operation instruction, the database operation instruction is split to obtain multiple subtasks, and finally, each subtask is independently executed on the shard corresponding to each subtask according to the dependency relationship between the subtasks and the task attributes corresponding to each subtask. Since the dependency relationship between the subtasks is clear and resources are reasonably allocated through task attributes, each subtask can be executed concurrently while ensuring the correctness of the task sequence. In addition, each subtask is executed independently on the corresponding shard, avoiding resource competition and mutual interference between shards. This method can effectively avoid performance bottlenecks or task blocking problems caused by centralized execution, thereby reducing system operation and maintenance costs and improving overall execution efficiency.

[0079] In one embodiment, the above-mentioned splitting of the database operation instruction to obtain multiple subtasks includes: parsing the database operation instruction to obtain key information; the key information includes the operation type and the target distributed table; obtaining the shard information corresponding to the target distributed table; according to the shard information and the operation type, splitting the database operation instruction into multiple subtasks.

[0080] First, parse the database operation instructions and extract key information from them. Key information includes the operation type and the target distributed table. For example, the operation type can be adding a column, adjusting a partition, deleting a table, etc. The target distributed table is the specific table on which the operation needs to be performed, such as user_events.

[0081] After parsing is completed, the system needs to obtain the shard information corresponding to the target distributed table. The shard information usually includes the shard identifier, such as shard 1, shard 2, etc., the storage node corresponding to the shard, such as shard 1 is stored in node A, shard 2 is stored in node B, and the distribution of shard copies, such as the node information where the primary copy and the secondary copy are located. This information is usually stored in the metadata module of the system to provide basic data for subsequent task splitting.

[0082] After obtaining the shard information, the database operation instructions are split according to the shard information and operation type to generate multiple subtasks. Optionally, each subtask corresponds to a shard and contains the specific operation content that needs to be performed on the shard. For example, for the operation of adding a column, it is necessary to generate an independent add column task for each shard, and these tasks will be executed on the master copy of the corresponding shard; for the operation of deleting a partition, it is necessary to find the shard where the partition is located and generate deletion tasks for these shards.

[0083] Exemplary, combined Figure 2 , Figure 2 The figure is a schematic diagram of instruction decomposition in an embodiment. The action in the figure is a database operation instruction. Among them, action 1 is an operation for table 1, including adding and deleting columns; action 2 is an operation for table 2, including adding indexes, modifying columns and deleting columns; action N is an operation for table x. Each action is subdivided into multiple subtasks, including in action 1, Task 1 adds column column 5, Task 2 adds column column 6, and Task 3 deletes column column 2.

[0084] Furthermore, subtasks include logical subtasks and physical subtasks. Database operation instructions are split according to shard information and operation types to generate multiple logical subtasks. Logical subtasks are the intermediate results of operation instruction splitting, indicating the distribution of operation tasks on shards. It is a task mapping on a shard range and does not specifically point to a physical node or a specific execution unit. For example, for the operation of adding a new column, logical tasks such as "execute the new column task on shard 1" and "execute the new column task on shard 2" can be generated instead of directly generating tasks for specific nodes or replicas. Based on the logical subtasks, the system further generates physical subtasks. Physical subtasks are executable units specific to shards and nodes. For example, for the logical subtask of adding a new column, "execute the new column task on shard 1" will be converted into "execute the new column task on the primary replica node A of shard 1". For the logical subtask of deleting a partition, it is necessary to locate the specific shard and node where the partition is located and generate the physical subtask of "delete the partition on the primary replica node B of shard 2". In this way, the splitting method using logical subtasks as the intermediate abstract layer not only ensures the flexibility of task splitting, but also provides a clearer logical mapping for subsequent task distribution and execution.

[0085] In the above embodiment, through this splitting method, each subtask is explicitly bound to its target slice for subsequent independent execution.

[0086] In one embodiment, the above-mentioned execution of each subtask on the slice corresponding to each subtask according to the dependency relationship between each subtask and the task attributes corresponding to each subtask includes: determining the execution status of each subtask according to the dependency relationship between each subtask; the execution status includes an executable state and a suspended state; the executable state indicates that the predecessor tasks of the subtask have been completed; the suspended state indicates that the subtask has an unfinished predecessor task; if the execution status of the subtask is an executable state, the subtask is allocated to the corresponding resource pool according to the task attributes corresponding to the subtask; according to the parallelism of the resource pool, the subtasks are executed in parallel on the slices corresponding to each subtask.

[0087] Since there is a dependency relationship between each subtask, the subsequent task can only be triggered after the predecessor task is completed. Therefore, in this embodiment, the execution status of each subtask is first determined according to the dependency relationship between each task.

[0088] There are two types of execution status: executable status and suspended status. The executable status means that all the predecessor tasks of the subtask have been completed, and the suspended status means that there are unfinished predecessor tasks of the subtask, and it will become executable only after all the predecessor tasks are completed.

[0089] If the execution state corresponding to the subtask is executable, it is allocated to the corresponding resource pool according to the task attributes corresponding to the subtask. Among them, the resource pool is a logical collection used to manage and allocate system computing resources. The resource pool is divided according to the characteristics of the task. For example, lightweight tasks use lightweight resource pools and heavy tasks use heavy resource pools. The resource pool contains pre-configured resources such as threads, memory, I / O bandwidth, etc., which are used to support parallel execution and load management of tasks. Through this resource division, the system can allocate resources more effectively and optimize task execution efficiency.

[0090] Furthermore, if the execution status of the subtask is an executable state, the subtask is allocated to a corresponding resource pool according to the task attributes corresponding to the subtask, including: if the execution status of the subtask is an executable state and the task attribute corresponding to the subtask is a lightweight task, the subtask is allocated to a lightweight resource pool; if the execution status of the subtask is executable and the task attribute corresponding to the subtask is a heavy task, the subtask is allocated to a heavy resource pool; wherein the concurrency of the lightweight resource pool is higher than the concurrency of the heavy resource pool.

[0091] If the execution status of the subtask is executable and the task attribute corresponding to the subtask is a lightweight task, the subtask is assigned to the lightweight resource pool. The lightweight resource pool is mainly used to process tasks that require less resources and have a short execution time, such as metadata updates or simple data structure adjustment operations. Since lightweight tasks have a small demand for system resources, lightweight resource pools are usually configured with higher concurrency to support the simultaneous execution of multiple lightweight tasks, thereby improving overall execution efficiency.

[0092] If the execution status of the subtask is executable and the task attribute corresponding to the subtask is a heavy task, the subtask is assigned to the heavy resource pool. The heavy resource pool is used to handle tasks that require high resource usage and take a long time to execute, such as large-scale data migration and partition reconstruction. The heavy resource pool usually limits the number of concurrent tasks to avoid excessive load on the system's CPU, memory, and I / O bandwidth, and to ensure the stability of task execution.

[0093] The reason why the concurrency of lightweight resource pool is higher than that of heavy resource pool is that lightweight tasks have low system overhead and low resource competition among them, so they can be processed quickly with higher concurrency configuration. However, heavy tasks consume large resources, and excessive concurrency may lead to system resource exhaustion or even crash, so lower concurrency is needed to balance system performance and task execution efficiency. This resource pool division strategy ensures the efficiency and stability of the system when executing different types of tasks, while improving the overall throughput of the system.

[0094] Afterwards, each subtask is executed in parallel on the shards corresponding to each subtask according to the parallelism of the resource pool.

[0095] Optionally, the system first extracts executable subtasks from the lightweight resource pool and the heavy resource pool. For the lightweight resource pool, since it is configured with higher concurrency, the system can schedule multiple lightweight tasks at the same time so that they can be quickly executed on the corresponding shards. For example, in the operation of adding a new column, the lightweight resource pool can schedule tasks for multiple shards at the same time, and these tasks are independently executed in parallel on their respective shards.

[0096] For heavy resource pools, although the concurrency is low, the system will gradually schedule heavy tasks based on resource availability. These tasks may involve large data operations and therefore require higher resource quotas, such as more threads, larger memory, or higher I / O bandwidth. Heavy tasks will be executed on shards one by one according to the set concurrency limit to ensure that they do not cause resource competition for other tasks or services in the system.

[0097] In this way, the system can dynamically manage the allocation and execution of tasks in the resource pool. Lightweight and heavyweight tasks are optimally scheduled in their respective resource pools and run in parallel on their respective shards, ultimately achieving efficient task processing and rational use of system resources. This mechanism effectively improves the overall throughput of the system while maintaining the stability of the execution process.

[0098] In another embodiment, if the execution state of a subtask is suspended, the predecessor tasks of the subtask are monitored; if it is detected that the predecessor tasks of the subtask have been completed, the subtask is allocated to a heavy resource pool or a light resource pool according to the task attributes corresponding to the subtask.

[0099] In another embodiment, if the execution status of a subtask is suspended, the system will continue to monitor the completion of the predecessor task of the subtask. The suspended status indicates that the subtask has unfinished dependent tasks and therefore cannot be directly executed. Through the monitoring mechanism, the system can obtain the execution status of the predecessor task in real time.

[0100] When it is detected that all the predecessor tasks of a suspended subtask have been completed, the status of the subtask will be updated to executable status. Subsequently, the system will assign it to the corresponding resource pool according to the task attributes corresponding to the subtask. If the task attribute of the subtask is a lightweight task, it will be assigned to the lightweight resource pool. The lightweight resource pool supports high concurrent execution and can quickly schedule multiple lightweight tasks to run in parallel on the shards. If the task attribute of the subtask is a heavy task, it will be assigned to the heavy resource pool. The heavy resource pool provides more resource support for resource-intensive tasks, while limiting the number of concurrent tasks to ensure the stability of the system.

[0101] Through this mechanism, the system can dynamically schedule subtasks in the suspended state to ensure that tasks can enter the execution phase in a timely manner after the dependency is released, and optimize the efficiency of task execution by utilizing the properties of the resource pool. This not only ensures the order of tasks, but also improves the overall performance and resource utilization of the system.

[0102] For example, assume that there are the following three database operation instructions:

[0103] 1. Action A: Add the column email to the order_data table and delete the column old_discount.

[0104] 2. Action B: Modify the partitioning rules for the user_data table.

[0105] 3. Action C: Delete the expired partitions of the log_data table.

[0106] Split each database operation instruction and build the corresponding DAG tree.

[0107] Among them, the task DAG of Action A is: Task1: Add a new column email → Task2: Delete the column old_discount; the task DAG of Action B is: Task1: Modify the partitioning rules in the metadata → Task2: Adjust the existing partition data; the task DAG of Action C is Task1: Delete the partition 202201.

[0108] The status of each task is now as follows:

[0109] Task Dependent tasks state A.Task1 none Executable A.Task2 A.Task1 Suspend B.Task1 none Executable B.Task2 B.Task1 Suspend C.Task1 none Executable

[0110] Then, according to the corresponding task attributes of A.Task1, B.Task1 and C.Task1, they are allocated to the corresponding resource pools.

[0111] Among them, A.Task1 adds a new column, which is a metadata change and is usually regarded as a lightweight task, so it can be assigned to a lightweight resource pool; B.Task1 also belongs to metadata adjustment, which usually does not affect a large amount of data and can be assigned to a lightweight resource pool; C.Task1 deletes a partition, which usually involves physical deletion of a large amount of data and storage space recovery, and is a heavy task, so it is assigned to a heavy resource pool.

[0112] Finally, according to the concurrency of the light resource pool and the heavy resource pool, each subtask is executed in parallel on the shard corresponding to each subtask.

[0113] In another embodiment, exemplary, combined Figure 3As shown, Figure 3 The figure is a schematic diagram of a subtask operation flow in one embodiment.

[0114] In the task initialization process, the system starts an initialization thread to obtain the tasks to be executed from the task queue. When obtaining tasks, the first unfinished task is taken out according to the library or table granularity. After that, the task status is checked, including whether it has timed out and whether it has been started. If the task waiting time exceeds the set threshold, it needs to be rescheduled. If the task has been assigned to the execution thread, it is considered that the task has been started. The checked tasks are added to the waiting queue and wait for the scheduling thread to perform subsequent processing.

[0115] In the task execution process, the scheduling thread takes the task out of the waiting queue and enters the task execution process. The scheduling thread checks the dependencies of the task. If the task has an unfinished pre-dependency, it will not be executed temporarily and wait for the dependency to be completed. According to the attributes of the task, the task is assigned to the corresponding resource pool, and the system resources required for the task execution are allocated. After that, the underlying SQL statement or API is called to actually execute the task on the corresponding shard. Finally, the system detects the execution result of the task. If the task is completed successfully, the task status is updated to "Completed". If the task execution fails, determine whether it needs to be retried. If it can be retried, put the task back into the waiting queue. If multiple retries fail, the failure information is recorded and an alarm is generated.

[0116] In one embodiment, the shards include a primary copy and multiple secondary copies; executing each subtask on the shards corresponding to each subtask includes: executing each subtask on the primary copy corresponding to the subtask, and synchronizing the changes to each secondary copy after the execution is completed on the primary copy.

[0117] Shards consist of a primary replica and multiple secondary replicas. When executing each subtask, the specific operation is first performed on the primary replica of the shard corresponding to the subtask. This step is the core stage of task execution, because the primary replica is the main node in the shard responsible for processing write operations and metadata changes. After the subtask is completed on the primary replica, the primary replica will synchronize the changes to all secondary replicas of the shard. The main purpose of secondary replica synchronization is to ensure the consistency and integrity of the data in the shard, so as to support query tasks and other operations to be load-balanced on the secondary replicas.

[0118] In this embodiment, the design of executing on the primary replica first and then synchronizing to the secondary replica can ensure the efficiency and stability of the system. The primary replica is responsible for centralized processing operations to avoid conflicts caused by distributed writes; while the synchronization of the secondary replica is completed in an asynchronous or semi-synchronous manner, ensuring execution performance under high concurrency. At the same time, this mechanism can also improve the fault tolerance of the system, that is, if the primary replica fails, the secondary replica can quickly take over to ensure the continuity and high availability of the system.

[0119] Exemplary, combined Figure 4 As shown, Figure 4 The following is a flowchart of subtask execution in an embodiment. Since each subtask can be composed of a group of SQL statements, the SQL statements will be executed cyclically during execution. The task needs to be executed on a shard, and each shard may have multiple copies. In order to avoid excessive occupation of system resources, the SQL of the task will be executed in batches. For example, only a part of the data or a subtask in a shard is processed at a time, and the next batch is executed after completion. Among them, the task is first executed on the master copy of the shard, and the master copy is responsible for processing write operations and metadata updates. After the master copy is executed, the changes are synchronized to the corresponding secondary copy. After the master copy completes the task, the system will synchronize the changes to the secondary copy of the shard according to the synchronization strategy. If the task needs to be supplemented on the secondary copy, the batch execution of the secondary copy will be started. Finally, if the task is completed on the master copy and does not need to be further executed on the secondary copy, the process ends. Alternatively, if the secondary copy needs to be executed, the task is finally completed after it is completed in batches. When the operations of the master and secondary copies of all shards are completed, the task is marked as completed and the next task scheduling is entered.

[0120] In an exemplary embodiment, in combination Figure 5 As shown, Figure 5 The diagram is a schematic diagram of the architecture of a distributed database after a node crashes in one embodiment.

[0121] ClickHouse uses the SharedNothing architecture, and each node manages its metadata independently. Therefore, when a node goes down, the following problems will occur:

[0122] 1. Metadata inconsistency: Since the metadata of each node is stored independently, after a node goes down, it cannot participate in metadata synchronization, resulting in inconsistent metadata status within the shard. If the query involves data from a down node, the query process may be affected.

[0123] 2. Metadata changes are blocked. When a node crashes during a write operation (such as adding a column or adjusting a partition), the write operation of the shard may be blocked, affecting the normal execution of online tasks.

[0124] In order to deal with node downtime, ClickHouse cluster needs to adjust the topology, including traffic management, topology management and metadata consistency management. Among them, traffic management is to transfer the query traffic of the downtime node to the healthy secondary replica node to ensure that the query task can be executed normally. Topology management is to only retain healthy nodes in the shards of the downtime node to participate in the service, and temporarily stop the write operation to the shard to which the downtime node belongs, but allow historical data to be queried, thereby reducing the impact of the downtime on users. Metadata consistency management is that when the downtime node is removed, other healthy nodes will readjust the metadata synchronization logic to avoid the interruption of the metadata change process due to the downtime node.

[0125] In the above embodiment, the ClickHouse cluster can effectively cope with the challenge of node failure, which not only ensures the stability of the system but also reduces the operation and maintenance cost of fault repair.

[0126] It should be understood that, although the various steps in the flowcharts involved in the above-mentioned embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps does not have a strict order restriction, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above-mentioned embodiments can include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0127] Based on the same inventive concept, the embodiment of the present application also provides a database operation instruction execution device for implementing the database operation instruction execution method involved above. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme recorded in the above method, so the specific limitations in the one or more database operation instruction execution method device embodiments provided below can refer to the above limitations on the database operation instruction execution method, and will not be repeated here.

[0128] In an exemplary embodiment, Figure 6 As shown, a method and device for executing a database operation instruction is provided, comprising: an instruction receiving module 610, an instruction splitting module 620 and an instruction execution module 630, wherein:

[0129] The instruction receiving module is used to receive database operation instructions; the database operation instructions are used to perform operations on a distributed database; the distributed database stores data in a distributed table through multiple shards.

[0130] The instruction splitting module is used to split the database operation instruction to obtain multiple subtasks.

[0131] The instruction execution module is used to execute each subtask on the slice corresponding to each subtask according to the dependency relationship between the subtasks and the task attributes corresponding to each subtask.

[0132] In one embodiment, the instruction splitting module includes:

[0133] The parsing unit is used to parse the database operation instruction to obtain key information; the key information includes the operation type and the target distributed table.

[0134] The information acquisition unit is used to acquire the shard information corresponding to the target distributed table.

[0135] A splitting unit is used to split the database operation instruction into multiple subtasks according to the sharding information and the operation type.

[0136] In one embodiment, the instruction execution module includes:

[0137] A state determination unit is used to determine the execution state of each subtask according to the dependency relationship between the subtasks; the execution state includes an executable state and a suspended state; the executable state indicates that the predecessor tasks of the subtask have been completed; the suspended state indicates that the subtask has an unfinished predecessor task.

[0138] The resource pool allocation unit is used to allocate the subtask to a corresponding resource pool according to a task attribute corresponding to the subtask if the execution state of the subtask is an executable state.

[0139] The concurrent execution unit is used to execute each of the subtasks in parallel on the slices corresponding to each of the subtasks according to the parallelism of the resource pool.

[0140] In one embodiment, the resource pool allocation unit includes:

[0141] The light resource allocation subunit is used to allocate the subtask to a light resource pool if the execution state of the subtask is an executable state and the task attribute corresponding to the subtask is a light task.

[0142] A heavy resource allocation unit is used to allocate the subtask to a heavy resource pool if the execution status of the subtask is executable and the task attribute corresponding to the subtask is a heavy task; wherein the concurrency of the lightweight resource pool is higher than the concurrency of the heavy resource pool; the heavy task and the lightweight task are determined according to a preset threshold, and the resources required by the lightweight task are less than those of the heavy task.

[0143] In one embodiment, the resource pool allocation unit further includes:

[0144] The detection subunit is used to monitor the predecessor task of the subtask if the execution state of the subtask is a suspended state.

[0145] Allocating resources is used to allocate the subtask to the heavy resource pool or the light resource pool according to the task attributes corresponding to the subtask if it is monitored that the predecessor tasks of the subtask have been completed.

[0146] In one embodiment, the instruction execution module includes:

[0147] The content change unit is used to execute each of the subtasks on the primary copy corresponding to the subtask, and after the execution is completed on the primary copy, synchronize the changed content to each of the secondary copies.

[0148] Each module in the above-mentioned database operation instruction execution method device can be implemented in whole or in part by software, hardware and a combination thereof. Each of the above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, or can be stored in the memory of the computer device in the form of software, so that the processor can call and execute the operations corresponding to each of the above modules.

[0149] In an exemplary embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as shown in FIG. Figure 7 As shown. The computer device includes a processor, a memory, an input / output interface (Input / Output, referred to as I / O) and a communication interface. The processor, the memory and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store database data. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a method for executing a database operation instruction is implemented.

[0150] Those skilled in the art will understand that Figure 7The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0151] In an exemplary embodiment, a computer device is provided, including a memory and a processor, wherein a computer program is stored in the memory, and the processor implements the steps of the method in any one of the above embodiments when executing the computer program.

[0152] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of the method in any one of the above embodiments are implemented.

[0153] In one embodiment, a computer program product is provided, comprising a computer program, which implements the steps of the method in any one of the above embodiments when executed by a processor.

[0154] A person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment method can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., but are not limited to this.

[0155] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, 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 application.

[0156] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A method for executing a database operation instruction, characterized in that: The method comprises: Receive a database operation instruction; the database operation instruction is used to perform data operations on a distributed database; the distributed database stores data in a distributed table through multiple shards; Splitting the database operation instruction to obtain multiple subtasks; According to the dependency relationship between the subtasks and the task attributes corresponding to the subtasks, the subtasks are executed on the slices corresponding to the subtasks.

2. The method according to claim 1, characterized in that The database operation instruction is split into multiple subtasks, including: Parsing the database operation instruction to obtain key information; the key information includes the operation type and the target distributed table; Obtaining shard information corresponding to the target distributed table; According to the sharding information and the operation type, the database operation instruction is split into a plurality of subtasks.

3. The method according to claim 1, characterized in that: The executing each subtask on a slice corresponding to each subtask according to the dependency relationship between the subtasks and the task attributes corresponding to the subtasks includes: Determine the execution status of each subtask according to the dependency relationship between the subtasks; the execution status includes an executable state and a suspended state; the executable state indicates that all the predecessor tasks of the subtask have been completed; the suspended state indicates that the subtask has an unfinished predecessor task; If the execution state of the subtask is an executable state, the subtask is allocated to a corresponding resource pool according to a task attribute corresponding to the subtask; According to the parallelism of the resource pool, each of the subtasks is executed in parallel on the slices corresponding to each of the subtasks.

4. The method according to claim 3, characterized in that If the execution state of the subtask is an executable state, allocating the subtask to a corresponding resource pool according to a task attribute corresponding to the subtask includes: If the execution state of the subtask is an executable state and the task attribute corresponding to the subtask is a lightweight task, allocating the subtask to a lightweight resource pool; If the execution status of the subtask is executable and the task attribute corresponding to the subtask is a heavy task, the subtask is assigned to the heavy resource pool; wherein the concurrency of the lightweight resource pool is higher than the concurrency of the heavy resource pool; the heavy task and the lightweight task are determined according to a preset threshold, and the resources required for the lightweight task are less than those for the heavy task.

5. The method according to claim 4, characterized in that The method further comprises: If the execution state of the subtask is a suspended state, monitoring the predecessor task of the subtask; If it is monitored that all the predecessor tasks of the subtask have been completed, the subtask is allocated to the heavy resource pool or the light resource pool according to the task attribute corresponding to the subtask.

6. The method according to claim 1, characterized in that The shard includes a primary copy and multiple secondary copies; The executing each of the subtasks on the slices corresponding to each of the subtasks includes: Each of the subtasks is executed on the primary replica corresponding to the subtask, and after completion of execution on the primary replica, the changed content is synchronized to each of the secondary replicas.

7. A database operation instruction execution device, characterized in that: The device comprises: An instruction receiving module, used for receiving a database operation instruction; the database operation instruction is used for performing an operation on a distributed database; the distributed database stores data in a distributed table through multiple shards; An instruction splitting module is used to split the database operation instruction into multiple subtasks; The instruction execution module is used to execute each subtask on the slice corresponding to each subtask according to the dependency relationship between the subtasks and the task attributes corresponding to each subtask.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images