Data proofreading method, device, computer equipment and storage medium

Abstract

The present application provides a data proofreading method, device, computer equipment and storage medium. The method includes: obtaining the indicator caliber of multiple target indicators; inputting each indicator caliber into the NL2SQL model, obtaining the SQL statement corresponding to each target indicator, and parsing the SQL statement to obtain the rule information corresponding to each target indicator; based on each rule information, generating an event rule table; obtaining the database change log, and traversing the event rule table to determine whether the database change log has matching rule information in the event rule table; if so, calculating the target indicator on the business side according to the matched target rule and the database change log, and comparing the target indicator on the business side with the target indicator on the big data side; when the difference between the target indicator on the business side and the target indicator on the big data side exceeds the preset range, an alarm is issued. The method is extremely efficient, and there is no data intrusion, which makes the overall performance extremely high and does not affect the business itself at all.

Description

Technical Field

[0001] The present application relates to the technical field of data proofreading, and in particular to a data proofreading method, device, computer equipment and storage medium. Background Art

[0002] With the advent of the big data era, more and more companies are paying more and more attention to the importance of data value, and one of the most important prerequisites for data value is the accuracy of data. When a company grows to a certain scale, it will begin to build its data assets, especially the need to build a set of data indicator systems based on raw data. A data indicator system refers to a set of quantitative indicators used to measure, evaluate and monitor the performance of a business, project or system. The ultimate goal is to empower the business, drive the business and form a business closed loop. The accuracy of each indicator in the indicator system is the most basic and necessary guarantee for the later use of the indicator. Therefore, it is necessary to proofread the data indicators, but the traditional proofreading method has problems such as intrusion into the data source, high maintenance cost and low proofreading efficiency. Summary of the invention

[0003] The purpose of this application is to solve at least one of the above-mentioned technical defects, especially the problems existing in the prior art such as intrusion into data sources, high maintenance costs, and low proofreading efficiency.

[0004] In a first aspect, the present application provides a data proofreading method, comprising:

[0005] Obtain the indicator caliber of multiple target indicators;

[0006] Input each indicator into the NL2SQL model to obtain the SQL statement corresponding to each target indicator, and parse the SQL statement to obtain the rule information corresponding to each target indicator;

[0007] Based on each rule information, an event rule table is generated;

[0008] Get the database change log and traverse the event rule table to determine whether the database change log has matching rule information in the event rule table;

[0009] If yes, the target indicator on the business side is calculated based on the matched target rules and database change logs, and the target indicator on the business side is compared with the target indicator on the big data side;

[0010] When the difference between the target indicator on the business side and the target indicator on the big data side exceeds the preset range, an alarm is issued.

[0011] In one embodiment, obtaining the indicator calibers of multiple target indicators includes:

[0012] Read the metadata of the data indicator table on the big data side to obtain the caliber of each indicator.

[0013] In one embodiment, each SQL statement is parsed to obtain corresponding rule information, including:

[0014] After selecting keywords from the SQL statement, get the aggregation rules;

[0015] Get the source table name from the source keyword of the SQL statement;

[0016] Get the predicate condition from the condition keyword of the SQL statement;

[0017] Get rule information based on aggregation rules, source table name and predicate conditions.

[0018] In one embodiment, the metadata of the data indicator table on the big data side is read to obtain the caliber of each indicator, and further includes:

[0019] Record the name of the data indicator table corresponding to each indicator caliber; the data indicator table includes an online indicator table and an offline indicator table, and the names of the online indicator table and the offline indicator table have different category identifiers;

[0020] According to the aggregation rules, source table name and predicate conditions, the rule information is obtained, including:

[0021] In the rule information, add the name of the data indicator table corresponding to the target indicator.

[0022] In one embodiment, obtaining a database change log, obtaining a database change log, and traversing an event rule table include:

[0023] Execute the first-stream processing task to continuously obtain the database change log in real time, and determine the rule information whose name of the data indicator table in the event rule table is accompanied by the category identifier of the online indicator table as the rule to be matched, and match the rule to be matched with the obtained database change log;

[0024] Execute the first batch of processing tasks to batch obtain all database change logs within the preset time range when the batch processing conditions are met, and determine the rule information whose name of the data indicator table in the event rule table has the offline indicator table category identifier as the rule to be matched, and match the rule to be matched with the obtained database change log.

[0025] In one of the embodiments, the rule information also records the indicator value of the corresponding target indicator on the big data side, and the data verification method further includes:

[0026] Execute the second stream processing task to continuously obtain the database change log in real time, and when the name of the change table in the database change log is the same as the name of the data indicator table of any rule information, update the indicator value of the rule information according to the changed field in the database change log.

[0027] In one embodiment, the data verification method further includes:

[0028] Use the stream execution mode in FlinkCDC to execute the first task code to perform the first stream processing task;

[0029] The batch execution mode in FlinkCDC is used to execute the second task code to execute the first batch of processing tasks; wherein the first task code and the second task code can be reused after modifying the judgment condition of the to-be-matched rule.

[0030] In one embodiment, comparing the target indicator on the business side with the target indicator on the big data side includes:

[0031] Read the indicator value corresponding to the target rule and compare the read indicator value with the target indicator on the business side.

[0032] In one embodiment, matching the to-be-matched rule with the acquired database change log includes:

[0033] Compare the change table name in the database change log with the source information of each rule to be matched;

[0034] If the comparison result is the same, the to-be-matched rule corresponding to the source information is determined to be the target rule.

[0035] In one embodiment, the target indicator of the business side is calculated according to the matched target rule and the database change log, including:

[0036] Read the changed fields in the database change log to obtain the target object;

[0037] The target element is obtained from the target object according to the predicate condition, and the target element is calculated according to the aggregation rule to obtain the target indicator on the business side.

[0038] In one of the embodiments, the database change log is a binlog log.

[0039] In a second aspect, the present application provides a data proofreading device, comprising:

[0040] A caliber acquisition module is used to obtain the indicator calibers of multiple target indicators;

[0041] The rule generation module is used to input the caliber of each indicator into the NL2SQL model, obtain the SQL statement corresponding to each target indicator, and parse the SQL statement to obtain the rule information corresponding to each target indicator;

[0042] A rule table generation module, used to generate an event rule table based on each rule information;

[0043] The rule matching module is used to obtain the database change log and traverse the event rule table to determine whether the database change log has matching rule information in the event rule table;

[0044] A data comparison module is used to calculate the target indicator on the business side according to the target rule and the database change log if the database change log has a matching target rule, and compare the target indicator on the business side with the target indicator on the big data side;

[0045] The alarm module is used to generate an alarm when the difference between the target indicator on the business side and the target indicator on the big data side exceeds a preset range.

[0046] In a third aspect, the present application provides a computer device comprising one or more processors and a memory, wherein the memory stores computer-readable instructions, and when the computer-readable instructions are executed by one or more processors, the steps of the data proofreading method in any of the above embodiments are executed.

[0047] In a fourth aspect, the present application provides a storage medium storing computer-readable instructions. When the computer-readable instructions are executed by one or more processors, the one or more processors execute the steps of the data proofreading method in any of the above embodiments.

[0048] It can be seen from the above technical solutions that the embodiments of the present application have the following advantages:

[0049] Based on the data proofreading method in this embodiment, the indicator calibers of multiple target indicators are first obtained, and these indicator calibers are input into the NL2SQL model to generate corresponding SQL statements, and then the SQL statements are parsed to extract the various elements related to the calculation of the target indicators. Based on these rule information, an event rule table in a unified format is generated. The event rule table is matched with the database change log to determine whether the change in the database is related to some target rules. If so, the target indicators of the affected business side are recalculated according to the matched target rules and database change logs, and compared with the target indicators calculated by the big data platform. If the difference between the two exceeds the preset range, the alarm mechanism will be triggered to notify the relevant personnel to handle it. This solution uses a natural language to SQL model to automatically convert the target indicator into SQL, which can enable the development to realize an indicator and automatically proofread with almost 0 development and 0 maintenance. It only needs to focus on the construction of the indicator system and the business itself, which improves efficiency. In addition, this method is proofread based on the database change log, does not invade the business database or the data warehouse source layer at all, and has extremely high performance and does not affect the business itself at all. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0051] Figure 1 A flowchart of a data proofreading method provided in one embodiment of the present application;

[0052] Figure 2 This is a flow chart of a data proofreading method in another embodiment of the present application;

[0053] Figure 3 A schematic diagram of a module of a data proofreading device provided in one embodiment of the present application;

[0054] Figure 4 An internal structure diagram of a computer device provided for one embodiment of the present application. DETAILED DESCRIPTION

[0055] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0056] This application embodiment provides a data verification method, see Figure 1 , including steps S102 to S112.

[0057] S102, obtaining indicator calibers of multiple target indicators.

[0058] It can be understood that the target indicator here refers to the data indicator that needs to be proofread. The indicator caliber refers to the natural language description of the data indicator. For example, "sales" and "number of new users yesterday" are commonly used in the e-commerce field. Generally speaking, all data indicators in the data indicator system will be selected as target indicators for data proofreading. However, some of them can also be selected as target indicators according to actual needs.

[0059] The indicator caliber can be obtained by reading the metadata of the data indicator table on the big data side to obtain the caliber of each indicator. The target indicator will be summarized in the data indicator table in the data warehouse on the big data side. Metadata is the data that describes the data indicator table, including information such as database name or table name, field name, etc. Generally speaking, the indicator caliber is the field name contained in the data indicator table. Therefore, the indicator caliber can be obtained by reading the metadata of the data indicator table on the big data side. Assuming that the data warehouse where the data indicator table is stored is MySQL, the metadata can be obtained through its built-in information_schema.

[0060] S104, input each indicator caliber into the NL2SQL model, obtain the SQL statement corresponding to each target indicator, and parse the SQL statement to obtain the rule information corresponding to each target indicator.

[0061] It can be understood that the NL2SQL model is an artificial intelligence model that converts natural language into SQL query statements. Through training on a large amount of annotated data, the model can understand the intention of natural language expression and generate corresponding SQL statements. It is worth mentioning that since only data comparison is required and there is no need to change the data in the database, the SQL statements involved in this embodiment are generally only query statements (Select). The training process of the NL2SQL model is relatively conventional, and it also involves multiple links such as data set preparation, feature extraction from the data set, feature-based training, and model effect evaluation. The data set can form a training data pair with the indicator caliber of the SQL statements used in the historical business development process and the data indicators that need to be queried. The training data pair needs to cover all indicators in the data indicator system to obtain a large number of training data pairs to form a training set.

[0062] After obtaining the SQL statements corresponding to each target indicator, these converted SQL statements do not need to be executed, but only need to be parsed to extract the elements in the SQL statement, a structured statement, to form rule information. The rule information includes the specific method of calculating the target indicator, the required original business data, and the identification of the storage location of the target indicator on the big data side. It can be used to determine whether to trigger data proofreading when data changes occur on the business side, and to guide the calculation of indicators on the business side when triggered.

