Method, device, computer equipment and storage medium for generating SQL statements

Abstract

The present invention discloses a method, device, computer equipment and storage medium for generating SQL statements, and belongs to the field of database technology. The method for generating SQL statements utilizes the reusability of rule templates, parses the acquired rule information to obtain table names and rule data, obtains all template identifiers associated with the parameters according to the table names and the parameters mapped by the rule data in the parameter mapping table, determines the corresponding rule template according to the acquired template identifier, and then inputs the acquired parameters into the rule template to automatically generate SQL statements, without manually writing SQL statements, thereby reducing the workload of personnel and improving the efficiency of generating SQL statements.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method, device, computer equipment and storage medium for generating SQL statements. Background Art

[0002] In the quality control link of the data middle platform, two types of rules, basic rules and business rules, are generally used to detect data quality. The verification of basic rules (such as non-empty judgment of fields, uniqueness verification, dictionary value verification, etc.) is relatively simple, and the verification logic is relatively easy to abstract to form basic rules. Most of the data quality tools on the market now also abstract these basic verification rules into methods for users to use. Business rule verification mainly focuses on verifying the business attributes of data. Usually, this type of business rule verification demand comes from the business system. It is to help the business system to more deeply verify whether the data meets the needs of the business system. Therefore, business rule detection is usually more complicated. At present, business rule verification mainly uses handwritten SQL statements to implement business logic verification, which is time-consuming and labor-intensive, cannot be reused, and is not convenient for unified management. Summary of the invention

[0003] In view of the problem that the SQL statements of existing business rules need to be handwritten and are inefficient, a method, device, computer equipment and storage medium for generating SQL statements are provided to improve the speed of obtaining SQL statements of business rules.

[0004] To achieve the above object, the present invention provides a method for generating an SQL statement, comprising:

[0005] Extract rule information from a database according to preset rules, the rule information including a table name and rule data, parse the rule information, and obtain the table name and the rule data; obtain parameters and template identifiers mapped to the table name and the rule data in a parameter mapping table, wherein each table name in the parameter mapping table corresponds to a parameter, each rule data corresponds to a parameter, each parameter is associated with at least one template identifier, and each template identifier is associated with a rule template;

[0006] Determine the rule template according to the acquired template identifier;

[0007] The table name and rule data are input into the rule template based on the acquired parameters to generate an SQL statement.

[0008] Optionally, the step of obtaining the table name and the parameters and template identifiers mapped to the rule data in a parameter mapping table may also include:

[0009] Matching the acquired table name and rule data with data in a storage module, and determining whether the table name and rule data are stored in the storage module, the storage module being used to store the table name and rule data associated with the generated SQL statement;

[0010] If the table name and the rule data are stored in the storage module, extracting rule information from the database according to a preset rule, the rule information including the table name and the rule data, parsing the rule information, and obtaining the table name and the rule data;

[0011] If the table name and the rule data are not stored in the storage module, the obtained table name and the rule data are stored in the storage module, and the parameters and template identifiers mapped by the table name and the rule data in the parameter mapping table are obtained.

[0012] Optionally, obtaining the table name and the parameters and template identifiers mapped to the rule data in a parameter mapping table includes:

[0013] Respectively matching the table name and the rule data with the parameters in the parameter mapping table to obtain a table name parameter that matches the table name and a rule parameter that matches the rule data;

[0014] All template identifiers associated with the table name parameter and the template identifier associated with the rule data are obtained.

[0015] Optionally, the rule data includes field data, or field data and dictionary data;

[0016] The determining the rule template according to the acquired template identifier includes:

[0017] identifying whether the rule data includes dictionary data;

[0018] If the rule data does not include dictionary data, the rule parameters that match the field data are field parameters;

[0019] The set of all template identifiers associated with the table name parameter is taken as a first set, and the set of all template identifiers associated with the field parameter is taken as a second set;

[0020] The template identifier common to the first set and the second set is used as a target identifier, and a rule template corresponding to the target identifier is acquired.

[0021] Optionally, also include:

[0022] If the rule data includes dictionary data, it is determined that the rule data includes both field data and the dictionary data, the rule parameters matching the field data are field parameters, and the rule parameters matching the dictionary data are dictionary parameters;

[0023] The set of all template identifiers associated with the table name parameter is taken as a first set, the set of all template identifiers associated with the field parameter is taken as a second set, and the set of all template identifiers associated with the dictionary parameter is taken as a third set;

[0024] The template identifier common to the first set, the second set and the third set is used as a target identifier, and a rule template corresponding to the target identifier is acquired.

[0025] Optionally, the rule template includes: an operation item, a data source and a logic item, and the data source includes a table name parameter;

[0026] The rule template meets at least one of the following conditions:

