Structured query language generation method and apparatus

Abstract

The application discloses a structured query language generation method, and relates to the technical field of data processing. The method comprises the following steps: acquiring a parameter set, wherein the parameter set comprises a parameter value set with a nested structure; acquiring an SQL template, wherein the SQL template comprises a parameter variable, and the parameter variable carries hierarchical path information; constructing a parameter object corresponding to the parameter set, wherein the parameter object supports obtaining parameter values of each layer in the parameter set through an access path; obtaining corresponding parameter values from the parameter object based on the hierarchical path information in the SQL template; replacing the parameter variable in the SQL template by using the obtained parameter values; and obtaining an SQL statement corresponding to the parameter set based on the replaced result, wherein the SQL statement conforms to an SQL native syntax specification, can be directly debugged by various database clients, and guarantees the syntax compliance and logical integrity of the generated SQL.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method and apparatus for generating a structured query language. Background Technology

[0002] With the widespread application of big data technology in industries such as finance, telecommunications, and the internet, the scale and structural complexity of data processing in various business systems continue to increase. To ensure the quality of data processing in these systems, automated testing has become an essential part of system iteration.

[0003] Data verification is a core step in big data automated testing. After the test system calls the interface under test or triggers a business process, it needs to query the actual data in the database using Structured Query Language (SQL) and compare the query results with the data returned by the interface or the expected business results to verify the correctness of the processing logic of the system under test. Due to the large number of test cases and the wide variety of scenarios, the SQL statements used for data verification need to be dynamically adjusted as the test parameters change. Therefore, the industry commonly uses SQL templates + parameter replacement to generate executable SQL statements.

[0004] However, the existing SQL template parameter replacement techniques have inherent flaws. On the one hand, they lack the ability to access nested parameters. Existing solutions cannot efficiently reference deep key-value pairs in multi-level nested key-value structures using concise syntax. Testers need to write additional value retrieval logic outside the template, increasing script complexity. On the other hand, existing solutions use non-SQL native syntax to achieve dynamic logic embedding, which means that the template cannot be recognized and debugged by native database clients such as the Hive command-line client (Apache Hive Command Line Interface, Hive CLI) after it leaves the test framework, resulting in extremely poor maintainability.

[0005] Therefore, there is an urgent need for a SQL generation solution that adapts to the complex nested parameter structure of big data and is compatible with the native SQL environment. Summary of the Invention

[0006] In view of the above problems, this application provides a SQL generation solution that adapts to complex nested parameter structures in big data and is compatible with native SQL environments. The specific solution is as follows: The first aspect of this application provides a method for generating a structured query language, including: Obtain a parameter set, which includes a nested set of parameter values; Obtain an SQL template, which includes parameter variables that carry hierarchical path information; Construct a parameter object that maps the parameter set, and the parameter object supports obtaining the parameter values ​​of each layer in the parameter set through an access path; Based on the hierarchical path information in the SQL template, the corresponding parameter values ​​are obtained from the parameter object; and the obtained parameter values ​​are used to replace the parameter variables in the SQL template. Based on the result of the replacement, the SQL statement corresponding to the parameter set is obtained.

[0007] Optionally, the SQL template may further include code block markers, which are used to define the range of executable code segments; The method further includes: Retrieve SQL fragments from code segment ranges defined by code block markers; The SQL statement corresponding to the parameter set obtained based on the replacement result includes: The SQL fragment and the replaced result are concatenated to obtain the target SQL statement corresponding to the parameter set.

[0008] Optionally, obtaining the SQL fragment from the code segment range defined by the code block marker includes: Read the SQL template line by line; When the start marker is detected, collect the lines of code between the start marker and the end marker; When the end marker is detected, the collected lines of code are executed in the namespace to obtain the SQL fragment; The namespace includes the parameter object and predefined output variables. The predefined output variables are used to store the SQL fragments, and the code fragments are configured to assign the generated SQL fragments to the predefined output variables.

[0009] Optionally, the SQL fragment includes at least one of the following: a first fragment, a second fragment, a third fragment, and a fourth fragment; The first segment is used to generate the list of fields to be queried in the query statement; the second segment is used to generate the filter conditions in the query statement; the third segment is used to generate the grouping fields in the query statement; and the fourth segment is used to generate the sorting conditions in the query statement.

[0010] Optionally, the SQL fragment is concatenated by concatenating the processed content sequentially according to the original row order of the SQL template; wherein the processed content includes SQL fragment rows, rows that have completed parameter value replacement, and rows that are retained as is.

[0011] Optionally, the executable code segment range defined by the code block marker in the SQL template is a script code segment; The script code snippet obtains the target value corresponding to the nested key in the parameter set through the dot chain access interface of the parameter object. The target value is used to obtain the content and structure of the SQL snippet to be generated.

[0012] Optionally, constructing the parameter object mapping the parameter set includes: Iterate through the key-value pairs of the parameter set; If the value of the current key-value pair is a dictionary, then the child parameter object corresponding to the current key-value pair is recursively constructed, and the child parameter object is used as a property of the parent parameter object. If the current key-value pair is a list containing dictionary elements, then recursively construct the child parameter objects corresponding to each dictionary element in the list, and use the child parameter objects as properties of the parent parameter object. If the value of the current key-value pair is not a dictionary type, then the value of the current key-value pair will be directly used as a property of the parent parameter object.

[0013] Optionally, the construction of the parameter object mapping the parameter set further includes: generating the class name of the parameter object based on the timestamp to avoid class name conflicts in concurrent scenarios.

[0014] Optionally, replacing the parameter variables in the SQL template using the obtained parameter values ​​includes: Identify the hierarchical path information carried in the parameter variables; The actual values ​​are obtained level by level along the attribute path corresponding to the hierarchical path information on the parameter object; Replace the parameter variable with the actual value.

[0015] A second aspect of this application provides a structured query language generation apparatus, comprising: A parameter acquisition module is used to acquire a parameter set, which includes a nested set of parameter values. The template acquisition module is used to acquire an SQL template, which includes parameter variables and carries hierarchical path information. A parameter object construction module is used to construct parameter objects that map the parameter set. The parameter objects support obtaining parameter values ​​of each layer in the parameter set through access paths. The parsing and replacement module is used to obtain the corresponding parameter values ​​from the parameter object based on the hierarchical path information in the SQL template; and to replace the parameter variables in the SQL template with the obtained parameter values. The concatenation module is used to obtain the SQL statement corresponding to the parameter set based on the replacement result.

[0016] This application provides a method and apparatus for generating a structured query language. The method includes: first, obtaining a parameter set, which includes a set of nested parameter values ​​that matches the actual input parameter structure of a big data query interface, providing a reliable input source for subsequent parameter mapping and reducing the risk of value retrieval failure due to parameter structure mismatch; second, obtaining an SQL template, which includes parameter variables carrying hierarchical path information; through hierarchical parameter identification design, the correlation between test logic and SQL syntax is preserved, alleviating the pain point of chaotic parameter identification in traditional templates, and without introducing non-SQL native syntax tags, laying the foundation for native compatibility; third, constructing a parameter object that maps the parameter set, wherein the parameter object supports retrieval of the parameter value through an access path. The parameter values ​​at each level of the parameter set are mapped to a hierarchical attribute structure by nested key-value pairs, abandoning the traditional cumbersome dictionary indexing method, reducing the access complexity of nested parameters, and mitigating the core defect of inefficient referencing of deeply nested parameters. Subsequently, based on the hierarchical path information in the SQL template, the corresponding parameter values ​​are obtained from the parameter object. The obtained parameter values ​​are then used to replace the parameter variables in the SQL template. By identifying the path information in the parameter variables, values ​​are retrieved and replaced level by level along the object attribute path, achieving automated replacement without requiring testers to write additional value retrieval logic outside the template. Finally, based on the replacement results, the SQL statement corresponding to the parameter set is obtained. The replacement results strictly conform to the native SQL syntax specification and can be directly debugged by native database clients such as Hive CLI, ensuring the syntactic compliance and logical integrity of the generated SQL.

