Test method, device and equipment of data warehouse script and storage medium

Abstract

This application discloses a testing method, apparatus, device, and storage medium for data warehouse scripts, relating to the field of data testing technology. The method includes: parsing an acquired script to be tested to extract the table name and data range information of the target data table generated by the script; obtaining the definition information and associated business rule information of the target data table based on the table name; generating at least one first-type detection statement based on the definition information and data range information, and generating at least one second-type detection statement based on the business rule information; and executing the first-type and second-type detection statements to obtain detection results. This application automatically parses the script to obtain the table name and data range, and automatically generates and executes two types of detection statements to obtain detection results, thereby reducing manual operations and improving the testing efficiency and accuracy of data warehouse scripts.

Description

Technical Field

[0001] This application relates to the field of data testing technology, and in particular to a testing method, apparatus, device and storage medium for data warehouse scripts. Background Technology

[0002] In the data warehouse development and testing process, developers process raw data and generate data tables by writing Structured Query Language (SQL) scripts. Testers need to verify the accuracy of the data tables generated by these scripts to ensure that the data quality meets business requirements. Understanding the business logic, result table structure, and data range of the scripts under test is fundamental to conducting testing.

[0003] Currently, testers primarily perform testing manually. The specific process involves first reading and understanding the test script provided by the developers, extracting information such as the generated data tables and their field definitions, and then manually writing multiple SQL queries to check data accuracy. However, this entirely manual approach results in a time-consuming and labor-intensive testing process, especially when repeated executions are required or when dealing with a large number of scripts, making low testing efficiency a major technical bottleneck. Summary of the Invention

[0004] The main objective of this application is to provide a testing method, apparatus, device, and storage medium for data warehouse scripts, aiming to solve the technical problem of low testing efficiency for data warehouse scripts caused by existing testing methods that rely on manual operations.

[0005] To achieve the above objectives, this application proposes a testing method for data warehouse scripts, the method comprising: The obtained test script is parsed to extract the table name of the target data table generated by the test script and the data range information of the target data table; Based on the table name of the target data table, obtain the definition information and associated business rule information of the target data table; Based on the definition information and the data range information, at least one first-type detection statement is generated, and based on the business rule information, at least one second-type detection statement is generated. Execute the first type of detection statement and the second type of detection statement to obtain the detection results.

[0006] In one embodiment, the step of obtaining the definition information and associated business rule information of the target data table based on the table name of the target data table includes: Based on the table name of the target data table, the definition statement of the target data table is queried from the data warehouse code library, and the definition statement is parsed. The field type and constraint conditions of each field in the target data table obtained from the parsing are used as the definition information. Based on the table name of the target data table, at least one business rule associated with the target data table is retrieved from the business rule configuration library as the business rule information. The business rule configuration library stores several business rules associated with different data tables.

[0007] In one embodiment, before the step of searching for at least one business rule associated with the target data table from the business rule configuration library based on the table name of the target data table as the business rule information, the method further includes: Obtain at least one historical data table with the same table name pattern as the target data table, and extract the source data from the historical data table; Perform field-level statistical analysis on the source data to obtain field value distribution characteristic information, and generate at least one candidate business rule based on the field value distribution characteristic information; Calculate the confidence level of each candidate business rule in the source data, where the confidence level is the ratio of the number of data records in the source data that satisfy each candidate business rule to the total number of data records in the corresponding source data; Candidate business rules with a confidence level higher than a preset threshold are written into the business rule configuration library as business rules in the business rule configuration library.

[0008] In one embodiment, the step of generating at least one first-type detection statement based on the definition information and the data range information includes: Based on the field type, generate a feature detection statement for statistically analyzing the data characteristics of each field in the target data table. The data characteristics include the maximum, minimum, and average values ​​of numeric fields, and the length of string fields. Based on the constraints, generate constraint detection statements to detect whether each field in the target data table satisfies the constraints; The data range information is added as a filtering condition to the feature detection statement and the constraint detection statement to generate at least one of the first type of detection statements.

[0009] In one embodiment, the step of generating at least one second-type detection statement based on the business rule information includes: Parse the business rule information to obtain the detection conditions defined in the business rule information; Based on the detection conditions, at least one second-type detection statement is generated for querying records in the target data table that violate the business rules.

[0010] In one embodiment, the step of executing the first type of detection statement and the second type of detection statement to obtain the detection result includes: Execute the first type of detection statement and the second type of detection statement in the preset data warehouse test environment, and obtain the execution results of the first type of detection statement and the second type of detection statement respectively; A data detection report is generated based on the execution results. The data detection report includes the execution status of each detection statement and the corresponding statistical values. Based on the data detection report, the detection statements whose execution status is abnormal are identified, and corresponding abnormal detail query statements are generated based on the detection statements whose execution status is abnormal according to the preset conversion rules. The data detection report and each of the anomaly detail query statements are used as the detection results.

[0011] In one embodiment, the step of parsing the acquired test script and extracting the table name and data range information of the target data table generated by the test script includes: Obtain the source code of the script to be tested from the data warehouse code repository; The source code is parsed to identify data table keywords and conditional filtering keywords. The table name of the target data table is determined based on the keywords of the data table, and the data range information of the target data table is determined based on the filtering keywords of the conditions.

[0012] Furthermore, to achieve the above objectives, this application also proposes a testing apparatus for data warehouse scripts, the apparatus comprising: The script parsing module is used to parse the acquired script to be tested and extract the table name of the target data table generated by the script to be tested and the data range information of the target data table. The detection statement generation module is used to obtain the definition information and associated business rule information of the target data table based on the table name of the target data table; generate at least one first type of detection statement according to the definition information and the data range information, and generate at least one second type of detection statement according to the business rule information; The detection execution module is used to execute the first type of detection statement and the second type of detection statement to obtain the detection results.

[0013] Furthermore, to achieve the above objectives, this application also proposes a data warehouse script testing device, the device comprising: a memory, a processor, and a data warehouse script testing program stored on the memory and executable on the processor, the data warehouse script testing program being configured to implement the steps of the data warehouse script testing method described above.

