Data extraction method and device, equipment and storage medium

Abstract

The invention discloses a data extraction method and device, equipment and a storage medium, and relates to the technical field of data processing.The method comprises the steps that a cursor record table is inquired, and a current cursor field is determined according to an inquiry result; performing data extraction on the to-be-extracted database table based on the current vernier field to obtain a plurality of pieces of query data; if the quantity of the query data reaches a preset allowable extraction quantity, updating a current vernier field based on a sentinel vernier field in the query data; and continuing to perform data extraction on the to-be-extracted database table based on the current vernier field when updating is completed. When the quantity of the query data extracted from the to-be-extracted database table reaches the preset allowable extraction quantity, the current vernier field is updated based on the sentry vernier field so as to continue to perform data extraction on the to-be-extracted database table; the technical problem that in the prior art, when a vernier positioning mode is adopted for data extraction, the vernier drifting phenomenon is likely to occur, and data extraction and data loss are caused is solved.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a data extraction method, device, equipment and storage medium. Background Art

[0002] Data extraction refers to the process of extracting data from the database of the business system and loading the data into the target system to meet the needs of various subsequent data applications. Business-layer query extraction is a common solution for database extraction. It generally queries a batch of data through periodic scheduling, then records the current cursor, and uses the current cursor to locate the next query, and so on. Although business-layer query extraction has the advantages of simple implementation, universal technology, and convenient operation and maintenance, during the database extraction process, if the extraction positioning cursor is not unique in the data sequence, the positioning cursor "drift" phenomenon will occur, making the next extraction positioning inaccurate, resulting in extraction loss.

[0003] The above contents are only used to assist in understanding the technical solution of the present invention and do not constitute an admission that the above contents are prior art. Summary of the invention

[0004] The main purpose of the present application is to provide a data extraction method, device, equipment and storage medium, aiming to solve the technical problem that cursor drift is prone to occur when data is extracted by cursor positioning in the prior art, resulting in loss of extracted data.

[0005] To achieve the above purpose, the present application proposes a data extraction method, which comprises:

[0006] Query the cursor record table and determine the current cursor field based on the query results;

[0007] Extract data from the database table to be extracted based on the current cursor field to obtain a number of query data;

[0008] If the number of the query data reaches the preset allowed extraction number, the current cursor field is updated based on the sentinel cursor field in the query data;

[0009] When the update is completed, the process returns to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a plurality of query data.

[0010] In one embodiment, if the amount of the query data reaches a preset allowable extraction amount, the step of updating the current cursor field based on the sentinel cursor field in the query data includes:

[0011] If the number of the query data reaches the preset allowed extraction number, a sentinel cursor field is determined from the query data, the sentinel cursor field being the cursor field with the largest field value in the query data;

[0012] Comparing the sentinel cursor field with the cursor fields corresponding to the query data before the sentinel cursor field in sequence;

[0013] The current cursor field is updated based on the comparison result.

[0014] In one embodiment, the step of updating the current cursor field based on the comparison result includes:

[0015] If there is a first cursor field in the cursor field that is not equal to the sentinel cursor field, the current cursor field is updated to the first cursor field.

[0016] In one embodiment, after the step of querying the cursor record table and determining the current cursor field according to the query result, the following step is further included:

[0017] Determine the current index field based on the current cursor field and a preset cursor backoff amount;

[0018] Extract data from the database table to be extracted based on the current index field to obtain a number of query data;

[0019] Compare the target index field corresponding to the target query data in the query data with the current cursor field;

[0020] If the target index field is larger than the current cursor field, data extraction from the database table to be extracted continues based on the target index field.

[0021] In one embodiment, the step of continuing to extract data from the database table to be extracted based on the target index field includes:

[0022] Update the current cursor field to the target index field;

[0023] When the update is completed, the method returns to the step of determining the current index field based on the current cursor field and the preset cursor backoff amount to continue extracting data from the database table to be extracted.

[0024] In one embodiment, after the step of querying the cursor record table and determining the current cursor field according to the query result, the following step is further included:

[0025] Performing a first data extraction on the database table to be extracted based on the first starting time, the first time interval and the current cursor field to obtain a plurality of first query data;

[0026] updating the current cursor field to a first positioning cursor field corresponding to first target query data in the first query data, wherein the first target query data is the query data with the largest field value in the first query data;

[0027] When the update is completed, the process returns to the step of extracting the first data from the database table to be extracted based on the first starting time, the first time interval and the current cursor field to obtain a plurality of first query data, so as to continue extracting the first data from the database table to be extracted.

[0028] In one embodiment, when the update is completed, returning to the step of extracting the first data from the database table to be extracted based on the first starting time, the first time interval and the current cursor field to obtain a plurality of first query data, and continuing the step of extracting the first data from the database table to be extracted, further comprises:

[0029] Performing a second data extraction on the database table to be extracted based on the second starting time, the second time interval and the current cursor field to obtain a plurality of second query data;

[0030] updating the current cursor field to a second positioning cursor field corresponding to the second target query data in the second query data, where the second target query data is the query data with the largest field value in the second query data;

[0031] When the update is completed, return to the step of performing second data extraction on the database table to be extracted based on the second starting time, the second time interval and the current cursor field to obtain a plurality of second query data, so as to continue performing second data extraction on the database table to be extracted;

[0032] The second starting time is greater than the first starting time, and the second time interval is twice the first time interval.

