SQL risk identification method, device and equipment

Through the full process of identifying and optimizing inefficient SQL, the performance problems caused by inefficient SQL in the big data technology platform are solved, and the normal operation of the business and the stability of the platform are achieved.

CN112784273BActive Publication Date: 2024-11-19INDUSTRIAL AND COMMERCIAL BANK OF CHINA
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202110183503.7
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2021-02-10
Publication Date
2024-11-19
Estimated Expiration
2041-02-10

AI Technical Summary

Technical Problem

In the big data technology platform, performance problems caused by inefficient SQL are difficult to detect and solve in a timely manner, resulting in resource bottlenecks and stability issues, affecting business operations.

Method used

By obtaining the submitted SQL code, judging risk SQL based on preset code inspection rules, calling the risk identification model of the test environment and production environment for full-process and comprehensive risk identification and performance optimization, identifying and optimizing slow SQL to ensure normal business operations .

Benefits of technology

It realizes timely identification and optimization of inefficient SQL, shortens the problem solving cycle, improves the performance and stability of the big data technology platform, and ensures business continuity.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN112784273B_ABST
    Figure CN112784273B_ABST
Patent Text Reader

Abstract

The embodiments of this specification provide a method, device and equipment for SQL risk identification in the field of big data technology. The method includes obtaining the submitted SQL code, judging whether there is risky SQL in the SQL code based on preset code checking rules; when it is determined that there is no risky SQL, publishing the SQL code to the test environment; calling the first risk identification model in the test environment to identify the risk of the SQL code; wherein the first risk identification model is obtained by training based on the SQL feature data in the test environment; when there is no risky SQL in the identification result, publishing the SQL code to the production environment; calling the second risk identification model in the production environment to identify the risk of slow SQL in the SQL code, and obtaining the risk identification result; wherein the second risk identification model is obtained by training based on the SQL feature data in the production environment. The embodiments of this specification can be used to accurately identify inefficient SQL in the whole process and in all aspects.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of big data, and in particular to a SQL risk identification method, device and equipment. Background Art

[0002] With the widespread rise of big data technology, more and more banks have established enterprise-level big data technology platforms. The business of data lakes and data warehouses is becoming increasingly complex, and the amount of data is growing exponentially. At the same time, the number of applications included in the data platform is increasing. Performance and stability, as important indicators of the quality of big data technology platforms, are facing increasing challenges. In the operation of big data technology platforms, performance problems caused by inefficient SQL (Structured Query Language) are becoming increasingly prominent. Once performance problems occur in the production environment, the resources of the entire big data technology platform will reach a bottleneck in a short period of time, seriously affecting the performance and stability of the platform.

[0003] Currently, performance issues caused by inefficient SQL can only be resolved through post-analysis and investigation. This results in problems such as delayed discovery of problems and a long problem-solving cycle, which in turn affects normal business operations.

[0004] Therefore, the industry is in urgent need of a technical solution that can solve the above technical problems. Summary of the invention

[0005] The embodiments of this specification provide a SQL risk identification method, device and equipment, which can accurately identify inefficient SQL in a full-process, all-round and accurate manner and optimize performance, thereby effectively solving problems such as delayed problem discovery and long problem solving cycle, and ensuring normal business operation.

[0006] The SQL risk identification method, device and equipment provided in this specification are implemented in the following ways.

[0007] A SQL risk identification method comprises: obtaining submitted SQL code, judging whether there is risky SQL in the SQL code based on preset code checking rules; when it is determined that there is no risky SQL, publishing the SQL code to a test environment; calling a first risk identification model in the test environment to perform risk identification on the SQL code; wherein the first risk identification model is obtained by training based on SQL feature data in the test environment; when there is no risky SQL in the identification result, publishing the SQL code to a production environment; calling a second risk identification model in the production environment to perform risk identification on slow SQL in the SQL code to obtain a risk identification result; wherein the second risk identification model is obtained by training based on SQL feature data in the production environment; the slow SQL is SQL exceeding a preset threshold.

[0008] A SQL risk identification device comprises: an acquisition module, used for acquiring submitted SQL code, and judging whether there is risky SQL in the SQL code based on preset code checking rules; a first publishing module, used for publishing the SQL code to a test environment when it is determined that there is no risky SQL; a first identification module, used for calling a first risk identification model in the test environment to perform risk identification on the SQL code; wherein the first risk identification model is obtained by training based on SQL feature data in the test environment; a second publishing module, used for publishing the SQL code to a production environment when there is no risky SQL in the identification result; a second identification module, used for calling a second risk identification model in the production environment to perform risk identification on slow SQL in the SQL code to obtain a risk identification result; wherein the second risk identification model is obtained by training based on SQL feature data in the production environment; the slow SQL is SQL exceeding a preset threshold.

[0009] A SQL risk identification device includes at least one processor and a memory storing computer executable instructions, wherein when the processor executes the instructions, the steps of any method embodiment in the embodiments of this specification are implemented.

[0010] A computer-readable storage medium stores computer instructions, which, when executed, implement the steps of any method embodiment in the embodiments of this specification.

[0011] This specification provides a SQL risk identification method, device and equipment. In some embodiments, the submitted SQL code can be obtained, and it can be determined whether there is risky SQL in the SQL code based on the preset code checking rules. When it is determined that there is no risky SQL, the SQL code can be published to the test environment, and then the first risk identification model in the test environment is called to identify the risk of the SQL code. It is also possible to publish the SQL code to the production environment when there is no risky SQL in the identification result, and call the second risk identification model in the production environment to identify the risk of slow SQL in the SQL code, so as to obtain the risk identification result. By adopting the implementation scheme provided in this specification, inefficient SQL can be identified and performance optimized in a full-process, all-round and accurate manner, thereby effectively solving the problems of untimely problem discovery and long problem solving cycle, so as to enable normal business operation. BRIEF DESCRIPTION OF THE DRAWINGS

[0012] The drawings described herein are used to provide a further understanding of this specification, constitute a part of this specification, and do not constitute a limitation of this specification. In the drawings:

[0013] Figure 1 This is a flowchart of an embodiment of a SQL risk identification method provided in this specification;

[0014] Figure 2 It is a flowchart of a specific embodiment of a SQL risk identification method provided in this specification;

[0015] Figure 3 It is a schematic diagram of the module structure of an embodiment of a SQL risk identification device provided in this specification;

[0016] Figure 4 This is a hardware structure block diagram of an embodiment of a SQL risk identification server provided in this specification. DETAILED DESCRIPTION

[0017] In order to enable those skilled in the art to better understand the technical solutions in this specification, the technical solutions in the embodiments of this specification will be clearly and completely described below in conjunction with the drawings in the embodiments of this specification. Obviously, the described embodiments are only part of the embodiments in this specification, not all of the embodiments. Based on one or more embodiments in this specification, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the embodiments of this specification.

[0018] The following is an example of a specific application scenario to illustrate the implementation plan of this specification. Specifically, Figure 1 It is a flowchart of an embodiment of a SQL risk identification method provided in this specification. Although this specification provides method operation steps or device structures as shown in the following embodiments or drawings, the method or device may include more or fewer operation steps or module units after partial combination based on routine or no creative labor.

[0019] An implementation scheme provided in this specification can be applied to a client, a server, etc. The client can include a terminal device, such as a smart phone, a tablet computer, etc. The server can include a single computer device, or a server cluster composed of multiple servers, or a server structure of a distributed system, etc.

[0020] It should be noted that the following embodiment description does not limit the technical solutions in other application scenarios that can be extended based on this specification. Figure 1 As shown, in an embodiment of a SQL risk identification method provided in this specification, the method may include the following steps.

[0021] S0: Obtain the submitted SQL code, and determine whether there is risky SQL in the SQL code based on preset code checking rules.

[0022] In some embodiments, after completing the writing of the SQL code, the developer can submit the SQL code through the SQL code submitter. The SQL code submitter can be used to submit the SQL code. For example, the developer can submit the SQL code through Git. Git is an open source distributed version control system that can effectively and quickly handle version management of projects from very small to very large.

[0023] In some embodiments, when the developer submits the SQL code, the server can obtain the submitted SQL code and determine whether there is risky SQL in the SQL code based on the preset code checking rules. The preset code checking rules can also be called SQL-related performance risk rules, which can be used to provide SQL code rule checking services. The SQL code rule checking service can use the preset code checking rules to check whether there is risky SQL in the SQL code.

[0024] In some embodiments, preset code checking rules can be created by analyzing production performance issues and combining industry big data performance cases. For example, in some implementation scenarios, when creating preset code checking rules, SQL can be parsed in combination with metadata. Among them, metadata can include data dictionaries (such as databases, data tables, table fields, primary key definitions), data capacity (such as database capacity size, data table capacity size, data table partition capacity size), statistical information (such as the number of data table records, data table partitions and distribution keys, data distribution), etc.

[0025] In some implementation scenarios, after creating the preset code checking rules, they can be stored in the code rule library, so that they can be directly retrieved from the code rule library when needed later, thereby effectively improving data processing efficiency. The code rule library can be used to store the preset code checking rules, which can be an Oracle database, MySQL database, etc.

[0026] In some embodiments, the preset code inspection rules may include at least one of the following: large-capacity tables use Cartesian products, large-capacity tables have a risk of full table scans, large-capacity tables have query columns that are not pruned, partition tables do not have partition restrictions, large-capacity tables use count(distinct) functions, large-capacity tables use row_number() functions, large-capacity tables use order by sorting operations, large-capacity tables use collect_set functions, large-capacity tables use collect_list functions, large-capacity tables use unixtimestamp functions, large-capacity tables use regular expressions, join fields and group by fields are consistent but the performance optimization switch is not turned on, large-capacity tables have non-primary key association risks, the number of union all parts exceeds the standard threshold, and the number of SQL nesting exceeds the standard threshold.

[0027] In some embodiments, when a developer submits an SQL code, the SQL code rule checking service may be automatically triggered to determine whether there is any risky SQL in the SQL code.

[0028] In some embodiments, judging whether there is risky SQL in the SQL code based on the preset code checking rules may include: parsing the SQL code using a rule matching algorithm, and then matching the parsing result with the preset code checking rules. When the match is successful, it can be indicated that there is risky SQL in the SQL code, otherwise, it does not exist. In some implementation scenarios, a rule matching algorithm corresponding to the code rule library can be constructed based on the code rule library and metadata. The rule matching algorithm can be used to identify SQL with performance risks.

[0029] In some embodiments, when it is determined that there is a risky SQL in the SQL code, the risky SQL can be intercepted, rejected for submission, and risk information can be returned so that developers can optimize performance. The risk information can include risk number, risk description information, risk name, risk line number, risk level, optimization suggestions, etc.

[0030] In some implementation scenarios, after R&D personnel optimize the performance of risky SQL, they can resubmit the optimized SQL through the SQL code submitter.

[0031] In the embodiments of this specification, by presetting code inspection rules to identify risks when SQL code is pre-submitted, inefficient SQL with performance risks can be discovered in time before production is released to avoid risks.

[0032] S2: When it is determined that the SQL code does not exist, the SQL code is published to the test environment.

[0033] In the embodiments of this specification, when it is determined that there is no risky SQL in the submitted SQL code, it can be published to the test environment for risk identification. The test environment can be understood as an environment for performance testing before the SQL code goes online. The test environment is generally a copy of the configuration of the production environment. If a program does not work properly in the test environment, it will not be published to the production machine.

[0034] In some implementation scenarios, the test environment may include a monitoring program, and the monitoring program may be used to call the first risk identification model.

[0035] S4: Calling a first risk identification model in the test environment to perform risk identification on the SQL code; wherein the first risk identification model is obtained by training based on SQL feature data in the test environment.

[0036] The first risk identification model can perform SQL risk prediction and identification in the test environment. The first risk identification model can also be called a test environment SQL risk identification and prediction model. The first risk identification model can provide a test environment SQL risk identification and prediction service.

