CN117632527A - Data writing method, device, equipment and medium based on primary key conflict detection - Google Patents

Abstract

The disclosure provides a data writing method, device, equipment and medium based on primary key conflict detection, and relates to the technical field of big data, wherein the method comprises the following steps: responding to a writing request for data to be written, and before the data to be written is written into a destination table, acquiring a first main key of the data to be written and second main keys of all records in the destination table; and determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key, if so, carrying out conflict processing on the target data according to a target conflict processing strategy, and if not, writing the data to be written into a destination table. Therefore, before data writing, target data of all primary key conflicts in the data to be written are detected once, multiple interactions with a database are not needed, the database detects and returns target data of one primary key conflict each time, and the data writing efficiency can be improved.

Description

Data writing method, device, equipment and medium based on primary key conflict detection

Technical Field

The disclosure relates to the technical field of big data and internet, in particular to a data writing method, device, equipment and medium based on primary key conflict detection.

Background

In the related art, batch data may be written into a database by: 1. connecting a database; 2. submitting the batch data to a database; 3. detecting data of the main key conflict by a database, and throwing out the main key conflict abnormality; 4. capturing a main key conflict exception thrown out by a database, and rolling back a transaction; 5. processing the data of the main key conflict; 6. and submitting the batch data to the database again to complete data writing.

However, the use of a database to detect primary key conflicts, and a transaction rollback mechanism to handle primary key conflicts as described above, suffers from at least the following drawbacks: the links for processing the primary key conflicts are long, and the database needs to be operated for many times (the database can only prompt one piece of primary key conflict data at a time), wherein the operations of capturing conflicts, rolling back the transaction and rewriting the database are time-consuming; if there are still primary key conflicts in the resubmitted batch, then the execution needs to be repeated: the processes of detecting the abnormality, capturing the abnormality, processing the data and writing the data are carried out, or batch data are submitted for a single time respectively, so that the writing efficiency of the data is low.

Disclosure of Invention

The object of the present disclosure is to solve at least one of the above technical problems to some extent.

Therefore, the present disclosure proposes a method, an apparatus, a device, and a medium for writing data based on primary key collision detection, so as to detect all primary key collision target data in data to be written at one time without multiple interactions with a database before writing data, and the database detects and returns one primary key collision target data each time, so that the writing efficiency of data can be improved.

An embodiment of a first aspect of the present disclosure provides a data writing method based on primary key collision detection, including:

responding to a writing request for data to be written, and before the data to be written is written into a destination table, acquiring a first main key of the data to be written and second main keys of all records in the destination table;

determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key;

in the case that the target data exist, carrying out conflict processing on the target data according to a target conflict processing strategy;

and writing the data to be written into the destination table in the condition that the target data does not exist.

An embodiment of a second aspect of the present disclosure provides a data writing device based on primary key collision detection, including:

The device comprises an acquisition module, a storage module and a storage module, wherein the acquisition module is used for responding to a writing request for data to be written, and acquiring a first main key of the data to be written and a second main key of each record in a destination table before the data to be written is written in the destination table;

the determining module is used for determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key;

the first processing module is used for carrying out conflict processing on the target data according to a target conflict processing strategy under the condition that the target data exist;

and the writing module is used for writing the data to be written into the destination table under the condition that the target data does not exist.

An embodiment of a third aspect of the present disclosure provides an electronic device, including: a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the data writing method based on primary key collision detection as described in the first aspect when executing the program.

An embodiment of a fourth aspect of the present disclosure proposes a non-transitory computer-readable storage medium, on which a computer program is stored, which when executed by a processor implements a data writing method based on primary key collision detection as described in the first aspect.

An embodiment of a fifth aspect of the present disclosure proposes a computer program product comprising a computer program which, when executed by a processor, implements a data writing method based on primary key collision detection as described in the above first aspect of the present disclosure.

The technical scheme provided by the embodiment of the disclosure at least brings the following beneficial effects:

acquiring a first main key of data to be written and second main keys of all records in a destination table before the data to be written is written in the destination table by responding to a writing request for the data to be written; and determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key, if so, carrying out conflict processing on the target data according to a target conflict processing strategy, and if not, writing the data to be written into a destination table. Therefore, before data writing, target data of all primary key conflicts in the data to be written are detected once, multiple interactions with a database are not needed, the database detects and returns target data of one primary key conflict each time, and the data writing efficiency can be improved.

Additional aspects and advantages of the disclosure will be set forth in part in the description which follows, and in part will be obvious from the description, or may be learned by practice of the disclosure.

Drawings

The foregoing and/or additional aspects and advantages of the present disclosure will become apparent and readily appreciated from the following description of the embodiments, taken in conjunction with the accompanying drawings, in which:

fig. 1 is a schematic flow chart of a data writing method based on primary key collision detection according to an embodiment of the disclosure;

FIG. 2 is a flowchart of another data writing method based on primary key collision detection according to an embodiment of the present disclosure;

FIG. 3 is a flowchart of another data writing method based on primary key collision detection according to an embodiment of the present disclosure;

FIG. 4 is a flowchart of another data writing method based on primary key collision detection according to an embodiment of the present disclosure;

FIG. 5 is a flowchart of another data writing method based on primary key collision detection according to an embodiment of the present disclosure;

fig. 6 is a schematic diagram of an implementation principle of a data writing method according to an embodiment of the disclosure;

FIG. 7 is a schematic diagram of a data writing device based on primary key collision detection according to one embodiment of the present disclosure;

fig. 8 is a schematic structural view of an electronic device according to an embodiment of the present disclosure.

Detailed Description

Embodiments of the present disclosure are described in detail below, examples of which are illustrated in the accompanying drawings, wherein like or similar reference numerals refer to like or similar elements or elements having like or similar functions throughout. The embodiments described below by referring to the drawings are exemplary and intended for the purpose of explaining the present disclosure and are not to be construed as limiting the present disclosure.