[0033] In addition, to achieve the above purpose, the present application also proposes a data extraction device, the device comprising:

[0034] The record table query module is used to query the cursor record table and determine the current cursor field according to the query result;

[0035] A data extraction module, used to extract data from the database table to be extracted based on the current cursor field, and obtain a number of query data;

[0036] A cursor determination module, configured to update the current cursor field based on the sentinel cursor field in the query data if the number of the query data reaches a preset allowable extraction number;

[0037] The data extraction module is also used to return to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a plurality of query data when the update is completed.

[0038] In addition, to achieve the above-mentioned purpose, the present application also proposes a data extraction device, which includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the data extraction method described above.

[0039] In addition, to achieve the above-mentioned purpose, the present application also proposes a storage medium, which is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, the steps of the data extraction method described above are implemented.

[0040] The present application provides a data extraction method, which discloses querying a cursor record table and determining a current cursor field according to the query result; extracting data from a database table to be extracted based on the current cursor field to obtain a number of query data; if the number of query data reaches a preset allowed extraction number, the current cursor field is updated based on a sentinel cursor field in the query data; when the update is completed, the method returns to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a number of query data; compared with the prior art, in the process of database extraction, if the extraction positioning cursor is not unique in the data sequence, the positioning cursor "drift" phenomenon will occur, making the next extraction positioning inaccurate, resulting in extraction loss, because the present invention updates the current cursor field based on the sentinel cursor field in the query data when the number of query data extracted from the database table to be extracted reaches the preset allowed extraction number, so as to continue to extract data from the database table to be extracted, thereby solving the technical problem that the cursor drift phenomenon is easy to occur when data extraction is performed by using the cursor positioning method in the prior art, resulting in extraction loss. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0042] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0043] Figure 1A flowchart of the first embodiment of the data extraction method of this application is provided;

[0044] Figure 2 A flowchart of an existing database extraction scheme provided for the data extraction method of this application;

[0045] Figure 3 A diagram showing the problem of cursor drift leading to lost data in the data extraction method provided in this application;

[0046] Figure 4 A flowchart of the implementation of the sentinel cursor mechanism provided in the first embodiment of the data extraction method of this application;

[0047] Figure 5 A flow chart of the second embodiment of the data extraction method of this application;

[0048] Figure 6 A diagram showing the loss of data due to the long and short transaction problems provided by the data extraction method of this application;

[0049] Figure 7 A flowchart of the implementation of the cursor rollback mechanism provided in the second embodiment of the data extraction method of this application;

[0050] Figure 8 A flowchart of the third embodiment of the data extraction method of this application is provided;

[0051] Fig. 9 A flowchart for implementing the double extraction mechanism provided in Embodiment 3 of the data extraction method of this application;

[0052] Fig.10 This is a schematic diagram of the module structure of the data extraction device according to an embodiment of the present application;

[0053] Fig.11 Schematic diagram of the device structure of the hardware operating environment involved in the data extraction method in the embodiment of the present application.

[0054] The purpose, features and advantages of this application will be further described in conjunction with the embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0055] It should be understood that the specific embodiments described herein are only used to explain the technical solutions of the present application and are not used to limit the present application.

[0056] In order to better understand the technical solution of the present application, a detailed description will be given below in conjunction with the accompanying drawings and specific implementation methods.

[0057] The main solution of the embodiment of the present application is: query the cursor record table and determine the current cursor field according to the query result; extract data from the database table to be extracted based on the current cursor field to obtain a number of query data; if the number of query data reaches the preset allowed extraction number, update the current cursor field based on the sentinel cursor field in the query data; when the update is completed, return to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a number of query data.

[0058] In the prior art, during the database extraction process, if the extraction positioning cursor is not unique in the data sequence, the positioning cursor "drifts" will occur, causing inaccurate positioning for the next extraction, resulting in missing numbers.

[0059] The present application provides a solution, which can update the current cursor field based on the sentinel cursor field in the query data when the number of query data extracted from the database table to be extracted reaches the preset allowed extraction number, so as to continue to extract data from the database table to be extracted, thereby solving the technical problem of cursor drift that is prone to occur when extracting data by cursor positioning in the prior art, resulting in loss of extraction data.

[0060] It should be noted that the execution subject of this embodiment can be a computing service device with data processing, network communication and program running functions, such as a tablet computer, a personal computer, a mobile phone, etc., or an electronic device capable of realizing the above functions, a data extraction device, etc. The following takes the data extraction device as an example (hereinafter referred to as the device) to illustrate this embodiment and the following embodiments.

[0061] Based on this, the present application embodiment provides a data extraction method, referring to Figure 1 , Figure 1 This is a flow chart of the first embodiment of the data extraction method of the present application.

[0062] In this embodiment, the data extraction method includes steps S10 to S40:

[0063] Step S10: query the cursor record table and determine the current cursor field according to the query result.