[0037] In some embodiments, the first risk identification model can be obtained in the following manner: acquiring feature data associated with SQL in a test environment; assigning category labels to the SQL based on operation and maintenance diagnostic information of the test environment; the category labels include inefficient SQL and non-inefficient SQL; using the feature data and category labels of the SQL to train the XGBoost model to obtain the first risk identification model; wherein, during the training process, the parameters of the model are continuously optimized based on feature engineering.

[0038] In some implementation scenarios, each feature data associated with SQL may include basic feature data, risk feature data, SQL execution plan feature data, etc. Among them, the basic feature data can be obtained by parsing SQL statements in the test environment / production environment based on metadata. The risk feature data can be obtained by checking SQL statements based on preset code inspection rules. The SQL execution plan feature data can be obtained based on parsing the SQL execution plan of the test environment / production environment.

[0039] In some implementation scenarios, you can obtain SQL statements in the test environment, and then parse the SQL statements in combination with metadata and statistical information of the corresponding environment to generate basic characteristic data related to SQL performance, such as the number of SQL statement nesting times, the number of SQL statement partition access times, the SQL statement partition access capacity, the number of SQL statement table access times, the number of SQL statement partition accesses, the number of SQL statement non-primary key association times, the number of SQL statement table accesses, the SQL statement table capacity, etc.

[0040] In some implementation scenarios, you can call the SQL code rule checker, pass the SQL statement as a parameter to the SQL code rule checker, and obtain the risk feature data of the SQL code rule check. For example, whether the large-capacity table in SQL uses Cartesian product, whether the query column of the large-capacity table in SQL is not trimmed, whether the large-capacity table in SQL has full table scan risk, the number of high risks, the number of medium risks, the number of low risks, etc. Among them, the SQL code rule checker can be obtained by encapsulating the preset code check rules.

[0041] In some implementation scenarios, the SQL execution plan of the test environment can be obtained, and then the SQL execution plan can be parsed to generate SQL execution plan feature data, such as SQL execution plan cost value, etc. Of course, the above is only an exemplary description, and the feature data associated with SQL is not limited to the above examples. Technical personnel in the relevant field may make other changes under the inspiration of the technical essence of this application, but as long as the functions and effects achieved are the same or similar to those of this application, they should be covered within the scope of protection of this application.

[0042] In some implementation scenarios, after obtaining the characteristic data associated with SQL in the test environment, the SQL can be labeled. Among them, labeling can be understood as assigning a category label, such as 1 or 0, to each SQL. In some implementation scenarios, it is possible to determine whether the relevant SQL is an inefficient SQL based on the operation and maintenance diagnostic information in the test environment, such as whether the SQL running time exceeds the preset value. Among them, the preset value can be set according to the actual scenario, and this specification does not limit this. For example, in some implementation scenarios, the SQL category whose running time exceeds the preset value can be marked as 1, and the SQL category whose running time does not exceed the preset value can be marked as 0. Among them, 1 can indicate that the SQL is an inefficient SQL, and 0 can indicate that the SQL is not an inefficient SQL. Among them, in some implementation scenarios, after the SQL is labeled, the category label can be stored as a characteristic field in the corresponding characteristic data.

[0043] In some implementation scenarios, after obtaining the feature data associated with SQL in the test environment and assigning a category label to each SQL, the feature data associated with SQL can be structured to obtain a feature data set for subsequent model training, and then stored in a database. Each feature data associated with SQL can include multiple feature fields, and each feature field can correspond to a feature factor importance. The feature field can be understood as an indicator that describes the SQL performance. The feature factor importance can identify the degree of influence of the feature field on the SQL performance.

[0044] In some implementation scenarios, after obtaining the feature data set for model training, the feature data set can be divided into a training set and a test set, and then the XGBoost model is trained using the training set, and the model obtained by training is verified using the test set. For example, in some implementation scenarios, after obtaining the feature data set for model training, 80% of the data in the feature training set can be used as a training set, and the remaining data can be used as a test set. Among them, XGBoost is an integrated learning algorithm, and the base model generally selects a tree model, but other types of models such as logistic regression can also be selected. Of course, the above is only an exemplary description. In the embodiments of this specification, the training set can also be used to train other machine learning models to obtain a first risk identification model. The technical personnel in the relevant field may make other changes under the inspiration of the technical essence of this application, but as long as the functions and effects achieved are the same or similar to those of this application, they should be covered within the scope of protection of this application.

[0045] In some implementation scenarios, during the process of training the XGBoost model using the training set, the parameters of the training model can be continuously optimized through feature engineering, so that the risk identification accuracy of the first risk identification model obtained is higher.

[0046] In some implementation scenarios, the feature engineering optimization method may include: feature selection, feature transformation, feature combination, etc. Among them, the feature fields selected in the feature selection may be fields that cause inefficient SQL. For example, in some implementation scenarios, the feature fields selected in the feature selection include at least one of the following: SQL running time, SQL execution start time, SQL execution end time, SQL length, SQL nesting times, SQL partition access times, SQL access table partition capacity, SQL access table partition number, SQL access table times, SQL access table number, SQL access table capacity, SQL access table capacity, non-primary key association times, SQL execution plan cost value, SQL hit high risk times, SQL hit medium risk times, SQL hit low risk times, SQL hit risk category, SQL hit large capacity table using Cartesian product times, SQL hit large capacity table has full table scan risk times, SQL hit large capacity table has query column without trimming times, SQL hit partition table without adding partition restriction conditions times, SQL hit large capacity table using count (distinct) function times, SQL hit large capacity table using row_number () function times, SQL hit large capacity table using order by sorting operation times, SQL hits large-capacity tables using collect_set function times, SQL hits large-capacity tables using collect_list function times, SQL hits large-capacity tables using unixtimestamp function times, SQL hits large-capacity tables using regular expressions times, SQL hits join fields and group by fields that are consistent but performance optimization switches are not turned on, SQL hits large-capacity tables and there are non-primary key association risks times, SQL hits union all parts times that exceed the standard threshold times, SQL hits SQL nesting times that exceed the standard threshold times. Of course, the above is only an exemplary explanation, and the feature fields selected during the training process are not limited to the above examples. Technical personnel in the relevant field may make other changes under the inspiration of the technical essence of this application, but as long as the functions and effects achieved are the same or similar to those of this application, they should be covered within the protection scope of this application. It should be noted that the feature fields selected for each training can be the same or different.