In the big data age, databases play a critical role for storing, retrieving and processing large amounts of structured data. ETL (Extract-Transform-Load) is used to describe the process of extracting (Extract), converting (Transform), and loading (Load) data from a source (e.g., a source database) to a destination (e.g., a destination database) as a basic service, and synchronize data between databases to provide data support for each service system. In the ETL scenario, data is typically extracted from a source database and then written to a destination database. When large-scale batch data is written into a target database, a main key conflict situation is often encountered. The primary key is a field in the data table for uniquely identifying each record, and the primary key collision refers to finding that the record of the same primary key exists in the data table when attempting to insert a piece of data.

In the related art, in the scenario of writing batch data into a destination database, the main key conflict detection of the destination database itself may be used, and the transaction rollback mechanism processes the main key conflict, and mainly includes the following steps:

step 1, connecting a target database;

step 2, submitting batch data to a target database;

Step 3, detecting data of the primary key conflict by a target database, and throwing out the primary key conflict abnormality;

step 4, capturing the conflict abnormality of the primary key thrown by the target database, and rolling back the transaction;

step 5, processing the primary key conflict data, namely, processing the primary key conflict data according to a conflict processing strategy, for example, when the conflict processing strategy is an updating strategy, changing an insert statement of the primary key conflict data into an updating statement, and for example, when the conflict processing strategy is a strategy for retaining original data in a target database, directly discarding the primary key conflict data;

and 6, submitting the batch data to a target database again to complete data writing.

Among them, primary key collisions typically include two collision handling strategies: 1. updating a strategy: updating original data in a target database by using new data; 2. policy of preserving original data in destination database: the new data is discarded, and the original data in the target database is reserved.

As can be seen from the above steps, the detection and processing of the primary key collision is time-consuming in the whole data writing process. For example, the data is submitted to the destination database, which is time consuming, and network communication is time consuming; the method that the target database throws out the main key conflict exception only throws out the information of one main key conflict data each time, can not find all the main key conflict data at one time, and needs more time-consuming operation to process all the main key conflicts; requiring the transaction to rollback and resubmit is time consuming.

In view of at least one problem presented above, an embodiment of the present disclosure provides a data writing method, apparatus, device, and medium based on primary key collision detection. Before describing embodiments of the present disclosure in detail, for ease of understanding, general technical words are first introduced:

main key (PRIMARY KEY): often there is a combination of one or more columns in the data table whose values can uniquely identify each row (or each record) in the data table, such one or more columns being referred to as a primary key of the data table by which the physical integrity of the data table can be enforced. When creating or altering a data table, a primary key may be created by defining PRIMARY KEY constraints. One of the tables can only have one PRIMARY KEY constraint, and the columns in the PRIMARY KEY constraint cannot accept null values. Since PRIMARY KEY constraints can ensure unique data, identification columns are often defined.

Map, a data structure in computer science, stores key-value pairs. Each key (key) is unique and can be used to find, delete or update a corresponding value.

The data writing method based on the primary key collision detection provided by the present disclosure is described in detail below with reference to fig. 1.

Fig. 1 is a flowchart of a data writing method based on primary key collision detection according to an embodiment of the present disclosure.

The data writing method based on the primary key collision detection provided by the embodiment of the disclosure can be executed by the data writing device based on the primary key collision detection provided by the embodiment of the disclosure. The data writing device based on the primary key collision detection in the present disclosure is applicable to an electronic apparatus to perform a data writing function based on the primary key collision detection. Alternatively, the data writing apparatus based on the primary key collision detection may be configured in an application of the electronic device so that the application may perform a data writing function based on the primary key collision detection.

The electronic device may be any device with computing power, and the device or an application in the device may be capable of performing a data writing function based on primary key collision detection. The device with computing capability may be, for example, a personal computer, a mobile terminal, a server, etc., and the mobile terminal may be, for example, a vehicle-mounted device, a mobile phone, a tablet computer, a personal digital assistant, a wearable device, etc., with various operating systems, a touch screen, and/or a hardware device of a display screen.

As shown in fig. 1, the data writing method based on primary key collision detection includes the following steps:

step S101, in response to a write request for the data to be written, the first primary key of the data to be written and the second primary keys of the records in the destination table are acquired before the data to be written is written into the destination table.

The number of data to be written may be one or may be multiple, which is not limited by the embodiment of the disclosure.

The destination table is a data table in the destination database.

The number of primary keys (first primary key in the present disclosure) to be written with data may be one or may be plural, which is not limited by the embodiment of the present disclosure. Similarly, the number of primary keys (denoted as second primary keys in this disclosure) for each record in the destination table may be one or more.

In the embodiment of the disclosure, when a write request for data to be written triggered by a user is received, the write request may be responded, and before the data to be written is written into a destination table, a first primary key of the data to be written is acquired, and a second primary key of each record in the destination table is acquired.

Step S102, determining whether target data conflicted by the primary key exists in the data to be written according to the first primary key and each secondary primary key, if so, executing step S103, and if not, executing step S104.

In the embodiment of the present disclosure, whether target data with a primary key conflict exists in data to be written may be determined by comparing the first primary key with each second primary key, that is, the first primary key of the target data is the same as the second primary key of a record in the destination table.

Step S103, performing conflict processing on the target data according to the target conflict processing strategy.

In the embodiment of the disclosure, in the case that target data exists in data to be written, the target data may be subjected to conflict processing according to a target conflict processing policy.

As one possible implementation manner, the manner of performing conflict processing on the target data is, for example: when the target conflict processing policy is an update policy, an update statement may be generated according to the target data, and the update statement may be executed to update the target table according to the target data.