[0064] In practical applications, refer to Figure 2 , Figure 2 The flowchart of the existing database extraction scheme provided for the data extraction method of this application is as follows. Figure 2As shown, currently existing business layer query extraction usually queries a batch of data through periodic scheduling, then records the current cursor, and uses the current cursor to locate the next query, where the positioning cursor generally uses a timestamp field, and is updated to the current system time when the data is written or changed. Specifically, first, the positioning cursor of the last extracted record can be read, and the data in the extraction table can be sorted according to the positioning cursor. After the sorting is completed, a batch of data is extracted using the last positioning cursor as the starting point through the extraction program, and the data extracted this time is pushed. At the same time, the positioning cursor is updated to the last data extracted this time, and then the above steps are executed in a periodic loop. However, using this method for data extraction is prone to cursor drift problems, which can lead to data loss. Refer to Figure 3 , Figure 3 This is a diagram showing the problem of cursor drift leading to data loss in the data extraction method of this application. Figure 3 As shown, Figure 3 The left side shows the data extraction process under normal circumstances, and the right side shows the data extraction process when the cursor drifts. Under normal circumstances, the extraction starting cursor of the nth extraction is Tx (i.e., the positioning cursor after the n-1th extraction). If the extraction condition is greater than Tx and a maximum of 5 items are extracted each time, the data extracted at this time are T1, T2, T3, T4, and T5. After the data extraction is completed, the positioning cursor can be updated to T5 and the n+1th data extraction can be continued. The extraction starting cursor of the n+1th data extraction is T5, and the extraction condition is greater than T5 and a maximum of 5 items are extracted each time. The data extracted at this time are T6, T7, and T8, and the positioning cursor is updated to T8 after the data extraction is completed. In an abnormal situation (cursor drift), suppose there are three records with cursor values ​​of T5 in the data to be extracted, and the last record of the nth extraction happens to be the first T5. When the n+1th extraction is performed, because the positioning condition is that the cursor value is greater than T5, the second and third T5s are directly skipped, resulting in loss of extraction data. Therefore, this scheme proposes a data extraction method that can use the sentinel cursor mechanism to solve the problem of loss of data caused by cursor drift.

[0065] It should be understood that the above-mentioned cursor record table may be a table for storing cursors corresponding to the data to be extracted from the database table to be extracted.

[0066] It should be noted that the current cursor field may be the field where the cursor of the record after the last extraction is located.

[0067] In this embodiment, it is assumed that the database table to be extracted is SOURCE_T, which contains business fields A, B, C, ..., and a cursor field OFFSET (generally of datetime type, T updates the current time when any data is written or updated), and the index is the OFFSET field; the cursor record table is OFFSET_T, which contains the table name field NAME to be extracted, the current cursor field CURRENT_OFFSET, and the primary key is the NAME field. When extracting data from the database table to be extracted, the device can first query the OFFSET_T table, and the query condition is NAME field = SOURCE_T. At this time, the query obtains 1 record, and its CURRENT_OFFSET field is recorded as x. At this time, the current cursor field CURRENT_OFFSET can be determined according to the query result.

[0068] Step S20: extracting data from the database table to be extracted based on the current cursor field to obtain a number of query data.

[0069] It should be noted that after determining the current cursor field, the current cursor field can be used as the query condition of the database table to be extracted to extract data from the database table to be extracted. In practical applications, the database table to be extracted SOURCE_T can be sorted in order by the OFFSET field, and the query condition is set to OFFSET> current cursor field x to query (extract data from) the database table to be extracted SOURCE_T, and at the same time, the extraction is limited to a maximum of n data, and at this time, a maximum of n+1 data are returned, and finally the query result corresponding to the database table to be extracted SOURCE_T is obtained. At this time, the query result can be m query data [T1, T2, T3, ..., Tm] (0≤m≤n+1).

[0070] Step S30: If the number of the query data reaches a preset allowable extraction number, the current cursor field is updated based on the sentinel cursor field in the query data.

[0071] It is understandable that the preset allowed extraction number can be the maximum number of data that can be extracted from the database table to be extracted each time. For example, if this embodiment limits the maximum return of n+1 data, the preset allowed extraction number is n+1.

[0072] It should be noted that the sentinel cursor field can be a field used to find a real cursor from the query data. In this embodiment, when extracting data from the database table to be extracted, one more piece of data can be read as a sentinel cursor, and the sentinel cursor can look forward for a piece of data that is not equal to itself as a real cursor, thereby solving the problem of data loss caused by cursor drift.

[0073] In actual applications, if the number of query data m < the preset allowed extraction number n+1, then all query data are returned, and the OFFSET field of the last record is recorded as y, so that y can be used as the current cursor field to continue to extract data from the database table to be extracted when the number is extracted next time. In addition, if the number of query data m = the preset allowed extraction number n+1, the sentinel cursor field can be determined from the query data, and the current cursor field can be updated based on the sentinel cursor field.

[0074] Specifically, the step S30 includes:

[0075] Step S301: If the number of the query data reaches a preset allowed extraction number, a sentinel cursor field is determined from the query data, where the sentinel cursor field is a cursor field with the largest field value in the query data.

[0076] It should be understood that in this embodiment, the sentinel cursor field can be the cursor field with the largest field value among all query data. For example, if m query data [T1, T2, T3, ..., Tm] (0≤m≤n+1) are obtained after data extraction from the database table to be queried, then the sentinel cursor field at this time is the OFFSET field of data Tm.

[0077] Step S302: sequentially comparing the sentinel cursor field with the cursor fields corresponding to the query data before the sentinel cursor field.

