Method, device and computer readable medium for implementing automatic partitioned data writes

Abstract

The application provides a data writing method and device for automatic partition and a computer readable medium. The method can create a declarative partition master table, data insertion rules, data update rules, and insertion functions and update functions in advance, bind the insertion functions with the data insertion rules, bind the update functions with the data update rules, call the insertion functions based on the data insertion rules when a data insertion statement is detected, complete the insertion of target data by executing the insertion functions instead of the data insertion statement, call the update functions based on the data update rules when a data update statement is detected, and complete the update of the target data by executing the update functions instead of the data update statement. Thus, while the characteristics of the database declarative partition table are retained, the rules can be used to change the data writing process and bypass the execution sequence limitation of the partition table trigger, so that the data writing can be successfully completed even if the partition sub-table is not created in advance.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of information technology, and in particular to a bilingual corpus detection method and device and a computer readable medium. BACKGROUND

[0002] Modern databases all support partitioned tables to replace single tables to store growing mass data. Such mass data that can be partitioned usually follows certain business rules, such as expanding data sets according to time, self-increasing sequence, etc. If such mass data is stored in a single table, as the data continues to expand, its effective index will also continue to grow, thereby making the index originally created to improve retrieval efficiency grow in size with the growth of mass data, reducing actual query and use efficiency. Modern databases, especially PostgreSQL databases, provide a method to specify how to divide a table into segments called partitions. The divided table is called a partitioned table. By storing data in table partitions, multi-table range parallel queries can be performed on partitioned tables according to query conditions; by table partitioning, insertion efficiency is accelerated, single table index volume is reduced, and data retrieval efficiency is improved.

[0003] PostgreSQL declarative partitioning semantically implements many table partitioning functions, with many excellent features, such as automatic partitioning indexing. If an index is created on a partitioned table, PostgreSQL automatically creates an index with the same properties for each partition. This greatly reduces the maintenance cost of partitioned tables; for example, intelligent partitioned joins. If two partitioned tables with the same partitioning method are used for equality joins using the partition key, direct join operations are allowed using matching partitions.

[0004] The declarative partitioning transaction process does not allow data to be inserted into a non-existent partition before it is created in the same transaction. During the start of the insertion of new data, the database engine collects information about the entire partitioned table; declarative partitioned tables have a built-in routing behavior that occurs after the pre-insertion trigger at the statement level. It will direct the actual INSERT / UPDATE action to the partition according to the partition column for insertion and update. At this time, the main partition table will not trigger its row-level trigger, but will trigger the row-level trigger in the partition table, and will not trigger the statement-level trigger of the partition table. It is not possible to dynamically create new partitions, and the insertion into a non-existent partition will cause the insertion to fail, and the subsequent actions will not be performed. Therefore, it is not possible to achieve the purpose of automatically creating partitions by creating triggers in the partition.

[0005] Currently, there are various schemes for partitioning data in PostgreSQL databases, such as pre-creating a batch of partitioned sub-tables, using non-declarative partitioning of the database itself, and using third-party plugins. Due to the existence of the above mechanisms, these methods will have problems:

[0006] One, the scheme of pre-creating a batch of partition sub-tables.

[0007] The biggest disadvantage of this way is that new partition sub-tables need to be created regularly to adapt to new data range partitioning. The partition sub-tables cannot be created according to the actual data partition where the data in the database falls. All data ranges must be created in advance and closely exist, so as not to miss the corresponding data in the partition.

[0008] Secondly, manual creation of partition sub-tables is not suitable for intensive partitioning, such as table sub-partitioning by minutes. Manual operation becomes impossible to implement persistently, which brings additional downtime overhead to subsequent sustainable maintenance.

[0009] Two, the scheme of using non-database self-declarative partitioning.

[0010] The feature of PostgreSQL cannot insert data in the same transaction range while determining whether a new partition needs to be created through a partition table trigger. A separate mechanism needs to be established to meet the function of automatic partitioning, so the database native declarative partitioning feature cannot be directly used.

[0011] Some additional adaptation work needs to be done to adapt to the dynamic creation of different partition sub-tables. The update of partition sub-table logic depends on the update of the program. The sustainable filling of new data depends on the length of the downtime maintenance overhead, and a series of features within the database partition need to be implemented by the program.