Alternatively, when remaining data other than the target data is included in the data to be written, an insert sentence (denoted as a first insert sentence in this disclosure) may be generated from the remaining data, and the first insert sentence may be executed to write the remaining data into the target table.

As an example, when data to be written is obtained from a source database and a destination table is stored in the destination database, a target conflict handling policy corresponding to the target data may be obtained, if the target conflict handling policy indicates that the data in the source database is preferentially adopted, an update statement is generated according to the target data, and a first insert statement is generated according to the rest of the data except for the target data in the data to be written, after that, the update statement may be executed to update the destination table according to the target data, and the first insert statement is executed to write the rest of the data into the destination table.

Therefore, under the condition that the primary key conflict processing strategy is an updating strategy (namely, a strategy of updating original data in the target database by new data), an update statement is generated according to target data of the primary key conflict, and the update statement is executed so as to update original data in the target database according to the new target data, and therefore the data synchronization requirement in an actual application scene can be met.

As another possible implementation manner, the conflict processing manner is that the target data is processed, for example: when the target conflict handling policy is a policy that preserves the original data in the destination database, the target data may be deleted.

Alternatively, when remaining data other than the target data is included in the data to be written, an insert sentence (denoted as a second insert sentence in this disclosure) may be generated from the remaining data, and the second insert sentence may be executed to write the remaining data into the target table.

As an example, when data to be written is obtained from a source database and a destination table is stored in the destination database, a target conflict handling policy corresponding to the target data may be obtained, if the target conflict handling policy indicates that the data in the destination database is preferentially adopted, the target data is deleted, a second insert sentence is generated according to the rest of the data except for the target data in the data to be written, and then the second insert sentence may be executed to write the rest of the data into the destination table.

Therefore, under the condition that the primary key conflict processing strategy is a strategy for retaining original data in the target database, deleting target data in primary key conflict can meet the data synchronization requirement in the actual application scene.

As an application scenario, before writing data to be written into a destination table, a target conflict processing policy corresponding to the data to be written may be specified or set, and when target data with a primary key conflict in the data to be written is detected, batch processing may be performed on the target data according to a target conflict processing policy specified in advance, so as to improve the writing efficiency of the data.

Step S104, the data to be written is written into the destination table.

In the embodiment of the present disclosure, in the case where the target data does not exist in the data to be written, the data to be written may be directly written into the destination table.

As a possible implementation manner, the writing manner of the data to be written is, for example: and generating a third insert statement according to the data to be written, and executing the third insert statement to write the data to be written into the destination table.

Therefore, when the data to be written does not have the primary key conflict data, all the data to be written are written into the destination table, and the actual data writing requirement can be met.

According to the data writing method based on the primary key conflict detection, a first primary key of data to be written and second primary keys of all records in a destination table are obtained before the data to be written is written in the destination table in response to a writing request for the data to be written; and determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key, if so, carrying out conflict processing on the target data according to a target conflict processing strategy, and if not, writing the data to be written into a destination table. Therefore, before data writing, target data of all primary key conflicts in the data to be written are detected once, multiple interactions with a database are not needed, the database detects and returns target data of one primary key conflict each time, and the data writing efficiency can be improved.

In order to clearly illustrate how the above embodiments detect target data, the present disclosure also proposes a data writing method based on primary key collision detection.

Fig. 2 is a flowchart of another data writing method based on primary key collision detection according to an embodiment of the present disclosure.

As shown in fig. 2, based on the embodiment shown in fig. 1, step S102 may specifically include the following steps:

Step S201, a main key list is generated according to each second main key.

In the embodiment of the present disclosure, a primary key list may be generated according to the second primary keys of each record in the destination table, that is, the primary key list includes the second primary key of each record in the destination table.

As one possible implementation, the primary key list may be stored in a Map, where the Map may be stored in memory or message middleware (e.g., hazelcast (a distributed computing and storage platform), dis (Remote Dictionary Server), i.e., remote dictionary service), etc., to reduce storage resource usage.

Step S202, writing the first primary key of each data to be written into the first message queue.

Step S203, a plurality of first threads are called to acquire a first main key from the first message queue, a main key list is queried according to the acquired first main key, whether a second main key matched with the acquired first main key exists in the main key list is determined, if so, target data exist, and data to be written corresponding to the acquired first main key is determined to be the target data.

In the embodiment of the present disclosure, in order to improve the primary key comparison efficiency, that is, improve the detection efficiency of the target data, a plurality of first threads may be invoked to concurrently perform the primary key comparison operation, so as to determine, from the data to be written, the target data in which the primary key conflict exists. Specifically, a plurality of first threads can be called simultaneously to acquire a first main key from a first message queue, and a main key list is queried according to the acquired first main key, so as to determine whether a second main key matched with the acquired first main key exists in the main key list, if so, it is determined that target data with main key conflict exists in data to be written, and the data to be written corresponding to the acquired first main key is used as target data.

In one possible implementation manner of the embodiment of the present disclosure, the first thread is created, for example, as follows: the resource occupancy rate (such as CPU (Central Processing Unit, central processing unit) occupancy rate, memory occupancy rate, occupancy rate of other hardware resources, etc.) of the device is obtained, the number of first threads to be created is determined according to the resource occupancy rate, and the number of first threads is created.

For example, when the resource occupancy is relatively low, a relatively large number of first threads may be created, and when the resource occupancy is relatively high, a relatively small number of first threads may be created.

Therefore, the number of threads to be created is determined based on the resource occupancy rate of the equipment, equipment resources can be reasonably utilized, resource waste is avoided, and the main key comparison operation is executed through a plurality of threads, so that the data writing efficiency can be further improved.

According to the data writing method based on the primary key conflict detection, primary key inquiry and comparison operation are executed through multiple threads concurrently, so that comparison efficiency can be improved, and detection efficiency of target data is improved.