[0027] The operation item includes a rule parameter, and the rule parameter is a field parameter; or

[0028] The operation item includes rule parameters, and the rule parameters include field parameters and dictionary parameters.

[0029] Optionally, the table name parameter and the rule parameter in the rule template both carry a preset identifier;

[0030] The step of inputting the table name and rule data into the rule template based on the acquired parameters to generate an SQL statement comprises:

[0031] The preset identifier in the rule template is identified, the table name parameter in the rule template is replaced by the table name, the rule parameter in the rule template is replaced by the rule data, and the SQL statement is generated.

[0032] To achieve the above object, the present invention also provides a device for generating SQL statements, comprising:

[0033] A parsing unit, configured to extract rule information from a database according to preset rules, wherein the rule information includes a table name and rule data, and parse the rule information to obtain the table name and the rule data;

[0034] A processing unit, used to obtain the table name and the parameters and template identifiers mapped to the rule data in a parameter mapping table, wherein each table name in the parameter mapping table corresponds to a parameter, each rule data corresponds to a parameter, each parameter is associated with at least one template identifier, and each template identifier is associated with a rule template;

[0035] A determination unit, configured to determine the rule template according to the acquired template identifier;

[0036] A generating unit is used to input the table name and rule data into the rule template based on the acquired parameters to generate an SQL statement.

[0037] To achieve the above objectives, the present invention also provides a computer device, which includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the steps of the above method when executing the computer program.

[0038] To achieve the above object, the present invention further provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program implements the steps of the above method when executed by a processor.

[0039] The method, device, computer equipment and storage medium for generating SQL statements provided by the present invention utilize the reusability of rule templates, parse the acquired rule information to obtain table names and rule data, obtain all template identifiers associated with the parameters according to the table names and the parameters mapped by the rule data in the parameter mapping table, determine the corresponding rule template according to the acquired template identifier, and then input the acquired parameters into the rule template to automatically generate SQL statements, without the need for manual SQL statements, thereby reducing the workload of personnel and improving the efficiency of generating SQL statements. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] Figure 1 A method flow chart of an embodiment of the method for generating SQL statements according to the present invention;

[0041] Figure 2 A method flow of an embodiment of determining a rule template according to an acquired template identifier according to the present invention;

[0042] Figure 3 A module diagram of an embodiment of the device for generating SQL statements according to the present invention;

[0043] Figure 4 A schematic diagram of the hardware architecture of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0044] In order to make the purpose, technical solutions and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not intended to limit the present application. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of the present application.

[0045] It should be noted that, in the absence of conflict, the embodiments of the present invention and the features in the embodiments may be combined with each other.

[0046] The method, device, computer equipment and storage medium for generating SQL statements provided by the present invention can be applied to the fields of intelligent medical care, medical insurance, finance, banking, etc. The method for generating SQL statements utilizes the reusability of rule templates, parses the acquired rule information to obtain table names and rule data, obtains all template identifiers associated with the parameters according to the table names and the parameters mapped by the rule data in the parameter mapping table, determines the corresponding rule template according to the acquired template identifier, and then inputs the acquired parameters into the rule template to automatically generate SQL statements, without manually writing SQL statements, thereby reducing the workload of personnel and improving the efficiency of generating SQL statements.

[0047] Embodiment 1

[0048] See also Figure 1 A method for generating an SQL statement in this embodiment includes the following steps:

[0049] S1. Extract rule information from a database according to preset rules, the rule information including a table name and rule data, parse the rule information, and obtain the table name and the rule data.

[0050] In one embodiment, the database includes at least one rule information, and the rule information is text information representing the business rules configured according to business needs. The table name refers to the name of the table involved in the above business rules, and the rule data is text information representing the specific content of the above business rules. Take the employee payment details table stored in the medical insurance database as an example: the rule information is "the personnel categories in the employee payment details table can only be in-service, retired, resigned, and old Red Army." Among them, the table name is "Employee Payment Details Table"; the rule data is "the personnel categories can only be in-service, retired, resigned, and old Red Army."

[0051] In one embodiment, the preset rule is an extraction rule, which can be to extract the rule information in the database one by one according to the order of the first letters in the rule information (such as the order of 26 letters), or other pre-set or randomly arranged order.

[0052] In one embodiment, the rule data includes one or more of field data and dictionary data. The field data is text information representing the first-level classification information in the text information, and the dictionary data belongs to the text information of the second-level (child) classification information under the first-level (parent) classification information. For example, the rule information is "personnel categories can only be in-service, retired, resigned, and old Red Army", where "personnel category" belongs to the first-level classification information, and "personnel category" is field data; "in-service, retired, resigned, and old Red Army" belongs to the second-level classification information, and in-service, retired, resigned, and old Red Army are all dictionary data.