[0063] Specifically, SQL statements are query statements, which include three keywords: Select, From, and Where. For example, take this SQL statement as an example:

[0064] select count(DISTINCT id) as last_day_incr from user_info where date (create_time) = CURRENT_DATE - 1 and ... . Among them, the content declared after selecting the keyword is the rule for how to aggregate and calculate the data, such as sum (SUM), count (COUNT), average (AVG), etc. The content declared after the source keyword is to specify the source of the query data, that is, from which data tables to obtain the original business data. The content declared after the condition keyword is the predicate condition used to filter and screen the data. Therefore, when parsing SQL statements, the focus is on extracting the content declared by these three keywords to obtain the aggregation rules, source table names, and predicate conditions respectively. In implementation, a parser (Parser) or matching algorithm is usually used to identify and extract these key information from SQL statements. The parser can parse the SQL statement into an abstract syntax tree (AST), and then use the matching algorithm to find the corresponding node or subtree in the AST to obtain the required aggregation rules, source table names, predicate conditions and other information.

[0065] S106: Generate an event rule table based on each rule information.

[0066] It can be understood that the event rule table is a table or data structure that stores various rule information. It organizes and stores the rule information obtained from step S104 in a specific format. This step is to organize and store the rule information corresponding to each indicator obtained in step S104 in a unified format into an event rule table. The design of this table needs to be able to effectively store and manage various types of rule information. Generate an event rule table in a unified format to facilitate rule search, comparison, application and maintenance.

[0067] S108, obtaining the database change log, and traversing the event rule table to determine whether the database change log has matching rule information in the event rule table.

[0068] It can be understood that the database change log records the log information of the data changes in the database table, usually including the change library name, the change table name, the data before and after the change, and other information. The database table here includes both the database for storing data indicators on the big data side and the database for storing original business data. The database change log reflects the changes in the database, and the rule information records which data is related to the calculation of the target indicator. If the original business data that has changed this time is related to the target indicator calculation process corresponding to one of the rule information, it means that the target indicator has changed, which means that the target indicator needs to be proofread. Therefore, by comparing the obtained database change log with the rule information in the event rule table, it can be determined whether to trigger the start of data proofreading. For example, the number of new users in the target indicator needs to be determined based on the number of entries of the user information of the new users. When calculating the target indicator, it is necessary to perform cumulative calculation based on the user information table of the new users. If the user information table changes, it means that the target indicator of the number of new users will change and needs to be proofread. At this time, the corresponding database change log will also appear, and the log will match the rule information corresponding to the number of new users, thereby triggering the proofreading of the number of new users. The database change log can be obtained continuously in real time (stream processing) or in batches of temporarily stored logs (batch processing) when certain conditions are met.

[0069] S110: If yes, then the target indicator on the business side is calculated according to the matched target rule and the database change log, and the target indicator on the business side is compared with the target indicator on the big data side.

[0070] It can be understood that data proofreading refers to comparing the data on the big data side with the data on the business side. The data on the big data side is the index value obtained based on the computing system based on the big data platform. When the data index is actually used, it is generally read from the database on the big data side, and the computing system of the big data platform may have problems, so proofreading is required. The proofreading in this embodiment is to directly use the original business data and its calculation formula to obtain the target index on the business side without going through the computing system of the big data platform. Among them, if the matching rule information is found in the event rule table, the matched rule information is determined as the target rule, and the original business data required to calculate the target index on the business side exists in the database change log, and the calculation formula exists in the target rule. The target index on the business side does not need to invade the database itself, will not affect the performance of the original database, and is more efficient. After calculating the target index on the business side, it is compared with the index on the big data side.

[0071] S112: When the difference between the target indicator on the business side and the target indicator on the big data side exceeds a preset range, an alarm is issued.

[0072] It is understandable that, usually, due to factors such as data delay and calculation accuracy, a certain difference between the indicator values ​​on the business side and the big data side is acceptable, as long as the difference is within a reasonable range. However, if the difference is too large and exceeds the pre-set threshold range, it may mean that there are serious calculation errors or data problems in the system, which need to be paid attention to and processed immediately. Therefore, this step will continuously monitor the difference in the indicator values ​​on both sides, and once it is found that it exceeds the threshold range, the alarm mechanism will be triggered. The alarm can be made in a variety of ways, such as group robots, emails, text messages, and phone calls. Specifically, the Alertmanager can be used for alarm.

[0073] This method first obtains the indicator calibers of multiple target indicators, inputs these indicator calibers into the NL2SQL model, generates corresponding SQL statements, and then parses the SQL statements to extract the various elements related to the calculation of the target indicators. Based on these rule information, an event rule table in a unified format is generated. The event rule table is matched with the database change log to determine whether the database change is related to some target rules. If so, the target indicators of the affected business side are recalculated according to the matched target rules and database change logs, and compared with the target indicators calculated by the big data platform. If the difference between the two exceeds the preset range, an alarm mechanism will be triggered to notify relevant personnel to handle it. This solution uses a natural language to SQL model to automatically convert target indicators into SQL, which enables developers to automatically proofread after implementing an indicator with almost zero development and zero maintenance, and only needs to focus on the construction of the indicator system and the business itself, which improves efficiency. In addition, this method is proofread based on the database change log, does not invade the business database or the data warehouse source layer at all, and has extremely high performance and does not affect the business itself at all.

[0074] In one of the embodiments, since data indicators can be divided into offline indicators and online indicators according to their nature. Online indicators are indicators that are calculated in real time according to the changes in raw data. Offline indicators are indicators that are calculated according to the changes in raw data within a period. In the data indicator table, these two types can be distinguished as online indicator tables and offline indicator tables in the table name. There are different category identifiers in the names of online indicator tables and offline indicator tables. For example, the suffix of the online indicator table name is real_time, and the suffix of the offline indicator table name is offline. For the data in these two data indicator tables, different mechanisms can be used for processing, and it is necessary to distinguish them in the rule information to facilitate identification and select different processing mechanisms. Specifically, when reading the metadata of the data indicator table on the big data side and obtaining the caliber of each indicator, it is also necessary to record the name of the data indicator table corresponding to each indicator caliber. In the rule information, in addition to the aggregation rule, the source table name and the predicate condition, it is also necessary to add the name of the data indicator table corresponding to the target indicator. It is used to distinguish the type of each rule information as offline or online.

[0075] In one embodiment, for the solution that includes both offline and online types in the indicator system, please refer to Figure 2 , the data proofreading method includes steps S202 to S214.

[0076] S202, obtaining the indicator scopes of multiple target indicators and the names of their corresponding data indicator tables.

[0077] S204, input each indicator caliber into the NL2SQL model, obtain the SQL statement corresponding to each target indicator, and parse the SQL statement to obtain the aggregation rules, source table name and predicate conditions, and obtain the rule information corresponding to each target indicator according to the aggregation rules, source table name and predicate conditions and the name of the indicator table.

[0078] S206: Generate an event rule table based on each rule information.

[0079] The description of steps S202 to S206 can be found above.

[0080] S208, execute the first stream processing task to continuously obtain the database change log in real time, and determine the rule information with the name of the data indicator table in the event rule table and the category identifier of the online indicator table as the rule to be matched, and match the rule to be matched with the obtained database change log to determine whether the database change log has matching rule information in the event rule table.

[0081] It can be understood that the stream processing task is a task that requires real-time processing. The first stream processing task is set for the online indicators in the target indicators. Specifically, the event rule table is distinguished according to the name of the data indicator table recorded in the rule information. In the first stream processing task, the name with the category identifier of the online indicator table (such as real_time) will be determined as the rules to be matched, that is, the rules that need to be traversed. By continuously acquiring the database change log in real time and matching it with the rules to be matched in the first stream processing task, it is determined whether the database change log has matching rule information in the event rule table.

[0082] Specifically, after the event rule table is generated, the FlinkCDC framework can be used to process other processes. FlinkCDC supports streaming or batch processing of tasks, and the execution mode can be set through execution.runtime-mode, including streaming, batch, and automatic. The relevant logic of the first stream processing task is written into the first task code, and the stream execution mode is used to execute the first task code to implement the relevant logic in the first stream processing task described in step S208.

[0083] S210, execute the first batch of processing tasks to batch obtain all database change logs within a preset time range when the batch processing conditions are met, and determine the rule information with the name of the data indicator table in the event rule table and the offline indicator table category identifier as the rule to be matched, and match the rule to be matched with the obtained database change log to determine whether the database change log has matching rule information in the event rule table.

[0084] It can be understood that the stream processing task is a task that periodically obtains data in batches and processes the obtained data. The first stream processing task is set for the offline indicator in the target indicator. Specifically, the event rule table is distinguished according to the name of the data indicator table recorded in the rule information. In the first batch of processing tasks, the names with the category identifier of the offline indicator table (such as offline) will be determined as the rules to be matched, that is, the rules that need to be traversed. Whether to trigger rule matching is determined by judging whether the batch processing conditions are met. When the batch processing conditions are met, all database change logs cached within the preset time range will be batch obtained at one time, and they will be matched with the rules to be matched in the first batch of processing tasks to determine whether the database change log has matching rule information in the event rule table. For example, for the sales volume of the day, the batch processing condition can be whether it reaches 24 o'clock in the evening. When it reaches 24 o'clock, all database change logs of the day will be batch obtained at one time, and then these database change logs will be matched with the rules to be matched in the first batch of processing tasks.

[0085] Specifically, after the event rule table is generated, the relevant logic of the first batch of processing tasks can be written into the second task code, and the batch execution mode in FlinkCDC can be used to execute the second task code to implement the relevant logic in the first batch of processing tasks described in step S210. It is worth mentioning that the logic of the first task code and the second task code are basically the same, and the only difference is the selection conditions for the rules to be matched. Therefore, they can be highly reused when writing, and only the judgment conditions of the rules to be matched need to be modified.

[0086] Whether in the first batch of processing tasks or the first-stream processing tasks, the database change log is matched with the rules to be matched in the following way: the change table name in the database change log is compared with the source information of each rule to be matched. In order to determine whether a database change log is related to a specific rule, the key is to compare the change table name recorded in the database change log with the source information involved in the rule definition. Specifically, the source information recorded in each rule information indicates which data table the original business data required to calculate the corresponding target indicator comes from. If the database change log records the data changes of these related tables, the calculation results of the corresponding target indicators will change, and data proofreading needs to be triggered. Therefore, in the matching process, the change table name in the database change log needs to be compared with the source information of each rule to be matched. If the change table name appears in the source information of a rule, it means that the change log has a matching relationship with this rule, and the subsequent business-side indicator calculation needs to be performed according to the rule.

[0087] In one of the embodiments, the specific method of calculation can be to read the changed field in the database change log to obtain the target object. The changed field includes all the fields in the table that have changed after the database is changed and their changed values. The changed field is generally structured data in json format, and the json object in the changed field is read out to obtain the target object. Since not all target objects need to be included in the final indicator calculation, they need to be filtered according to the predicate conditions defined in the rules. The predicate conditions usually include a series of logical expressions to determine whether each key-value pair contained in the target object is required for calculating the business-side indicators. For each element (key-value pair) in the target object, only the target element that meets the predicate condition will be extracted as the target element. After the target element is extracted, the target element can be calculated according to the aggregation rule. If the sum is calculated, the target element is numerically accumulated, and the count is the number of target elements.

[0088] S212: If yes, the target index on the business side is calculated according to the matched target rule and the database change log, and the target index on the business side is compared with the target index on the big data side.

[0089] S214: When the difference between the target indicator on the business side and the target indicator on the big data side exceeds a preset range, an alarm is issued.

[0090] Steps S212 to S214 may refer to the above description.

[0091] In one of the embodiments, the rule information also records the indicator value of the corresponding target indicator on the big data side. The data proofreading method also includes: executing a second stream processing task to continuously obtain a database change log in real time, and when the name of the change table in the database change log is the same as the name of the data indicator table of any rule information, the indicator value of the rule information is updated according to the changed field in the database change log.

[0092] It can be understood that the indicator value on the big data side can also be obtained in a non-invasive manner and added to the corresponding rule information. Specifically, an additional stream processing task, namely the second stream processing task, is added to FlinkCDC. By continuously acquiring the database change log in real time, the real-time changes of the database can be understood. Since the name of the corresponding data indicator table is also recorded in the rule information, the name is compared with the change table name in the database change log. If the change table name in the database change log is the same as the name, it means that the data indicator table where the target indicator corresponding to the rule information is located has changed, then the target indicator may have changed. Therefore, it is necessary to update the indicator value on the big data side recorded in the rule information. The field name of each target indicator is also recorded in the rule information. The specific way to update is to read the json object from the changed field of the database change log, match the key of the json object with each field name, find the rule information to be updated, and add the corresponding value to the rule information to be updated to complete the update of the indicator value. On this basis, since the indicator value of the corresponding target indicator on the big data side is recorded in the rule information, it can be directly read from the rule information and compared with the calculated indicator value on the business side. Therefore, in one embodiment, comparing the target indicator on the business side with the target indicator on the big data side includes: reading the indicator value corresponding to the target rule, and comparing the read indicator value with the target indicator on the business side.

[0093] In one of the embodiments, the data in the data indicator table is calculated by the big data platform based on the original business data. The big data platform includes a source layer, a data warehouse layer, a wide table layer, an aggregation layer, and an indicator layer. Among them, the source layer is to synchronize the original business data to the data warehouse without additional processing. The data warehouse layer converts and optimizes the data of the source layer to create a fact table DWD and a dimension table DIM with the same business structure. The wide table layer is to associate the dimension table with the fact table to form a core detailed wide table DWS in different data domains. The purpose of the wide table is to reduce downstream calculations and associations and reuse core calculation logic. The aggregation layer is based on the wide table for light aggregation to form data indicators DWT of different time periods and dimensions in a single data domain. The indicator layer is based on the core business and goals of different data domains, and the aggregation layer is aggregated to form an indicator layer in a single data domain. The indicator layer only describes the summary analysis values ​​of this data domain, specified dimensions, and specified time periods (data indicators do not cross data domains).

[0094] In one embodiment, the database change log is a binlog log. When MySQL processes each update operation, it will first record the operation in detail in the binlog, and then perform the actual data modification. The purpose of this is to use the binlog to recover the data when it is accidentally damaged or lost. Therefore, the binlog records all changes made to the database data and is very suitable as a source of change logs.

[0095] This application provides a data proofreading device, see Figure 3 , including a caliber acquisition module 310, a rule generation module 320, a rule table generation module 330, a rule matching module 340, a data comparison module 350 and an alarm module 360.

[0096] The caliber acquisition module 310 is used to obtain the indicator calibers of multiple target indicators. The rule generation module 320 is used to input the calibers of each indicator into the NL2SQL model, obtain the SQL statements corresponding to each target indicator, and parse the SQL statements to obtain the rule information corresponding to each target indicator. The rule table generation module 330 is used to generate an event rule table based on each rule information. The rule matching module 340 is used to obtain the change log of the database, and traverse the event rule table to determine whether the database change log has matching rule information in the event rule table. The data comparison module 350 is used to calculate the target indicator on the business side according to the target rule and the database change log if there is a matching target rule in the database change log, and compare the target indicator on the business side with the target indicator on the big data side. The alarm module 360 ​​is used to alarm when the difference between the target indicator on the business side and the target indicator on the big data side exceeds a preset range.

[0097] In one of the embodiments, the caliber acquisition module 310 is used to read the metadata of the data indicator table on the big data side to obtain the caliber of each indicator.

[0098] In one embodiment, the rule generation module 320 is used to obtain the aggregation rule from the selection keywords of the SQL statement; obtain the source table name from the source keywords of the SQL statement; obtain the predicate condition from the condition keywords of the SQL statement; and obtain the rule information based on the aggregation rule, the source table name and the predicate condition.

[0099] In one embodiment, the caliber acquisition module 310 is used to record the name of the data indicator table corresponding to each indicator caliber; wherein the data indicator table includes an online indicator table and an offline indicator table, and the names of the online indicator table and the offline indicator table have different category identifiers. The rule generation module 320 is used to add the name of the data indicator table corresponding to the target indicator to the rule information.

[0100] In one embodiment, the rule matching module 340 is used to execute a first stream processing task to continuously obtain database change logs in real time, and determine the rule information whose name of the data indicator table in the event rule table has the category identifier of the online indicator table as the rule to be matched, and match the rule to be matched with the obtained database change log; execute a first batch of processing tasks to batch obtain all database change logs within a preset time range when the batch processing conditions are met, and determine the rule information whose name of the data indicator table in the event rule table has the category identifier of the offline indicator table as the rule to be matched, and match the rule to be matched with the obtained database change log.

[0101] In one embodiment, the rule generation module 320 is used to execute the second stream processing task to continuously obtain the database change log in real time, and when the name of the change table in the database change log is the same as the name of the data indicator table of any rule information, the indicator value of the rule information is updated according to the changed field in the database change log.

[0102] In one embodiment, the rule generation module 320 is used to execute the first task code using the stream execution mode in FlinkCDC to execute the first stream processing task; and to execute the second task code using the batch execution mode in FlinkCDC to execute the second stream processing task; wherein the first task code and the second task code can be reused after modifying the judgment condition of the rule to be matched.

[0103] In one embodiment, the data comparison module 350 is used to read the indicator value corresponding to the target rule, and compare the read indicator value with the target indicator on the business side.

[0104] In one embodiment, the rule matching module 340 is used to compare the change table name in the database change log with the source information of each to-be-matched rule; if the comparison result is the same, the to-be-matched rule corresponding to the source information is determined to be the target rule.

[0105] In one embodiment, the data comparison module 350 is used to read the changed fields in the database change log to obtain the target object; obtain the target element from the target object according to the predicate condition, and calculate the target element according to the aggregation rule to obtain the target indicator on the business side.

[0106] For the specific definition of the data proofreading device, please refer to the definition of the data proofreading method above, which will not be repeated here. The various modules in the above-mentioned data proofreading device can be implemented in whole or in part by software, hardware and a combination thereof. The above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, or can be stored in the memory in the computer device in the form of software, so that the processor can call and execute the operations corresponding to the above modules. It should be noted that the division of modules in the embodiment of the present application is schematic and is only a logical function division. There may be other division methods in actual implementation.

[0107] The present application provides a computer device, including one or more processors and a memory, wherein the memory stores computer-readable instructions, and when the computer-readable instructions are executed by the one or more processors, the following are performed: obtaining the indicator caliber of multiple target indicators; inputting each indicator caliber into an NL2SQL model to obtain an SQL statement corresponding to each target indicator, and parsing the SQL statement to obtain rule information corresponding to each target indicator; generating an event rule table based on each rule information; obtaining a database change log, and traversing the event rule table to determine whether the database change log has matching rule information in the event rule table; if so, calculating the target indicator on the business side according to the matched target rule and the database change log, and comparing the target indicator on the business side with the target indicator on the big data side; and giving an alarm when the difference between the target indicator on the business side and the target indicator on the big data side exceeds a preset range.

[0108] In one of the embodiments, when the computer-readable instructions are executed by one or more processors, the following is performed: reading the metadata of the data indicator table on the big data side to obtain the caliber of each indicator.

[0109] In one embodiment, when the computer-readable instructions are executed by one or more processors, the following are performed: obtaining aggregation rules from the selection keywords of the SQL statement; obtaining the source table name from the source keywords of the SQL statement; obtaining the predicate condition from the condition keywords of the SQL statement; and obtaining rule information based on the aggregation rules, the source table name and the predicate condition.

[0110] In one embodiment, when the computer-readable instructions are executed by one or more processors, the computer-readable instructions are executed to: record the name of the data indicator table corresponding to each indicator caliber; wherein the data indicator table includes an online indicator table and an offline indicator table, and the names of the online indicator table and the offline indicator table have different category identifiers. When the computer-readable instructions are executed by one or more processors, the computer-readable instructions are executed to: add the name of the data indicator table corresponding to the target indicator to the rule information.

[0111] In one embodiment, when the computer-readable instructions are executed by one or more processors, the following are performed: executing a first stream processing task to continuously obtain database change logs in real time, and determining the rule information whose name of the data indicator table in the event rule table has the category identifier of the online indicator table as the rule to be matched, and matching the rule to be matched with the obtained database change log; executing a first batch of processing tasks to batch obtain all database change logs within a preset time range when the batch processing conditions are met, and determining the rule information whose name of the data indicator table in the event rule table has the category identifier of the offline indicator table as the rule to be matched, and matching the rule to be matched with the obtained database change log.

[0112] In one embodiment, when the computer-readable instructions are executed by one or more processors, the following are performed: executing a second stream processing task to continuously obtain a database change log in real time, and when a change table name in the database change log is the same as the name of a data indicator table of any rule information, updating the indicator value of the rule information according to the changed field in the database change log.

[0113] In one embodiment, when the computer-readable instructions are executed by one or more processors, the following are performed: using the stream execution mode in FlinkCDC to execute the first task code to execute the first stream processing task; using the batch execution mode in FlinkCDC to execute the second task code to execute the second stream processing task; wherein the first task code and the second task code can be reused after modifying the judgment condition of the rule to be matched.

[0114] In one embodiment, when the computer-readable instructions are executed by one or more processors, the following is performed: reading the indicator value corresponding to the target rule, and comparing the read indicator value with the target indicator on the business side.

[0115] In one embodiment, when the computer-readable instructions are executed by one or more processors, the following is performed: comparing the change table name in the database change log with the source information of each to-be-matched rule; if the comparison result is the same, determining that the to-be-matched rule corresponding to the source information is the target rule.

[0116] In one of the embodiments, when the computer-readable instructions are executed by one or more processors, the following are performed: reading the changed fields in the database change log to obtain the target object; obtaining the target element from the target object according to the predicate condition, and calculating the target element according to the aggregation rule to obtain the target indicator on the business side.

[0117] Indicatively, if Figure 4 As shown, Figure 4 A schematic diagram of the internal structure of a computer device provided in an embodiment of the present application. Figure 4 The computer device 400 includes a processing component 402, which further includes one or more processors, and a memory resource represented by a memory 401 for storing instructions executable by the processing component 402, such as an application. The application stored in the memory 401 may include one or more modules, each corresponding to a set of instructions. In addition, the processing component 402 is configured to execute instructions to perform the steps of the data proofreading method of any of the above embodiments.

[0118] The computer device 400 may further include a power supply component 403 configured to perform power management of the computer device 400 , a wired or wireless model interface 404 configured to connect the computer device 400 to the model, and an input / output (I / O) interface 405 .

[0119] The present application provides a storage medium, in which computer-readable instructions are stored. When the computer-readable instructions are executed by one or more processors, the one or more processors are caused to execute: obtaining the indicator caliber of multiple target indicators; inputting each indicator caliber into an NL2SQL model to obtain an SQL statement corresponding to each target indicator, and parsing the SQL statement to obtain rule information corresponding to each target indicator; generating an event rule table based on each rule information; obtaining a database change log, and traversing the event rule table to determine whether the database change log has matching rule information in the event rule table; if so, calculating the target indicator on the business side according to the matched target rule and the database change log, and comparing the target indicator on the business side with the target indicator on the big data side; and issuing an alarm when the difference between the target indicator on the business side and the target indicator on the big data side exceeds a preset range.

[0120] In one of the embodiments, when the computer-readable instructions are executed by one or more processors, the one or more processors execute: reading the metadata of the data indicator table on the big data side to obtain the caliber of each indicator.

[0121] In one embodiment, when the computer-readable instructions are executed by one or more processors, the one or more processors are caused to execute: obtaining aggregation rules from the selection keywords of the SQL statement; obtaining the source table name from the source keywords of the SQL statement; obtaining the predicate condition from the condition keywords of the SQL statement; and obtaining rule information based on the aggregation rules, the source table name and the predicate condition.

[0122] In one embodiment, when the computer-readable instructions are executed by one or more processors, the one or more processors execute: recording the name of the data indicator table corresponding to each indicator caliber; wherein the data indicator table includes an online indicator table and an offline indicator table, and the names of the online indicator table and the offline indicator table have different category identifiers. When the computer-readable instructions are executed by one or more processors, the one or more processors execute: adding the name of the data indicator table corresponding to the target indicator to the rule information.

[0123] In one embodiment, when the computer-readable instructions are executed by one or more processors, the one or more processors execute: executing a first stream processing task to continuously obtain database change logs in real time, and determining the rule information whose name of the data indicator table in the event rule table has the category identifier of the online indicator table as the rule to be matched, and matching the rule to be matched with the obtained database change log; executing a first batch of processing tasks to batch obtain all database change logs within a preset time range when the batch processing conditions are met, and determining the rule information whose name of the data indicator table in the event rule table has the category identifier of the offline indicator table as the rule to be matched, and matching the rule to be matched with the obtained database change log.

[0124] In one embodiment, when the computer-readable instructions are executed by one or more processors, the one or more processors execute: execute a second stream processing task to continuously obtain a database change log in real time, and when the name of a change table in the database change log is the same as the name of a data indicator table of any rule information, update the indicator value of the rule information according to the changed field in the database change log.

[0125] In one of the embodiments, when the computer-readable instructions are executed by one or more processors, the one or more processors execute: using the stream execution mode in FlinkCDC, executing the first task code to perform the first stream processing task; using the batch execution mode in FlinkCDC, executing the second task code to perform the second stream processing task; wherein the first task code and the second task code can be reused after modifying the judgment condition of the rule to be matched.

[0126] In one embodiment, when the computer-readable instructions are executed by one or more processors, the one or more processors execute: reading the indicator value corresponding to the target rule, and comparing the read indicator value with the target indicator on the business side.

[0127] In one of the embodiments, when the computer-readable instructions are executed by one or more processors, the one or more processors execute: comparing the change table name in the database change log with the source information of each to-be-matched rule; if the comparison result is the same, determining that the to-be-matched rule corresponding to the source information is the target rule.

[0128] In one of the embodiments, when the computer-readable instructions are executed by one or more processors, the one or more processors execute: reading the changed fields in the database change log to obtain the target object; obtaining the target element from the target object according to the predicate condition, and calculating the target element according to the aggregation rule to obtain the target indicator on the business side.

[0129] Finally, it should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the presence of other identical elements in the process, method, article or device including the elements.

[0130] The various embodiments in this specification are described in a progressive manner, and each embodiment focuses on the differences from other embodiments. The various embodiments can be combined as needed, and the same or similar parts can refer to each other.

[0131] The above description of the disclosed embodiments enables those skilled in the art to implement or use the present application. Various modifications to these embodiments will be apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application will not be limited to the embodiments shown herein, but will conform to the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A data proofreading method, characterized in that: include: Obtaining the indicator calibers of multiple target indicators; specifically comprising: reading metadata of a data indicator table on a big data side to obtain the indicator calibers of each indicator; recording the name of the data indicator table corresponding to each indicator caliber; wherein the data indicator table includes an online indicator table and an offline indicator table, and the names of the online indicator table and the offline indicator table have different category identifiers; Input each of the indicator calibers into the NL2SQL model, obtain the SQL statements corresponding to each of the target indicators, and parse the SQL statements to obtain the rule information corresponding to each of the target indicators; specifically including: obtaining the aggregation rule after selecting the keywords of the SQL statement; obtaining the source table name after the source keywords of the SQL statement; obtaining the predicate condition after the condition keywords of the SQL statement; obtaining the rule information according to the aggregation rule, the source table name and the predicate condition; adding the name of the data indicator table corresponding to the target indicator to the rule information; Based on each of the rule information, generate an event rule table; Obtain the database change log, and traverse the event rule table to determine whether the database change log has matching rule information in the event rule table; specifically including: executing the first stream processing task to continuously obtain the database change log in real time, and determining the rule information with the name of the data indicator table in the event rule table and the category identifier of the online indicator table as the rule to be matched, and matching the rule to be matched with the obtained database change log; executing the first batch processing task to batch obtain all the database change logs within a preset time range when the batch processing conditions are met, and determining the rule information with the name of the data indicator table in the event rule table and the category identifier of the offline indicator table as the rule to be matched, and matching the rule to be matched with the obtained database change log; If yes, the target indicator on the business side is calculated according to the matched target rule and the database change log, and the target indicator on the business side is compared with the target indicator on the big data side; When the difference between the target indicator on the business side and the target indicator on the big data side exceeds a preset range, an alarm is issued.

2. The data proofreading method according to claim 1, characterized in that: The rule information also records the indicator value corresponding to the target indicator on the big data side, and the data proofreading method further includes: Execute the second stream processing task to continuously obtain the database change log in real time, and when the name of the change table in the database change log is the same as the name of the data indicator table of any of the rule information, update the indicator value of the rule information according to the changed field in the database change log.

3. The data proofreading method according to claim 1, characterized in that: Also includes: Utilize the stream execution mode in FlinkCDC to execute the first task code to execute the first stream processing task; The second task code is executed by using the batch execution mode in FlinkCDC to execute the first batch of processing tasks; wherein the first task code and the second task code are reusable after modifying the judgment condition of the rule to be matched.

4. The data proofreading method according to claim 2, characterized in that: The comparing the target indicator on the business side with the target indicator on the big data side includes: The indicator value corresponding to the target rule is read, and the read indicator value is compared with the target indicator on the business side.

5. The data proofreading method according to claim 1, characterized in that: The matching the to-be-matched rule with the acquired database change log includes: Compare the change table name in the database change log with the source information of each of the to-be-matched rules; If the comparison result is the same, it is determined that the to-be-matched rule corresponding to the source information is the target rule.

6. The data proofreading method according to claim 1, characterized in that: The calculating the target indicator of the business side according to the matched target rule and the database change log includes: Read the changed fields in the database change log to obtain the target object; A target element is acquired from the target object according to the predicate condition, and the target element is calculated according to the aggregation rule to obtain the target indicator on the business side.

7. The data proofreading method according to claim 1, characterized in that: The database change log is a binlog log.

8. A data proofreading device, characterized in that: include: The caliber acquisition module is used to obtain the indicator calibers of multiple target indicators; specifically, it includes: reading the metadata of the data indicator table on the big data side to obtain the calibers of each indicator; recording the name of the data indicator table corresponding to each indicator caliber; wherein the data indicator table includes an online indicator table and an offline indicator table, and the names of the online indicator table and the offline indicator table have different category identifiers; A rule generation module is used to input the caliber of each indicator into the NL2SQL model, obtain the SQL statement corresponding to each target indicator, and parse the SQL statement to obtain the rule information corresponding to each target indicator; specifically including: obtaining the aggregation rule after selecting the keyword of the SQL statement; obtaining the source table name after the source keyword of the SQL statement; obtaining the predicate condition after the condition keyword of the SQL statement; obtaining the rule information according to the aggregation rule, the source table name and the predicate condition; adding the name of the data indicator table corresponding to the target indicator to the rule information; A rule table generating module, used for generating an event rule table based on each of the rule information; A rule matching module is used to obtain the change log of the database, and traverse the event rule table to determine whether the database change log has matching rule information in the event rule table; specifically comprising: executing a first stream processing task to continuously obtain the database change log in real time, and determining the rule information whose name of the data indicator table in the event rule table is accompanied by the category identifier of the online indicator table as the rule to be matched, and matching the rule to be matched with the obtained database change log; executing a first batch processing task to batch obtain all the database change logs within a preset time range when the batch processing conditions are met, and determining the rule information whose name of the data indicator table in the event rule table is accompanied by the category identifier of the offline indicator table as the rule to be matched, and matching the rule to be matched with the obtained database change log; A data comparison module, for calculating the target indicator on the business side according to the target rule and the database change log if the change log of the database has a matching target rule, and comparing the target indicator on the business side with the target indicator on the big data side; The alarm module is used to generate an alarm when the difference between the target indicator on the business side and the target indicator on the big data side exceeds a preset range.

9. A computer device, characterized in that: The method comprises one or more processors and a memory, wherein the memory stores computer-readable instructions, and when the computer-readable instructions are executed by the one or more processors, the steps of the data proofreading method according to any one of claims 1 to 7 are executed.

10. A storage medium, characterized in that: The storage medium stores computer-readable instructions, and when the computer-readable instructions are executed by one or more processors, the one or more processors execute the steps of the data proofreading method according to any one of claims 1 to 7.

Images