In order to clearly illustrate how target data is detected in any embodiment of the present disclosure, the present disclosure also proposes a data writing method based on primary key collision detection.

Fig. 3 is a flowchart of another data writing method based on primary key collision detection according to an embodiment of the present disclosure.

As shown in fig. 3, based on the embodiment shown in fig. 1, step S102 may specifically include the following steps:

step S301, hash operation is performed on the first primary key of each data to be written, so as to obtain a first hash value of each data to be written.

In the embodiment of the present disclosure, hash operations may be performed on the first primary keys of each piece of data to be written based on a hash algorithm, so as to obtain first hash values of each piece of data to be written.

Step S302, hash operation is carried out on each second main key to obtain a second hash value corresponding to each second main key.

Similarly, the same hash algorithm may be adopted to perform hash operation on the second primary key of each record, so as to obtain the second hash value of each record.

Step S303, determining whether target data exists in each data to be written according to the first hash value and each second hash value of each data to be written.

In the embodiment of the present disclosure, whether target data with a primary key conflict exists in each piece of data to be written may be determined by comparing the first hash value of each piece of data to be written with each second hash value, that is, the first hash value of the target data is the same as the second hash value of a record in the destination table.

In any one embodiment of the present disclosure, in order to improve the comparison efficiency of hash values, that is, improve the detection efficiency of target data, a plurality of second threads may be invoked to concurrently perform hash value comparison operations to determine target data having a primary key collision from data to be written.

Specifically, the first hash value of each piece of data to be written can be written into the second message queue, a hash list is generated according to each second hash value, then a plurality of second threads can be called to acquire the first hash value from the second message queue, the hash list is queried according to the acquired first hash value to determine whether the second hash value matched with the acquired first hash value exists in the hash list, if the second hash value does not exist, the first primary key corresponding to the acquired first hash value and the second primary key corresponding to the matched second hash value are indicated to not collide, if the second hash value exists, the first primary key corresponding to the acquired first hash value and the second primary key corresponding to the matched second hash value cannot be indicated to have collision, at the moment, whether the first primary key corresponding to the acquired first hash value and the second primary key corresponding to the matched second hash value are matched can be further judged, if the first primary key corresponding to the acquired first hash value and the second primary key corresponding to the matched second hash value are indicated to have collision, and the data to be obtained as the primary key corresponding to be matched, and the data to be matched is determined.

The second thread is created in a similar manner to the first thread, and will not be described herein.

Therefore, the hash value query and comparison operation are executed concurrently by a plurality of threads, so that the comparison efficiency can be improved, and the detection efficiency of the target data can be further improved.

According to the data writing method based on the primary key conflict detection, whether the target data with the primary key conflict exists in the data to be written is determined based on the hash value corresponding to the primary key, and the hash value is a data compression mode, so that the comparison efficiency and the accuracy of the comparison result can be considered, the detection efficiency and the accuracy of the detection result of the target data are improved, and the writing efficiency and the writing accuracy of the data are further improved.

In any embodiment of the present disclosure, the number of the first primary keys to be written into the data may be plural, and similarly, the number of the second primary keys recorded in each record may be plural, where in order to improve the accuracy of the comparison result, each primary key to be written into the data needs to be compared with each primary key recorded in the destination table. Next, a detailed description will be given of an alignment method of the plurality of primary keys with reference to fig. 4.

Fig. 4 is a flowchart of another data writing method based on primary key collision detection according to an embodiment of the present disclosure.

As shown in fig. 4, based on the embodiment shown in fig. 1, step S102 may specifically include the following steps:

step S401, merging the plurality of first primary keys of any data to be written to obtain a first merged primary key of any data to be written.

In the embodiment of the present disclosure, for any piece of data to be written, a plurality of first primary keys of the data to be written may be combined (or spliced) to obtain a first combined primary key of the data to be written.

Step S402, merging the plurality of second primary keys of any record to obtain a second merged primary key of any record.

In the embodiment of the present disclosure, for any record in the destination table, multiple second primary keys of the record may be combined (or spliced) to obtain a second combined primary key of the record.

Step S403, determining whether the target data exists in the data to be written according to the first merging main key and the second merging main key of the data to be written.

In the embodiment of the present disclosure, whether target data with a primary key conflict exists in each piece of data to be written may be determined by comparing the first merged primary key with each second merged primary key of each piece of data to be written.

As a possible implementation manner, a merging primary key list may be generated according to each second merging primary key, the first merging primary key of each piece of data to be written is written into a third message queue, then, a plurality of third threads may be called to acquire the first merging primary key from the third message queue, query the merging primary key list according to the acquired first merging primary key, so as to determine whether a second merging primary key matched with the acquired first merging primary key exists in the merging primary key list, if so, determine that target data with primary key conflict exists in the data to be written, and use the data to be written corresponding to the acquired first merging primary key as target data.

The third thread is created in a similar manner to the first thread, and will not be described herein.

As another possible implementation manner, hash operations may be performed on the first merged primary key of each piece of data to be written respectively to obtain a third hash value of each piece of data to be written, hash operations may be performed on the second merged primary key of each record respectively to obtain a fourth hash value of each record, and then, whether target data with primary key conflicts exists in each piece of data to be written may be determined according to the third hash value and each fourth hash value of each piece of data to be written.

For example, a third hash value of each piece of data to be written may be written into a fourth message queue, and a hash value list may be generated according to each recorded second hash value, then, a plurality of fourth threads may be called to acquire the third hash value from the fourth message queue, and query the hash value list according to the acquired third hash value, so as to determine whether a fourth hash value matched with the acquired third hash value exists in the hash value list, if so, whether a first merging primary key corresponding to the acquired third hash value is matched with a second merging primary key corresponding to the matched fourth hash value is determined, if so, whether target data exists is determined, and the data to be written corresponding to the acquired third hash value is used as target data.

