CN117056428A - Automatic expansion method, device and server of partition table - Google Patents
Automatic expansion method, device and server of partition table Download PDFInfo
- Publication number
- CN117056428A CN117056428A CN202311035608.3A CN202311035608A CN117056428A CN 117056428 A CN117056428 A CN 117056428A CN 202311035608 A CN202311035608 A CN 202311035608A CN 117056428 A CN117056428 A CN 117056428A
- Authority
- CN
- China
- Prior art keywords
- partition
- target
- upper boundary
- basic
- type
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000005192 partition Methods 0.000 title claims abstract description 511
- 238000000034 method Methods 0.000 title claims abstract description 54
- 238000012163 sequencing technique Methods 0.000 claims description 9
- 238000012216 screening Methods 0.000 claims description 4
- 238000000638 solvent extraction Methods 0.000 abstract description 3
- 238000004891 communication Methods 0.000 description 18
- 230000006870 function Effects 0.000 description 7
- 239000000306 component Substances 0.000 description 5
- 238000004590 computer program Methods 0.000 description 4
- 238000012545 processing Methods 0.000 description 4
- 230000008878 coupling Effects 0.000 description 3
- 238000010168 coupling process Methods 0.000 description 3
- 238000005859 coupling reaction Methods 0.000 description 3
- 238000010586 diagram Methods 0.000 description 3
- 230000008569 process Effects 0.000 description 3
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 108010001267 Protein Subunits Proteins 0.000 description 1
- 239000008358 core component Substances 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 230000006386 memory function Effects 0.000 description 1
- 230000005055 memory storage Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000000750 progressive effect Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
- G06F16/278—Data partitioning, e.g. horizontal or vertical partitioning
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides an automatic expansion method, device and server for a partition table, and belongs to the technical field of big data. The method comprises the following steps: obtaining the table name of a target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure; obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table; determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters; executing the partition expansion statement, and performing partition expansion on the target partition table. Based on the method, the partitioning expansion can be automatically performed on the Gaussian database table.
Description
Technical Field
The specification belongs to the technical field of big data, and particularly relates to an automatic expansion method, device and server for a partition table.
Background
In the technical field of big data, as data in a Gaussian database table is continuously increased, the speed of inquiring the data is reduced, and then the Gaussian database table can be partitioned to obtain a Gaussian database partition table. Partitioning a gaussian database table, namely splitting the gaussian database table into a plurality of partitions, storing different data in each partition, and storing the plurality of partitions into a plurality of table spaces, wherein the gaussian database partition table is still a complete table logically. Therefore, when data is queried, the complete partition table of the Gaussian database is not required to be scanned, and only the corresponding partition is required to be scanned, so that the data query efficiency can be improved. However, in the gaussian database partition table, the upper boundary of the partition cannot be automatically expanded, so as the data increases, there is a problem that the data exceeds the existing partition and cannot be stored.
In view of the above technical problems, no effective solution has been proposed at present.
Disclosure of Invention
The application provides an automatic expansion method, device and server for a partition table, which can automatically expand the upper boundary of the partition table.
The embodiment of the application aims to provide an automatic expansion method of a partition table, which comprises the following steps:
Obtaining the table name of a target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure;
obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table;
determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters;
executing the partition expansion statement, and performing partition expansion on the target partition table.
Further, in another embodiment of the method, the obtaining the partition type of the target partition table according to the table name of the target partition table, the first base table, and the second base table includes:
extracting a partition field sequence number corresponding to a table name of the target partition table from the first basic table;
screening a partition field sequence number corresponding to the data attribute field from the partition field sequence number to be used as a target partition field sequence number;
Taking the field sequence number of the target partition as a column sequence number;
extracting target data corresponding to the column sequence number from the second basic table;
determining the data type of the target data as the partition type of a target partition table; wherein the partition type includes one of: time type, digital type.
Further, in another embodiment of the method, the obtaining the current upper boundary of the target partition table according to the table name of the target partition table, the first base table, and the second base table includes:
extracting a partition generation sequence number corresponding to the table name of the target partition table from the first basic table;
extracting a target partition corresponding to the partition generation sequence number from the second basic table;
and taking the upper boundary of the target partition as the current upper boundary of the target partition table.
Further, in another embodiment of the method, the method further comprises:
the partition generation sequence numbers are arranged in a descending order to obtain a sequencing result;
extracting a partition generation sequence number with the front sequencing from the sequencing result;
the extracting the target partition corresponding to the partition generation sequence number from the second basic table includes:
And extracting the target partition corresponding to the partition generation sequence number of which the sequence is front from the second basic table.
Further, in another embodiment of the method, determining a partition expansion statement according to the partition type, the current upper boundary, and a partition parameter includes:
determining a time interval as a partition parameter under the condition that the partition type is a time type;
obtaining an upper boundary of a target according to the current upper boundary and the partition parameters;
and constructing a partition expansion statement according to the upper boundary of the target.
Further, in another embodiment of the method, the determining a partition expansion statement according to the partition type, the current upper boundary, and a partition parameter further includes:
in the case that the partition type is a digital type, determining a digital interval or an expansion coefficient as a partition parameter;
obtaining an upper boundary of a target according to the current upper boundary and the partition parameters;
and constructing a partition expansion statement according to the upper boundary of the target.
Further, in another embodiment of the method, the executing the partition expansion statement performs partition expansion on the target partition table, including: executing the partition expansion statement according to a preset execution mode, and performing partition expansion on the target partition table; wherein the execution mode includes: and executing at regular time and executing in batches.
Further, in another embodiment of the method, after obtaining the table name of the target partition table and the first base table and the second base table corresponding to the target partition table, the method further includes:
extracting the partition number corresponding to the table name of the target partition table from the first basic table;
detecting whether the partition number is smaller than a partition number threshold;
and under the condition that the partition number is smaller than the partition number threshold, executing the table name, the first basic table and the second basic table according to the target partition table to obtain the partition type and the current upper boundary of the target partition table.
An object of an embodiment of the present application is to provide an automatic expanding apparatus for a partition table, including:
the acquisition module is used for acquiring the table name of the target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure;
the determining module is used for obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table;
The generation module is used for determining a partition expansion statement according to the partition type, the current upper boundary and the partition parameters;
and the execution module is used for executing the partition expansion statement and carrying out partition expansion on the target partition table.
It is an object of an embodiment of the present application to provide a computer readable storage medium having stored thereon computer instructions which when executed by a processor implement the above-described method of automatically expanding partition tables.
An object of an embodiment of the present application is to provide a server, which includes at least one processor and a memory for storing instructions executable by the processor, where the processor implements the method for automatically expanding the partition table when executing the instructions.
Based on the automatic expansion method of the partition table, the table name of the target partition table, and the first basic table and the second basic table corresponding to the target partition table are obtained; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure; obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table; determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters; executing the partition expansion statement, and performing partition expansion on the target partition table.
Drawings
In order to more clearly illustrate the embodiments of the present disclosure, the drawings that are required for the embodiments will be briefly described below, and the drawings described below are only some embodiments described in the present disclosure, and other drawings may be obtained according to these drawings without inventive effort for a person of ordinary skill in the art.
FIG. 1 is a schematic diagram of an embodiment of a flow of an automatic expanding method for partition tables according to an embodiment of the present disclosure;
FIG. 2 is a schematic diagram of an embodiment of an automatic expansion device for partition tables according to embodiments of the present disclosure;
fig. 3 is a schematic diagram of an embodiment of a structure of a server according to an embodiment of the present disclosure.
Detailed Description
In order to make the technical solutions in the present specification better understood by those skilled in the art, the technical solutions in the embodiments of the present specification will be clearly and completely described below with reference to the drawings in the embodiments of the present specification, and it is obvious that the described embodiments are only some embodiments of the present specification, not all embodiments. All other embodiments, which can be made by one of ordinary skill in the art without undue burden from the present disclosure, are intended to be within the scope of the present disclosure.
In the technical field of big data, as data in a Gaussian database table is continuously increased, the speed of inquiring the data is reduced, and then the Gaussian database table can be partitioned to obtain a Gaussian database partition table. Partitioning a gaussian database table, namely splitting the gaussian database table into a plurality of partitions, storing different data in each partition, and storing the plurality of partitions into a plurality of table spaces, wherein the gaussian database partition table is still a complete table logically. Therefore, when data is queried, the complete partition table of the Gaussian database is not required to be scanned, and only the corresponding partition is required to be scanned, so that the data query efficiency can be improved. However, in the gaussian database partition table, the upper boundary of the partition cannot be automatically expanded, so as the data increases, there is a problem that the data exceeds the existing partition and cannot be stored.
Aiming at the problems and the specific reasons for generating the problems in the prior art, the application considers providing an automatic expansion method of the partition table, and realizes the automatic expansion of the partition table of the Gaussian database.
Based on the above thought, the present specification proposes an automatic expansion method for a partition table, which includes: obtaining the table name of a target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure; obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table; determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters; executing the partition expansion statement, and performing partition expansion on the target partition table.
It should be noted that, the information data related to the user in the present application is obtained and used on the premise that the user knows and agrees. And, the information data is acquired, stored, used, processed and the like, which meet the relevant regulations of national laws and regulations.
Referring to fig. 1, the present disclosure proposes an automatic expanding method of partition tables, which is applied to a gaussian database. In particular implementations, the method may include the following.
S101: obtaining the table name of a target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second base table is used for storing attributes of the table structure.
In some embodiments, the target partition table is a gaussian database partition table. The first base table is a gaussian system table corresponding to the gaussian database partition table, and the gaussian system table is used for storing a table structure of the gaussian database partition table, for example: partition field number (partkey), partition generation number (relfilenode), system number (partid) of each partition of the gaussian database partition table. The second base table is a system view corresponding to the gaussian database partition table, and the system view is used for storing attributes of the table structure, for example: the data type (data_type) of the data corresponding to the partition field number.
In some embodiments, the Gaussian system table is where the Gaussian database stores structural metadata, which is the source of Gaussian database operation control information, which is a core component of the Gaussian database system. The system view provides a method of querying the gaussian system table and accessing the internal state of the database.
In one embodiment, each partition has an upper boundary, which is the maximum value of the data in the partition, and a lower boundary, which is the minimum value of the data in the partition; and the upper boundary of the previous partition is equal to the lower boundary of the next partition.
In some embodiments, the partition type of the target partition table includes one of: time type, digital type. The upper boundary and the lower boundary of the time type target partition table are time values, and the upper boundary and the lower boundary of the digital type target partition table are numerical values.
In a specific example of a scenario, for a time-type partition SYS_2022_06_01, its upper boundary is set up TO (TO_DATE ('2022-06-01', 'SYYY-MM-DD HH24: MI: SS')) and its lower boundary is set up TO (TO_DATE ('2022-05-01', 'SYYY-MM-DD HH24: MI: SS')), all data with a creation time greater than 2022 on 5-month 1 and less than 2022 on 1-month 6 falls into the partition SYS_2022_06_01. The next partition of SYS_2022_06_01 is SYS_2022_07_01, the upper boundary of which is set up as (TO_DATE ('2022-07-01', 'SYYYY-MM-DD HH24: MI: SS')), and the lower boundary of which is set up as (TO_DATE ('2022-06-01', 'SYYYY-MM-DD HH24: MI: SS')), all data whose creation time is greater than 2022 on 1 month 6 and less than or equal TO 2022 on 1 month 7 falls into this partition of SYS_2022_07_01.
In a specific example of a scenario, for a partition M1 of digital type, its upper boundary is 10000 and its lower boundary is 0, and all data greater than 0 and less than 10000 will fall into M1. The next partition of M1 is M2, whose upper boundary is 20000 and lower boundary is 10000, and all data greater than 10000 and less than 20000 will fall into M2.
In some embodiments, to avoid data exceeding an existing partition, the upper boundary of the last partition of the target partition table needs to be automatically expanded to accommodate more data.
In some embodiments, a digital target partition table may be created by a table creating statement, where the first base table and the second base table corresponding to the target partition table are automatically generated:
--Create table
create table partition_table
(
pk_id VARCHAR2(20)not null,
CREATE_TIME timestamp not null
)
in some embodiments, the partition fields include a primary key ID field and a data attribute field, with pk_id representing the primary key ID field and CREATE_TIME representing the data attribute field. In the first base table, a partition field number (partkey) is used to represent a number of a primary key ID field or a number of a data attribute field, partkey=1 represents a primary key ID field, and partkey=2 represents a data attribute field.
In some embodiments, the data attribute field may be a header of a partition table to represent a data attribute or data type of the column of data. For example, when the data attribute field is "date of birth", the data corresponding to the data attribute field is of a time type, and the data type of the data attribute field itself is also of a time type; when the data attribute field is "expenditure of the month", the data corresponding to the data attribute field is all of a digital type, and the data type of the data attribute field is also of a digital type.
S102: and obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table.
In some embodiments, after obtaining the table name of the target partition table and the first base table and the second base table corresponding to the target partition table, the method further includes: searching the table name of the target partition table in the second basic table, and if the table name of the target partition table does not exist, returning a prompt of 'the table does not belong to the Gaussian database partition table' to the user.
In some embodiments, after obtaining the table name of the target partition table and the first base table and the second base table corresponding to the target partition table, the method further includes:
s1: extracting the partition number corresponding to the table name of the target partition table from the first basic table;
s2: detecting whether the partition number is smaller than a partition number threshold;
s3: and under the condition that the partition number is smaller than the partition number threshold, executing the table name, the first basic table and the second basic table according to the target partition table to obtain the partition type and the current upper boundary of the target partition table.
In some embodiments, the partition number threshold may be set to 1000, and if the partition number is greater than or equal to the partition number threshold, indicating that the partition has not been expanded, a prompt may be returned to the user that the partition number is up to the upper limit.
In some embodiments, obtaining the partition type of the target partition table according to the table name of the target partition table, the first base table, and the second base table specifically includes:
s1: extracting a partition field sequence number corresponding to a table name of the target partition table from the first basic table;
s2: screening a partition field sequence number corresponding to the data attribute field from the partition field sequence number to be used as a target partition field sequence number;
s3: taking the field sequence number of the target partition as a column sequence number;
s4: extracting target data corresponding to the column sequence number from the second basic table;
s5: determining the data type of the target data as the partition type of a target partition table; wherein the partition type includes one of: time type, digital type.
In some embodiments, the partition field sequence number is first extracted from the first base table, which has 3 results: partkey=1, partkey=2, and partkey is empty. Then, the partition field sequence number corresponding to the data attribute field is extracted (i.e. the partition key=2 is extracted), and the partition field sequence number corresponding to the data attribute field is used as the column sequence number of the second base table (i.e. 2 is used as the column sequence number of the second base table). Finally, in the second base table, a query statement of my_tab_columns.column_id=pg_partition.partition key is constructed, target data corresponding to the column sequence number is extracted (i.e. column 2 data of the second base table is extracted), and the data type of the target data is determined and is used as the partition type of the target partition table. Since the second base table is used for storing the attributes of the table structures, the data attribute field is also one of the table structures, and thus the data type corresponding to the data attribute field can be determined by searching the data attribute field in the second base table.
In some embodiments, according to the table name of the target partition table, the first base table, and the second base table, the current upper boundary of the target partition table is obtained, which specifically includes:
s1: extracting a partition generation sequence number corresponding to the table name of the target partition table from the first basic table;
s2: extracting a target partition corresponding to the partition generation sequence number from the second basic table;
s3: and taking the upper boundary of the target partition as the current upper boundary of the target partition table.
In some embodiments, the partition generation sequence number (relfilenode) indicates the order of partition generation, with the greater the partition generation sequence number, the later the time of partition generation.
In some embodiments, the method further comprises:
s1: the partition generation sequence numbers are arranged in a descending order to obtain a sequencing result;
s2: and extracting the partition generation sequence number with the front sequencing from the sequencing result.
The extracting the target partition corresponding to the partition generation sequence number from the second basic table includes:
and extracting the target partition corresponding to the partition generation sequence number of which the sequence is front from the second basic table.
In some embodiments, the partition generation sequence number of the first partition in the order, that is, the largest partition generation sequence number, may be extracted from the ordering result, and the target partition corresponding to the largest partition generation sequence number, that is, the last partition in the target partition table, may be extracted from the second base table. The upper boundary of the last partition is then taken as the current upper boundary (max_high_value) of the target partition table.
S103: and determining a partition expansion statement according to the partition type, the current upper boundary and the partition parameters.
In some embodiments, determining a partition expansion statement according to the partition type, the current upper boundary, and partition parameters specifically includes:
s1: determining a time interval as a partition parameter under the condition that the partition type is a time type;
s2: obtaining an upper boundary of a target according to the current upper boundary and the partition parameters;
s3: and constructing a partition expansion statement according to the upper boundary of the target.
In some embodiments, current upper boundary + time interval = target upper boundary.
In some embodiments, if the set time interval is A1, the statement of the upper boundary of the target is add_montahs (to_date, 'SYYYY-MM-DD HH24: MI: SS'), A1), and max_high_value represents the current upper boundary; the partition expansion statement is ALTER TABLE tableName ADD PARTITION SYS _next_high_ value values le SS than add _montas (to_date, 'SYYYY-MM-DD HH24: MI: SS'), A1).
In one specific example of a scenario, the time interval may be determined to be 1 month, and then the statement of the build of the upper boundary of the target is add_montas (to_date, 'SYYYY-MM-DD HH24: MI: SS'), where 1 represents that the time interval is 1 month, and max_high_value represents the current upper boundary; the partition expansion statement is ALTER TABLE tableName ADD PARTITION SYS _next_high_ value values less than add _montas (to_date, 'SYYYY-MM-DD HH24: MI: SS'), which means that: starting with the current upper boundary, and expanding the current upper boundary at intervals of 1 month to obtain the latest upper boundary, wherein the latest upper boundary is equal to the current upper boundary plus 1 month.
In some embodiments, determining a partition expansion statement according to the partition type, the current upper boundary, and partition parameters specifically includes:
s1: in the case that the partition type is a digital type, determining a digital interval or an expansion coefficient as a partition parameter;
s2: obtaining an upper boundary of a target according to the current upper boundary and the partition parameters;
s3: and constructing a partition expansion statement according to the upper boundary of the target.
In some embodiments, the expansion coefficient refers to a multiple of expanding the current upper boundary by the expansion coefficient=target upper boundary.
In some embodiments, the current upper boundary + the digital interval = target upper boundary.
In some embodiments, the current upper boundary =expansion coefficient=target upper boundary.
In some embodiments, if the number interval is set to A2, the table-building statement of the upper boundary of the target is to_number (max_hig h_value) +a2, and max_high_value represents the current upper boundary; the partition extension statement is ALTER TABLE tableName ADD PARTITION SYS _next_high_ value values less than to _number (max_high_value) +a2.
In a specific scenario example, the number interval may be determined to be 100000, and then the table-building statement of the upper boundary of the target is to_number (max_high_value) +100000, where max_high_value represents the current upper boundary; the partition extension statement is ALTER TABLE tableName ADD PARTITION SYS _next_high_ value values less than to _number (max_high_value) +100000, which means that: starting with the current upper boundary and taking 100000 as an interval, expanding the current upper boundary to obtain the latest upper boundary, wherein the latest upper boundary is equal to the current upper boundary plus 100000.
In some embodiments, if the expansion coefficient is set to A3, the table-building statement of the upper boundary of the target is to_number (max_hig h_value) A3, where max_high_value represents the current upper boundary; the partition extension statement is ALTER TABLE tableName ADD PARTITION SYS _next_high_ value values less than to _number (max_high_value) A3.
In a specific scenario example, the expansion coefficient may be determined to be 2, and then the table-building statement of the upper boundary of the target is to_number (max_high_value) x 2, where max_high_value represents the current upper boundary; the partition extension statement is ALTER TABLE tableName ADD PARTITION SYS _next_high_ value values less than to _number (max_high_value) x 2, which means that: starting with the current upper boundary, expanding the current upper boundary by a factor of 2 to obtain the latest upper boundary, wherein the latest upper boundary is equal to twice of the current upper boundary.
S104: executing the partition expansion statement, and performing partition expansion on the target partition table.
In some embodiments, executing the partition expansion statement, and performing partition expansion on the target partition table specifically includes: executing the partition expansion statement according to a preset execution mode, and performing partition expansion on the target partition table; wherein the execution mode includes: and executing at regular time and executing in batches.
In some embodiments, when the preset execution mode is timing execution, a start time and an end time may be further set, and the partition expansion statement is executed within the range of the start time and the end time. For example, periodically using a quatertz timing task.
In some embodiments, when there are multiple target partition tables, batch execution may be set; serial execution or parallel execution can be further set, wherein serial execution refers to that tasks are executed one by one according to the sequence, and each task can be executed after the execution of the next task; parallel means that a plurality of tasks are performed simultaneously, i.e. the plurality of tasks are performed at the same time and are mutually independent; the task refers to a task of an extended partition. For example, batch execution using DBF (database file format).
Based on the above embodiment, for an application scenario with a large amount of processing data, the timing execution time may be set to be at night (e.g., 0 to 6 points), and the timing execution may be performed at night according to a certain time schedule sequence, so as to avoid affecting normal use of the working time service.
Based on the above method for automatically expanding the partition table, the present disclosure further provides an embodiment of an automatic expanding device for a partition table, as shown in fig. 2, where the automatic expanding device for a partition table specifically includes the following modules: an acquisition module 201, a determination module 202, a generation module 203 and an execution module 204.
The obtaining module 201 is configured to obtain a table name of a target partition table, and a first base table and a second base table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure;
a determining module 202, configured to obtain a partition type and a current upper boundary of the target partition table according to the table name of the target partition table, the first base table, and the second base table;
a generating module 203, configured to determine a partition expansion statement according to the partition type, the current upper boundary, and a partition parameter;
and the execution module 204 is configured to execute the partition expansion statement and perform partition expansion on the target partition table.
In some embodiments, the determining module 202 is specifically configured to extract, from the first base table, a partition field sequence number corresponding to a table name of the target partition table; screening a partition field sequence number corresponding to the data attribute field from the partition field sequence number to be used as a target partition field sequence number; taking the field sequence number of the target partition as a column sequence number; extracting target data corresponding to the column sequence number from the second basic table; determining the data type of the target data as the partition type of a target partition table; wherein the partition type includes one of: time type, digital type.
In some embodiments, the determining module 202 is specifically configured to extract, from the first base table, a partition generation sequence number corresponding to a table name of the target partition table; extracting a target partition corresponding to the partition generation sequence number from the second basic table; and taking the upper boundary of the target partition as the current upper boundary of the target partition table.
In some embodiments, the generating module 203 is specifically configured to determine, when the partition type is a time type, a time interval as a partition parameter; obtaining an upper boundary of a target according to the current upper boundary and the partition parameters; and constructing a partition expansion statement according to the upper boundary of the target.
In some embodiments, the generating module 203 is specifically configured to determine a digital interval or an expansion coefficient as a partition parameter in the case that the partition type is a digital type; obtaining an upper boundary of a target according to the current upper boundary and the partition parameters; and constructing a partition expansion statement according to the upper boundary of the target.
In some embodiments, the execution module 204 is specifically configured to execute the partition expansion statement according to a preset execution mode, and perform partition expansion on the target partition table; wherein the execution mode includes: and executing at regular time and executing in batches.
In some embodiments, the automatic expanding device of the partition table is further configured to extract, from the first base table, a partition number corresponding to a table name of the target partition table; detecting whether the partition number is smaller than a partition number threshold; and under the condition that the partition number is smaller than the partition number threshold, executing the table name, the first basic table and the second basic table according to the target partition table to obtain the partition type and the current upper boundary of the target partition table.
It should be noted that, the units, devices, or modules described in the above embodiments may be implemented by a computer chip or entity, or may be implemented by a product having a certain function. For convenience of description, the above devices are described as being functionally divided into various modules, respectively. Of course, when the present description is implemented, the functions of each module may be implemented in the same piece or pieces of software and/or hardware, or a module that implements the same function may be implemented by a plurality of sub-modules or a combination of sub-units, or the like. The above-described apparatus embodiments are merely illustrative, for example, the division of the units is merely a logical function division, and there may be additional divisions when actually implemented, for example, multiple units or components may be combined or integrated into another system, or some features may be omitted or not performed. Alternatively, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices or units, which may be in electrical, mechanical or other form.
The embodiments of the present specification also provide a computer storage medium storing computer program instructions for an automatic expansion method of partition tables, which when executed by a processor, implement: obtaining the table name of a target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure; obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table; determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters; executing the partition expansion statement, and performing partition expansion on the target partition table.
In the present embodiment, the storage medium includes, but is not limited to, a random access Memory (Random Access Memory, RAM), a Read-Only Memory (ROM), a Cache (Cache), a Hard Disk (HDD), or a Memory Card (Memory Card). The memory may be used to store computer program instructions. The network communication unit may be an interface for performing network connection communication, which is set in accordance with a standard prescribed by a communication protocol.
In this embodiment, the functions and effects of the program instructions stored in the computer storage medium may be explained in comparison with other embodiments, and are not described herein.
The application also provides a computer program product comprising a computer program which, when executed by a processor, realizes: obtaining the table name of a target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure; obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table; determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters; executing the partition expansion statement, and performing partition expansion on the target partition table.
The present disclosure also provides a server comprising a processor and a memory for storing processor-executable instructions, the processor, when embodied, being operable to perform the following steps according to the instructions: obtaining the table name of a target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure; obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table; determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters; executing the partition expansion statement, and performing partition expansion on the target partition table.
In order to more accurately complete the above instructions, referring to fig. 3, another specific server is further provided in this embodiment of the present disclosure, where the server includes a network communication port 301, a processor 302, and a memory 303, and the above structures are connected by an internal cable, so that each structure may perform specific data interaction.
The network communication port 301 may be specifically configured to obtain a table name of a target partition table, and a first base table and a second base table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second base table is used for storing attributes of the table structure.
The processor 302 may be specifically configured to obtain a partition type and a current upper boundary of the target partition table according to the table name of the target partition table, the first base table, and the second base table; determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters; executing the partition expansion statement, and performing partition expansion on the target partition table.
The memory 303 may be used for storing a corresponding program of instructions.
In this embodiment, the network communication port 301 may be a virtual port that binds with different communication protocols, so that different data may be sent or received. For example, the network communication port may be a port responsible for performing web data communication, a port responsible for performing FTP data communication, or a port responsible for performing mail data communication. The network communication port may also be an entity's communication interface or a communication chip. For example, it may be a wireless mobile network communication chip, such as GSM, CDMA, etc.; it may also be a Wifi chip; it may also be a bluetooth chip.
In this embodiment, the processor 302 may be implemented in any suitable manner. For example, the processor may take the form of, for example, a microprocessor or processor, and a computer-readable medium storing computer-readable program code (e.g., software or firmware) executable by the (micro) processor, logic gates, switches, an application specific integrated circuit (Application Specific Integrated Circuit, ASIC), a programmable logic controller, and an embedded microcontroller, among others. The description is not intended to be limiting.
In this embodiment, the memory 303 may include a plurality of layers, and in a digital system, the memory may be any memory as long as it can hold binary data; in an integrated circuit, a circuit with a memory function without a physical form is also called a memory, such as a RAM, a FIFO, etc.; in the system, the storage device in physical form is also called a memory, such as a memory bank, a TF card, and the like.
Although the present description provides method operational steps as described in the examples or flowcharts, more or fewer operational steps may be included based on conventional or non-inventive means. The order of steps recited in the embodiments is merely one way of performing the order of steps and does not represent a unique order of execution. When implemented by an apparatus or client product in practice, the methods illustrated in the embodiments or figures may be performed sequentially or in parallel (e.g., in a parallel processor or multi-threaded processing environment, or even in a distributed data processing environment). The terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, it is not excluded that additional identical or equivalent elements may be present in a process, method, article, or apparatus that comprises a described element. The terms first, second, etc. are used to denote a name, but not any particular order.
Those skilled in the art will also appreciate that, in addition to implementing the controller in a pure computer readable program code, it is well possible to implement the same functionality by logically programming the method steps such that the controller is in the form of logic gates, switches, application specific integrated circuits, programmable logic controllers, embedded microcontrollers, etc. Such a controller can be regarded as a hardware component, and means for implementing various functions included therein can also be regarded as a structure within the hardware component. Or even means for achieving the various functions may be regarded as either software modules implementing the methods or structures within hardware components.
The description may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, classes, etc. that perform particular tasks or implement particular abstract data types. The specification may also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules may be located in both local and remote computer storage media including memory storage devices.
From the above description of embodiments, it will be apparent to those skilled in the art that the present description may be implemented in software plus a necessary general hardware platform. Based on such understanding, the technical solutions of the present specification may be embodied essentially in the form of a software product, which may be stored in a storage medium, such as a ROM/RAM, a magnetic disk, an optical disk, etc., and include several instructions to cause a computer device (which may be a personal computer, a mobile terminal, a server, or a network device, etc.) to perform the methods described in the various embodiments or portions of the embodiments of the present specification.
Various embodiments in this specification are described in a progressive manner, and identical or similar parts are all provided for each embodiment, each embodiment focusing on differences from other embodiments. The specification is operational with numerous general purpose or special purpose computer system environments or configurations. For example: personal computers, server computers, hand-held or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set top boxes, programmable electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments that include any of the above systems or devices, and the like.
Although the present specification has been described by way of example, it will be appreciated by those skilled in the art that there are many variations and modifications to the specification without departing from the spirit of the specification, and it is intended that the appended claims encompass such variations and modifications as do not depart from the spirit of the specification.
Claims (11)
1. An automatic expansion method for a partition table, comprising:
obtaining the table name of a target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure;
obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table;
determining a partition expansion statement according to the partition type, the current upper boundary and partition parameters;
executing the partition expansion statement, and performing partition expansion on the target partition table.
2. The method of claim 1, wherein obtaining the partition type of the target partition table based on the table name of the target partition table, the first base table, and the second base table comprises:
Extracting a partition field sequence number corresponding to a table name of the target partition table from the first basic table;
screening a partition field sequence number corresponding to the data attribute field from the partition field sequence number to be used as a target partition field sequence number;
taking the field sequence number of the target partition as a column sequence number;
extracting target data corresponding to the column sequence number from the second basic table;
determining the data type of the target data as the partition type of a target partition table; wherein the partition type includes one of: time type, digital type.
3. The method of claim 1, wherein obtaining the current upper boundary of the target partition table based on the table name of the target partition table, the first base table, and the second base table comprises:
extracting a partition generation sequence number corresponding to the table name of the target partition table from the first basic table;
extracting a target partition corresponding to the partition generation sequence number from the second basic table;
and taking the upper boundary of the target partition as the current upper boundary of the target partition table.
4. A method according to claim 3, characterized in that the method further comprises:
The partition generation sequence numbers are arranged in a descending order to obtain a sequencing result;
extracting a partition generation sequence number with the front sequencing from the sequencing result;
the extracting the target partition corresponding to the partition generation sequence number from the second basic table includes:
and extracting the target partition corresponding to the partition generation sequence number of which the sequence is front from the second basic table.
5. The method of claim 2, wherein determining a partition extension statement based on the partition type, the current upper boundary, and a partition parameter comprises:
determining a time interval as a partition parameter under the condition that the partition type is a time type;
obtaining an upper boundary of a target according to the current upper boundary and the partition parameters;
and constructing a partition expansion statement according to the upper boundary of the target.
6. The method of claim 2, wherein determining a partition extension statement based on the partition type, the current upper boundary, and a partition parameter, further comprises:
in the case that the partition type is a digital type, determining a digital interval or an expansion coefficient as a partition parameter;
obtaining an upper boundary of a target according to the current upper boundary and the partition parameters;
And constructing a partition expansion statement according to the upper boundary of the target.
7. The method of claim 1, wherein executing the partition expansion statement to partition expand the target partition table comprises:
executing the partition expansion statement according to a preset execution mode, and performing partition expansion on the target partition table; wherein the execution mode includes: and executing at regular time and executing in batches.
8. The method of claim 1, wherein after obtaining the table name of the target partition table and the first base table and the second base table corresponding to the target partition table, the method further comprises:
extracting the partition number corresponding to the table name of the target partition table from the first basic table;
detecting whether the partition number is smaller than a partition number threshold;
and under the condition that the partition number is smaller than the partition number threshold, executing the table name, the first basic table and the second basic table according to the target partition table to obtain the partition type and the current upper boundary of the target partition table.
9. An automatic expansion device for a partition table, the device comprising:
The acquisition module is used for acquiring the table name of the target partition table, and a first basic table and a second basic table corresponding to the target partition table; wherein the target partition table includes a plurality of partitions; the first basic table is used for storing a table structure of the target partition table; the second basic table is used for storing the attribute of the table structure;
the determining module is used for obtaining the partition type and the current upper boundary of the target partition table according to the table name of the target partition table, the first basic table and the second basic table;
the generation module is used for determining a partition expansion statement according to the partition type, the current upper boundary and the partition parameters;
and the execution module is used for executing the partition expansion statement and carrying out partition expansion on the target partition table.
10. A server comprising a processor and a memory for storing processor-executable instructions, which when executed by the processor implement the steps of the method of any one of claims 1 to 8.
11. A computer readable storage medium, having stored thereon computer instructions which, when executed by a processor, implement the steps of the method of any of claims 1 to 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311035608.3A CN117056428A (en) | 2023-08-16 | 2023-08-16 | Automatic expansion method, device and server of partition table |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311035608.3A CN117056428A (en) | 2023-08-16 | 2023-08-16 | Automatic expansion method, device and server of partition table |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN117056428A true CN117056428A (en) | 2023-11-14 |
Family
ID=88662164
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202311035608.3A Pending CN117056428A (en) | 2023-08-16 | 2023-08-16 | Automatic expansion method, device and server of partition table |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN117056428A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN118193541A (en) * | 2024-05-15 | 2024-06-14 | 数翊科技(北京)有限公司 | Partition table creation and partition automatic expansion method based on distributed database |
-
2023
- 2023-08-16 CN CN202311035608.3A patent/CN117056428A/en active Pending
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN118193541A (en) * | 2024-05-15 | 2024-06-14 | 数翊科技(北京)有限公司 | Partition table creation and partition automatic expansion method based on distributed database |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110321344B (en) | Information query method and device for associated data, computer equipment and storage medium | |
| CN108121782B (en) | Distribution method of query request, database middleware system and electronic equipment | |
| CN110309122B (en) | Method, device, server and storage medium for obtaining incremental data | |
| CN117056428A (en) | Automatic expansion method, device and server of partition table | |
| CN107798135B (en) | Paging query method and device and electronic equipment | |
| CN109299101B (en) | Data retrieval method, device, server and storage medium | |
| CN110572464A (en) | Bottom layer resource state synchronization method and device, cloud platform system and storage medium | |
| CN110222046B (en) | List data processing method, device, server and storage medium | |
| CN114595242A (en) | Data operation method and device, computer equipment and storage medium | |
| CN105302827A (en) | Event search method and device | |
| CN114448972A (en) | Distributed storage log compression downloading method, system, terminal and storage medium | |
| CN110874365B (en) | Information query method and related equipment thereof | |
| CN111858607A (en) | Data processing method and device, electronic equipment and computer readable medium | |
| CN109165259B (en) | Index table updating method based on network attached storage, processor and storage device | |
| CN111858606A (en) | Data processing method and device and electronic equipment | |
| CN111858581A (en) | Page query method and device, storage medium and electronic equipment | |
| CN107070987B (en) | Data acquisition method and system for distributed object storage system | |
| CN106446080B (en) | Data query method, query service equipment, client equipment and data system | |
| CN116628042A (en) | Data processing method, device, equipment and medium | |
| CN107679093B (en) | Data query method and device | |
| CN114385657A (en) | Data storage method, device and storage medium | |
| CN109388658B (en) | Data determination method and device | |
| CN110928947A (en) | Data synchronization method and device based on button and related equipment | |
| CN110674214A (en) | Big data synchronization method and device, computer equipment and storage medium | |
| CN110990708A (en) | Hot event determination method and device, storage medium and electronic equipment |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |