CN105095056B - A kind of method of data warehouse data monitoring - Google Patents
A kind of method of data warehouse data monitoring Download PDFInfo
- Publication number
- CN105095056B CN105095056B CN201510502221.3A CN201510502221A CN105095056B CN 105095056 B CN105095056 B CN 105095056B CN 201510502221 A CN201510502221 A CN 201510502221A CN 105095056 B CN105095056 B CN 105095056B
- Authority
- CN
- China
- Prior art keywords
- monitoring
- data
- configuration
- content
- field
- 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
Abstract
A kind of method of data warehouse data monitoring, comprises the following steps：It is determined that needing the monitoring table and content of data warehouse being monitored, parameter configuration, three kinds of the content point of monitoring table parameter configuration are carried out；1 1) whether the content that mainly monitors of data volume monitoring amount of configuration data monitoring to be that monitoring table lists the data volume increased newly with the same day abnormal；1 2) database Data source table structure control configuration；The content of Data source table structure control is whether the table structure in monitoring data source changes, including new field, deletion field, modification field type, modification field length etc.；These, which change some, can cause data syn-chronization program to report an error, and some, which may represent business and change, all needs data analyst to pay close attention to；1 3) data source literary name section value monitoring configuration be monitoring data source table emphasis field value, typically dimension table value.
Description
Technical field
The present invention relates to the method being monitored extremely occur in a kind of business datum source processing procedure to database.
Background technology
One data warehouse correspond to multiple business datum sources.With business deepen constantly, it is necessary to the data analyzed not yet
Disconnected to increase, accordingly, data warehouse task is various.Have daily and largely increase data storage newly in data warehouse, if daily
Occur exception in task processes, the quality of data can be influenceed, and the data in the next time backward may be produced
Influence.Therefore exception of the data in processing procedure is found in time, is handled.What it is to data warehouse is very important.
A kind of database monitoring methods of CN103605722A, according to the information of each database, obtain corresponding with the information of each database
Monitor configuration file；Corresponding with the information of each database according to acquisition monitors configuration file, using write in advance with
Monitoring programme corresponding to the information of each database, each self-corresponding database is monitored.
CN103746837A database monitoring systems, including database monitoring device and monitoring cluster, wherein, the data
Storehouse supervising device, for receiving the triggering command from monitoring cluster；The first instruction is sent to load balancing apparatus, described first
Instruct and refer to for indicating whether available, the described cluster of load balancing apparatus detection cluster is available in the cluster
The software agent of each node whether there is；Receive the first response message from the load balancing apparatus；Emphasize to load
Equilibrium monitoring.
But prior art do not have business datum source is changed and will the abnormal early warning of output and to the comprehensive of database
Monitoring, this to ensure data warehouse stabilization be accurately highly effective.To reach to existing Mission Monitor, more efficient profit
Prevention data missing is omitted, while the purpose of monitoring business DSN change, it is proposed that a kind of number of data warehouse
According to monitoring technology, the quality of data of the daily processing to data warehouse carries out comprehensive monitoring.
The content of the invention
The method that the present invention proposes a kind of monitoring of data warehouse data, it is by performing timing monitor task and correlation
A series of configurations can be achieved to be monitored the data cases of the table of the daily renewal of data warehouse, can also realize because of business datum
Source change and will the abnormal early warning of output, the various problems in program emerged in operation can be found in time.To ensureing data
The stable and accurate operation in warehouse can play considerable effect.
The technical scheme is that a kind of method of data warehouse data monitoring, comprises the following steps：
1) the monitoring table and content for the data warehouse that needs are monitored are determined, carries out parameter configuration, monitoring table parameter is matched somebody with somebody
Three kinds of the content put point；
1-1) data volume monitoring configuration
The content of data volume monitoring is whether the data volume increased newly on the same day that monitoring table is listed is abnormal, judges abnormal standard
First it is whether data volume is 0, is so exactly abnormal if 0；Next to that carried out with the historical data of yesterday and last week on the same day
Contrast, if relatively differing by more than certain proportion or threshold value with historical data, is considered as exception；The content bag for needing monitoring to configure
Include data name, table name, time field, time field type, statistical item, statistical condition, monitoring period；Monitoring programme is by root
Corresponding SQL statement is spliced into according to the content for needing to monitor configuration, treats scheduler program to perform；
1-2) the Data source table structure control configuration of database
The content of Data source table structure control is：Whether the table structure in monitoring data source changes, including new field,
Delete field, modification field type, modification field length；These changes will cause data syn-chronization program to report an error, or represent business
Change；
The content for needing monitoring to configure includes the type of database, database connected mode, database table name, corresponding data
Library name；Monitoring programme will check table structural information into associated databases according to these contents；And industry corresponding to what is preserved before
Business database information is compared, if it find that there is variation, designated person is arrived in system hair alarm；
1-3) the literary name section value monitoring configuration of data source
The content of Data source table field value monitoring is the value of monitoring data source table emphasis field, including dimension table takes
Value, these values change, and change occurs in the business of representing；
The content for needing to configure includes database table name, field name, data type；Monitoring programme will be according to these contents
The field value of correlation is checked into data warehouse；And compared with the corresponding field value information preserved before, if
It was found that there is variation, designated person is arrived in system hair alarm；
2) deployment services have a high regard for business, and timing performs monitoring programme
Because data warehouse data is all updating daily, corresponding monitoring programme also performs daily；According to daily data
The run time in warehouse determines the execution time monitored daily；
Taken when realizing timing monitor, if monitor task is not fully completed, monitoring programme by dormancy for a period of time
Continue executing with；
3) monitored results formation report is sent to designated person；Monitoring is performed to the end, it is necessary to different by what is checked
Reason condition informs responsible person concerned, and alarm mode includes form, mail, short message；
Also include alarm personnel depaly in monitoring configuration, the content of configuration includes personnel's name, mobile phone, mailbox；By difference
Table divided according to project, responsible person corresponding to each project configuration；The exception found in system operation will be according to right
Related personnel is issued in the configuration answered；
In step 3), alarm mode is selected according to the rank of alarm；High severity alarm must be led in time by short message mode
Know attendant, alerted without the alarm of the common grade solved at once using lettergram mode；
Data warehouse monitoring table is monitored by accessing the log sheet after statistics.
Step 1-1) described in ratio take 50%.
Also a set of corresponding reporting system simultaneously, facilitates monitoring personnel to check history alarm logging.
Beneficial effects of the present invention：
1st, it can understand whether the implementation status for grasping daily data warehouse scheduler routine is normal；
2nd, abnormal conditions in data processing, the method being combined by system early warning and artificial treatment are handled,
Ensure reliable and stable in data handling procedure；
3rd, comprehensive system monitors, and improves the availability of system, also improves the efficiency of system maintenance；
4th, to database overall monitor, multiple monitoring demands can be completed by the way that hardware and software platform is unified, avoid overlapping development,
Reduce cost.
Brief description of the drawings
A kind of method process chart of the data warehouse data monitoring of Fig. 1 the present embodiment.
Embodiment
Such as Fig. 1, a kind of method handling process of data warehouse data monitoring of the present embodiment, including：
Step 11, determine the information such as data warehouse table, Data source table and data sheet field to be monitored.
Wherein, the basic principle of amount of configuration data monitoring table is that to want the table of data warehouse monitoring table be all important
Table.More than 80% index of Analysis of Data Warehouse should be able to be covered.The daily data volume for monitoring table simultaneously should not be excessive, no
It is too detailed, and the table after monitoring statisticss should be selected.Such as the detailed access log table in website, millions of are had daily
Data, and table of the access log table monitored by this data warehouse after statistics is monitored, the derivative table after statistics can
Can only hundreds of, the efficiency of monitoring programme can be ensured by so monitoring this derivative table, and can also meet the need of monitoring
Ask.
When disposition data source monitors, each table in synchrodata source is configured, it is ensured that by factor data source
Variation caused by influence control to minimum.
In configuration data literary name section, to determine which field needs to monitor according to business.Generally require monitoring is
The dimension field of main business table.Such as register member's type, member's state these.These changes may all represent business
It was found that adjustment.Certain influence can be very likely produced to data statistics.
The information of step 12, analysis table to be monitored, saved it according to the requirement of allocation list in monitoring allocation list.Because
System includes three monitoring functions, it is therefore desirable to configures three allocation lists.Detailed configuration content is introduced in the following step.
Step 13, according to configuration information, monitor table data volume and whether trend normal
Such as the daily newly-increased data volume of registered members' table is monitored, it is necessary to which the information of configuration is as follows
1) project name：Data warehouse underlying table (monitoring of data warehouse data amount)
2) table name：USERS
3) time field：TRUNC(ADD_TIME)
4) time field type：DATE.In the system, 3 kinds of date type point：DATE, NUMBER (such as 20150101),
ALL (specified time is not All Time scope for representative).
5) statistical：COUNT(*).The number of data or SUM (USER_COUNT) in time range are represented,
The statistics summation of i.e. a certain row.
6) statistical condition：Nothing.Limitation can be added according to project demand, for example require that monitoring User Status (STATUS) is
By auditing the member of (value 1), configuration information is STATUS=1 here.
7) period is monitored：6.Here 6 represent hour, represent to monitor after 6 points, are usually worth according to historical experience
Arrive.If system time is not carried out the monitoring of this table less than 6 points.Monitoring data to 6 points has exception, it is also possible to is
Because data are also not carried out, next monitoring period also may proceed to be monitored.Until data are normal or attendant is manual
Adjust abnormality.
By above-mentioned configuration, system can go out query statement (being performed by monitoring degree) in monitoring with automatic Mosaic, as follows：
SELECT COUNT (*) FROM USERS WHERE TRUNC (ADD_TIME)=DATE ' 2015-01-01 '
Monitoring programme can perform this query statement in database, and implementing result is saved in result table, for alerting journey
Sequence is analyzed in next step.If it find that data are 0 in analysis, then exception can be judged as.And short message and mail notification correspond to project
Director.And if if contrast yesterday data and last week simultaneously data data fluctuations all more than 50%, then be also likely to be
It is problematic, it can also mail to project leader.Project leader combines business and judges whether exception again.
Step 14, whether changed according to configuration information, monitoring data source table structure.
The table by the business library synchronization that data warehouse is related to is needed all to be stored in allocation list before monitoring.Need to match somebody with somebody
The information put includes：Service Database table name, former or history corresponding data warehouse table name, table owning user name, business
The information such as database connected mode, Service Database type.
By above-mentioned configuration, system can go in specified database to extract the literary name segment information of data source in monitoring.Such as
Fruit can obtain literary name segment information in oracle database using this system view of dba_tab_columns, if
Literary name segment information can be obtained using information_schema.columns in Mysql databases, other databases
It is the same.Obtained information is contrasted with the historical information preserved before, it is possible to recognize Service Database
Which changes.If synchrodata is possible to have an impact it is necessary to adjust the structure of the corresponding table of data warehouse in time.
Step 15, according to configuration information, monitor and specify whether the value of literary name section has increase.
Need the information of specified table being all stored in allocation list before monitoring.The information for needing to configure includes：Table name,
The information such as field name, field type, time field, time type.
By above-mentioned configuration, system goes the corresponding field of data warehouse not repeated in newly-increased extracting data in monitoring
Information and value, preserve, and contrasted with historical data before, may have change if increase represents business
Change, it is necessary to be alerted.
Step 16, generation report, occur warning information to designated person.
The exception being found and non-conformance description can be output in monitoring table by monitoring programme in the process of implementation.According to
Confidence ceases and the rank of alarm selects alarm mode.High severity alarm needs to notify attendant in time by short message mode,
The alarm of common grade does not have to the use lettergram mode alarm solved at once.
Again while also a set of corresponding reporting system, facilitates monitoring personnel to check history alarm logging.
The embodiment of patent of the present invention is the foregoing is only, is not intended to limit the invention patent, it is all in the present invention
All any modification, equivalent and improvement done within the spirit and principle of patent etc., with included in the guarantor of patent of the present invention
Within the scope of shield.
Claims (2)
- A kind of 1. method of data warehouse data monitoring, it is characterized in that comprising the following steps：1）It is determined that needing the monitoring table and content of data warehouse being monitored, parameter configuration is carried out, monitoring table parameter configuration Three kinds of content point；1-1）Data volume monitoring configurationThe content of data volume monitoring is whether the data volume increased newly on the same day that monitoring table is listed is abnormal, judges abnormal standard first It is whether data volume is 0, is so exactly abnormal if 0；Next to that the historical data with yesterday and last week on the same day is contrasted, If relatively differing by more than certain proportion or threshold value with historical data, it is considered as exception；The content for needing monitoring to configure includes data name, table name, time field, time field type, statistical item, statistics bar Part, monitoring period；The content of monitoring configuration as needed is spliced into corresponding SQL statement by monitoring programme, treats scheduler program Perform；1-2）The Data source table structure control configuration of databaseThe content of Data source table structure control is：Whether the table structure in monitoring data source changes, including new field, deletion Field, modification field type, modification field length；These changes will cause data syn-chronization program to report an error, or represent business Change；The content for needing monitoring to configure includes the type of database, database connected mode, database table name, correspondence database Name；Monitoring programme will check table structural information into associated databases according to these contents；And the corresponding service with preserving before Database information is compared, if it find that there is variation, designated person is arrived in system hair alarm；1-3）The literary name section value monitoring configuration of data sourceThe content of Data source table field value monitoring is the value of monitoring data source table emphasis field, includes the value of dimension table, These values change, and change occurs in the business of representing；The content for needing to configure includes database table name, field name, data type；Monitoring programme will be according to these contents to number According to the field value that correlation is checked in warehouse；And compared with the corresponding field value information preserved before, if it find that There is variation, designated person is arrived in system hair alarm；2）Deployment services have a high regard for business, and timing performs monitoring programmeBecause data warehouse data is all updating daily, corresponding monitoring programme also performs daily；According to daily data warehouse Run time determine the execution time monitored daily；Timing is taken to monitor when realizing, if monitor task is not fully completed, monitoring programme continues dormancy for a period of time Perform；3）Monitored results formation report is sent to designated person；Monitoring is performed to the end, it is necessary to the abnormal conditions that will be checked Responsible person concerned is informed, alarm mode includes form, mail, short message；Also include alarm personnel depaly in monitoring configuration, the content of configuration includes personnel's name, mobile phone, mailbox；By different tables Divided according to project, responsible person corresponding to each project configuration；The exception found in system operation will be according to corresponding Related personnel is issued in configuration；Step 3）In, alarm mode is selected according to the rank of alarm；High severity alarm must be notified in time by short message mode Attendant, alerted without the alarm of the common grade solved at once using lettergram mode；Data warehouse monitoring table is monitored by accessing the log sheet after statistics.
- 2. the method for data warehouse data monitoring according to claim 1, it is characterized in that step 1-1）Described in ratio Take 50%.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510502221.3A CN105095056B (en) | 2015-08-14 | 2015-08-14 | A kind of method of data warehouse data monitoring |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510502221.3A CN105095056B (en) | 2015-08-14 | 2015-08-14 | A kind of method of data warehouse data monitoring |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN105095056A CN105095056A (en) | 2015-11-25 |
| CN105095056B true CN105095056B (en) | 2018-01-12 |
Family
ID=54575552
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201510502221.3A Active CN105095056B (en) | 2015-08-14 | 2015-08-14 | A kind of method of data warehouse data monitoring |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN105095056B (en) |
Families Citing this family (23)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106919602A (en) * | 2015-12-25 | 2017-07-04 | 阿里巴巴集团控股有限公司 | A kind of data monitoring management method, data monitoring method and system |
| CN105677567A (en) * | 2016-01-10 | 2016-06-15 | 上海与德通讯技术有限公司 | Automation testing method and system |
| CN107193821B (en) * | 2016-03-14 | 2021-07-13 | 创新先进技术有限公司 | Monitoring method and system |
| CN108304413A (en) * | 2017-01-13 | 2018-07-20 | 北京京东尚科信息技术有限公司 | distributed data warehouse monitoring method, device, electronic equipment and storage medium |
| CN107220301A (en) * | 2017-05-10 | 2017-09-29 | 北京小度信息科技有限公司 | The data monitoring method and device of a kind of configurableization |
| CN109218051B (en) * | 2017-07-03 | 2022-04-01 | 中国移动通信有限公司研究院 | Method for managing terminal of internet of things, management server, terminal of internet of things and system |
| CN108090138A (en) * | 2017-11-29 | 2018-05-29 | 链家网（北京）科技有限公司 | The monitoring method and system of a kind of data warehouse |
| CN108415814B (en) * | 2018-01-11 | 2021-02-19 | 平安科技（深圳）有限公司 | Method for automatically recording field change, application server and computer readable storage medium |
| CN108874644B (en) * | 2018-06-06 | 2021-12-24 | 平安科技（深圳）有限公司 | Data monitoring method and device, computer equipment and storage medium |
| CN108959564B (en) * | 2018-07-04 | 2020-11-27 | 玖富金科控股集团有限责任公司 | Data warehouse metadata management method, readable storage medium and computer device |
| CN109857720B (en) * | 2018-12-20 | 2024-02-02 | 中国平安人寿保险股份有限公司 | Database table monitoring method, device, computer device and readable storage medium |
| CN109800229B (en) * | 2018-12-29 | 2021-10-08 | 深圳云天励飞技术有限公司 | Data access method and related equipment |
| CN109857619A (en) * | 2019-02-03 | 2019-06-07 | 北京字节跳动网络技术有限公司 | State subscription method, apparatus, storage medium and the electronic equipment of data warehouse table |
| CN109918271A (en) * | 2019-03-28 | 2019-06-21 | 上海中通吉网络技术有限公司 | Data quality monitoring method, system and storage medium |
| CN110134680B (en) * | 2019-04-04 | 2022-11-29 | 平安科技（深圳）有限公司 | Space monitoring method and device, computer equipment and storage medium |
| CN110399376A (en) * | 2019-08-08 | 2019-11-01 | 北京明略软件系统有限公司 | The method and device of the former data variation of automatic identification table |
| CN110647452B (en) * | 2019-08-30 | 2023-02-07 | 深圳壹账通智能科技有限公司 | Test method, test device, computer equipment and storage medium |
| CN110851325B (en) * | 2019-11-08 | 2024-03-15 | 土巴兔集团股份有限公司 | Method, device and equipment for monitoring data warehouse based on Hive table |
| CN110837458B (en) * | 2019-11-08 | 2024-03-29 | 土巴兔集团股份有限公司 | Method, equipment and storage medium for data balance verification |
| CN113836160B (en) * | 2021-09-28 | 2024-01-23 | 上海市大数据股份有限公司 | Data stream state monitoring alarm system based on master-slave synchronization |
| CN116361391A (en) * | 2023-03-30 | 2023-06-30 | 中电云数智科技有限公司 | Method and device for detecting and repairing structural abnormality of data synchronization table |
| CN116149969B (en) * | 2023-04-04 | 2023-06-20 | 湖南中青能科技有限公司 | Database model matching anomaly monitoring and processing method |
| CN116820892A (en) * | 2023-07-14 | 2023-09-29 | 佛山众陶联供应链服务有限公司 | Data processing monitoring system for a plurality of bins |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101989278A (en) * | 2009-08-06 | 2011-03-23 | 上海杉达学院 | Database inquiry device with automatic splicing function |
| CN102508833A (en) * | 2011-09-22 | 2012-06-20 | 用友软件股份有限公司 | Data monitoring device and data monitoring method |
| CN102855319A (en) * | 2012-09-05 | 2013-01-02 | 国家电网公司 | ORACLE database operation monitoring system |
| CN103036736A (en) * | 2012-11-30 | 2013-04-10 | 航天恒星科技有限公司 | Configuration equipment monitoring system and monitoring method based on data sources |
| CN104636483A (en) * | 2015-02-16 | 2015-05-20 | 广东省公安厅 | Data monitoring method |
| CN104636450A (en) * | 2015-01-26 | 2015-05-20 | 上海新炬网络信息技术有限公司 | Database table space monitoring method |
-
2015
- 2015-08-14 CN CN201510502221.3A patent/CN105095056B/en active Active
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101989278A (en) * | 2009-08-06 | 2011-03-23 | 上海杉达学院 | Database inquiry device with automatic splicing function |
| CN102508833A (en) * | 2011-09-22 | 2012-06-20 | 用友软件股份有限公司 | Data monitoring device and data monitoring method |
| CN102855319A (en) * | 2012-09-05 | 2013-01-02 | 国家电网公司 | ORACLE database operation monitoring system |
| CN103036736A (en) * | 2012-11-30 | 2013-04-10 | 航天恒星科技有限公司 | Configuration equipment monitoring system and monitoring method based on data sources |
| CN104636450A (en) * | 2015-01-26 | 2015-05-20 | 上海新炬网络信息技术有限公司 | Database table space monitoring method |
| CN104636483A (en) * | 2015-02-16 | 2015-05-20 | 广东省公安厅 | Data monitoring method |
Non-Patent Citations (1)
| Title |
|---|
| 数据仓库增量维护的体系结构研究;赵清;《中国优秀硕士论文全文数据库 信息科技辑》;20070815;第2007年卷(第02期);正文6页第3段、7页第1段、8页第3段、12页第3段、18页第3段 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN105095056A (en) | 2015-11-25 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN105095056B (en) | A kind of method of data warehouse data monitoring | |
| CN111049705B (en) | Method and device for monitoring distributed storage system | |
| US10963330B2 (en) | Correlating failures with performance in application telemetry data | |
| CN107678907B (en) | Database service logic monitoring method, system and storage medium | |
| US10592308B2 (en) | Aggregation based event identification | |
| JP4458493B2 (en) | Log notification condition definition support apparatus, log monitoring system, program, and log notification condition definition support method | |
| US9189543B2 (en) | Predicting service request breaches | |
| US8510602B2 (en) | Testing a software application used in a database system | |
| CN107302469B (en) | Monitoring device and method for data update of distributed service cluster system | |
| CN103401698A (en) | Monitoring system used for alarming server status in server cluster operation | |
| CN108509313A (en) | A kind of business monitoring method, platform and storage medium | |
| CN109344189A (en) | Big data calculation method and device based on NiFi | |
| CN105653362A (en) | Method and equipment for managing timed tasks | |
| CN106951360B (en) | Data statistical integrity calculation method and system | |
| CN110471822A (en) | Method, apparatus, computer system and medium for monitoring host computer system | |
| CN114443437A (en) | Alarm root cause output method, apparatus, device, medium, and program product | |
| CN108255661A (en) | A kind of method and system for realizing Hadoop cluster monitorings | |
| CN102930690B (en) | Alarm processor and alarm processing method | |
| US20160080305A1 (en) | Identifying log messages | |
| CN111737233A (en) | Data monitoring method and device | |
| CN116545867A (en) | Method and device for monitoring abnormal performance index of network element of communication network | |
| CN114338435B (en) | Network change monitoring method, device, computer equipment and storage medium | |
| CN110866037B (en) | Message filtering method and device | |
| CN113835916A (en) | Ambari big data platform-based alarm method, system and equipment | |
| CN108880903B (en) | Data stream monitoring method, system, device and computer readable storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |