Automated table partitioning method, system and equipment based on GaussDB and storage medium

Abstract

The invention provides a GaussDB-based automatic table partitioning method, system and device and a storage medium, and the method comprises the following steps: S1, traversing current partition tables, and recognizing the partition type of each current partition table; s2, calculating a partition name needing to be created according to a data input date, checking whether the created partition name exists in a current partition table or not, and if not, executing partition table creation operation; s3, generating corresponding SQL statements partitioned according to time granularity according to partition grammar of the GaussDB database and types of partition key fields, and processing data exceeding a current partition range; s4, executing the SQL statement to complete partition operation, and returning an execution result or abnormal information; and S5, creating a configurable timed task, executing the operations of the steps S1 to S4, and completing the automatic task processing of the table partition. Automatic new partition creation of the partition table can be realized, so that the maintenance cost is greatly reduced, and the query performance is remarkably improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to an automatic table partitioning method, system, device and storage medium based on GaussDB. Background Art

[0002] With the rapid advancement of cutting-edge technologies such as cloud computing, big data, and artificial intelligence, my country has made great progress in the field of database technology. GaussDB, launched by Huawei, is an enterprise-level distributed database designed to provide excellent performance, high availability, and flexible scalability.

[0003] In production environments, the continuous expansion of data volume often causes a single data table to become extremely large, which in turn leads to a significant reduction in query efficiency. When designing database tables, the industry generally uses table partitioning technology to address this challenge. However, the traditional partition table management method requires operation and maintenance personnel to manually create new partitions and clean up expired partitions regularly, which undoubtedly increases the operation and maintenance burden and cost.

[0004] To solve this problem, GaussDB introduced the automatic partition management function. This function can be easily enabled by configuring the table-level parameters period (a special data type used to store information about a time period or duration) and ttl (Time to Live, the survival time of data in network transmission). However, it is worth noting that this technology currently has the following limitations:

[0005] (1) Applicable to small computers, accelerated clusters and single-machine cluster environments.

[0006] (2) Only supported in clusters of version 8.1.3 and above.

[0007] (3) The partition key must be unique, and its data type is limited to timestamp, timestamptz (timestamp with time zone information), and date.

[0008] (4) Tables containing maxvalue partitions (the maximum possible value in a partition) are not supported

[0009] In view of this, the present invention proposes an automatic table partitioning method, system, device and storage medium based on GaussDB, which can meet business needs and realize automatic creation of new partitions of partitioned tables, thereby greatly reducing maintenance costs and significantly improving query performance. Summary of the invention

[0010] In order to solve the problems that the GaussDB partition key must be unique, its data type is limited to timestamp and timestamptz, and it does not support tables containing maxvalue partitions, this application provides an automatic table partitioning method, system, device and storage medium based on GaussDB to solve the above technical defects.

[0011] According to the first aspect of the present application, an automatic table partitioning method based on GaussDB is proposed, and the method includes the following steps:

[0012] S1. Traverse the current partition table and identify the partition type of each current partition table;

[0013] S2. Calculate the partition name to be created according to the date of the data input, and check whether the created partition name exists in the current partition table. If not, perform the partition table creation operation;

[0014] S3. Generate the corresponding SQL statement for partitioning by time granularity according to the partitioning syntax of the GaussDB database and the type of the partitioning key field, and process the data beyond the current partition range;

[0015] S4. Execute the SQL statement to complete the partition operation and return the execution result or exception information;

[0016] S5. Create a configurable scheduled task and execute the operations of steps S1 to S4 to complete the automatic task processing of the table partition.

[0017] Preferably, in step S5, creating a configurable scheduled task specifically includes the following sub-steps:

[0018] S51. Define a log table in the GaussDB database. The log table is used to record the startup log of the scheduled task.

[0019] S52, read personalized configuration information from the configuration table of the GaussDB database, where the configuration information includes an execution strategy for the partition operation;

[0020] S53. Calculate the specific date of the current month according to the current system time, and pass the date as a parameter to the table partition stored procedure;

[0021] S54, after executing the partition creation or related partition management operation, the operation log information is recorded in the defined log table, the operation log information includes: whether the operation is successful or not, the operation time and the operation type;

[0022] S55, regularly scanning the log table according to the number of days for log retention set in the personalized configuration information, and deleting old log records that exceed the specified number of days for retention;

[0023] S56. After all operations of the scheduled task are completed, the end log of the task is recorded in the log table, marking the completion time and status of the task.