[0053] In this embodiment, the rule information can also be stored in the server. The server can be an independent server or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDN), as well as big data and artificial intelligence platforms.

[0054] In this embodiment, the field data may represent one field, or the field data may represent multiple fields, such as a field range consisting of several fields.

[0055] In a possible implementation, the data corresponding to the table name may be medical data, such as data in a personal health record table, data in a prescription form, data in an examination report, and the like.

[0056] Specifically, step S1 is used to perform semantic recognition on the rule information to obtain the table name and the rule data.

[0057] In this embodiment, natural language processing (NLP) technology can be used for semantic recognition, and word segmentation, named entities, part-of-speech tagging, synonym segmentation, word vectors, etc. in the rule information can be identified through word-level analysis to obtain table names and rule data.

[0058] In a preferred embodiment, before executing step S1, a corresponding parameter mapping table may be configured according to the table name corresponding to the ods layer verified by the rule information.

[0059] S2. Obtain the table name and the parameters and template identifier mapped to the rule data in the parameter mapping table.

[0060] Among them, each table name in the parameter mapping table corresponds to a parameter, each rule data corresponds to a parameter, each parameter is associated with at least one template identifier, and each template identifier is associated with a rule template.

[0061] In this embodiment, the parameter mapping table includes: implementation rule information, parameters, parameter categories and template identifiers. The so-called implementation rule information refers to the table name, rule data (field data, dictionary data) and other data in the rule information. The parameter is the table name (or data source) data corresponding to the ods (full name Operational Data Store, a system used to store business data of multiple data sources) layer where the application rule information is verified. The so-called parameter categories include table name parameters, rule parameters (field parameters, dictionary parameters), etc.

[0062] In this embodiment, a large number of rule templates can be pre-configured according to the business requirements proposed by the business system, thereby forming a rule base composed of a large number of rule templates to unify data standards.

[0063] It should be noted that the rule template includes: operation items, data sources and logic items, and the data source includes table name parameters. The rule template meets at least one of the following conditions:

[0064] The operation item includes a rule parameter, and the rule parameter is a field parameter; or

[0065] The operation item includes rule parameters, and the rule parameters include field parameters and dictionary parameters.

[0066] In actual applications, considering that the verification contents of business rules are different, business rules may involve dictionary data or may not involve dictionary data. Therefore, the rule template may include dictionary parameters in addition to table name parameters and field parameters.

[0067] In this embodiment, the field parameters in the rule template correspond to the field data in the rule information; the dictionary parameters in the rule template correspond to the dictionary data in the rule information; and the table name parameters in the rule template correspond to the table name in the rule information.

[0068] Taking "Employee Payment Details Table" as the table name, "Personnel Category" as the field data, and "In-service, Retired, Retired, and Old Red Army" as the dictionary data as an example, parameter mapping table 1 is as follows:

[0069] Table 1

[0070] Actual rule information parameter Parameter Category Template Logo Employee payment details ODS_STAF_PSN_CLCT_DETAIL_D Table name parameter ID1, ID2, ID3 Personnel Category PSN_TYPE Field parameters ID1, ID4, ID9 Working 01 Dictionary Parameters ID1, ID2, ID6 retire 02 Dictionary Parameters ID1, ID2, ID6 Retirement 03 Dictionary Parameters ID1, ID2, ID6 Old Red Army 04 Dictionary Parameters ID1, ID2, ID6

[0071] It can be seen from the above table that the table name parameter corresponding to the "Employee Payment Details Table" is "ODS_STAF_PSN_CLCT_DETAIL_D", the field parameter corresponding to the "Personnel Category" is "PSN_TYPE", the dictionary parameter value corresponding to "Employed" is 01, the dictionary parameter value corresponding to "Retired" is 02, the dictionary parameter value corresponding to "Retired" is 03, and the dictionary parameter value corresponding to "Old Red Army" is 04.

[0072] Further, step S2 may include the following steps:

[0073] S21. Match the table name and the rule data with the parameters in the parameter mapping table respectively, and obtain table name parameters matching the table name and rule parameters matching the rule data.

[0074] S22. Acquire all template identifiers associated with the table name parameter and the template identifier associated with the rule data.

[0075] In this embodiment, the parameter mapping table is a form pre-configured according to the verified database. Different forms are configured according to parameter types (table name parameter class, field parameter class, dictionary parameter class), or all parameter types can be aggregated in the same form.

[0076] In a preferred embodiment, the database may include at least two pieces of rule information, and before executing step S2, further includes:

[0077] A. Match the obtained table name and the rule data with the data in the storage module, and determine whether the table name and the rule data are stored in the storage module. If so, it means that the rule information associated with the table name and the rule data has been parsed before executing step S1, and return to step S1, and extract the next rule information in the database according to the preset rule for parsing; if not, it means that the table name and the rule data are not stored in the storage module, and the obtained table name and the rule data are stored in the storage module, and step S2 is executed.

[0078] The storage module is used to store the table name and the rule data associated with the generated SQL statement.

[0079] S3. Determine the rule template according to the acquired template identifier.

[0080] Further, see Figure 2 The step S3 shown includes the following steps:

[0081] S31. Identify whether the rule data includes dictionary data. If so, determine that the rule data includes both field data and dictionary data, the rule parameters matching the field data are field parameters, and the rule parameters matching the dictionary data are dictionary parameters, and execute step S34; if not, the rule parameters matching the field data are field parameters, and execute step S32.

[0082] S32. The set of all template identifiers associated with the table name parameter is taken as a first set, and the set of all template identifiers associated with the field parameter is taken as a second set;

[0083] S33. Take the template identifier common to the first set and the second set as a target identifier, and obtain a rule template corresponding to the target identifier.

[0084] S34. The set of all template identifiers associated with the table name parameter is taken as the first set, the set of all template identifiers associated with the field parameter is taken as the second set, and the set of all template identifiers associated with the dictionary parameter is taken as the third set;

[0085] S35. Take the template identifier common to the first set, the second set and the third set as a target identifier, and obtain a rule template corresponding to the target identifier.

[0086] In this embodiment, if multiple target identifiers are obtained, multiple rule templates may be determined, and each target identifier corresponds to a rule template.

[0087] As an example and not a limitation, when multiple rule information are obtained at the same time, the process of determining the rule template is as follows: a1 extracts the table name and rule data in a rule information according to a preset order (such as: the default sorting order, or the order of acquisition, etc.), and stores the table name and rule data as an extracted information in a preset storage module; a2 compares the table name and rule data of the extracted rule information with the parameters mapped in the parameter mapping table to determine the matching parameters; a3 determines the template identifier that matches the table name and the rule data at the same time according to the matching parameters, thereby determining at least one rule template that matches the table name and the rule data (multiple rule templates can be matched) according to the template identifier; a4 extracts the table name and rule data in the next rule information according to the preset order; a5 matches the extracted table name and rule data with the extraction information stored in the storage module to determine whether they match. If they match, repeat step a4; if not, execute step a2 until the extraction of table names and rule data in all rule information is completed.

[0088] In this embodiment, the table name parameter and the rule parameter in the rule template are both identified by a preset identifier.

[0089] As an example but not a limitation, the preset identifier is #{}#, and the verification rule information is "the personnel categories in the employee payment details table can only be in-service, retired, retired, and veteran Red Army", and the SQL rule template is as follows:

[0090] SELECT count(1)statis

[0091] FROM#{Employee Payment Details List}#

[0092] WHERE#{Personnel Category}#not in(#{Personnel Category:[On-the-job, Retired, Retired, Old Red Army]}#)

[0093] S4. Input the table name and rule data into the rule template based on the acquired parameters to generate an SQL statement.

[0094] Specifically, step S4 is adopted to identify the preset identifier in the rule template, the table name is replaced by the table name parameter in the rule template, the rule data is replaced by the rule parameter in the rule template, and the SQL statement is generated.

[0095] Take "Employee Payment Details Table" as the table name, "Personnel Category" as the field data, "In-service, Retired, Resigned, and Old Red Army" as the dictionary data, and the verification rule information is "The personnel categories of the employee payment details table can only be in-service, retired, resigned, and old Red Army" as an example. After replacing the above data with the corresponding positions in the above SQL rule template in combination with Table 1, the generated SQL statement is:

[0096] SELECT count(1)statis

[0097] FROM ODS_STAF_PSN_CLCT_DETAIL_D

[0098] WHERE PSN_TYPE not in('01','02','03','04')

[0099] In this embodiment, the parameters in the rule template (table name parameters, field parameters, dictionary parameters) and the actual rule information (such as table name, field data, dictionary data, etc.) can be matched, and business rule verification SQL statements that can be actually executed can be dynamically generated in batches, thereby realizing business rule verification.

[0100] In step S4, if the rule template includes a sub-template, the sub-template is stripped out, and the rule template parameters are replaced in the outer layer first, and then the parameters in the inner layer (ie, the sub-template) are replaced.

[0101] In this embodiment, the generated SQL statement can be sent to the computing engine (SPARK), which calculates the task time and resource space, applies to the resource manager for resource allocation based on the task time and resource space, creates a task, sends the task to the corresponding resource, executes the task operation, and obtains the task result.

