A method and device for inserting data into a specified partition of an openGauss database

Abstract

The present application relates to a method and device for inserting data into a specified partition of an openGauss database. The method includes: obtaining the partitioning rules of the partition table and the characteristics of the data to be inserted; inferring the target partition where the data to be inserted should be stored; when executing the data insertion task, first specifying the target partition information where the data is to be inserted; verifying the consistency of the data to be inserted and the target partitioning rules; and directly inserting the data into the target partition based on the partitioning rules of the partition table and the characteristics of the data to be inserted. When executing the data insertion task, the method skips unnecessary partition routing calculations and directly inserts data into the specified partition. The method can avoid a large number of redundant partition routing calculations, reduce performance loss, significantly improve the insertion performance of the partition table, and at the same time ensure the consistency of the distribution rules of the partition table data. The method is particularly suitable for scenarios with a large number of partitions.

Description

Technical Field

[0001] The present application belongs to the technical field of data insertion, and in particular, relates to a method, device, computer-readable storage medium, and electronic device for inserting data into a specified partition of an openGauss database. Background Art

[0002] In the openGauss database, a partition table refers to a table in which certain fields are divided into different partitions for storage according to certain rules. When inserting data into a partition table, each piece of data is partitioned and routed, and then each piece of data is inserted into the partition that meets the partition rules.

[0003] Currently, when inserting data into the partition table of openGauss, partition routing calculation needs to be performed for each piece of data. The purpose of partition routing calculation is to confirm which partition the data should be stored in. The more partitions a partition table has, the longer the routing calculation time will be, the greater the performance loss will be, and ultimately the overall data insertion performance will be reduced. Summary of the invention

[0004] In order to address the above issues and improve the insertion performance of the partition table, this application proposes a new method for inserting data into a specified partition in the openGauss database. This method aims to reduce the performance loss of data during the partition routing calculation process, especially when there are too many partitions.

[0005] The applicant has discovered that for specific data in some specific scenarios, the batch of data could have been directly inserted into a specific partition in the partition table (for example, if the partition table partition rule is divided by day, then the data generated on that day must be inserted into the partition corresponding to that day's date). However, according to the current method, such data still needs to go through partition routing. Since the purpose of routing calculation is to find the target partition where the data is to be inserted, it is completely unnecessary to perform a partition routing calculation when the target partition where the data should be inserted is known, and a large number of redundant partition routing calculations will inevitably bring additional performance loss.

[0006] In summary, this application obtains the partitioning rules of the partition table and the characteristics of the data to be inserted, and infers the partition where the data to be inserted should be stored based on the partitioning rules of the partition table and the characteristics of the data to be inserted. When executing the data insertion task, the partition information of the data to be inserted is pre-specified. At this time, unnecessary partition routing calculations can be skipped, and the data can be directly inserted into the specified partition. Since a large number of redundant partition routing calculations are avoided, this method can reduce performance loss and significantly improve the insertion performance of the partition table. At the same time, in order to avoid human error in destroying the consistency of the distribution rules of the partition table data, this method needs to verify the inserted data with the specified partition before inserting the data to ensure that the data complies with the partitioning rules of the specified partition.

[0007] In order to achieve the above objectives, this application provides the following technical solutions: A first aspect of the present application provides a method for inserting data into a specified partition of an openGauss database, the method comprising: Get the partitioning rules of the partition table and the characteristics of the data to be inserted; Infer the target partition where the data to be inserted should be stored based on the partition rules of the partition table and the characteristics of the data to be inserted; When executing a data insertion task, first specify the target partition information where the data is to be inserted; Before inserting data, verify the consistency between the data to be inserted and the target partitioning rules; According to the partition table information and the target partition information, directly insert the data into the target partition.

[0008] Optionally, the method of the present application further includes: adding a grammatical sub-rule for specifying a partition when inserting data into the partition table to the rule for specifying the partition table name in the INSERT INTO statement.

[0009] Optionally, in the method of the present application, the grammatical sub-rule for specifying a partition when inserting data into a partition table is an extension of the insert_target rule in the gram.y file, and a new INSERT INTO implementation statement of "relation_name PARTITION ( partition_name )" is added. In the INSERT INTO implementation statement, after specifying the partition table name, a specific syntax is used to specify the target partition name into which the data is to be inserted, and relevant information of the target partition is obtained based on the partition table name and the target partition name.

[0010] Optionally, the method of the present application also includes: in the semantic analysis stage, obtaining partition table information according to the partition table name, verifying whether the specified partition name is legal, and if the partition name is legal, obtaining partition information according to the partition name.

[0011] Optionally, in the method of the present application, in the semantic analysis stage, metadata information of the partition table is obtained according to the partition table name through the function setTargetTable(), and if the specified partition name is legal, metadata information of the partition is obtained according to the partition name.

[0012] Optionally, in the method of the present application, the verification of the consistency between the data to be inserted and the target partitioning rule includes: in the Insert main process ExecInsertT(), first determine whether the target partition is specified, then obtain the target partitioning rule according to the specified target partition, and determine whether the data to be inserted and the target partitioning rule are consistent. If they are consistent, execute the data insertion task; if they are inconsistent, return with an error.

[0013] A second aspect of the present application provides a device for inserting data into a specified partition of an openGauss database, the device comprising: Information acquisition module: used to obtain the partition rules of the partition table and the characteristics of the data to be inserted; Partition selection module: used to infer the target partition where the data to be inserted should be stored based on the partition rules of the partition table and the characteristics of the data to be inserted; Partition designation module: used to specify the target partition information where the data is to be inserted when executing the data insertion task; Verification module: used to verify the consistency of the data to be inserted and the target partitioning rules before inserting the data; Task execution module: used to directly insert data into the target partition according to the partition table information and the target partition information.

[0014] The device implements the steps of the aforementioned method of inserting data into a designated partition in the openGauss database when running.

[0015] A third aspect of the present application provides an electronic device, comprising: a memory and a processor; Memory: used to store computer programs; Processor: used to execute the computer program to implement the steps of the aforementioned method of inserting data into a designated partition of the openGauss database.

[0016] A fourth aspect of the present application provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the aforementioned method of inserting data into a specified partition of an openGauss database are implemented.

[0017] In summary, this application proposes a new method for inserting data into a specified partition in an openGauss database. This method infers the partition where the data to be inserted should be stored based on the partition rules of the partition table and the characteristics of the data to be inserted. When executing the data insertion task, first specify the partition information where the data is to be inserted, skip unnecessary partition routing calculations, and directly insert data into the specified partition. This method can avoid a large number of redundant partition routing calculations, reduce performance loss, significantly improve the insertion performance of the partition table, and at the same time ensure the consistency of the distribution rules of the partition table data. This method is particularly suitable for scenarios with a large number of partitions.

[0018] Other features and advantages of the present application will be described in the following description, or understood by practicing the present application. The purpose and other advantages of the present application can be realized and obtained by the techniques indicated in the description, claims and drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the technical solutions of the embodiments of the present application, a brief introduction will be given below to the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present application. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

[0020] Figure 1 The present invention provides an overall implementation flowchart of the method for inserting data into a specified partition of an openGauss database provided in an embodiment of the present application.

[0021] Figure 2 A structural diagram of the composition of an apparatus for inserting data into a specified partition of an openGauss database provided in an embodiment of the present application.

[0022] Figure 3 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0023] In order to make the purpose, technical scheme and advantages of the embodiments of the present application clearer, the technical scheme in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0024] As used herein, the term “including” and its variations are open inclusions, ie, “including but not limited to”; the term “based on” means “based at least in part on”; and the term “one embodiment” means “at least one embodiment”.

[0025] It should be noted that the modifications of "one" and "plurality" mentioned in the present application are illustrative rather than restrictive, and those skilled in the art should understand that unless the context clearly indicates otherwise, it should be understood as "one or more".

[0026] Figure 1 The overall implementation process of the method for inserting data into a specified partition in the openGauss database provided by the present application is shown, including the following steps: Get the partitioning rules of the partition table and the characteristics of the data to be inserted; Infer the target partition where the data to be inserted should be stored based on the partition rules of the partition table and the characteristics of the data to be inserted; When executing a data insertion task, first specify the target partition information where the data is to be inserted; Before inserting data, verify the consistency between the data to be inserted and the target partitioning rules; According to the partition table information and the target partition information, directly insert the data into the target partition.