[0047] Feature transformation can be understood as transforming feature fields to derive new feature fields, such as transforming the "SQL execution start time" to convert the date into a week to generate a new feature field "SQL execution start time (week)", with values ​​such as Wednesday and Saturday. Feature combination can be understood as combining and calculating two or more feature fields to derive new feature fields, such as combining "SQL hit high risk times", "SQL hit medium risk times", and "SQL hit low risk times" into a new feature field "SQL hit total risk", as a new feature field.

[0048] In the embodiments of this specification, by continuously selecting, transforming, and combining features, adjusting the parameters of the XGBoost model, and continuously optimizing the model, the obtained model can be made more accurate.

[0049] In some embodiments, after the first identified risk model is obtained, it may be stored so that it can be directly called when needed, thereby improving processing efficiency.

[0050] In the embodiment of the present specification, after the SQL code is published to the test environment, the first risk identification model in the test environment can be called to perform risk identification on the SQL code.

[0051] In some embodiments, when the submitted SQL code passes the check of the preset code check rules and there is no risky SQL, the SQL code can be published to the test environment, and the monitoring program in the test environment can automatically detect the SQL with version changes, call the first risk identification model in the test environment to identify the risk of the SQL code, and return the identification result. The identification result may include whether there is risky SQL, the importance of the corresponding characteristic factors when there is risky SQL, etc.

[0052] In some embodiments, calling the first risk identification model in the test environment to perform risk identification on the SQL code may include: parsing the SQL code to obtain SQL statements included in the SQL code; parsing the SQL statements based on metadata to obtain basic feature data corresponding to the SQL code; checking the SQL statements based on preset code checking rules to obtain risk feature data corresponding to the SQL code; parsing the SQL execution plan of the SQL statement to obtain SQL execution plan feature data corresponding to the SQL code; and inputting the basic feature data, risk feature data, and SQL execution plan feature data corresponding to the SQL code into the first risk identification model for risk identification.

[0053] In some embodiments, after obtaining the risk identification result of the SQL code by the first risk identification model, the identification result can be pushed to the R&D personnel so that the R&D personnel can perform targeted performance tuning according to the identification result.

[0054] In some implementation scenarios, after R&D personnel optimize the performance of risky SQL based on the identification results, they can resubmit the optimized SQL through the SQL code submitter.

[0055] In the embodiment of this specification, by using the first risk identification model to identify risks in SQL code during the testing phase, inefficient SQL with performance risks can be discovered in time before production release to avoid risks.

[0056] S6: When there is no risky SQL in the identification result, the SQL code is released to the production environment.

[0057] In the embodiment of this specification, after calling the first risk identification model in the test environment to identify the risk of the SQL code, if there is no risk SQL in the obtained identification result, the SQL code can be released to the production environment. Among them, the production environment can be understood as the environment after the SQL code is released online. The production environment can formally provide external services, generally turn off error reporting, and turn on error logging. The production environment can be understood as an environment that includes all functions.

[0058] In some implementation scenarios, the test environment may include a monitoring and early warning program, and the monitoring and early warning program may be used to call the second risk identification model.

[0059] In some implementation scenarios, after the SQL code is risk identified by calling the SQL risk identification and prediction service of the test environment, if no performance risk is predicted, the SQL code can be released to production.

[0060] S8: Calling a second risk identification model in the production environment to perform risk identification on slow SQL in the SQL code to obtain a risk identification result; wherein, the second risk identification model is trained based on SQL feature data in the production environment; and the slow SQL is SQL that exceeds a preset threshold.

[0061] The second risk identification model can perform SQL risk prediction and identification in the production environment. The second risk identification model can also be called a production environment SQL risk identification and prediction model. The second risk identification model can provide a production environment SQL risk identification and prediction service.

[0062] In some embodiments, the second risk identification model can be obtained in the following manner: obtaining feature data associated with SQL in the production environment; assigning category labels to the SQL based on the operation and maintenance diagnostic information of the production environment; the category labels include inefficient SQL and non-inefficient SQL; using the feature data and category labels of the SQL to train the XGBoost model to obtain the second risk identification model; wherein, during the training process, the parameters of the model are continuously optimized based on feature engineering.

[0063] It should be noted that the training process of the second risk identification model is similar to the training process of the first risk identification model. For details, please refer to the embodiment of the training process of the first risk identification model in step S4, which will not be described in detail.

[0064] In the embodiment of the present specification, after the SQL code is released to the production environment, the second risk identification model in the production environment can be called to perform risk identification on the slow SQL in the SQL code.

[0065] In some embodiments, after the SQL code is released to the production environment, the monitoring and early warning program in the production environment can automatically detect the SQL with version changes, call the second risk identification model in the production environment to identify the risk of slow SQL in the SQL code, and return the identification result. The identification result may include whether there is risk SQL, the importance of the corresponding feature factor when there is risk SQL, etc.

[0066] In some embodiments, calling the second risk identification model in the production environment to identify the risk of slow SQL in the SQL code may include: parsing the SQL code to obtain SQL statements included in the SQL code; selecting SQL statements exceeding a preset threshold from the SQL statements to obtain slow SQL; parsing the slow SQL based on metadata to obtain basic feature data corresponding to the SQL code; checking the slow SQL based on preset code checking rules to obtain risk feature data corresponding to the SQL code; parsing the SQL execution plan of the slow SQL to obtain SQL execution plan feature data corresponding to the SQL code; inputting the basic feature data, risk feature data, and SQL execution plan feature data corresponding to the SQL code into the second risk identification model for risk identification. Slow SQL can be understood as SQL exceeding a preset threshold. The preset threshold can be set according to the actual scenario. For example, SQL in different applications and different business scenarios can be set with a suitable threshold, and SQL exceeding the threshold can be defined as slow SQL.

[0067] In some embodiments, after obtaining the result of risk identification of slow SQL in the SQL code by the second risk identification model, the identification result can be pushed to the R&D personnel so that the R&D personnel can perform targeted performance tuning according to the identification result.

[0068] In the embodiments of this specification, by using the second risk identification model to identify the risks of slow SQL in the SQL code during the production stage, inefficient SQL with performance risks can be discovered in time during production operation to avoid risks.

[0069] In an embodiment of the present specification, when the SQL code is identified to contain risky SQL based on preset code checking rules or the first risk identification model or the second risk identification model, the risky SQL and the corresponding feature fields can be obtained; the risky SQL can be tuned based on the importance of the feature factors corresponding to the feature fields to obtain optimized SQL.

[0070] In some embodiments, after obtaining the optimized SQL, the optimized SQL can be submitted to a simulation environment; in the simulation environment, the preset code inspection rules, the first risk identification model, and the second risk identification model are called to identify the risk of the optimized SQL; when it is confirmed that there is no risk, the risk SQL and the optimized SQL are scheduled; the running time of the risk SQL and the optimized SQL is compared, and it is determined whether the optimized SQL achieves the expected goal based on the comparison result; when it is confirmed that the expected goal is achieved, the optimized SQL is released to the production environment. The simulation environment can be used to simulate a real environment, such as a test environment, a production environment, etc.

[0071] In some implementation scenarios, after tuning the risky SQL, in order to verify whether there is still a risk, the obtained optimized SQL can be submitted to the simulation environment. After the simulation environment recognizes the SQL version change, it can automatically call the preset code checking rules, the first risk identification model, and the second risk identification model for risk identification.

[0072] In some implementation scenarios, after risk identification of the optimized SQL, if the identification result shows no risk, the SQL runtime of the SQL before optimization (risk SQL) and the SQL runtime of the optimized SQL can be obtained, and then the performance of the optimized SQL can be determined by comparing the corresponding SQL runtimes. Of course, the above is only an exemplary description, and the parameters indicating SQL performance are not limited to SQL runtime. The technicians in the relevant field may make other changes under the inspiration of the technical essence of this application, but as long as the functions and effects achieved are the same or similar to those of this application, they should be covered within the protection scope of this application.

[0073] In some implementation scenarios, the SQL comparison and verification scheduler can be used to verify whether the performance of the tuned SQL is better than that of the pre-optimized SQL. Among them, the SQL comparison and verification scheduler can automatically adjust cluster environment parameters, allocate queue resources, and automatically synchronize production data to the test environment through data synchronization tools, so as to maintain consistency during the running of inefficient SQL and optimized SQL, and schedule the running of inefficient SQL and optimized SQL respectively. Among them, inefficient SQL can be understood as risky SQL.

[0074] In some implementation scenarios, after determining that the performance of the optimized SQL has been improved, you can further determine whether the corresponding performance has reached the expected target. In some implementation scenarios, after determining that the expected target has been reached, you can release the version to the production environment. In some implementation scenarios, if it is determined that the expected target has not been reached, you can continue to optimize the performance and compare the performance before and after optimization until the performance after optimization reaches the expected target and is released for production.

[0075] In the embodiments of this specification, in the process of training the machine learning model using feature data, feature engineering including feature selection, feature transformation, feature combination, etc. is carried out to continuously iteratively optimize the training model parameters, and respectively construct risk identification models for the test environment and the production environment, which can not only provide guarantees for the SQL risk prediction and identification services for the test environment and the production environment, but also improve the accuracy of risk identification.

[0076] The embodiments of this specification can not only be put into practical application in a highly automated manner to achieve accurate identification of inefficient SQL, but also support efficient and rapid comparison testing and verification of SQL before and after optimization, thereby effectively improving the efficiency of risky SQL testing.

[0077] The embodiments of this specification identify risks in SQL code based on preset code inspection rules and risk identification models in the test environment. Risk identification of SQL can be performed in the R&D test stage, thereby avoiding production release and avoiding risks in advance. After the SQL is released and put into production, slow SQL is promptly warned through production monitoring and early warning measures, thereby achieving risk identification in the production environment operation stage. It can be seen that the embodiments of this specification can fully and comprehensively identify inefficient SQL with performance risks before, during, and after the event, avoid production performance risks, and thus protect the performance and stability of big data applications.

[0078] The embodiments of this specification identify inefficient SQL based on preset code inspection rules and risk identification models, and can not only push whether there are performance code risks, but also push performance tuning suggestions, so that R&D personnel can accurately locate the tuning direction of the problematic SQL and formulate corresponding tuning strategies.

[0079] The embodiments of this specification provide a simulation environment to perform automated testing on SQL performance, which can largely avoid performance problems caused by inefficient SQL in the production environment and effectively protect the performance and stability of the big data technology platform.

[0080] In the embodiments of this specification, R&D testers only need to submit SQL to the scheduling server. The scheduling framework will automatically adjust the cluster parameter configuration, allocate queue resources, synchronize data from the production environment to the test environment, automatically schedule SQL execution, record the operation indicator results and resource monitoring information, and save them to the database.

[0081] Of course, the above is only an exemplary description, and the embodiments of this specification are not limited to the above examples. Technical personnel in the relevant field may make other changes based on the technical essence of this application. However, as long as the functions and effects achieved are the same or similar to those of this application, they should be included in the scope of protection of this application.

[0082] The above method is described below in conjunction with a specific embodiment. However, it should be noted that the specific embodiment is only for better illustrating the present application and does not constitute an improper limitation on the present application. Figure 2 As shown, Figure 2 It is a flowchart of a specific embodiment of a SQL risk identification method provided in this specification. Specifically, in this embodiment, based on the SQL feature data set of the test environment and the production environment, feature optimization engineering can be carried out on the feature data through feature selection, feature conversion, and feature combination methods, and the feature data set can be continuously trained using a machine learning algorithm to obtain a qualified SQL risk identification prediction model. The SQL risk identification prediction model includes a test environment SQL risk identification prediction model and a production environment SQL risk identification prediction model.