[0102] In this embodiment, the method for generating SQL statements utilizes the reusability of rule templates, and parses the obtained rule information to obtain table names and rule data, and obtains all template identifiers associated with the parameters according to the table names and the parameters mapped by the rule data in the parameter mapping table, and determines the corresponding rule template according to the obtained template identifier, thereby automatically generating SQL statements by inputting the table names and rule data into the rule template with the obtained parameters, without the need for manual handwriting of SQL statements, thus reducing the workload of personnel and improving the efficiency of generating SQL statements. The present invention can be implemented in the data middle station, can ensure the correctness of downstream business system data, realizes one-time configuration and multi-location use, and can effectively reduce the workload of business data verification.

[0103] Embodiment 2

[0104] See also Figure 3 In this embodiment, a device 1 for generating SQL statements includes: a parsing unit 11, a processing unit 12, a determining unit 13 and a generating unit 14.

[0105] The parsing unit 11 is used to extract rule information from the database according to preset rules, wherein the rule information includes a table name and rule data, and parse the rule information to obtain the table name and the rule data.

[0106] The database includes at least one rule information. The preset rule is an extraction rule, which can be to extract the rule information in the database one by one according to the order of the first letters in the rule information, or other pre-set or randomly arranged order. The rule data includes field data, or field data and dictionary data.

[0107] The above rule information can also be stored in a server. The server can be an independent server or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDN), as well as big data and artificial intelligence platforms.

[0108] Take the rule data "Personnel categories can only be in-service, retired, resigned, and veteran Red Army" as an example: "Personnel category" is field data; "in-service, retired, resigned, and veteran Red Army" are all dictionary data.

[0109] In this embodiment, the field data may represent one field, or the field data may represent multiple fields, such as a field range consisting of several fields.

[0110] In this embodiment, rule information mainly refers to business rules. Rule information is text information configured according to business needs. Take the verification of the employee payment details table stored in the medical insurance database as an example: the verified rule information is "the personnel categories of the employee payment details table can only be in-service, retired, resigned, and veteran Red Army". Among them, the table name is "Employee Payment Details Table"; the rule data is "the personnel categories can only be in-service, retired, resigned, and veteran Red Army".

[0111] In a possible implementation, the data corresponding to the table name may be medical data, such as data in a personal health record table, data in a prescription form, data in an examination report, and the like.

[0112] Specifically, the parsing unit 11 may perform semantic recognition on the rule information to obtain the table name and the rule data.

[0113] In this embodiment, natural language processing (NLP) technology can be used for semantic recognition, and word segmentation, named entities, part-of-speech tagging, synonym segmentation, word vectors, etc. in the rule information can be identified through word-level analysis to obtain table names and rule data.

[0114] In a preferred embodiment, before parsing the rule information through the parsing unit 11, a corresponding parameter mapping table can be configured according to the table name corresponding to the ods layer verified by the rule information.

[0115] The processing unit 12 is used to obtain the table name and the parameters and template identifier mapped by the rule data in the parameter mapping table.

[0116] Among them, each table name in the parameter mapping table corresponds to a parameter, each rule data corresponds to a parameter, each parameter is associated with at least one template identifier, and each template identifier is associated with a rule template.

[0117] In this embodiment, the parameter mapping table includes: implementation rule information, parameters, parameter categories and template identifiers. The so-called implementation rule information refers to the table name, rule data (field data, dictionary data) and other data in the rule information. The parameter is the table name (or data source) data corresponding to the ods (full name Operational Data Store, a system used to store business data of multiple data sources) layer where the application rule information is verified. The so-called parameter categories include table name parameters, rule parameters (field parameters, dictionary parameters), etc.

[0118] In this embodiment, a large number of rule templates can be pre-configured according to the business requirements proposed by the business system, thereby forming a rule base composed of a large number of rule templates to unify data standards.

[0119] It should be noted that the rule template includes: operation items, data sources and logic items, and the data source includes table name parameters. The rule template meets at least one of the following conditions:

[0120] The operation item includes a rule parameter, and the rule parameter is a field parameter; or

[0121] The operation item includes rule parameters, and the rule parameters include field parameters and dictionary parameters.

[0122] In actual applications, considering that the verification contents of business rules are different, business rules may involve dictionary data or may not involve dictionary data. Therefore, the rule template may include dictionary parameters in addition to table name parameters and field parameters.

[0123] In this embodiment, the field parameters in the rule template correspond to the field data in the rule information; the dictionary parameters in the rule template correspond to the dictionary data in the rule information; and the table name parameters in the rule template correspond to the table name in the rule information.