[0012] Three, the scheme of using third-party plug-ins

[0013] The third-party plug-in depends on the upgrade risk, and the production environment can only rely on a specific version. For example, pg_pathman is a third-party plug-in of PostgreSQL database. The project is currently in the maintenance stage, and the official expects to stop subsequent support for PostgreSQL database.

[0014] Therefore, there is no scheme in the prior art that can successfully complete data writing without pre-creating partition sub-tables while retaining the features of database declarative partition tables. SUMMARY

[0015] An object of the present application is to provide a data writing method for implementing automatic partitioning to solve the problem that the existing scheme cannot successfully complete data writing without pre-creating partition sub-tables while retaining the features of database declarative partition tables.

[0016] The data writing method for implementing automatic partitioning provided in the embodiments of the present application comprises:

[0017] precreating a declarative partition master table, data insertion rules, data update rules, and an insertion function and an update function, and binding the insertion function with the data insertion rules and the update function with the data update rules;

[0018] when detecting a data insertion statement, calling an insertion function based on the data insertion rules, and completing insertion of target data by executing the insertion function instead of the data insertion statement;

[0019] when detecting a data update statement, calling an update function based on the data update rules, and completing update of target data by executing the update function instead of the data update statement.

[0020] Further, the insertion of the target data by executing the insertion function instead of the data insertion statement comprises:

[0021] judging whether a partition sub-table corresponding to the target data exists;

[0022] if the partition sub-table exists, inserting the target data in the queried partition sub-table;

[0023] if the partition sub-table does not exist, creating a partition sub-table and inserting the target data in the created partition sub-table.

[0024] Further, the update of the target data by executing the update function instead of the data update statement when detecting the data update statement comprises:

[0025] judging whether an updated field in the target data is a partition key;

[0026] if the updated field is not the partition key, updating the target data in the corresponding partition sub-table;

[0027] if the updated field is the partition key, judging whether a partition sub-table corresponding to the target data exists;

[0028] if the partition sub-table exists, deleting the target data in the original partition sub-table and inserting the target data in the queried partition sub-table;

[0029] if the partition sub-table does not exist, deleting the target data in the original partition sub-table, creating a partition sub-table, and inserting the target data in the created partition sub-table.

[0030] Further, the judgment of whether the partition sub-table corresponding to the target data exists comprises:

[0031] determining a name of the partition sub-table corresponding to the target data according to content of the target data and a preset rule;

[0032] querying, in the database, whether the partitioned sub-table exists according to the name of the partitioned sub-table.

[0033] Further, according to the content of the target data, a name of a corresponding partitioned sub-table is determined according to a preset rule, including:

[0034] According to the timestamp of the target data, a time range interval of the target data is determined according to a preset time interval.

[0035] According to the time range interval, a suffix name of the partitioned sub-table and start time and end time of the partitioned range are determined.

[0036] According to the name of the partitioned main table, the suffix name of the partitioned sub-table, and the start time and end time of the partitioned range, a name of the partitioned sub-table corresponding to the target data is generated.

[0037] Further, whether the partitioned sub-table exists is queried in the database according to the name of the partitioned sub-table, including:

[0038] According to the name of the partitioned sub-table, an association query is performed in a pg_inherits table and a pg_class table of the database, and whether the partitioned sub-table exists is determined according to a query result.

[0039] Further, the target data is inserted into the partitioned sub-table created or queried, including:

[0040] According to the partitioned main table, a data table field is queried, and a partition key in the data table field is filtered to generate a field template.

[0041] According to the field template, the target data is inserted into a corresponding field of the partitioned sub-table.

[0042] The target data is updated in the corresponding partitioned sub-table, including:

[0043] According to the partitioned main table, a data table field is queried, and a partition key in the data table field is filtered to generate a field template.

[0044] According to the field template, the target data is updated in the corresponding field of the partitioned sub-table.

[0045] Further, the method further includes:

[0046] A partition key definition of the partitioned main table is extracted by a pg_get_partkeydef built-in function, and a partition key in the partition key definition is filtered and reserved by a strpos and substr function.

