Method for writing prompt in SQL (Structured Query Language) business scene
By performing three-level keyword splitting and indexing of SQL in the data asset system, combined with Levenshtein Distance algorithm and scoring formula, SQL completion and prompting are achieved, solving the SQL logic problems, repeated writing and inconsistencies in data standards in the existing technology. The problem has improved SQL development efficiency and writing quality.
Patent Information
- Application Number
- CN202411079803.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-07
- Publication Date
- 2024-12-03
AI Technical Summary
Existing SQL prompt tools cannot complete the complete SQL logic, resulting in problems with business personnel writing SQL logic, repeated writing and inconsistent data standards.
By regularly extracting SQL from the data asset system, performing three-level keyword splitting and building indexes, listening to the SQL content input by users in real time, using the Levenshtein Distance algorithm and specific scoring formulas to match and sort, providing SQL completion and prompt functions.
It improves SQL development efficiency, reduces redundant logic development, and promotes the unity of data developers' writing quality and SQL logical expression methods.
Smart Images

Figure CN119065648A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of SQL technology, and in particular to a method for writing prompts for SQL business scenarios. Background Art
[0002] Using SQL to analyze and extract business data has become an increasingly important scenario for every company. However, the technical level of SQL users varies, resulting in the following problems:
[0003] 1. SQL logic problems: Some business personnel have many SQL logic problems when writing SQL. After the written SQL is submitted and executed, it will occupy a lot of resources and is worthless;
[0004] 2. Repeated writing: Different business personnel often write SQL for repeated business scenarios, wasting a lot of time;
[0005] 3. Inconsistent standards: Some business scenarios have already been implemented with standard SQL, but due to information dissemination, business personnel from different organizations may use new methods to implement them, which also leads to inconsistent data standards.
[0006] The SQL prompt tool in the prior art is based on keyword completion, such as completing the entire field after entering the first few letters of the field. It is impossible to complete or prompt the complete SQL logic, and there is a lack of a method that can provide complete SQL logic prompts. Summary of the invention
[0007] The technical problem to be solved by the present invention is to solve the deficiencies of the above-mentioned prior art and provide a method for writing prompts for SQL business scenarios.
[0008] In order to solve the above technical problems, the technical solution adopted by the present invention is:
[0009] A method for writing SQL business scenario prompts, comprising the following steps:
[0010] S1. Regularly extract SQL statements from the data asset system as data sources, extract table names, divide SQL keywords into at least three levels, split SQL statements according to the keyword levels, store the processed SQL statements in the database, and build indexes;
[0011] The splitting method is as follows:
[0012] Start recording from the third-level keyword, until the second-level, first-level, unpaired right bracket or SQL is finished;
[0013] Then, the SQL is re-read to split the secondary keywords, starting from the secondary keywords, until a primary, unpaired right bracket is encountered, or the SQL is finished reading;
[0014] Finally, the first-level keyword splitting is performed, and the records are recorded from the beginning, and the redundant splitting is recursively performed according to the SQL level;
[0015] S2. The front end monitors the SQL content entered by the user in the edit box in real time. When the user does not enter any new information within the set time, the completion interface of the backend service is called to trigger the completion and prompt functions, and the matched data set is returned to the front end.
[0016] The specific methods of the completion and prompt functions are as follows:
[0017] S21, search the standardized SQL input by the user, find the last keyword, and store the subsequent content into variable a;
[0018] S22. Use the table name and keywords to match the data stored in the database, compare the matched result clause SQL with variable a, and if the matched clause SQL completely contains the content of variable a, it is recorded as valid; if there is no match at all, it returns empty and cannot be completed.
[0019] Furthermore, after extracting the SQL of the data asset system and after the front end obtains the SQL content entered by the user in the edit box, the SQL content is cleaned and standardized, deleting the comment part in the SQL statement, converting all SQL statements into a unified lowercase format, and deleting extra spaces and line breaks.
[0020] Furthermore, the Levenshtein Distance algorithm is used to calculate the similarity between the complete standardized SQL input by the user and the complete SQL text matched to the record, and the highest similarity is selected;
[0021] For SQL statements with the same similarity, the scores are calculated again using the following formula:
[0022] f = |(user input standard SQL length - variable a length) - (the position of the beginning of the matched clause SQL in the complete SQL) |
[0023] Find a set of data that is closest to the matching clause in the original SQL position and the SQL clause being written by the user in the standard SQL position written by the user, and sort them from small to large.
[0024] Furthermore, the operation keywords in the keywords are taken as the first-level keywords, the data set keywords are taken as the second-level keywords, and the logic keywords are taken as the third-level keywords.
[0025] Furthermore, the operation keywords include "select", "insert", "update", "delete", "create", "alter", "drop", "truncate", and "union".
[0026] The data set class keywords include "from", "where", "order by", "group by", "having", "join", and "on".
[0027] From the above technology, it can be seen that the present invention has the following advantages: after the SQL being written is standardized, it is split according to keywords, and the written SQL is automatically supplemented by the rigorous and scientific SQL text in the data asset system, thereby facilitating the development of SQL; this method promotes the development efficiency of data developers and reduces the development of redundant logic; the prompted SQLs are all high-quality SQLs collected from data assets, and the quality of SQL written by developers is improved through prompts, which promotes the unification of the logical expression of business code SQL. BRIEF DESCRIPTION OF THE DRAWINGS
[0028] Figure 1 It is a flow chart of the data preprocessing process in the present invention;
[0029] Figure 2 The present invention provides a flowchart of the completion and prompting process. DETAILED DESCRIPTION
[0030] The technical solution of the present invention will be described clearly and completely below in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0031] In the description of the present invention, it should be noted that the terms "center", "up", "down", "left", "right", "vertical", "horizontal", "inside", "outside" and the like indicate positions or positional relationships based on the positions or positional relationships shown in the accompanying drawings, and are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and therefore cannot be understood as limiting the present invention. In addition, the terms "first" and "second" are used for descriptive purposes only, and cannot be understood as indicating or implying relative importance. Among them, the terms "first position" and "second position" are two different positions.
[0032] The method of the present invention is divided into two processes: data preprocessing process and completion and prompting process.
[0033] The data preprocessing process refers to collecting standardized and normalized business SQL through the data asset system and preprocessing the SQL;
[0034] Completion and prompts: When writing SQL, SQL simulation completion and prompts are performed in the writing board according to certain rules;
[0035] The following is a detailed introduction to the data preprocessing process and the two processes of completion and prompting.
[0036] Data Collection:
[0037] The data asset system is a system that stores standardized business SQL developed by many data technicians. The SQL of data assets is extracted as the data source to ensure that prompts and completions are standardized and reasonable.
[0038] The data asset system automatically performs a full extraction operation every morning to ensure the timeliness and integrity of the data. Full extraction is because the number of SQL scripts maintained by data assets is not large, only about thousands of data, but the data changes frequently and the logic often needs to be adjusted. Full extraction ensures that all data changes will be captured, so it is more effective. This process uses JDBC to batch pull from the data asset database every day, and a full extraction takes about 5 minutes.
[0039] Data cleaning and standardization include the following:
[0040] Comment removal: Delete the comments in the SQL statement to reduce unnecessary information interference. This is achieved by using standard comment symbols, such as "\**\", "##", "--", etc., and using regular expressions to remove the comment content.
[0041] Format unification: Convert all SQL statements to a unified lowercase format to simplify subsequent processing steps.
[0042] Whitespace processing: Remove extra spaces and line breaks to improve data cleanliness and reduce noise.
[0043] Extract table names: Extract the table names used in the standardized SQL. This is done directly using the javasqlparse package.
[0044] SQL keywords are divided into operation, data set and logic keywords, and are assigned three levels: one, two and three, to support different levels of logical splitting. As shown in Figure 1
[0045]
[0046]
[0047] Table 1
[0048] Split the previously standardized SQL according to the defined keywords. The specific splitting principle is as follows:
[0049] First, perform the third-level keyword splitting, and start recording from the third-level keyword reading until the second-level, first-level, non-paired right bracket is read or the SQL is read; then re-read the SQL to split the second-level keyword, starting from the second-level keyword reading, until the first-level, non-paired right bracket is encountered, or the SQL is read; finally, perform the first-level keyword splitting and start recording from the beginning. The difference from the above is that it is necessary to split the SQL level recursively and redundantly. For example, if there are 3 nested selects in an SQL and no other first-level keywords, it will eventually be redundantly split into 3 SQLs in the first-level keywords. The first select is the entire SQL statement, including the content of the second select; the second select includes the content of the innermost third select.
[0050] The split data may contain irregular abnormal data such as extra left brackets, which will be cleaned; invalid data generated after splitting (such as empty strings or single-table data) will be marked and excluded.
[0051] For example, there is a SQL as follows:
[0052] SQL
[0053] SQL1:
[0054] Select
[0055] a.column1 / *Field 1 of table a* /
[0056] ,a.column2 / *Field 2 of table a* /
[0057] ,b.column1 / *Field 1 of table b* /
[0058] from a
[0059] left join(select column3 from c where c.column3='5')b
[0060] on a.column3=b.column3
[0061] where
[0062] a.column4>500and a.column5='Male'
[0063] After data normalization, it looks like this:
[0064] SQL
[0065] select a.column1,a.column2,b.column1 from a left join(select column3from c where c.column3='5')b on a.column3=b.column3 where a.column4>500anda.column5='male'
[0066] The split data is shown in Table 2:
[0067]
[0068] The preprocessed SQL statements are stored in the database for subsequent query and matching. In this solution, Kudu is used as the data storage engine to create a partition table. The data of the day is stored in the partition of the day. Impala is used as the query engine. The final split data is about 500,000, which is about 100 times larger than the thousands of original data initially extracted. To improve query efficiency, indexes are created for keywords and corresponding tables.
[0069] The front end monitors the SQL content entered by the user in the edit box in real time. If the user does not enter any new information within 2 seconds, the completion interface of the background service is called to trigger the completion and prompt functions.
[0070] The specific implementation process of completion and prompt is as follows:
[0071] Search the standardized SQL input by the user in reverse order, find the last keyword, and store the subsequent content in variable a;
[0072] Use table names and keywords to accurately match the data stored in Kudu. If no match is found, the data cannot be completed.
[0073] Detailed process: Use Java Kudu Client to query the split information table of Table 2 stored in Kudu. The request parameters are the table name and keyword. By combining the two, multiple data are requested using the query. For example, if the keyword is and and the table name is t, the data that can be queried is as follows:
[0074]
[0075] Compare the standard clause SQL of the matched result with variable a. If the clause SQL that is matched completely contains the content of variable a, record it as valid. If there is no match at all, return empty and it cannot be completed. For example: Assume the content of variable a is "t.column4>", use the java String method, execute the above clause SQL respectively, and check whether clause SQL.contain(a) returns true. If true, it means it is completely contained. Here, "contained" usually means checking whether a specific element or sub-structure is contained in a data structure (such as a string, array, set, etc.). In the above case, "t.column4>" is a part of "t.column4>500and t1.column5='男'" that is contained and has the same character order.
[0076] Calculate the text similarity between the complete standardized SQL input by the user and the complete SQL calculation text in the matched record (the Levenshtein Distance algorithm can be used for calculation), and sort according to the similarity to take the highest;
[0077] For SQLs with the same similarity, use the following formula to calculate the score again:
[0078] f = |(the length of the standard SQL input by the user - the length of variable a) - (the position of the start of the clause SQL that is matched in the complete SQL)|
[0079] Find the group of data where the position of the matched clause in the original SQL is closest to the position of the SQL clause being written by the user in the standard SQL written by the user. At this time, the smaller the absolute value of f, the closer they are, so sort them from small to large.
[0080] The Levenshtein Distance algorithm calculates the minimum number of edit operations between two strings. The edit operations include insertion, deletion, and replacement. The principle of the algorithm can be implemented through dynamic programming. Its core idea is to construct a two-dimensional array to store the minimum edit distance to reach the solution of each sub-problem. The following are the detailed steps:
[0081] 1. Initialization
[0082] Create a two-dimensional array d of size (m + 1) x (n + 1), where m and n are the lengths of the two strings respectively. d[i][j] represents the edit distance between the first i characters of the first string and the first j characters of the second string.
[0083] 2. Fill in the boundary values
[0084] Initialize the boundary values of the two-dimensional array. The edit distance to convert the first string to an empty string is its length, and the edit distance to convert an empty string to the second string is its length.
[0085] 3. Recursive calculation
[0086] The matched data set is returned to the front end. Since the scope of the data query is narrowed down by precise matching, the whole process is completed within 1-2 seconds.
[0087] The sorted matching results are displayed in the prompt box on the right column for the user to select. The user can click to view the complete original SQL script of the prompt. After the user selects a prompt, the system automatically completes it into the edit box where the user inputs. The completed content is marked in non-black to distinguish it from the user's original input. The user confirms the completed content by pressing the Enter key, and the completed content immediately turns black, indicating that it has become part of the user's input.
[0088] Record the user's selection preferences and return the data to the background record in order to improve and optimize the matching algorithm in the future. Provide a user feedback interface to allow users to evaluate the prompt results and further guide system learning.
[0089] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. These modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application, and should all be included in the protection scope of the present application.
Claims
1. A method for providing SQL business scenario writing prompts, characterized in that: The steps include: S1. Regularly extract SQL statements from the data asset system as data sources, extract table names, divide SQL keywords into at least three levels, split SQL statements according to the keyword levels, store the processed SQL statements in the database, and build indexes; The splitting method is as follows: Start recording from the third-level keyword, until the second-level, first-level, unpaired right bracket or SQL is finished; Then, the SQL is re-read to split the secondary keywords, starting from the secondary keywords, until a primary, unpaired right bracket is encountered, or the SQL is finished reading; Finally, the first-level keyword splitting is performed, and the records are recorded from the beginning, and the redundancy splitting is recursively performed according to the SQL level; S2. The front end monitors the SQL content entered by the user in the edit box in real time. When the user does not enter any new information within the set time, the completion interface of the backend service is called to trigger the completion and prompt functions, and the matched data set is returned to the front end. The specific methods of the completion and prompt functions are as follows: S21, search the standardized SQL input by the user, find the last keyword, and store the subsequent content into variable a; S22. Use the table name and keywords to match the data stored in the database, compare the matched result clause SQL with variable a, and if the matched clause SQL completely contains the content of variable a, it is recorded as valid; if there is no match at all, it returns empty and cannot be completed.
2. The method for writing SQL business scenarios according to claim 1, characterized in that: After extracting the SQL of the data asset system and after the front end obtains the SQL content entered by the user in the edit box, the SQL content is cleaned and standardized, deleting the comments in the SQL statement, converting all SQL statements into a unified lowercase format, and deleting extra spaces and line breaks.
3. The method for writing SQL business scenarios according to claim 2, characterized in that: The following steps are also included: The Levenshtein Distance algorithm is used to calculate the similarity between the complete SQL entered by the user and the complete SQL text matched in the record, and the highest similarity is selected; For SQL statements with the same similarity, the scores are calculated again using the following formula: f=|(user input standard SQL length - length of variable a) - (position of the beginning of the matched clause SQL in the complete SQL)| Find a set of data that is closest to the matching clause in the original SQL position and the SQL clause being written by the user in the standard SQL position written by the user, and sort them from small to large.
4. The method for writing SQL business scenario prompts according to claim 1, characterized in that: The operation keywords in the keywords are taken as the first-level keywords, the data set keywords are taken as the second-level keywords, and the logic keywords are taken as the third-level keywords.
5. The method for writing SQL business scenario prompts according to claim 1, characterized in that: The operation keywords include "select", "insert", "update", "delete", "create", "alter", "drop", "truncate", and "union"; The data set class keywords include "from", "where", "order by", "group by", "having", "join", and "on".
