Data quality detection method, device and storage medium

CN118626483BActive Publication Date: 2024-11-26SHENZHEN SMARTCITY TECH DEV GRP CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411096414.9
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-08-12
Publication Date
2024-11-26
Estimated Expiration
2044-08-12

AI Technical Summary

Technical Problem

The maintenance and updating of data quality rules in existing technologies are costly in terms of resources, which leads to frequent resource investment when business needs and data structures change.

Method used

By generating quality rule templates and using preset placeholders to replace rules or regular expressions to fill the templates, data quality detection scripts can be quickly built, reducing the need for frequent maintenance of data quality rules.

Benefits of technology

It reduces the resource costs of maintaining and updating data quality rules, improves the efficiency and compatibility of data quality detection, and reduces the need for frequent updates to data quality rules.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118626483B_ABST
    Figure CN118626483B_ABST
Patent Text Reader

Abstract

The present application discloses a data quality detection method, device and storage medium, which belongs to the field of data processing technology. The method includes: after obtaining an executable structured query statement, based on a preset placeholder replacement rule, replacing the target field in the executable structured query statement with a placeholder to generate a quality rule template; or, after receiving a regular expression, filling the template based on a preset expression to generate a quality rule template; after receiving a quality detection instruction, obtaining the filling data in the quality detection instruction, and determining the quality rule template corresponding to the quality detection instruction; filling the filling data into the quality rule template, generating a data quality detection script, and detecting the data to be detected according to the data quality detection script. The present application generates a quality rule template according to a regular expression or a structured query statement, and generates a data quality detection script to improve the efficiency of data quality detection.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a data quality detection method, device and storage medium. Background Art

[0002] In the process of testing data quality, according to the data quality rules, data that meets the quality requirements can be screened from the tested data, and data that does not meet the requirements can be removed to improve the data quality of the data to be tested. Among them, the data quality rules are usually stored in the corresponding database. According to the data type of the data to be tested, the corresponding data quality rules can be determined in the database, and the data to be tested can be tested according to the quality rules.

[0003] In related technologies, data quality is usually tested based on data quality rules preset in the database. In this case, when business requirements and data structures change and the original data quality rules are no longer applicable, it is necessary to rebuild data quality rules that are consistent with the latest business logic and data structure to complete the maintenance and update of data quality rules. This results in a high resource cost for the maintenance and update of data quality rules.

[0004] The above contents are only used to assist in understanding the technical solution of the present application and do not constitute an admission that the above contents are prior art. Summary of the invention

[0005] The main purpose of this application is to provide a data quality detection method, device and storage medium, aiming to solve the technical problem of high resource cost occupied by maintenance and update of data quality rules.

[0006] To achieve the above object, the present application provides a data quality detection method, which comprises the following steps:

[0007] After obtaining the executable structured query statement, based on the preset placeholder replacement rule, the target field in the executable structured query statement is replaced with the placeholder to generate a quality rule template;

[0008] Alternatively, after receiving the regular expression, filling the template based on a preset expression to generate the quality rule template;

[0009] After receiving the quality detection instruction, obtaining the filling data in the quality detection instruction, and determining the quality rule template corresponding to the quality detection instruction;

[0010] The filling data is filled into the quality rule template, a data quality detection script is generated, and the data to be detected is detected according to the data quality detection script.

[0011] Optionally, after receiving the executable structured query statement, replacing the target field in the executable structured query statement with a placeholder based on a preset placeholder replacement rule, before the step of generating a quality rule template, further includes:

[0012] After receiving the original structured query statement, verifying whether the original structured query statement is executable through the database engine;

[0013] When the original structured query statement is executable and the running result on the database engine is consistent with the expected running result, outputting the executable structured query statement based on the original structured query statement;

[0014] When the original structured query statement is not executable, or the running result of the original structured query statement is inconsistent with the expected running result, the original structured query statement is modified based on the grammatical rules corresponding to the database engine to generate the executable structured query statement.

[0015] Optionally, when the original structured query statement is not executable, or the running result of the original structured query statement is inconsistent with the expected running result, based on the grammatical rules corresponding to the database engine, modifying the original structured query statement to generate the executable structured query statement includes:

[0016] When the original structured query statement is not executable, or the running result of the original structured query statement is inconsistent with the expected running result, determining a grammar rule corresponding to the database engine;

[0017] By using the statement adjustment model, according to the grammatical rules, determining the points to be adjusted where the original structured query statement does not match the grammatical rules;

[0018] According to the expected operation result and the grammatical rule, as well as the point to be adjusted, the original structured query statement is adjusted to generate the executable structured query statement.

[0019] Optionally, the step of filling the filling data into the quality rule template, generating a data quality detection script, and detecting the data to be detected according to the data quality detection script includes:

[0020] Identify the data type of the filling data, and determine the corresponding target placeholder in the quality rule template according to the data type;

[0021] Replace the target placeholder with the filling data to generate the data quality detection script;

[0022] According to the data quality detection script, a corresponding data quality detection task is established, and the data to be detected is detected based on the data quality detection task.

[0023] Optionally, the step of establishing a corresponding data quality detection task according to the data quality detection script, and detecting the data to be detected based on the data quality detection task includes:

[0024] Creating a data detection project, and generating corresponding data detection tasks in the data detection project based on the data quality detection script;

[0025] The data detection project is packaged and run through the database engine.

[0026] Optionally, after receiving the quality detection instruction, before the step of acquiring the filling data in the quality detection instruction and determining the quality rule template corresponding to the quality detection instruction, the method further includes:

[0027] After the quality rule template is generated, corresponding input information is determined based on a user interface, and template information of the quality rule template is determined according to the input information, wherein the template information includes but is not limited to the name of the quality rule template, data source type, table / field level, and rule dimension;

[0028] The template information is associated with the quality rule template.

[0029] Optionally, the method further includes, after receiving the regular expression, filling the template based on a preset expression to generate the quality rule template:

[0030] After the template detection module receives the quality rule template, determining the structured query statement to be detected corresponding to the quality rule template;

[0031] Performing lexical analysis on the structured query statement to be detected to determine corresponding morphemes;

[0032] According to the morphemes and the grammatical rules of the database engine, the morphemes are combined into corresponding grammatical structures, and semantic analysis is performed on the grammatical structures;

[0033] Generate a parse tree of the structured query statement to be detected according to the semantic analysis result, and verify whether the parse tree complies with preset parse tree verification rules;

[0034] When the parse tree meets the preset parse tree verification rule, the quality rule template is judged to be correct.

[0035] Optionally, after the step of filling the filling data into the quality rule template, generating a data quality detection script, and detecting the data to be detected according to the data quality detection script, the method further includes:

[0036] Obtaining a detection result of the data to be detected;

[0037] A quality analysis report of the data to be detected is generated according to the detection result of the data to be detected, the data to be detected, and a data quality analysis report template.

[0038] In addition, to achieve the above-mentioned purpose, the present application also provides a data quality detection device, which includes: a memory, a processor, and a data quality detection program stored on the memory and executable on the processor, and the data quality detection program is configured to implement the steps of the data quality detection method as described above.

[0039] In addition, to achieve the above-mentioned purpose, the present application also provides a storage medium, on which a data quality detection program is stored, and when the data quality detection program is executed by a processor, the steps of the data quality detection method described above are implemented.

[0040] The present application quickly generates a corresponding quality rule template based on a structured query statement or regular expression input by a user, and after receiving a quality detection instruction, generates a corresponding data quality detection script based on the fill-in data in the quality detection instruction and a preset quality rule template, and detects the data to be detected through the data quality detection script, thereby avoiding the need to frequently maintain data quality rules and / or quality detection templates during the data quality detection process, thereby reducing the resource cost of data quality rule maintenance and updating during the data quality detection process. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Figure 1 This is a flow chart of the first embodiment of the data quality detection method of this application;

[0042] Figure 2 This is a schematic diagram of the data quality detection process involved in the first embodiment of the data quality detection method of this application;

[0043] Figure 3 This is a flow chart of the second embodiment of the data quality detection method of this application;

[0044] Figure 4 This is a flow chart of the third embodiment of the data quality detection method of this application;

[0045] Figure 5 This is a flow chart of a fourth embodiment of the data quality detection method of the present application;

[0046] Figure 6 It is a structural diagram of a data quality detection device for a hardware operating environment involved in an embodiment of the present application.

[0047] The realization of the purpose, functional features and advantages of this application will be further explained in conjunction with embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0048] It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0049] This application constructs a quality rule template by having the user input the original SQL statement or regular expression, and generates a corresponding data quality detection script based on the quality detection instructions and the placeholders in the quality rule template, and uses the data quality detection script to detect the data to be detected, thereby reducing the number of data quality rules that need to be updated and maintained, so as to reduce the resource cost of maintaining and updating data quality rules.

[0050] In order to better understand the above technical solution, exemplary embodiments of the present application will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present application are shown in the accompanying drawings, it should be understood that the present application can be implemented in various forms and should not be limited by the embodiments described herein. On the contrary, these embodiments are provided in order to enable a more thorough understanding of the present application and to fully convey the scope of the present application to those skilled in the art.

[0051] In order to better understand the above technical solution, the above technical solution will be described in detail below in conjunction with the accompanying drawings and specific implementation methods.

[0052] Embodiment 1

[0053] When data quality requirements change according to business and demand changes, the update and maintenance of data quality rules usually takes a long time and occupies high resource costs. In order to improve the efficiency of maintaining and updating data quality rules, the present application embodiment provides a data quality detection method, referring to Figure 1 , Figure 1 This is a flow chart of the first embodiment of the data quality detection method of the present application.

[0054] In this embodiment, the data quality detection method includes:

[0055] Step S10: after obtaining the executable structured query statement, based on a preset placeholder replacement rule, the target field in the executable structured query statement is replaced with a placeholder to generate a quality rule template;

[0056] Step S20: Alternatively, after receiving the regular expression, filling the template based on a preset expression to generate the quality rule template;

[0057] In this embodiment, the data quality detection system has two modes for generating quality rule detection templates. In the data quality detection system, the user can select the structured query language (SQL) mode to generate a quality rule template by inputting a structured query statement, or select the regular expression mode to generate a quality rule template by inputting a regular expression.

[0058] As an optional implementation, Figure 2 As shown, Figure 2 Schematic diagram of the data quality detection process involved in the embodiment of the present application. After receiving the original structured queryable statement input by the user, the data quality detection system can generate a quality rule template based on the original structured query statement. The data quality detection system will first verify whether the original structured query statement is executable through a database engine pre-configured in the system, and generate an executable structured statement based on the verification result and the original structured query statement. The data quality detection system will identify the executable structured statement, determine the target field in the executable structured statement, and replace the target field with a placeholder to generate a quality rule template.

[0059] In specific implementation, when identifying an executable structured statement, the data quality detection system can split the morphemes in the executable structured statement through semantic recognition and determine the semantic information corresponding to the morphemes. The data quality detection system can determine the corresponding placeholders for replacement based on the morphemes and the corresponding semantic information, thereby generating a quality rule template. In addition, the data quality detection system can determine the expected execution result of the corresponding executable structured statement based on the morphemes and the corresponding semantic information.

[0060] Specifically, when the original structured query statement is executable and the running result on the database engine is consistent with the expected running result, the data detection system can output an executable structured query statement based on the original structured query statement. When the original structured query statement is not executable, or the running result of the original structured query statement is inconsistent with the expected running result, the data quality detection system can modify the original structured query statement based on the grammatical rules corresponding to the database engine and generate an executable structured query statement. The database engine can be selected as a database engine such as Spark SQL.

[0061] For example, in complex computing tasks and / or large-scale data sets, distributed computing based on Spark SQL will be more advantageous. The data quality detection system also supports quality detection of the data to be detected in the Spark SQL engine. After receiving the user's original structured query statement, the data quality detection system can debug whether the original structured query statement is executable in the Spark SQL engine, and when the original structured query statement is not executable, or the running result of the original structured query statement is inconsistent with the expected running result, the original SQL executable by each type of database queried in step 1 is adjusted and modified to the syntax supported by Spark SQL to make it compatible with Spark SQL and executable to obtain correct results.

[0062] Taking the application scenario of DM (DaMeng) database as an example, since Spark SQL uses Hive as the parser and execution engine, Spark SQL does not support the SQL syntax of all databases, and may not support or have limited support for some advanced features of DM database. Therefore, when executing SQL scripts of DM database in Spark SQL, you may encounter some unsupported syntax or limited functions specific to DM database. Even after adjusting the script to the syntax supported by Spark SQL, there may be situations where the SQL execution results and the original SQL execution results are different, or the semantic analysis component may report an error when parsing the SQL according to the actual data source type.

[0063] If you need to query the total number of records that match the regular expression of phone numbers in the DAMO database, the corresponding input original structured statement is:

[0064] Select count(1) from “dm anll type”.“rule mode” where regexp like(cast(“phone” as string) ,

[0065] '^0[0-9]{2,3}-[0-9]{7,8}|(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])[0-9]{8}$');

[0066] If you directly execute this statement in Spark SQL, the result of the SQL execution may not be consistent with the result returned by the original SQL execution, because when executing SQL statements in Spark SQL, the field phone is not wrapped with the "" symbol that can be directly executed by the DAMO data engine, but with the ` symbol. Spark SQL cannot recognize the "" symbol. The reference to the column field should use the backquote symbol so that Spark SQL can correctly recognize and parse the column field name. Therefore, it is necessary to convert the original structured statement into the syntax supported by Spark SQL:

[0067] Select count(1) from “dm anll type”.“rule mode” where regexp like(cast(`phone` as string) ,

[0068] '^0[0-9]{2,3}-[0-9]{7,8}|(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])[0-9]{8}$');

[0069] Furthermore, by using placeholders to replace the library name, table name, and column name in the structured statement, the generated structured query statement is:

[0070] Select count(1) from “${database}”.“${tableName}” where regexp like(cast(`${colName}` as string) ,

[0071] '^0[0-9]{2,3}-[0-9]{7,8}|(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])[0-9]{8}$');

[0072] The placeholder template can use semantic analysis to verify the grammatical correctness, thereby generating a corresponding quality rule template.

[0073] For example, when the requirement is to query 50 abnormal data records that do not match the regular expression of the phone number, it is necessary to use a non-subquery or associated query statement to query the results on the Spark SQL engine. When translating the original structured statement into the grammatical content supported by the Spark SQL engine, the regexp_like keyword supported by the DAMO database does not support the not keyword, so regexp_like needs to be transformed into cast(`phone` as string) not regexp to ensure the normal use of the quality rule template.

[0074] As another optional implementation, Figure 2 As shown, the data quality detection system can also generate a quality rule template according to the regular expression after receiving the regular expression input by the user. The regular expression can represent the corresponding data quality rule, and the data quality detection system can fill in the expression filling template preset in the data quality system based on the regular expression to generate the corresponding quality rule template.

[0075] Exemplarily, there are corresponding expression placeholders and rule template placeholders in the expression filling template. The data quality detection system can replace the expression placeholder with the regular expression to generate a quality rule template, or can generate a corresponding structured query statement based on the regular expression, and then replace the generated structured query statement with the corresponding expression placeholder to generate a quality rule template.

[0076] It should be noted that the regular expression filling template can be generated by using an existing quality rule template as a quality rule template sample, or a quality rule template sample input by a user as a reference. The expression filling template is generated by replacing the regular expression in the quality rule template sample with an expression placeholder. Optionally, the data quality detection system can also directly obtain the expression filling template input by the user and save it.

[0077] Step S30: after receiving the quality detection instruction, obtaining the filling data in the quality detection instruction, and determining the quality rule template corresponding to the quality detection instruction;

[0078] In this embodiment, the data quality detection system will save the corresponding quality rule template after generating it, and after receiving the quality detection instruction from the user, determine and call the corresponding quality rule template according to the instruction information in the quality detection quality. In addition, the quality detection instruction also includes corresponding filling data, and the data quality detection system can replace the placeholder in the quality rule template based on the filling data, thereby performing quality detection of the data to be detected.

[0079] Further, as described in step S30, after receiving the quality detection instruction, obtaining the filling data in the quality detection instruction, and before determining the quality rule template corresponding to the quality detection instruction, it also includes:

[0080] Step S31: after generating the quality rule template, determining corresponding input information based on the user interface, and determining template information of the quality rule template according to the input information, wherein the template information includes but is not limited to the name of the quality rule template, data source type, table / field level, and rule dimension;

[0081] Step S32: Associating the template information with the quality rule template.

[0082] In this embodiment, to facilitate the calling of the quality rule template, the data quality detection system will determine the template information corresponding to the quality rule template according to the input information of the user input obtained in the user interface after generating the quality rule template. The template information includes but is not limited to the name of the quality rule template, the data source type, the table / field level, and the rule dimension.

[0083] It should be noted that the name of the quality rule template is used by the data quality detection system to quickly determine the corresponding quality rule template. The data source type is used to indicate the data type that the quality rule template can detect. The table / field level is used to express the granularity of the data that the quality rule template can detect, that is, the degree of precision of data detection. The rule dimension is used to express the database types supported by the quality rule template, such as the big data database Hive, the relational database MySQL, Oracle, Postgrel SQL, DM and other database types.

[0084] Exemplarily, after obtaining the quality inspection instruction, the data quality inspection system will compare the data information to be inspected in the quality inspection instruction with the quality rule information in the quality rule template, determine the quality rule template that meets the inspection requirements of the data to be inspected, and call the quality rule template.

[0085] Step S40: Fill the fill data into the quality rule template, generate a data quality detection script, and detect the data to be detected according to the data quality detection script.

[0086] In this embodiment, the fill data in the quality detection instruction includes but is not limited to the table name and / or field name and / or conditional statement corresponding to the data to be detected. The data quality detection system can fill the fill data into the target field of the quality rule template according to the fill data, generate a data quality detection script, and detect the data to be detected according to the data quality detection script. In the process of running the data quality detection script, the corresponding data to be detected and the detection method of the data to be detected can be quickly determined according to the fill data in the target field.

[0087] Specifically, Figure 2 As shown, the data quality detection system will identify the data type of the fill data and determine the corresponding target placeholder in the quality rule template according to the data type. By replacing the target placeholder with the fill data, the data quality detection system can generate a corresponding data quality detection script, establish a corresponding data quality detection task according to the data quality detection script, and detect the data to be detected based on the data quality detection task.

[0088] Optionally, the data quality detection system can also generate data quality detection rules after filling the fill data into the corresponding quality rule template. In the process of establishing the corresponding data quality detection task, the data quality detection system will first create a corresponding data detection project according to the data quality detection rule, and generate a corresponding data detection task in the data detection project based on the data quality detection script. The data quality detection system will package the data detection project and run the data detection project through the database engine.

[0089] For example, in the process of executing the data inspection task, you can create a Java or Scala project, write a Spark task in the project to run the data quality inspection rules, and write the results back to the specified database. After packaging the project into a JAR package, the data quality inspection system can submit the project task to a single machine or cluster for execution based on the spark-submit command.

[0090] Furthermore, Java or Scala projects can execute SQL in a running relational database through JDBC (Java Database Connectivity). Before executing a query, you need to ensure that a temporary view is successfully created and registered. You can create a temporary view (Temporary View) through the CreateOrReplaceTempView method. When registering a temporary view, you need to parse the target SQL to obtain the corresponding table name, register the table name in the view, and replace all places where the table name is used during the execution with the registered view name. Among them, the process of parsing SQL to obtain the table name is to first parse directly based on the actual database type. If the parsing is successful, the table name can be obtained. If the parsing fails, use the CCJSqlParserUtil tool to parse it, and return if the parsing is successful. If the parsing fails again, it is forced to specify the use of Hive type for parsing. Since the design goal of Spark SQL is to be compatible with Hive, most Hive syntax and functions can still work normally in Spark SQL; you can also use Apache Calcite to customize the parser and rules for parsing SQL, so that you can support specific SQL syntax to parse the corresponding table name, and then register the table name to the temporary view.

[0091] In an embodiment of the present application, the data quality detection system can customize the quality rule template based on regular expressions or executable structured query statements, and quickly generate data quality detection rules through the quality rule module, thereby reducing the frequency of maintenance of data quality rules, thereby reducing the resource cost occupied by data quality rule maintenance and updating.

[0092] Embodiment 2

[0093] Based on the same inventive concept, the present application also provides a second embodiment, referring to Figure 3 , Figure 3 This is a flow chart of the second embodiment of the data quality detection method of the present application.

[0094] In this embodiment, after obtaining the executable structured query statement as described in step S10, based on the preset placeholder replacement rule, the target field in the executable structured query statement is replaced with a placeholder, and after the step of generating a quality rule template, the step further includes:

[0095] Step S11: when the original structured query statement is not executable, or the running result of the original structured query statement is inconsistent with the expected running result, determining the grammatical rule corresponding to the database engine;

[0096] Step S12: determining, by using the statement adjustment model and according to the grammatical rules, points to be adjusted where the original structured query statement does not match the grammatical rules;

[0097] Step S13: adjusting the original structured query statement according to the expected operation result, the grammatical rules, and the points to be adjusted, to generate the executable structured query statement.

[0098] In this embodiment, according to the different execution results of the database engine on the original structured query statement, the data quality detection system can perform corresponding actions. In particular, to ensure that the data quality detection can be performed as expected by the user, when the original structured query statement is not executable or the running result of the original structured query statement is inconsistent with the expected running result, the data quality detection system will adjust the original structured query statement and generate a structured query statement that can be executed by the database engine.

[0099] In specific implementation, the data quality detection system will first obtain the grammatical rules corresponding to the database engine, and detect the original structured query statement according to the grammatical rules to determine the points to be adjusted in the original structured query statement that do not match the database engine. After determining the points to be adjusted, the data quality detection system will adjust the points to be adjusted according to the expected execution structure of the original structured query statement and / or the original structured query statement, the grammatical rules of the database engine, and the generated executable structured query statement, and generate an executable structured query statement.

[0100] Exemplarily, the statement adjustment model can be configured with a corresponding OpenAI model, or other natural language processing models can be used to identify and adjust structured statements. In this case, the statement adjustment model can generate a structured query statement that complies with the grammatical rules of the database engine and replace the statement to be replaced based on the semantic information corresponding to the statement to be replaced, thereby generating an executable structured query statement.

[0101] Optionally, in order to ensure the accuracy of the executable structured query statement generated after adjustment, the data quality detection system may also allow the user to adjust the original structured query statement after marking the points to be adjusted.

[0102] Furthermore, after the data quality detection system completes the adjustment of the original structured query statement, or receives the structured query statement adjusted by the user to the original structured query statement, the data quality system will verify again whether the execution structure of the adjusted structured query statement meets expectations. When the execution result of the adjusted structured query statement meets expectations, the data quality detection system will use the adjusted structured query statement as an executable structured statement. When the execution result does not meet expectations, the data quality detection system will re-adjust the structured query statement, or display a prompt message indicating that the execution result is incorrect in the user interface.

[0103] The embodiment of the present application replaces structured query statements that cannot be recognized by the database engine with structured query statements that can be executed by the database engine according to the grammatical rules of the database engine, without requiring the user to check and adjust the input structured query statements, thereby improving the efficiency of the data quality detection system in generating quality rule templates and improving the compatibility of the data quality detection system.

[0104] Since the system introduced in the second embodiment of the present application is a system used to implement the method of the first embodiment of the present application, based on the method introduced in the first embodiment of the present application, the person skilled in the art can understand the specific structure and deformation of the system, so it is not repeated here. All systems used in the method of the first embodiment of the present application belong to the scope of protection of this application.

[0105] Embodiment 3

[0106] Based on the same inventive concept, the present application also provides a third embodiment, referring to Figure 4 , Figure 4 This is a flow chart of the fourth embodiment of the data quality detection method of the present application.

[0107] In this embodiment, after obtaining the executable structured query statement as described in step S10, based on the preset placeholder replacement rule, the target field in the executable structured query statement is replaced with a placeholder, and before the step of generating the quality rule template, it also includes:

[0108] Step S14: after the template detection module receives the quality rule template, determining the structured query statement to be detected corresponding to the quality rule template;

[0109] Step S15: performing lexical analysis on the structured query statement to be detected to determine corresponding morphemes;

[0110] Step S16: combining the morphemes into corresponding grammatical structures according to the morphemes and the grammatical rules of the database engine, and performing semantic analysis on the grammatical structures;

[0111] Step S17: generating a parse tree of the structured query statement to be detected according to the semantic analysis result, and verifying whether the parse tree complies with a preset parse tree verification rule;

[0112] Step S18: When the parse tree meets the preset parse tree verification rules, the quality rule template is judged to be correct.

[0113] In this embodiment, in order to ensure the availability of the quality rule template and avoid saving the wrong quality rule template, after generating the quality rule template, the data quality detection system will use the template detection module to detect the structured query statement of the quality rule template as the structured query statement to be detected.

[0114] In specific implementation, after the data quality detection system compares the quality rule template and extracts the structured query statement to be detected contained in the quality rule template, it can first perform morpheme splitting of the structured query statement to be detected through lexical analysis to determine the corresponding morphemes. Then, through the grammatical rules of the database engine, the morphemes are combined into corresponding grammatical structures and semantic analysis is performed.

[0115] Furthermore, the data quality detection system will construct a parse tree based on the semantic analysis structure, and verify through the parse tree whether the structured query statement to be detected complies with the grammatical rules of the database engine, thereby determining whether the quality rule template is correct.

[0116] For example, in order to verify whether the SQL script of the quality rule template is correct, the data quality detection system also has a syntax parsing function. The data quality detection system can verify whether the quality rule template is correct based on the functions provided by the open source components Druid SQL semantic analysis and CCJSqlParserUtil tool.

[0117] Furthermore, when verifying the correctness of SQL scripts with placeholders, since this is no longer a standard SQL syntax that can be directly executed in the database, direct verification using Druid SQL semantic analysis or other parsing tools will result in errors. In this case, you can use a third-party library or tool to parse SQL, or use Apache Calcite to customize the SparkSQL parser and rules to support specific SQL syntax parsing.

[0118] Optionally, the data quality detection system can also verify the correctness of the SQL script of the quality rule template SQL through JDBC. After the user uses the rule template and replaces the placeholder to obtain the structured statement that is actually executed, the data quality detection system can execute it through JDBC to obtain the result.

[0119] The embodiment of the present application verifies the function of the quality rule template to ensure the correctness of the quality rule template and improves the stability of the data quality detection process.

[0120] Since the system introduced in the third embodiment of the present application is a system used to implement the method of the first embodiment of the present application, based on the method introduced in the first embodiment of the present application, the person skilled in the art can understand the specific structure and deformation of the system, so it is not repeated here. All systems used in the method of the first embodiment of the present application belong to the scope of protection of the present application.

[0121] Embodiment 4

[0122] Based on the same inventive concept, the present application also provides a fourth embodiment, referring to Figure 5 , Figure 5 This is a flow chart of the fourth embodiment of the data quality detection method of the present application.

[0123] In this embodiment, after the step of filling the filling data into the quality rule template, generating a data quality detection script, and detecting the data to be detected according to the data quality detection script as described in step S40, the following steps are also included:

[0124] Step S41: Obtain the detection result of the data to be detected;

[0125] Step S42: Generate a quality analysis report of the data to be detected based on the detection result of the data to be detected, the data to be detected, and a data quality analysis report template.

[0126] In this embodiment, the data quality detection system will record the target data that does not meet the data quality rules during the detection process of the data to be detected. After the quality detection of the data to be detected is completed, the data quality detection system will fill it into the data quality analysis report template based on the recorded target data and the relationship between the target data and the data to be detected, and generate a quality analysis report for the data to be detected.

[0127] Exemplarily, the data quality detection system will fill in the data volume of the target data, the proportion of the target data in the data to be detected, and the importance and other information into the analysis report template to generate a quality analysis report for the data to be detected.

[0128] During specific implementation, the data quality detection system can also evaluate the overall quality of the data to be detected and provide directions for improving the data quality based on the target data and the connection between the target data and the data to be detected. The data quality detection system can calculate the weight values ​​of the data volume, data volume proportion, and data importance of the data to be detected by the weighted summation method to determine the evaluation value of the overall quality of the data to be detected. At the same time, the data quality detection system can analyze and determine the direction of improvement of the data quality based on the weight value, and provide improvement suggestions to the user. When the evaluation value of the data quality is lower than the preset evaluation value threshold, the data quality detection system will also output an alarm to prompt the user.

[0129] Furthermore, after the data quality detection system generates a quality analysis report of the data to be detected, it will output a user quality report in the user interface, and based on the recorded target data, output a prompt message in the user interface indicating that there is data that does not meet the data quality requirements. The user can modify or delete the target data in the data operation module corresponding to the user interface. Optionally, the system can also remove the target data from the database after recording the target data based on the user's preset instructions.

[0130] Since the system introduced in the second embodiment of the present application is a system used to implement the method of the first embodiment of the present application, based on the method introduced in the first embodiment of the present application, the person skilled in the art can understand the specific structure and deformation of the system, so it is not repeated here. All systems used in the method of the first embodiment of the present application belong to the scope of protection of this application.

[0131] Embodiment 5

[0132] Reference Figure 6 , Figure 6 This is a schematic diagram of the structure of a data quality detection device for the hardware operating environment involved in the embodiment of the present application.

[0133] like Figure 6As shown, the data quality detection device may include: a processor 1001, such as a core processor (Central Processing Unit, CPU), a communication bus 1002, a user interface 1003, a network interface 1004, and a memory 1005. Among them, the communication bus 1002 is used to realize the connection and communication between these components. The user interface 1003 may include a display screen (Display), an input unit such as a keyboard (Keyboard), and the optional user interface 1003 may also include a standard wired interface and a wireless interface. The network interface 1004 may optionally include a standard wired interface and a wireless interface (such as a wireless fidelity (WIreless-FIdelity, WI-FI) interface). The memory 1005 may be a high-speed random access memory (Random Access Memory, RAM) memory, or a stable non-volatile memory (Non-Volatile Memory, NVM), such as a disk memory. The memory 1005 may also be a storage device independent of the aforementioned processor 1001.

[0134] Those skilled in the art will understand that Figure 6 The structure shown in the figure does not constitute a limitation on the data quality detection device, and may include more or less components than those shown in the figure, or combine certain components, or arrange the components differently.

[0135] like Figure 6 As shown, the memory 1005 as a storage medium may include an operating system, a data storage module, a network communication module, a user interface module and a data quality detection program.

[0136] exist Figure 6 In the data quality detection device shown, the network interface 1004 is mainly used for data communication with other devices; the user interface 1003 is mainly used for data interaction with the user; the processor 1001 and the memory 1005 in the data quality detection device of the present application can be set in the data quality detection device, and the data quality detection device calls the data quality detection program stored in the memory 1005 through the processor 1001, and performs the following steps:

[0137] After obtaining the executable structured query statement, based on the preset placeholder replacement rule, the target field in the executable structured query statement is replaced with the placeholder to generate a quality rule template;

[0138] Alternatively, after receiving the regular expression, filling the template based on a preset expression to generate the quality rule template;

[0139] After receiving the quality detection instruction, obtaining the filling data in the quality detection instruction, and determining the quality rule template corresponding to the quality detection instruction;

[0140] The filling data is filled into the quality rule template, a data quality detection script is generated, and the data to be detected is detected according to the data quality detection script.

[0141] Furthermore, the data quality detection device calls the data quality detection program stored in the memory 1005 through the processor 1001, and further performs the following steps:

[0142] After receiving the original structured query statement, verifying whether the original structured query statement is executable through the database engine;

[0143] When the original structured query statement is executable and the running result on the database engine is consistent with the expected running result, outputting the executable structured query statement based on the original structured query statement;

[0144] When the original structured query statement is not executable, or the running result of the original structured query statement is inconsistent with the expected running result, the original structured query statement is modified based on the grammatical rules corresponding to the database engine to generate the executable structured query statement.

[0145] Furthermore, the data quality detection device calls the data quality detection program stored in the memory 1005 through the processor 1001, and further performs the following steps:

[0146] When the original structured query statement is not executable, or the running result of the original structured query statement is inconsistent with the expected running result, determining a grammar rule corresponding to the database engine;

[0147] By using the statement adjustment model, according to the grammatical rules, determining the points to be adjusted where the original structured query statement does not match the grammatical rules;

[0148] According to the expected operation result and the grammatical rule, as well as the point to be adjusted, the original structured query statement is adjusted to generate the executable structured query statement.

[0149] Furthermore, the data quality detection device calls the data quality detection program stored in the memory 1005 through the processor 1001, and further performs the following steps:

[0150] Identify the data type of the filling data, and determine the corresponding target placeholder in the quality rule template according to the data type;

[0151] Replace the target placeholder with the filling data to generate the data quality detection script;

[0152] According to the data quality detection script, a corresponding data quality detection task is established, and the data to be detected is detected based on the data quality detection task.

[0153] Furthermore, the data quality detection device calls the data quality detection program stored in the memory 1005 through the processor 1001, and further performs the following steps:

[0154] Creating a data detection project, and generating corresponding data detection tasks in the data detection project based on the data quality detection script;

[0155] The data detection project is packaged and run through the database engine.

[0156] Furthermore, the data quality detection device calls the data quality detection program stored in the memory 1005 through the processor 1001, and further performs the following steps:

[0157] After the quality rule template is generated, corresponding input information is determined based on a user interface, and template information of the quality rule template is determined according to the input information, wherein the template information includes but is not limited to the name of the quality rule template, data source type, table / field level, and rule dimension;

[0158] The template information is associated with the quality rule template.

[0159] Furthermore, the data quality detection device calls the data quality detection program stored in the memory 1005 through the processor 1001, and further performs the following steps:

[0160] After the template detection module receives the quality rule template, determining the structured query statement to be detected corresponding to the quality rule template;

[0161] Performing lexical analysis on the structured query statement to be detected to determine corresponding morphemes;

[0162] According to the morphemes and the grammatical rules of the database engine, the morphemes are combined into corresponding grammatical structures, and semantic analysis is performed on the grammatical structures;

[0163] Generate a parse tree of the structured query statement to be detected according to the semantic analysis result, and verify whether the parse tree complies with preset parse tree verification rules;

[0164] When the parse tree meets the preset parse tree verification rule, the quality rule template is judged to be correct.

[0165] Furthermore, the data quality detection device calls the data quality detection program stored in the memory 1005 through the processor 1001, and further performs the following steps:

[0166] Obtaining a detection result of the data to be detected;

[0167] A quality analysis report of the data to be detected is generated according to the detection result of the data to be detected, the data to be detected, and a data quality analysis report template.

[0168] In addition, the present application also provides a computer-readable storage medium, which stores the data quality detection program. The data quality detection program can also be executed by a processor to implement the steps of each embodiment of the above-mentioned data quality detection method.

[0169] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present application may adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes.

[0170] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0171] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0172] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0173] It should be noted that in the claims, any reference signs placed between brackets shall not be construed as limiting the claims. The word "comprising" does not exclude the presence of components or steps not listed in the claims. The word "a" or "an" preceding a component does not exclude the presence of a plurality of such components. The present application may be implemented by means of hardware comprising several different components and by means of a suitably programmed computer. In a unit claim enumerating several means, several of these means may be embodied by the same item of hardware. The use of the words first, second, and third etc. does not indicate any order. These words may be interpreted as names.

[0174] Although the preferred embodiments of the present application have been described, those skilled in the art may make other changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications falling within the scope of the present application.

[0175] Obviously, those skilled in the art can make various changes and modifications to the present application without departing from the spirit and scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is also intended to include these modifications and variations.

Claims

1. A data quality detection method, It is characterized in that The data quality detection method comprises the following steps: After receiving the original structured query statement, verify whether the original structured query statement is executable through a database engine pre-configured in the system, and generate an executable structured statement according to the verification result and the original structured query statement, wherein the database engine is a Spark SQL engine; Splitting the morphemes in the executable structured statement through semantic recognition, and determining the semantic information corresponding to the morphemes, and determining the target field and the expected execution result of the executable structured statement according to the morphemes and the corresponding semantic information; When the original structured query statement is executable and the running result on the database engine is consistent with the expected running result, the original structured query statement is output as an executable structured query statement; or, when the original structured query statement is not executable or the running result is inconsistent with the expected running result, the statement adjustment model is used to determine the points to be adjusted where the original structured query statement does not match the grammatical rules according to the grammatical rules corresponding to the database engine, and the original structured query statement is adjusted according to the expected running result and the grammatical rules, as well as the points to be adjusted, to generate the executable structured query statement, which is compatible with Spark SQL and can be executed to obtain correct results, so that the database engine can correctly execute the structured query statement containing the placeholder; After obtaining the executable structured query statement, based on the preset placeholder replacement rule, the target field in the executable structured query statement is replaced with the placeholder to generate a quality rule template; Determining corresponding input information based on the user interface, and determining template information of the quality rule template according to the input information, and associating the template information with the quality rule template; After receiving the quality inspection instruction, obtaining the filling data in the quality inspection instruction, comparing the data to be inspected in the quality inspection instruction with the template information of the quality rule template, and determining and calling the corresponding quality rule template; Identify the data type of the fill data, and determine the corresponding target placeholder in the quality rule template according to the data type, replace the target placeholder with the fill data, generate the data quality detection script, establish the corresponding data quality detection task according to the data quality detection script, and detect the data to be detected based on the data quality detection task.

2. The data quality detection method according to claim 1, It is characterized in that The step of establishing a corresponding data quality detection task according to the data quality detection script, and detecting the data to be detected based on the data quality detection task includes: Creating a data detection project, and generating corresponding data detection tasks in the data detection project based on the data quality detection script; The data detection project is packaged and run through the database engine.

3. The data quality detection method according to claim 1, It is characterized in that Before the step of obtaining the filling data in the quality detection instruction after receiving the quality detection instruction and determining the quality rule template corresponding to the quality detection instruction, the method further includes: After the quality rule template is generated, corresponding input information is determined based on a user interface, and template information of the quality rule template is determined according to the input information, wherein the template information includes a name of the quality rule template, a data source type, a table / field level, and a rule dimension; The template information is associated with the quality rule template.

4. The data quality detection method according to claim 1 further comprises: include: After the template detection module receives the quality rule template, determining the structured query statement to be detected corresponding to the quality rule template; Performing lexical analysis on the structured query statement to be detected to determine corresponding morphemes; According to the morphemes and the grammatical rules of the database engine, the morphemes are combined into corresponding grammatical structures, and semantic analysis is performed on the grammatical structures; Generate a parse tree of the structured query statement to be detected according to the semantic analysis result, and verify whether the parse tree complies with preset parse tree verification rules; When the parse tree meets the preset parse tree verification rule, the quality rule template is judged to be correct.

5. The data quality detection method according to claim 1, It is characterized in that After the step of filling the filling data into the quality rule template, generating a data quality detection script, and detecting the data to be detected according to the data quality detection script, the method further includes: Obtaining a detection result of the data to be detected; According to the test results of the data to be tested and the data to be tested, as well as the analysis report template of the data quality, Generate a quality analysis report for the data to be tested.

6. A data quality detection device, It is characterized in that The data quality detection device comprises: a memory, a processor, and a data quality detection program stored in the memory and executable on the processor, wherein the data quality detection program is configured to implement the steps of the data quality detection method according to any one of claims 1 to 5.

7. A storage medium, It is characterized in that The storage medium stores a data quality detection program, and when the data quality detection program is executed by the processor, the steps of the data quality detection method according to any one of claims 1 to 5 are implemented.