[0047] The embodiment of the present application also provides a data writing device for realizing automatic partitioning, wherein the device comprises a memory for storing computer program instructions and a processor for executing the computer program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to execute the data writing method for realizing automatic partitioning.

[0048] The embodiment of the present application also provides a computer readable medium, which stores computer program instructions, and the computer program instructions can be executed by a processor to realize the data writing method for realizing automatic partitioning.

[0049] In the data writing scheme for realizing automatic partitioning provided by the embodiment of the present application, a declarative partition master table, data insertion rules, data update rules, an insertion function and an update function can be created in advance, the insertion function is bound to the data insertion rules, the update function is bound to the data update rules, when a data insertion statement is detected, the insertion function is called based on the data insertion rules, the insertion of target data is completed by executing the insertion function to replace the data insertion statement, and when a data update statement is detected, the update function is called based on the data update rules, the update of target data is completed by executing the update function to replace the data update statement. Thus, while the characteristics of the declarative partition table of the database are retained, the flow of data writing can be changed by using rules to bypass the execution order limitation of the partition table trigger, and the writing of data can be successfully completed even if the partition sub-table is not created in advance. BRIEF DESCRIPTION OF DRAWINGS

[0050] Other features, objects and advantages of the present application will become more apparent from the following detailed description of non-limiting embodiments read in conjunction with the accompanying drawings:

[0051] Figure 1 A schematic diagram of a conventional data insertion / update flow for a declarative partition table of a PostgreSQL database;

[0052] Figure 2 A schematic diagram of steps of a configuration process of a database for realizing the data writing method provided by the embodiment of the present application;

[0053] The same or similar reference signs in the drawings represent the same or similar components. DETAILED DESCRIPTION

[0054] The present application will be further described in detail below with reference to the accompanying drawings.

[0055] In order to make the objectives, technical solutions and advantages of the embodiments of the present application clearer, the following will be combined with the accompanying drawings for the embodiments of the present application to make a clear and complete description of the technical solutions in the embodiments of the present application. Obviously, the described embodiments are some but not all of the embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by a person of ordinary skill in the art without creative work fall within the protection scope of the present application.

[0056] Those skilled in the art can understand that the singular forms "a", "an" and "the" used in this application include the plural forms, unless specifically stated otherwise. It should be further understood that the use of the term "include" in the specification of the present application means that the features, integers, steps, operations, elements and / or components exist, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof. It should be understood that when we say that an element is "connected" or "coupled" to another element, it can be directly connected or coupled to the other element, or there can be an intermediate element. In addition, "connected" or "coupled" used herein can include wireless connection or wireless coupling. The phrase "and / or" used herein includes all or any single unit and all combinations of the associated listed items.

[0057] In a typical configuration of the present application, the devices of the terminal and the service network each include one or more processors (CPUs), input / output interfaces, network interfaces and memories.

[0058] The memory can include non-permanent memory in a computer readable medium, random access memory (RAM) and / or non-volatile memory such as read only memory (ROM) or flash memory (flash RAM). The memory is an example of a computer readable medium.

[0059] The computer readable medium includes permanent and non-permanent, removable and non-removable media, and can be implemented by any method or technology to store information. The information can be computer program instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read only memory (ROM), electrically erasable programmable read only memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassette, magnetic tape disk storage or other magnetic storage device, or any other non-transmission medium that can be used to store information accessible by a computing device.

[0060] For a declarative partitioned table of PostgreSQL database, all inserted rows will be routed into partitions based on the value of partition key. Each partition has a subset of data defined by its partition boundary. The currently supported partition methods are range, list and hash. That is, for the insert / update operation of a partitioned table, there is a built-in routing rule. According to the definition, in combination with the insert / update operation of the table and the trigger flow, a piece of data will go through the steps as shown in Figure 1 when it is inserted into a partitioned table:

[0061] Step 1. Trigger the pre-insert trigger of the partition master table.

[0062] Step 2. Automatically route to the partitioned sub-table that meets the range.

[0063] Step 3. Trigger the pre-insert trigger of the partitioned sub-table that meets the range.