[0078] It should be noted that the query data before the sentinel cursor field, that is, all field values ​​before the query data corresponding to the sentinel cursor field, are smaller than the data of the sentinel cursor field. In practical applications, for the sentinel cursor field, the query data before the corresponding query data Tm includes T1, T2, T3, ..., Tm-1. At this time, the sentinel cursor field can be compared with the cursor fields corresponding to Tm-1, ..., T2, T1 (that is, the index field OFFSET of the record to be extracted in the database table) in turn.

[0079] Step S303: updating the current cursor field based on the comparison result.

[0080] Specifically, the step S303 includes: if there is a first cursor field in the cursor field that is not equal to the sentinel cursor field, updating the current cursor field to the first cursor field.

[0081] It is understandable that the first cursor field may be a cursor field in the query data that is not equal to the sentinel cursor field. In this embodiment, there is only one first cursor field, that is, when the sentinel cursor field is compared with the cursor field corresponding to the query data before the query data Tm, the first cursor field that is not equal to the sentinel cursor field may be determined as the first cursor field.

[0082] In actual application, if the number of query data m = the preset allowed extraction number n+1, then the OFFSET field corresponding to Tm can be taken as the sentinel cursor field, and the OFFSET field of Tm (i.e., the sentinel cursor field) and the OFFSET field of Tm-1 are compared. If the OFFSET field of data Tm = the OFFSET field of Tm-1, then the OFFSET field of data Tm and the OFFSET field of Tm-2 are compared, and so on, until Ty is found to satisfy the OFFSET field of data Tm ≠ Ty, and the query data [T1, T2, T3, ..., Ty] is returned, and the OFFSET field of Ty is recorded as y (i.e., the first cursor field mentioned above). In addition, if Ty is not found to satisfy the OFFSET field of data Tm ≠ Ty (i.e., the OFFSET fields of [T1, T2, T3, ..., Tm] are all equal), the extraction is suspended and a reminder for manual intervention is given.

[0083] Step S40: when the update is completed, return to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a plurality of query data.

[0084] It should be understood that after finding the first cursor field that is not equal to the sentinel cursor field, the cursor record table OFFSET_T can be updated, the screening condition is NAME field = SOURCE_T, and the current cursor field is updated to the first cursor field. When the update is completed, return to step S20 to perform periodic repeated data extraction on the database table to be extracted.

[0085] In the specific implementation, the sentinel cursor mechanism proposed in this solution can read one more line as a sentinel cursor when extracting data. The sentinel cursor can find the first data that is not equal to itself as the real cursor, thus solving the problem of data loss caused by cursor drift. Figure 4 , Figure 4 This is a flowchart of the implementation of the sentinel cursor mechanism provided in the first embodiment of the data extraction method of this application. Figure 4As shown, when the cursor drifts, suppose there are three records with cursor values ​​of T5 in the data to be extracted, and the last record of the nth extraction happens to be the first T5. When the n+1th extraction is performed, because the positioning condition is that the cursor value is greater than T5, the second and third T5s are directly skipped, resulting in loss of extraction. In this embodiment, when the number m of data extracted for the nth time is equal to the preset allowed extraction number n+1, the OFFSET field of data Tm can be selected as the sentinel cursor. At this time, the sentinel cursor can find the first data that is not equal to itself as the real cursor, and update the current cursor field based on the real cursor, and then continue to extract the n+1th data from the database table to be extracted based on the updated current cursor field. For example, Figure 5 The six query data obtained during the nth extraction are T1, T2, T3, T4, T5, and T5. At this time, the last query data T5 can be used as a sentinel cursor, and then the sentinel cursor can find the first query data T4 that is not equal to itself as the real cursor, and update the current cursor field to the OFFSET field of the query data T4 record, so that when the n+1th data is extracted, the OFFSET field of T4 can be used as the current cursor field to continue data extraction for the extracted database table.

[0086] The present embodiment provides a data extraction method, which queries a cursor record table and determines a current cursor field according to the query result; extracts data from a database table to be extracted based on the current cursor field to obtain a number of query data; if the number of query data reaches a preset allowed extraction number, the current cursor field is updated based on a sentinel cursor field in the query data; when the update is completed, the method returns to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a number of query data; compared with the prior art, in the process of database extraction, if the extraction positioning cursor is not unique in the data sequence, the positioning cursor "drift" phenomenon will occur, making the next extraction positioning inaccurate, resulting in extraction loss, because the present invention updates the current cursor field based on the sentinel cursor field in the query data when the number of query data extracted from the database table to be extracted reaches the preset allowed extraction number, so as to continue to extract data from the database table to be extracted, thereby solving the technical problem that the cursor drift phenomenon is easy to occur when data extraction is performed by cursor positioning in the prior art, resulting in extraction loss.

[0087] Based on the first embodiment of the present application, in the second embodiment of the present application, the same or similar contents as those in the above-mentioned embodiment 1 can be referred to the above introduction, and will not be repeated in the following. Figure 5 , Figure 5 A flow chart of the second embodiment of the data extraction method of this application is provided.

[0088] In this embodiment, after step S10, the data extraction method further includes steps S21 to S51:

[0089] Step S21: Determine the current index field based on the current cursor field and a preset cursor backoff amount.

[0090] In actual applications, database transactions are isolated, that is, operations in a transaction can only be seen by other transactions after the transaction is committed. Figure 6 , Figure 6 This is a diagram showing the loss of data due to the long and short transaction problem provided by the data extraction method of this application. Figure 6 The operation of data 2 can only be seen by other transactions after T8 time. In addition, the current system time of the database is generally taken as the start time of this transaction, for example Figure 6 The current system time obtained by data 2 is T4. At this time, when a long transaction contains another short transaction, and the extraction transaction time is between the long and short transaction commit times, a mismatch between the long and short transactions occurs, resulting in loss of extraction data. Figure 6 In the embodiment of the present invention, "data 2" is taken as an example, data 2 affairs start at T4, data 4 affairs start at T5, and data 4 affairs end and submit at T6 (cursor is T5). If extracting from T7, then extracting less than data 2 (because data 2 affairs are not submitted, invisible in the extraction affairs, can not be extracted), now extracting to data 4, and record positioning cursor is T5, data 2 affairs end and submit at T8 (cursor is T4). If extracting from T11, still extracting less than data 2 (because the current system time of data 2 records is T4, less than extraction starting point cursor T5, can not be extracted), and extracting twice at T7 and T11 can not extract data 2, thereby cause extraction and loss of numbers. Therefore, the present embodiment proposes a kind of data extraction method, and the method can adopt cursor rollback mechanism or double extraction mechanism to solve the problem of losing numbers caused by the problem of long and short affairs.

[0091] It should be noted that the preset cursor rollback amount can be the value to which the current cursor field is rolled back each time data is extracted. Correspondingly, the current index field can be the positioning cursor when data is extracted for the data to be extracted this time. In this embodiment, the preset cursor rollback amount can be subtracted from the cursor recorded last time each time the number is drawn and positioned, so that the starting point of the number is rolled back for a short period of time, thereby solving the problem of lost data caused by the problem of long and short transactions.

[0092] Step S31: extracting data from the database table to be extracted based on the current index field to obtain a number of query data.

[0093] In practical applications, first, the cursor record table OFFSET_T can be queried, and the query condition is NAME field = SOURCE_T. At this time, the query obtains 1 record, and its current cursor field CURRENT_OFFSET is recorded as x. Assuming that the preset cursor backoff amount is Δt (it is recommended that Δt < ΔT / 10, ΔT is the time interval between two extractions), the current index field can be determined based on the current cursor field x and the preset cursor backoff amount Δt, where the current index field = x-Δt. Then, data can be extracted from the database table to be extracted based on the current index field, and the data extraction condition is OFFSET>x-Δt, and the sorting condition is to sort in the order of the OFFSET field, and finally m query data [T1, T2, T3, ..., Tm] (0≤m≤n) are obtained.

[0094] Step S41: Compare the target index field corresponding to the target query data in the query data with the current cursor field, the target query data being the query data with the largest field value in the query data.

[0095] It can be understood that the target query data can be the query data with the largest field value in the query data. For example, for m query data [T1, T2, T3, ..., Tm] (0≤m≤n), the target query data is Tm. Correspondingly, the target index field is the field corresponding to the target query data, such as the target index field corresponding to the target query data Tm is the OFFSET field of the data Tm.

[0096] Step S51: If the target index field is larger than the current cursor field, continue to extract data from the database table to be extracted based on the target index field.

[0097] In this embodiment, if the target index field corresponding to the target query data Tm is larger than the current cursor field, that is, if the OFFSET field of data Tm is ≤x, no operation is performed; if the OFFSET field of data Tm is >x, the record [T1, T2, T3, ..., Tm] is returned, and the OFFSET field of data Tm is taken as y (that is, the above-mentioned target index field) to continue data extraction from the database table to be extracted based on the target index field.

[0098] Specifically, the step of continuing to extract data from the database table to be extracted based on the target index field includes: updating the current cursor field to the target index field; when the update is completed, returning to the step of determining the current index field based on the current cursor field and the preset cursor backoff amount to continue extracting data from the database table to be extracted.

[0099] It should be understood that when the target index field corresponding to the query data is larger than the current cursor field, the cursor record table OFFSET_T can be updated, the filtering condition is NAME field = SOURCE_T, and the current cursor field CURRENT_OFFSET is updated to the target index field y, so that data can be extracted from the database table to be extracted based on the updated current cursor field.

[0100] In the specific implementation, refer to Figure 7 , Figure 7 This is a flow chart of the implementation of the cursor rollback mechanism provided in the second embodiment of the data extraction method of this application. Figure 7 As shown, the cursor rollback mechanism in this embodiment is specifically that each time the number is drawn and positioned, Δt is subtracted from the last record cursor, so that the starting point of the number is rolled back for a short period of time, thereby solving the problem of long and short transaction loss. For example, if the positioning cursor after the first number is T1-Δt, the second number is drawn with T1-Δt as the extraction starting point cursor, and the number extraction condition is OFFSET>T1-Δt. At this time, data 1 and data 4 can be extracted, and the current cursor is updated to T5. When the third number is drawn, the extraction starting point cursor can be rolled back by Δt, that is, T5-Δt is used as the extraction starting point cursor for the third extraction. The extraction condition at this time is OFFSET>T5-Δt, so that data 2, data 3 and data 4 are extracted. After the third number is completed, the current cursor is updated to T9, and then the database table to be extracted can continue to be periodically drawn.

[0101] In this embodiment, the current index field is determined based on the current cursor field and the preset cursor rollback amount; data is extracted from the database table to be extracted based on the current index field to obtain a number of query data; the target index field corresponding to the target query data in the query data is compared with the current cursor field, and the target query data is the query data with the largest field value in the query data; if the target index field is larger than the current cursor field, data extraction continues to be performed on the database table to be extracted based on the target index field, so that data can be extracted from the database table to be extracted based on the cursor rollback mechanism, thereby solving the problem of data loss caused by long and short transactions.

[0102] Based on the first embodiment and / or the second embodiment of the present application, in the third embodiment of the present application, the same or similar contents as those in the above embodiments can be referred to the above introduction, and will not be described in detail later. Figure 8 , Figure 8 A flow chart of the third embodiment of the data extraction method of this application is provided.

[0103] In this embodiment, after step S10, the data extraction method further includes steps S22 to S42:

[0104] Step S22: performing a first data extraction on the database table to be extracted based on the first starting time, the first time interval and the current cursor field to obtain a plurality of first query data.

[0105] It should be noted that the double extraction mechanism proposed in this embodiment can be to add an additional extraction task on the basis of the existing extraction task to achieve double data extraction, thereby solving the problem of data loss caused by long and short transactions.

[0106] It should be understood that the first data extraction is the existing extraction task. In this case, the first starting time may be the extraction starting time of the first data extraction task; and the first time interval may be the time interval between two extractions of the first data extraction task.

[0107] Step S32: updating the current cursor field to a first positioning cursor field corresponding to first target query data in the first query data, wherein the first target query data is query data with the largest field value in the first query data.

[0108] It is understandable that the first target query data may be the query data with the largest field value in the first query data, wherein the first query data is the data extracted by the first data extraction task. Correspondingly, the first positioning cursor field is the cursor field corresponding to the first target query data.

[0109] Step S42: When the update is completed, return to the step of performing the first data extraction on the database table to be extracted based on the first starting time, the first time interval and the current cursor field to obtain a plurality of first query data, so as to continue performing the first data extraction on the database table to be extracted.

[0110] In actual applications, for the first data extraction task, the first starting time T, the first time interval ΔT, the query condition OFFSET> current cursor field x, and the sorting condition can be used as query conditions to extract data from the database to be extracted, thereby obtaining m first query data [T1, T2, T3, ..., Tm], and then the records [T1, T2, T3, ..., Tm] can be returned, and the OFFSET field of the first target query data Tm is taken as y, and then the cursor record table OFFSET_T is updated, the screening condition is NAME field = SOURCE_T, and the current cursor field CURRENT_OFFSET is updated to y (that is, the first positioning cursor field mentioned above). When the update is completed, periodic data extraction can continue to be performed on the database table to be extracted based on the updated current cursor field.

[0111] Further, after step S42, it also includes: performing a second data extraction on the database table to be extracted based on the second starting time, the second time interval and the current cursor field to obtain a plurality of second query data; updating the current cursor field to a second positioning cursor field corresponding to the second target query data in the second query data, the second target query data being the query data with the largest field value in the second query data; when the update is completed, returning to the step of performing a second data extraction on the database table to be extracted based on the second starting time, the second time interval and the current cursor field to obtain a plurality of second query data, so as to continue performing a second data extraction on the database table to be extracted; wherein, the second starting time is greater than the first starting time, and the second time interval is twice the first time interval.

[0112] It is understandable that the second data extraction task can be a new extraction task added to the existing extraction task. Accordingly, the second starting time can be the extraction starting time of the second data extraction task; and the second time interval can be the time interval between two extractions of the second data extraction task.

[0113] In this embodiment, for the second data extraction task, the database to be extracted can be used as a condition for data extraction with the second starting time T+ΔT / 2, the second time interval 2ΔT, the query condition OFFSET>current cursor field x, and the sorting condition of sorting in the order of the OFFSET field, thereby obtaining m first query data [T1, T2, T3, ..., Tm], and then the record [T1, T2, T3, ..., Tm] can be returned, and the OFFSET field of the second target query data Tm is taken as y, and then the cursor record table OFFSET_T is updated, the screening condition is NAME field = SOURCE_T, and the current cursor field CURRENT_OFFSET is updated to y (that is, the above-mentioned second positioning cursor field). When the update is completed, the database table to be extracted can continue to be periodically extracted based on the updated current cursor field.

[0114] In the specific implementation, refer to Fig. 9 , Fig. 9 This is a flowchart of the implementation of the double extraction mechanism provided in the third embodiment of the data extraction method of this application. Fig. 9As shown, the double extraction mechanism in this embodiment is to add an additional extraction task on the basis of the existing extraction task, so as to realize double data extraction, thereby solving the problem of data loss caused by long and short transactions. For example, for the extraction task 1, if its first starting time is set to T7, the first time interval is 4, and T1 is used as the extraction starting point cursor to extract data, data 4 can be extracted at this time, and then the current cursor can be updated to T5, so that T5 can be used as the starting cursor to continue to extract periodic data from the database table to be extracted next time. In addition, the device can also perform the extraction task 2 at the same time, wherein the second starting time corresponding to the extraction task 2 is T12, the second time interval is 8, and T1 is used as the extraction starting point cursor to extract data, and data 2, data 3 and data 4 can be extracted at this time, and then the current cursor can be updated to T9, so that T9 can be used as the starting cursor to continue to extract periodic data from the database table to be extracted next time.