[0083] In this embodiment, when a user submits SQL code, the SQL code rule checking service can be automatically triggered to determine whether there is a performance risk SQL in the submitted SQL code. Among them, the SQL with performance risk is intercepted, the submission is rejected, and its performance risk and performance tuning suggestions are pushed, and it can be resubmitted after code optimization.

[0084] Furthermore, when the SQL code rule check service determines that there is no performance risk SQL in the submitted SQL code, the SQL code is released to the test environment. The SQL version change identification tool in the test environment can automatically identify the newly released SQL code and generate SQL feature data, and then call the test environment SQL risk identification prediction service to determine whether there is performance risk SQL in the SQL code. If there is a performance risk, the predicted risk results and the importance of the feature factors are pushed to the R&D personnel for performance tuning. If there is no performance risk, it is released for production.

[0085] Furthermore, after the production is released, the production monitoring and early warning tool in the production environment can monitor the operation of SQL and automatically call the production environment SQL risk identification and prediction service to determine whether there is a performance risk SQL in the SQL code. If there is a performance risk, the predicted risk results and the importance of characteristic factors will be pushed to the R&D personnel for performance tuning. When monitoring the operation of SQL, appropriate thresholds can be set according to the SQL of different applications and business scenarios, and SQL that exceeds the threshold can be diagnosed as slow SQL.

[0086] In this embodiment, after performance tuning of the risk SQL identified by the SQL risk prediction and identification model, a SQL performance comparison test can be carried out in a simulation environment to determine whether the performance optimization achieves the expected effect. If it does not meet the expectations, it is pushed to R&D personnel for iterative optimization and comparison test verification. If the performance of the SQL after tuning is improved and meets the performance expectations compared with the SQL before tuning, it is released for production online.

[0087] From the above description, it can be seen that the embodiment of the present application can obtain the submitted SQL code, determine whether there is risky SQL in the SQL code based on the preset code checking rules, and when it is determined that there is no risky SQL, the SQL code can be published to the test environment, and then the first risk identification model in the test environment is called to identify the risk of the SQL code. It is also possible to publish the SQL code to the production environment when there is no risky SQL in the identification result, and call the second risk identification model in the production environment to identify the risk of slow SQL in the SQL code, thereby obtaining a risk identification result. Compared with the prior art, in which performance problems caused by inefficient SQL can only be solved through post-analysis and investigation, the embodiment of this specification can fully, comprehensively and accurately identify inefficient SQL and optimize performance, thereby effectively solving problems such as untimely discovery of problems and long problem-solving cycles, so that the business can operate normally.

[0088] In this specification, each embodiment of the above method is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. For relevant parts, please refer to the partial description of the method embodiment.

[0089] Based on the above-mentioned SQL risk identification method, one or more embodiments of this specification also provide a SQL risk identification device. The device may include a system (including a distributed system), software (application), module, component, server, client, etc. that uses the method described in the embodiment of this specification and is combined with necessary implementation hardware. Based on the same innovative concept, the device in one or more embodiments provided in the embodiment of this specification is as described in the following embodiments. Since the implementation scheme of the device to solve the problem is similar to the method, the implementation of the specific device in the embodiment of this specification can refer to the implementation of the aforementioned method, and the repetitions will not be repeated. As used below, the term "unit" or "module" can be a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, the implementation of hardware, or a combination of software and hardware, is also possible and conceived.

[0090] Specifically, Figure 3 is a schematic diagram of a module structure of an embodiment of a SQL risk identification device provided in this specification, such as Figure 3As shown, a SQL risk identification device provided in this specification may include: an acquisition module 120 , a first publishing module 122 , a first identification module 124 , a second publishing module 126 , and a second identification module 128 .

[0091] The acquisition module 120 may be used to acquire the submitted SQL code and determine whether there is a risky SQL in the SQL code based on a preset code checking rule;

[0092] The first publishing module 122 may be used to publish the SQL code to the test environment when it is determined that the SQL code does not exist;

[0093] The first identification module 124 may be used to call a first risk identification model in the test environment to identify risks of the SQL code; wherein the first risk identification model is obtained by training based on SQL feature data in the test environment;

[0094] The second publishing module 126 may be used to publish the SQL code to the production environment when there is no risky SQL in the identification result;

[0095] The second identification module 128 can be used to call the second risk identification model in the production environment to perform risk identification on the slow SQL in the SQL code to obtain a risk identification result; wherein the second risk identification model is trained based on the SQL feature data in the production environment; the slow SQL is the SQL that exceeds a preset threshold.

[0096] It should be noted that the above-mentioned device may also include other implementation modes according to the description of the method embodiment. The specific implementation mode may refer to the description of the relevant method embodiment, which will not be described one by one here.

[0097] The present specification also provides an embodiment of a SQL risk identification device, comprising a processor and a memory for storing processor executable instructions, wherein the instructions, when executed by the processor, implement the following steps: obtaining a submitted SQL code, and determining whether there is a risky SQL in the SQL code based on preset code checking rules; when it is determined that there is no risky SQL, publishing the SQL code to a test environment; calling a first risk identification model in the test environment to perform risk identification on the SQL code; wherein the first risk identification model is obtained by training based on SQL feature data in the test environment; when there is no risky SQL in the identification result, publishing the SQL code to a production environment; calling a second risk identification model in the production environment to perform risk identification on slow SQL in the SQL code to obtain a risk identification result; wherein the second risk identification model is obtained by training based on SQL feature data in the production environment; the slow SQL is an SQL that exceeds a preset threshold.

[0098] It should be noted that the above-mentioned device may also include other implementations according to the description of the method or device embodiment. The specific implementation methods can refer to the description of the relevant method embodiments, which will not be described one by one here.