[0064] Step 4. Data insertion

[0065] Step 5. Trigger the post-insert trigger of the partitioned sub-table that meets the range.

[0066] Step 6. Trigger the post-insert trigger of the partition master table.

[0067] Among them, the partition table master table will only trigger the statement-level trigger, and the partitioned sub-table will only trigger the row-level trigger. The database will automatically handle the routing rule for insert and update actions.

[0068] The entire insertion process is within a transaction, and during the insertion, the entire partition table information is collected; the declarative partitioned table has a built-in routing behavior, which occurs after the pre-insert trigger at the statement level. It will direct the actual insert / update action to the partitioned sub-table for insertion and update according to the partition column. At this time, the partition master table will not trigger its row-level trigger, but will trigger the row-level trigger in the partitioned sub-table, and will not trigger the statement-level trigger of the partitioned sub-table. Therefore, in this process, if the inserted or updated data needs to be written to a partitioned sub-table that has not been created, it is not possible to dynamically create a new partition at the statement level, reach the automatic routing stage, and no longer perform subsequent actions, which will cause the insertion or update to fail.

[0069] Thus, the embodiment of the present application provides a data writing method for realizing automatic partitioning. The method can create a declarative partition master table, data insertion rules, data update rules, and insertion functions and update functions in advance, bind the insertion functions with the data insertion rules, bind the update functions with the data update rules, call the insertion functions based on the data insertion rules when detecting a data insertion statement, complete the insertion of target data by executing the insertion functions to replace the data insertion statement, call the update functions based on the data update rules when detecting a data update statement, and complete the update of target data by executing the update functions to replace the data update statement. Thus, while the characteristics of the database declarative partition table are retained, the flow of data writing can be changed by using rules to bypass the execution order limitation of the partition table trigger, and the writing of data can be successfully completed even if the partition sub-table is not created in advance.

[0070] In actual scenarios, the execution subject of the method can be a user device, a network device, or a device integrated by a user device and a network device through a network, and can also be a program running in the above device. The user device includes, but is not limited to, computers, mobile phones, tablet computers, and various terminal devices. The network device includes, but is not limited to, network hosts, single network servers, multiple network server sets, or computer sets based on cloud computing, and the like. Here, the cloud is composed of a large number of hosts or network servers based on cloud computing, wherein cloud computing is a kind of distributed computing, and a virtual computer composed of a group of loosely coupled computer sets.

[0071] Taking a PostgreSQL database as an example, a declarative partition master table is created, so that the scheme of the present application uses the related characteristics of the PostgreSQL declarative partition, such as automatic indexing of partition, intelligent partition connection, and the like. The data insertion rules and the data update rules utilize the rule system of the PostgreSQL. After the insertion functions are bound with the data insertion rules and the update functions are bound with the data update rules, the execution actions of the related functions can be replaced with the conventional insertion and update actions in the PostgreSQL database by using a query rewriting technology, so as to realize automatic table partitioning during data writing.

[0072] When a data insertion statement (insert) is detected, the insertion functions can be called based on the data insertion rules, and the insertion of target data is completed by executing the insertion functions to replace the data insertion statement. The specific process can include the following steps:

[0073] Firstly, it is judged whether the partition sub-table corresponding to the target data exists. The target data is the data to be inserted this time. The specific way of judgment can be that the name of the partition sub-table corresponding to the target data is determined according to the content of the target data in a preset rule, and then it is queried in the database whether the partition sub-table exists through the name of the partition sub-table. If the partition sub-table is queried to exist, it means that the partition sub-table exists, otherwise, if the partition sub-table is not queried to exist, it means that the partition sub-table does not exist.

[0074] When the boundary range of the partition is related to time, the timestamp in the content of the target data can be related when naming the name of the partition sub-table. Thus, when the name of the partition sub-table corresponding to the target data is determined according to the content of the target data in a preset rule, the time range interval of the target data can be determined according to the timestamp of the target data in a preset time interval, and then the suffix name of the partition sub-table and the start time and end time of the partition range are determined according to the time range interval, and finally the name of the partition sub-table corresponding to the target data is generated according to the name of the partition master table, the suffix name of the partition sub-table and the start time and end time of the partition range.