[0115] In this embodiment, it is disclosed that after the first data extraction is performed on the database table to be extracted, the second data extraction is performed on the database table to be extracted based on the second starting time, the second time interval and the current cursor field to obtain a plurality of second query data; the current cursor field is updated to the second positioning cursor field corresponding to the second target query data in the second query data, and the second target query data is the query data with the largest field value in the second query data; when the update is completed, the step of performing the second data extraction on the database table to be extracted based on the second starting time, the second time interval and the current cursor field to obtain a plurality of second query data is returned to continue the second data extraction on the database table to be extracted, so that data extraction on the database table to be extracted based on the double extraction mechanism can be realized, thereby solving the problem of data loss caused by the long and short transaction problem.

[0116] It should be noted that the above examples are only used to understand the present application and do not constitute a limitation on the data extraction method of the present application. More simple transformations based on this technical concept are all within the scope of protection of the present application.

[0117] This application also provides a data extraction device, please refer to Fig.10 , the data extraction device comprises:

[0118] The record table query module 10 is used to query the cursor record table and determine the current cursor field according to the query result;

[0119] A data extraction module 20 is used to extract data from the database table to be extracted based on the current cursor field to obtain a number of query data;

[0120] A cursor determination module 30, configured to update the current cursor field based on the sentinel cursor field in the query data if the number of the query data reaches a preset allowable extraction number;

[0121] The data extraction module 20 is further configured to return to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a plurality of query data when the update is completed.

[0122] The data extraction device provided by the present application adopts the data extraction method in the above embodiment, which can solve the technical problem that the cursor drift phenomenon is easy to occur when the data is extracted by the cursor positioning method in the prior art, resulting in the loss of the extracted data. Compared with the prior art, the beneficial effects of the data extraction device provided by the present application are the same as the beneficial effects of the data extraction method provided by the above embodiment, and the other technical features in the data extraction device are the same as the features disclosed in the above embodiment method, which will not be repeated here.

[0123] The present application provides a data extraction device, which includes: at least one processor; and a memory that is communicatively connected to the at least one processor; wherein the memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the data extraction method in the above-mentioned embodiment one.

[0124] Reference below Fig.11 , which shows a schematic diagram of the structure of a data extraction device suitable for implementing the embodiment of the present application. The data extraction device in the embodiment of the present application may include but is not limited to mobile terminals such as mobile phones, laptop computers, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Descriptions), PMPs (Portable Media Players), vehicle-mounted terminals (such as vehicle-mounted navigation terminals), etc., and fixed terminals such as digital TVs, desktop computers, etc. Fig.11 The data extraction device shown is merely an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0125] like Fig.11As shown, the data extraction device may include a processing device 1001 (e.g., a central processing unit, a graphics processor, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM: Read Only Memory) 1002 or a program loaded from a storage device 1003 to a random access memory (RAM: Random Access Memory) 1004. In RAM1004, various programs and data required for the operation of the data extraction device are also stored. The processing device 1001, ROM1002, and RAM1004 are connected to each other through a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Generally, the following systems can be connected to the I / O interface 1006: an input device 1007 including, for example, a touch screen, a touch pad, a keyboard, a mouse, an image sensor, a microphone, an accelerometer, a gyroscope, etc.; an output device 1008 including, for example, a liquid crystal display (LCD: Liquid Crystal Display), a speaker, a vibrator, etc.; a storage device 1003 including, for example, a magnetic tape, a hard disk, etc.; and a communication device 1009. The communication device 1009 can allow the data extraction device to communicate wirelessly or wired with other devices to exchange data. Although the data extraction device with various systems is shown in the figure, it should be understood that it is not required to implement or have all the systems shown. More or fewer systems can be implemented or have alternatively.

[0126] In particular, according to the embodiments disclosed in the present application, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present application include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through a communication device, or installed from a storage device 1003, or installed from a ROM 1002. When the computer program is executed by the processing device 1001, the above-mentioned functions defined in the method of the embodiment disclosed in the present application are executed.

[0127] The data extraction device provided by the present application adopts the data extraction method in the above embodiment to solve the technical problem of data extraction. Compared with the prior art, the beneficial effects of the data extraction device provided by the present application are the same as the beneficial effects of the data extraction method provided by the above embodiment, and the other technical features in the data extraction device are the same as the features disclosed in the method of the previous embodiment, which will not be repeated here.

[0128] It should be understood that the various parts disclosed in this application can be implemented by hardware, software, firmware or a combination thereof. In the description of the above embodiments, specific features, structures, materials or characteristics can be combined in any one or more embodiments or examples in a suitable manner.

[0129] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art who is familiar with the present technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.

[0130] The present application provides a computer-readable storage medium having computer-readable program instructions (ie, computer programs) stored thereon, wherein the computer-readable program instructions are used to execute the data extraction method in the above-mentioned embodiment.

[0131] The computer-readable storage medium provided in the present application may be, for example, a USB flash drive, but is not limited to electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, systems or devices, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, system or device. The program code contained on the computer-readable storage medium may be transmitted using any appropriate medium, including but not limited to: wires, optical cables, RF (Radio Frequency), etc., or any suitable combination of the above.

[0132] The computer-readable storage medium may be included in the data extraction device; or may exist independently without being assembled into the data extraction device.

[0133] The above-mentioned computer-readable storage medium carries one or more programs. When the above-mentioned one or more programs are executed by the data extraction device, the data extraction device: queries the cursor record table and determines the current cursor field according to the query result; extracts data from the database table to be extracted based on the current cursor field to obtain a number of query data; if the number of the query data reaches the preset allowed extraction number, updates the current cursor field based on the sentinel cursor field in the query data; when the update is completed, returns to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a number of query data.