[0024] Further preferably, in step S53, according to the current system time, the specific date of the current month is calculated, and the date is passed as a parameter to the table partition storage procedure, further comprising:

[0025] If the current time is after the preset date of each month, the date parameter of the next month is passed in to create the partition of the next month in advance.

[0026] Further preferably, the log information recorded in the log table also includes: task type, task status, execution timestamp, operation object and error information.

[0027] Preferably, the GaussDB-based automatic table partitioning method proposed in the present application also includes:

[0028] Get all data sources through JNDI, and filter out GaussDB data sources from all data sources;

[0029] Traverse the GaussDB data source to obtain the JDBC connection, and execute SQL to create a table partition stored procedure through the JDBC connection.

[0030] Preferably, in step S1, it also includes:

[0031] Use the system view or metadata query function provided by the GaussDB database to traverse all the user's current partition tables and identify the partition type of each partition table, where the partition type is divided according to the time granularity.

[0032] Preferably, the automatic table partitioning method based on GaussDB proposed in the present application also includes: creating a partition table using a naming rule in a fixed format, and identifying the partition type by a specific prefix.

[0033] In the second aspect, the present application proposes an automatic table partitioning system based on GaussDB, characterized in that the system includes:

[0034] An identification module is configured to traverse the current partition table and identify the partition type of each current partition table;

[0035] The judgment module is configured to calculate the partition name to be created according to the date of the data input, and check whether the created partition name exists in the current partition table. If not, the partition table creation operation is performed;

[0036] The generation module is configured to generate the corresponding SQL statements for partitioning by time granularity according to the partitioning syntax of the GaussDB database and the type of the partitioning key field, and process the data beyond the current partition range;

[0037] The partitioning module is configured to execute SQL statements to complete the partitioning operation and return the execution result or exception information;

[0038] The timing processing module is configured to create a configurable timing task, execute the operations of the identification module, the judgment module, the generation module and the partitioning module, and complete the automatic task processing of the table partition.

[0039] In a third aspect, the present application proposes a terminal device, comprising a processor, a memory, and a computer program stored in the memory, wherein the computer program is executed by the processor to implement an automatic table partitioning method based on GaussDB as described in any one of the above.

[0040] In a fourth aspect, the present application proposes a computer-readable storage medium, in which a computer program is stored. When the computer program is executed by a processor, the automatic table partitioning method based on GaussDB as described in any one of the above is implemented.

[0041] Compared with the prior art, the beneficial results of the present invention are:

[0042] (1) Relaxed permissions: By executing table partitioning tasks under respective data source users, cross-user permissions and permission constraints are avoided, ensuring the security and flexibility of operations.

[0043] (2) Version compatibility: The present invention does not impose strict requirements on the version and deployment environment of GaussDB. It relies on standard SQL syntax and can realize automatic table partitioning to ensure wide applicability.

[0044] (3) Low constraints: It supports multiple partition field types (number, timestamp, char), has no constraints on the timestamp type, and specifically supports maxvalue partitioning, which improves design flexibility.

[0045] (4) Low maintenance cost: The entire automation process does not require DBA intervention, achieving full automation of operation and maintenance, significantly reducing maintenance costs and improving production efficiency. Enhanced security: During the encoding process, a set of summaries is randomly selected to increase the complexity of the encoding, thereby increasing the difficulty of cracking. This method effectively prevents attackers from restoring data through brute force cracking, thereby enhancing data security. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] Other features, objects and advantages of the present application will become more apparent by reading the detailed description of non-limiting embodiments made with reference to the following drawings:

[0047] Figure 1 is a flowchart of an automatic table partitioning method based on GaussDB according to the present application;

[0048] Figure 2 It is a schematic diagram of the execution flow of creating table partitions through a stored procedure according to the present application;

[0049] Figure 3 It is a schematic diagram of the execution flow of the automated partition creation strategy according to the present application;

[0050] Figure 4 It is a structural diagram of the GaussDB-based automatic table partitioning system according to the present application;

[0051] Figure 5 It is a structural diagram of a computer system suitable for implementing an electronic device of an embodiment of the present application. DETAILED DESCRIPTION

[0052] The present application will be further described in detail below in conjunction with the accompanying drawings and embodiments. It is to be understood that the specific embodiments described herein are only used to explain the relevant invention, rather than to limit the invention. It should also be noted that, for ease of description, only the parts related to the relevant invention are shown in the accompanying drawings.

[0053] It should be noted that, in the absence of conflict, the embodiments and features in the embodiments of the present application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.