[0027] Specifically, first, in order to specify the partition when inserting data into the partition table, it is necessary to implement the syntax for specifying the partition in the INSERT INTO statement. The strategy adopted by this application is to add a new syntax rule "relation_name PARTITION ( partition_name )" to the rule for specifying the partition table name in the INSERT INTO statement.

[0028] Then in the semantic analysis phase, after obtaining the partition table information according to the table name, it is verified whether the specified partition name is legal, and the partition information is obtained according to the partition name.

[0029] Then, in the logic of determining the partition to which the data belongs during the execution phase, for data for which partition information has been specified, the partition routing calculation is skipped and the specified partition is used as the target partition for data insertion.

[0030] Finally, because the partition routing calculation has not been performed, in order to ensure the consistency of the data with the partition rules of the specified partition, it is necessary to perform a consistency check on the data and the target partition rules. After the check passes, the data is inserted into the specified partition.

[0031] In order to better understand the technical solution of the present application, it is further described with reference to the following embodiments.

[0032] Construct the following INSERT INTO statement: INSERT INTO table_name PARTITION (partition_name) VALUES (xxx); In the provided INSERT INTO statement, after specifying the partition table name, use a specific syntax to specify the name of the target partition where the data is to be inserted. Based on the table name and partition name, you can obtain relevant information about the partition to be inserted.

[0033] 1. To support the partition specification in the INSERT INTO statement, you first need to add the syntax for specifying partitions in the INSERT INTO statement. The specific method is to expand the insert_target rule in the gram.y file and add a sub-rule "relation_name PARTITION ( partition_name )" so that the target partition name can be specified after the partition table name is specified.

[0034] 2. In the semantic analysis stage, after obtaining the metadata information of the partition table through the function setTargetTable(), the specified partition name is verified and the metadata information of the specified partition is obtained.

[0035] When inserting data into a partition table, the original INSERT function will perform partition routing calculation based on each piece of data to obtain the target partition for data insertion. However, in this application, the partition for data insertion is directly provided in the INSERT statement, so partition routing calculation is no longer required. However, in order to ensure the consistency of the data distribution rules of the partition table, it is necessary to verify whether the data is consistent with the partition rules of the specified target partition.

[0036] 3. In the main Insert process ExecInsertT(), before calculating the partition route, first determine whether the partition is specified: a) Obtain the partitioning rules according to the specified partition and determine whether the data is consistent with the partitioning rules; b) If they are inconsistent, an error is reported.

[0037] 4. Insert data into the target partition according to the partition table information and partition information.

[0038] Figure 2 The present application shows a device for inserting data into a specified partition of an openGauss database, including: Information acquisition module: used to obtain the partition rules of the partition table and the characteristics of the data to be inserted; Partition selection module: used to infer the target partition where the data to be inserted should be stored based on the partition rules of the partition table and the characteristics of the data to be inserted; Partition designation module: used to specify the target partition information where the data is to be inserted when executing the data insertion task; Verification module: used to verify the consistency of the data to be inserted and the target partitioning rules before inserting the data; Task execution module: used to directly insert data into the target partition according to the partition table information and the target partition information.

[0039] When the above device is running, the steps of the method for inserting data into a designated partition in the openGauss database disclosed in the present application are implemented.

[0040] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the device, method and computer program product according to various embodiments of the present application. In this regard, each square box in the flow chart or block diagram can represent a module, program segment or a part of code, and the module, program segment or a part of code contains one or more executable instructions for realizing the specified logical function. It should also be noted that each square box in the block diagram and / or flow chart, and the combination of the square boxes in the block diagram and / or flow chart can be realized by a special hardware-based system that performs the specified function or operation, or can be realized by a combination of special hardware and computer instructions.

[0041] like Figure 3 As shown, the embodiment of the present application also discloses an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a computer program executable by the processor, and a communication bus 340. The processor 310, the communication interface 320, and the memory 330 communicate with each other through the communication bus 340. The processor 310 runs the executable computer program to implement the steps of the method of inserting data into a specified partition of the openGauss database.

[0042] It is understandable that, in addition to the memory and the processor, the electronic device may also include an input device such as a keyboard, an output device such as a display, and other communication modules. The input device, the output device, and other communication modules communicate with the processor via an I / O interface (i.e., an input / output interface).