[0075] The preset time interval can be year, month, week, day, etc., so that the specific time range interval corresponding to the target data can be determined according to the timestamp of the target data. Taking week as an example, if the timestamp of the target data is January 26, 2022, the time range interval corresponding to the target data can be determined as the 4th week of 2022, so that the suffix name of the partition sub-table can be determined as 202204. If the time range contained in the 4th week of 2022 is from 00:00 on January 22, 2022 to 23:59 on January 28, 2022, the start time of the partition range can be determined as 00:00 on January 22, 2022, and the end time of the partition range can be determined as 23:59 on January 28, 2022, which can be recorded as 202201220000 and 202201282359 respectively. After determining the suffix name and the start time and end time of the partition range, the name of the partition sub-table corresponding to the target data is generated according to the name of the partition master table, the suffix name of the partition sub-table and the start time and end time of the partition range. For example, the name of the partition master table is table, and the name of the partition sub-table to which the target data with the timestamp of January 26, 2022 should be inserted is table.202204 (202201220000-202201282359).

[0076] In an actual scenario, a database partition range type can be created in advance, which can include a suffix of a partition sub-table, a start time and an end time of a partition range, and other specific data structures. Thus, when the name of the partition sub-table is determined, the database partition range type can be used to generate the name of the partition sub-table in a standardized manner, so that whether the partition sub-table exists can be determined more conveniently and accurately.

[0077] When it is determined whether the partition sub-table exists in the database through the name of the partition sub-table, a correlation query can be performed in a pg_inherits table and a pg_class table of the database according to the name of the partition sub-table, and whether the partition sub-table exists can be determined according to a query result.

[0078] If the partition sub-table exists, the target data is inserted into the partition sub-table.

[0079] If the partition sub-table does not exist, the partition sub-table is created, and the target data is inserted into the created partition sub-table. The partition sub-table can be created by calling a corresponding function configured in advance, for example, a new partition master function is called in an embodiment of the present application. In the process of creating the partition sub-table, an "IF NOT EXISTS" condition can be added to avoid repeated creation of the partition sub-table.

[0080] When a data update statement (update) is detected, an update function can be called based on the data update rule, and the update function is executed to replace the data update statement to update the target data. When the data is updated, there are two cases. When the updated field is not a partition key, that is, the value of the partition key of the target data is the same as the original value in the partition sub-table, a delete command does not need to be executed, and a new partition sub-table does not need to be created. The relevant field in the partition sub-table corresponding to the target data can be directly updated. When the updated field is a partition key, that is, the value of the partition key of the target data is different from the original value in the partition sub-table, a new partition sub-table can be created. Therefore, it is necessary to determine whether the corresponding partition sub-table exists. If the partition sub-table does not exist, the partition sub-table needs to be created, and then the old data in the original partition sub-table is deleted, and a new record is inserted into the new partition sub-table to realize partition movement.

[0081] The specific process can include the following steps: first, determine whether the updated field in the target data is a partition key. If the updated field is not a partition key, update the target data in the corresponding partition sub-table. If the updated field is a partition key, continue to determine whether the partition sub-table corresponding to the target data exists. When determining whether the partition sub-table corresponding to the target data exists, the same method as when inserting data can be used, that is, first determine the name of the partition sub-table corresponding to the target data according to the content of the target data and a preset rule, and then query whether the partition sub-table exists in the database through the name of the partition sub-table.

[0082] If the partition sub-table corresponding to the target data exists, it means that a new partition sub-table does not need to be created, and at this time the target data can be deleted from the original partition sub-table and inserted into the queried partition sub-table;

[0083] If the partition sub-table corresponding to the target data does not exist, it means that a new partition sub-table needs to be created before the update of the target data can be completed, and at this time the target data can be deleted from the original partition sub-table, a partition sub-table is created, and the target data is inserted into the created partition sub-table.

[0084] When inserting the target data into the created or queried partition sub-table, the data table field of the partition master table can be queried, the partition key in the data table field is filtered, the field template is generated, and then the target data is inserted into the corresponding field of the partition sub-table based on the field template. In this way, the correct format of the target data can be ensured, and errors in the insertion process can be avoided.