[0017] Compared to traditional SQL generation solutions, the SQL generation method provided in this application improves the efficiency of accessing nested parameters and solves the problem of difficulty in retrieving values ​​for complex nested parameters. Furthermore, the generated SQL strictly conforms to the native SQL syntax specification and can be directly debugged by native database clients such as Hive, with no risk of variable pollution during concurrent execution. Attached Figure Description

[0018] The above and other features, advantages, and aspects of the embodiments of this disclosure will become more apparent from the accompanying drawings and the following detailed description. Throughout the drawings, the same or similar reference numerals denote the same or similar elements. It should be understood that the drawings are schematic, and the originals and elements are not necessarily drawn to scale.

[0019] Figure 1 A flowchart illustrating a structured query language generation method provided in an embodiment of this application; Figure 2 This is a schematic diagram illustrating a parameter variable parsing and substitution process according to an embodiment of this application; Figure 3This is a schematic diagram of a structured query language generation device provided in an embodiment of this application. Detailed Implementation

[0020] The embodiments of this application will now be clearly and completely described with reference to the accompanying drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the scope of protection of this application.

[0021] Various modifications and variations can be made to this application without departing from its spirit or scope, which will be apparent to those skilled in the art. Therefore, this application is intended to cover modifications and variations falling within the scope of the corresponding claims (the claimed technical solutions) and their equivalents. It should be noted that the implementation methods provided in the embodiments of this application can be combined with each other without contradiction.

[0022] To make the above-mentioned objectives, features and advantages of this application more apparent and understandable, the application will be further described in detail below with reference to the accompanying drawings and specific embodiments.

[0023] As mentioned in the background, the lack of a native SQL dynamic assembly solution in the Python ecosystem is a pain point in the industry. The two shortcomings of existing technologies are not accidental: mainstream programming languages ​​such as Java and Go have mature SQL assembly dependency packages that natively support nested parameter access and dynamic logic embedding. However, the Python ecosystem has long lacked a native SQL assembly solution adapted to big data testing scenarios. Developers can only rely on plain text replacement logic to implement parameter replacement, which is the common underlying cause of both shortcomings. Plain text replacement logic does not adapt to nested parameter structures and cannot map multi-level nested key-value pairs to dot-mapping accessible object structures. Therefore, it can only retrieve deep key-value pairs through dictionary indexing, forcing testers to write additional value retrieval logic outside the template. Simultaneously, to achieve dynamic logic embedding, plain text replacement logic can only reuse non-SQL native syntax tags from general template engines. These tags are not part of the SQL syntax specification, so the template cannot be recognized and debugged by the database's native client after leaving the testing framework.

[0024] In addition, the existing solutions have several drawbacks. First, they have poor adaptability to large-size SQL templates. For SQL templates with thousands of lines in big data scenarios, existing plain text replacement solutions often use a full-load mode, which can easily lead to memory overflow. Second, the SQL structure lacks flexibility. The field list, query conditions, grouping / sorting rules, and other structural components of existing SQL templates are fixed during the writing process, making it difficult to dynamically adjust based on test parameter values. When different test scenarios require different query logic, a separate template needs to be maintained for each scenario, resulting in high reuse costs. Third, execution security is insufficient: concurrent execution of multiple test cases can easily lead to variable pollution, and missing parameters can easily generate SQL statements with syntax errors, further increasing test maintenance costs.

[0025] In view of this, this application first constructs a parameter object that maps nested parameter sets, recursively converting multi-level nested key-value pairs into an object structure that supports dot-chain access, thus solving the problem of complex nested parameter value retrieval from the root, without the need to write additional value retrieval logic outside the template; then, parameter value replacement is completed based on the parameter object: by identifying parameter variables that carry hierarchical path information in the SQL template, values ​​are retrieved and replaced level by level along the attribute path of the parameter object, generating directly executable SQL statements to meet basic testing requirements.

[0026] This application provides a method for generating a structured query language, such as... Figure 1 As shown, Figure 1 A flowchart of a structured query language generation method provided in this application embodiment is shown. The method includes: S01: Obtain the parameter set, which includes a set of parameter values ​​in a nested structure.

[0027] In step S01, the parameter set refers to a collection containing multiple parameters, where at least some parameter values ​​are themselves subsets of parameters, thus forming a multi-layered nested structure. The parameter set is the source of input data for the SQL statement generation process, and its content determines which specific values ​​are filled into the final generated SQL statement and the dynamic structure of the SQL statement. The parameter set is designed to match the actual input parameter structure of the big data query interface, providing a reliable input source for subsequent parameter mapping and reducing the risk of value retrieval failures due to parameter structure mismatches.

[0028] The nested structure of parameter sets can take many forms. In one embodiment, the parameter set is represented as a collection of key-value pairs (such as a dictionary or mapping structure in a programming language), where some keys correspond to scalar values ​​(such as strings or numbers), while the values ​​corresponding to other keys are themselves a subset of key-value pairs. For example, in a big data service interface testing scenario in the financial industry, a parameter set used to query product information might contain the following: the value corresponding to the key "status" is the string "ACTIVE," indicating that only products with an active status are being queried; the value corresponding to the key "groupMap" is a subset containing the values ​​"team_leader" corresponding to the key "MANAGE_TEAM_LEADER_NAME" and "project_name" corresponding to the key "MANAGE_TEAM_PROJECT_NAME." These two sets of sub-parameters represent the name of the management team leader and the name of the management team's project, respectively. In this example, the parameter set contains two levels of nesting: the first level directly contains the two keys "status" and "groupMap," and the second level contains two sub-keys within "groupMap."

[0029] In another embodiment, the parameter set can have three or more levels of nesting. For example, in a user behavior analysis scenario in the telecommunications industry, the parameter set might contain a top-level key "queryConfig," whose corresponding subset contains the key "filterRules," which in turn corresponds to a subset containing deeper parameters such as "timeRange" (time range) and "region" (region). This multi-level nested structure is a common pattern in actual big data query interfaces. In yet another embodiment, the parameter set can also contain list-type parameter values, where each element in the list is itself a subset of key-value pairs. For example, in a batch data quality verification scenario in the internet industry, the value corresponding to the key "batchItems" in the parameter set is a list, where each element is a subset of key-value pairs containing "itemId" and "itemType," representing a batch of items to be queried. It should be noted that the source of the parameter set is not limited to a specific industry or application scenario. The parameter set can come from the test case configuration file of an automated testing framework, the input interface parameters of a data processing system, or the set of query conditions submitted by the user through an interactive interface. The nesting depth and specific key names of the parameter set are also unrestricted. The method of this application can adaptively handle parameter sets with arbitrary nesting depths.

[0030] S02: Obtain an SQL template, which includes parameter variables that carry hierarchical path information.

[0031] Based on the aforementioned parameter set, step S02 further obtains the SQL template that matches the parameter set. The SQL template refers to a pre-defined SQL statement framework file, which includes parameter variables and static SQL text (such as fixed table names, join conditions, etc.). Parameter variables are placeholders; they do not directly represent a specific value but carry hierarchical path information, i.e., a path description from the root node of the parameter set, through which intermediate levels are traversed, and finally reaching the target parameter value. Through this hierarchical parameter identification design, parameter variables retain the correlation between test logic and SQL syntax, alleviating the pain point of the chaotic parameter identification in traditional templates. The hierarchical path information specifies, in the form of a path, which intermediate levels are traversed sequentially from the top level of the parameter set to finally reach the target parameter. For example, the path "groupMap.MANAGE_TEAM_LEADER_NAME" means: starting from the root node of the parameter set, first enter the sub-level named "groupMap", and then retrieve the value with the key "MANAGE_TEAM_LEADER_NAME" from that sub-level. It is important to note that the design of the writing format of parameter variables in the SQL template is crucial: it must carry sufficient hierarchical path information to support nested parameter positioning, while avoiding the introduction of non-SQL native syntax tags, so as to ensure that the template can be directly recognized by database native clients such as Hive CLI.

