Shulou(Shulou.com)05/31 Report--

This article mainly explains the "DBus database table structure change processing method is what", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "DBus database table structure change processing method is what" it!

I. change in the structure of the perception table

Oracle has provided us with good support for perceiving table structure changes through DDL trigger. The next thing we need to consider is how to make DBus aware of table structure changes. We discuss the following two options:

1.1 RPC scenario

Invoke the REST service provided by DBus in DDL trigger to send the table structure change event to DBus.

The idea of this scheme is simple and easy to implement, but it also has some obvious disadvantages, such as DBus needs to provide REST services with high availability and low latency, otherwise the DDL operation in the database may become slow or even error; the REST server of DBus must open firewall policy for all databases with real-time data synchronization requirements, which will bring great trouble to the deployment of DBus.

1.2 OGG real-time synchronization scheme

The table structure change event is stored in an Event table in DDL trigger, and then the data is synchronized from the log to Kafka in real time through OGG, thus the table structure change event is sensed.

The implementation of this scheme is relatively complex but has many advantages, such as relatively less intrusiveness to the database, DDL only writes the data to the Event table, compared with network communication, its delay is lower and the reliability is higher; the more obvious advantage is that this scheme is based on database log and can use the data of Event table to strictly distinguish the data before and after the change of table structure.

For example, for table: test, execute insert → alter → insert in turn, because there is a delay in reading database logs by OGG. If you use the RPC scheme, there may be such a situation: after the DBus REST service receives the alter event, the record of the first insert is captured by OGG and sent to DBus, and DBus will think that this data contains alter-changed data. This is a very serious problem, and the OGG real-time synchronization scheme is implemented by reading logs by OGG, which can perfectly avoid this problem.

Compared with the two schemes, the OGG real-time synchronization scheme has obvious advantages, and finally we adopt this scheme.

However, the adoption of this scheme is not plain sailing. According to the overall idea of the scheme, we encounter a very strange problem: the data written to the Event table through DDL trigger can not be read by OGG. After many attempts, we try to find the answer in the OGG document, but the final result is: DML or DDL operations performed from within a DDL trigger are not captured.

This answer makes the problem more difficult, but this is the best solution and we have no reason to give up. So we began to try to call the stored procedure in DDL trigger and perform the insert operation of Event table in the stored procedure, but because the stored procedure and DDL trigger still belong to the same transaction, the data of Event table still can not be captured by OGG. But through this attempt, we think that as long as we write the Event table in another transaction, we can solve the problem we face, so we think of RPC, but the disadvantage of RPC is too obvious. So are there any other alternatives?

In fact, many languages can be used to write stored procedures in oracle database, Oracle 8i began to support java to write stored procedures, so we immediately began to implement java stored procedures, through JDBC connection to the database to write Event tables and commit transactions, and finally verified the feasibility of this method through practice. OGG successfully obtained the data that DDL trigger called java stored procedures to write to Event tables.

However, this realization is not perfect. When we deployed DDL trigger in the production environment, we found that the components needed to execute java were not installed in the database server, and each deployment required DBA students to install the components needed to execute java stored procedures. We tried to find a solution that did not use java stored procedures. Here, I would like to thank Mr. Han Feng for his help. After listening to our implementation principle, Mr. Han inspired us that autonomous affairs should be able to solve this problem. We immediately began to transform DDL trigger to support autonomous affairs. After the transformation, the scheme is perfect, and the final implementation logic is shown in figure 1:

Second, handle table structure change events

DBus already has the ability to perceive table structure changes through events, so let's explain in detail how to handle table structure change events.

The following figure describes the complete processing flow of Event:

Event describes the name of the table that has undergone structural changes, the schema to which the table belongs, and the metadata version number. After DBus accepts and parses Event, it invokes the metadata crawling module according to the table name, schema and version number to obtain the metadata (including field type, length, comments, etc.) of the table. In fact, DDL trigger and alter statements are executed in a transaction. In this way, the metadata of the modified table structure cannot be obtained from the data dictionary of oracle during the execution of trigger. The metadata we wrote into the meta_ query table is only the metadata information before the execution of the alter statement (so we named the table table_meta_his). To get complete metadata information, you need to query with table_meta_his and data dictionary, as shown in the following SQL:

There are two possible results of this SQL:

1) contains only the data in the all_tab_cols view

2) contains both data in the all_tab_cols view and data in the table_meta_his table (the purpose of the is_current field is to distinguish the source of the field)

The result An indicates that no data is found in the table_meta_his table, which indicates that the table structure change did not happen again during the period from the generation table structure change Event to the metadata crawler successfully fetching the metadata, while the result B indicates that the table structure change occurred one or more times during this period.

Why use union all?

Using the two SQL in the figure above alone may cause the metadata fetching program to get the wrong result. For example, after receiving the table structure change Event 1, we call SQL 1 to query the table_meta_his result set is empty, and the table structure changes again before calling SQL 2 (named Event 2). In this case, the result we query through SQL 2 is actually the result of the change again. If the metadata generated by this result is used to parse the data between Event 1 and Event 2, if the two table structure changes are incompatible, it will inevitably lead to parsing failure.

Thank you for your reading, the above is the content of "what is the processing method of DBus database table structure change". After the study of this article, I believe you have a deeper understanding of what the processing method of DBus database table structure change is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.