Data extraction method, device, apparatus and storage medium

Abstract

The application 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 following steps: querying a cursor record table, and determining a current cursor field according to a query result; performing data extraction on a database table to be extracted based on the current cursor field, and obtaining a plurality of pieces of query data; if the number of the query data reaches a preset allowed extraction number, updating the current cursor field based on a sentinel cursor field in the query data; and continuing to perform data extraction on the database table to be extracted based on the current cursor field after the updating is completed. When the number of the query data extracted from the database table to be extracted reaches the preset allowed extraction number, the current cursor field is updated based on the sentinel cursor field, so that the data extraction on the database table to be extracted is continued, and the technical problem that the cursor drift phenomenon is prone to occurring when the data extraction is performed by using the cursor positioning method in the prior art, and the data is lost during the extraction, is solved.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to data extraction methods, apparatus, devices and storage media. Background Technology

[0002] Data extraction refers to the process of extracting data from the database of a business system and loading it into the target system to meet various subsequent data applications. Business-layer query extraction is a common database extraction solution. It typically involves periodically querying a batch of data, recording the current cursor, and using that cursor to locate the next query, repeating this process. While business-layer query extraction has advantages such as simplicity, general technical applicability, and ease of maintenance, a "drift" phenomenon can occur if the cursor used for data extraction is not unique within the data sequence. This can lead to inaccurate cursor location for subsequent extractions and ultimately, lost data.

[0003] The above content is only used to help understand the technical solution of the present invention and does not represent an admission that the above content is prior art. Summary of the Invention

[0004] The main purpose of this application is to provide a data extraction method, apparatus, device and storage medium, which aims to 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 extracted data.

[0005] To achieve the above objectives, this application proposes a data extraction method, which includes:

[0006] The cursor record table is queried, and the current cursor field is determined based on the query results;

[0007] Based on the current cursor field, data is extracted from the database table to be extracted, and several query data are obtained.

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

[0009] Upon completion of the update, return to the step of extracting data from the database table to be extracted based on the current cursor field to obtain several query data entries.

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

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

[0012] The sentinel cursor field is compared sequentially with the cursor fields corresponding to the query data preceding the sentinel cursor field.

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

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

[0015] If there exists a first cursor field in the cursor field that is not equal to the sentinel cursor field, then 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 based on the query result, the method further includes:

[0017] The current index field is determined based on the current cursor field and the preset cursor backoff amount;

[0018] Based on the current index field, data is extracted from the database table to be extracted, resulting in several query data entries.

[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 greater than the current cursor field, then data extraction continues on the database table to be extracted 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] Upon completion of the update, return to the step of determining the current index field based on the current cursor field and the preset cursor backoff amount, so as 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 based on the query result, the method further includes:

[0025] Based on the first start time, the first time interval, and the current cursor field, the first data is extracted from the database table to be extracted, and several first query data are obtained.

[0026] Update the current cursor field to the first positioning cursor field corresponding to the first target query data in the first query data, where the first target query data is the query data with the largest field value in the first query data.

[0027] Upon completion of the update, the process returns to the step of extracting first data from the database table to be extracted based on the first start time, the first time interval, and the current cursor field to obtain several first query data entries, so as to continue extracting first data from the database table to be extracted.

[0028] In one embodiment, after the step of returning to the step of performing first data extraction on the database table to be extracted based on the first start time, the first time interval, and the current cursor field to obtain several first query data records upon completion of the update, to continue performing first data extraction on the database table to be extracted, the method further includes:

[0029] Based on the second start time, the second time interval, and the current cursor field, the second data extraction is performed on the database table to be extracted, and several second query data are obtained.

[0030] Update the current cursor field to the 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] Upon completion of the update, return to the step of performing a second data extraction on the database table to be extracted based on the second start time, the second time interval, and the current cursor field to obtain several second query data records, so as to continue performing a second data extraction on the database table to be extracted;

[0032] Wherein, the second start time is greater than the first start time, and the second time interval is twice the first time interval.

[0033] Furthermore, to achieve the above objectives, this application also proposes a data extraction apparatus, the apparatus comprising:

[0034] The record table query module is used to query the cursor record table and determine the current cursor field based on the query results;

[0035] The data extraction module is used to extract data from the database table to be extracted based on the current cursor field, and obtain several query data.

[0036] The cursor determination module is used to update the current cursor field based on the sentinel cursor field in the query data if the number of query data reaches the preset allowed 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 several query data when the update is completed.