[0014] In addition, to achieve the above objectives, this application also proposes a storage medium, which is a computer-readable storage medium, on which a test program for a data warehouse script is stored, and when the test program for the data warehouse script is executed by a processor, it implements the steps of the test method for the data warehouse script as described above.

[0015] This application discloses a testing method for a data warehouse script. The method includes: parsing the acquired script to be tested to extract the table name and data range information of the target data table generated by the script; obtaining the definition information and associated business rule information of the target data table based on the table name; generating at least one first-type detection statement based on the definition information and data range information, and generating at least one second-type detection statement based on the business rule information; executing the first-type detection statement and the second-type detection statement to obtain the detection result.

[0016] Because this application can automatically generate and execute detection statements based on the parsing results of the script to be tested, testers are not required to manually read the script, write the detection statements, or execute them manually, thus reducing manual operations in the testing process and improving testing efficiency. Simultaneously, this application obtains specific data range information by parsing the script and uses this data range information as a filtering condition for the detection statements. This allows the generated detection statements to accurately target the data partition or data range processed by the current script, avoiding interference from data generated by other scripts that might be introduced due to detection based solely on table names, thereby improving the accuracy of the detection results. Attached Figure Description

[0017] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0018] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0019] Figure 1 This is a flowchart illustrating the first embodiment of the data warehouse script testing method of this application; Figure 2 This is a flowchart illustrating the second embodiment of the data warehouse script testing method of this application; Figure 3 This is a flowchart illustrating the third embodiment of the data warehouse script testing method of this application; Figure 4 This is a schematic diagram of the entire process of testing the data warehouse script in this application; Figure 5 This is a schematic diagram of the module structure of the test device for the data warehouse script in this application; Figure 6 This is a schematic diagram of the test equipment for the data warehouse script in this application.

[0020] The purpose, features, and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0021] It should be understood that the specific embodiments described herein are merely illustrative of the technical solutions of this application and are not intended to limit this application.

[0022] To better understand the technical solution of this application, a detailed description will be provided below in conjunction with the accompanying drawings and specific implementation methods.

[0023] This application provides a method for testing data warehouse scripts, referring to... Figure 1 , Figure 1 This is a flowchart illustrating the first embodiment of the testing method for the data warehouse script of this application. In this embodiment, the method includes: steps S10~S40: Step S10: Parse the obtained test script and extract the table name of the target data table generated by the test script and the data range information of the target data table.

[0024] It should be noted that the method in this embodiment can be applied in data warehouse development and testing scenarios to automate the testing of Structured Query Language (SQL) scripts written by data warehouse developers. The executing entity of this embodiment can be a computing electronic device with functions such as data processing, network communication, and program execution, such as a server, desktop computer, or cloud computing platform. This embodiment and the following embodiments are described using a data warehouse script testing device (hereinafter referred to as "the device") as an example.

[0025] It should be understood that the script to be tested can be an SQL script file written by data warehouse developers to process raw data and generate result tables. This SQL script file can be stored in the data warehouse code repository and can be uniquely identified by its script name.

[0026] Specifically, the device can receive the name of the script to be tested input by the user, and obtain the corresponding script source code file from the data warehouse code library based on the name, as the script to be tested.

[0027] It should also be noted that after obtaining the script to be tested, the device can parse the script to identify the target data table to be generated by the script and the data range defined by the script. Considering that different SQL scripts may write data to the same data table, and each SQL script may only process a specific range of data (e.g., data from a specific date, data from a specific region, etc.), this embodiment parses the script to be tested and simultaneously obtains the table name and data range of the target data table, so as to accurately locate the data set to be tested in subsequent detection.

[0028] Furthermore, to illustrate in detail how to parse the test script, step S10 specifically includes: steps S101~S103: Step S101: Obtain the source code of the script to be tested from the data warehouse code repository.

[0029] It should be noted that after receiving the script name of the test script input by the user, the device can automatically connect to the data warehouse code repository (referred to as the "data warehouse code repository"), search by script name, and obtain the complete source code text content corresponding to the test script. This source code text content contains all the SQL statements written by the developers.

[0030] Step S102: Perform syntax parsing on the source code to identify data table keywords and conditional filtering keywords in the source code.

[0031] It should be noted that the device can use a Structured Query Language (SCL) parser to perform lexical and syntactic analysis on the source code, traversing the keywords within the source code. Data table keywords can include terms like "create table," "write to overwrite table," and "write to table," used to define or write data tables. Conditional filtering keywords can include terms like "of," "join," and others used to set data filtering conditions.

[0032] For example, if the script to be tested contains the statement "Write to the overlay table, daily sales order summary table, from, original order table, select, all fields, where, order date equals April 9, 2026", the device can recognize the data table keyword "Write to the overlay table" and the conditional filter keyword "where".

[0033] Step S103: Determine the name of the target data table based on the keywords of the data table, and determine the data range information of the target data table based on the filtering keywords of the conditions.

[0034] It should be noted that the device can extract the table name from the position following the keywords in the data table, and use this as the name of the target data table. Simultaneously, the device can extract the conditional expression from the position following the conditional filter keywords, and use this as the data range information for the target data table.

[0035] Using the example above, the device can determine that the target data table is named "Daily Sales Order Summary Table" and the data range information is "Order date equal to April 9, 2026".

[0036] Step S20: Based on the table name of the target data table, obtain the definition information and associated business rule information of the target data table.

[0037] It should be noted that after obtaining the name of the target data table, the device can further obtain the structural information and business constraint information of the target data table in order to generate targeted detection statements in the future.

[0038] The definition information can be used to describe the structural characteristics of the target data table, including the name, field type, field length, and constraints of each field in the table (such as whether nullables are allowed, whether it is a primary key, etc.).

[0039] Specifically, the device can query the definition statement of the target data table from the data warehouse code library based on the table name, and obtain the aforementioned definition information by parsing the definition statement. This definition statement is the statement used when creating the data table.

