CN102662946B - The method and system of change is recorded in a kind of automatic monitoring data storehouse - Google Patents
The method and system of change is recorded in a kind of automatic monitoring data storehouse Download PDFInfo
- Publication number
- CN102662946B CN102662946B CN201210039995.3A CN201210039995A CN102662946B CN 102662946 B CN102662946 B CN 102662946B CN 201210039995 A CN201210039995 A CN 201210039995A CN 102662946 B CN102662946 B CN 102662946B
- Authority
- CN
- China
- Prior art keywords
- data
- record
- tables
- database
- cryptographic hash
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
- Debugging And Monitoring (AREA)
Abstract
The invention discloses a kind of method recording change in automatic monitoring data storehouse, comprise: each tables of data be respectively in monitored target database sets up state table, calculate cryptographic hash and Major key that in each tables of data, data record is corresponding respectively, and obtained cryptographic hash and Major key are saved in described state table; Each tables of data in regular autoscan database, calculates the hash value collection that each data record is corresponding; The cryptographic hash that the cryptographic hash obtain the described tables of data of scanning and described state table are preserved contrasts, and determines to record the change whether occurred in described tables of data.The present invention also provides corresponding system.The characteristic that the present invention can not rely on concrete database realizes, to the supervision of data record change, possessing versatility.And the exchanges data between heterogeneous database can be realized flexibly according to the present invention.
Description
Technical field
The present invention relates to data to store and administrative skill field, particularly relate to the method and system recording change in a kind of automatic monitoring data storehouse.
Background technology
In Database Systems application, the data exchange middleware of application, needs to monitor, by the data record automatic synchronization of change in each target database the corresponding data table change of disparate databases.Sometimes also need to carry out the exchanges data between database, to do corresponding process according to the data variation of other databases.Add, the object in deletion or Update Table storehouse, once maloperation, may cause burden, needs a data base administrator or developer to carry out the rewriting of code to the affected entity of relevant possibility.Therefore be necessary automatically to monitor the change of database data record.
But Database Systems manufacturer can not revise self product, its database structure also can not be revised.
In prior art, the method for the change of data record in database being carried out to monitoring automatically has the following two kinds usually:
The first, carry out the change of capture-data record by database trigger.
In order to when database structure change, can carry out following the tracks of, record, DDL trigger can be utilized to record the operation of similar " user sets up table " this change, greatly can alleviate the fussy degree of the change of tracking and location database pattern like this.
DDL trigger is a kind of special trigger, triggers when response data definitional language (DDL) statement.May be used for performing management role in a database, such as, examination & verification and normative database operation.
Use DDL trigger, following several object can be reached:
Prevent from carrying out some change to database schema;
Wish certain situation to occur with the change in response database framework in database;
Want the change in database of record framework or event.
The same with the DML trigger of standard, DDL trigger performs storing process when response events.But with the DML trigger of standard unlike, they do not perform storing process when responding UPDATE, INSERT or DELETE statement of his-and-hers watches or view.They mainly perform storing process at response data definitional language (DDL) statement.The system stored procedures performing the operation of DDL formula also can excite DDL trigger.
First, need to set up a table, be used for all DDL operations within the scope of database of record;
For preserving the table of DDL event after creating, also need to set up a DDL trigger, all events of DDL_DATABASE_LEVEL_EVENTS rank in monitor database:
Preserve all DDL operate and the DDL trigger of creation database rank by creating a log sheet, can the successfully change of all DDL levels in acquisition database, follow the tracks of for DBA and monitor that any change provides more powerful function.
This method depends on the trigger arrangement of database, when data record changes, is initiatively sent out by change information by database.
First scheme, increases timestamp field record data update time in table.
When needing the situation of change monitoring certain table in a certain important time period, comprise insertion, renewal, delete.For example, when statistical conversion to outside system time, what ites is desirable to derive is whole data, and preferably derive derived last time after the data that change.
As DBA, traditional trigger operation can be adopted, build the change that a metadata table or timestamp row carry out monitor data.
Such as, whether the data using CheckSum () and CheckSum_Agg () function to be generally used for detection one table are changed.This is the better method of a replacement trigger, and just this operation can cause the operation of table scan.So still use metadata to carry out the change of tracking data, just newly-built row are for preserving CHECKSUM_AGG (BINARY_CHECKSUM (*)), it will produce a unique value in full table, to distinguish number as you see, for the situation of single change, CHECKSUM be use reasonable, but CHECKSUM_AGG () but can not the situation of change of change certificate of response data.
But the value finding CHECKSUM_AGG (BINARY_CHECKSUM (*)) before and after adjustment is the same, can not distinguish.CHECKSUM_AGG (), although function is described to the change of energy monitoring form data, is not all right in reality test.Especially, when his-and-hers watches carry out symmetric data amendment, cannot monitor.
This second method adds a timestamp field in tables of data, the time of record data record change, reaches the object of monitoring data record change.
This two schemes all needs the characteristic depending on concrete database, such as does programming and could realize, to the supervision of data record change, not possessing versatility.
Summary of the invention
In view of this, the invention provides the method and system of a kind of automatic monitoring data storehouse record change, the characteristic not relying on concrete database realizes, to the supervision of data record change, possessing versatility.The method of a kind of automatic monitoring data storehouse record change provided by the invention, comprising:
The each tables of data be respectively in monitored target database sets up state table, calculates cryptographic hash and Major key that in each tables of data, data record is corresponding respectively, and is saved in described state table by obtained cryptographic hash and Major key;
Each tables of data in regular autoscan database, calculates the hash value collection that each data record is corresponding;
The cryptographic hash that the cryptographic hash obtain the described tables of data of scanning and described state table are preserved contrasts, and determines to record the change whether occurred in described tables of data.
The system of a kind of automatic monitoring data storehouse record change provided by the invention, comprising:
Storage unit, sets up state table for each tables of data be respectively in monitored target database, preserves cryptographic hash corresponding to the data record of described tables of data and Major key in this state table;
Scanning element, for each tables of data in regular autoscan database;
Computing unit, for calculating hash value collection corresponding to each data record;
Contrast unit, contrasts for the cryptographic hash of being preserved by state table in the scanning cryptographic hash that obtains of described tables of data and described storage unit, determines to record the change whether occurred in described tables of data.
In sum, the technical scheme of automatic monitoring data storehouse provided by the invention record change, not from concrete database, but the record from the execution reading database of technical standard SQL statement in tables of data, then generate corresponding cryptographic hash, record Major key and the cryptographic hash of this record, be stored in state table, in order to follow-up scanning contrast, find the concrete transition activities of data record.Do not rely on concrete database, just can there occurs which kind of change that additions and deletions change by the data record of corresponding data table in automatic monitoring data storehouse.The characteristic that the present invention can not rely on concrete database realizes, to the supervision of data record change, possessing versatility.And the exchanges data between heterogeneous database can be realized flexibly according to the present invention.
Accompanying drawing explanation
Fig. 1 is the method flow diagram of a kind of automatic monitoring data storehouse record change provided by the invention;
Fig. 2 is the system architecture schematic diagram of automatic monitoring data storehouse provided by the invention record change.
Embodiment
In view of in prior art, the change of data record in database is carried out to the method Shortcomings of monitoring automatically, the present invention is directed to the method and system that noninvasive data storehouse proposes to record in a kind of automatic monitoring data storehouse change, do not rely on concrete database, which the data record just automatically can telling corresponding data table in database there occurs specifically changes.
Non-intrusion type: do not need to do unnecessary work in target database, just can realize target.Namely rely on less to database, versatility is higher.
Data-base recording changes: the change referring to increase that the record of each tables of data in database occurs, deletion or amendment.
With reference to Fig. 1, the invention provides a kind of method recording change in automatic monitoring data storehouse, comprise the steps:
S01, each tables of data be respectively in monitored target database sets up state table, calculates cryptographic hash and Major key that in each tables of data, data record is corresponding respectively, and is saved in described state table by obtained cryptographic hash and Major key;
S02, each tables of data in regular autoscan database, calculates the hash value collection that each data record is corresponding;
S03, the cryptographic hash that the cryptographic hash obtain the described tables of data of scanning and described state table are preserved contrasts, and determines to record the change whether occurred in described tables of data.
In specific embodiments, database can adopt any one type following:
oracle、mysql、access、sqlite。
Described cryptographic hash corresponding to each data record that calculate out calculates cryptographic hash corresponding to data record after being read by the SQL statement of standard.
Particularly, suppose the Table A, the table B that need to pay close attention in certain database, the step of monitoring its change is:
1) for Table A, table B set up state table a and table b, the cryptographic hash that respectively the data record of reckoner A and table B is corresponding and Major key respectively, and respectively cryptographic hash corresponding for the data record of Table A and table B and Major key are saved in two state table table a and table b;
By standard SQL queries statement, the major key that reading table B respectively records, namely as Major key required in this method; All fields that reading table B respectively records, change into character string forms, then calculate corresponding cryptographic hash (MD5), as record cryptographic hash required in this method.
2) regular autoscan database table A and table B, calculates the hash value collection that data record is corresponding;
3) cryptographic hash that scan table A gained cryptographic hash and table a are preserved is contrasted, draw the change that Table A record occurs; The cryptographic hash that scan table B gained cryptographic hash is preserved with table b contrasts, and draws the change that table B record occurs.
Because method provided by the invention is read the record of institute's monitoring form, such as read the record of table B, just use select*from B] after calculate cryptographic hash corresponding to data record as the basis of comparing before and after data variation, do not rely on the characteristic of concrete database, all have good versatility, can support several data lab environment in actual production.
The method of automatic monitoring data storehouse provided by the invention record change does not rely on the characteristic of concrete database, and can monitor the change of disparate databases tables of data record, such as oracle, mysql, access, sqlite etc., possess versatility.
The exchanges data between heterogeneous database can be realized flexibly according to the present invention.
Because this method realizes based on stsndard SQL, be so just applicable to the database that all compatibility standard SQL statement perform, each database in namely can detecting, the record change of tables of data.The record of these changes just can pass through another exchanging mechanism, is synchronized in other databases.
Therefore, exchanges data between heterogeneous database can be realized based on the present invention.
With reference to Fig. 2, record the system 200 of change in the automatic monitoring data storehouse that the embodiment of the present invention provides, comprising:
Storage unit 210, for store be respectively monitored target database in each tables of data set up state table, preserve cryptographic hash corresponding to the data record of described tables of data and Major key in this state table;
Scanning element 220, for each tables of data in regular autoscan database;
Record in reading database in tables of data, and be supplied to computing unit 230;
Computing unit 230, for calculating hash value collection corresponding to each data record;
Contrast unit 240, contrasts for the cryptographic hash of being preserved by state table in the scanning cryptographic hash that obtains of described tables of data and storage unit 210, determines to record the change whether occurred in described tables of data.
Described database is any one type following:
oracle、mysql、access、sqlite。
Wherein, computing unit 230 comprises:
Data read module 230a, by data record in the SQL statement reading database of standard;
Computing module 230b, calculates out the cryptographic hash that each data record is corresponding.
In sum, the technical scheme of automatic monitoring data storehouse provided by the invention record change, not from concrete database, but the record from the execution reading database of technical standard SQL statement in tables of data, then generate corresponding cryptographic hash, record Major key and the cryptographic hash of this record, be stored in state table, in order to follow-up scanning contrast, find the concrete transition activities of data record.Do not rely on concrete database, just can there occurs which kind of change that additions and deletions change by the data record of corresponding data table in automatic monitoring data storehouse.
Application example
The data exchange middleware applied in the engineering construction of soil and water conservation monitoring Network and information system, technical scheme provided by the invention is adopted automatically to monitor, by the data record automatic synchronization of change in each target database the corresponding data table change of disparate databases.
Due to its non-invasive feature, the installing DB in national each basin and province and configuration can be made all to adopt default behavior, then start the system mentioned in the present invention, can realize doing automatically to detect to the data-base recording in each basin and province.Then by other programs, the change information found in detection is synchronized in the database of each place.
Above to the technical scheme recording change in a kind of automatic monitoring data storehouse provided by the present invention, apply specific case herein to set forth principle of the present invention and embodiment, the explanation of above embodiment just understands method of the present invention and core concept thereof for helping; Meanwhile, for one of ordinary skill in the art, according to thought of the present invention, all will change in specific embodiments and applications, in sum, this description should not be construed as limitation of the present invention.
Claims (4)
1. a method for automatic monitoring data storehouse record change, is characterized in that, comprising:
The each tables of data be respectively in monitored target database sets up state table, calculates cryptographic hash and Major key that in each tables of data, data record is corresponding respectively, and is saved in described state table by obtained cryptographic hash and Major key;
Each tables of data in regular autoscan database, calculates the hash value collection that each data record is corresponding;
The cryptographic hash that the cryptographic hash obtain the described tables of data of scanning and described state table are preserved contrasts, and determines to record the change whether occurred in described tables of data;
Described cryptographic hash corresponding to each data record that calculate out calculates cryptographic hash corresponding to data record after being read by the SQL statement of standard.
2. the method for claim 1, is characterized in that, described database is any one type following:
oracle、mysql、access、sqlite。
3. a system for automatic monitoring data storehouse record change, is characterized in that, comprising:
Storage unit, sets up state table for each tables of data be respectively in monitored target database, preserves cryptographic hash corresponding to the data record of described tables of data and Major key in this state table;
Scanning element, for each tables of data in regular autoscan database;
Computing unit, for calculating hash value collection corresponding to each data record;
Contrast unit, contrasts for the cryptographic hash of being preserved by state table in the scanning cryptographic hash that obtains of described tables of data and described storage unit, determines to record the change whether occurred in described tables of data;
Described computing unit comprises:
Data read module, by data record in the SQL statement reading database of standard;
Computing module, calculates out the cryptographic hash that each data record is corresponding.
4. system as claimed in claim 3, it is characterized in that, described database is any one type following:
oracle、mysql、access、sqlite。
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210039995.3A CN102662946B (en) | 2012-02-20 | 2012-02-20 | The method and system of change is recorded in a kind of automatic monitoring data storehouse |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201210039995.3A CN102662946B (en) | 2012-02-20 | 2012-02-20 | The method and system of change is recorded in a kind of automatic monitoring data storehouse |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN102662946A CN102662946A (en) | 2012-09-12 |
| CN102662946B true CN102662946B (en) | 2015-10-07 |
Family
ID=46772437
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201210039995.3A Active CN102662946B (en) | 2012-02-20 | 2012-02-20 | The method and system of change is recorded in a kind of automatic monitoring data storehouse |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN102662946B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| RU2725760C1 (en) * | 2017-05-25 | 2020-07-06 | Алибаба Груп Холдинг Лимитед | Method and apparatus for determining state of database and method and apparatus for verifying consistency |
Families Citing this family (19)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103870465B (en) * | 2012-12-07 | 2018-05-18 | 厦门雅迅网络股份有限公司 | A kind of implementation method of the database reptile of non-invasive |
| CN103345521B (en) * | 2013-07-17 | 2016-04-13 | 腾讯科技（深圳）有限公司 | A kind of method and apparatus processing key assignments in Hash table database |
| CN103605584A (en) * | 2013-10-22 | 2014-02-26 | 芜湖大学科技园发展有限公司 | Method for mirroring metadata in electric power metadata management platform |
| US9552390B2 (en) | 2014-04-29 | 2017-01-24 | Futurewei Technologies, Inc. | System and method for out of order multiple query execution within stored procedure |
| CN103970847A (en) * | 2014-04-29 | 2014-08-06 | 国家无线电监测中心成都监测站 | Wireless short wave monitoring data storing method and database system |
| CN106557308B (en) * | 2015-09-29 | 2020-11-03 | 腾讯科技（深圳）有限公司 | Software continuous integration method and device |
| CN107103041A (en) * | 2017-03-28 | 2017-08-29 | 广东网金控股股份有限公司 | A kind of storage method and storage system of SQL SERVER database updates |
| CN107818178A (en) * | 2017-11-23 | 2018-03-20 | 郑州云海信息技术有限公司 | A kind of method of automatically generated data storehouse change SQL scripts |
| CN108153838B (en) * | 2017-12-15 | 2022-03-11 | 山东中维世纪科技股份有限公司 | MySQL database middleware preprocessing method |
| CN110196880B (en) * | 2018-06-08 | 2023-05-12 | 腾讯科技（深圳）有限公司 | Heterogeneous database data synchronization method and device, storage medium and electronic device |
| CN109933490B (en) * | 2019-03-11 | 2022-07-12 | 福建慧政通信息科技有限公司 | Data monitoring method and terminal |
| CN109977170A (en) * | 2019-03-20 | 2019-07-05 | 南威软件股份有限公司 | The synchronous method and system of network data inside and outside a kind of government affairs |
| CN110209512B (en) * | 2019-05-30 | 2020-06-30 | 口碑(上海)信息技术有限公司 | Data checking method and device based on multiple data sources |
| CN112579559B (en) * | 2019-09-30 | 2024-07-26 | 北京国双科技有限公司 | Key value pair management verification method, device, equipment and storage medium |
| CN110968569B (en) * | 2019-12-19 | 2023-12-29 | 广州品唯软件有限公司 | Database management method, database management device, and storage medium |
| US11625373B2 (en) | 2020-04-30 | 2023-04-11 | International Business Machines Corporation | Determining additions, deletions and updates to database tables |
| CN112000732A (en) * | 2020-07-16 | 2020-11-27 | 北京计算机技术及应用研究所 | Trigger-based mutual synchronization method between heterogeneous databases |
| US11531664B2 (en) * | 2021-01-06 | 2022-12-20 | Google Llc | Stand in tables |
| CN114201615B (en) * | 2021-12-28 | 2023-06-27 | 上海市精神卫生中心（上海市心理咨询培训中心） | Scientific research data change review method and server based on data snapshot |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101136833A (en) * | 2007-09-26 | 2008-03-05 | 深圳市迅雷网络技术有限公司 | Method, system and device for across entity network to access data |
| CN101464902A (en) * | 2009-01-06 | 2009-06-24 | 中国科学院软件研究所 | Verification method and system for outsourced database query result |
| CN101697169A (en) * | 2009-10-23 | 2010-04-21 | 中兴通讯股份有限公司 | Method, device and system for data synchronization between source database and destination database |
| CN102096676A (en) * | 2009-12-11 | 2011-06-15 | 中国移动通信集团公司 | Data updating and query control method and system |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7707181B2 (en) * | 2003-02-19 | 2010-04-27 | Microsoft Corporation | System and method of distributing replication commands |
-
2012
- 2012-02-20 CN CN201210039995.3A patent/CN102662946B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101136833A (en) * | 2007-09-26 | 2008-03-05 | 深圳市迅雷网络技术有限公司 | Method, system and device for across entity network to access data |
| CN101464902A (en) * | 2009-01-06 | 2009-06-24 | 中国科学院软件研究所 | Verification method and system for outsourced database query result |
| CN101697169A (en) * | 2009-10-23 | 2010-04-21 | 中兴通讯股份有限公司 | Method, device and system for data synchronization between source database and destination database |
| CN102096676A (en) * | 2009-12-11 | 2011-06-15 | 中国移动通信集团公司 | Data updating and query control method and system |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| RU2725760C1 (en) * | 2017-05-25 | 2020-07-06 | Алибаба Груп Холдинг Лимитед | Method and apparatus for determining state of database and method and apparatus for verifying consistency |
Also Published As
| Publication number | Publication date |
|---|---|
| CN102662946A (en) | 2012-09-12 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN102662946B (en) | The method and system of change is recorded in a kind of automatic monitoring data storehouse | |
| US10073903B1 (en) | Scalable database system for querying time-series data | |
| CN106648446B (en) | Storage method and device for time sequence data and electronic equipment | |
| CN102982085B (en) | Data mover system and method | |
| US10896156B2 (en) | Flexible synchronous file system replication | |
| US20110153568A1 (en) | High volume, high speed adaptive data replication | |
| US10552460B2 (en) | Sensor data management apparatus, sensor data management method, and computer program product | |
| CN107533551A (en) | The other big data statistics of block level | |
| CN107977396B (en) | Method and device for updating data table of KeyValue database | |
| TW201800967A (en) | Method and device for processing distributed streaming data | |
| US10303654B2 (en) | Hybrid data distribution in a massively parallel processing architecture | |
| CN103902623A (en) | Method and system for accessing files on a storage system | |
| CN104731864A (en) | Data storage method for mass unstructured data | |
| CN105279285B (en) | A kind of synchronization system and method for relevant database and non-relational database | |
| CN102722584B (en) | Data storage system and method | |
| CN104461824A (en) | Magnetic disk health information optimal management method and device | |
| Kvet et al. | Transaction management in fully temporal system | |
| CN111125229A (en) | Data blood margin generation method and device and electronic equipment | |
| Jianmin et al. | An improved join‐free snowflake schema for ETL and OLAP of data warehouse | |
| CN105373621A (en) | Rapid database-system-across data increment migration method | |
| CN107515807B (en) | Method and device for storing monitoring data | |
| CN105446824A (en) | Table increment obtaining method and remote data backup method | |
| CN103605797A (en) | Data storage and inquiry devices and data storage and inquiry methods | |
| CN101853278A (en) | Application method of data storage system | |
| CN106127074A (en) | A kind of storage device based on intelligent key and data thereof store and read method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C14 | Grant of patent or utility model | ||
| GR01 | Patent grant |