[0038] In addition, to achieve the above objectives, this application also proposes a data extraction device, the device comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the data extraction method as described above.

[0039] In addition, to achieve the above objectives, this application also proposes a storage medium, which is a computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, it implements the steps of the data extraction method described above.

[0040] This application provides a data extraction method. The method involves querying a cursor record table and determining the current cursor field based on the query results; extracting data from the database table to be extracted based on the current cursor field to obtain several query data records; if the number of query data records reaches a preset allowed extraction quantity, updating the current cursor field based on the sentinel cursor field in the query data; and upon completion of the update, returning to the step of extracting data from the database table to be extracted based on the current cursor field to obtain several query data records. Compared to existing technologies where cursor "drift" occurs during database data extraction if the cursor is not unique in the data sequence, leading to inaccurate positioning and data loss in subsequent extractions, this invention updates the current cursor field based on the sentinel cursor field in the query data when the number of query data records extracted from the database table reaches the preset allowed extraction quantity, thus continuing data extraction from the database table to be extracted. This solves the technical problem of cursor drift and data loss that easily occurs when using cursor positioning for data extraction in existing technologies. Attached Figure Description

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

[0042] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0043] Figure 1This is a flowchart illustrating an embodiment of the data extraction method of this application.

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

[0045] Figure 3 A diagram illustrating how cursor drift in the data extraction method of this application leads to data loss during extraction;

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

[0047] Figure 5 This is a flowchart illustrating Embodiment 2 of the data extraction method of this application;

[0048] Figure 6 A diagram illustrating the data extraction method of this application that suffers from data loss due to the long and short transaction issues.

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

[0050] Figure 8 This is a flowchart illustrating Embodiment 3 of the data extraction method of this application;

[0051] Figure 9 A flowchart illustrating the implementation of the dual extraction mechanism provided in Embodiment 3 of the data extraction method of this application;

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

[0053] Figure 11 This is a schematic diagram of the device structure of the hardware operating environment involved in the data extraction method in the embodiments of this application.

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

[0055] It should be understood that the specific embodiments described herein are merely illustrative of the technical solutions of this application and are not intended to limit this application.

[0056] To better understand the technical solution of this application, a detailed description will be provided below in conjunction with the accompanying drawings and specific implementation methods.

[0057] The main solution of this application embodiment is as follows: query the cursor record table and determine the current cursor field based on the query result; extract data from the database table to be extracted based on the current cursor field to obtain several query data records; if the number of query data records 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 several query data records.

[0058] Because existing technologies can cause cursor "drift" during database data extraction if the cursor is not unique in the data sequence, the next data extraction will be inaccurate and result in lost data.

[0059] This application provides a solution that, when the number of query data extracted from the database table to be extracted reaches the preset allowed extraction number, updates the current cursor field based on the sentinel cursor field in the query data to continue extracting data from the database table to be extracted. This solves the technical problem that cursor drift is prone to occur when using cursor positioning for data extraction in the prior art, resulting in data loss.

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

[0061] Based on this, embodiments of this application provide a data extraction method, referring to... Figure 1 , Figure 1 This is a flowchart illustrating the first embodiment of the data extraction method of this 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 based on the query results.

[0064] In practical applications, refer to Figure 2 , Figure 2 A flowchart illustrating an existing database data extraction scheme provided for the data extraction method of this application. For example... Figure 2As shown, current business-layer query extraction typically involves periodically querying a batch of data, recording the current cursor, and using this cursor to locate the next query. The cursor is usually positioned using a timestamp field, updated to the current system time during data writing and changes. Specifically, the cursor of the last extracted record is read first, and the data in the extraction table is sorted according to the cursor. After sorting, the extraction program extracts a batch of data starting from the last cursor, pushes the extracted data, and simultaneously updates the cursor to the last extracted record. This process is then repeated periodically. However, this method of data extraction is prone to cursor drift, leading to lost data. (Refer to...) Figure 3 , Figure 3 This diagram illustrates how cursor drift in the data extraction method used in this application leads to data loss. (Example:) 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 is drifting. Under normal circumstances, the starting cursor for the nth extraction is Tx (i.e., the positioning cursor after the (n-1)th extraction). If the extraction condition is greater than Tx and a maximum of 5 data points are extracted each time, then the extracted data are T1, T2, T3, T4, and T5. After the data extraction is completed, the positioning cursor can be updated to T5, and the (n+1)th data extraction can continue. The starting cursor for the (n+1)th data extraction is T5, and the extraction condition is greater than T5 and a maximum of 5 data points are extracted each time. Then the extracted data are T6, T7, and T8, and the positioning cursor is updated to T8 after the data extraction is completed. In abnormal situations (cursor drift), suppose there are three records in the data to be extracted, all with a cursor value of T5. The last record in the nth extraction is exactly the first T5. In the (n+1)th extraction, because the positioning condition is that the cursor value is greater than T5, the second and third T5 records are skipped, resulting in missing data. Therefore, this solution proposes a data extraction method that uses a sentinel cursor mechanism to solve the problem of missing data caused by cursor drift.