[0040] Business rule information can describe the business logic constraints that the data in the target data table needs to meet, such as "the value of the order amount field cannot be negative" or "the value of the status field must be within the specified enumeration set".

[0041] Specifically, the device can retrieve the business rule information associated with the target data table from the business rule configuration library based on the table name. The business rule configuration library can be a database that pre-stores the business rules corresponding to various data tables, and these business rules can be pre-configured by business personnel or testers according to business needs.

[0042] Step S30: Generate at least one first-type detection statement based on the definition information and the data range information, and generate at least one second-type detection statement based on the business rule information.

[0043] It should be noted that after obtaining the definition information, business rule information, and data range information of the target data table, the device can automatically generate various detection statements for detecting data quality.

[0044] It should be understood that the first type of detection statement is a standardized detection statement generated based on the structure definition of the data table. This first type of detection statement can be a detection statement, i.e., a data feature SQL, that is, it can be independent of specific business operations and applicable to data tables of different business types. It can include SQL statements for standardized detection content such as detecting data volume, detecting duplicate values ​​in fields, detecting null values ​​in fields, and calculating the maximum, minimum, and average values ​​of fields.

[0045] Specifically, the device can automatically generate corresponding statistical detection statements based on field types (such as numeric, string, and date types) and corresponding constraint detection statements based on constraints (such as NOT NULL constraints and primary key constraints). Finally, the extracted data range information is added as a filtering condition to the above detection statements to ensure that the detection range is consistent with the data range processed by the script under test.

[0046] The second type of detection statement is a business logic detection statement generated based on business rule information. This second type of detection statement can be related to specific business scenarios and is used to verify whether the data meets preset business constraints; that is, it is a business rule detection SQL.

[0047] Specifically, the device can parse the business rule information obtained from the business rule configuration library, extract the detection conditions defined therein, and then generate an SQL statement for querying data table records that violate the detection conditions.

[0048] Step S40: Execute the first type of detection statement and the second type of detection statement to obtain the detection results.

[0049] It should be noted that after generating all the detection statements (first type of detection statements and second type of detection statements), the device can actually execute all the above detection statements in the data warehouse test environment and organize the execution results into a format that can be read and analyzed by testers.

[0050] Furthermore, to specifically illustrate how to execute the detection statement to obtain the detection result, step S40 specifically includes: steps S401~S404: Step S401: Execute the first type of detection statement and the second type of detection statement in the preset data warehouse test environment, and obtain the execution results of the first type of detection statement and the second type of detection statement respectively.

[0051] It should be noted that the default data warehouse test environment can be an independent database environment isolated from the production environment, used to perform data testing tasks.

[0052] The device can submit all generated Type I and Type II detection statements to the test environment for batch execution and collect the execution results returned by each detection statement.

[0053] Specifically, for detection statements used to query abnormal records, the execution result can include the data records returned by the query; for detection statements used to analyze statistical data, the execution result can include statistical values ​​(such as count results, maximum value, minimum value, etc.).

[0054] Step S402: Generate a data detection report based on the execution results. The data detection report includes the execution status of each detection statement and the corresponding statistical values.

[0055] It should be understood that the device can generate corresponding test items based on the execution status of each test statement, and list them one by one in the data test report. The execution status can include two states: "pass" and "fail".

[0056] For example, if the number of records returned after a certain detection statement is executed is 0, or the statistical value meets the preset normal range, its execution status can be marked as "passed"; if the number of records returned is greater than 0, or the statistical value exceeds the preset normal range, its execution status can be marked as "failed".

[0057] Step S403: Based on the data detection report, determine the detection statements whose execution status is abnormal, and generate corresponding abnormal detail query statements based on the detection statements whose execution status is abnormal according to the preset conversion rules.

[0058] It should be noted that the abnormal status can correspond to the "failed" execution status mentioned above. The device can traverse the data detection report and identify all detection statements with an execution status of "failed". For each detection statement identified as abnormal, the device can convert it into an SQL statement for extracting abnormal data details, i.e., a data feature detail query SQL, according to preset conversion rules.

[0059] For example, for the detection statement "Query all records in the 'Daily Sales Order Summary Table' where 'Amount' is less than 0, and the order date is equal to 'April 9, 2026'", since it is itself a detailed query statement, the device can directly treat it as an abnormal detailed query statement.

[0060] If the execution result of the detection statement "Count the total number of data entries in the 'Daily Sales Order Summary Table' where the order date is equal to 'April 9, 2026'" is 0 (i.e., the data volume is empty), the device can convert it to "Query all fields in the 'Daily Sales Order Summary Table' where the order date is equal to 'April 9, 2026'" to extract all data for manual review.

[0061] Step S404: Use the data detection report and each of the anomaly detail query statements as the detection result.

[0062] In practice, the device can output both the generated data detection report and the anomaly detail query statement. Testers can review the data detection report to understand the overall detection situation and directly copy and execute the anomaly detail query statement to obtain specific anomaly data records for further manual analysis.

[0063] This embodiment automatically parses the test script, extracts the table name and data range information of the target data table, and automatically generates detection statements based on the table definition information and business rule information. This avoids testers manually reading the script and writing detection statements, thereby reducing manual operations in the testing process and improving testing efficiency. Simultaneously, by extracting data range information from the test script and using it as a filtering condition for the detection statements, this embodiment ensures that the detection statements accurately target the data range processed by the current script, avoiding interference from data generated by other scripts that might be introduced when detection is based solely on table names, thus improving the accuracy of the detection results. Furthermore, by automatically generating anomaly detail query statements from the detection results, this embodiment further reduces the workload of testers manually writing troubleshooting statements, enhancing the automation level of the testing process.

[0064] Based on the first embodiment of this application, in the second embodiment of this application, the content that is the same as or similar to that in the first embodiment described above can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 2 , Figure 2 This is a flowchart illustrating the second embodiment of the testing method for the data warehouse script in this application.

[0065] In this embodiment, to specifically illustrate how to obtain definition information and business rule information based on the table name of the target data table so as to generate various detection statements in the subsequent process, step S20 specifically includes: steps S201~S202: Step S201: Query the definition statement of the target data table from the data warehouse code library according to the table name of the target data table, parse the definition statement, and use the field type and constraint conditions of each field in the target data table obtained from the parsing as the definition information.

