Method for realizing incremental data extraction based on materialized view log

Abstract

The invention particularly relates to a method for realizing incremental data extraction based on materialized view logs. According to the method for realizing incremental data extraction based on the materialized view log, updating and insertion of data are realized in a mode of combining an Oracle database SQL language and the materialized view log, and incremental data extraction of a special table without a primary key in a data warehouse project is realized. According to the method for extracting the incremental data based on the materialized view log, the incremental data can be obtained from the table without the primary key and the timestamp in the Oracle database, the I / O and calculation overhead is reduced, the table structure does not need to be modified, the automation degree is high, the application scene is wide, and the method is suitable for application and popularization.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and particularly relates to a method for realizing incremental data extraction based on a materialized view log. Background Art

[0002] The traditional methods for Oracle database data extraction mainly include the following:

[0003] (1) Using SQL statements: By writing SQL query statements, data can be directly queried and exported from the Oracle database. This method is applicable to simple data extraction requirements, and data in the table can be queried according to conditions by executing the SELECT statement.

[0004] (2) Using Oracle export tools: For large amounts of data, Oracle-provided export tools such as EXP and EXPDP can be used to export data to files for transmission or backup.

[0005] (3) Using Oracle data integration tools: Oracle provides various data integration tools such as Oracle GoldenGate and Oracle Data Integrator (ODI), which can implement more complex data operations, including real-time data extraction, data conversion, and data loading, etc.

[0006] However, in the actual operation process, the following problems may be encountered:

[0007] Low efficiency of full extraction: In the early stage of data synchronization, a full table scan is required. When the amount of data is very large, directly using SQL statements for full extraction may cause performance problems, such as slow query speed and consumption of a large amount of system resources, and cannot meet the real-time requirements.

[0008] Dependence on table structure modification: During the extraction process, if the data in the database is modified by other transactions, the extracted data may be inconsistent. Methods such as timestamp and full record comparison require adding additional fields or maintaining complex logic, which affects the original business design.

[0009] Network latency: If the extraction operation needs to transmit data through the network, network latency may affect the extraction efficiency.

[0010] Permission issues: Without corresponding permissions, it may be impossible to access certain database objects or perform certain data extraction operations.

[0011] Performance loss of triggers: Incremental capture based on triggers has a significant impact on the performance of the source database and is difficult to handle high-concurrency scenarios.

[0012] To solve the above problems, improve the loading efficiency, reduce latency and optimize the user experience, the present invention proposes a method for realizing incremental data extraction based on materialized view logs. Summary of the Invention

[0013] In order to make up for the deficiencies of the prior art, the present invention provides a simple and efficient method for realizing incremental data extraction based on materialized view logs.

[0014] The present invention is realized through the following technical solutions:

[0015] A method for realizing incremental data extraction based on materialized view logs, characterized in that: the update and insertion of data are realized by combining the SQL language of the Oracle database with the materialized view logs, and the incremental data extraction of special tables without primary keys in the data warehouse project is realized; specifically includes the following steps:

[0016] Step S1, perform custom settings according to requirements to clarify the target table structure;

[0017] Step S2, create a materialized view log;

[0018] Step S3, obtain incremental data from the source table and sort it in ascending order according to the sequence value;

[0019] Step S4, submit the data and delete the already submitted incremental data from the source table;

[0020] Step S5, after the increment is completed, delete the materialized view log created on the target table.

[0021] In the step S1, create a target table, customize the name of the target table, and the table contains a column of numeric type and a column of variable-length string (VARCHAR) type;

[0022] Among them, the column of numeric type serves as the primary key (PRIMARY KEY) of the table and is the unique identifier of each target in the table;

[0023] The column of variable-length string (VARCHAR) type is used to store the target name, and the maximum character length is customized according to requirements.

[0024] In the step S2, create a materialized view log on the target table, and the log includes the row identifier ROWID, the sequence sequence and including new VALUES;

[0025] Among them, the row identifier ROWID is used to uniquely identify each row in the target table;

[0026] The sequence sequence is used to record the changes of the contents of each column in the target table;

[0027] including new VALUES indicates the newly inserted values in the target table.

[0028] A system for realizing incremental data extraction based on a materialized view log, including a target table management module, a materialized view log management module, and a source table management module;

[0029] The target table management module is responsible for customizing settings according to requirements and clarifying the target table structure;

[0030] The source table management module is responsible for obtaining incremental data from the source table, sorting it in ascending order according to the sequence value, and deleting the already submitted incremental data from the source table after submitting the data;

[0031] The materialized view log management module is responsible for creating a materialized view log and deleting the materialized view log created on the target table after the increment is completed.

[0032] The target table management module is responsible for creating a target table, customizing the naming of the target table, and the table contains a column of numeric type and a column of variable-length string (VARCHAR) type;

[0033] Among them, the column of numeric type is used as the primary key (PRIMARY KEY) of the table and is the unique identifier of each target in the table;

[0034] The column of variable-length string (VARCHAR) type is used to store the target name and customize the maximum character length according to requirements.

[0035] The materialized view log management module is responsible for creating a materialized view log on the target table, and the log includes the row identifier ROWID, the sequence, and including new VALUES;

[0036] Among them, the row identifier ROWID is used to uniquely identify each row in the target table;

[0037] The sequence is used to record the changes of the contents of each column in the target table;

[0038] including new VALUES indicates the newly inserted values in the target table.

[0039] A device for realizing incremental data extraction based on a materialized view log, characterized in that: it includes a memory and a processor; the memory is used to store a computer program, and the processor is used to implement the above method steps when executing the computer program.

[0040] A readable storage medium, characterized in that: a computer program is stored on the readable storage medium, and when the computer program is executed by a processor, the above-mentioned method steps are implemented.

[0041] The beneficial effects of the present invention are as follows: The method for implementing incremental data extraction based on materialized view logs can obtain incremental data from tables in an Oracle database without a primary key and a timestamp, which not only reduces I / O and computing overhead, but also does not require modifying the table structure, has a high degree of automation, a wide range of application scenarios, and is suitable for popularization and application. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0043] Appendix Figure 1 It is a schematic diagram of the method for implementing incremental data extraction based on materialized view logs of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0044] In order to enable those skilled in the art to better understand the technical solutions in the present invention, the following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, rather than all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0045] When a data warehouse or other data needs to be synchronized, some tables cannot obtain incremental data because they do not have a timestamp and a primary key.

[0046] The method for implementing incremental data extraction based on materialized view logs realizes data update and insertion in a way that combines Oracle database SQL language with materialized view logs, and realizes incremental data extraction for special tables without a primary key in a data warehouse project; specifically includes the following steps:

[0047] Step S1: Perform custom settings according to requirements to clarify the target table structure;

[0048] Step S2: Create a materialized view log;

[0049] The materialized view log has performance optimization. It can not only incrementally maintain and only process changed data, greatly reducing I / O and computing overhead, and is suitable for large data volume scenarios, but also supports timed or on-demand refresh strategies, flexibly adapting to different real-time requirements.

[0050] At the same time, the materialized view log also has compatibility and scalability. It can be integrated with advanced replication tools such as Oracle Streams and GoldenGate, and a complex data distribution architecture can be built, which is suitable for scenarios such as cross-database replication, data warehouse ETL, and real-time reports.

[0051] Step S3: Obtain incremental data from the source table and sort it in ascending order according to the sequence value;

[0052] Step S4: Commit the data and delete the committed incremental data from the source table;

[0053] Step S5: After the increment is completed, delete the materialized view log created on the target table.

[0054] In the said step S1, create a target table and customize the naming of the target table. The table contains a column of numeric type and a column of variable-length string (VARCHAR) type;

[0055] Among them, the column of numeric type is used as the primary key (PRIMARY KEY) of the table and is the unique identifier of each target in the table;

[0056] The column of variable-length string (VARCHAR) type is used to store the target name, and the maximum character length can be customized according to requirements.

[0057] In the said step S2, create a materialized view log on the target table. The log includes the row identifier ROWID, the sequence sequence, and including new VALUES;

[0058] Among them, the row identifier ROWID is used to uniquely identify each row in the target table;

[0059] The sequence sequence is used to record the changes of the contents of each column in the target table;

[0060] including new VALUES represents the newly inserted values in the target table.

[0061] Embodiment

[0062] In the student management application scenario, the specific implementation solution is as follows:

[0063] (1) Create a table named "STUDENT", which contains the following columns:

[0064] Student ID (C_ID): This is a numeric field that serves as the primary key to uniquely identify each student.

[0065] Student Name (C_NAME): This is a string field with a length not exceeding 255 characters, used to store the names of students.

[0066] The code is as follows:

[0067] CREATE TABLE STUDENT(

[0068] C_ID NUMBER PRIMARY KEY,

[0069] C_NAME VARCHAR(255) )

[0071] (2) Create a materialized view log on the STUDENT table, which contains the following:

[0072] ROWID: The row identifier, used to uniquely identify each row in the table.

[0073] sequence: A sequence used to record changes to the C_ID and C_NAME columns.

[0074] including new VALUES: Indicates that newly inserted values are included in the log.

[0075] The SQL statement is as follows:

[0076] CREATE MATERIALIZEDVIEW LOG ON STUDENT WITH ROWID,

[0077] sequence(C_ID,C_NAME)including new VALUES;

[0078] (3) Query all records in the source table MLOG$_STUDENT and sort them in ascending order by the value of the SEQUENCE$$ column to obtain incremental data;

[0079] The SQL statement is as follows:

[0080] SELECT*

[0081] FROM MLOG$_STUDENT

[0082] ORDER BY SEQUENCE$$;

[0083] (4) After submitting the incremental data, delete all records in the source table MLOG$_STUDENT where the value of the SEQUENCE$$ column is greater than lastID.

[0084] The SQL statement is as follows:

[0085] DELETE FROM MLOG$_STUDENT WHERE SEQUENCE$$>lastID

[0086] (5) After the increment is completed, delete the records in the source table MLOG$_STUDENT that meet the condition (the value of the SEQUENCE$$ column is greater than lastID).

[0087] The SQL statement is as follows:

[0088] DROP MATERIALIZEDVIEW LOG ON STUDENT;

[0089] The system for realizing incremental data extraction based on the materialized view log includes a target table management module, a materialized view log management module, and a source table management module;

[0090] The target table management module is responsible for customizing settings according to requirements and clarifying the target table structure;

[0091] The source table management module is responsible for obtaining incremental data from the source table, sorting it in ascending order according to the sequence value, and deleting the already submitted incremental data from the source table after submitting the data;

[0092] The materialized view log management module is responsible for creating a materialized view log and deleting the materialized view log created on the target table after the increment is completed.

[0093] The target table management module is responsible for creating a target table, customizing the name of the target table, and the table contains a column of numeric type and a column of variable-length string (VARCHAR) type;

[0094] Among them, the column of numeric type is used as the primary key (PRIMARY KEY) of the table and is the unique identifier of each target in the table;

[0095] The column of variable-length string (VARCHAR) type is used to store the target name and customize the maximum character length according to requirements.

[0096] The materialized view log management module is responsible for creating a materialized view log on the target table, and the log includes the row identifier ROWID, the sequence sequence, and including new VALUES;

[0097] Among them, the row identifier ROWID is used to uniquely identify each row in the target table;

[0098] The sequence is used to record the changes of the contents of each column in the target table;

[0099] including new VALUES represents the newly inserted values in the target table.

[0100] The device for realizing incremental data extraction based on the materialized view log includes a memory and a processor; the memory is used to store computer programs, and the processor is used to implement the above method steps when executing the computer programs.

[0101] A computer program is stored on the readable storage medium, and when the computer program is executed by the processor, the above method steps are implemented.

[0102] Compared with the prior art, the method for realizing incremental data extraction based on the materialized view log has the following characteristics:

[0103] First, it reduces I / O and computing overhead: only processes incremental change data (such as INSERT / UPDATE / DELETE records generated by DML operations), avoids full table scans or full-scale calculations, and greatly reduces resource consumption. For example, when using the REFRESH FAST mode, the system only needs to read the change records in the materialized view log for synchronization.

[0104] Second, it supports flexible refresh strategies: allows on-demand (ON DEMAND) or scheduled refreshes, and users can freely choose real-time or batch processing modes to balance business real-time performance and system load. Automated refresh management can be achieved through stored procedures (such as DBMS_MVIEW.REFRESH).

[0105] Third, there is no need to modify the source table structure: the materialized view log is automatically maintained by the Oracle kernel, and there is no need to add additional fields such as timestamps and version numbers to the base table, retaining the integrity of the original business design.

[0106] Fourth, it adapts to the scenario of tables without a primary key: supports identifying change records through ROWID, solves the problem of incremental data capture for tables without a primary key, and expands the applicable scenarios.

[0107] The above-described embodiments are only one of the specific implementation manners of the present invention, and the common changes and substitutions made by those skilled in the art within the scope of the technical solution of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for implementing incremental data extraction based on materialized view logs, characterized in that: Implement data update and insertion by combining Oracle database SQL language with materialized view logs, and realize incremental data extraction for tables without primary keys in data warehouse projects; Specifically, it includes the following steps: Step S1: Perform custom settings according to requirements to clarify the target table structure; Step S2: Create a materialized view log; Step S3: Obtain incremental data from the source table and sort it in ascending order according to the sequence value; Step S4: Submit the data and delete the already submitted incremental data from the source table; Step S5: After the increment is completed, delete the materialized view log created on the target table.

2. The method for implementing incremental data extraction based on a materialized view log according to claim 1, wherein: In the said Step S1, create a target table, customize the name of the target table, and the table contains a column of numeric type and a column of variable-length string type; Among them, the column of numeric type serves as the primary key of the table and is the unique identifier of each target in the table; The column of variable-length string type is used to store the target name, and the maximum character length is customized according to requirements.

3. The method for implementing incremental data extraction based on a materialized view log according to claim 1, characterized in that: In the said Step S2, create a materialized view log on the target table, and the log includes row identifier ROWID, sequence, and including new VALUES; Among them, the row identifier ROWID is used to uniquely identify each row in the target table; The sequence is used to record the changes of the contents of each column in the target table; including new VALUES indicates the newly inserted values in the target table.

4. A system for realizing incremental data extraction based on a materialized view log, characterized in that: It includes a target table management module, a materialized view log management module, and a source table management module; The target table management module is responsible for performing custom settings according to requirements to clarify the target table structure; The source table management module is responsible for obtaining incremental data from the source table, sorting it in ascending order according to the sequence value, and deleting the already submitted incremental data from the source table after submitting the data; The materialized view log management module is responsible for creating a materialized view log and deleting the materialized view log created on the target table after the increment is completed.

5. The system for realizing incremental data extraction based on a materialized view log according to claim 4, characterized in that: The said target table management module is responsible for creating a target table, customizing the name of the target table, and the table contains a column of numeric type and a column of variable-length string type; Among them, the column of numeric type serves as the primary key of the table and is the unique identifier of each target in the table; The column of variable-length string type is used to store the target name, and the maximum character length is customized according to requirements.

6. The system for realizing incremental data extraction based on a materialized view log according to claim 4, wherein: The said materialized view log management module is responsible for creating a materialized view log on the target table, and the log includes row identifier ROWID, sequence, and including new VALUES; Among them, the row identifier ROWID is used to uniquely identify each row in the target table; The sequence is used to record the changes of the contents of each column in the target table; including new VALUES indicates the newly inserted values in the target table.

7. An apparatus for realizing incremental data extraction based on a materialized view log, characterized in that: It includes a memory and a processor; the memory is used to store computer programs, and the processor is used to implement the method steps described in any one of claims 1 to 3 when executing the computer programs.

8. A readable storage medium, characterized in that: A computer program is stored on the readable storage medium, and when the computer program is executed by a processor, the method steps described in any one of claims 1 to 3 are implemented.

Images