[0065] It should be understood that the aforementioned cursor record table can be a table used to store the cursors corresponding to the data to be extracted from the database table.

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

[0067] In this embodiment, assume 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, where T updates the current time whenever any data is written or updated), with the OFFSET field as the index; the cursor record table is OFFSET_T, which contains the table name field NAME, the current cursor field CURRENT_OFFSET, and the NAME field as the primary key. When extracting data from the database table to be extracted, the device can first query the OFFSET_T table with the query condition that the NAME field = SOURCE_T. This query returns one record, denoted as x for its CURRENT_OFFSET field. The current cursor field CURRENT_OFFSET can then be determined based on the query result.

[0068] Step S20: Extract data from the database table to be extracted based on the current cursor field to obtain several 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 for the database table to be extracted. In practical applications, the database table to be extracted, SOURCE_T, can be sorted sequentially by the OFFSET field, and the query condition can be set to OFFSET > current cursor field x, to query (extract data) the database table to be extracted, SOURCE_T. At the same time, the extraction is limited to a maximum of n data records, and at most n+1 data records will be returned. Finally, the query result corresponding to the database table to be extracted, SOURCE_T, can be obtained. The query result can be m data records [T1, T2, T3, ..., Tm] (0 ≤ m ≤ n+1).

[0070] Step S30: If the amount of query data reaches the preset allowed extraction quantity, then update the current cursor field based on the sentinel cursor field in the query data.

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

[0072] It should be noted that the aforementioned sentinel cursor field can be used to find the actual cursor from the query data. In this embodiment, when extracting data from the database table to be extracted, an additional data record can be read as a sentinel cursor. The sentinel cursor can then look back for a record that is not equal to itself as the actual cursor, thereby solving the problem of data loss caused by cursor drift.

[0073] In practical applications, if the number of data queried, m, is less than the preset allowed extraction number, n+1, then all queried data is returned, and the OFFSET field of the last record is recorded as y. This allows y to be used as the current cursor field for data extraction from the database table to be extracted in the next extraction. Conversely, if the number of data queried, m, is equal to the preset allowed extraction number, n+1, then the sentinel cursor field can be determined from the queried data, and the current cursor field can be updated based on the sentinel cursor field.

[0074] Specifically, step S30 includes:

[0075] Step S301: If the amount of query data reaches the preset allowed extraction quantity, then determine the sentinel cursor field from the query data. The sentinel cursor field is the 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 the query data. For example, if m query data [T1,T2,T3,...,Tm] (0≤m≤n+1) are obtained after extracting data from the database table to be queried, then the sentinel cursor field is the OFFSET field of the data Tm.

[0077] Step S302: Compare the sentinel cursor field with the cursor fields corresponding to the query data preceding the sentinel cursor field in sequence.

[0078] It should be noted that the query data preceding the sentinel cursor field refers to all data whose field values ​​are less than the sentinel cursor field. In practical applications, for the sentinel cursor field, the query data preceding its corresponding query data Tm includes T1, T2, T3, ..., Tm-1. In this case, the sentinel cursor field can be compared sequentially with the cursor fields corresponding to Tm-1, ..., T2, T1 (i.e., the index field OFFSET of the records to be extracted from the database table).

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

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

[0081] It is understood that the aforementioned first cursor field can be any 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 comparing the sentinel cursor field with the cursor fields corresponding to the query data before the query data Tm, the first cursor field that is not equal to the sentinel cursor field can be determined as the first cursor field.

[0082] In practical applications, if the number of data items m = the preset allowed extraction number n + 1, the OFFSET field corresponding to Tm can be taken as the sentinel cursor field. The OFFSET field of Tm (i.e., the sentinel cursor field) is compared with the OFFSET field of Tm-1. If the OFFSET field of data Tm equals the OFFSET field of Tm-1, the comparison continues with the OFFSET field of data Tm and the OFFSET field of Tm-2, and so on, until a Ty is found that satisfies the condition that the OFFSET field of data Tm ≠ the OFFSET field of Ty. The query data [T1, T2, T3, ..., Ty] is then returned, and the OFFSET field of Ty is recorded as y (i.e., the first cursor field mentioned above). Furthermore, if no Ty is found that satisfies the condition that the OFFSET field of data Tm ≠ the OFFSET field of Ty (i.e., the OFFSET fields of [T1, T2, T3, ..., Tm] are all equal), the data extraction is paused, and manual intervention is requested.