[0066] It should be noted that, in addition to storing data processing scripts, the data warehouse code repository also stores the definition statements for each data table. These definition statements are SQL statements used to create data tables, such as the "create table" statement.

[0067] The above definition statement defines the specific structure of the data table, including which fields the data table contains, what the name of each field is, what type of data each field stores (such as integer, string, date, decimal, etc.), what the length limit of each field is, and whether each field has any constraints (such as not being null, being unique, etc.).

[0068] In practice, the device can search the data warehouse code library based on the table name of the target data table to find the definition statement corresponding to that table name. After obtaining the definition statement, the device then uses an SQL parser to parse the syntax of the definition statement, extracting the field information declared in the definition statement line by line. The parser can identify the name, data type, data length, whether nullables are allowed, and whether it is a primary key, among other attributes, of each field in the definition statement. Finally, by summarizing the parsing results, the definition information of the target data table is obtained.

[0069] For example, if the target data table is named "Daily Sales Order Summary Table", and its definition statement contains field declarations such as "Order Identifier; Integer; Cannot be empty", "Product Name; String; Length 50", "Amount; Decimal; Length 10, retain 2 decimal places", and "Order Date; Date", then the definition information obtained after device parsing includes: there is an integer field named "Order Identifier" that cannot be empty, there is a string field named "Product Name" with a maximum length of 50, there is a decimal field named "Amount" with a total length of 10 digits, of which 2 are decimals, and there is a date field named "Order Date".

[0070] Step S202: Based on the table name of the target data table, search the business rule configuration library for at least one business rule associated with the target data table as the business rule information. The business rule configuration library stores several business rules associated with different data tables.

[0071] It should be noted that the business rule configuration library is a pre-built database that stores the business rules corresponding to various data tables. Business rules are constraints pre-configured by business personnel or testers based on actual business needs, describing the business logic that the data in the data tables should satisfy.

[0072] For example, for the sales order table, you can configure a business rule that "the order amount cannot be negative"; for the user information table, you can configure a business rule that "the user's age must be between 0 and 150"; for the status field, you can configure a business rule that "the status value is one of 'success', 'failure', or 'processing'".

[0073] Specifically, the device can search the business rule configuration library based on the table name of the target data table to find business rules that are related to that table name. Each business rule can be stored in a structured or semi-structured format, such as "field name + comparison operator + threshold" or "conditional expression". The device will summarize all the related business rules found as the business rule information for the target data table.

[0074] For example, if the target data table is named "Daily Sales Order Summary Table", the rules associated with this table in the business rule configuration library may include: "Amount is greater than or equal to 0", "Status belongs to the set {'Success', 'Failure', 'Processing'}", "Order date cannot be later than the current system date", etc.

[0075] Furthermore, to be more specific, and to ensure that the business rule configuration library can provide sufficient business rule information for the target data table, candidate business rules can be automatically mined based on historical data and added to the business rule configuration library. Therefore, before step S202, the following steps are also included: steps S01~S04: Step S01: Obtain at least one historical data table with the same table name pattern as the target data table, and extract the source data from the historical data table.

[0076] It's important to note that in a real-world data warehouse production environment, different data tables are typically managed according to certain naming conventions. For example, data tables partitioned by date can be named in the form of "Daily Sales Order Summary Table_20260101" or "Daily Sales Order Summary Table_20260102," etc. These data tables share the same naming pattern (i.e., the same prefix).

[0077] In addition, data tables at different levels can be named "Original Order Table", "Detailed Order Table", and "Summary Order Table", meaning that data tables at the same level under different business types can also have similar naming patterns.

[0078] It should be understood that if the target data table to be tested is being created for the first time or has not yet accumulated enough historical data, the target data table itself may not have any historical data available for analysis. However, since other historical data tables with the same table name pattern or similar naming rules as the target data table may already exist and have accumulated a large amount of historical data, and these historical data tables are usually the same or highly similar to the target data table in structure, the data patterns of these historical data tables can be used as a basis for mining candidate business rules.

[0079] In practical implementation, the device can extract the naming pattern of the target data table based on its table name (e.g., removing date suffixes, removing hierarchical identifiers, etc.), and then retrieve historical data tables in the data warehouse that match the naming pattern. The retrieved historical data tables can be one or more. The device reads the stored source data from these historical data tables as the basis for subsequent analysis.

[0080] For example, if the target data table is named "Daily Sales Order Summary Table_20260103", the device can extract its table name pattern as "Daily Sales Order Summary Table", and then retrieve historical data tables such as "Daily Sales Order Summary Table_202600101" and "Daily Sales Order Summary Table_20260412". The device extracts source data from the above historical data tables for subsequent analysis.

[0081] Step S02: Perform field-level statistical analysis on the source data to obtain field value distribution characteristic information, and generate at least one candidate business rule based on the field value distribution characteristic information.

[0082] It should be noted that after obtaining the source data from the historical data table, the device can perform statistical analysis on each field in the source data to discover hidden patterns in the data. Field-level statistical analysis is an independent analysis performed on each field to identify the value distribution characteristics of that field.

[0083] For numeric fields, the device can calculate statistics such as the minimum, maximum, average, median, and standard deviation of the field, and observe whether the data consistently falls within a certain interval. For example, if a numeric field has never shown a negative number in historical data, a candidate rule can be generated: "The value of this field is greater than or equal to zero."

[0084] For string fields, the device can count the number of unique values ​​and the frequency of each value. If a string field only has a limited number of values ​​in historical data, such as "success", "failure", and "processing", then a candidate rule can be generated: "The value of this field is one of 'success', 'failure', and 'processing'".

[0085] For date fields, the device can analyze features such as whether the field's values ​​are consecutive and whether there are null values. For example, if a date field has not had any null values ​​in historical data, a candidate rule "this field cannot be null" can be generated.