[0032] In one embodiment, a parameter variable can be represented in the form of "$param.groupMap.MANAGE_TEAM_LEADER_NAME$", where the starting symbol "$param." indicates that this is a variable that needs to obtain a value from the parameter set, the middle part is the hierarchical path information, and the ending symbol "$" indicates the end of the variable. In this format, the parameter variable as a whole is represented as an identifier string wrapped in "$", which does not contain any special characters that are illegal in SQL syntax. Therefore, it can be treated as a string literal or comment in the native database client without causing a syntax error. For example, an SQL template line containing a parameter variable, "WHERE product.status = $param.status$", cannot be directly executed in the native database client because the parameter variable has not been replaced, but its SQL syntax structure is complete. Developers and testers can debug the SQL logic in the native client by manually replacing the parameter variable. In contrast, existing solutions use non-SQL native syntax markers (such as proprietary placeholder syntax of specific frameworks), which makes the template unable to be recognized as a valid SQL statement in the native client, thus losing the ability to debug independently outside the test framework. Optionally, parameter variables can also use other formats, as long as they can carry hierarchical path information and do not break the SQL syntax structure, such as "${param.groupMap.MANAGE_TEAM_LEADER_NAME}", etc. This application does not restrict the specific writing format. In addition, parameter variables can carry path information of any level. For example, "$param.status$" carries a single-level path (directly obtaining "status" at the root level of the parameter set), "$param.groupMap.MANAGE_TEAM_LEADER_NAME$" carries a two-level path, and "$param.queryConfig.filterRules.timeRange$" carries a three-level path. The method of this application does not restrict the depth of hierarchical paths and supports nested parameter access at any depth.

[0033] S03: Construct a parameter object that maps the parameter set, wherein the parameter object supports obtaining the parameter values ​​of each layer in the parameter set through an access path.

[0034] After obtaining the parameter set and SQL template, step S03 constructs the parameter set into a parameter object that supports access paths to retrieve parameter values ​​at each level. The parameter object is a data structure obtained by mapping nested key-value pairs to a hierarchical attribute structure. Its core function is to eliminate the traditional, lengthy dictionary indexing method and reduce the complexity of accessing nested parameters. The access path supported by the parameter object refers to a sequence of attribute names that starts from the root object of the parameter object, sequentially accesses each level of attributes, and finally reaches the target parameter value. Each segment of the access path corresponds to a key name at a level in the parameter set. For example, the access path "param.groupMap.MANAGE_TEAM_LEADER_NAME" consists of three segments: root object "param" → attribute "groupMap" → attribute "MANAGE_TEAM_LEADER_NAME".

[0035] To more intuitively understand the improvements brought by the access path method compared to the traditional method, the following example illustrates a nested parameter located under the key "groupMap" with the subkey "MANAGE_TEAM_LEADER_NAME": In the traditional method, retrieving this value requires writing the retrieval logic outside the template. First, the corresponding subset is retrieved from the param using the first-level key name, and then the final value is retrieved from the subset using the second-level key name. The code is verbose, and the code length increases linearly with the nesting level. Furthermore, writing the retrieval logic outside the template separates the SQL template from the retrieval code, resulting in high maintenance costs. In contrast, with the access path method proposed in this application, the parameter object itself supports directly retrieving deep parameter values ​​through the hierarchical path. No retrieval logic needs to be written outside the template. Subsequent steps directly utilize the hierarchical path information carried in the parameter variables to retrieve the actual value level by level along the attribute chain of the parameter object. The entire process is fully automated within the SQL generation process.

[0036] Optionally, in one embodiment of this application, constructing a parameter object that maps the parameter set includes: Iterate through the key-value pairs of the parameter set; If the value of the current key-value pair is a dictionary, then the child parameter object corresponding to the current key-value pair is recursively constructed, and the child parameter object is used as a property of the parent parameter object. If the current key-value pair is a list containing dictionary elements, then recursively construct the child parameter objects corresponding to each dictionary element in the list, and use the child parameter objects as properties of the parent parameter object. If the value of the current key-value pair is not a dictionary type, then the value of the current key-value pair will be directly used as a property of the parent parameter object.

[0037] This embodiment describes in detail three construction strategies for parameter objects, each corresponding to one of three parameter value types. Specifically: The first category involves handling dictionary-type values: When traversing the parameter set, if a key's value is itself a collection of key-value pairs (i.e., a dictionary), it cannot be simply used as a property value because it contains sub-parameters that also need to support access paths. Therefore, a recursive parameter object construction process is required for this dictionary value. Recursion involves taking the current key-value pair's value (a dictionary) as input to a new parameter set, re-executing the traversal and construction process to obtain the corresponding child parameter objects; then, these child parameter objects are used as properties of the current parent parameter object, with the property name being the key name of the current key-value pair.

[0038] For example, the parameter set contains the key-value pair "groupMap": {"MANAGE_TEAM_LEADER_NAME":"team_leader", "MANAGE_TEAM_PROJECT_NAME":"project_name"}. When iterating over this key-value pair, the value is found to be a dictionary type. Therefore, {"MANAGE_TEAM_LEADER_NAME":"team_leader", "MANAGE_TEAM_PROJECT_NAME": "project_name"} is used as input to recursively construct child parameter objects. Once constructed, this child parameter object has two attributes: MANAGE_TEAM_LEADER_NAME (value "team_leader") and MANAGE_TEAM_PROJECT_NAME (value "project_name"). This child parameter object is then used as the groupMap attribute of the parent parameter object. In this way, accessing param.groupMap will yield the child parameter object, and accessing param.groupMap.MANAGE_TEAM_LEADER_NAME will yield "team_leader". By mapping nested key-value pairs to a hierarchical attribute structure, the traditional verbose dictionary indexing method (such as param['groupMap']['MANAGE_TEAM_LEADER_NAME']) is completely abandoned, reducing the complexity of accessing nested parameters.

[0039] It's important to note that recursion can handle arbitrary depths of nesting. For example, with the parameter set {"a": {"b": {"c":123}}}, the first level of traversal finds that the key "a" is a dictionary, and recursively constructs child parameter object A. During the construction of A, traversing the sub-dictionary finds that the key "b" is a dictionary, and recursively constructs child parameter object B. During the construction of B, traversing the sub-dictionary finds that the key "c" is a non-dictionary type (the value 123), and directly uses 123 as the attribute c of B. Finally, 123 can be accessed via param.abc.

[0040] The second category: handling list type values: when iterating through the parameter set, if a value corresponding to a certain key is found to be a list (array), and the elements in the list contain dictionary types, it is necessary to recursively construct child parameter objects for each dictionary element in the list.

[0041] In one embodiment, the parameter set contains key-value pairs "batchItems":[{"itemId":"001","itemType":"A"},{"itemId":"002","itemType":"B"}]. For each dictionary element in the list, child parameter objects (each with itemId and itemType properties) are recursively constructed. Each child parameter object is then presented as a list as the batchItems property of the parent parameter object. Thus, "001" can be accessed via param.batchItems[0].itemId, eliminating the need to write hierarchical indexing logic outside the template.

[0042] The third category: Handling non-dictionary type values: When traversing the parameter set, if a key corresponds to a primitive type (such as string, number, boolean, null, etc.), no recursive conversion is needed. The key name is directly used as the attribute name, and the value as the attribute value, and added to the current parameter object. This is the simplest of the three strategies and also the termination condition for recursion: the recursion stops when the parameter value no longer contains a dictionary substructure.

[0043] The three strategies described above cover all data structure types that may appear in the actual parameter set. By recursively processing dictionary elements in dictionaries and lists, nested parameter structures of arbitrary complexity are uniformly converted into parameter object trees that support dot-chain access. Moreover, the conversion process is automatic and does not require prior knowledge of the specific structure of the parameter set.