[0085] Similarly, when updating the target data in the corresponding partition sub-table, a similar method can also be used, that is, first filter the partition key in the data table field of the partition master table and generate a field template, and then update the target data in the corresponding field of the partition sub-table based on the field template, thereby avoiding errors in the update process.

[0086] Before filtering the partition key in the data table field, the partition key definition of the partition master table can be extracted through the pg_get_partkeydef built-in function, and the partition key in the partition key definition can be filtered and retained through the strpos and substr functions, thereby accurately and quickly obtaining the partition key.

[0087] In actual scenarios, the database can be configured in advance to enable automatic creation of partition sub-tables during data writing while retaining the declarative partition feature. Figure 2 For the steps of the configuration process, the following steps can be used to complete the corresponding configuration:

[0088] Step one, create a database partition range type. The type contains the suffix name of the database partition subtable and the start time and end time of the partition range, so as to facilitate the generation of the partition subtable name later.

[0089] Step two, create a table partition function. The table partition function is composed of a series of functions, and the implementation steps of the table partition function include the following sub-steps:

[0090] Sub-step one, partition table name convention function.

[0091] The partition subtable name convention function is used to generate the name of the subtable according to the rules. The generation rule can be daily partition, weekly partition, monthly partition, annual partition, etc. The function passes in a timestamp of data to be inserted, calculates the time range interval to which the input timestamp belongs according to the day, week, month, and year interval, generates the subtable suffix name according to the time range interval, and the start time and end time of the partition range.

[0092] Sub-step two, get the partition table partition name function

[0093] The function queries the pg_inherits table in the system database (pg_catalog) and the pg_class table in the system database (pg_catalog) to determine whether there is a partition subtable. If there is no partition subtable, return NULL.

[0094] Sub-step three, get the partition table partition key function

[0095] The pg_get_partkeydef built-in function is used to extract the partition key definition of the partition table, and the strpos and substr functions are used to filter and retain the partition column in the partition key definition. The partition column is the partition key.

[0096] Sub-step four, get the partition table update column template function

[0097] The function queries the fields on the main table by inputting the partition table main table, and filters the partition key using the function in sub-step three to generate the update field template.

[0098] Sub-step five, create a new partition main function

[0099] The function is used to create a partition subtable. The function input is the partition table main table name, and the "IF NOT EXISTS" condition needs to be added during the creation of the subtable to avoid duplication.

[0100] Sub-step six, create an insertion function

[0101] Intercept the target data of insertion, call the foregoing sub-steps, query whether the partition sub-table corresponding to the timestamp of the inserted data exists, and if not, create the partition sub-table and then insert into the created partition sub-table.

[0102] Sub-step seven, creating an update function

[0103] Intercept the target data of update, call the foregoing sub-steps, for the update with the same partition key, no delete command needs to be executed, and no new non-existing partition sub-table needs to be created, and the field in the partition sub-table is directly updated. For the data with the updated partition key, it is queried whether the new partition sub-table exists, and if not, the old data on the original partition sub-table is deleted, and the new data is inserted into the new partition sub-table, so that the partition movement is realized.

[0104] Step three, creating a declarative partition table main table without creating any sub-table including a default table;

[0105] Step four, creating a data insertion rule, binding the insertion function created in the foregoing sub-step six to the data insertion rule, and replacing the default insertion processing of the database.

[0106] Step four, creating a data update rule, binding the update function created in the foregoing sub-step seven to the data update rule, and replacing the default update processing of the database.

[0107] When the scheme is applied to PostgreSQL data, the creation of a new partition sub-table no longer needs to be created manually, but can be automatically created according to the target data of insertion or update as needed, and there is no need to automatically create continuous partition sub-tables until it is determined whether a new partition sub-table needs to be created when new data is inserted. At the same time, when data is inserted or updated into the partition sub-table, the query condition does not need to be changed, and the insertion or update can still be performed on the partition main table. In addition, the characteristics of the declarative partition table of the PostgreSQL database are also retained, and functions such as automatic partition indexing, intelligent partition connection, intelligent partition aggregation, cross-partition movement, and partition pruning enhancement can be realized. Automatic routing can also be realized, and the change of the partition logic can take effect immediately and does not depend on the upgrade of the database itself.