[0086] In addition, the device can also analyze the relationships between fields. For example, if there is a clear dependency between two fields (such as field B must have a certain value if field A has a certain value), the device can also generate cross-field candidate rules based on this relationship.

[0087] For example, analyzing the "Amount" field in the "Daily Sales Order Summary Table_20260414" reveals that the value of this field in all historical records is greater than or equal to zero. Therefore, the device can generate the candidate rule "Amount is greater than or equal to zero". Analyzing the "Status" field in the same table reveals that the value of this field only exists in three forms: "Success", "Failure", and "Processing". Therefore, the device can generate the candidate rule "Status belongs to the set {'Success', 'Failure', 'Processing'}".

[0088] Step S03: Calculate the confidence level of each candidate business rule in the source data. The confidence level is the ratio of the number of data records in the source data that satisfy each candidate business rule to the total number of data records in the source data.

[0089] It should be noted that since the aforementioned candidate business rules are based on patterns derived from historical data statistics, and historical data may contain random cases or outliers, some patterns may not be true business constraints. For example, a numerical field may be positive in 99% of the historical data, but negative in 1% of the data. In this case, the field being "positive" may simply be a common occurrence, rather than a business rule that must be strictly followed. Therefore, to distinguish between "general patterns" and "mandatory constraints," the device needs to calculate the confidence level of each candidate rule.

[0090] Understandably, the confidence score is calculated as follows: Confidence score = Number of data records that satisfy the candidate rule / Total number of records in the source data.

[0091] The ratios mentioned above reflect the general applicability of candidate rules in historical data. The closer the ratio is to 1, the more commonly the candidate rule holds true in historical data, and the more likely it is to be a genuine business constraint; the lower the ratio, the more counterexamples there are in historical data, and it may just be a coincidence.

[0092] In a practical implementation, the device can iterate through each generated candidate rule, count the number of data records in the source data that satisfy the candidate rule for each candidate rule, and then divide by the total number of records in the source data to obtain the confidence level of the candidate rule.

[0093] For example, for the candidate rule "amount greater than or equal to zero", if the number of records with an amount greater than or equal to zero in the device statistics source data is 1000 and the total number of records in the source data is 1000, then the confidence level is 1000 / 1000=100%; for the candidate rule "amount less than 1000", if the number of records with an amount less than 1000 in the device statistics source data is 998 and the total number of records in the source data is 1000, then the confidence level is 998 / 1000=99.8%; for the candidate rule "amount equal to zero", if the number of records with an amount equal to zero in the device statistics source data is 10 and the total number of records in the source data is 1000, then the confidence level is 10 / 1000=1%.

[0094] Step S04: Write the candidate business rules with a confidence level higher than a preset threshold into the business rule configuration library, so as to serve as the business rules in the business rule configuration library.

[0095] It should be noted that a confidence threshold can be preset in the device. This confidence threshold can be adjusted according to actual business needs, for example, set to 95%, 99%, or even 100%. The device can then consider candidate rules with a confidence level higher than this threshold as eligible business rules. Conversely, candidate rules with a confidence level lower than or equal to the preset threshold can be discarded and not written to the business rule configuration library.

[0096] It should be understood that for candidate rules with a confidence level higher than a preset threshold, the device can write them into the business rule configuration library and establish a relationship with the target data table. Thus, when the device executes step S202, these rules are used as the business rules searched for to generate the second type of detection statement.

[0097] For example, if the preset threshold is 95%, the device can write the candidate rule "amount greater than or equal to zero" with a confidence level of 100% into the business rule configuration library; the device can also write the candidate rule "amount less than 1000" with a confidence level of 99.8% into the business rule configuration library; and the device will discard the candidate rule "amount equal to zero" with a confidence level of 1%.

[0098] Understandably, the device can automatically extract reliable business rules from historical data and add them to the business rule configuration library without human intervention. This reduces the workload of manual rule configuration, while also discovering implicit rules that may have been overlooked by humans, thus improving the rule coverage and comprehensiveness of testing in the business rule configuration library.

[0099] This embodiment automatically obtains the structured definition information of the target data table by retrieving definition statements from the data warehouse code repository and parsing them to obtain field types and constraints, providing a data foundation for generating the first type of detection statements. Simultaneously, this embodiment obtains the business constraint information of the target data table by searching for business rules in the business rule configuration library, providing a basis for generating the second type of detection statements. Furthermore, this embodiment adds an automatic rule mining step based on historical data before searching for business rules. It can automatically learn data patterns from historical data tables with the same table name pattern as the target data table, generate candidate business rules, and write high-confidence rules into the configuration library after confidence evaluation. This achieves automatic discovery and supplementation of business rules, reducing the workload of manual configuration and improving the comprehensiveness of rule coverage.

[0100] Based on the first and second embodiments of this application, in the third embodiment of this application, the content that is the same as or similar to the first and second embodiments described above can be referred to the above description, and will not be repeated hereafter. Based on this, please refer to... Figure 3 , Figure 3 This is a flowchart illustrating the third embodiment of the testing method for the data warehouse script in this application.

[0101] In this embodiment, to specifically illustrate how to generate the two types of detection statements respectively, step S30 includes: steps S301~S305: Step S301: Based on the field type, generate a feature detection statement for statistically analyzing the data characteristics of each field in the target data table. The data characteristics include the maximum, minimum, and average values ​​of numeric fields, and the length of string fields.

[0102] It should be noted that the definition information of the target data table includes the field type of each field. Different field types correspond to different data feature statistical methods. The device can automatically select the appropriate statistical template based on the field type and generate the corresponding feature detection statement.

[0103] For numeric fields (such as integers and decimals), the device can generate test statements to calculate the maximum, minimum, and average values ​​of the field. These statistical values ​​can help testers understand the data distribution of the field and identify whether there are abnormally large, small, or unreasonable data values. For example, if the maximum value of a numeric field far exceeds the normal business range, there may be data errors.

[0104] Specifically, the device can generate detection statements in the following forms: "Query the maximum value of a field in the target data table", "Query the minimum value of a field in the target data table", "Query the average value of a field in the target data table".

