Automatic table partitioning method, system, device and storage medium based on GaussDB

Abstract

The present invention provides an automatic table partitioning method, system, device and storage medium based on GaussDB. The method includes the following steps: S1, traversing the current partition table and identifying the partition type of each current partition table; S2, calculating the partition name to be created based on the date of the data input, and checking whether the created partition name exists in the current partition table. If not, executing the partition table creation operation; S3, generating the corresponding SQL statement for partitioning by time granularity based on the partitioning syntax of the GaussDB database and the type of the partition key field, and processing data outside the current partition range; S4, executing the SQL statement to complete the partitioning operation and returning the execution result or exception information; S5, creating a configurable timed task, executing the operations of steps S1-S4, and completing the automatic table partitioning task processing. It can realize the automatic creation of new partitions for the partitioned table, thereby significantly reducing maintenance costs and significantly improving query performance.

Description

Technical Field

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

[0002] With the rapid advancement of cutting-edge technologies such as cloud computing, big data, and artificial intelligence, my country has made significant progress in database technology. Huawei's GaussDB, an enterprise-class distributed database, is designed to deliver exceptional performance, high availability, and flexible scalability.

[0003] In production environments, the ever-expanding volume of data often leads to individual data tables becoming extremely large, significantly reducing query efficiency. When designing database tables, the industry generally adopts table partitioning to address this challenge. However, traditional partitioned table management requires operations personnel to manually create new partitions and clean up expired partitions on a regular basis, which undoubtedly increases the operational burden and costs.

[0004] To address this challenge, GaussDB introduces automatic partition management. This feature 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 time it takes for data to survive network transmission). However, it is worth noting that this technology currently has the following limitations:

[0005] (1) Applicable to minicomputers, acceleration clusters and single-machine cluster environments.

[0006] (2) Only supported in clusters with version 8.1.3 or later.

