A method and system for verifying the quality of various platform data with metadata

Abstract

The present invention discloses a method and system for verifying the quality of various platform data with metadata, which relates to the technical field of data processing and includes an oracle database, a temporary storage unit, a metadata management platform, and a comparison unit. The oracle database, the temporary storage unit, the metadata management platform, and the comparison unit are electrically connected. The metadata management platform is used to configure data sources for metadata pages or write sql. The oracle database is used to collect the processed data volumes of oracle - sql, hive - sql, and mongo - sql. The temporary storage unit is used to temporarily store the data volumes processed by hive - sql and mongo - sql, and then calculate the sql and send it to the oracle database. The comparison unit is used to compare the data values in the database and various amounts, and send the result to the enterprise WeChat after page display. The present invention can use presto as a data query engine, which improves the ad - hoc query efficiency and can parse sql into corresponding mongodb query statements.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing. Specifically, it relates to a method and system for verifying the quality of various platform data with metadata. Background Art

[0002] To be compatible with historical script processing, historical script data is modified and the method of directly sending email notifications is adopted. However, as business data grows and requirements become more complex, requirements that cannot be solved by a simple relational database will synchronize the tables of the relational database to other platforms for processing. At this time, there are relatively high requirements for the data quality of each platform, and it is necessary to actively obtain whether the data volume and amount of each table on the current day or T-1 day are consistent regularly or irregularly every day, monitor the status of data on each platform in a timely manner, and quickly process data problems. Summary of the Invention

[0003] In order to overcome the deficiencies of the prior art, a method and system for verifying the quality of various platform data with metadata according to the present invention can use Presto as a data query engine to improve query efficiency.

[0004] The technical solution adopted by the present invention to solve its technical problems is: a method for verifying the quality of various platform data with metadata, the improvement lies in, including:

[0005] S1: Configure the data source or write SQL on the metadata management platform page;

[0006] S2: Process the data volume or amount value of T-1 day simultaneously with multiple threads;

[0007] S3: Query the data in the oracle database, perform logical comparison processing, and display it on the html page;

[0008] S4: Push the compared html page to the enterprise WeChat.

[0009] As an improvement of the above technical solution, in step S1, the configured content includes the user, password, library table, and business date of the database.

[0010] As a further improvement of the above technical solution, in step S2, multiple data platforms are used to process the data volume or amount value of T-1 day simultaneously with multiple threads.

[0011] As a further improvement of the above technical solution, the data volume includes the capital data volume and the lowercase data volume.

[0012] As a further improvement of the above technical solution, the multiple data platforms include oracle-sql, hive-sql, and mongo-sql;

[0013] The uppercase and lowercase data volumes of the oracle-sql are directly stored in the oracle database;

[0014] The hive-sql processes the lowercase data volume;

[0015] The mongo-sql processes the uppercase data volume.

[0016] As a further improvement of the above technical solution, the lowercase data volume of the hive-sql is stored in a temporary storage unit after escaping the partition field.

[0017] As a further improvement of the above technical solution, the lowercase data volume of the hive-sql is first processed by presto and then stored in a temporary storage unit.

[0018] As a further improvement of the above technical solution, the uppercase data volume of the mongo-sql is stored in a temporary storage unit after escaping the partition field.

[0019] As a further improvement of the above technical solution, the uppercase data volume of the mongo-sql is first modified in the source code of presto through mongodb and then stored in a temporary storage unit.

[0020] As a further improvement of the above technical solution, the data volume stored in the temporary storage unit is stored in the oracle database after calculating the sql.

[0021] A system for verifying the data quality of each platform's metadata, the improvement lies in including an oracle database, a temporary storage unit, a metadata management platform, and a comparison unit, and the oracle database, the temporary storage unit, the metadata management platform, and the comparison unit are electrically connected;

[0022] The metadata management platform is used to configure data sources or write sql on the metadata page. The oracle database is used to collect the processed data volumes of oracle-sql, hive-sql, and mongo-sql. The temporary storage unit is used to temporarily store the data volumes processed by hive-sql and mongo-sql, and then send them to the oracle database after calculating the sql. The comparison unit is used to compare the data values and various amounts in the database, and send them to the enterprise wechat after page display.

[0023] The beneficial effects of the present invention are:

[0024] 1. Optimize the management of various important metadata;

[0025] 2. Task notifications are configurable and can be managed centrally, eliminating the need to write excessive redundant scripts.

[0026] 3. Send enterprise WeChat notifications to reduce response time.

[0027] 4. Use Presto as the data query engine to improve the efficiency of ad-hoc queries and parse SQL into corresponding MongoDB query statements. Description of the Drawings

[0028] Figure 1 This is the structural framework of the present invention. Detailed Embodiments

[0029] The present invention will be further described below in conjunction with the drawings and embodiments.

[0030] The concept, specific structure, and technical effects of the present invention will be clearly and completely described below in conjunction with the embodiments and drawings to fully understand the purpose, features, and effects of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, not all of them. Based on the embodiments of the present invention, other embodiments obtained by those skilled in the art without creative efforts shall fall within the scope of protection of the present invention. In addition, all the connection / linkage relationships involved in the patent do not simply refer to the direct connection of components, but refer to the formation of a more optimal connection structure by adding or reducing connection accessories according to specific implementation situations. The various technical features in the present invention can be combined with each other without conflict.

[0031] Since all data is written starting from Oracle, the data volume and amount aggregation of all other clusters are based on Oracle. Because there is a certain timeliness in the comparison, the comparison amount difference within 1 yuan is accurate. Any difference in the number of data entries is considered abnormal and requires manual intervention for investigation and handling.

[0032] The SQL used in the present invention is the abbreviation of Structured Query Language, which is a special-purpose programming language, a database query and programming language used for accessing data and querying, updating, and managing relational database systems.

[0033] Reference Figure 1 , the present invention discloses a method for verifying the quality of various platform data by metadata, including:

[0034] S1: Configure the data source on the metadata management platform page or write SQL;

[0035] S2: Process the data volume or amount value of T-1 days simultaneously in multiple threads;

[0036] S3: Query the data in the oracle database, perform logical comparison processing, and then display it on the html page;

[0037] S4: Push the compared html page to the enterprise WeChat.

[0038] In the above embodiment, in step S1, the configured content includes the user, password, database tables, and business date of the database. In step S2, multiple data platforms are used to process the data volume or amount value of T-1 days simultaneously in multiple threads. The metadata management platform configures the data sources of each cluster, including the user, password, database tables, and business date of the database to be configured, etc. Set the metadata information of each table required according to the requirements. The program queries the data in the oracle metadata table, performs logical comparison and various requirement processing in the program, and displays it on the page. Then, the compared html page is sent to the enterprise WeChat. There are unified start and disable notifications for a certain task on the page. At the same time, it is also set to start the task manually by clicking. The task notifications are configurable and can be managed uniformly without writing too many redundant scripts, which is convenient for business personnel to understand the current data status at any time, view and adjust various information and indicators, and handle problems in a timely manner.

[0039] Furthermore, the data volume includes uppercase data volume and lowercase data volume. The multiple data platforms include oracle-sql, hive-sql, and mongo-sql; the uppercase data volume and lowercase data volume of oracle-sql are directly stored in the oracle database; hive-sql processes the lowercase data volume, and the lowercase data volume is stored in the temporary storage unit after escaping the partition field; mongo-sql processes the uppercase data volume and is stored in the temporary storage unit after escaping the partition field.

[0040] In the above embodiments, the small - case data volume of the hive - sql in the present invention first passes through presto and then is stored in the temporary storage unit. The large - case data volume of the mongo - sql first passes through mongodb after modifying the presto source code and then is stored in the temporary storage unit. Because using only hive - sql is very slow, presto and mongodb are introduced. Presto is a distributed SQL query engine open - sourced by Facebook, suitable for interactive analytical queries. Presto has a clear architecture and is an independent running system that does not depend on any other external systems. Presto itself provides cluster monitoring, can complete scheduling based on monitoring information, and has rich plugin interfaces, perfectly docking with external storage systems or adding custom functions. Mongodb is a database based on distributed file storage. Since it does not support sql engine queries and presto only supports lowercase databases and tables, the presto source code is modified to have a version that only supports uppercase.

[0041] In addition, the data volume stored in the temporary storage unit is stored in the oracle database after calculating sql. Among them, using sql for calculation is a mature technical means, and the present invention will not repeat it.

[0042] A system for the metadata to check the data quality of each platform includes an oracle database, a temporary storage unit, a metadata management platform, and a comparison unit. The oracle database, the temporary storage unit, the metadata management platform, and the comparison unit are electrically connected;

[0043] The metadata management platform is used to configure data sources on the metadata page or write sql. The oracle database is used to collect various processed data volumes of oracle - sql, hive - sql, and mongo - sql. The temporary storage unit is used to temporarily store the data volumes processed by hive - sql and mongo - sql, and then send them to the oracle database after calculating sql. The comparison unit is used to compare the data values and various amounts in the database and send them to the enterprise WeChat after page display.

[0044] The metadata management platform of the present invention configures data sources or writes SQL on the page, and then processes the data volume or amount value of T-1 day simultaneously in multiple threads (using oracle-sql, hive-sql, mongo-sql), queries the data in the oracle database, and after performing logical comparison processing in the comparison unit, displays it on the html page. The compared html page is pushed to the enterprise WeChat. There are unified start and disable notifications for a certain task on the page. At the same time, it is also set to actively click manually to start the task. The task notifications are configurable and can be managed uniformly, without writing too many redundant scripts, which is convenient for business personnel to understand the current data status at any time, view and adjust various information and indicators, and handle problems in a timely manner.

[0045] The beneficial effects of the present invention are:

[0046] 1. Optimize the management of various important metadata;

[0047] 2. The task notifications are configurable and can be managed uniformly, without writing too many redundant scripts;

[0048] 3. Send enterprise WeChat notifications to reduce the response time;

[0049] 4. Use presto as the data query engine, which improves the ad-hoc query efficiency and can parse SQL into corresponding mongodb query statements.

[0050] The above is a specific description of the preferred embodiment of the present invention. However, the present invention is not limited to the described embodiment. Those skilled in the art can make various equivalent deformations or substitutions without departing from the spirit of the present invention, and these equivalent deformations or substitutions are all included in the scope defined by the claims of this application.

Claims

1. A method for checking the quality of various platform data with metadata, characterized in that, Including: S1: Configure the data source or write SQL on the metadata management platform page; S2: Use multiple data platform threads to process the data volume or amount value of T-1 days simultaneously. The data volume includes uppercase data volume and lowercase data volume. The multiple data platforms include oracle-sql, hive-sql, and mongo-sql. The uppercase data volume and lowercase data volume of oracle-sql are directly stored in the oracle database. The hive-sql processes the lowercase data volume. The mongo-sql processes the uppercase data volume; S3: Query the data in the oracle database, perform logical comparison processing, and then display it on the html page; S4: Push the compared html page to enterprise WeChat.

2. The method for checking the quality of each platform data based on the metadata according to claim 1, wherein In step S1, the configured content includes the user, password, database tables, and business date of the database.

3. A method for checking the quality of various platform data according to the metadata as claimed in claim 1, characterized in that The lowercase data volume of the hive-sql is stored in the temporary storage unit after escaping the partition field.

4. A method for checking the quality of each platform data according to the metadata as claimed in claim 3, characterized in that, The lowercase data volume of the hive-sql is first processed by presto and then stored in the temporary storage unit.

5. A method for checking the quality of each platform data according to the metadata as claimed in claim 1, wherein, The uppercase data volume of the mongo-sql is stored in the temporary storage unit after escaping the partition field.

6. A method for checking the quality of each platform data according to the metadata as claimed in claim 5, characterized in that, The uppercase data volume of the mongo-sql is first modified with the presto source code by mongodb and then stored in the temporary storage unit.

7. A method for checking the quality of various platform data according to the metadata as claimed in claim 4 or 6, characterized in that, The data volume stored in the temporary storage unit is stored in the oracle database after calculating the SQL.

8. A system for verifying the quality of various platform data with metadata, characterized in that, Including an oracle database, a temporary storage unit, a metadata management platform, and a comparison unit. The oracle database, the temporary storage unit, the metadata management platform, and the comparison unit are electrically connected; The metadata management platform is used to configure the data source or write SQL on the metadata page. The oracle database is used to collect the processed data volumes of oracle-sql, hive-sql, and mongo-sql. The temporary storage unit is used to temporarily store the data volumes processed by hive-sql and mongo-sql, and then send them to the oracle database after calculating the SQL. The comparison unit is used to compare the data values and amounts in the database, and send them to enterprise WeChat after page display. The data volume includes uppercase data volume and lowercase data volume. The uppercase data volume and lowercase data volume of oracle-sql are directly stored in the oracle database. The hive-sql processes the lowercase data volume. The mongo-sql processes the uppercase data volume.

Images