ODS and data lake docking system table structure change monitoring method

Abstract

The invention discloses an ODS and data lake docking system table structure change monitoring method. The monitoring method comprises the steps that an xml script for generating DDL is developed; developing a data receiving verification script; a DDL verification script is developed; and clearing the log after online. And the controllability of the online link when a large number of meters are online, the system is online for the first time or the meter range is expanded is improved.

Description

Technical Field

[0001] The present invention relates to the field of database and big data technology, and in particular to a method for monitoring table structure changes of an ODS and data lake docking system. Background Art

[0002] In today's data-driven era, database and big data technologies continue to develop, and the importance of data is becoming increasingly prominent. ODS (data exchange platform) and data lake, as key data storage and transmission components, play a vital role in the information architecture of enterprises. During the entire data processing process of the enterprise, data needs to be provided to multiple downstream systems to support various business applications and decision-making analysis of the enterprise.

[0003] Since ODS (data exchange platform) and data lake are important hubs for enterprise data processing, one interface usually needs to connect to multiple downstream systems. When the table structure of the upstream source system changes to better support the business system, it is easy for the notification to be delayed or missed when notifying the downstream system of the synchronous change; and although the interfaces used by the downstream systems are the same, the usage indicators are not exactly the same, which will lead to problems such as data docking errors and crossovers during the data processing process. This makes the downstream system face a high risk of production failure during the online change process, and after the production fails, it can only rely on manual investigation of the specific reasons for the production failure. This not only affects the success rate of the project commissioning, but also increases the labor cost and manual operation risks.

[0004] Based on the above problems, it is mainly because the ODS (data exchange platform) and the data lake and downstream systems are highly dependent on the timeliness and accuracy of communication during the system table structure change process. In order to improve the one-time success rate of the online changes of each downstream system and more effectively reduce the risk of manual operation by personnel, it is necessary to realize highly automated monitoring of the table structure change tasks in the data processing process, so as to better improve the degree of automation of the online changes of the downstream systems and effectively avoid the failure of online production due to communication problems and manual operation problems.

[0005] At present, the main implementation solutions for the upstream system transformation that requires table structure changes are:

[0006] 1. Task confirmation stage: mainly relies on manual email notification or metadata management system for task confirmation.

[0007] 2. Change implementation phase: mainly relies on the interface information documents manually provided by the upstream, and each downstream system manually confirms and manually modifies the online content of each downstream system.

[0008] 3. Change verification phase: mainly depends on whether the issued data file can be stored normally and manually check the specific cause of failure after the error is reported.

[0009] At present, when a large number of tables are online, the system is online for the first time, or the scope of tables is expanded, the controllability of this link in the current online plan is low:

[0010] All relevant constraints rely entirely on offline manual confirmation, and the method of comparing tables one by one is extremely inefficient. This not only consumes a lot of manpower costs, but also seriously hinders the progress of going online, so that the communication and confirmation link has become a pain point for various downstream systems.

[0011] Manual notifications are often missed and untimely, making it impossible for downstream systems to make timely adjustments, thereby increasing the risk of launch failure.

[0012] Different downstream systems use the same interface but different indicators, which can easily lead to problems such as incorrect docking and cross-connection. Summary of the invention

[0013] In view of the above problems, the present invention is proposed to provide a method for monitoring table structure changes in an ODS and data lake docking system that overcomes the above problems or at least partially solves the above problems.

[0014] According to one aspect of the present invention, a method for monitoring table structure changes of an ODS and data lake docking system is provided, and the monitoring method comprises:

[0015] Develop XML scripts to generate DDL;

[0016] Develop data receiving verification script;

[0017] Develop DDL verification scripts;

[0018] Clean up the logs after going online.

[0019] Optionally, the XML script for developing and generating DDL specifically includes:

[0020] The operation premise is to wait for the upstream system data to be sent;

[0021] Deployment location, deploy the generate_xml.sh script on the etl server of this system;

[0022] Implementation method: Use shell language to implement;

[0023] Calling parameters, program name, ODS data delivery storage path, local table creation statement script name;

[0024] Generate results. By executing the script, two configuration files are generated.

[0025] Optionally, the configuration file specifically includes: ods_fig_date.xml and local_fig_date.xml, including key information of field sequence, table name, number of fields and field length.

[0026] Optionally, the development data reception verification script specifically includes:

[0027] Operation prerequisite: deploy the tab_list_check.sh script on the ETL server of this system;

[0028] Implementation method: using shell language;

[0029] The calling parameters include program name, path / ods_fig_date.xml, path / local_fig_date.xml;

[0030] Generate results. After executing the script, generate a verification result log. If there is no error information in the log, continue with the subsequent steps.

[0031] Optionally, the development DDL verification script specifically includes:

[0032] Operation prerequisite: deploy the loading_check.sh script on the ETL server of this system;

[0033] Implementation method: using shell language;

[0034] Calling parameters: program name, path / ods_fig_date.xml, path / local_fig_date.xml;

[0035] Generate results. After executing the script, a verification result log is generated. If there is no error information in the log, you can continue the online operation.

[0036] Optionally, the post-online log cleaning specifically includes:

[0037] Operation content: After going online, you can clear the loading_log.log log content.

[0038] The present invention provides a method for monitoring table structure changes in an ODS and data lake docking system, the monitoring method comprising: developing an XML script for generating DDL; developing a data receiving verification script; developing a DDL verification script; and cleaning up logs after going online. This method improves the controllability of the online link when a large number of tables are online, the system is online for the first time, or the table range is expanded.

