Shulou(Shulou.com)06/01 Report--

This article introduces ORACLE materialized view how to solve CMS data synchronization and late DATA PIPLELINE, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Why only DataPiple Line is involved, because if there were DataPipe Line, I wouldn't have to write the following story. So the development of a new technology and software can solve many headaches. Then let's take a look at the problem first.

No matter how many ideas people have, there are actually things that can meet your needs, such as Dta Pipleline. I can think of several problems that it can solve right now.

1 the grayscale release of heterogeneous databases, and the trouble of replacing the database system can be solved through this data pipeline system.

(2) Real-time analysis of big data.

(3) data synchronization and single database to multi-database data distribution, and can solve the possible performance problems.

4 data desensitization and real-time data synchronization from production database to test database

And, of course, its own function, data pipeline.

The story is recently due to split part of the original ORACLE database functions, to MYSQL, the need for data synchronization, database synchronization methods are many, but to find a suitable is not easy, not technically difficult, but too many methods, there may be choice phobia. And I found a problem, that is, it is a simple thing to think, and when it is really done, it becomes not so simple. All kinds of problems arise. In fact, after so many years of work, we have seen great storms, so I have been afraid to say that this is no problem. That is very general, and this will certainly work (of course, when you cheer up and encourage others or yourself, you still have to say).

In fact, the use of any technology should be determined according to the business, and the use of a certain technology should be determined purely from the way of technology. At the beginning of this task, it was actually very simple to use Kettle to extract data from ORACLE to MYSQL. The problem is neither KETTLE nor database, but the problem is still at the non-technical level. The problem is that the trigger point of data changes to some tables in the CMS system is not clear, but how to write the relevant extraction statements in an unclear way. Then such a result will cause a correlation effect, 1 because the change point of the extracted data can not be found, so the point of extracting data cannot be found, 2 can not find the point, then can not carry out the incremental extraction of SQL statements 3 can not incrementally extract data, the development of students can not get the correct data 4 can not have the correct data, the whole project will have a problem, chain reaction.

There is no shortage of technical solutions to solve problems, but lack of reliable and stable solutions, so the introduction of a plan, especially an untried one, has the degree of difficulty and the possibility of failure. We must find a solution that can be accepted and accepted by the three parties, business, development, operation and maintenance, to determine the plan.

Since some data points and data changes are still uncertain, there are many ways to make the changes can be captured by technical means.

Recently, we have begun to test DATA PIPLELINE if we do this data synchronization scheme through DATA PIPLELINE, in fact, things will become much easier, first of all, to extract data from the bottom, but also to refresh it regularly, and so on, so the flow of data from ORACLE to MYSQL is as simple as turning on the faucet, the data is coming as soon as the water is coming, turning it off or off. Of course, with DATAPIPLE LINE, most of the functions of our KETTLE can no longer be used.

But good things actually need to wait, the project will not wait, the current requirements still have to get the changing data in the traditional way, and at a point in time, it is thought of the materialized view, through the materialized view ON DEMAND + time point, we can easily know the data changes of the table we are concerned about during the day.

When it comes to materialized views, ORACLE is still the strongest in the traditional database, and others are not as functional as ORACLE, so each database should actually learn. SQL SERVER created a temporal Table in 2016, although it can solve some of the problems, but it is absolutely busy enough for the BUG of concurrent transactions to drink a pot.

Let's talk about how the overall ORACLE can meet the physical way of data extraction of our KETTLE this time.

First of all, the ORACLE where the CMS system is located needs to copy the tables, establish the materialized view, and establish the log of the materialized view. And the requirement is that both tables and materialized views should have primary key, otherwise other methods will have to be considered.

It seems that the problem has been solved, but in fact, the unexpected problem occurs when you think there is no problem.

We are using ID in MLOG$_CMS_TEST.

To determine whether the data is incremental.

Then here comes the problem.

problem

1 data in the process of operation, there will be three types of I U D operations, but if it is D, then this data should be cleared, so we should delete this ID from our copied statements in the process of copying.

2 to solve this problem, use the following statement to filter out the deleted records directly. There are only insert and update statements. The problem seems to have been solved, but in fact,

In fact, no, if I am inserting data at this time, because in the way of the primary key, I also insert the original deleted primary key, then the above statement will go wrong because the newly inserted data is filtered.

Therefore, the solution of ignoring the business is not a stable one. It is assumed that the deleted data will no longer be filled and will not be inserted according to the original primary key. Then there will be no problem with the above plan, otherwise there will be loopholes.

Finally, I still look forward to using new technologies to solve some business problems, fast, secure and stable. For example, if I expect Data Pipleline to increase the function of the destination side of postgresql, the hair of project personnel who are looking forward to transferring from Oracle, sql server to Postgresql will flourish for a longer period of time.

About ORACLE materialized view how to solve CMS data synchronization and late DATA PIPLELINE to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.