[0105] For string fields (such as text and character types), the device can generate test statements to calculate the string length of the field, including the maximum length, minimum length, and average length. These statistical values ​​can help testers understand the data filling situation of the field and discover whether there is data with abnormal length (e.g., a field is generally 10 characters long, but some records reach 100 characters).

[0106] Specifically, the device can generate detection statements in the form of: "Query the maximum string length of a certain field in the target data table" or "Query the minimum string length of a certain field in the target data table".

[0107] For date fields, the device can generate test statements to calculate the earliest and latest dates in the field, thereby helping testers understand whether the time range of the data meets expectations.

[0108] For example, if the definition information of the target data table contains a numeric field named "Amount", the device can generate a feature detection statement: "Query the maximum, minimum, and average values ​​of the Amount field in the target data table". If the definition information contains a string field named "Product Name", the device can generate a feature detection statement: "Query the maximum string length of the Product Name field in the target data table".

[0109] Step S302: Based on the constraints, generate a constraint detection statement to detect whether each field in the target data table satisfies the constraints.

[0110] It should be noted that the definition information of the target data table also includes constraints for each field. Constraints are rules that restrict the values ​​of fields, such as NOT NULL constraints (the field cannot be null), DUAL constraints (the field's value must be unique within the table and cannot be duplicated), and PRIMARY KEY constraints (the field serves as the table's primary key and cannot be null or duplicated). These constraints can be mandatory requirements at the data table structure level, and data that violates these constraints can be considered to have data quality issues.

[0111] The device can generate corresponding constraint detection statements based on different constraint types, and then query abnormal records in the target data table that do not meet the constraints based on the constraint detection statements, so that testers can locate the problem.

[0112] For NOT NULL constraints, the device can generate a detection statement to query records where the field is null. Specifically, the device can generate a detection statement in the form of: "Query all records in the target data table where a certain field is null".

[0113] For unique constraints or primary key constraints, the device can generate a detection statement to query records where the field contains duplicate values. Specifically, the device can generate a detection statement in the form of: "Query records in the target data table where a certain field contains duplicate values, and count the number of times each value appears."

[0114] For example, if the definition information of the target data table contains a field named "order identifier" and the field is set with a non-null constraint and a unique constraint, the device can generate constraint detection statements: "Query the records in the target data table where the order identifier field is empty", and "Query the records in the target data table where the order identifier field has duplicate values ​​and display the number of times each order identifier appears".

[0115] Step S303: Add the data range information as a filtering condition to the feature detection statement and the constraint detection statement to generate at least one of the first type of detection statements.

[0116] It should be noted that the data range information mentioned above limits the range of data processed by the script under test. For example, if the data range information is "order date equals January 1, 2026", it indicates that the script under test processed data for January 1, 2026, while the target data table may also contain data for other dates (generated by other scripts). Therefore, by adding a data range filter, it is possible to prevent the generated detection statement from detecting the entire target data table, thereby preventing data generated by other scripts from being included in the detection range, which would cause the detection results to fail to accurately reflect the problems of the current script.

[0117] Therefore, the device can add data range information as a filtering condition to the aforementioned generated feature detection and constraint detection statements. After adding the filtering condition, the obtained detection statement can perform detection only on the subset of data processed by the script to be detected, thereby ensuring the accuracy and specificity of the detection results.

[0118] Specifically, the device can parse the conditional expressions in the data range information and append them to the filtering condition part of the original detection statement. If the original detection statement (feature detection statement and constraint detection statement) already has filtering conditions, the data range information can be logically combined with the original conditions (usually using "AND" logic).

[0119] For example, if the feature detection statement "Query the maximum, minimum and average values ​​of the amount field in the target data table" has been generated, and the data range information is "Order date equals January 1, 2026", then after the device adds the data range information as a filter condition, the first type of detection statement generated is: "Query the maximum, minimum and average values ​​of the amount field in the target data table, where the order date equals January 1, 2026".

[0120] In practice, the device can automatically generate a set of first-class detection statements based on the field types, constraints, and data range information of the target data table through the above steps S301 to S303.

[0121] Step S304: Parse the business rule information to obtain the detection conditions defined in the business rule information.

[0122] It should be noted that the business rule information stored in the business rule configuration library describes the business logic constraints that data should meet. Each business rule can contain a detection condition, which describes the data characteristics that violate the business rule. Business rule information can be stored in various forms, such as structured data (containing field names, comparison operators, thresholds, etc.) or conditional expression strings.

[0123] Specifically, the device can parse the acquired business rule information and extract the defined detection conditions. For structured business rules, the device can directly read the values ​​of each field and combine them into a conditional expression; for rules stored in string form, the device can use a rule parser to parse them and identify the components of the conditional expression.

[0124] For example, if the business rule information contains a rule "the amount cannot be negative", the device can parse and obtain the detection condition "the value of the amount field is less than 0". If the business rule information contains a business rule "the status can only be one of success, failure, or processing", the device can parse and obtain the detection condition "the value of the status field is not in the set {success, failure, processing}".

[0125] Step S305: Based on the detection conditions, generate at least one second-type detection statement for querying records in the target data table that violate the business rule information.

[0126] It should be noted that after obtaining the detection conditions of the business rules, the device can generate corresponding detection statements based on these conditions. The second type of detection statement can be used to query records in the target data table that violate the business rules, thus assisting testers in locating problems.

[0127] It should be understood that since the detection conditions themselves are used to describe abnormal data, the device can directly use these detection conditions as filtering conditions for the query statement. Specifically, the device can generate a second type of detection statement in the form of: "Query all records in the target data table that satisfy the detection conditions." Here, the detection conditions are the conditional expressions obtained from the preceding steps.

[0128] In addition, similar to the first type of detection statement, the device can also add the aforementioned extracted data range information as a filtering condition to the second type of detection statement to ensure that the detection range is consistent with the data range processed by the current script.