[0044] Optionally, in one embodiment of this application, the construction of the parameter object mapping the parameter set further includes: generating the class name of the parameter object based on a timestamp to avoid class name conflicts in concurrent scenarios.

[0045] The technical solution provided in this embodiment is used to solve a technical problem unique to concurrent scenarios: when multiple SQL generation tasks run simultaneously, if the parameter objects built by each task use the same class name, the class definition built later will overwrite the class definition built earlier, which will lead to attribute access errors or data corruption, i.e., the risk of "variable pollution". This is a real problem in multi-threaded testing frameworks or multi-project concurrent execution scenarios.

[0046] In this embodiment, a globally unique class name is dynamically generated each time a parameter object is constructed. Since parameter objects are constructed at different times, the millisecond-level timestamp of the current system time can be used as part of the class name to ensure its uniqueness. For example, the class name can be constructed by concatenating a fixed prefix with a 13-digit millisecond timestamp. Since millisecond-level timestamps are naturally different at different times, the probability of class name conflicts is extremely low, and there is no risk of variable pollution during concurrent execution.

[0047] Optionally, the class name can be generated by obtaining the current system time, converting it into a millisecond-level timestamp (i.e., the number of milliseconds that have elapsed from a certain reference time point to the current time), using this timestamp integer as the class name suffix, and concatenating it with a fixed prefix. Optionally, the class name can also be combined with other unique information, such as task identifier, process identifier, random number, etc., to further enhance the uniqueness guarantee. This application does not impose restrictions on specific class name generation rules, as long as it can ensure that the parameter object class names constructed by different tasks in concurrent scenarios do not conflict.

[0048] S04: Based on the hierarchical path information in the SQL template, obtain the corresponding parameter value from the parameter object; and use the obtained parameter value to replace the parameter variable in the SQL template.

[0049] After the parameter object is constructed, step S04 performs parameter variable replacement. This is the core transformation step in the entire method, converting the SQL template from a "frame containing placeholders" into an "executable SQL statement." The core idea of ​​parameter variable replacement is to utilize the hierarchical path information carried in the parameter variables to obtain the actual values ​​level by level along the corresponding paths on the parameter object, and then replace the parameter variables with these actual values. By identifying the path information in the parameter variables and retrieving and replacing values ​​level by level along the object attribute paths, automated replacement is achieved, eliminating the need for testers to write additional value retrieval logic outside the template.

[0050] Optionally, the replacement process can be as follows: First, scan each line of text in the SQL template using pattern matching (such as regular expressions) to identify tags that conform to the parameter variable format. When a parameter variable is identified, extract its hierarchical path information. Then, on the parameter object, access each level of attribute sequentially according to the hierarchical path information, separated by dots, until the final actual value is obtained. Finally, replace the parameter variable in the SQL template line entirely with the actual value. The replacement process can be repeated. There may be multiple parameter variables in the same line. For example, in the line "WHERE product.status = $param.status$ AND product.type = $param.type$", the replacement process first identifies the first parameter variable "$param.status$", extracts the path "status", and obtains the actual value (let's say "ACTIVE") from the parameter object to complete the replacement. Then, it identifies the second parameter variable "$param.type$", extracts the path "type", and obtains the actual value (let's say "STANDARD") from the parameter object to complete the replacement. Finally, the line becomes "WHERE product.status= ACTIVE AND product.type = 'STANDARD'", and the whole process is completed automatically. For multi-level nested parameter variables such as "$param.groupMap.MANAGE_TEAM_LEADER_NAME$", the replacement process extracts the path "groupMap.MANAGE_TEAM_LEADER_NAME". It first accesses the "groupMap" property of the parameter object to obtain the sub-object, and then accesses the "MANAGE_TEAM_LEADER_NAME" property of the sub-object to obtain the final value. This hierarchical access process can be achieved by dynamically executing the attribute access expression in the specified execution environment. The hierarchical path in the parameter variable is converted into an attribute access statement, which is executed in the namespace containing the parameter object to obtain the result value.

[0051] Optionally, in one embodiment of this application, replacing the parameter variables in the SQL template using the obtained parameter values ​​includes: Identify the hierarchical path information carried in the parameter variables; The actual values ​​are obtained level by level along the attribute path corresponding to the hierarchical path information on the parameter object; Replace the parameter variable with the actual value.

[0052] Optional, such as Figure 2 As shown, Figure 2This is a flowchart illustrating a parameter variable parsing and replacement process. This embodiment breaks down the parameter variable replacement process into three sub-steps: identification, step-by-step acquisition, and replacement. Specifically: Step S201: Identify hierarchical path information.

[0053] Locate the position of parameter variables in the SQL template row and extract the hierarchical path information they carry. The key to identification is the format convention of the parameter variables. For example, parameter variables begin with "$param." and end with "$", with the middle part being the hierarchical path information. Pattern matching (such as regular expressions) is used for identification, and predefined pattern rules describe the format characteristics of parameter variables.

[0054] For example, for the line "WHERE product.status = $param.status$", pattern matching identifies "$param.status$" as a parameter variable, and the extracted hierarchical path information is "status". For the line "AND product.type= $param.groupMap.MANAGE_TEAM_LEADER_NAME$", it identifies "$param.groupMap.MANAGE_TEAM_LEADER_NAME$" as a parameter variable, and the extracted hierarchical path information is "groupMap.MANAGE_TEAM_LEADER_NAME".

[0055] Step S202: Obtain the actual value step by step.

[0056] The extracted hierarchical path information is parsed into attribute paths, and accessed level by level on the parameter object until the final actual value is obtained. The parsing method is as follows: the hierarchical path information is separated by dots to obtain a sequence of attribute names. Starting from the parameter object, each attribute name in the sequence is accessed in turn, and each access returns the next level object (or the final value).

[0057] Taking the hierarchical path information "groupMap.MANAGE_TEAM_LEADER_NAME" as an example, the attribute name sequence ["groupMap", "MANAGE_TEAM_LEADER_NAME"] is obtained by splitting by the dot. First, the attribute "groupMap" is accessed on the parameter object to get a child parameter object; then the attribute "MANAGE_TEAM_LEADER_NAME" is accessed on the child parameter object to get the final value "team_leader".

[0058] There are several ways to implement hierarchical retrieval. Optionally, it can be implemented by dynamically executing attribute access expressions, constructing an attribute access statement from the hierarchical path information, executing it in the namespace containing the parameter object, and directly obtaining the result value. Optionally, it can also be implemented through a recursive function: starting from the parameter object, retrieve the attribute value corresponding to the first segment of the path, determine whether the value is still a parameter object, and if so, continue recursively retrieving the attribute value corresponding to the next segment of the path until the path is exhausted.

[0059] Step S203: Replace.

[0060] Replace all the parameter variables identified in the SQL template line with the actual values ​​obtained in sub-step two. After the replacement, the line no longer contains parameter variable markers and becomes a normal SQL text line.

[0061] If a row contains multiple parameter variables, each parameter variable is processed in turn. The order of replacement does not affect the final result because each parameter variable does not overlap. The entire row after replacement is composed entirely of native SQL syntax elements and no longer contains any parameter variable markers. It can be directly parsed and executed normally by native clients such as Hive CLI.

[0062] S05: Based on the result of the replacement, obtain the SQL statement corresponding to the parameter set.

[0063] After the preceding replacements, step S05 generates the final SQL statement based on the replacement results. The replaced results strictly conform to the native SQL syntax specifications and can be directly debugged by native database clients such as Hive CLI, ensuring the syntactic compliance and logical integrity of the generated SQL. Specifically, in simple scenarios where the SQL template does not contain code block markers, step S05 can directly output the replaced SQL text, for example, if the SQL template is "SELECT..." FROM productsWHERE status = $param.status$, where the status value in the parameter set is "ACTIVE", after replacement, we directly get "SELECT The query "FROM products WHERE status = 'ACTIVE'" is a query statement that fully conforms to the SQL syntax specification and can be submitted and executed directly through native clients such as Hive CLI. However, in scenarios where the SQL template contains code block markers, step S05 also requires concatenating the SQL fragment generated by executing the code fragment with the replaced result to obtain the final target SQL statement; the concatenated result also strictly conforms to the native SQL syntax specification and does not contain any non-native SQL syntax markers.