[0134] Computer program code for performing the operations of the present application may be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0135] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present application. In this regard, each square box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the square box can also occur in a sequence different from that marked in the accompanying drawings. For example, two square boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each square box in the block diagram and / or flow chart, and the combination of the square boxes in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0136] The modules involved in the embodiments described in this application may be implemented by software or hardware, wherein the name of the module does not constitute a limitation on the unit itself in some cases.

[0137] The readable storage medium provided by the present application is a computer-readable storage medium, which stores computer-readable program instructions (i.e., computer programs) for executing the above-mentioned data extraction method, and can solve the technical problem that the cursor drift phenomenon is prone to occur when data is extracted by using the cursor positioning method in the prior art, resulting in the loss of the number of extracted data. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided by the present application are the same as the beneficial effects of the data extraction method provided by the above-mentioned embodiment, and will not be repeated here.

[0138] The above descriptions are only some embodiments of the present application, and are not intended to limit the patent scope of the present application. All equivalent structural changes made using the contents of the present application specification and drawings under the technical concept of the present application, or direct / indirect applications in other related technical fields are included in the patent protection scope of the present application.

Claims

1. A data extraction method, characterized in that: The method includes: Query the cursor record table and determine the current cursor field based on the query results; Extract data from the database table to be extracted based on the current cursor field to obtain a number of query data; If the number of the query data reaches the preset allowed extraction number, the current cursor field is updated based on the sentinel cursor field in the query data; When the update is completed, the process returns to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a plurality of query data.

2. The method according to claim 1, characterized in that If the amount of the query data reaches a preset allowable extraction amount, the step of updating the current cursor field based on the sentinel cursor field in the query data comprises: If the number of the query data reaches the preset allowed extraction number, a sentinel cursor field is determined from the query data, the sentinel cursor field being the cursor field with the largest field value in the query data; Comparing the sentinel cursor field with the cursor fields corresponding to the query data before the sentinel cursor field in sequence; The current cursor field is updated based on the comparison result.

3. The method according to claim 2, characterized in that The step of updating the current cursor field based on the comparison result includes: If there is a first cursor field in the cursor field that is not equal to the sentinel cursor field, the current cursor field is updated to the first cursor field.

4. The method according to any one of claims 1 to 3, characterized in that After the step of querying the cursor record table and determining the current cursor field according to the query result, the following steps are also included: Determine the current index field based on the current cursor field and a preset cursor backoff amount; Extract data from the database table to be extracted based on the current index field to obtain a number of query data; Compare the target index field corresponding to the target query data in the query data with the current cursor field; If the target index field is larger than the current cursor field, data extraction from the database table to be extracted continues based on the target index field.

5. The method according to claim 4, characterized in that The step of continuing to extract data from the database table to be extracted based on the target index field includes: Update the current cursor field to the target index field; When the update is completed, the method returns to the step of determining the current index field based on the current cursor field and the preset cursor backoff amount to continue extracting data from the database table to be extracted.

6. The method according to any one of claims 1 to 3, characterized in that After the step of querying the cursor record table and determining the current cursor field according to the query result, the following steps are also included: Performing a first data extraction on the database table to be extracted based on the first starting time, the first time interval and the current cursor field to obtain a plurality of first query data; updating the current cursor field to a first positioning cursor field corresponding to first target query data in the first query data, wherein the first target query data is the query data with the largest field value in the first query data; When the update is completed, the process returns to the step of extracting the first data from the database table to be extracted based on the first starting time, the first time interval and the current cursor field to obtain a plurality of first query data, so as to continue extracting the first data from the database table to be extracted.

7. The method according to claim 6, characterized in that When the update is completed, the method returns to the step of extracting the first data from the database table to be extracted based on the first starting time, the first time interval and the current cursor field to obtain a plurality of first query data, and then continues to extract the first data from the database table to be extracted, and further includes: Performing a second data extraction on the database table to be extracted based on the second starting time, the second time interval and the current cursor field to obtain a plurality of second query data; updating the current cursor field to a second positioning cursor field corresponding to the second target query data in the second query data, where the second target query data is the query data with the largest field value in the second query data; When the update is completed, return to the step of performing second data extraction on the database table to be extracted based on the second starting time, the second time interval and the current cursor field to obtain a plurality of second query data, so as to continue performing second data extraction on the database table to be extracted; The second starting time is greater than the first starting time, and the second time interval is twice the first time interval.

8. A data extraction device, characterized in that: The device comprises: The record table query module is used to query the cursor record table and determine the current cursor field according to the query result; A data extraction module, used to extract data from the database table to be extracted based on the current cursor field, and obtain a number of query data; A cursor determination module, configured to update the current cursor field based on the sentinel cursor field in the query data if the number of the query data reaches a preset allowable extraction number; The data extraction module is also used to return to the step of extracting data from the database table to be extracted based on the current cursor field to obtain a plurality of query data when the update is completed.

9. A data extraction device, characterized in that: The device comprises: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the computer program is configured to implement the steps of the data extraction method according to any one of claims 1 to 7.

10. A storage medium, characterized in that: The storage medium is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, the steps of the data extraction method according to any one of claims 1 to 7 are implemented.

Images