[0108] Based on the same inventive concept, the embodiments of the present application also provide a data writing device for realizing automatic partitioning, which corresponds to the method for realizing automatic partitioning in the foregoing embodiments, and has a similar problem solving principle. The data migration device of the heterogeneous database provided in the embodiments of the present application includes a memory for storing computer program instructions and a processor for executing the computer program instructions, wherein when the computer program instructions are executed by the processor, the device triggers the method and / or technical solution of the foregoing embodiments of the present application.

[0109] In particular, the methods and / or embodiments in the present application can be implemented as a computer software program. For example, embodiments of the present disclosure include a computer program product comprising a computer program carried on a computer readable medium, the computer program comprising program code for executing the methods illustrated by the flowcharts. When the computer program is executed by a processing unit, the above-mentioned functions defined in the methods of the present application are performed.

[0110] It should be noted that the computer readable medium in the present application can be a computer readable signal medium or a computer readable storage medium or any combination of the two. The computer readable medium may, for example, be—but is not limited to—electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus or device, or any combination of the above. More specific examples of the computer readable storage medium can include, but are not limited to, an electrical connection having one or more wires, a portable computer diskette, 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 disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present application, the computer readable medium can be any tangible medium that contains or stores a program that can be used by or in connection with an instruction execution system, apparatus or device.

[0111] In the present application, the computer readable signal medium can include a data signal propagated in a baseband or as a carrier wave in a propagated data signal, in which the computer readable program code is carried. Such a propagated data signal can take a variety of forms, including but not limited to electro-magnetic, optical, or any suitable combination thereof. The computer readable signal medium can also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate or transport program for use by or in connection with an instruction execution system, apparatus, or device. Program code contained on the computer readable medium can be transmitted by any suitable medium, including but not limited to wireless, wire line, optical fiber cable, RF, etc., or any suitable combination of the above.

[0112] Computer program code for carrying out operations of the present application can be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C++ or the like, and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code can execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the latter scenario, 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 the connection can be made to an external computer (for example, through the Internet using an Internet Service Provider).

[0113] The flow diagrams and block diagrams in the drawings are representative of the architectural, functional, and operational aspects of possible implementations of apparatuses, methods, and computer program products according to various embodiments of the present application. In this regard, each block in the flow diagrams or block diagrams can represent a module, a segment, or a portion of code, which comprises one or more executable instructions for implementing the specified logical functions. It should also be noted that in some alternative implementations, the functions noted in the blocks can occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently or the blocks may

[0114] As another aspect, the present application also provides a computer readable medium, which can be contained in the apparatus described in the above embodiments, or can exist separately without being assembled into the apparatus. The above computer readable medium carries one or more computer program instructions, which can be executed by a processor to implement the methods and / or technical solutions of the above embodiments of the present application.

[0115] It is noted that the present application can be implemented in software and / or in a combination of software and hardware, e.g., using application specific integrated circuits (ASIC), a general purpose computer or any other similar hardware devices. In some embodiments, software programs implementing the present application can be stored in a computer readable medium, e.g., RAM memory, magnetic or optical drive or diskette, and the like. Further, some of the steps or functionality can be implemented in hardware, e.g., as circuitry which co-operates with the software to perform the various steps or functionality.

[0116] It will be apparent to those skilled in the art that the present application is not limited to the details of the foregoing exemplary embodiments, and that the present application can be implemented in other specific forms without departing from the spirit or essential characteristics thereof. The present embodiments are therefore to be considered in all respects as illustrative and not restrictive, the scope of the application being indicated by the appended claims rather than by the foregoing description, and all changes which come within the meaning and range of equivalency of the claims are therefore intended to be embraced therein. No feature of the application is considered critical unless it is expressly stated as such. Further, no embodiment herein listed is intended to be exhausted or limiting of the application's scope as encompassed by the claims. The meaning of a phrase such as "including", "having", "containing" or "comprising", "consisting of" should not be interpreted as being restrictive. The indefinite articles "a" and "an" should not be construed as limiting the scope of the claims. Any use of the terms "first", "second", "third", etc. is intended to denote a name and not a specific order. The recitation of steps in a method claim having more than one step is not to be construed as implying that the steps are carried out in the order recited. The reference to a sequence of steps should not be interpreted as limiting the order in which the steps are carried out.