[0129] For example, if the detection condition obtained from the aforementioned parsing is "the value of the status field is not in the set {success, failure, processing}", and the data range information is "the order date is equal to April 9, 2026", then the second type of detection statement generated by the device can be: "Query the records in the target data table whose status field value is not within success, failure, or processing, where the order date is equal to April 9, 2026".

[0130] In practice, the device can automatically generate a second type of detection statement set based on business rule information through steps S304 to S305.

[0131] This embodiment automatically generates feature detection statements based on field types, enabling statistical detection of maximum, minimum, and average values ​​for numeric fields, and length detection for string fields, thus comprehensively understanding the data distribution characteristics of each field in the target data table. By automatically generating constraint detection statements based on constraints, it can detect violations at the data table structure level, such as non-null constraints and unique constraints, ensuring that the data meets the basic requirements of the table structure. By adding data range information as a filtering condition to various detection statements, it ensures that the detection range accurately corresponds to the data range processed by the current test script, avoiding interference from data generated by other scripts. Furthermore, by parsing business rule information and generating detection statements for querying records that violate business rules, it can automatically complete data quality detection at the business logic level, further reducing the workload of manually writing detection statements.

[0132] Furthermore, you can also refer to this section. Figure 4 The entire process of testing the data warehouse scripts in this application is described. Figure 4 This is a schematic diagram of the entire process of testing the data warehouse script in this application.

[0133] Depend on Figure 4 As can be seen, when starting the test, the script to be tested can be imported from the data warehouse code repository by the script name of the script to be tested, and the imported script to be tested can be checked for consistency, that is, to determine whether the script name exists in the data warehouse code repository, whether the version matches, and whether the dependencies are complete.

[0134] If the check result is a mismatch, the process will terminate abnormally; if the check result is a match, the subsequent steps will continue.

[0135] After the consistency check passes, the device retrieves the source code of the script to be tested from the data warehouse code repository, parses the script, and extracts the table name of the target data table generated by the script and the data range information of the table (indicated by the conditional filtering keywords).

[0136] Next, the device retrieves the definition statement of the extracted target data table from the data warehouse code library based on the table name, parses the definition statement, and generates data feature SQL.

[0137] Meanwhile, the device can also extract business rules related to the target data table from the business rule configuration library based on the table name of the target data table, and generate business rule detection SQL based on the business rules.

[0138] After the above two types of detection statements are generated, the device submits the data feature SQL and business rule detection SQL to the data warehouse test environment for batch execution.

[0139] After execution, the device can generate data feature detection results (the execution results of the two types of detection statements) and generate data feature detail query SQL based on the detection results.

[0140] Finally, the equipment outputs the above test results and detailed data feature query SQL for testers to perform manual analysis and confirmation.

[0141] The testing method for the data warehouse script in this application automatically parses the script to obtain table names and data ranges, automatically generates and executes data feature detection statements and business rule detection statements, and then produces detection results and detailed query statements, reducing manual operations and improving testing efficiency and accuracy.

[0142] It should be noted that the above examples are only for understanding this application and do not constitute a limitation on the testing method of the data warehouse script of this application. Any simple modifications based on this technical concept are within the protection scope of this application.

[0143] In addition, this application also provides a testing device for data warehouse scripts, such as... Figure 5 As shown, Figure 5 This is a schematic diagram of the module structure of the test device for the data warehouse script in this application. Then, by... Figure 5 It is known that the device includes: The script parsing module 501 is used to parse the acquired script to be tested and extract the table name of the target data table generated by the script to be tested and the data range information of the target data table. The detection statement generation module 502 is used to obtain the definition information and associated business rule information of the target data table based on the table name of the target data table; generate at least one first type of detection statement according to the definition information and the data range information, and generate at least one second type of detection statement according to the business rule information. The detection execution module 503 is used to execute the first type of detection statement and the second type of detection statement to obtain the detection result.

[0144] This embodiment's device automatically generates and executes detection statements based on the parsing results of the script to be tested, eliminating the need for testers to manually read the script, write the detection statements, and execute them manually. This reduces manual operations during the testing process and improves testing efficiency. Furthermore, this application obtains specific data range information by parsing the script and uses this data range information as a filtering condition for the detection statements. This allows the generated detection statements to accurately target the data partition or data range processed by the current script, avoiding interference from data generated by other scripts that might be introduced due to detection based solely on table names, thereby improving the accuracy of the detection results.

[0145] This application also provides a data warehouse script testing device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to execute the data warehouse script testing method in the first embodiment described above.

[0146] The following is for reference. Figure 6 , Figure 6 This is a schematic diagram of the structure of the test device for the data warehouse script in this application. The test device for the data warehouse script in this application may include, but is not limited to, mobile terminals such as mobile phones, laptops, digital broadcast receivers, personal digital assistants (PDAs), tablet computers (PADs), portable media players (PMPs), and fixed terminals such as digital TVs and desktop computers. Figure 6 The test device for the data warehouse script shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0147] like Figure 6As shown, the test device for data warehouse scripts may include a processing unit 1001 (e.g., a central processing unit, a graphics processing unit, etc.), which can perform various appropriate actions and processes according to a program stored in read-only memory (ROM) 1002 or a program loaded from storage device 1003 into random access memory (RAM) 1004. The RAM 1004 also stores various programs and data required for the operation of the test device for data warehouse scripts. The processing unit 1001, ROM 1002, and RAM 1004 are interconnected via a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Typically, the following systems can be connected to the I / O interface 1006: input devices 1007 including, for example, touchscreens, touchpads, keyboards, mice, image sensors, microphones, accelerometers, gyroscopes, etc.; output devices 1008 including, for example, liquid crystal displays (LCDs), speakers, vibrators, etc.; storage devices 1003 including, for example, magnetic tapes, hard disks, etc.; and communication devices 1009. Communication device 1009 allows the test equipment for data warehouse scripts to communicate wirelessly or wiredly with other devices to exchange data. While the figure shows test equipment for data warehouse scripts with various systems, it should be understood that implementation or possession of all the systems shown is not required. More or fewer systems may be implemented alternatively.