[0124] Taking "Employee Payment Details Table" as the table name, "Personnel Category" as the field data, and "In-service, Retired, Resigned, Old Red Army" as the dictionary data as an example, parameter mapping table 1: the table name parameter corresponding to the "Employee Payment Details Table" is "ODS_STAF_PSN_CLCT_DETAIL_D", the field parameter corresponding to "Personnel Category" is "PSN_TYPE", the dictionary parameter value corresponding to "In-service" is 01, the dictionary parameter value corresponding to "Retired" is 02, the dictionary parameter value corresponding to "Resigned" is 03, and the dictionary parameter value corresponding to "Old Red Army" is 04.

[0125] Furthermore, the processing unit 12 is used to match the table name and the rule data with the parameters in the parameter mapping table respectively, and obtain the table name parameter matching the table name, and the rule parameter matching the rule data. The processing unit 12 is also used to obtain all template identifiers associated with the table name parameter, and the template identifier associated with the rule data.

[0126] In this embodiment, the parameter mapping table is a form pre-configured according to the verified database. Different forms are configured according to parameter types (table name parameter class, field parameter class, dictionary parameter class), or all parameter types can be aggregated in the same form.

[0127] In a preferred embodiment, the database may include at least two pieces of rule information, and the device 1 for generating SQL statements may further include: a matching unit.

[0128] A matching unit is used to match the obtained table name and the rule data with the data in the storage module, and determine whether the table name and the rule data are stored in the storage module. If so, it means that the rule information associated with the table name and the rule data has been parsed before the parsing unit 11 parses the rule information, and the parsing unit 11 can extract the next rule information from the database according to the preset rule for parsing; if not, it means that the table name and the rule data are not stored in the storage module, and the obtained table name and the rule data are stored in the storage module, and the processing unit 12 obtains the parameters and template identifiers mapped to the table name and the rule data in the parameter mapping table.

[0129] The storage module is used to store the table name and the rule data associated with the generated SQL statement.

[0130] The determination unit 13 is configured to determine the rule template according to the acquired template identifier.

[0131] Further, the determination unit 13 may include a recognition module, a first acquisition module and a second acquisition module.

[0132] The identification module is used to identify whether the rule data includes dictionary data.

[0133] The first acquisition module is used to, when the rule data includes dictionary data, take the set of all template identifiers associated with the table name parameter as the first set, take the set of all template identifiers associated with the field parameter as the second set, take the template identifier common to the first set and the second set as the target identifier, and acquire the rule template corresponding to the target identifier.

[0134] The second acquisition module is used to, when the rule data does not include dictionary data, take the set of all template identifiers associated with the table name parameter as the first set, take the set of all template identifiers associated with the field parameter as the second set, take the set of all template identifiers associated with the dictionary parameter as the third set, take the template identifier common to the first set, the second set and the third set as the target identifier, and acquire the rule template corresponding to the target identifier.

[0135] In this embodiment, if multiple target identifiers are obtained, multiple rule templates may be determined, and each target identifier corresponds to a rule template.

[0136] As an example and not a limitation, when multiple rule information are obtained at the same time, the process of determining the rule template is as follows: a1 extracts the table name and rule data in a rule information according to a preset order (such as: the default sorting order, or the order of acquisition, etc.), and stores the table name and rule data as an extracted information in a preset storage module; a2 compares the table name and rule data of the extracted rule information with the parameters mapped in the parameter mapping table to determine the matching parameters; a3 determines the template identifier that matches the table name and the rule data at the same time according to the matching parameters, thereby determining at least one rule template that matches the table name and the rule data (multiple rule templates can be matched) according to the template identifier; a4 extracts the table name and rule data in the next rule information according to the preset order; a5 matches the extracted table name and rule data with the extraction information stored in the storage module to determine whether they match. If they match, repeat step a4; if not, execute step a2 until the extraction of table names and rule data in all rule information is completed.

[0137] In this embodiment, the table name parameter and the rule parameter in the rule template are both identified by a preset identifier.

[0138] The generating unit 14 is used to input the table name and rule data into the rule template based on the acquired parameters to generate an SQL statement.

[0139] Specifically, the generating unit 14 is used to identify the preset identifier in the rule template, replace the table name parameter in the rule template with the table name, replace the rule parameter in the rule template with the rule data, and generate the SQL statement.

[0140] In this embodiment, the parameters in the rule template (table name parameters, field parameters, dictionary parameters) and the actual rule information (such as table name, field data, dictionary data, etc.) can be matched, and business rule verification SQL statements that can be actually executed can be dynamically generated in batches, thereby realizing business rule verification.

[0141] In step S4, if the rule template includes a sub-template, the sub-template is stripped out, and the rule template parameters are replaced in the outer layer first, and then the parameters in the inner layer (ie, the sub-template) are replaced.