[0099] The method embodiments provided in this specification can be executed in a mobile terminal, a computer terminal, a server or a similar computing device. Taking running on a server as an example, Figure 4 1 is a hardware structure block diagram of an embodiment of a SQL risk identification server provided in this specification. The server may be the SQL risk identification device or SQL risk identification apparatus in the above embodiment. Figure 4 As shown, the server 10 may include one or more (only one is shown in the figure) processors 100 (the processor 100 may include but is not limited to a processing device such as a microprocessor MCU or a programmable logic device FPGA), a memory 200 for storing data, and a transmission module 300 for communication functions. A person skilled in the art can understand that Figure 4 The structure shown is only for illustration and does not limit the structure of the above electronic device. Figure 4 More or fewer components shown in the figure may also include other processing hardware, such as a database or multi-level cache, GPU, or other hardware with Figure 4 Different configurations shown.

[0100] The memory 200 can be used to store software programs and modules of application software, such as program instructions / modules corresponding to the SQL risk identification method in the embodiment of this specification. The processor 100 executes various functional applications and data processing by running the software programs and modules stored in the memory 200. The memory 200 may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 200 may further include a memory remotely arranged relative to the processor 100, and these remote memories can be connected to the computer terminal via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0101] The transmission module 300 is used to receive or send data via a network. The specific example of the above network may include a wireless network provided by a communication provider of a computer terminal. In one example, the transmission module 300 includes a network adapter (Network Interface Controller, NIC), which can be connected to other network devices through a base station so as to communicate with the Internet. In one example, the transmission module 300 can be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0102] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0103] The methods or devices described in the above embodiments provided in this specification can implement business logic through computer programs and record them on storage media, and the storage media can be read and executed by computers to achieve the effects of the solutions described in the embodiments of this specification. The storage medium may include a physical device for storing information, which is usually to digitize the information and then store it in a medium using electrical, magnetic or optical methods. The storage medium may include: devices that use electrical energy to store information, such as various types of memories, such as RAM, ROM, etc.; devices that use magnetic energy to store information, such as hard disks, floppy disks, magnetic tapes, magnetic core memories, bubble memories, and U disks; devices that use optical methods to store information, such as CDs or DVDs. Of course, there are other types of readable storage media, such as quantum memories, graphene memories, and so on.

[0104] The above-mentioned SQL risk identification method or device embodiment provided in this specification can be implemented by a processor in a computer executing corresponding program instructions, such as using the C++ language of the Windows operating system to implement it on a PC, a Linux system, or other systems such as Android and iOS system programming languages ​​to implement it on a smart terminal, as well as processing logic based on a quantum computer, etc.

[0105] It should be noted that the devices, equipment, and systems described in the specification may also include other implementation methods according to the description of the relevant method embodiments. The specific implementation methods can refer to the description of the corresponding method embodiments, which will not be described one by one here.

[0106] Each embodiment in this application is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the hardware + program embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0107] For the convenience of description, the above devices are described in various modules according to their functions. Of course, when implementing one or more of the present specification, the functions of some modules can be implemented in the same or more software and / or hardware, and the modules that implement the same function can also be implemented by a combination of multiple sub-modules or sub-units.

[0108] The present invention is described with reference to the flowcharts and / or block diagrams of the methods, devices, equipment, and systems according to the embodiments of the present invention. It should be understood that it can be implemented by computer program instructions, and these computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device produce a device for implementing the specified function. These computer program instructions can also be stored in a computer-readable memory that can guide the computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce a manufactured product including an instruction device, and the instruction device is implemented in the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0109] Those skilled in the art will appreciate that one or more embodiments of the present specification may be provided as a method, system or computer program product. Therefore, one or more embodiments of the present specification may be provided in the form of a complete hardware embodiment, a complete software embodiment or an embodiment combining software and hardware.

[0110] The above is only an example of one or more embodiments of this specification and is not intended to limit one or more embodiments of this specification. For those skilled in the art, one or more embodiments of this specification may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of this application should be included in the scope of the claims.

Claims

1. A SQL risk identification method, characterized in that: include: Obtain the submitted SQL code, and determine whether there is risky SQL in the SQL code based on preset code checking rules; When it is determined that the SQL code does not exist, publishing the SQL code to the test environment; Calling a first risk identification model in the test environment to perform risk identification on the SQL code; wherein the first risk identification model is obtained by training based on SQL feature data in the test environment; When there is no risky SQL in the identification result, the SQL code is released to the production environment; Calling a second risk identification model in the production environment to perform risk identification on the slow SQL in the SQL code to obtain a risk identification result; wherein the second risk identification model is obtained by training based on SQL feature data in the production environment; and the slow SQL is an SQL that exceeds a preset threshold; The calling of the first risk identification model in the test environment to identify the risk of the SQL code includes: Parsing the SQL code to obtain SQL statements included in the SQL code; Parsing the SQL statement based on the metadata to obtain basic feature data corresponding to the SQL code; Check the SQL statement based on preset code checking rules to obtain risk feature data corresponding to the SQL code; Parsing the SQL execution plan of the SQL statement to obtain SQL execution plan feature data corresponding to the SQL code; Inputting basic characteristic data, risk characteristic data, and SQL execution plan characteristic data corresponding to the SQL code into the first risk identification model for risk identification; The calling of the second risk identification model in the production environment to identify the risk of slow SQL in the SQL code includes: Parsing the SQL code to obtain SQL statements included in the SQL code; Selecting SQL statements exceeding a preset threshold from the SQL statements to obtain slow SQL statements; Parsing the slow SQL based on the metadata to obtain basic feature data corresponding to the SQL code; Check the slow SQL based on preset code checking rules to obtain risk feature data corresponding to the SQL code; Parsing the SQL execution plan of the slow SQL to obtain SQL execution plan feature data corresponding to the SQL code; The basic characteristic data, risk characteristic data, and SQL execution plan characteristic data corresponding to the SQL code are input into the second risk identification model for risk identification.