[0039] The above description is only an overview of the technical solution of the present invention. In order to more clearly understand the technical means of the present invention, it can be implemented according to the contents of the specification. In order to make the above and other purposes, features and advantages of the present invention more obvious and easy to understand, the specific implementation methods of the present invention are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative work.

[0041] Figure 1 A flow chart of a source system table structure change production task provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0042] The exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0043] The terms "comprises" and "having" and any variations thereof in the description embodiments, claims and drawings of the present invention are intended to cover non-exclusive inclusions, for example, including a series of steps or units.

[0044] The technical solution of the present invention is further described in detail below in conjunction with the accompanying drawings and embodiments.

[0045] From the perspective of the downstream system (data user), the present invention proposes a method to ensure the successful data entry. Based on the existing tools and environment, the method can be completed by using shell scripts and deploying them on the etl server. The specific technical development method is as follows:

[0046] 1. Execute generate_xml.sh: This script is used to generate two XML files, namely the DDL of ODS and the DDL of this system.

[0047] 2. Execute tab_list_check.sh: This script is used to verify whether all received data files have been received and whether there is any data missing.

[0048] 3. Execute loading_check.sh: This script verifies whether the number of received data is consistent with the online table structure of this system, and whether the length of each field successfully loads the data into the database.

[0049] Implement DDL automatic verification solution before data import, including:

[0050] 1. Develop an XML script to generate DDL:

[0051] Operation prerequisite: You need to wait for the upstream system data to be sent down.

[0052] Deployment location: Deploy the generate_xml.sh script on the etl server of this system.

[0053] Implementation method: Use shell language.

[0054] Calling parameters: program name, ODS data storage path, and local table creation statement script name.

[0055] Output: By executing the script, two configuration files are generated, namely ods_fig_date.xml and local_fig_date.xml, which contain key information such as field sequence, table name, number of fields, and field length.

[0056] 2. Develop data receiving verification script:

[0057] Operation prerequisite: Deploy the tab_list_check.sh script on the ETL server of this system.

[0058] Implementation method: Implemented with the help of shell language.

[0059] Calling parameters: program name, path / ods_fig_date.xml, path / local_fig_date.xml.

[0060] Generation results: After executing the script, a verification result log is generated. If there is no error information in the log, you can continue with the subsequent steps.

[0061] 3. Develop DDL verification script:

[0062] Prerequisite: Also deploy the loading_check.sh script on the ETL server of this system.

[0063] Implementation method: Use shell language.

[0064] Calling parameters: program name, path / ods_fig_date.xml, path / local_fig_date.xml.

[0065] Generation results: After executing the script, a verification result log is generated. If there is no error information in the log, you can continue the online operation.

[0066] 4. Clean up logs after going online:

[0067] Operation content: After going online, you can clear the loading_log.log log content.

[0068] Beneficial effects: Improve the controllability of the online process when a large number of tables are online, the system is online for the first time, or the table range is expanded. The details are as follows:

[0069] Change the current situation where all relevant constraints rely on offline manual confirmation and table-by-table comparison, which is inefficient, reduce labor costs and speed up the online progress.

[0070] Eliminate omissions and untimely manual notifications, and ensure that downstream systems can be adjusted in a timely manner to reduce the risk of online failure.

[0071] Solve the problems of docking errors and cross-connections caused by different downstream systems using the same interface but different indicators.

[0072] The above specific implementation methods further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above are only specific implementation methods of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for monitoring table structure changes in an ODS and data lake docking system, characterized in that: The monitoring method comprises: Develop XML scripts to generate DDL; Develop data receiving verification script; Develop DDL verification scripts; Clean up the logs after going online.

2. According to claim 1, a method for monitoring table structure changes of an ODS and data lake docking system is characterized in that: The XML script for developing and generating DDL specifically includes: The operation premise is to wait for the upstream system data to be sent; Deployment location, deploy the generate_xml.sh script on the etl server of this system; Implementation method: Use shell language to implement; Calling parameters, program name, ODS data delivery storage path, local table creation statement script name; Generate results. By executing the script, two configuration files are generated.

3. A method for monitoring table structure changes of an ODS and data lake docking system according to claim 2, characterized in that: The configuration files specifically include: ods_fig_date.xml and local_fig_date.xml, including key information of field sequence, table name, number of fields and field length.

4. According to claim 1, a method for monitoring table structure changes of an ODS and data lake docking system is characterized in that: The development data receiving verification script specifically includes: Operation prerequisite: deploy the tab_list_check.sh script on the ETL server of this system; Implementation method: using shell language; The calling parameters include program name, path / ods_fig_date.xml, path / local_fig_date.xml; Generate results. After executing the script, generate a verification result log. If there is no error information in the log, continue with the subsequent steps.

5. According to claim 1, a method for monitoring table structure changes of an ODS and data lake docking system, characterized in that: The development of the DDL verification script specifically includes: Operation prerequisite: deploy the loading_check.sh script on the ETL server of this system; Implementation method: using shell language; Calling parameters: program name, path / ods_fig_date.xml, path / local_fig_date.xml; Generate results. After executing the script, a verification result log is generated. If there is no error information in the log, you can continue the online operation.

6. A method for monitoring table structure changes of an ODS and data lake docking system according to claim 1, characterized in that: The post-launch log cleaning specifically includes: Operation content: After going online, you can clear the loading_log.log log content.

Images