[0054] The present invention aims to provide an automatic table partitioning method designed specifically for GaussDB database, which can flexibly adapt to different GaussDB versions, supports partitioning by day, month, and year, and the partition fields support number, timestamp, char types, and especially supports maxvalue partitioning.

[0055] Figure 1 The flowchart of the automatic table partitioning method based on GaussDB of the present application is shown as follows: Figure 1 As shown, the method comprises the following steps:

[0056] S1. Traverse the current partition table and identify the partition type of each current partition table.

[0057] In this embodiment, when creating a business partition table, a fixed-format naming rule is adopted, and a specific prefix is ​​used to identify the partition type (day, month, year) to ensure that the partition table name is standardized and easy to identify in the GaussDB environment, and facilitates subsequent management and query optimization.

[0058] S2. Calculate the partition name to be created based on the date of the data input (partitioned by day, month, and year), and check whether the created partition name exists in the current partition table. If not, perform the partition table creation operation, and if it exists, skip the creation step. It should be understood that the partition name is generated based on the partition type.

[0059] Preferably, the system view or metadata query function provided by the GaussDB database can be used to traverse all current partition tables of the user, and can be filtered through the configuration table to identify the partition type of each partition table, where the partition type is divided according to the time granularity (partitioned by day, month, and year).

[0060] S3. Generate corresponding SQL statements for partitioning by time granularity (partitioning by day, month, or year) based on the partitioning syntax of the GaussDB database and the type of the partitioning key field, and process data that exceeds the current partition range (i.e., specially process the case of maxvalue partitions).

[0061] S4. Execute SQL statements in the GaussDB environment to complete the partitioning operation and return the execution results or exception information.

[0062] In this implementation example, when the application starts, the system will dynamically obtain the current data source information, including GaussDB connection information and user information, and automatically create a table partitioning stored procedure for each database user to ensure the automation and personalization of partitioning operations.

[0063] S5. Create a configurable scheduled task and execute the operations of steps S1 to S4 to complete the automatic task processing of the table partition.

[0064] That is, the system automatically creates a scheduled task (configurable time) for each GaussDB database user, and creates a thread in the background to execute the automatic task processing method of table partitions, ensuring the regular execution of partition operations and the efficiency of partition management.

[0065] In this embodiment, creating a configurable scheduled task specifically includes the following sub-steps:

[0066] S51. Define a log table in the GaussDB database. The log table is used to record the startup log of the scheduled task.

[0067] S52, read personalized configuration information from the configuration table of the GaussDB database, where the configuration information includes an execution strategy for the partition operation;

[0068] S53. Calculate the specific date of the current month according to the current system time, and pass the date as a parameter to the table partition stored procedure;

[0069] In particular, if the current time is after the preset day of each month, the date parameter of the next month is passed in to create the partition of the next month in advance. For example: based on the current time, the date of the month is calculated and passed to the table partitioning stored procedure; if the current time is after the 20th of each month, the date parameter of the next month is passed in to create the partition of the next month in advance.

[0070] S54. After executing the partition creation or related partition management operation, the operation log information is recorded in the defined log table, and the operation log information includes: whether the operation is successful or not, the operation time, the operation type, the task type, the task status, the execution timestamp, the operation object and the error information;

[0071] S55. According to the log retention days set in the personalized configuration information, regularly scan the log table and delete old log records that exceed the specified retention days to ensure the effectiveness of log management;

[0072] S56. After all operations of the scheduled task are completed, the end log of the task is recorded in the log table, marking the completion time and status of the task.

[0073] In a specific embodiment, when creating a partition table, in order to ensure that the partition name not only complies with the naming rules of GaussDB but also clearly expresses the type and time range of the partition, the partition name is created in the following agreed and standardized format:

[0074] 1) Day partition: d_date, such as d_20241018

[0075] 2) Monthly partition: m_month, such as m_202410

[0076] 3) Year partition: y_year, such as y_2024

[0077] In addition, compared with other partitioning technical solutions, which usually can only create partitions for date type (timestamp, timestamptz and date) data, the present invention designs a partitioning function for date type (timestamp, timestamptz and date), numeric type (numeric) and string type (char, varchar and varchar2; char: used to store fixed-length character data, varchar and varchar2: used to store variable-length character data) data in the GaussDB database to meet the actual project's partitioning requirements for multiple data types.

[0078] Figure 2 The schematic diagram of the execution flow of creating table partitions through a stored procedure of the present application is shown as follows: Figure 2 As shown, the steps to create a partition are as follows:

[0079] 1) Calculate the first and last day of the month based on the stored procedure parameters year and month. For example, create a partition for October 2024 and pass in 2024 and 10 to calculate the first day of October 20241001 and the last day 20241031.

[0080] 2) Calculate the partition boundary based on the last day of the month, such as: 20241101:

[0081] 3) Taking the monthly partition table as an example, filter out the monthly partition table of the current user from the GaussDB database system table MY_PART_TABLES (used to store the information of the partition table under the current user) according to the partition name m_ (here only the current user is queried and there is no permission access restriction in the whole process), loop through the monthly partition table to determine whether the month m_202410 partition is created. If not, proceed to the next step to create the partition.

[0082] 4) Combined with the month to be partitioned, such as 202410, query the maxvalue partition used for segmentation from the GaussDB database system table MY_TAB_PARTITIONS (database object, which stores the relevant information of all partition tables under the current user), such as m_999912.

[0083] 5) Get the type of the current partition field, and generate a partition statement based on the field type in combination with steps 2) and 4).

[0084] 6) Rename the temporary partition and rename the three partition types of year, month, and day according to the partition naming rules mentioned above.

[0085] 7) The principles of day partition and year partition are similar to those of month partition (day partition is created from the first day of the month to the last day of the month, and year partition is created based on the year), so we will not go into details here.

[0086] The present invention designs a GaussDB database automatic partition creation strategy, which automatically creates the required partitions for the user's table through steps such as reading the configuration table, calculating the current system time, and calling the stored procedure. Figure 3 The following is a schematic diagram showing the execution process of the automated partition creation strategy of the present application: Figure 3 As shown, automated partition creation includes the following steps:

[0087] 1) Initialize the user task status table JOB_SCHEDULE_STATUS. To avoid repeated execution of tasks on multiple machines, query the current user executable status based on the row lock. If executable, proceed to the next step.

[0088] Task status table JOB_SCHEDULE_STATUS:

[0089] Fields type describe JOB_NAME VARCHAR Task Name USER_NAME VARCHAR username STATUS VARCHAR Task status: 1 is executable, 0 is not executable RUN_DATE DATE Run time RUN_INTERVAL VARCHAR Run interval

[0090] 2) Record the user task start log, such as: "USER user: start executing daily background job tasks."

[0091] Log table structure JOB_LOG:

[0092] Fields type describe LOG_SEQ NUMBER sequence LOG_DATE DATE date ERR_CODE NUMBER Error code, 0 means success LOG_MSG VARCHAR Log information

[0093] 3) Read the configuration table JOB_PARAMETER and determine whether to execute automatic table partitioning based on the table parameter RUN_ADD_PARTITION (with the permission to add partitions). The default value 1 indicates execution.

[0094] Configuration table JOB_PARAMETER:

[0095] Fields type describe NAME VARCHAR Parameter name VALUE VARCHAR Parameter Value DESCRIPTION VARCHAR describe

[0096] 4) Calculate the current system year and month, taking October 2024 as an example, record the partition start log "USER user: start calling the P_ADD_PARTITION stored procedure to create the partition for October 2024"; read the partition filter table to pass in the year, month and excluded partition table parameters to call the stored procedure; record the log "USER user: successfully created USER.ACTION_LOG (used to record user operation log) 202410 partition" according to the result returned by the stored procedure; record the partition end log "USER user: completed calling P_ADD_PARTITION to successfully create the partition for October 2024".

[0097] 5) Similarly, calculate whether the current date is after the 20th. If so, create the monthly partition for the next month. The steps are the same as 3) and will not be repeated here.

[0098] 6) Record the user task completion log, such as: "USER user: ended the execution of daily background job tasks."

[0099] 7) Read the configuration table JOB_PARAMETER (an object containing job runtime parameters), obtain the log retention parameter based on the table parameter LOG_SAVE_DAYS (the number of days to save logs), the default is 365 days, and delete the log data older than 365 days.

[0100] 8) Change the user task status table and modify the user task status to executable and waiting for the next task execution.

[0101] In a specific embodiment, in order to ensure that each GuassDB database user can use the partition management function of the present application, the present application dynamically creates the required stored procedures for each database user when the application starts. The specific steps are as follows:

[0102] 1) Get the parameter "Whether to update the stored procedure" in the system parameter table. If the value is 0, continue to the next step to create a stored procedure.

[0103] 2) Obtain all data sources through JNDI (JNDI, Java Naming and Directory Interface, which mainly provides naming and directory services for resources required by applications), filter out GaussDB data sources, and traverse the data sources to obtain JDBC (Java Database Connectivity) connections.