[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 GaussDB partition keys must be unique, their data types are limited to timestamp and timestamptz, and tables containing maxvalue partitions are not supported, the present 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 based on the date of the data input, and check whether the created partition name exists in the current partition table. If not, execute the partition table creation operation;

[0014] S3. Generates the corresponding SQL statement for partitioning by time granularity based on the partitioning syntax of the GaussDB database and the type of the partition key field, and processes data that exceeds the current partition range.

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

[0016] S5. Create a configurable scheduled task and execute steps S1-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 based on the current system time, and pass the date as a parameter to the table partition stored procedure.

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

[0022] S55. Scan the log table regularly according to the log retention days set in the personalized configuration information, and delete old log records that exceed the specified retention days;

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

[0024] Further preferably, 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, further comprising:

[0025] 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 for 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 automatic table partitioning method based on GaussDB proposed in this 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 a JDBC connection, and execute the SQL to create a table partition stored procedure through the JDBC connection.

[0030] Preferably, in step S1, the following is further included:

[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 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 fixed-format naming rule, and identifying the partition type through a specific prefix.

[0033] In a 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 time-granular partitioning based on the partitioning syntax and partition key field type of the GaussDB database, and process data that exceeds the current partition range;

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

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

[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 the GaussDB-based automatic table partitioning method 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 present invention has the following advantages:

[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 the flexibility of the design.

[0045] (4) Low maintenance costs: The entire automated process does not require DBA intervention, achieving fully automated operations and maintenance, significantly reducing maintenance costs and improving production efficiency. Enhanced security: During the encoding process, a set of digests 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 from the detailed description of non-limiting embodiments made with reference to the following drawings:

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

[0048] Figure 2 This is a schematic diagram of the execution flow of creating table partitions through stored procedures according to this application;

[0049] Figure 3 This is a schematic diagram of the execution process of the automated partition creation strategy according to this application;

[0050] Figure 4 This 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 the electronic device of the embodiment of the present application. DETAILED DESCRIPTION

[0052] The present application will be further described in detail below with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are merely for the purpose of explaining the relevant invention and are not intended to limit the invention. It should also be noted that, for ease of description, only portions relevant 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 of the embodiments in this 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 automated table partitioning method designed specifically for the GaussDB database. The method can flexibly adapt to different GaussDB versions, supports partitioning by day, month, and year, and the partition fields support number, timestamp, and 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 includes 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; if so, 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 the user's current partition tables, and filtering can be performed 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 time-granular partitioning (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 handle the case of maxvalue partitioning).

[0061] S4. Execute the SQL statement in the GaussDB environment to complete the partitioning operation and return the execution result 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 steps S1-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 table partition automatic task processing method to ensure 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 based on 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 for the next month is passed in to create the partition for the next month in advance. For example, based on the current time, the date of the current 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 for the next month is passed in to create the partition for the next month in advance.

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

[0071] S55. Regularly scan the log table according to the log retention days set in the personalized configuration information 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 task end log 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, to ensure that the partition name conforms to the GaussDB naming rules and clearly expresses the partition type and time range, the partition name is created according to the following agreed 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 technology 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), numerical 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 following is a schematic diagram of the execution flow of creating table partitions through a stored procedure in this application, as shown in 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, if you create a partition for October 2024 and pass in 2024 and 10, you will get the first day of October as 20241001 and the last day as 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 current user's monthly partition table from the GaussDB database system table MY_PART_TABLES (used to store information about the current user's partition tables) based on the partition name m_ (only the current user is queried here, and there are no permission access restrictions throughout the process). Loop through the monthly partition table to determine whether the month m_202410 partition has been created. If not, proceed to the next step to create the partition.

[0082] 4) Based on the month to be partitioned, such as 202410, query the maxvalue partition used for partitioning from the GaussDB database system table MY_TAB_PARTITIONS (a database object that stores information about 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 partitioning and year partitioning are similar to those of month partitioning (day partitioning is created from the first day of the month to the last day of the month, and year partitioning is created based on the year). We will not go into details here.

[0086] The present invention designs an automatic partition creation strategy for the GaussDB database, which automatically creates the required partitions for the user's table by reading the configuration table, calculating the current system time, calling the stored procedure and other steps. Figure 3 The following is a schematic diagram showing the execution process of the automated partition creation strategy of this 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 task execution 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] Field 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 Runtime 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] Field 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 perform 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] Field 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; based on the result returned by the stored procedure, record the log "USER user: Successfully created USER.ACTION_LOG (used to record user operation log) 202410 partition"; 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 next month's monthly partition. The steps are the same as 3) and are not repeated here.

[0098] 6) Record the user task completion log, such as: "USER user: completed 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), 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 this application, this 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 "Whether to Update the Stored Procedure" parameter 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 (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 partitioning method is created for each database user. The specific steps are as follows:

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

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

[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 a 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 applied to various electronic devices. The system 400 includes the following modules:

[0111] An 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] The 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 that exceeds the current partition range;

[0114] Partitioning module 440 is configured to execute SQL statements to complete partitioning operations and return execution results 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] This invention automates the creation of table partitions by automating the creation of stored procedures and combining them with configuration tables and partition fields. It also performs partition maintenance operations by executing automated task processing for table partitions via scheduled tasks. This technology significantly enhances the level of automated operation and maintenance, 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 the GaussDB-based automatic table partitioning method 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 structural diagram 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 limit 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 unit 508 into a random access memory (RAM) 503. Various programs and data required for the operation of the computer system 500 are also stored in the RAM 503. 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, and the like; an output section 507 including a liquid crystal display (LCD) and speakers; a storage section 508 including a hard disk; and a communication section 509 including a network interface card such as a LAN card or a modem. 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, or a semiconductor memory, is installed in the drive 510 as needed, so that computer programs read therefrom can be 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 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 via the communication section 509, and / or installed from a removable medium 511. When the computer program is executed by the central processing unit (CPU) 501, the above-mentioned functions defined in the method of the present application are performed.

[0123] It should be noted that the computer-readable storage medium described in this application can be a computer-readable signal medium or a computer-readable storage medium, or any combination of the two. The computer-readable storage medium can 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 can 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 this application, a computer-readable storage medium can be any tangible medium that contains or stores a program that can be used by or in conjunction with an instruction execution system, device, or device. In this application, a computer-readable signal medium can include a data signal propagated in baseband or as part of a carrier wave, which carries computer-readable program code. This propagated data signal can take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. A computer-readable signal medium may also be any computer-readable storage medium other than a computer-readable storage medium that can transmit, propagate, or transport a program for use by or in conjunction with an instruction execution system, apparatus, or device. Program code contained on a computer-readable storage medium may be transmitted using any suitable medium, including but not limited to wireless, wireline, optical cable, RF, or any suitable combination thereof.