[0148] The data warehouse script testing device provided in this application, employing the data warehouse script testing method described in the above embodiments, can solve the technical problems of data warehouse script testing. Compared with the prior art, the beneficial effects of the data warehouse script testing device provided in this application are the same as those of the data warehouse script testing method provided in the above embodiments, and other technical features in this data warehouse script testing device are the same as those disclosed in the previous embodiment method, and will not be repeated here.

[0149] This application also provides a computer-readable storage medium having computer-readable program instructions (i.e., a computer program) stored thereon, the computer-readable program instructions being used to execute the test method of the data warehouse script in the above embodiments.

[0150] The computer-readable storage medium provided in this application may be, for example, a USB flash drive, but is not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this embodiment, the computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, system, or device. The program code contained on the computer-readable storage medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, RF (Radio Frequency), etc., or any suitable combination thereof.

[0151] The readable storage medium provided in this application is a computer-readable storage medium that stores computer-readable program instructions (i.e., a computer program) for executing the above-described data warehouse script testing method, thereby solving the technical problems of the data warehouse script testing method. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in this application are the same as the beneficial effects of the data warehouse script testing method provided in the above embodiments, and will not be repeated here.

[0152] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or system that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or system. Unless otherwise specified, an element defined by the phrase "comprising one..." does not exclude the presence of other elements in the process, method, article, or system that includes that element.

[0153] The above embodiment numbers are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments. They are only some embodiments of this application and do not limit the scope of this application. All equivalent structural transformations made under the technical concept of this application and using the content of this application specification and drawings, or direct / indirect applications in other related technical fields, are included within the protection scope of this application.

Claims

1. A method for testing data warehouse scripts, characterized in that, The method includes: The obtained test script is parsed to extract the table name of the target data table generated by the test script and the data range information of the target data table; Based on the table name of the target data table, obtain the definition information and associated business rule information of the target data table; Based on the definition information and the data range information, at least one first-type detection statement is generated, and based on the business rule information, at least one second-type detection statement is generated. Execute the first type of detection statement and the second type of detection statement to obtain the detection results.

2. The method as described in claim 1, characterized in that, The step of obtaining the definition information and associated business rule information of the target data table based on the table name of the target data table includes: Based on the table name of the target data table, the definition statement of the target data table is queried from the data warehouse code library, and the definition statement is parsed. The field type and constraint conditions of each field in the target data table obtained from the parsing are used as the definition information. Based on the table name of the target data table, at least one business rule associated with the target data table is retrieved from the business rule configuration library as the business rule information. The business rule configuration library stores several business rules associated with different data tables.

3. The method as described in claim 2, characterized in that, Before the step of searching for at least one business rule associated with the target data table from the business rule configuration library based on the table name of the target data table as the business rule information, the method further includes: Obtain at least one historical data table with the same table name pattern as the target data table, and extract the source data from the historical data table; Perform field-level statistical analysis on the source data to obtain field value distribution characteristic information, and generate at least one candidate business rule based on the field value distribution characteristic information; Calculate the confidence level of each candidate business rule in the source data, where the confidence level is the ratio of the number of data records in the source data that satisfy each candidate business rule to the total number of data records in the corresponding source data; Candidate business rules with a confidence level higher than a preset threshold are written into the business rule configuration library as business rules in the business rule configuration library.

4. The method as described in claim 2, characterized in that, The step of generating at least one first-type detection statement based on the definition information and the data range information includes: Based on the field type, generate a feature detection statement for statistically analyzing the data characteristics of each field in the target data table. The data characteristics include the maximum, minimum, and average values ​​of numeric fields, and the length of string fields. Based on the constraints, generate constraint detection statements to detect whether each field in the target data table satisfies the constraints; The data range information is added as a filtering condition to the feature detection statement and the constraint detection statement to generate at least one of the first type of detection statements.

5. The method as described in claim 4, characterized in that, The step of generating at least one second-type detection statement based on the business rule information includes: Parse the business rule information to obtain the detection conditions defined in the business rule information; Based on the detection conditions, at least one second-type detection statement is generated for querying records in the target data table that violate the business rules.

6. The method as described in claim 1, characterized in that, The step of executing the first type of detection statement and the second type of detection statement to obtain the detection result includes: Execute the first type of detection statement and the second type of detection statement in the preset data warehouse test environment, and obtain the execution results of the first type of detection statement and the second type of detection statement respectively; A data detection report is generated based on the execution results. The data detection report includes the execution status of each detection statement and the corresponding statistical values. Based on the data detection report, the detection statements whose execution status is abnormal are identified, and corresponding abnormal detail query statements are generated based on the detection statements whose execution status is abnormal according to the preset conversion rules. The data detection report and each of the anomaly detail query statements are used as the detection results.

7. The method as described in claim 1, characterized in that, The step of parsing the acquired test script and extracting the table name and data range information of the target data table generated by the test script includes: Obtain the source code of the script to be tested from the data warehouse code repository; The source code is parsed to identify data table keywords and conditional filtering keywords. The table name of the target data table is determined based on the keywords of the data table, and the data range information of the target data table is determined based on the filtering keywords of the conditions.

8. A testing device for data warehouse scripts, characterized in that, The device includes: The script parsing module is used to parse the acquired script to be tested and extract the table name of the target data table generated by the script to be tested and the data range information of the target data table. The detection statement generation module is used to obtain the definition information and associated business rule information of the target data table based on the table name of the target data table; generate at least one first type of detection statement according to the definition information and the data range information, and generate at least one second type of detection statement according to the business rule information; The detection execution module is used to execute the first type of detection statement and the second type of detection statement to obtain the detection results.

9. A testing device for data warehouse scripts, characterized in that, The device includes: a memory, a processor, and a test program for a data warehouse script stored in the memory and executable on the processor, wherein the test program for the data warehouse script, when executed by the processor, implements the test method for the data warehouse script as described in any one of claims 1 to 7.

10. A storage medium, characterized in that, The storage medium stores a test program for a data warehouse script, which, when executed by a processor, implements the test method for the data warehouse script as described in any one of claims 1 to 7.

Images