[0104] 3) Execute SQL to create a stored procedure through JDBC connection.

[0105] 4) Update the system parameter table "Whether to update the stored procedure" parameter value to 1 to avoid repeated creation of the stored procedure.

[0106] In a specific embodiment, the application configuration file is read to obtain the scheduled task execution time, a scheduled automatic task is created according to the time, and a thread execution table partition method is created for each database user. The specific steps are as follows:

[0107] 1) Read the job-time attribute of the application configuration file. By default, it is executed every morning (00:00:00).

[0108] 2) Calculate the current time and the execution time interval based on the execution time, and create a scheduled task thread pool. The default size of the thread pool is the number of logical cores of the machine Runtime.getRuntime().availableProcessors(), and supports configuration core-pool-size (number of core threads) adjustment.

[0109] 3) Create a thread task execution table partitioning method for each database user.

[0110] Further references Figure 4 As an implementation of the above method, in the second aspect, the present application provides a structural diagram of an embodiment of an automatic table partitioning system 400 based on GaussDB, which can be specifically applied to various electronic devices. The system 400 includes the following modules:

[0111] The identification module 410 is configured to traverse the current partition table and identify the partition type of each current partition table;

[0112] The judgment module 420 is configured to calculate the partition name to be created according to the date of the data input, and check whether the created partition name exists in the current partition table. If not, the partition table creation operation is performed;

[0113] A generation module 430 is configured to generate a corresponding SQL statement for partitioning by time granularity according to the partitioning syntax of the GaussDB database and the type of the partitioning key field, and process data beyond the current partition range;

[0114] The partitioning module 440 is configured to execute SQL statements to complete the partitioning operation and return the execution result or exception information;

[0115] The timing processing module 450 is configured to create a configurable timing task, execute the operations of the identification module 410, the judgment module 420, the generation module 430 and the partitioning module 440, and complete the automatic task processing of the table partition.

[0116] The present invention can realize the automatic creation of table partitions by automatically creating stored procedures and combining configuration tables and partition fields, and complete the partition maintenance operation by executing the automatic task processing method of table partitions through scheduled tasks. This technology significantly improves the level of operation and maintenance automation, reduces human intervention, and effectively improves production efficiency.

[0117] In a third aspect, the present application proposes a terminal device, comprising a processor, a memory, and a computer program stored in the memory, wherein the computer program is executed by the processor to implement an automatic table partitioning method based on GaussDB as described in any one of the above.

[0118] In a fourth aspect, the present application proposes a computer-readable storage medium, in which a computer program is stored. When the computer program is executed by a processor, the automatic table partitioning method based on GaussDB as described in any one of the above is implemented.

[0119] Reference below Figure 5 , which shows a schematic diagram of the structure of a computer system 500 suitable for implementing an electronic device of an embodiment of the present application. Figure 5 The electronic device shown is merely an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0120] like Figure 5As shown, the computer system 500 includes a central processing unit (CPU) 501, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 502 or a program loaded from a storage part 508 into a random access memory (RAM) 503. In the RAM 503, various programs and data required for the operation of the computer system 500 are also stored. The CPU 501, the ROM 502, and the RAM 503 are connected to each other via a bus 504. An input / output (I / O) interface 505 is also connected to the bus 504.

[0121] The following components are connected to the I / O interface 505: an input section 506 including a keyboard, a mouse, etc.; an output section 507 including a liquid crystal display (LCD), etc. and a speaker, etc.; a storage section 508 including a hard disk, etc.; and a communication section 509 including a network interface card such as a LAN card, a modem, etc. The communication section 509 performs communication processing via a network such as the Internet. A drive 510 is also connected to the I / O interface 505 as needed. A removable medium 511, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 510 as needed, so that a computer program read therefrom is installed into the storage section 508 as needed.

[0122] In particular, according to an embodiment of the present disclosure, the process described above with reference to the flowchart can be implemented as a computer software program. For example, an embodiment of the present disclosure includes a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through a communication part 509, and / or installed from a removable medium 511. When the computer program is executed by a central processing unit (CPU) 501, the above-mentioned functions defined in the method of the present application are executed.

[0123] It should be noted that the computer-readable storage medium described in the present application may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, 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), an 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 above. In the present application, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, device or device. In the present application, a computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable program code. This propagated data signal may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. The computer-readable signal medium may also be any computer-readable storage medium other than a computer-readable storage medium that can send, propagate or transmit a program for use by or in conjunction with an instruction execution system, apparatus or device. The program code contained on the computer-readable storage medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, RF, etc., or any suitable combination of the above.