[0043] Computer program code for performing the operations of the present application may be written in one or more programming languages ​​or a combination thereof, including, but not limited to, object-oriented programming languages, such as Java, Smalltalk, C++, and conventional procedural programming languages, such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving a remote computer, the remote computer may be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0044] Furthermore, the present application also discloses a computer-readable storage medium. When the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device can execute the various steps of the method for inserting data into a specified partition of an openGauss database disclosed in the present application.

[0045] In the context of the present application, a computer-readable storage medium may be a tangible medium, more specific examples of which would include a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0046] In particular, according to an embodiment of the present application, the process described in the flowchart can be implemented as a computer software program. For example, an embodiment of the present application includes a computer program product, which includes a computer program carried on a non-transitory computer-readable medium, and the computer program contains program code for executing the method of inserting data into a specified partition of the openGauss database disclosed in the present application. When the computer program is executed by a processing device, the above functions defined in the method of the embodiment of the present application are executed.

[0047] Although several specific implementation details are included in the above discussion, these should not be interpreted as limiting the scope of the present application. The above description is only a preferred embodiment of the present application and an explanation of the technical principles used. Those skilled in the art should understand that the scope of disclosure involved in the present application is not limited to the technical solutions formed by a specific combination of the above technical features, but also should cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the above public concept.

[0048] Those skilled in the art should also understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein; and these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A method for inserting data into a specified partition of an openGauss database, characterized in that: The method comprises: Get the partitioning rules of the partition table and the characteristics of the data to be inserted; Infer the target partition where the data to be inserted should be stored based on the partition rules of the partition table and the characteristics of the data to be inserted; When executing a data insertion task, first specify the target partition information where the data is to be inserted; Before inserting data, verify the consistency between the data to be inserted and the target partitioning rules; According to the partition table information and the target partition information, directly insert the data into the target partition.

2. The method according to claim 1, characterized in that: The method further comprises: adding a grammar sub-rule for specifying a partition when inserting data into the partition table to the rule for specifying the partition table name in the INSERT INTO statement.

3. The method according to claim 2, characterized in that The grammatical sub-rule for specifying a partition when inserting data into a partition table, the grammatical sub-rule is to expand the insert_target rule in the gram.y file, and to add an INSERT INTO implementation statement of "relation_name PARTITION ( partition_name )". In the INSERT INTO implementation statement, after specifying the partition table name, a specific syntax is used to specify the target partition name to which the data is to be inserted, and relevant information of the target partition is obtained according to the partition table name and the target partition name.

4. The method according to claim 1, characterized in that: The method further comprises: in the semantic analysis stage, obtaining partition table information according to the partition table name, verifying whether the specified partition name is legal, and if the partition name is legal, obtaining partition information according to the partition name.

5. The method according to claim 4, characterized in that In the semantic analysis phase, the metadata information of the partition table is obtained according to the partition table name through the function setTargetTable(). If the specified partition name is legal, the metadata information of the partition is obtained according to the partition name.

6. The method according to claim 1, characterized in that The verification of the consistency between the data to be inserted and the target partition rule includes: in the Insert main process ExecInsertT(), firstly judging whether the target partition is specified, then obtaining the target partition rule according to the specified target partition, judging whether the data to be inserted and the target partition rule are consistent, if they are consistent, executing the data insertion task, if they are inconsistent, reporting an error and returning.

7. A device for inserting data into a specified partition of an openGauss database, characterized in that: The device comprises: Information acquisition module: used to obtain the partition rules of the partition table and the characteristics of the data to be inserted; Partition selection module: used to infer the target partition where the data to be inserted should be stored based on the partition rules of the partition table and the characteristics of the data to be inserted; Partition designation module: used to specify the target partition information where the data is to be inserted when executing the data insertion task; Verification module: used to verify the consistency of the data to be inserted and the target partitioning rules before inserting the data; Task execution module: used to directly insert data into the target partition according to the partition table information and the target partition information.

8. 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 for inserting data into a designated partition of an openGauss database as described in any one of claims 1 to 6 are implemented.

9. An electronic device, characterized in that: include: Memory and processor; Memory: used to store computer programs; Processor: used to execute the computer program to implement the steps of the method for inserting data into a specified partition of an openGauss database as described in any one of claims 1 to 6.

Images