According to the data writing method based on the primary key conflict detection, when all primary keys of the data to be written are matched with all primary keys of a record in the destination table, the data to be written is used as target data of the primary key conflict, and accuracy of primary key conflict detection can be improved. And under the condition that the number of the main keys of each record in the data to be written or the destination table is a plurality of, merging the plurality of main keys of the same data to obtain a merged main key, detecting target data of main key conflict by a mode of comparing the merged main key once without comparing each main key one by one, improving the comparison efficiency, saving storage space and reducing resource consumption by merging the main keys.

In order to clearly illustrate how to perform conflict processing on target data according to a target conflict processing policy in any embodiment of the present disclosure, the present disclosure further provides a data writing method based on primary key conflict detection.

Fig. 5 is a flowchart of another data writing method based on primary key collision detection according to an embodiment of the present disclosure.

As shown in fig. 5, on the basis of the embodiment shown in fig. 1, step S103 may specifically include the following steps:

in step S501, when at least one item of target data exists, a target conflict processing policy corresponding to each item of target data is determined from at least one configured conflict processing policy according to attribute information of each item of target data.

Wherein the attribute information may include, but is not limited to: traffic type, data source, timestamp, etc., which may include, but is not limited to: creation time or modification time.

Among the conflict handling policies include, but are not limited to: updating policies (for indicating the preferential use of data in the source database), policies that preserve the original data in the destination database (for indicating the preferential use of data in the destination database), and the like.

In the embodiment of the disclosure, when at least one item of target data exists in the data to be written, attribute information of each item of target data may be acquired, and a target conflict processing policy corresponding to each item of target data may be determined from at least one configured conflict processing policy according to the attribute information of each item of target data.

For example, taking the example that the attribute information includes a service type, the target conflict handling policy corresponding to the target data belonging to the service type a may be an update policy, and the target conflict handling policy corresponding to the target data belonging to the service type B may be a policy for retaining original data in the destination database.

For another example, taking the example that the attribute information includes the data source, the target conflict processing policy corresponding to the target data of the data source 1 may be a policy of retaining the original data in the destination database, and the target conflict processing policy corresponding to the target data of the data source 2 may be an update policy.

For another example, taking the example that the attribute information includes a timestamp (such as a creation time), a target conflict processing policy corresponding to target data with a time difference between the creation time and the current time being smaller than a set threshold may be an update policy, and a target conflict processing policy corresponding to target data with a time difference between the creation time and the current time being greater than or equal to the set threshold may be a policy for retaining original data in the destination database.

Step S502, adopting a target conflict processing strategy corresponding to each target data to process conflict processing on each target data.

In the embodiment of the disclosure, a target conflict processing policy corresponding to each item of target data may be used to perform conflict processing on each item of target data.

As an example, for any one item of target data, when the target conflict processing policy corresponding to the target data is an update policy, an update statement may be generated according to the target data, and an insert statement may be generated according to the rest of the data except the target data in the data to be written, and the update statement may be executed to update the target table according to the target data, and the insert statement may be executed to write the rest of the data into the target table.

As another example, for any one item of target data, when the target conflict processing policy corresponding to the target data is a policy of retaining original data in the target database, the target data may be deleted, and an insert sentence may be generated according to the remaining data except for the target data in the data to be written, and the insert sentence may be executed to write the remaining data into the target table.

According to the data writing method based on the primary key conflict detection, as attribute information of different target data may be different and conflict processing strategies corresponding to different attribute information are different, in the method, the target conflict processing strategy corresponding to each item of target data is determined pertinently according to the attribute information of the target data, and the target data is processed according to the target conflict processing strategy, so that personalized data writing requirements under different application scenes can be met.

In any embodiment of the present disclosure, the number of data to be written is taken as a plurality of pieces to perform an example, in order to improve the efficiency of primary key collision detection, it is not necessary to rely on primary key collision detection of a database itself, and interactions with the database are reduced.

As an example, the implementation principle of the data writing method provided by the embodiment of the present disclosure may be as shown in fig. 6, and mainly includes the following steps:

step 1, before data writing, identifying the main key of each record in the destination table. For example, a destination table in a destination database may be queried to obtain the primary keys of all records.

And 2, storing the main keys of each record into a Map.

It should be noted that if a record has multiple primary keys, the multiple primary keys of the record are combined or spliced to obtain a combined primary key, and then stored in the Map.

And step 3, traversing the data to be written, and searching whether a main key of the data to be written exists in the Map, if so, indicating that the main key conflict exists.

It should be noted that, if there are multiple primary keys in the data to be written, the Map is queried after the multiple primary keys of the data to be written are combined or spliced.

And 4, generating SQL (Structured Query Language ) sentences according to the query result.

It should be noted that, if there is no target data with a primary key conflict in the data to be written, an insert statement is generated according to the data to be written, and the insert statement is executed to write the data to be written into the target table. Alternatively, after the data to be written is written into the destination table, the primary key of the data to be written may also be stored in the Map.

In the case that there is target data with a primary key conflict in the data to be written, the target data may be processed according to a conflict processing policy corresponding to the target data, for example, when the conflict processing policy indicates that there is data in the target database updated with new data (i.e., indicates that the source database is preferentially adopted), an update statement may be generated according to the target data, and the update data may be executed to update the target table according to the target data; when the conflict processing policy indicates to retain the original data in the destination database (i.e., indicates to preferentially employ the data in the destination database), the target data may be deleted without generating the SQL statement.

And 5, submitting SQL sentences in batches.

And 6, successfully writing.