[0142] In this embodiment, the generated SQL statement can be sent to the computing engine (SPARK), which calculates the task time and resource space, applies to the resource manager for resource allocation based on the task time and resource space, creates a task, sends the task to the corresponding resource, executes the task operation, and obtains the task result.

[0143] In this embodiment, the device 1 for generating SQL statements utilizes the reusability of rule templates, and parses the acquired rule information through the parsing unit 11 to obtain the table name and rule data. The processing unit 12 obtains all template identifiers associated with the parameters according to the table name and the parameters mapped by the rule data in the parameter mapping table. The determination unit 13 determines the corresponding rule template according to the acquired template identifier, so that the generation unit 14 inputs the acquired parameters into the rule template to automatically generate SQL statements, without the need for manual handwriting of SQL statements, thereby reducing the workload of personnel and improving the efficiency of generating SQL statements. The present invention can be implemented in the data middle station, can ensure the correctness of the downstream business system data, realizes one-time configuration and multi-location use, and can effectively reduce the workload of business data verification.

[0144] Embodiment 3

[0145] To achieve the above-mentioned purpose, the present invention further provides a computer device 2, which includes multiple computer devices 2. The components of the device 1 for generating SQL statements in the second embodiment can be dispersed in different computer devices 2. The computer device 2 can be a smart phone, tablet computer, laptop computer, desktop computer, rack server, blade server, tower server or cabinet server (including an independent server or a server cluster composed of multiple servers) that executes a program. The computer device 2 of this embodiment at least includes but is not limited to: a memory 21, a processor 23, a network interface 22 and a device 1 for generating SQL statements that can be interconnected through a system bus (refer to Figure 4 ). It should be pointed out that Figure 4 Only a computer device 2 having components is shown, but it should be understood that implementing all of the components shown is not a requirement and more or fewer components may alternatively be implemented.

[0146] In this embodiment, the memory 21 includes at least one type of computer-readable storage medium, and the readable storage medium includes flash memory, hard disk, multimedia card, card-type memory (for example, SD or DX memory, etc.), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, etc. In some embodiments, the memory 21 can be an internal storage unit of the computer device 2, such as a hard disk or memory of the computer device 2. In other embodiments, the memory 21 can also be an external storage device of the computer device 2, such as a plug-in hard disk equipped on the computer device 2, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. Of course, the memory 21 can also include both the internal storage unit of the computer device 2 and its external storage device. In this embodiment, the memory 21 is generally used to store the operating system and various application software installed on the computer device 2, such as the program code of the method for generating SQL statements in embodiment one. In addition, the memory 21 can also be used to temporarily store various types of data that have been output or are to be output.

[0147] The processor 23 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chips in some embodiments. The processor 23 is generally used to control the overall operation of the computer device 2, such as executing control and processing related to data interaction or communication with the computer device 2. In this embodiment, the processor 23 is used to run the program code stored in the memory 21 or process data, such as running the device 1 for generating SQL statements.

[0148] The network interface 22 may include a wireless network interface or a wired network interface, and the network interface 22 is generally used to establish a communication connection between the computer device 2 and other computer devices 2. For example, the network interface 22 is used to connect the computer device 2 to an external terminal through a network, and to establish a data transmission channel and a communication connection between the computer device 2 and the external terminal. The network may be a wireless or wired network such as an Intranet, the Internet, the Global System of Mobile communication (GSM), Wideband Code Division Multiple Access (WCDMA), a 4G network, a 5G network, Bluetooth, or Wi-Fi.

[0149] It should be pointed out that Figure 4 Only computer device 2 is shown with components 21 - 23 , but it should be understood that implementation of all of the components shown is not a requirement, and more or fewer components may alternatively be implemented.

[0150] In this embodiment, the device 1 for generating SQL statements stored in the memory 21 can also be divided into one or more program modules, and the one or more program modules are stored in the memory 21 and executed by one or more processors (processor 23 in this embodiment) to complete the present invention.

[0151] Embodiment 4

[0152] To achieve the above purpose, the present invention also provides a computer-readable storage medium, which includes multiple storage media, such as flash memory, hard disk, multimedia card, card-type memory (for example, SD or DX memory, etc.), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, server, App application mall, etc., on which a computer program is stored, and the program realizes corresponding functions when executed by the processor 23. The computer-readable storage medium of this embodiment is used to store the device 1 for generating SQL statements, and when executed by the processor 23, the method for generating SQL statements of embodiment 1 is realized.

[0153] The serial numbers of the above embodiments of the present invention are only for description and do not represent the advantages or disadvantages of the embodiments.

[0154] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method.