Claims

1. A method for data write with automatic partitioning, the method comprising: The method is applied to a PostgreSQL database, and comprises the following steps: Pre-creating a declarative partition master table, data insertion rules, data update rules, and an insertion function and an update function, and binding the insertion function with the data insertion rules and the update function with the data update rules, wherein the data insertion rules and the data update rules utilize a rule system of the PostgreSQL, and after the binding of the insertion function with the data insertion rules and the binding of the update function with the data update rules, the execution actions of the insertion function and the update function are replaced with the conventional insertion and update actions in the PostgreSQL database through a query rewriting technique; When a data insertion statement is detected, the insertion function is called based on the data insertion rules, and the insertion of target data is completed by executing the insertion function to replace the data insertion statement; When a data update statement is detected, the update function is called based on the data update rules, and the update of target data is completed by executing the update function to replace the data update statement; The insertion of target data by executing the insertion function to replace the data insertion statement comprises the following steps: Determining whether a partition sub-table corresponding to the target data exists; If the partition sub-table exists, inserting the target data into the queried partition sub-table; If the partition sub-table does not exist, creating a partition sub-table and inserting the target data into the created partition sub-table. Determining whether a partition sub-table corresponding to the target data exists comprises the following steps: Determining the name of the partition sub-table corresponding to the target data according to the content of the target data and a preset rule; and Querying whether the partition sub-table exists in the database through the name of the partition sub-table.

2. The method of claim 1, wherein, The update of target data by executing the update function to replace the data update statement comprises the following steps: Determining whether an updated field in the target data is a partition key; If the updated field is not the partition key, updating the target data in the corresponding partition sub-table; If the updated field is the partition key, determining whether a partition sub-table corresponding to the target data exists; If the partition sub-table exists, deleting the target data in the original partition sub-table and inserting the target data into the queried partition sub-table; If the partition sub-table does not exist, deleting the target data in the original partition sub-table, creating a partition sub-table, and inserting the target data into the created partition sub-table.

3. The method of claim 1, wherein, Determining the name of the partition sub-table corresponding to the target data according to the content of the target data and a preset rule comprises the following steps: Determining a time range interval of the target data according to a time stamp of the target data and a preset time interval; Determining a suffix name of the partition sub-table and start time and end time of a partition range according to the time range interval; Generating the name of the partition sub-table corresponding to the target data according to a name of a partition master table, the suffix name of the partition sub-table, and the start time and the end time of the partition range.

4. The method of claim 1, wherein, Querying whether the partition sub-table exists in the database through the name of the partition sub-table comprises the following steps: According to the name of the partitioned sub-table, a correlation query is performed in a pg_inherits table and a pg_class table of a database, and whether the partitioned sub-table exists is determined according to a query result.

5. The method according to claim 1 or 2, characterized in that, The target data is inserted into the created or queried partitioned sub-table, including: According to a data table field of the partitioned master table, a partition key in the data table field is filtered, and a field template is generated; Based on the field template, the target data is inserted into a corresponding field of the partitioned sub-table; The target data is updated in the corresponding partitioned sub-table, including: According to a data table field of the partitioned master table, a partition key in the data table field is filtered, and a field template is generated; Based on the field template, the target data is updated in the corresponding field of the partitioned sub-table.

6. The method of claim 4, wherein, The method further includes: A partition key definition of the partitioned master table is extracted through a pg_get_partkeydef built-in function, and a partition key in the partition key definition is filtered and reserved through a strpos and a substr function.

7. A data writing device that implements automatic partitioning, wherein, The device includes a memory for storing computer program instructions and a processor for executing the computer program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to perform the method of any one of claims 1 to 6. 8.A computer readable medium having stored thereon computer program instructions executable by a processor to implement the method of any one of claims 1 to 6.

Images