[0124] 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 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 the case of a remote computer, the remote computer may be connected to the user's computer through 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).

[0125] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, 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, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the square box can also occur in a sequence different from that marked in the accompanying drawings. For example, two square boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. 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 implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0126] 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 the invention involved in the present application is not limited to the technical solution formed by a specific combination of the above technical features, but should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the above invention concept. For example, the above features are formed by replacing each other with the technical features with similar functions disclosed in this application (but not limited to).

Claims

1. An automatic table partitioning method based on GaussDB, characterized in that: The method comprises the following steps: S1. Traverse the current partition table and identify the partition type of each current partition table; S2. Calculate the partition name to be created according to the date of the data input, and check whether the created partition name exists in the current partition table. If not, perform the partition table creation operation; S3. Generate the corresponding SQL statement for partitioning by time granularity according to the partitioning syntax of the GaussDB database and the type of the partitioning key field, and process the data beyond the current partition range; S4. Execute the SQL statement to complete the partitioning operation, and return the execution result or exception information; S5. Create a configurable scheduled task and execute the operations of steps S1 to S4 to complete the automatic task processing of the table partition.

2. The automatic table partitioning method based on GaussDB according to claim 1 is characterized in that: In step S5, creating a configurable scheduled task specifically includes the following sub-steps: S51, defining a log table in the GaussDB database, wherein the log table is used to record the startup log of the scheduled task; S52, reading personalized configuration information from the configuration table of the GaussDB database, wherein the configuration information includes an execution strategy for partition operations; S53. Calculate the specific date of the current month according to the current system time, and pass the date as a parameter to the table partition storage procedure; S54, after executing the partition creation or related partition management operation, recording the operation log information in the defined log table, the operation log information including: whether the operation is successful or not, the operation time and the operation type; S55, regularly scanning the log table according to the number of days for log retention set in the personalized configuration information, and deleting old log records that exceed the specified number of days for retention; S56. After all operations of the scheduled task are completed, an end log of the task is recorded in the log table, marking the completion time and status of the task.

3. The automatic table partitioning method based on GaussDB according to claim 2 is characterized in that: In step S53, the specific date of the current month is calculated according to the current system time, and the date is passed as a parameter to the table partitioning stored procedure, which also includes: If the current time is after the preset date of each month, the date parameter of the next month is passed in to create the partition of the next month in advance.

4. The automatic table partitioning method based on GaussDB according to claim 2 is characterized in that: The log information recorded in the log table also includes: task type, task status, execution timestamp, operation object and error information.

5. The automatic table partitioning method based on GaussDB according to claim 1, characterized in that: Also includes: Get all data sources through JNDI, and filter out GaussDB data sources from all data sources; Traverse the GaussDB data source to obtain a JDBC connection, and execute SQL to create a table partition stored procedure through the JDBC connection.

6. The automatic table partitioning method based on GaussDB according to claim 1, characterized in that: In step S1, it also includes: By using the system view or metadata query function provided by the GaussDB database, all current partition tables of the user are traversed to identify the partition type of each partition table, where the partition type is divided according to the time granularity.

7. The automatic table partitioning method based on GaussDB according to claim 1, characterized in that: Also includes: The partition table is created using a naming rule in a fixed format, and the partition type is identified by a specific prefix.

8. An automatic table partitioning system based on GaussDB, characterized in that: The system comprises: An identification module is configured to traverse the current partition table and identify the partition type of each current partition table; The judgment module is configured to calculate the partition name to be created according to the date of the data input, and check whether the created partition name exists in the current partition table. If not, the partition table creation operation is performed; The generation module is configured to generate the corresponding SQL statements for partitioning by time granularity according to the partitioning syntax of the GaussDB database and the type of the partitioning key field, and process the data beyond the current partition range; A partitioning module is configured to execute the SQL statement to complete the partitioning operation and return the execution result or exception information; The timing processing module is configured to create configurable timing tasks, execute the operations of the identification module, the judgment module, the generation module and the partitioning module, and complete the automatic task processing of table partitions.

9. A terminal device, characterized in that: It includes a processor, a memory, and a computer program stored in the memory, wherein the computer program is executed by the processor to implement the GaussDB-based automatic table partitioning method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, wherein a computer program is stored in the medium, and when the computer program is executed by a processor, the automatic table partitioning method based on GaussDB as described in any one of claims 1 to 7 is implemented.

Images