[0155] The above are only preferred embodiments of the present invention, and are not intended to limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made using the contents of the present invention specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A method for generating an SQL statement, characterized in that: include: Extracting rule information from a database according to preset rules, the rule information including a table name and rule data, parsing the rule information, and obtaining the table name and the rule data; Matching the acquired table name and rule data with data in a storage module, and determining whether the table name and rule data are stored in the storage module, the storage module being used to store the table name and rule data associated with the generated SQL statement; If the table name and the rule data are stored in the storage module, extracting rule information from the database according to a preset rule, the rule information including the table name and the rule data, parsing the rule information, and obtaining the table name and the rule data; If the table name and the rule data are not stored in the storage module, the obtained table name and the rule data are stored in the storage module, and the parameters and template identifiers mapped to the table name and the rule data in the parameter mapping table are obtained; Obtain the table name and the parameters and template identifiers mapped to the rule data in the parameter mapping table, wherein each table name in the parameter mapping table corresponds to a parameter, each rule data corresponds to a parameter, each parameter is associated with at least one template identifier, and each template identifier is associated with a rule template; Determine the rule template according to the acquired template identifier; The table name and rule data are input into the rule template based on the acquired parameters to generate an SQL statement.

2. The method for generating SQL statements according to claim 1, characterized in that: The obtaining of the table name and the parameters and template identifiers mapped by the rule data in the parameter mapping table includes: Respectively matching the table name and the rule data with the parameters in the parameter mapping table to obtain a table name parameter that matches the table name and a rule parameter that matches the rule data; All template identifiers associated with the table name parameter and the template identifier associated with the rule data are obtained.

3. The method for generating SQL statements according to claim 1 or 2, characterized in that: The rule data includes field data, or field data and dictionary data; The determining the rule template according to the acquired template identifier includes: identifying whether the rule data includes dictionary data; If the rule data does not include dictionary data, the rule parameters that match the field data are field parameters; The set of all template identifiers associated with the table name parameter is taken as a first set, and the set of all template identifiers associated with the field parameter is taken as a second set; The template identifier common to the first set and the second set is used as a target identifier, and a rule template corresponding to the target identifier is acquired.

4. The method for generating SQL statements according to claim 3, characterized in that: Also includes: If the rule data includes dictionary data, it is determined that the rule data includes both field data and the dictionary data, the rule parameters matching the field data are field parameters, and the rule parameters matching the dictionary data are dictionary parameters; The set of all template identifiers associated with the table name parameter is taken as a first set, the set of all template identifiers associated with the field parameter is taken as a second set, and the set of all template identifiers associated with the dictionary parameter is taken as a third set; The template identifier common to the first set, the second set and the third set is used as a target identifier, and a rule template corresponding to the target identifier is acquired.

5. The method for generating SQL statements according to claim 1, characterized in that: The rule template includes: an operation item, a data source and a logic item, wherein the data source includes a table name parameter; The rule template meets at least one of the following conditions: The operation item includes a rule parameter, and the rule parameter is a field parameter; or The operation item includes rule parameters, and the rule parameters include field parameters and dictionary parameters.

6. The method for generating SQL statements according to claim 2 or 5, characterized in that: The table name parameter and rule parameter in the rule template all carry preset identifiers; The step of inputting the table name and rule data into the rule template based on the acquired parameters to generate an SQL statement comprises: The preset identifier in the rule template is identified, the table name parameter in the rule template is replaced by the table name, the rule parameter in the rule template is replaced by the rule data, and the SQL statement is generated.

7. A device for generating SQL statements, characterized in that: include: A parsing unit, configured to extract rule information from a database according to preset rules, wherein the rule information includes a table name and rule data, and parse the rule information to obtain the table name and the rule data; a matching unit, configured to match the obtained table name and the rule data with data in a storage module, and determine whether the table name and the rule data are stored in the storage module, wherein the storage module is configured to store the table name and the rule data associated with the generated SQL statement; If the table name and the rule data are stored in the storage module, extracting rule information from the database according to a preset rule, the rule information including the table name and the rule data, parsing the rule information, and obtaining the table name and the rule data; If the table name and the rule data are not stored in the storage module, the obtained table name and the rule data are stored in the storage module, and the parameters and template identifiers mapped to the table name and the rule data in the parameter mapping table are obtained; A processing unit, used to obtain the table name and the parameters and template identifiers mapped to the rule data in a parameter mapping table, wherein each table name in the parameter mapping table corresponds to a parameter, each rule data corresponds to a parameter, each parameter is associated with at least one template identifier, and each template identifier is associated with a rule template; A determination unit, configured to determine the rule template according to the acquired template identifier; A generating unit is used to input the table name and rule data into the rule template based on the acquired parameters to generate an SQL statement.

8. A computer device, characterized in that: The computer device comprises a memory, a processor, and a computer program stored in the memory and executable on the processor, and the processor implements the steps of the method according to any one of claims 1 to 6 when executing the computer program.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images