Alternatively, if the system is a distributed multi-instance architecture, the Map may be replaced with message middleware such as hazelcast, redis to ensure data consistency among the instances.

It can be understood that for the primary key query, since the primary key is generally a field with a shorter length, the query time is less, the occupied space after the primary key is stored in the memory is also less, the pressure on the system load is relatively less, and the writing performance of the data can be greatly improved.

Compared with the prior art, the data writing method based on the primary key conflict detection has at least the following advantages:

1. in the prior art, a database is relied on to detect the main key conflict, and only one piece of conflict data can be prompted at a time, and in the method, before data is written, all data with the main key conflict in the data to be written are detected rapidly, so that the writing efficiency of the data can be improved.

2. In the prior art, when the primary key conflict is detected, the data needs to be rolled back, and then the data is processed for many times and written into the database.

3. In the prior art, corresponding anomalies are required to be captured for each database, and different error codes are identified, but in the method, the main key conflict is detected without depending on the database anomalies, so that the data is directly processed in the memory, and the method is applicable to all database types needing to process the main key conflict, and has wider applicability.

In summary, the data writing method based on primary key collision detection provided by the present disclosure has at least the following advantages:

1. the real-time performance of data synchronization is improved: the writing speed of the database is increased, and the data can enter the target database more quickly, so that the real-time performance of data synchronization is improved.

2. Improving the data processing capacity: since the data volume in the big data age is very huge, if the writing speed of the database is slow, data accumulation and delay may be caused, and thus the overall data processing capacity is reduced. The writing speed of the database is accelerated, the processing requirement of a large amount of data can be better met, and the data processing capacity is improved.

3. Enhancing user experience: for some application scenes with higher real-time requirements, the writing speed of the database is increased, the user experience can be improved, for example, the user can obtain the latest data faster, and therefore the satisfaction degree of the user is improved.

4. The product competitiveness is improved: in the current competitive market environment, the performance of the product is one of the core competence, and the advantage of the large data product in the writing speed is also the embodiment of the technical capability.

Corresponding to the data writing method based on the primary key collision detection provided in the above embodiments, one embodiment of the present disclosure further provides a data writing device based on the primary key collision detection. Since the data writing device based on the primary key collision detection provided in the embodiment of the present disclosure corresponds to the data writing method based on the primary key collision detection provided in the above-described several embodiments, the implementation of the data writing method based on the primary key collision detection is also applicable to the data writing device based on the primary key collision detection provided in the embodiment, and will not be described in detail in the embodiment.

Fig. 7 is a schematic structural diagram of a data writing device based on primary key collision detection according to an embodiment of the present disclosure.

As shown in fig. 7, the data writing apparatus 700 based on primary key collision detection may include: the acquisition module 710, the determination module 720, the first processing module 730, and the writing module 740.

The obtaining module 710 is configured to obtain, in response to a write request for the data to be written, a first primary key of the data to be written and a second primary key of each record in the destination table before writing the data to be written into the destination table.

The determining module 720 is configured to determine whether target data with a primary key conflict exists in the data to be written according to the first primary key and each second primary key.

The first processing module 730 is configured to, in the case where there is target data, perform conflict processing on the target data according to a target conflict processing policy.

The writing module 740 is configured to write the data to be written into the destination table in the case where the target data does not exist.

As one possible implementation manner of the embodiments of the present disclosure, the amount of data to be written is at least one piece; the determining module 720 is specifically configured to: generating a main key list according to each second main key; writing the first primary key of each data to be written into a first message queue; calling a plurality of first threads to acquire a first main key from a first message queue, inquiring a main key list according to the acquired first main key to determine whether a second main key matched with the acquired first main key exists in the main key list, if so, determining that target data exists, and taking the data to be written corresponding to the acquired first main key as the target data.

As one possible implementation of the embodiments of the present disclosure, the primary key list is stored in memory or message middleware.

As one possible implementation manner of the embodiment of the present disclosure, the data writing apparatus 700 based on primary key collision detection may further include:

the second processing module is used for acquiring the resource occupancy rate of the equipment, and determining the number of first threads to be created and the number of first threads to be created according to the resource occupancy rate.

As one possible implementation manner of the embodiments of the present disclosure, the amount of data to be written is at least one piece; the determining module 720 is specifically configured to: carrying out hash operation on a first primary key of each data to be written respectively to obtain a first hash value of each data to be written; performing hash operation on each second main key to obtain a second hash value corresponding to each second main key; and determining whether target data exist in each piece of data to be written according to the first hash value and the second hash value of each piece of data to be written.

As one possible implementation of the embodiments of the present disclosure, the determining module 720 is specifically configured to: writing the first hash value of each data to be written into a second message queue; generating a hash list according to each second hash value; and calling a plurality of second threads to acquire a first hash value from the second message queue, inquiring a hash list according to the acquired first hash value to determine whether a second hash value matched with the acquired first hash value exists in the hash list, judging whether a first main key corresponding to the acquired first hash value is matched with a second main key corresponding to the matched second hash value if the second hash value exists, determining that target data exists if the first main key corresponding to the acquired first hash value is matched with the second main key corresponding to the matched second hash value, and taking the data to be written corresponding to the acquired first hash value as the target data.

As one possible implementation manner of the embodiment of the present disclosure, the number of first primary keys of each piece of data to be written is plural, and the number of second primary keys of each record is plural; the determining module 720 is specifically configured to: combining a plurality of first main keys of any data to be written to obtain a first combined main key of any data to be written; merging the plurality of second primary keys of any record to obtain a second merged primary key of any record; and determining whether target data exists in the data to be written according to the first merging main key and the second merging main key of the data to be written.