[0064] Optionally, in one embodiment of this application, the SQL template further includes code block markers, which are used to define a range of executable code segments; The method further includes: Retrieve SQL fragments from code segment ranges defined by code block markers; The SQL statement corresponding to the parameter set obtained based on the replacement result includes: The SQL fragment and the replaced result are concatenated to obtain the target SQL statement corresponding to the parameter set.

[0065] Code block markers are symbols used in the SQL template to identify the start and end points of a block of executable code. Code block markers appear in pairs: a start marker indicates the beginning of the code segment, and an end marker indicates the end. The content between the two markers constitutes the executable code segment interval. This interval is not treated as ordinary SQL text but is extracted as the executable code segment. An SQL segment refers to the SQL text generated after the code segment is executed. The content of the SQL segment is not fixed but dynamically determined based on the actual values ​​of the parameters in the parameter set.

[0066] The design of code block tags is a key difference between this application and existing solutions. Existing solutions typically use non-SQL native syntax tags (such as proprietary tag syntax of specific template engines) to achieve dynamic logic embedding. This results in the templates not being recognized by native database clients such as Hive CLI after leaving the test framework. When native clients encounter these non-SQL native syntax tags, they will directly report syntax errors, and developers and testers cannot debug the SQL logic of the templates in native clients.

[0067] The code block tagging design in this application strictly adheres to the native SQL syntax specification: the tags themselves use specific identifiers guided by SQL comment symbols, and the content within the tagged area is treated as ordinary comment content by the SQL parser. In one embodiment, the starting tag is "#py-region" and the ending tag is "#region-py". Because they begin with the SQL comment symbol "#" (a valid single-line comment guide in Hive), in native database clients such as Hive CLI, the tagged line and all lines between it are treated as comments and automatically ignored, thus preventing syntax errors. Developers and testers can directly open and view the SQL logic of the template in Hive CLI. Although the dynamic part (code block tagging area) is treated as a comment and skipped in the native client, the static SQL framework is fully visible and structurally clear, greatly improving the maintainability and debugging capabilities of the template.

[0068] Optionally, code block markers can be introduced using other native SQL comment characters. For example, they can begin with "--" (the standard SQL single-line comment character), such as "--py-region" and "--region-py"; code block markers can also be wrapped in SQL block comment syntax, such as " / PY-REGION / "and" / REGION-PY Regardless of the specific form used, the core principle is that the code block marker must be a valid comment form according to the native SQL syntax specification to ensure that the template can be parsed correctly by the native database client.

[0069] Optionally, in one embodiment of this application, obtaining the SQL fragment from the code segment range defined by the code block marker includes: Read the SQL template line by line; When the start marker is detected, collect the lines of code between the start marker and the end marker; When the end marker is detected, the collected lines of code are executed in the namespace to obtain the SQL fragment; The namespace includes the parameter object and predefined output variables. The predefined output variables are used to store the SQL fragments, and the code fragments are configured to assign the generated SQL fragments to the predefined output variables.

[0070] In this embodiment, line-by-line reading refers to reading the SQL template file into memory line by line. During the reading process, a status flag is maintained to record whether the current position is within the code segment range. This line-by-line processing method allows the entire processing flow to be streamed, eliminating the need to load the entire SQL template into memory at once for full analysis. The namespace refers to the variable environment in which the code segment executes. Several variables are pre-defined in the namespace, and the code segment can access and modify these variables. Unlike the local variables of the code segment itself, the variables in the namespace are pre-injected externally before the code segment executes. The predefined output variable is a variable with a specific name in the namespace, specifically used to receive the SQL segment generated after the code segment executes. The code segment assigns the dynamically generated SQL text to this variable, and external programs obtain the SQL segment by reading the value of this variable.

[0071] The following section uses a specific processing flow to illustrate the implementation of code snippet retrieval and execution. The SQL template file is read line by line and processed sequentially. A status flag is set, initially to the "non-code region" state. For each line: (1) Determine if the current line matches the start marker. If it matches, switch the state to "within code block" and skip the current line (the start marker itself is not part of the code block) and continue reading the next line.

[0072] (2) If the current state is "within code interval", determine whether the current line matches the end marker. If it matches, switch the state back to "non-code interval", stop collecting code lines, and enter the execution phase. If it does not match, collect the current line into the code line buffer.

[0073] (3) If the current state is “non-code zone” and the current line is not the start mark, then the line will be treated as a normal SQL template line (enter the parameter variable replacement process).

[0074] When an end marker is detected, the generation phase is triggered. All lines of code collected in the code line buffer are concatenated into a complete code snippet, which is then executed within a pre-configured namespace. The namespace contains at least two predefined variables: a parameter object (provided with the predefined name "param"), allowing the code snippet to access any parameter value from the parameter set via the parameter object's access path; and a predefined output variable (e.g., named "__ret__"), initially set to an empty string. During execution, the code snippet performs logical checks and string concatenation based on the parameter values ​​in the parameter object, assigning the dynamically generated SQL snippet as a string to the output variable. After execution, the value of the output variable is read, which is the dynamically generated SQL snippet.

[0075] The following example illustrates this process. Suppose there's a code snippet in the SQL template that checks if the `groupMap` attribute of a parameter object contains the key "MANAGE_TEAM_LEADER_NAME". If it does, the corresponding database field name is appended to the output variable. The parameter object `param` and the output variable `__ret__` (initialized as an empty string) have already been pre-injected into the namespace. The code snippet's execution logic is as follows: First, it checks if the `groupMap` attribute of the parameter object exists and is not empty. Then, it checks if `groupMap` contains each expected key name. For existing keys, the corresponding database field names are appended to the `__ret__` variable in comma-separated format. After execution, the string stored in the `__ret__` variable is the dynamically generated list of query fields, such as "manage.team_leader, manage.project_name".

[0076] In another embodiment, the code snippet can generate filter conditions (WHERE clauses). For example, the execution logic of the code snippet is as follows: check if the key "riskLevel" exists in the parameter set; if it exists and its value is not empty, generate the corresponding filter condition string (such as "AND risk.level = 'HIGH'") and assign it to the output variable; if it does not exist or is empty, the output variable remains an empty string. In this way, the same SQL template can automatically generate SQL statements containing different filter conditions under different parameter sets.

[0077] Optionally, in one embodiment of this application, the SQL fragment includes at least one of the following: a first fragment, a second fragment, a third fragment, and a fourth fragment; The first segment is used to generate the list of fields to be queried in the query statement; the second segment is used to generate the filter conditions in the query statement; the third segment is used to generate the grouping fields in the query statement; and the fourth segment is used to generate the sorting conditions in the query statement.