2. The method according to claim 1, characterized in that The preset code inspection rules include at least one of the following: large-capacity tables use Cartesian products, large-capacity tables have full table scan risks, large-capacity tables have query columns that are not trimmed, partition tables do not add partition restrictions, large-capacity tables use count(distinct) functions, large-capacity tables use row_number() functions, large-capacity tables use order by sorting operations, large-capacity tables use collect_set functions, large-capacity tables use collect_list functions, large-capacity tables use unixtimestamp functions, large-capacity tables use regular expressions, join fields and group by fields are consistent but the performance optimization switch is not turned on, large-capacity tables have non-primary key association risks, the number of union all parts exceeds the standard threshold, and the number of SQL nesting exceeds the standard threshold.

3. The method according to claim 1, characterized in that The first / second risk identification model is obtained in the following way: Obtain feature data associated with SQL in the test environment / production environment; Assign a category label to the SQL according to the operation and maintenance diagnostic information of the test environment / production environment; the category label includes inefficient SQL and non-inefficient SQL; The XGBoost model is trained using the feature data and category labels of the SQL to obtain the first / second risk identification model; wherein, during the training process, the parameters of the model are continuously optimized based on feature engineering.

4. The method according to claim 3, characterized in that The feature data includes basic feature data, risk feature data, and SQL execution plan feature data; the basic feature data is obtained by parsing SQL statements in the test environment / production environment based on metadata; the risk feature data is obtained by checking SQL statements based on preset code checking rules; The SQL execution plan characteristic data is obtained based on the analysis of the SQL execution plan of the test environment / production environment.

5. The method according to claim 3, characterized in that: The feature engineering includes feature selection, feature transformation, and feature combination; wherein the feature fields selected in the feature selection include at least one of the following: SQL running time, SQL execution start time, SQL execution end time, SQL length, SQL nesting times, SQL partition access times, SQL access table partition capacity, SQL access table partition number, SQL access table times, SQL access table number, SQL access table capacity, non-primary key association times, SQL execution plan cost value, SQL hit high risk times, SQL hit medium risk times, SQL hit low risk times, SQL hit risk category, SQL hit large capacity table using Cartesian product times, SQL hit large capacity table has full table scan risk times, SQL hit large capacity table has query column without trimming times, SQL hit partition table without adding partition restriction conditions times, SQL hit large capacity table using count (distinct) function times, SQL hit large capacity table using row_number () function times, SQL hit large capacity table using order by sorting operations, SQL hits large-capacity tables using the collect_set function, SQL hits large-capacity tables using the collect_list function, SQL hits large-capacity tables using the unixtimestamp function, SQL hits large-capacity tables using regular expressions, SQL hits join fields and group by fields that are consistent but the performance optimization switch is not turned on, SQL hits large-capacity tables with non-primary key association risks, SQL hits union all parts that exceed the standard threshold, SQL hits SQL nesting that exceeds the standard threshold.

6. The method according to claim 1, characterized in that When the SQL code is identified as having risky SQL based on the preset code checking rule or the first risk identification model or the second risk identification model, the method includes: Get risk SQL and corresponding feature fields; The risk SQL is tuned based on the importance of the characteristic factors corresponding to the characteristic fields to obtain optimized SQL.

7. The method according to claim 6, characterized in that After obtaining the optimized SQL, the following steps are included: Submitting the optimized SQL to the simulation environment; Invoking preset code checking rules, a first risk identification model, and a second risk identification model in the simulation environment to perform risk identification on the optimized SQL; When it is confirmed that there is no risk, schedule risk SQL and optimize SQL; Compare the running time of risky SQL and optimized SQL, and determine whether the optimized SQL achieves the expected goal based on the comparison results; When it is confirmed that the expected goal has been achieved, the optimized SQL is released to the production environment.

8. A SQL risk identification device, characterized in that: include: An acquisition module is used to acquire the submitted SQL code and determine whether there is a risky SQL in the SQL code based on a preset code checking rule; A first publishing module, used for publishing the SQL code to a test environment when it is determined that the SQL code does not exist; A first identification module, used to call a first risk identification model in the test environment to perform risk identification on the SQL code; wherein the first risk identification model is obtained by training based on SQL feature data in the test environment; A second publishing module is used to publish the SQL code to the production environment when there is no risky SQL in the identification result; A second identification module is used to call a second risk identification model in the production environment to perform risk identification on the slow SQL in the SQL code to obtain a risk identification result; wherein the second risk identification model is obtained by training based on SQL feature data in the production environment; and the slow SQL is an SQL that exceeds a preset threshold; The calling of the first risk identification model in the test environment to identify the risk of the SQL code includes: Parsing the SQL code to obtain SQL statements included in the SQL code; Parsing the SQL statement based on the metadata to obtain basic feature data corresponding to the SQL code; Check the SQL statement based on preset code checking rules to obtain risk feature data corresponding to the SQL code; Parsing the SQL execution plan of the SQL statement to obtain SQL execution plan feature data corresponding to the SQL code; Inputting basic characteristic data, risk characteristic data, and SQL execution plan characteristic data corresponding to the SQL code into the first risk identification model for risk identification; The calling of the second risk identification model in the production environment to identify the risk of slow SQL in the SQL code includes: Parsing the SQL code to obtain SQL statements included in the SQL code; Selecting SQL statements exceeding a preset threshold from the SQL statements to obtain slow SQL statements; Parsing the slow SQL based on the metadata to obtain basic feature data corresponding to the SQL code; Check the slow SQL based on preset code checking rules to obtain risk feature data corresponding to the SQL code; Parsing the SQL execution plan of the slow SQL to obtain SQL execution plan feature data corresponding to the SQL code; The basic characteristic data, risk characteristic data, and SQL execution plan characteristic data corresponding to the SQL code are input into the second risk identification model for risk identification.

9. A SQL risk identification device, characterized in that: The method comprises at least one processor and a memory storing computer executable instructions, wherein the processor implements the steps of the method according to any one of claims 1 to 7 when executing the instructions.

10. A computer-readable storage medium, characterized in that: Computer instructions are stored thereon, and when the instructions are executed, the steps of the method according to any one of claims 1 to 7 are implemented.