As a possible implementation manner of the embodiments of the present disclosure, the data to be written is obtained from a source database, and the destination table is stored in the destination database; the first processing module 730 is specifically configured to: under the condition that target data exist, acquiring a target conflict processing strategy corresponding to the target data; generating an update statement according to the target data under the condition that the target conflict processing strategy indicates that the data in the source database is preferentially adopted; generating a first insert statement according to the rest data except the target data in the data to be written; and executing an update statement to update the destination table according to the target data, and executing a first insert statement to write the rest of data into the destination table.

As a possible implementation manner of the embodiments of the present disclosure, the data to be written is obtained from a source database, and the destination table is stored in the destination database; the first processing module 730 is specifically configured to: under the condition that target data exist, acquiring a target conflict processing strategy corresponding to the target data; deleting target data under the condition that the target conflict processing strategy indicates that the data in the target database is preferentially adopted; generating a second insert statement according to the rest data except the target data in the data to be written; the second insert statement is executed to write the remaining data to the destination table.

As one possible implementation manner of the embodiment of the present disclosure, the number of target data is at least one, and the first processing module 730 is specifically configured to: determining a target conflict processing strategy corresponding to each target data from at least one configured conflict processing strategy according to attribute information of each target data under the condition that the target data exist; performing conflict processing on each target data by adopting a target conflict processing strategy corresponding to each target data; wherein the attribute information includes: at least one of a traffic type, a data source, a timestamp including a creation time or a modification time.

As one possible implementation of the embodiments of the present disclosure, the writing module 740 is specifically configured to: generating a third insert sentence according to the data to be written under the condition that the target data does not exist; a third insert statement is executed to write the data to be written to the destination table.

According to the data writing device based on the primary key conflict detection, a first primary key of data to be written and second primary keys of all records in a destination table are acquired before the data to be written is written in the destination table in response to a writing request for the data to be written; and determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key, if so, carrying out conflict processing on the target data according to a target conflict processing strategy, and if not, writing the data to be written into a destination table. Therefore, before data writing, target data of all primary key conflicts in the data to be written are detected once, multiple interactions with a database are not needed, the database detects and returns target data of one primary key conflict each time, and the data writing efficiency can be improved.

In order to implement the above embodiments, the present disclosure further provides an electronic device, and fig. 8 is a schematic structural diagram of the electronic device provided in the embodiment of the present disclosure. The electronic device includes:

A memory 801, a processor 802, and a computer program stored on the memory 801 and executable on the processor 802.

The processor 802 implements the data writing method based on primary key collision detection provided in any of the above embodiments when executing the program.

Further, the electronic device further includes:

a communication interface 803 for communication between the memory 801 and the processor 802.

A memory 801 for storing a computer program executable on the processor 802.

The memory 801 may include high-speed RAM memory or may further include non-volatile memory (non-volatile memory), such as at least one magnetic disk memory.

And a processor 802, configured to implement the data writing method based on primary key collision detection according to any one of the foregoing embodiments when executing the program.

If the memory 801, the processor 802, and the communication interface 803 are implemented independently, the communication interface 803, the memory 801, and the processor 802 may be connected to each other through a bus and perform communication with each other. The bus may be an industry standard architecture (Industry Standard Architecture, abbreviated ISA) bus, an external device interconnect (Peripheral Component, abbreviated PCI) bus, or an extended industry standard architecture (Extended Industry Standard Architecture, abbreviated EISA) bus, among others. The buses may be classified as address buses, data buses, control buses, etc. For ease of illustration, only one thick line is shown in fig. 8, but not only one bus or one type of bus.

Alternatively, in a specific implementation, if the memory 801, the processor 802, and the communication interface 803 are integrated on a chip, the memory 801, the processor 802, and the communication interface 803 may communicate with each other through internal interfaces.

The processor 802 may be a central processing unit (Central Processing Unit, abbreviated as CPU), or an application specific integrated circuit (Application Specific Integrated Circuit, abbreviated as ASIC), or one or more integrated circuits configured to implement embodiments of the present disclosure.

In order to implement the above-described embodiments, the embodiments of the present disclosure also propose a non-transitory computer-readable storage medium, on which a computer program is stored, which when executed by a processor implements a data writing method based on primary key collision detection as provided in any of the above-described embodiments.

In order to implement the above embodiments, the embodiments of the present disclosure further propose a computer program product, which when executed by an instruction processor in the computer program product, implements the data writing method based on primary key collision detection provided in any of the above embodiments.

In the description of the present specification, a description referring to terms "one embodiment," "some embodiments," "examples," "specific examples," or "some examples," etc., means that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the present disclosure. In this specification, schematic representations of the above terms are not necessarily directed to the same embodiment or example. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples. Furthermore, the different embodiments or examples described in this specification and the features of the different embodiments or examples may be combined and combined by those skilled in the art without contradiction.

Furthermore, the terms "first," "second," and the like, are used for descriptive purposes only and are not to be construed as indicating or implying a relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defining "a first" or "a second" may explicitly or implicitly include at least one such feature. In the description of the present disclosure, the meaning of "a plurality" is at least two, such as two, three, etc., unless explicitly specified otherwise.

Any process or method descriptions in flow charts or otherwise described herein may be understood as representing modules, segments, or portions of code which include one or more executable instructions for implementing specific logical functions or steps of the process, and additional implementations are included within the scope of the preferred embodiment of the present disclosure in which functions may be executed out of order from that shown or discussed, including substantially concurrently or in reverse order, depending on the functionality involved, as would be understood by those reasonably skilled in the art of the embodiments of the present disclosure.

Logic and/or steps represented in the flowcharts or otherwise described herein, e.g., a ordered listing of executable instructions for implementing logical functions, can be embodied in any computer-readable medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions. For the purposes of this description, a "computer-readable medium" can be any means that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the computer-readable medium would include the following: an electrical connection (electronic device) having one or more wires, a portable computer diskette (magnetic device), a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). In addition, the computer readable medium may even be paper or other suitable medium on which the program is printed, as the program may be electronically captured, via, for instance, optical scanning of the paper or other medium, then compiled, interpreted or otherwise processed in a suitable manner, if necessary, and then stored in a computer memory.