[0078] In this embodiment, the first segment refers to the part of the SQL query statement after the SELECT keyword and before the FROM keyword. It specifies which fields should be included in the query results. In business scenarios, different parameter combinations may require displaying different sets of fields; therefore, the field list is the most common dynamically changing part. For example, in big data query scenarios in the financial industry, the basic fields always include product_id and product_name, while extended fields (such as the name of the management team leader and the name of the management team's project) only need to be displayed if the corresponding keys are present in the parameter set's groupMap. The code snippet dynamically determines whether to add the corresponding database field names to the query field list by checking if the keys for each optional field exist in the groupMap.

[0079] The second segment refers to the part after the WHERE keyword in an SQL query statement, which is used to limit the conditions that the query results should meet. For example, in a risk control rule query scenario, the basic filter condition is "status = 'ACTIVE'", while the risk level filter condition "AND risk.level = 'HIGH'" is only needed if the parameter set contains the "riskLevel" key and its value is "HIGH". The code segment dynamically determines whether to generate the corresponding filter condition by checking the parameter value.

[0080] The third segment refers to the part after the GROUP BY keyword in the SQL query statement, which is used to specify which fields to group the query results by. For example, in a statistical query scenario, the basic grouping field is "product.category", but when the parameter set contains the "regionGroup" key, it is necessary to add additional grouping by region. The code snippet dynamically determines the composition of the grouping fields by checking the relevant keys in the parameter set.

[0081] The fourth segment refers to the part after the ORDER BY keyword in the SQL query statement, which is used to specify the sorting rules of the query results. For example, in a list query scenario, the default is to sort by creation time in descending order. However, when the parameter set contains the "sortField" key, it needs to be sorted according to the field specified by the user. The code snippet dynamically generates the ORDER BY clause by reading the sorting-related parameters in the parameter set.

[0082] It should be noted that the above four fragments can be used individually or in any combination. Regardless of which fragment is generated, the final complete SQL statement obtained by concatenating it with the replaced SQL text strictly conforms to the original SQL syntax.

[0083] Optionally, in one embodiment of this application, the SQL fragment is concatenated by concatenating the processed content sequentially according to the original row order of the SQL template; wherein the processed content includes SQL fragment rows, rows that have completed parameter value replacement, and rows that are retained as is.

[0084] This embodiment clarifies how the results of SQL fragments and parameter variable substitutions are merged into the final target SQL statement. The core principle is to maintain the original row order of the SQL template. That is, regardless of how each row is processed (replaced by code fragments, parameter values, or retained as is), the order in which each row appears in the final SQL statement is completely consistent with its order in the SQL template. This ensures that the final generated SQL statement maintains syntactic consistency with the design intent of the SQL template, guaranteeing the logical integrity of the generated SQL.

[0085] The processed content is divided into three categories: The first type is SQL fragment lines. These lines come from the execution results of code fragment intervals. In the SQL template, the code block marker interval occupies a continuous range of lines (from the start marker line to the end marker line). In the final SQL statement, this range is replaced with the content of the SQL fragment. After the replacement, the content in the SQL fragment line strictly conforms to the original SQL syntax. The original code block markers and code content have been completely stripped away, and the final SQL statement does not contain any non-original SQL syntax markers.

[0086] The second category consists of rows that have completed parameter value replacement. These rows come from SQL templates that do not contain code block markers but contain parameter variables. While keeping the original row positions unchanged, the parameter variables in these rows are replaced with actual parameter values. The content of the replaced rows consists entirely of native SQL syntax elements (keywords, table names, field names, literals, etc.) and no longer contains any parameter variable markers.

[0087] The third category consists of rows that are preserved as is. These rows come from plain SQL text lines in the SQL template that contain neither code block markers nor parameter variables. These rows are pure SQL statements themselves and remain completely unchanged in the final SQL statement.

[0088] The following example illustrates the concatenation process. Assume the SQL template has the following row sequence (each row is identified by a row number): (line 1) "SELECT"; (line 2) "product.product_id,"; (line 3) "product.product_name,"; (line 4) start tag "#py-region"; (lines 5-7) code snippet content; (line 8) end tag "#region-py"; (line 9) "FROM product_table product"; (line 10) "WHERE product.status = $param.status$". After processing: lines 1, 2, and 3 are retained as is; lines 4-8 are replaced by the execution results of the SQL fragment (assuming they are the two lines “manage.team_leader,” and “manage.project_name”); line 9 is retained as is; in line 10, “$param.status$” is replaced with “ACTIVE”. After concatenating the original lines in the order they appear, the final SQL statement is composed entirely of native SQL syntax elements and does not contain any non-native SQL syntax markers. It can be executed directly through native clients such as Hive CLI.

[0089] Optionally, in one embodiment of this application, the executable code segment range defined by the code block marker in the SQL template is a script code segment; The script code snippet obtains the target value corresponding to the nested key in the parameter set through the dot chain access interface of the parameter object. The target value is used to obtain the content and structure of the SQL snippet to be generated.

[0090] The script code snippet refers to executable code written in a scripting language (such as Python, JavaScript, or other interpreted languages). Unlike compiled languages, script code snippets can be directly interpreted and executed in the runtime environment without pre-compilation, making them particularly suitable for embedding in SQL templates for real-time dynamic execution. The dot chaining access interface refers to an attribute access method provided by the parameter object. By connecting attribute names at each level sequentially with dots, direct access to nested parameter values ​​can be achieved. For example, param.groupMap.MANAGE_TEAM_LEADER_NAME is the value of MANAGE_TEAM_LEADER_NAME under groupMap in the parameter set obtained through the dot chaining access interface.

[0091] In this embodiment, the code snippet reads the parameter value through the dot chain access interface provided by the parameter object, instead of the traditional layer-by-layer indexing method. This design makes the code for accessing nested parameters in the code snippet extremely concise and effectively improves access efficiency. The multi-layer indexing value retrieval logic that originally needed to be written outside the template can now be completed with just one line of dot chain access.

[0092] In one embodiment, the script code snippet needs to retrieve the value of MANAGE_TEAM_LEADER_NAME from the groupMap in the parameter set. Using the dot notation chaining interface, this value can be directly obtained within the code snippet via param.groupMap.MANAGE_TEAM_LEADER_NAME. In contrast, the traditional approach requires first checking if the key "groupMap" exists in param, then checking if its value is a dictionary, and finally checking if the key "MANAGE_TEAM_LEADER_NAME" exists within it; these multiple layers of checks increase the script's complexity.

[0093] The following three complete embodiments illustrate the execution process of the method of this application from end to end, in order to help those skilled in the art to understand the technical solution more intuitively.

[0094] Example 1: Big Data Testing Scenario in the Financial Industry.

[0095] This embodiment is applied to the automated testing of data interfaces for commercial banks in the financial industry. Testers need to dynamically generate SQL query statements corresponding to different business scenarios based on different test parameters to verify whether the data processing logic of the system under test is correct.

[0096] First, the parameter acquisition module obtains the parameter set. In this embodiment, the parameter set contains the following: The top-level structure contains two keys, "status" and "groupMap". "status" is a string with the value "ACTIVE", indicating that the query status is active, matching the actual input parameter structure for filtering products by status in financial transactions. "groupMap" is a dictionary (nested subset) containing two subkeys: the subkey "MANAGE_TEAM_LEADER_NAME" with the value "team_leader" (the database field alias corresponding to the name of the management team leader), and the subkey "MANAGE_TEAM_PROJECT_NAME" with the value "project_name" (the database field alias corresponding to the name of the management team project).

[0097] Then, the template acquisition module retrieves the SQL template. In this embodiment, the SQL template design strictly follows the native SQL syntax specification: the first three lines are fixed SELECT keywords and basic query fields; followed by a pair of code block markers, which are themselves guided by SQL comment symbols (start marker "#py-region", end marker "#region-py"), which are treated as comments in native clients such as HiveCLI and do not affect the template's syntactic parsing; code snippets are between the markers; then come the FROM clause and JOIN condition; the last line is the WHERE clause, which contains a parameter variable "$param.status$". The entire template can be directly opened and viewed in native clients such as Hive CLI, the static SQL framework is clearly visible, and developers and testers can intuitively understand the template's SQL logic.

[0098] Next, the parameter object construction module receives the parameter set and constructs parameter objects according to three strategies. It iterates through two key-value pairs in the parameter set: the key "status" corresponds to the string "ACTIVE," which is a non-dictionary type, so "status" is directly used as the attribute name and "ACTIVE" as the attribute value; the key "groupMap" corresponds to a dictionary, triggering recursive construction. During recursive construction, it iterates through the two key-value pairs of the sub-dictionary, both of which are strings corresponding to strings, and are therefore non-dictionary types, directly used as attributes of the child parameter object. After recursion, the child parameter object is used as the "groupMap" attribute of the parent parameter object. Simultaneously, a unique class name is generated based on the current millisecond-level timestamp, eliminating the risk of variable pollution during concurrent execution of multiple projects in the financial industry. The constructed parameter object supports retrieving "ACTIVE" from `param.status` and retrieving "team_leader" from `param.groupMap.MANAGE_TEAM_LEADER_NAME`.

[0099] Then, the parsing and replacement module and the concatenation module work together: First, they process the code block marker ranges in the SQL template: reading the SQL template line by line, upon detecting the starting marker "#py-region", they enter code collection mode, collecting all subsequent lines until encountering the ending marker "#region-py". The execution logic of the collected code snippets is as follows: First, check if the groupMap attribute of the parameter object exists and is not empty; second, check if the groupMap contains each expected key name; third, for existing keys, obtain their corresponding values ​​(database field aliases) through the dot-matrix chaining access interface and concatenate them into the output variable according to the SQL field list format; fourth, remove extra commas at the end; the code snippet is executed in the namespace containing the parameter object and the output variable. After execution, the output variable stores the dynamically generated list of query fields. Throughout the process, the code snippets read nested parameter values ​​through the dot-matrix chaining access interface of the parameter object, eliminating the need to write additional value retrieval logic outside the template.

[0100] Meanwhile, the parsing and replacement module processes the parameter variable portion of the SQL template: it identifies the parameter variable "$param.status$" in the WHERE clause line, extracts the hierarchical path information as "status", obtains the actual value "ACTIVE" along the attribute path of the parameter object, and replaces the entire parameter variable with "ACTIVE". The entire replacement process is completed automatically, requiring no manual intervention from testers.

[0101] Finally, the concatenation module concatenates the parts according to the original row order of the SQL template. The final output target SQL statement is a complete SELECT query, which includes basic fields and dynamic fields, and limits the filter condition to ACTIVE status. This SQL statement is composed entirely of native SQL syntax elements: keywords such as SELECT, FROM, LEFT JOIN, WHERE, table name, field name, and condition value. It does not contain any non-native SQL syntax markers. Testers can directly copy this SQL statement into native clients such as Hive CLI for independent verification and debugging.

[0102] This embodiment also demonstrates a significant advantage of this solution: if the parameter set changes, for example, if a new key "RISK_LEVEL" is added to the groupMap, testers only need to add the corresponding key-value pair to the parameter set. When the code snippet is executed, it will automatically detect the existence of the new key and add the corresponding field to the query results. The entire process does not require modification of the SQL template or writing new value retrieval logic outside the template. This reflects the flexibility of this solution in "adapting multiple parameter combinations to the same SQL template" and significantly reduces the maintenance cost of big data automated test scripts.

[0103] Example 2: Risk control test scenario.

[0104] The application background of this embodiment is data query testing of risk control rules in the financial industry. Unlike the first embodiment, this embodiment focuses on demonstrating the dynamic generation process of the aforementioned second segment (filter condition statement) and the native compatibility advantages of the SQL template.

[0105] The parameter set includes a basic filter field "status" with a value of "ACTIVE" and an optional filter field "riskLevel" with a value of "HIGH". The SQL template includes a code block marker after the WHERE clause. The execution logic of the code snippet is as follows: First, check if the riskLevel attribute exists in the parameter object and if its value is not empty; second, if it exists and its value is not empty, concatenate the value to generate the corresponding filter condition text (e.g., "AND risk.level = 'HIGH'") and assign it to the output variable; third, if it does not exist or its value is empty, the output variable remains an empty string.

[0106] In this embodiment, the riskLevel value is "HIGH". The code snippet generates the filter condition "ANDrisk.level = 'HIGH'" and assigns it to the output variable. Simultaneously, a parameter variable "$param.status$" in the template is replaced with "ACTIVE". After concatenation, the final SQL statement is "SELECT...". The statement "FROM risk_records WHERE status = ACTIVE AND risk.level = 'HIGH'" fully conforms to the native SQL syntax.

[0107] Regarding Hive CLI compatibility, even if the `riskLevel` parameter is missing or the code snippet outputs an empty string, the template still displays correctly in the native client. Code block markers are ignored as comments, and the static SQL framework is clearly visible. Developers and testers can understand the overall logical structure of the SQL by viewing the template in the Hive CLI, which is something that traditional non-SQL native marker solutions cannot achieve.

[0108] Example 3: Batch query concurrency scenario.

[0109] The application background of this embodiment is batch data quality verification in the Internet industry. It focuses on demonstrating the role of the aforementioned second type of strategy (processing of list type values) and the aforementioned timestamp naming mechanism in concurrent scenarios.

[0110] The parameter set contains a list of keys "batchItems". The list contains multiple dictionary elements, each of which contains two subkeys: "itemId" and "itemType". When the parameter object construction module iterates through batchItems during the construction process, it recognizes that its value is a list containing dictionary elements. Therefore, it recursively constructs child parameter objects for each dictionary element in the list. After the construction is completed, each item can be accessed through param.batchItems[0].itemId, param.batchItems[1].itemId, etc.

[0111] The execution logic of the code snippet is as follows: iterate through each child parameter object in the batchItems list, read the itemId attribute value of each object, and format it as a list of values ​​in the IN clause. For example, if batchItems contains two items (itemId "001" and "002" respectively), the code snippet will generate "('001', '002')" as the value of the IN clause.

[0112] In concurrent scenarios, suppose two batch query tasks run simultaneously in the same process space with different parameter sets. Because the parameter object construction module generates parameter object class names for each task that include different millisecond-level timestamps, even if the parameter objects of the two tasks exist simultaneously in the same process space, no class name conflict will occur. Each task's parameter object remains independent, correctly reflecting its corresponding parameter set content, and there is no risk of variable pollution during concurrent execution. This mechanism is particularly important for scenarios in industries such as finance and telecommunications that require high-concurrency execution of large-scale automated tests.

[0113] In summary, this application provides a structured query language generation method. By constructing nested parameter sets into parameter objects that support hierarchical path access and assigning hierarchical path information to parameter variables in the SQL template, it achieves adaptive parsing and replacement of parameters with arbitrary nesting depths, solving the problem of difficult value retrieval for complex nested parameters. Simultaneously, by introducing a code block marking mechanism designed in the form of native SQL comments into the SQL template, the SQL template is not only a static framework but can also embed executable code snippets to dynamically generate SQL fragments. Furthermore, the entire template and the final generated SQL statement strictly conform to the native SQL syntax specification, allowing direct debugging by native database clients such as Hive CLI, and eliminating the risk of variable pollution during concurrent execution. The parameter object construction supports recursive conversion of dictionary types, list types containing dictionary elements, and basic types, fully covering various data structures of parameter sets in actual business scenarios. This solution can be widely applied to big data automated testing scenarios in industries such as finance, telecommunications, and the internet that require dynamic generation of SQL statements.

[0114] This application provides a structured query language generation apparatus for executing the structured query language generation method described in any of the above method embodiments, such as... Figure 3 As shown, the device 300 includes: The parameter acquisition module 301 is used to acquire a parameter set, which includes a nested set of parameter values. The template acquisition module 302 is used to acquire an SQL template, the SQL template including parameter variables, the parameter variables carrying hierarchical path information; The parameter object construction module 303 is used to construct a parameter object that maps the parameter set. The parameter object supports obtaining the parameter values ​​of each layer in the parameter set through an access path. The parsing and replacement module 304 is used to obtain the corresponding parameter value from the parameter object based on the hierarchical path information in the SQL template; and to replace the parameter variable in the SQL template with the obtained parameter value. The splicing module 305 is used to obtain the SQL statement corresponding to the parameter set based on the replacement result.

[0115] Optionally, the SQL template may also include code block markers, which are used to define the range of executable code segments.

[0116] Optionally, the apparatus further includes a fragment generation module for obtaining SQL fragments from a code fragment range defined by a code block marker.

[0117] Optionally, the splicing module is specifically used to splice the SQL fragment and the replaced result to obtain the target SQL statement corresponding to the parameter set.

[0118] Optionally, the device includes a fragment generation module, specifically used to read the SQL template line by line; when the start marker is detected, collect the lines of code between the start marker and the end marker; when the end marker is detected, execute the collected lines of code in a namespace to obtain the SQL fragment; wherein, the namespace includes the parameter object and predefined output variables, the predefined output variables are used to store the SQL fragment, and the code fragment is configured to assign the generated SQL fragment to the predefined output variables.

[0119] Optionally, the SQL fragment includes at least one of the following: a first fragment, a second fragment, a third fragment, and a fourth fragment; The first segment is used to generate the list of fields to be queried in the query statement; the second segment is used to generate the filter conditions in the query statement; the third segment is used to generate the grouping fields in the query statement; and the fourth segment is used to generate the sorting conditions in the query statement.

[0120] Optionally, the splicing module includes a row order marking submodule and a content splicing submodule. The row order marking submodule is used to mark the rows in the SQL template according to the original row order of the SQL template, and to clarify the content type corresponding to each row. The content type includes dynamic SQL fragment rows, rows that have completed parameter value replacement, and rows that are retained as is. The content splicing submodule is used to splice the various types of content marked by the row order marking submodule in the original row order to obtain the target SQL statement corresponding to the parameter set.

[0121] Optionally, the executable code segment range defined by the code block marker in the SQL template is a script code segment; The device further includes a script execution module, which is used for the script code fragment to obtain the target value corresponding to the nested key in the parameter set through the dot chain access interface of the parameter object. The target value is used to obtain the content and structure of the SQL fragment to be generated.

[0122] Optionally, the parameter object construction module includes: a key-value pair traversal submodule, a type judgment submodule, and a recursive construction submodule; the key-value pair traversal submodule is used to traverse the key-value pairs of the parameter set; the type judgment submodule is used to identify the type of the current value, the type including dictionary type, list type containing dictionary elements, and non-dictionary type; the recursive construction submodule is used to execute the corresponding construction logic according to the output of the type judgment submodule: if the value of the current key-value pair is a dictionary type, then recursively construct the child parameter object corresponding to the current key-value pair, and use the child parameter object as an attribute of the parent parameter object; if the value of the current key-value pair is a list type containing dictionary elements, then recursively construct the child parameter object corresponding to each dictionary element in the list, and use the child parameter object as an attribute of the parent parameter object; if the value of the current key-value pair is a non-dictionary type, then directly use the value of the current key-value pair as an attribute of the parent parameter object.

[0123] Optionally, the construction of the parameter object mapping the parameter set further includes: generating the class name of the parameter object based on the timestamp to avoid class name conflicts in concurrent scenarios.

[0124] Optionally, the parsing and replacement module includes a path identification submodule, a step-by-step value retrieval submodule, and a variable replacement submodule, wherein the path identification submodule is used to identify the hierarchical path information carried in the parameter variable; the step-by-step value retrieval submodule retrieves the actual value step by step along the attribute path on the parameter object corresponding to the hierarchical path information; and the variable replacement submodule replaces the parameter variable with the actual value.

[0125] This embodiment provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the structured query language generation method described in any of the foregoing embodiments.

[0126] The computer-readable storage media include, but are not limited to, physical media capable of storing computer programs, such as hard disks, USB flash drives, flash memory, read-only memory (ROM), and random access memory (RAM).

[0127] The various embodiments in this specification are described in a progressive, parallel, or combined manner. Each embodiment focuses on the differences from other embodiments, and the same or similar parts between the embodiments can be referred to each other.

[0128] It should be noted that, in the description of this application, the accompanying drawings and embodiments are illustrative rather than restrictive. The same reference numerals throughout the embodiments identify the same structures. It should also be noted that, in this document, relational terms such as "first" and "second" are used merely to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that an article or device comprising a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such an article or device. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in an article or device comprising the aforementioned element.

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

Claims

1. A method for generating a structured query language, characterized in that, include: Obtain a parameter set, which includes a nested set of parameter values; Obtain an SQL template, which includes parameter variables that carry hierarchical path information; Construct a parameter object that maps the parameter set, and the parameter object supports obtaining the parameter values ​​of each layer in the parameter set through an access path; Based on the hierarchical path information in the SQL template, the corresponding parameter values ​​are obtained from the parameter object; And use the obtained parameter values ​​to replace the parameter variables in the SQL template; Based on the result of the replacement, the SQL statement corresponding to the parameter set is obtained.

2. The method according to claim 1, characterized in that, The SQL template also includes code block markers, which are used to define the range of executable code segments; The method further includes: Retrieve SQL fragments from code segment ranges defined by code block markers; The SQL statement corresponding to the parameter set obtained based on the replacement result includes: The SQL fragment and the replaced result are concatenated to obtain the target SQL statement corresponding to the parameter set.

3. The method according to claim 2, characterized in that, The process of obtaining SQL fragments from the code segment range defined by the code block marker includes: Read the SQL template line by line; When the start marker is detected, collect the lines of code between the start marker and the end marker; When the end marker is detected, the collected lines of code are executed in the namespace to obtain the SQL fragment; The namespace includes the parameter object and predefined output variables. The predefined output variables are used to store the SQL fragments, and the code fragments are configured to assign the generated SQL fragments to the predefined output variables.

4. The method according to claim 2, characterized in that, The SQL fragment includes at least one of the following: a first fragment, a second fragment, a third fragment, and a fourth fragment; The first segment is used to generate the list of fields to be queried in the query statement; the second segment is used to generate the filter conditions in the query statement; the third segment is used to generate the grouping fields in the query statement; and the fourth segment is used to generate the sorting conditions in the query statement.

5. The method according to claim 2, characterized in that, The step of concatenating the SQL fragments involves concatenating the processed content sequentially according to the original row order of the SQL template; wherein the processed content includes SQL fragment rows, rows where parameter values ​​have been replaced, and rows that are retained as is.

6. The method according to claim 2, characterized in that, The executable code segment range defined by the code block marker in the SQL template is a script code segment; The script code snippet obtains the target value corresponding to the nested key in the parameter set through the dot chain access interface of the parameter object. The target value is used to obtain the content and structure of the SQL snippet to be generated.

7. The method according to claim 1, characterized in that, The parameter object that constructs the mapping of the parameter set includes: Iterate through the key-value pairs of the parameter set; If the value of the current key-value pair is a dictionary, then the child parameter object corresponding to the current key-value pair is recursively constructed, and the child parameter object is used as a property of the parent parameter object. If the current key-value pair is a list containing dictionary elements, then recursively construct the child parameter objects corresponding to each dictionary element in the list, and use the child parameter objects as properties of the parent parameter object. If the value of the current key-value pair is not a dictionary type, then the value of the current key-value pair will be directly used as a property of the parent parameter object.

8. The method according to claim 1, characterized in that, The construction of the parameter object mapping the parameter set also includes: generating the class name of the parameter object based on the timestamp to avoid class name conflicts in concurrent scenarios.

9. The method according to claim 1, characterized in that, The step of replacing the parameter variables in the SQL template using the obtained parameter values ​​includes: Identify the hierarchical path information carried in the parameter variables; The actual values ​​are obtained level by level along the attribute path corresponding to the hierarchical path information on the parameter object; Replace the parameter variable with the actual value.

10. A structured query language generation device, characterized in that, include: A parameter acquisition module is used to acquire a parameter set, which includes a nested set of parameter values. The template acquisition module is used to acquire an SQL template, which includes parameter variables and carries hierarchical path information. A parameter object construction module is used to construct parameter objects that map the parameter set. The parameter objects support obtaining parameter values ​​of each layer in the parameter set through access paths. The parsing and replacement module is used to obtain the corresponding parameter values ​​from the parameter object based on the hierarchical path information in the SQL template; And use the obtained parameter values ​​to replace the parameter variables in the SQL template; The concatenation module is used to obtain the SQL statement corresponding to the parameter set based on the replacement result.

Images