[0124] Computer program code for performing the operations of the present application can 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 can be executed entirely on the user's computer, partially on the user's computer, as a stand-alone 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 can 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 can be connected to an external computer (e.g., through the Internet using an Internet service provider).

[0125] The flow charts and block diagrams in the accompanying drawings illustrate the possible architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present application. In this regard, each 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 in some alternative implementations, the functions marked in the box can also occur in a different order than that marked in the accompanying drawings. For example, two 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 box in the block diagram and / or flow chart, and the combination of the boxes in the block diagram and / or flow chart can be implemented by a dedicated hardware-based system that performs the specified function or operation, or can be implemented by a combination of dedicated hardware and computer instructions.

[0126] The above description is merely a preferred embodiment of the present application and an illustration of the technical principles employed. Those skilled in the art should understand that the scope of the invention involved in this application is not limited to the technical solutions formed by the specific combination of the above-mentioned technical features, but also encompasses other technical solutions formed by any combination of the above-mentioned technical features or their equivalents without departing from the above-mentioned inventive concept. For example, the above-mentioned features may be replaced with (but not limited to) technical features with similar functions disclosed in this application.

Claims

1. An automatic table partitioning method based on GaussDB, characterized in that: The method comprises the following steps: S1. Obtain all data sources through JNDI and select 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; 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. The method also includes: creating the partition table using a fixed format naming rule and identifying the partition type by a specific prefix; S2. Calculate the partition name to be created based on 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. Generates the corresponding SQL statement for partitioning by time granularity based on the partitioning syntax of the GaussDB database and the type of the partition key field, and processes data that exceeds 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 steps S1-S4 to complete the automatic task processing of the table partition. In step S5, creating a configurable scheduled task specifically includes the following sub-steps: S51. Define a log table in the GaussDB database, wherein the log table is used to record the startup log of the scheduled task, and the log information recorded in the log table also includes: task type, task status, execution timestamp, operation object, and error information; S52. Read personalized configuration information from the configuration table of the GaussDB database, where the configuration information includes an execution strategy for a partition operation; 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, further comprising: 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 for the next month in advance; Calculate the current time and execution time interval based on the execution time, and create a scheduled task thread pool; S54. After executing a partition creation or related partition management operation, log information of the operation is recorded in the defined log table, where the log information of the operation includes: whether the operation is successful or not, the operation time, and the operation type; S55. Scan the log table regularly according to the log retention days set in the personalized configuration information, and delete old log records that exceed the specified retention days; 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.

2. An automatic table partitioning system based on GaussDB, characterized in that: The system comprises: The identification module obtains all data sources through JNDI and selects 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; 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. The method also includes: creating the partition table using a fixed format naming rule and identifying the partition type by a specific prefix; 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 time-granular partitioning based on the partitioning syntax and partition key field type of the GaussDB database, and process data that exceeds 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, judgment module, generation module, and partitioning module, and complete the automatic task processing of table partitioning; In the timer processing module, creating a configurable timer task includes the following sub-steps: S51. Define a log table in the GaussDB database, where the log table is used to record the startup log of the scheduled task. The log information recorded in the log table also includes: task type, task status, execution timestamp, operation object, and error information; S52. Read personalized configuration information from the configuration table of the GaussDB database, where the configuration information includes an execution strategy for a partition operation; 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, further comprising: 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 for the next month in advance; Calculate the current time and execution time interval based on the execution time, and create a scheduled task thread pool; S54. After executing a partition creation or related partition management operation, log information of the operation is recorded in the defined log table, where the log information of the operation includes: whether the operation is successful or not, the operation time, and the operation type; S55. Scan the log table regularly according to the log retention days set in the personalized configuration information, and delete old log records that exceed the specified retention days; 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. A terminal device, characterized in that: The system comprises a processor, a memory and a computer program stored in the memory, wherein the computer program is executed by the processor to implement the automatic table partitioning method based on GaussDB as claimed in claim 1.

4. 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 claimed in claim 1 is implemented.

Images