It should be understood that portions of the present disclosure may be implemented in hardware, software, firmware, or a combination thereof. In the above-described embodiments, the various steps or methods may be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system. As with the other embodiments, if implemented in hardware, may be implemented using any one or combination of the following techniques, as is well known in the art: discrete logic circuits having logic gates for implementing logic functions on data signals, application specific integrated circuits having suitable combinational logic gates, programmable Gate Arrays (PGAs), field Programmable Gate Arrays (FPGAs), and the like.

Those of ordinary skill in the art will appreciate that all or a portion of the steps carried out in the method of the above-described embodiments may be implemented by a program to instruct related hardware, where the program may be stored in a computer readable storage medium, and where the program, when executed, includes one or a combination of the steps of the method embodiments.

Furthermore, each functional unit in the embodiments of the present disclosure may be integrated in one processing module, or each unit may exist alone physically, or two or more units may be integrated in one module. The integrated modules may be implemented in hardware or in software functional modules. The integrated modules may also be stored in a computer readable storage medium if implemented in the form of software functional modules and sold or used as a stand-alone product.

The above-mentioned storage medium may be a read-only memory, a magnetic disk or an optical disk, or the like. Although embodiments of the present disclosure have been shown and described above, it will be understood that the above embodiments are illustrative and not to be construed as limiting the present disclosure, and that variations, modifications, alternatives, and variations may be made to the above embodiments by one of ordinary skill in the art within the scope of the present disclosure.

Claims (10)

1. A data writing method based on primary key collision detection, comprising:

responding to a writing request for data to be written, and before the data to be written is written into a destination table, acquiring a first main key of the data to be written and second main keys of all records in the destination table;

determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key;

in the case that the target data exist, carrying out conflict processing on the target data according to a target conflict processing strategy;

and writing the data to be written into the destination table in the condition that the target data does not exist.

2. The method according to claim 1, wherein the amount of data to be written is at least one;

The determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key includes:

generating a main key list according to each second main key;

writing the first primary key of each data to be written into a first message queue;

calling a plurality of first threads to acquire a first main key from the first message queue, inquiring the main key list according to the acquired first main key to determine whether a second main key matched with the acquired first main key exists in the main key list, if so, determining that target data exists, and taking the data to be written corresponding to the acquired first main key as the target data.

3. The method of claim 2, wherein before the invoking the plurality of first threads retrieves the first primary key from the first message queue, the method further comprises:

acquiring the resource occupancy rate of the equipment;

determining the number of first threads to be created according to the resource occupancy rate;

the number of first threads is created.

4. The method according to claim 1, wherein the amount of data to be written is at least one;

The determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key includes:

performing hash operation on the first primary key of each piece of data to be written respectively to obtain a first hash value of each piece of data to be written;

performing hash operation on each second main key to obtain a second hash value corresponding to each second main key;

and determining whether the target data exists in each data to be written according to the first hash value and the second hash value of each data to be written.

5. The method of claim 4, wherein determining whether the target data exists in each of the data to be written according to the first hash value and each of the second hash values of each of the data to be written comprises:

writing the first hash value of each data to be written into a second message queue;

generating a hash list according to each second hash value;

and calling a plurality of second threads to acquire a first hash value from the second message queue, inquiring the hash list according to the acquired first hash value to determine whether a second hash value matched with the acquired first hash value exists in the hash list, judging whether a first main key corresponding to the acquired first hash value is matched with a second main key corresponding to the matched second hash value if the second hash value exists, and determining that target data exists and taking data to be written corresponding to the acquired first hash value as the target data if the first main key corresponding to the acquired first hash value is matched with the second main key corresponding to the matched second hash value.

6. The method of claim 1, wherein the number of first primary keys per piece of data to be written is plural, and the number of second primary keys per record is plural;

the determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key includes:

merging a plurality of first main keys of any data to be written to obtain a first merged main key of any data to be written;

merging the plurality of second primary keys of any record to obtain a second merged primary key of any record;

and determining whether the target data exists in the data to be written according to the first merging main key and the second merging main key of the data to be written.

7. The method according to any one of claims 1 to 6, wherein the number of target data is at least one,

and under the condition that the target data exists, carrying out conflict processing on the target data according to a target conflict processing strategy, wherein the conflict processing comprises the following steps:

determining a target conflict processing strategy corresponding to each target data from at least one configured conflict processing strategy according to attribute information of each target data under the condition that the target data exists;

Performing conflict processing on each target data by adopting a target conflict processing strategy corresponding to each target data;

wherein the attribute information includes: at least one of a traffic type, a data source, a timestamp including a creation time or a modification time.

8. A data writing apparatus based on primary key collision detection, comprising:

the device comprises an acquisition module, a storage module and a storage module, wherein the acquisition module is used for responding to a writing request for data to be written, and acquiring a first main key of the data to be written and a second main key of each record in a destination table before the data to be written is written in the destination table;

the determining module is used for determining whether target data with main key conflict exists in the data to be written according to the first main key and each second main key;

the first processing module is used for carrying out conflict processing on the target data according to a target conflict processing strategy under the condition that the target data exist;

and the writing module is used for writing the data to be written into the destination table under the condition that the target data does not exist.

9. An electronic device, comprising:

a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the data writing method based on primary key collision detection as claimed in any one of claims 1 to 7 when the program is executed.

10. A non-transitory computer-readable storage medium having stored thereon a computer program, wherein the program when executed by a processor implements the primary key collision detection-based data writing method according to any one of claims 1 to 7.