[0083] Step S40: Upon completion of the update, return to the step of extracting data from the database table to be extracted based on the current cursor field to obtain several query data records.

[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 with the filter condition 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 and repeated data extraction from the database table to be extracted.

[0085] In its implementation, the sentinel cursor mechanism proposed in this scheme can read an additional record as a sentinel cursor during data extraction. This sentinel cursor can then find the first record that is not equal to itself and use it as the actual cursor, thus resolving the data loss problem caused by cursor drift. (See reference...) Figure 4 , Figure 4 This is a flowchart illustrating the implementation of the sentinel cursor mechanism provided in Embodiment 1 of the data extraction method of this application. Figure 4As shown, during cursor drift, assuming there are three records with cursor values ​​of T5 in the data to be extracted, and the last record of the nth extraction is exactly the first T5, in the (n+1)th extraction, because the positioning condition is that the cursor value is greater than T5, the second and third T5 records are skipped, resulting in missed extractions. However, in this embodiment, when the number of data m extracted in the nth extraction is equal to the preset allowed extraction number n+1, the OFFSET field of data Tm can be selected as the sentinel cursor. The sentinel cursor can then find the first data record that is not equal to itself as the real cursor, and update the current cursor field based on this real cursor. Then, based on the updated current cursor field, the (n+1)th data extraction continues from the database table to be extracted. For example, Figure 5 In the nth data extraction, the 6 query data obtained are T1, T2, T3, T4, T5, T5. At this time, the last query data T5 can be used as a sentinel cursor. 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. Thus, in the (n+1)th data extraction, the OFFSET field of T4 can be used as the current cursor field to continue data extraction from the database table to be extracted.

[0086] This embodiment provides a data extraction method. The method queries a cursor record table and determines the current cursor field based on the query results. Data is extracted from the database table to be extracted based on the current cursor field to obtain several query data records. If the number of query data records reaches a preset allowed extraction quantity, the current cursor field is updated based on the sentinel cursor field in the query data. Upon completion of the update, the method returns to the step of extracting data from the database table to be extracted based on the current cursor field to obtain several query data records. Compared to existing technologies where cursor "drift" occurs during database data extraction if the cursor is not unique in the data sequence, leading to inaccurate positioning and data loss in subsequent extractions, this invention updates the current cursor field based on the sentinel cursor field in the query data when the number of query data records extracted from the database table reaches the preset allowed extraction quantity, thus continuing data extraction from the database table to be extracted. This solves the technical problem of cursor drift and data loss that easily occurs when using cursor positioning for data extraction in existing technologies.

[0087] Based on the first embodiment of this application, in the second embodiment of this application, the content that is the same as or similar to that in the first embodiment described above can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 5 , Figure 5 This is a flowchart illustrating the second embodiment of the data extraction method of this application.

[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 the preset cursor backoff amount.

[0090] In practical applications, database transactions exhibit isolation, meaning that operations within a transaction can only be seen by other transactions after that transaction has been committed. (See reference...) Figure 6 , Figure 6 This diagram illustrates how the long and short transaction issues in the data extraction method of this application lead to data loss during extraction. (Example:) Figure 6 The operations performed on data 2 will only be visible to other transactions after time T8; furthermore, the current system time of the database is generally the start time of this transaction, for example... Figure 6 Data 2 retrieves the current system time as T4. At this point, if a long transaction contains another short transaction, and the data extraction transaction's time falls between the commit times of the long and short transactions, a long-short transaction mismatch occurs, leading to lost data extraction. Figure 6 Taking "Data 2" as an example, the Data 2 transaction starts at time T4, the Data 4 transaction starts at time T5, and the Data 4 transaction ends and commits at time T6 (cursor is T5). If data extraction starts from time T7, Data 2 cannot be extracted (because the Data 2 transaction has not been committed, it is not visible in the data extraction transaction and will not be extracted). At this time, Data 4 is extracted, and the cursor is recorded as T5. The Data 2 transaction ends and commits at time T8 (cursor is T4). If data extraction starts from time T11, Data 2 still cannot be extracted (because the current system time recorded for Data 2 is T4, which is less than the cursor T5 at the starting point of the data extraction, and will not be extracted). Furthermore, Data 2 cannot be extracted in both the time extractions at times T7 and T11, resulting in data loss during data extraction. Therefore, this embodiment proposes a data extraction method that can use a cursor rollback mechanism or a double extraction mechanism to solve the data loss problem caused by the long and short transaction issue.

[0091] It should be noted that the aforementioned preset cursor backoff amount can be the value used to backoff the current cursor field each time data is extracted. Correspondingly, the aforementioned current index field can be the positioning cursor used when extracting data for the current time. In this embodiment, the preset cursor backoff amount can be subtracted from the previously recorded cursor value each time data is extracted, causing the extraction start point to backoff by a small amount of time, thereby resolving the data loss problem caused by the long / short transaction issue.

[0092] Step S31: Extract data from the database table to be extracted based on the current index field to obtain several query data.

[0093] In practical applications, we can first query the cursor record table OFFSET_T with the query condition NAME field = SOURCE_T. This will retrieve one record. Let the current cursor field CURRENT_OFFSET be x. Assume the preset cursor backoff is Δt (it is recommended that Δt < ΔT / 10, where ΔT is the time interval between two data extractions). Then, we can determine the current index field based on the current cursor field x and the preset cursor backoff Δt, where the current index field = x - Δt. Next, we can extract data from the database table to be extracted based on the current index field, with the data extraction condition OFFSET > x - Δt and the sorting condition being sorted according to the OFFSET field order. Finally, we obtain m records of query data [T1, T2, T3, ..., Tm] (0 ≤ m ≤ n).

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

[0095] It is understandable that the target query data mentioned above 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 mentioned above is the field corresponding to the target query data. For example, the target index field corresponding to the target query data Tm is the OFFSET field of data Tm.

[0096] Step S51: If the target index field is greater than the current cursor field, then 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 greater than the current cursor field, that is, if the OFFSET field of data Tm is less than or equal to x, no operation is performed; if the OFFSET field of data Tm is greater than x, then the record [T1, T2, T3, ..., Tm] is returned, and the OFFSET field of data Tm is taken as y (i.e., the target index field mentioned above), so as to continue to extract data 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; and, upon completion of the update, returning to the step of determining the current index field based on the current cursor field and a preset cursor backoff amount, so as 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 queried data is greater than the current cursor field, the cursor record table OFFSET_T can be updated with the filter condition NAME field = SOURCE_T, and the current cursor field CURRENT_OFFSET can be updated to the target index field y, so that subsequent data extraction from the database table to be extracted can continue based on the updated current cursor field.

[0100] In the specific implementation, refer to Figure 7 , Figure 7 This is a flowchart illustrating the implementation of the cursor rollback mechanism provided in Embodiment 2 of the data extraction method of this application. Figure 7 As shown, in this embodiment, the cursor rollback mechanism specifically involves subtracting Δt from the previously recorded cursor value during each data extraction and positioning, causing the extraction start point to roll back a short period of time, thereby resolving the issue of data loss in both long and short transactions. For example, if the positioning cursor after the first data extraction is T1-Δt, then the second data extraction will use T1-Δt as the extraction start cursor, with the extraction condition being OFFSET>T1-Δt. At this point, data 1 and data 4 can be extracted, and the current cursor will be updated to T5. During the third extraction, the extraction start cursor can be rolled back by Δt, i.e., T5-Δt will be used as the extraction start cursor for the third extraction, with the extraction condition being OFFSET>T5-Δt. This will extract data 2, data 3, and data 4. After the third data extraction is completed, the current cursor will be updated to T9, and periodic data extraction of the database table to be extracted can continue thereafter.

[0101] In this embodiment, the current index field is determined based on the current cursor field and a preset cursor backoff amount; data is extracted from the database table to be extracted based on the current index field to obtain several 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 greater than the current cursor field, data extraction from the database table to be extracted continues based on the target index field, so that data extraction from the database table to be extracted can be performed based on the cursor backoff mechanism, thereby solving the data loss problem caused by the long and short transaction problem.

[0102] Based on the first and / or second embodiments of this application, in the third embodiment of this application, the content that is the same as or similar to the above embodiments can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 8 , Figure 8 This is a flowchart illustrating the data extraction method of Embodiment 3 of this application.

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

[0104] Step S22: Based on the first start time, the first time interval, and the current cursor field, perform the first data extraction on the database table to be extracted to obtain several first query data.

[0105] It should be noted that the dual extraction mechanism proposed in this embodiment can add an extra 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 aforementioned first data extraction is the existing data extraction task. In this case, the aforementioned first start time can be the data extraction start time of the first data extraction task; the first time interval can be the time interval between two data extractions of the first data extraction task.

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

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

[0109] Step S42: Upon completion of the update, return to the step of extracting first data from the database table to be extracted based on the first start time, the first time interval, and the current cursor field to obtain several first query data records, so as to continue extracting first data from the database table to be extracted.

[0110] In practical applications, for the first data extraction task, the database to be extracted can be used as the query conditions: the first start time T, the first time interval ΔT, the query condition OFFSET > the current cursor field x, and the sorting condition is sorting by the OFFSET field order. This will yield m first query data [T1,T2,T3,...,Tm]. Then, the records [T1,T2,T3,...,Tm] can be returned, and the OFFSET field of the first target query data Tm can be taken as y. The cursor record table OFFSET_T can then be updated, with the filter condition being NAME field = SOURCE_T. The current cursor field CURRENT_OFFSET can be updated to y (i.e., the first positioning cursor field mentioned above). After the update is completed, periodic data extraction can continue based on the updated current cursor field of the database table to be extracted.

[0111] Further, after step S42, the method further includes: performing a second data extraction on the database table to be extracted based on the second start time, the second time interval, and the current cursor field to obtain several second query data records; updating the current cursor field to the second positioning cursor field corresponding to the second target query data in the second query data, wherein the second target query data is the query data with the largest field value in the second query data; upon completion of the update, returning to the step of performing a second data extraction on the database table to be extracted based on the second start time, the second time interval, and the current cursor field to obtain several second query data records, to continue performing a second data extraction on the database table to be extracted; wherein the second start time is greater than the first start time, and the second time interval is twice the first time interval.

[0112] It is understandable that the aforementioned second data extraction can be a new data extraction task added on top of an existing data extraction task. Accordingly, the aforementioned second start time can be the start time of the data extraction in the second data extraction task; the second time interval can be the time interval between two data extractions in the second data extraction task.

[0113] In this embodiment, for the second data extraction task, data can be extracted from the database to be extracted based on the second start time T+ΔT / 2, the second time interval 2ΔT, the query condition OFFSET>current cursor field x, and the sorting condition being sorted according to the OFFSET field order. This yields m first query data records [T1,T2,T3,...,Tm]. Then, the records [T1,T2,T3,...,Tm] can be returned, and the OFFSET field of the second target query data Tm is taken as y. The cursor record table OFFSET_T is then updated, with the filtering condition being NAME field = SOURCE_T. The current cursor field CURRENT_OFFSET is updated to y (i.e., the second positioning cursor field mentioned above). Upon completion of the update, periodic data extraction can continue based on the updated current cursor field of the database table to be extracted.

[0114] In the specific implementation, refer to Figure 9 , Figure 9 This is a flowchart illustrating the implementation of the dual-extraction mechanism provided in Embodiment 3 of the data extraction method of this application. Figure 9As shown, the dual extraction mechanism in this embodiment adds an extra extraction task on top of the existing extraction task, achieving double data extraction and thus solving the data loss problem caused by long and short transactions. For example, for extraction task one, if its first start time is set to T7, the first time interval is 4, and T1 is used as the starting cursor for data extraction, data 4 can be extracted. Then, the current cursor can be updated to T5, so that the next time, T5 can be used as the starting cursor to continue periodically extracting data from the database table to be extracted. In addition, the device can also perform extraction task two simultaneously, where the second start time corresponding to extraction task two is T12, the second time interval is 8, and T1 is used as the starting cursor for data extraction. At this time, data 2, data 3, and data 4 can be extracted. Then, the current cursor can be updated to T9, so that the next time, T9 can be used as the starting cursor to continue periodically extracting data from the database table to be extracted.

[0115] In this embodiment, after performing a first data extraction on the database table to be extracted, a second data extraction is performed on the database table to be extracted based on a second start time, a second time interval, and the current cursor field to obtain several second query data records. The current cursor field is updated to the 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 complete, the process returns to the step of performing a second data extraction on the database table to be extracted based on the second start time, the second time interval, and the current cursor field to obtain several second query data records, so as to continue performing a second data extraction on the database table to be extracted. This enables data extraction from the database table to be extracted based on a dual extraction mechanism, thereby solving the data loss problem caused by the long and short transaction problem.

[0116] It should be noted that the above examples are only for understanding this application and do not constitute a limitation on the data extraction method of this application. Any simple modifications based on this technical concept are within the protection scope of this application.

[0117] This application also provides a data extraction device, please refer to... Figure 10 The data extraction device includes:

[0118] The record table query module 10 is used to query the cursor record table and determine the current cursor field based on the query results;

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

[0120] The cursor determination module 30 is used to update the current cursor field based on the sentinel cursor field in the query data if the number of query data reaches the preset allowed extraction number;

[0121] The data extraction module 20 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 several query data when the update is completed.

[0122] The data extraction device provided in this application, employing the data extraction method described in the above embodiments, can solve the technical problem in the prior art where cursor drift easily occurs during data extraction using cursor positioning, leading to data loss. Compared with the prior art, the beneficial effects of the data extraction device provided in this application are the same as those of the data extraction method provided in the above embodiments, and other technical features in the data extraction device are the same as those disclosed in the methods of the above embodiments, and will not be repeated here.

[0123] This application provides a data extraction device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the data extraction method in Embodiment 1 above.

[0124] The following is for reference. Figure 11 The diagram illustrates a structural schematic of a data extraction device suitable for implementing embodiments of this application. The data extraction device in the embodiments of this application may include, but is not limited to, mobile terminals such as mobile phones, laptops, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Description), PMPs (Portable Media Players), in-vehicle terminals (e.g., in-vehicle navigation terminals), and fixed terminals such as digital TVs and desktop computers. Figure 11 The data extraction device shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0125] like Figure 11As shown, the data extraction device may include a processing unit 1001 (e.g., a central processing unit, a graphics processing unit, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1002 or a program loaded from a storage device 1003 into a random access memory (RAM) 1004. The RAM 1004 also stores various programs and data required for the operation of the data extraction device. The processing unit 1001, ROM 1002, and RAM 1004 are interconnected via a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Typically, the following systems can be connected to the I / O interface 1006: input devices 1007 including, for example, a touchscreen, touchpad, keyboard, mouse, image sensor, microphone, accelerometer, gyroscope, etc.; output devices 1008 including, for example, a liquid crystal display (LCD), speaker, vibrator, etc.; storage devices 1003 including, for example, magnetic tape, hard disk, etc.; and communication devices 1009. Communication device 1009 allows the data extraction device to communicate wirelessly or wiredly with other devices to exchange data. Although the figures show data extraction devices with various systems, it should be understood that implementation or possession of all the systems shown is not required. More or fewer systems may be implemented alternatively.

[0126] Specifically, according to the embodiments disclosed in this application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed in this application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device, or installed from storage device 1003, or installed from ROM 1002. When the computer program is executed by processing device 1001, it performs the functions defined in the methods of the embodiments disclosed in this application.

[0127] The data extraction device provided in this application, employing the data extraction method described in the above embodiments, can solve the technical problem of data extraction. Compared with the prior art, the beneficial effects of the data extraction device provided in this application are the same as those of the data extraction method described in the above embodiments, and other technical features of this data extraction device are the same as those disclosed in the method of the previous embodiment, and will not be repeated here.

[0128] It should be understood that the various parts disclosed in this application can be implemented using 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 suitable manner in one or more embodiments or examples.

[0129] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

[0130] This application provides a computer-readable storage medium having computer-readable program instructions (i.e., a computer program) stored thereon, the computer-readable program instructions being used to execute the data extraction method described in the above embodiments.

[0131] The computer-readable storage medium provided in this application may be, for example, a USB flash drive, but is not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. 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 conjunction with an instruction execution system, system, or device. The program code contained on the computer-readable storage medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, RF (Radio Frequency), etc., or any suitable combination thereof.

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

[0133] The aforementioned computer-readable storage medium carries one or more programs. When these programs are executed by the data extraction device, the data extraction device performs the following actions: queries a cursor record table and determines the current cursor field based on the query result; extracts data from the database table to be extracted based on the current cursor field to obtain several query data records; if the number of query data records reaches a preset allowed extraction number, updates the current cursor field based on the sentinel cursor field in the query data; and upon completion of the update, returns to the step of extracting data from the database table to be extracted based on the current cursor field to obtain several query data records.

[0134] Computer program code for performing the operations of this application can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, and conventional procedural programming languages ​​such as the "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a Local Area Network (LAN) or a Wide Area Network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0135] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0136] The modules described in the embodiments of this application can be implemented in software or hardware. The names of the modules do not necessarily limit the functionality of the unit itself.

[0137] The readable storage medium provided in this application is a computer-readable storage medium that stores computer-readable program instructions (i.e., a computer program) for executing the above-described data extraction method. This solves the technical problem in the prior art where cursor drift easily occurs during data extraction using cursor positioning, leading to data loss. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in this application are the same as those of the data extraction method provided in the above embodiments, and will not be repeated here.

[0138] The above description is only a part of the embodiments of this application and does not limit the patent scope of this application. All equivalent structural transformations made under the technical concept of this application and using the contents of the specification and drawings of this application, or direct / indirect applications in other related technical fields, are included in the patent protection scope of this application.

Claims

1. A data extraction method, characterized in that, The method includes: The cursor record table is queried, and the current cursor field is determined based on the query results; Based on the current cursor field, data is extracted from the database table to be extracted, and several query data are obtained. If the amount of queried data reaches the preset allowed extraction quantity, the current cursor field is updated based on the sentinel cursor field in the queried data; Upon completion of the update, return to the step of extracting data from the database table to be extracted based on the current cursor field to obtain several query data records; The step of updating the current cursor field based on the sentinel cursor field in the query data if the quantity of the queried data reaches the preset allowed extraction quantity includes: If the amount of query data reaches the preset allowed extraction quantity, then a sentinel cursor field is determined from the query data, and the sentinel cursor field is the cursor field with the largest field value in the query data; The sentinel cursor field is compared sequentially with the cursor fields corresponding to the query data preceding the sentinel cursor field. If there exists a first cursor field in the cursor field that is not equal to the sentinel cursor field, then the current cursor field is updated to the first cursor field.

2. The method as described in claim 1, characterized in that, After the steps of querying the cursor record table and determining the current cursor field based on the query results, the method further includes: The current index field is determined based on the current cursor field and the preset cursor backoff amount; Based on the current index field, data is extracted from the database table to be extracted, resulting in several query data entries. 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 greater than the current cursor field, then data extraction continues on the database table to be extracted based on the target index field.

3. The method as described in claim 2, characterized in that, The step of further extracting 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; Upon completion of the update, return to the step of determining the current index field based on the current cursor field and the preset cursor backoff amount, so as to continue extracting data from the database table to be extracted.

4. The method as described in claim 1, characterized in that, After the steps of querying the cursor record table and determining the current cursor field based on the query results, the method further includes: Based on the first start time, the first time interval, and the current cursor field, the first data is extracted from the database table to be extracted, and several first query data are obtained. Update the current cursor field to the first positioning cursor field corresponding to the first target query data in the first query data, where the first target query data is the query data with the largest field value in the first query data. Upon completion of the update, the process returns to the step of extracting first data from the database table to be extracted based on the first start time, the first time interval, and the current cursor field to obtain several first query data entries, so as to continue extracting first data from the database table to be extracted.

5. The method as described in claim 4, characterized in that, After the step of returning to the step of extracting first data from the database table to be extracted based on the first start time, the first time interval, and the current cursor field to obtain several first query data records, upon completion of the update, to continue extracting first data from the database table to be extracted, the method further includes: Based on the second start time, the second time interval, and the current cursor field, the second data extraction is performed on the database table to be extracted, and several second query data are obtained. Update the current cursor field to the 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; Upon completion of the update, return to the step of performing a second data extraction on the database table to be extracted based on the second start time, the second time interval, and the current cursor field to obtain several second query data records, so as to continue performing a second data extraction on the database table to be extracted; Wherein, the second start time is greater than the first start time, and the second time interval is twice the first time interval.

6. A data extraction device, characterized in that, The device includes: The record table query module is used to query the cursor record table and determine the current cursor field based on the query results; The data extraction module is used to extract data from the database table to be extracted based on the current cursor field, and obtain several query data. The cursor determination module is used to update the current cursor field based on the sentinel cursor field in the query data if the number of query data reaches the preset allowed 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 several query data when the update is completed; The cursor determination module is further configured to, if the number of query data reaches a preset allowed extraction number, determine a sentinel cursor field from the query data, wherein the sentinel cursor field is the cursor field with the largest field value in the query data; sequentially compare the sentinel cursor field with the cursor fields corresponding to the query data preceding the sentinel cursor field; if there is a first cursor field in the cursor fields that is not equal to the sentinel cursor field, update the current cursor field to the first cursor field.

7. A data extraction device, characterized in that, The device includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the data extraction method as described in any one of claims 1 to 5.

8. 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, it implements the steps of the data extraction method as described in any one of claims 1 to 5.

Images