SQL script simulation execution test method and device, equipment and medium
By reassembling the SQL script into a three-segment test script and using an empty data table for simulation test in the formal environment, the problems of inconsistent metadata and long test cycles in the SQL script availability test are solved, and efficient and accurate test results are achieved .
Patent Information
- Application Number
- CN202411148673.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-21
- Publication Date
- 2024-11-26
AI Technical Summary
In the prior art, SQL script availability testing has problems of metadata inconsistency and long test cycles, especially when dealing with SQL scripts containing temporary tables and large data volumes, it is difficult to achieve accuracy and efficiency testing.
Ensure metadata consistency and improve testing efficiency by reassembling the SQL script to be tested into a three-segment test script, using empty data tables in a formal environment.
It achieves the improvement of metadata consistency and test coverage of SQL scripts, shortens the test cycle, improves the accuracy and efficiency of tests, and ensures the reliability and security of SQL scripts.
Smart Images

Figure CN119025426A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of SQL script usability testing, and in particular to a SQL script simulation execution test method, device, equipment and medium. Background Art
[0002] Enterprise data warehouse projects contain a wealth of SQL scripts for batch computing of data. As business needs increase and change, a large number of SQL scripts are added every day, and SQL script usability testing is a complex and important issue.
[0003] In the prior art, a test environment with resources and a data warehouse of similar size is usually built in imitation of the formal environment, and then the SQL scripts to be launched are simulated and tested in the test environment. However, this method has the following problems: (1) The metadata of multiple test environments are often inconsistent. Since the real formal data warehouse environment is relatively complex, some temporary tables are not created in the test environment in a 1:1 manner. Therefore, for some SQL scripts containing temporary tables, unless the metadata of the test environment is improved, it is impossible to implement the technical problem of usability testing of such SQL scripts; (2) Some SQL scripts have a large amount of data in the table and take a long time to execute. For example, the execution time ranges from more than 10 minutes to several hours, resulting in a long test cycle, which in turn affects the efficiency of SQL script launch.
[0004] To this end, the present invention provides a SQL script simulation execution test method, device, equipment and medium. Summary of the invention
[0005] Based on this, it is necessary to provide a SQL script simulation execution test method, device, equipment and medium to address the above technical problems. This method improves the test coverage and accuracy by reassembling the SQL script to be tested into a three-stage test script, and using an empty data table in the formal environment system to execute the three-stage test script to replace the original script.
[0006] According to one aspect of the present invention, a SQL script simulation execution test method is provided, comprising: creating a new database and a new user in a data warehouse of a formal environment system, and granting the new user permission to use the new database; obtaining a SQL script to be tested, and using a SQL syntax parser to parse and process the SQL script to be tested to obtain a source table and a target table used by each SQL statement, and capturing syntax anomalies of the SQL script to be tested during the parsing process; reassembling the SQL script to be tested into a three-segment test script; testing the generated three-segment test script in a new database to obtain corresponding test results; based on the test results, determining whether the three-segment test script has error information, and if the three-segment test script has error information, replacing the simulated table name contained in the error information with the original table name of the corresponding source table, and returning the replaced original table name of the source table to the front-end page.
[0007] In some optional implementations of some embodiments, after granting the new user the right to use the new database, the method further includes: granting the new user read-only rights to other related databases included in the data warehouse of the formal environment system.
[0008] In some optional implementations of some embodiments, obtaining the SQL script to be tested, using a SQL syntax parser to parse the SQL script to be tested, and obtaining the source table and target table used by each SQL statement, specifically includes: obtaining the SQL script to be tested, using the Spark Calalyst parser engine to parse the SQL script to be tested into a logical execution plan, obtaining a SQL syntax tree from the logical execution plan, and obtaining the source table and target table used by each SQL statement.
[0009] In some optional implementations of some embodiments, after obtaining the source table, it also includes: determining whether the source table contains a newly created table statement, and if the source table contains a newly created table statement, marking the corresponding source table.
[0010] In some optional implementations of some embodiments, capturing syntax anomalies in the SQL script to be tested during the parsing process specifically includes: capturing syntax anomalies in the SQL script to be tested during the parsing process, generating error information, and returning the error prompt obtained from the error information to the front-end page.
[0011] In some optional implementations of some embodiments, reassembling the SQL script to be tested into a three-segment test script specifically includes: splitting the SQL script to be tested and generating a three-segment test script, wherein the three-segment test script includes a first segment, a second segment, and a third segment.
[0012] In some optional implementations of some embodiments, the first paragraph of content is a statement for creating a simulated table, and the statement for creating a simulated table includes using a CREATE TABLE LIKE statement to create a simulated table with the same structure as the source table in the new database; the second paragraph of content is replacing the original table name of the source table contained in the SQL script to be tested, and the replacement of the original table name of the source table contained in the SQL script to be tested includes replacing the original table name of the source table contained in the SQL script to be tested with the table name of the corresponding simulated table in the new database based on a preset table name replacement rule, and keeping the CREATE TABLE statement unchanged; the third paragraph of content is deleting the created simulated table, and deleting the created simulated table includes using a DROPTABLE statement to delete all simulated tables created in the new database after the test is completed.
[0013] According to a second aspect of the present invention, a SQL script simulation execution test device is provided, comprising: a creation module, used to create a new database and a new user in a data warehouse of a formal environment system, and grant the new user permission to use the new database; an acquisition module, used to acquire the SQL script to be tested; a parsing module, used to parse the SQL script to be tested using an SQL syntax parser, obtain the source table and target table used by each SQL statement, and capture syntax anomalies of the SQL script to be tested during the parsing process; a three-segment test script generation module, used to reassemble the SQL script to be tested into a three-segment test script; a script testing module, used to test the generated three-segment test script in a new database to obtain corresponding test results; an error prompt module, used to determine whether the three-segment test script has error information based on the test results, and if the three-segment test script has error information, replace the simulated table name contained in the error information with the original table name of the corresponding source table, and return the replaced original table name of the source table to the front-end page.
[0014] According to a third aspect of the present invention, an electronic device 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.
[0015] According to a fourth aspect of the present invention, there is provided a computer-readable storage medium storing a computer program, wherein the computer program implements the steps of the above method when executed by a processor.
[0016] The advantages and beneficial effects of the present invention are as follows: the present invention reassembles the SQL script to be tested into a three-stage test script, and uses a formal environment test after complete permission isolation, so as to ensure that the metadata of the SQL script to be tested and the three-stage test script are consistent. At the same time, the use of an empty data table to simulate the test can effectively improve the execution efficiency of the SQL script, and based on the technical idea of equivalent replacement, the execution of the three-stage test script replaces the execution of the original script, which can improve the coverage and accuracy of the test. BRIEF DESCRIPTION OF THE DRAWINGS
[0017] Figure 1 The present invention is a flowchart of a SQL script simulation execution test method according to an embodiment of the present invention.
[0018] Figure 2 The figure is a schematic diagram of a SQL script simulation execution test device according to an embodiment of the present invention.
[0019] Figure 3 Schematic diagram of an electronic device in an embodiment of the present invention. DETAILED DESCRIPTION
[0020] In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below through specific implementation methods in conjunction with the accompanying drawings. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.
[0021] Embodiment 1 Attached Figure 1 A flowchart of a SQL script simulation execution test method provided in this application.
[0022] Reference Figure 1 A SQL script simulation execution test method is applied to the formal environment system, in which the front-end uses the Vue framework to build a responsive interface, and the back-end uses Spring Boot for back-end development. It is used for users to execute and test scripts in a safe environment, and at the same time record execution logs to facilitate problem tracking. The method specifically includes the following steps.
[0023] S1. Create a new database and a new user in the data warehouse of the formal environment system, and grant the new user the right to use the new database, and grant the new user the read-only permission to other related databases contained in the data warehouse of the formal environment system.
[0024] In some embodiments, taking the need to optimize a complex SQL query script as an example, a SQL script simulation execution test method according to the present invention is applied. The SQL query script is used to extract and summarize detailed information of user orders from multiple data tables. The SQL query script not only includes joint queries (JOIN) and subqueries of multiple data tables, but also includes operations such as data aggregation (GROUP BY) and sorting (ORDER BY). Due to the huge amount of data, directly running such an SQL query script in the formal environment system may lead to excessive resource consumption, and any errors may affect the data and system stability in the production environment. Therefore, it is necessary to perform comprehensive syntax verification and execution testing on the SQL query script in a safe test environment to ensure its correctness and degree of optimization. In view of this, it is first necessary to create a safe test environment, create a new database in the data warehouse of the formal environment system, such as: canary_test, create a new user, such as: canary, and grant the new user all permissions to the canary_test database and read-only permissions to other related databases. Create a new database in the formal environment system to ensure the consistency of metadata. At the same time, ensure that the canary user can access and copy the structure of the source table, but cannot modify the actual production data.
[0025] S2. Obtain the SQL script to be tested, and use the SQL syntax parser to parse the SQL script to be tested to obtain the source table and target table used by each SQL statement, and capture the syntax anomalies of the SQL script to be tested during the parsing process.
[0026] In some embodiments, the SQL script to be tested takes the above-mentioned SQL query script as an example, and the SQL script to be tested may be as follows.
[0027] CREATE TABLE prod_db.user_order_summary AS; SELECT u.user_id, u.user_name, COUNT(o.order_id) AS total_orders, SUM(o.order_value) AS total_value; FROM prod_db.users u; JOIN prod_db.orders o ON u.user_id = o.user_id; WHERE o.order_date BETWEEN '2024-01-01' AND '2024-07-01'; GROUP BY u.user_id, u.user_name; ORDER BY total_value DESC.
[0028] In some embodiments, an SQL script to be tested is obtained, and an SQL syntax parser is used to parse the SQL script to be tested to obtain a source table and a target table used by each SQL statement, specifically including: obtaining an SQL script to be tested, using a Spark Calalyst parser engine to parse the SQL script to be tested into a logical execution plan, obtaining an SQL syntax tree from the logical execution plan, and obtaining a source table and a target table used by each SQL statement.
[0029] In some embodiments, after obtaining the source table, the method further includes: determining whether the source table contains a newly created table statement; if the source table contains a newly created table statement, annotating the corresponding source table.
[0030] In some embodiments, during the parsing process, the syntax anomalies of the SQL script to be tested are captured, specifically including: during the parsing process, the syntax anomalies of the SQL script to be tested are captured, an error message is generated, and the error prompt obtained from the error message is returned to the front-end page.
[0031] In some embodiments, the parsePlan method of the Spark Catalyst parser engine is used to parse the SQL script to be tested into a logical execution plan, obtain the SQL syntax tree from the logical execution plan, analyze the source table and target table used by each SQL statement, and mark the source table for subsequent processing based on whether the SQL script to be tested has a newly created table statement. It should be understood that the source tables included in the SQL script to be tested may reuse some existing data tables in the data warehouse, so not all source tables have newly created table statements.
[0032] In some embodiments, during the parsing process, the syntax anomalies of the SQL script to be tested are captured to ensure that all potential syntax problems are captured in the parsing stage. If a syntax anomaly occurs during the parsing process, it means that there is a problem with the syntax detection of the metadata of the SQL script to be tested, and an error prompt is required. The error prompt obtained from the error message is returned to the front-end page, and detailed error information and possible solutions are provided. The solution is pre-configured, and the corresponding solution can be pre-configured according to the error information before the parsing process; the user can quickly locate the problem based on the error location of the error message to reduce debugging time.
[0033] S3. Reassemble the SQL script to be tested into a three-stage test script.
[0034] In some embodiments, reassembling the SQL script to be tested into a three-segment test script specifically includes: splitting the SQL script to be tested and generating a three-segment test script, wherein the three-segment test script includes a first segment, a second segment, and a third segment.
[0035] In some embodiments, the first paragraph is a statement for creating a simulated table, which includes using a CREATE TABLE LIKE statement to create a simulated table with the same structure as the source table in the new database; the second paragraph is replacing the original table name of the source table contained in the SQL script to be tested, which includes replacing the original table name of the source table contained in the SQL script to be tested with the table name of the corresponding simulated table in the new database based on a preset table name replacement rule, and keeping the CREATE TABLE statement unchanged; the third paragraph is deleting the created simulated table, which includes deleting all simulated tables created in the new database after the test is completed, using a DROP TABLE statement.
[0036] In some embodiments, the first paragraph is a statement for creating a simulated table. Before starting the test, you first need to create a corresponding simulated table for the source table. In order to ensure that the newly created simulated table is completely consistent with the source table in structure, you can use the CREATETABLE LIKE statement to create the simulated table. This method directly copies the structure of the source table (including column names, data types, indexes, etc.) without copying the data. The purpose is to generate a simulated table with the same structure as the source table in the canary_test database, so that when the script is tested later, operations can be performed on these simulated tables without affecting the real data in the formal environment. For example, if the source table is located in prod_db.orders, the corresponding simulated table can be named canary_test.prod_db_orders. This naming method can both maintain the readability of the table name and avoid table name conflicts.
[0037] In some embodiments, taking the above-mentioned SQL query script as an example, a simulation table with the same structure as the source table is created in the canary_test database using a CREATE TABLE LIKE statement. The simulation table does not contain data, and the content of the first paragraph is as follows.
[0038] CREATE TABLE canary_test.prod_db_users LIKE prod_db.users; CREATE TABLE canary_test.prod_db_orders LIKE prod_db.orders.
[0039] In some embodiments, the second paragraph replaces the original table name of the source table contained in the SQL script to be tested. In this process, the CREATE TABLE statement needs to be handled with caution because these statements involve the creation of new tables and usually do not need to be modified in the test environment. For example, the original script may contain a statement such as CREATETABLE prod_db.new_tableAS SELECT * FROM prod_db.orders. In the three-stage test script, the CREATE TABLE statement needs to be retained as it is, but the data tables involved are replaced with corresponding simulation tables. In order to ensure the accuracy of this process, each table name in the SQL script needs to be accurately located and parsed, and replaced according to predefined rules. This step can not only prevent the SQL script from accidentally modifying the data in the formal environment when executed in the test environment, but also ensure the authenticity and effectiveness of the test.
[0040] In some embodiments, taking the above-mentioned SQL query script as an example, the table name in the original script is replaced with the corresponding table in canary_test, and the CREATE TABLE statement is kept unchanged. The content of the second paragraph is as follows.
[0041] CREATE TABLE canary_test.user_order_summary AS; SELECT u.user_id, u.user_name, COUNT(o.order_id) AS total_orders, SUM(o.order_value) AS total_value; FROM canary_test.prod_db_users u; JOIN canary_test.prod_db_orders o ON u.user_id = o.user_id; WHERE o.order_date BETWEEN '2024-01-01' AND '2024-07-01'; GROUP BY u.user_id, u.user_name; ORDER BY total_value DESC.
[0042] In some embodiments, the third paragraph is to delete the created simulation table, and delete all simulation tables created during the test process. The purpose is to keep the test environment clean and ensure that subsequent test scripts can be executed in a clean test environment. If the simulation table is not cleaned up after each test, it will not only cause redundancy of data and tables in the database, but may also cause table name conflicts or other unexpected problems during the next test. Therefore, the last part of the generated script usually contains a set of DROP TABLE statements for deleting all simulation tables created in the canary_test database. In this way, the starting point of each test can be guaranteed to be consistent, thereby ensuring the idempotence of the script.
[0043] In some embodiments, taking the above-mentioned SQL query script as an example, after the test is completed, in order to keep the test environment clean, all created simulation tables are deleted, and the content of the third paragraph is as follows.
[0044] DROP TABLE IF EXISTS canary_test.user_order_summary; DROP TABLE IF EXISTS canary_test.prod_db_users; DROP TABLE IF EXISTS canary_test.prod_db_orders.
[0045] In some embodiments, through this three-stage test script, the entire process of script execution can be simulated in the test environment without affecting the actual data; this method can not only detect potential problems in the script, but also provide assistance for subsequent debugging; the cleanup step after each test also ensures the purity of the test environment, so that the next test will not be affected by the previous test. In summary, this method not only improves the safety of the test, but also ensures the repeatability and reliability of the script.
[0046] S4. Test the generated three-stage test script in the new database to obtain the corresponding test results.
[0047] In some embodiments, the generated three-step test script is executed in the canary_test database. Since the simulation table has no real data, the execution efficiency is high. At the same time, if the three-step test script is executed successfully, it means that the syntax is correct and the logic is reasonable. If an error message appears during the execution of the three-step test script, it means that the original script may also have problems in the production environment.
[0048] S5. Based on the test results, determine whether the three-stage test script has error information. If the three-stage test script has error information, replace the simulation table name contained in the error information with the original table name of the corresponding source table, and return the replaced original table name of the source table to the front-end page.
[0049] In some embodiments, if an error message appears during the execution of the script, the error prompt will be fed back to the user in the form of the original table name. For example, if an error occurs when executing the JOIN operation on canary_test.prod_db_users, the feedback error prompt is that the error originates from the prod_db.users table, that is, the simulated table name contained in the error message is replaced with the original table name of the corresponding source table, and the replaced original table name of the source table is returned to the front-end page, which helps users quickly locate the problem so that users can more easily understand the error source table name and error location.
[0050] In some embodiments, the SQL script simulation execution test method provided by the present invention can efficiently and safely test and optimize complex SQL scripts without affecting the production environment, which not only improves the execution efficiency of SQL scripts, but also reduces the risks of the production environment and improves the accuracy and reliability of overall data analysis.
[0051] Embodiment 2 This embodiment provides a SQL script simulation execution test device 200 based on the above embodiment 1. Figure 2 , used to implement the steps of a SQL script simulation execution test method described in the above embodiment 1, the device mainly includes: a creation module 201, an acquisition module 202, a parsing module 203, a three-stage test script generation module 204, a script testing module 205 and an error prompt module 206.
[0052] In some embodiments, the creation module 201 is used to create a new database and a new user in the data warehouse of the formal environment system, and grant the new user permission to use the new database.
[0053] In some embodiments, the acquisition module 202 is used to acquire the SQL script to be tested.
[0054] In some embodiments, the parsing module 203 is used to parse the SQL script to be tested using a SQL syntax parser to obtain the source table and target table used by each SQL statement, and capture syntax anomalies of the SQL script to be tested during the parsing process.
[0055] In some embodiments, the three-segment test script generation module 204 is used to reassemble the SQL script to be tested into a three-segment test script.
[0056] In some embodiments, the script testing module 205 is used to test the generated three-stage test script in the new database to obtain corresponding test results.
[0057] In some embodiments, the error prompt module 206 is used to determine whether the three-stage test script has error information based on the test results. If the three-stage test script has error information, the simulation table name contained in the error information is replaced with the original table name of the corresponding source table, and the replaced original table name of the source table is returned to the front-end page.
[0058] Embodiment 3 This embodiment further provides an electronic device based on the above embodiment 1. Figure 3 , Figure 3 The electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present disclosure.
[0059] like Figure 3 As shown, the electronic device may include a processing device (e.g., a central processing unit, a graphics processing unit, etc.) 301, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 302 or a program loaded from a storage device 308 to a random access memory (RAM) 303. In the RAM 303, various programs and data required for the operation of the electronic device are also stored. The processing device 301, the ROM 302, and the RAM 303 are connected to each other via a bus 304. An input / output (I / O) interface 305 is also connected to the bus 304.
[0060] Typically, the following devices may be connected to the I / O interface 305: an input device 306 including, for example, a touch screen, a touch pad, a keyboard, a mouse, a camera, etc., an output device 307 including, for example, a liquid crystal display (LCD), a speaker, etc., a storage device 308 including, for example, a magnetic tape, a hard disk, etc., and a communication device 309. The communication device 309 may allow the electronic device to communicate with other devices wirelessly or by wire to exchange data. Figure 3 An electronic device having various devices is shown, but it should be understood that it is not required to implement or possess all the devices shown. More or fewer devices may be implemented or possessed instead. Figure 3 Each block shown in the figure may represent one device, or may represent multiple devices as required.
[0061] In particular, according to some embodiments of the present disclosure, the process described above with reference to the flowchart can be implemented as a computer software program. For example, some embodiments of the present disclosure include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In some such embodiments, the computer program can be downloaded and installed from the network through the communication device 309, or installed from the storage device 308, or installed from the ROM 302. When the computer program is executed by the processing device 301, the above-mentioned functions defined in the method of some embodiments of the present disclosure are executed.
[0062] Embodiment 4 Based on the above-mentioned embodiment 1, this embodiment further provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the steps of the above-mentioned method are implemented.
[0063] It should be noted that the computer-readable medium mentioned above in some embodiments of the present disclosure may be a computer-readable signal medium or a computer-readable storage medium or any combination of the above two. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media may include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In some embodiments of the present disclosure, the computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, device or device. In some embodiments of the present disclosure, the computer-readable signal medium may include a data signal propagated in a baseband or as part of a carrier wave, which carries a computer-readable program code. This propagated data signal may take a variety of forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. Computer readable signal media may also be any computer readable medium other than computer readable storage media, which may send, propagate or transmit a program for use by or in conjunction with an instruction execution system, apparatus or device. The program code contained on the computer readable medium may be transmitted using any appropriate medium, including but not limited to: wires, optical cables, RF (radio frequency), etc., or any suitable combination of the above.
[0064] In this embodiment, the client and the server may communicate using any currently known or future developed network protocol such as HTTP (HyperTextTransferProtocol), and may be interconnected with any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include a local area network ("LAN"), a wide area network ("WAN"), an internet (e.g., the Internet), and a peer-to-peer network (e.g., an ad hoc peer-to-peer network), as well as any currently known or future developed network.
[0065] The computer-readable medium may be included in the device, or may exist independently without being installed in the electronic device. The computer-readable medium carries one or more programs. When the one or more programs are executed by the electronic device, the electronic device: obtains training data, converts the training data into initial data; determines an initial rule base based on the initial data, and optimizes the parameters of the initial rule base to obtain a target rule base; calculates the rules in the target rule base according to a preset activation weight calculation formula to obtain activation weights; determines abnormal information according to the test data and the activation weights.
[0066] Computer program code for performing the operations of some embodiments of the present disclosure may be written in one or more programming languages or a combination thereof, including object-oriented programming languages such as Java, Smalltalk, C++, and conventional procedural programming languages such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a separate software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider).
[0067] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present disclosure. In this regard, each square box in the flow chart or block diagram can represent a module, a program segment or a part of a code, and the module, the program segment or a part of the code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some implementations as replacements, the functions marked in the square box can also occur in a sequence different from that marked in the accompanying drawings. For example, two square boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each square box in the block diagram and / or flow chart, and the combination of the square boxes in the block diagram and / or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.
[0068] The units described in some embodiments of the present disclosure may be implemented by software or hardware. The units described may also be provided in a processor, for example, may be described as: a processor including a data acquisition unit, a rule determination unit, a weight calculation unit, and an anomaly determination unit. The names of these units do not, in some cases, constitute limitations on the units themselves, for example, the data acquisition unit may also be described as a "unit for acquiring training data".
[0069] The functions described above herein may be performed at least in part by one or more hardware logic components. For example, exemplary types of hardware logic components that may be used include, without limitation, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), complex programmable logic devices (CPLDs), etc.
[0070] Obviously, it should be understood by those skilled in the art that the above-mentioned various steps of the present invention can be performed in a manner different from the present invention, and the simulation method and experimental equipment include but are not limited to the above description. The above-mentioned various steps of the present invention can be performed in an order different from that here in some cases, and the steps shown or described above can be performed separately. Therefore, the present invention is not limited to any specific combination of hardware and software.
[0071] The above contents are further detailed descriptions of the present invention in combination with specific implementation methods, and it cannot be determined that the specific implementation of the present invention is limited to these descriptions. For ordinary technicians in the technical field to which the present invention belongs, several simple deductions or substitutions can be made without departing from the concept of the present invention, which should be regarded as falling within the scope of protection of the present invention.
Claims
1. A SQL script simulation execution test method, characterized in that: include: Create a new database and a new user in the data warehouse of the formal environment system, and grant the new user the authority to use the new database; Obtain the SQL script to be tested, use the SQL syntax parser to parse the SQL script to be tested, obtain the source table and target table used by each SQL statement, and capture the syntax anomalies of the SQL script to be tested during the parsing process; Reassemble the SQL script to be tested into a three-stage test script; Test the generated three-stage test script in the new database and obtain the corresponding test results; Based on the test results, determine whether the three-stage test script has error information. If the three-stage test script has error information, replace the simulated table name contained in the error information with the original table name of the corresponding source table, and return the replaced original table name of the source table to the front-end page.
2. A SQL script simulation execution test method according to claim 1, characterized in that: After granting the new user the right to use the new database, the method further includes: Grant the new user read-only permissions to other related databases contained in the data warehouse of the formal environment system.
3. A SQL script simulation execution test method according to claim 1, characterized in that: The step of obtaining the SQL script to be tested and using a SQL syntax parser to parse the SQL script to be tested to obtain the source table and target table used by each SQL statement specifically includes: Get the SQL script to be tested, use the Spark Calalyst parser engine to parse the SQL script to be tested into a logical execution plan, obtain the SQL syntax tree from the logical execution plan, and obtain the source table and target table used by each SQL statement.
4. A SQL script simulation execution test method according to claim 3, characterized in that: After obtaining the source table, the method further includes: Determine whether the source table contains a newly created table statement. If the source table contains a newly created table statement, mark the corresponding source table.
5. A SQL script simulation execution test method according to claim 1, characterized in that: In the parsing process, the syntax anomalies of the SQL script to be tested are captured, specifically including: During the parsing process, the syntax anomalies of the SQL script to be tested are captured, an error message is generated, and the error prompt obtained from the error message is returned to the front-end page.
6. A SQL script simulation execution test method according to claim 1, characterized in that: The reassembling of the SQL script to be tested into a three-segment test script specifically includes: splitting the SQL script to be tested and generating a three-segment test script, wherein the three-segment test script includes a first segment, a second segment and a third segment.
7. A SQL script simulation execution test method according to claim 6, characterized in that: The first section is a statement for creating a simulated table, which includes using a CREATE TABLE LIKE statement to create a simulated table with the same structure as the source table in the new database; the second section is replacing the original table name of the source table included in the SQL script to be tested, which includes replacing the original table name of the source table included in the SQL script to be tested with the table name of the corresponding simulated table in the new database based on a preset table name replacement rule, and keeping the CREATE TABLE statement unchanged; The third paragraph is about deleting the created simulation table. The created simulation table is deleted by using the DROP TABLE statement after the test is completed to delete all the simulation tables created in the new database.
8. A SQL script simulation execution test device, characterized in that: include: The creation module is used to create a new database and a new user in the data warehouse of the formal environment system, and grant the new user the permission to use the new database; Acquisition module, used to obtain the SQL script to be tested; The parsing module is used to parse the SQL script to be tested using an SQL syntax parser to obtain the source table and target table used by each SQL statement, and to capture syntax anomalies of the SQL script to be tested during the parsing process; A three-stage test script generation module is used to reassemble the SQL script to be tested into a three-stage test script; The script test module is used to test the generated three-stage test script in the new database and obtain the corresponding test results; The error prompt module is used to determine whether the three-stage test script has error information based on the test results. If the three-stage test script has error information, the simulation table name contained in the error information is replaced with the original table name of the corresponding source table, and the replaced original table name of the source table is returned to the front-end page.
9. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 7 are implemented.
10. A computer-readable storage medium storing a computer program, 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 7 are implemented.
Citation Information
Cited By
Software testing method and device and electronic equipment
CN119226177A
Large model driven efficient NL2SQL conversion system and method based on big data training
CN120872999A
Structured query statement extraction method and device, equipment and storage medium
CN121560909A
