Sql statement conversion method and device, electronic equipment and storage medium

Abstract

The application provides a SQL statement conversion method and device, electronic equipment and storage medium, which can be used in the field of big data. The method of the application uses a set of split symbols corresponding to an original type to split a first SQL statement of the original type to be converted to obtain a content segment of the first SQL statement; in a key command set corresponding to the original type, a key command matched by the content segment of the first SQL statement is searched, a conversion program corresponding to the matched key command is called, and the first SQL statement is converted into a second SQL statement of a target type with the same function by executing the corresponding conversion program; and the second SQL statement of the target type is output, so that the first SQL statement of the original type is automatically converted into the second SQL statement of the target type, the technical barrier of a person skilled in the art who is not familiar with a database is eliminated, the artificial cost of SQL statement conversion is reduced, and the efficiency of SQL statement conversion is improved.

Description

Technical Field

[0001] This application relates to the field of big data, and in particular to a method, apparatus, electronic device, and storage medium for converting SQL statements. Background Technology

[0002] With the development of the internet, the amount of data online is increasing exponentially, making data storage an unavoidable issue. Currently, many database tools are available on the market. However, due to data growth or changes in data standards, existing database tools may no longer meet the needs, requiring replacement to satisfy user data requirements.

[0003] However, after changing database tools (such as replacing Teradata with Hive), SQL statements applicable to the original Teradata dataset cannot be directly applied to the new Hive database. The SQL statements used by technicians for database operations such as adding, deleting, modifying, and querying become invalid, posing significant technical challenges for personnel unfamiliar with the new database's SQL statements. Therefore, a solution is urgently needed to convert between these two different types of SQL statements. Summary of the Invention

[0004] This application provides an SQL statement conversion method, apparatus, electronic device, and storage medium for automatically converting between two different types of SQL statements.

[0005] Firstly, this application provides a method for converting SQL statements, including:

[0006] Get the first SQL statement of the original type to be converted;

[0007] Using the set of delimiters corresponding to the original type, the first SQL statement is segmented to obtain content fragments of the first SQL statement;

[0008] In the key command set corresponding to the original type, find the key command that matches the content fragment of the first SQL statement, call the conversion program corresponding to the key command, and convert the first SQL statement into a second SQL statement of the target type with the same function.

[0009] Output the second SQL statement of the target type.

[0010] Secondly, this application provides an SQL statement conversion apparatus, comprising:

[0011] The module for obtaining the SQL statement to be converted is used to obtain the first SQL statement of the original type to be converted;

[0012] The SQL statement segmentation module is used to segment the first SQL statement using the segmentation symbol set corresponding to the original type, so as to obtain the content fragments of the first SQL statement;

[0013] The SQL statement conversion module is used to find the key command that matches the content fragment of the first SQL statement in the key command set corresponding to the original type, call the conversion program corresponding to the key command, and convert the first SQL statement into a second SQL statement of the target type with the same function.

[0014] The target SQL statement output module is used to output a second SQL statement of the target type.

[0015] Thirdly, this application provides an electronic device, comprising:

[0016] A processor, and a memory communicatively connected to the processor;

[0017] The memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory to implement the method as described in the first aspect.

[0018] Fourthly, this application provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the method described in the first aspect.

[0019] Fifthly, this application provides a computer program product comprising: a computer program stored in a readable storage medium, at least one processor of an electronic device being able to read the computer program from the readable storage medium, and the at least one processor executing the computer program causing the electronic device to perform the method described in the first aspect above.

[0020] The SQL statement conversion method, apparatus, electronic device, and storage medium provided in this application obtain a first SQL statement of the original type to be converted, segment the first SQL statement using the delimiter set corresponding to the original type to obtain content fragments of the first SQL statement; in the key command set corresponding to the original type, search for the key command that matches the content fragment of the first SQL statement, call the conversion program corresponding to the matched key command, and convert the first SQL statement into a second SQL statement of the target type with the same function by executing the corresponding conversion program; output the second SQL statement of the target type, thereby realizing the automatic conversion of the first SQL statement of the original type into the second SQL statement of the target type, eliminating the technical barrier of technicians who are unfamiliar with the database, reducing the manual cost of SQL statement conversion, and improving the efficiency of SQL statement conversion. Attached Figure Description

[0021] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0022] Figure 1 A flowchart of an SQL statement conversion method provided for an exemplary embodiment of this application;

[0023] Figure 2 A flowchart illustrating the configuration of a segmentation symbol set provided for an exemplary embodiment of this application;

[0024] Figure 3 A flowchart illustrating the configuration of key commands and conversion procedures provided in an exemplary embodiment of this application;

[0025] Figure 4 This is a schematic diagram of the structure of an SQL statement conversion device provided in an embodiment of this application;

[0026] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an example embodiment of this application.

[0027] The accompanying drawings illustrate specific embodiments of this application, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concept of this application to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0028] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numbers in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this application. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this application as detailed in the appended claims.

[0029] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with relevant laws, regulations and standards, and corresponding operation entry points are provided for users to choose to authorize or refuse.

[0030] Structured Query Language (SQL) is a special-purpose programming language used for database querying and programming. It is used to access, query, update, and manage relational database systems.

[0031] Teradata database: a big data warehouse system mainly used for data storage and processing, featuring unlimited parallelism, shared architecture-free operation, and automatic distribution.

[0032] Hive database: A data warehouse tool based on Hadoop, used for data extraction, transformation, and loading. It is a mechanism for storing, querying, and analyzing large-scale data stored in Hadoop. Hive data warehouse tools can map structured data files to a database table and provide SQL query functionality, transforming SQL statements into MapReduce tasks for execution.

[0033] Hadoop is a distributed system infrastructure. Based on Hadoop, users can develop distributed programs without understanding the underlying details of distributed systems, fully utilizing the power of clusters for high-speed computation and storage.

[0034] MapReduce is a distributed computing framework primarily used for parallel processing of large-scale datasets. It mainly consists of a Map phase (the Map operation process) and a Reduce phase (the Reduce operation process), with the processing logic of these two phases corresponding to the Map and Reduce functions, respectively. The general idea of ​​its parallel computing is as follows: the file is divided into numerous smaller files and run on various nodes (i.e., the Map operation process), with the results temporarily stored locally; then, after shuffling and sorting, the relevant Map outputs are read and sent to the same Reduce operation process; finally, the results of the Reduce processing are stored on the distributed cluster.

[0035] It should be noted that the SQL statement conversion method, device, electronic device and storage medium of this application can be used in the field of big data, or in any field other than big data. The application field of the SQL statement conversion method, device, electronic device and storage medium of this application is not limited.

[0036] With the development of the internet, the amount of data online is increasing exponentially, making data storage an unavoidable issue. Currently, many database tools are available on the market. However, due to data growth or changes in data standards, existing database tools may no longer meet the needs, requiring replacement to satisfy user data requirements.

[0037] However, after changing database tools (such as replacing Teradata with Hive), SQL statements applicable to the original Teradata dataset cannot be directly applied to the new Hive database. The SQL statements used by technicians for database operations such as adding, deleting, modifying, and querying become invalid, posing significant technical challenges for personnel unfamiliar with the new database's SQL statements. Therefore, a solution is urgently needed to convert between these two different types of SQL statements.

[0038] This application provides a method for converting SQL statements. The method involves obtaining a first SQL statement of the original type to be converted, segmenting the first SQL statement using a set of delimiters corresponding to the original type to obtain content fragments of the first SQL statement, searching for matching key commands in the set of key commands corresponding to the original type, calling the conversion program corresponding to the matching key commands, and executing the corresponding conversion program to convert the first SQL statement into a second SQL statement of the target type with the same function. Finally, the method outputs the second SQL statement of the target type, thus automatically converting the first SQL statement of the original type into a second SQL statement of the target type.

[0039] In this context, the original type of SQL statement can be any SQL statement supported by the user's original database, while the target type of SQL statement can be any SQL statement supported by the database the user has switched to. For example, when the user changes their database from Teradata to Hive, the original type of SQL statement can be Teradata SQL statements supported by the Teradata database, and the target type of SQL statement can be Hive SQL statements used by the Hive database.

[0040] The method in this embodiment can implement a conversion program for converting various SQL statements (with uniquely corresponding keywords as key commands) in the original type SQL to the target type SQL, and configure the key commands and conversion programs corresponding to various SQL statements in the original type SQL statements. When converting SQL statements based on user needs, the corresponding conversion program is executed according to the key command matched by the first SQL statement of the original type provided by the user, thereby converting the first SQL statement into a second SQL statement of the target type. This allows users unfamiliar with the target type SQL statements to automatically convert their familiar original type SQL statements into target type SQL statements using the solution of this application, thereby enabling the definition, operation, and access of the target type database.

[0041] The technical solution of this application and how the technical solution of this application solves the above-mentioned technical problems are described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of this application will now be described with reference to the accompanying drawings.

[0042] Figure 1 This is a flowchart illustrating an exemplary embodiment of the SQL statement conversion method provided in this application. The execution entity in this embodiment can be an electronic device that implements SQL statement conversion, specifically a mobile terminal, personal computer, server, etc., but this embodiment does not impose any specific limitations here.

[0043] like Figure 1 As shown, the specific steps of this method are as follows:

[0044] Step S101: Obtain the first SQL statement of the original type to be converted.

[0045] In the example application scenario where the user changes the applicable database, the original type of SQL statement can be the SQL statement supported by the original database used by the user before the change, and the target type of SQL statement can be the SQL statement supported by the target database used by the user after the change.

[0046] For example, when a user changes their database from Teradata to Hive, the original type of SQL statement can be Teradata SQL statement supported by the Teradata database, and the target type of SQL statement can be Hive SQL statement used by the Hive database. The solution in this application converts the Teradata SQL statement used by the Teradata database into the Hive SQL statement used by the Hive database.

[0047] In addition, the SQL statement of the original type can also be any SQL statement other than Teradata SQL statement, and the SQL statement of the target type can also be any SQL statement other than Hive SQL statement. This embodiment does not make specific limitations here.

[0048] In this embodiment, different types of SQL statements refer to SQL statements based on different syntax specifications / rules. For example, different types of databases support the use of different SQL statements. For any type of SQL statement (including primitive types and target types), there are SQL statements capable of implementing various functions. For example, SQL statements used to implement various functions such as data querying, data insertion, data deletion, data grouping, filtering, and sorting. The syntax structure of SQL statements used to implement the same function / operation in different types of SQL statements may differ.

[0049] For example, in Teradata SQL, the SQL statement used to delete data is the DELETE statement. The syntax of the DELETE statement is: DELETE FROM table_name WHERE condition. Here, table_name refers to the name of the data table, and condition represents the condition that the data to be deleted must meet. However, in Hive SQL, the Hive database does not directly support the DELETE statement for deleting data.

[0050] In one use case, an SQL statement conversion service based on the solution of this application runs on an electronic device, and provides an Application Programming Interface (API) for this service. Other applications on the same device can call the SQL statement conversion service through this API. The electronic device receives call instructions from other applications on the same device to the SQL statement conversion service API, the call instructions containing a first SQL statement of the original type to be converted.

[0051] In another use case, electronic devices can provide a visual interactive interface through a client, through which users can submit the first SQL statement of the original type to be converted to the electronic device.

[0052] Specifically, a visual interactive interface is provided on the client side, through which users can directly input or upload SQL statement information to be converted, which includes one or more first SQL statements.

[0053] To enable batch conversion of SQL statements, users can input or upload multiple initial SQL statements to be converted through this visual interactive interface. Upon submission, the client packages the multiple initial SQL statements input or uploaded by the user into a single SQL statement to be converted, and then sends this SQL statement information to the electronic device. This SQL statement information contains at least one SQL statement of the original type, and adjacent SQL statements are separated by a preset delimiter.

[0054] The preset separator used to separate different first SQL statements can be set as a symbol that is not used inside the SQL statements of the original type SQL and the target type SQL, according to the actual application scenario. For example, the preset separator can be a semicolon, or it can be a symbol that is not used inside other SQL statements. This embodiment does not make specific limitations here.

[0055] Optionally, an input interface for the SQL statements to be converted can be displayed on the client side. This interface provides an input area for raw SQL statements and displays prompts indicating that adjacent SQL statements should be separated by a preset delimiter. Users can edit multiple SQL statements to be converted in the input area of ​​this interface, separating adjacent statements using the preset delimiter. In this case, the user's input constitutes the SQL statement information to be converted. This information contains at least one raw SQL statement, with adjacent statements separated by the preset delimiter. When the input interface is submitted, the client sends the user-inputted SQL statement information to the electronic device. The electronic device receives the SQL statement information edited and submitted by the user through the input interface.

[0056] Optionally, an input interface for the SQL statements to be converted can be displayed on the client side. This input interface provides an input area for raw SQL statements. This input area supports the user entering multiple independent SQL statements. For example, multiple input boxes can be provided, each allowing the input of one SQL statement, and the user can increase the number of input boxes as needed. When the user completes the input of one or more SQL statements and submits the input interface, the client separates the one or more SQL statements entered by the user in the input interface using a preset delimiter and concatenates them to obtain the SQL statement information to be converted. This SQL statement information is then sent to the electronic device. This SQL statement information contains at least one raw SQL statement, and adjacent SQL statements are separated by a preset delimiter. The electronic device receives the SQL statement information edited and submitted by the user through the input interface.

[0057] Optionally, a file upload interface can be displayed on the client side, allowing users to upload files containing SQL statement information. Users can edit the SQL statements to be converted within the file, separating different SQL statements using a preset delimiter. After editing, the file is uploaded through the file upload interface. The client then sends the uploaded file to the electronic device. This file contains the original type of SQL statements to be converted, with different SQL statements separated by the preset delimiter. The electronic device receives the uploaded file, reads its contents, and obtains the SQL statement information.

[0058] Furthermore, after receiving the SQL statement information to be converted submitted by the user, the electronic device uses a preset delimiter to divide the SQL statement information, thereby obtaining at least one first SQL statement to be converted.

[0059] Step S102: Use the delimiter set corresponding to the original type to segment the first SQL statement to obtain the content fragment of the first SQL statement.

[0060] After obtaining at least one first SQL statement to be converted, each first SQL statement is converted into a second SQL statement of the target type.

[0061] In this embodiment, the delimiter set includes various symbols used in the original SQL type, including but not limited to spaces, punctuation marks, and operators. For example, all symbols used in the original SQL type, except for reserved keywords, can be added to the delimiter set as delimiters. The delimiters included in the delimiter set can be configured according to the needs of the actual application scenario, and this embodiment does not impose specific limitations here.

[0062] In this step, for any first SQL statement to be converted, the first SQL statement is divided into multiple segments without delimiters using delimiters from a preset set of delimiters, resulting in content segments of the first SQL statement. These content segments retain the keywords that appear in the first SQL statement.

[0063] For example, in Teradata SQL, the DELETE statement used to delete data is: DELETE FROM table_name WHERE condition. Using the space as a delimiter, we can get the following content fragment: DELETE, FROM, "table_name", WHERE, "condition", where "table_name" refers to the specific table name and "condition" refers to the specific condition expression. If the condition expression contains delimiters, it will be split into multiple smaller fragments.

[0064] Step S103: In the key command set corresponding to the original type, find the key command that matches the content fragment of the first SQL statement, call the conversion program corresponding to the key command, and convert the first SQL statement into a second SQL statement of the target type with the same function.

[0065] In this embodiment, the conversion programs corresponding to various key commands are pre-developed and stored.

[0066] In this step, based on the content fragments obtained from segmenting the first SQL statement, each content fragment is matched with the key commands in the key command set corresponding to the original type to determine whether there are key commands consistent with the content fragments. The key commands consistent with the content fragments are taken as the key commands matched by the content fragments.

[0067] In practical applications, for relatively simple first SQL statements that do not contain nested clauses, this step involves matching a unique key command in the first SQL statement. The conversion program corresponding to the key command matched in the first SQL statement is then invoked, and by executing this conversion program, the first SQL statement is transformed into a second SQL statement of the target type with the same functionality.

[0068] For a first SQL statement containing nested clauses, where other SQL statements are nested within the first SQL statement, this step involves matching multiple key commands. The first SQL statement is then split into multiple SQL clauses that do not contain nested clauses, each containing one matched key command. Based on the nesting relationship between the SQL clauses, placeholder information is set at the positions where inner SQL clauses are nested within outer SQL clauses. This placeholder information replaces the inner SQL clause nested within the outer SQL clause.

[0069] For example, if the first SQL statement nests SQL clause 1, and SQL clause 1 nests SQL clause 2, then the first SQL statement is the outer SQL clause of SQL clause 1, SQL clause 1 is the inner SQL clause of the first SQL statement, SQL clause 1 is the outer SQL clause of SQL clause 2, and SQL clause 2 is the inner SQL clause of SQL clause 1.

[0070] Further, through steps S102-S103, the multiple original type SQL clauses of the first SQL statement are converted into target type SQL clauses respectively, and the placeholder information is preserved during the conversion process. Further, based on the nesting relationship between the multiple original type SQL clauses of the first SQL statement, the target type SQL clauses are nested to obtain the second SQL statement of the target type.

[0071] For nested SQL clauses of the target type, the nesting relationship between each SQL clause of the target type inherits the nesting relationship between each SQL clause of the corresponding original type. By replacing the placeholder information in the outer SQL with the inner SQL clause of the target type, the second SQL statement of the target type can be obtained.

[0072] Step S104: Output the second SQL statement of the target type.

[0073] After obtaining the second SQL statement of the target type, the second SQL statement of the target type is output to the client for display so that the user can view and use it; or, the second SQL statement of the target type is returned to other applications through a callback interface.

[0074] The method in this embodiment obtains a first SQL statement of the original type to be converted, segments the first SQL statement using the delimiter set corresponding to the original type, and obtains content fragments of the first SQL statement; in the key command set corresponding to the original type, it searches for the key command that matches the content fragment of the first SQL statement, calls the conversion program corresponding to the matched key command, and converts the first SQL statement into a second SQL statement of the target type with the same function by executing the corresponding conversion program; and outputs the second SQL statement of the target type. This realizes the automatic conversion of the first SQL statement of the original type into the second SQL statement of the target type, eliminates the technical barrier of technicians who are not familiar with the database, reduces the manual cost of SQL statement conversion, and improves the efficiency of SQL statement conversion.

[0075] Figure 2 This is a flowchart illustrating the configuration of a delimiter set provided in an exemplary embodiment of this application. In an optional embodiment, those skilled in the art can flexibly configure the delimiters included in the delimiter set through a delimiter configuration interface provided by an electronic device. Specifically, as... Figure 2 As shown, the specific process for configuring the delimiter set is as follows:

[0076] Step S200: Configure the default delimiter set.

[0077] In this embodiment, a default set of delimiters can be configured, which includes spaces, various punctuation marks, and various operators.

[0078] In addition, the default delimiter set can be configured to include symbols used in other primitive SQL types, excluding reserved keywords. The specific configuration and adjustment can be made according to the actual application scenario. This embodiment does not impose any specific limitations here.

[0079] For different raw SQL types, you can configure different default delimiter sets, or configure a uniform default delimiter set.

[0080] Step S201: In response to the delimiter set configuration instruction, display the delimiter configuration interface, and display the symbol set used by the original type and the currently configured delimiter set in the delimiter configuration interface.

[0081] In this embodiment, users or relevant technicians can configure the delimiters included in the delimiter set at any time. Specifically, users or relevant technicians can send delimiter set configuration instructions to the electronic device through a specific command line or by triggering controls on the visual interface (e.g., clicking the "Configure Delimiter Set" button, link, etc.).

[0082] Upon receiving a delimiter set configuration command, the electronic device displays a delimiter configuration interface on the client side. This interface shows the symbol set used by the original type, as well as the currently configured delimiter set. The symbol set used by the original type includes all or some of the symbols used in the original type's SQL, excluding reserved keywords; these can be understood as symbols that can be considered as candidate delimiters.

[0083] Step S202: In response to the operation of determining at least one symbol in the symbol set displayed in the delimiter configuration interface as a delimiter, add at least one symbol to the delimiter set.

[0084] Based on the symbol set used by the original type displayed in the delimiter configuration interface, users can select one or more symbols to add to the delimiter set as delimiters.

[0085] The operation of identifying at least one symbol in the symbol set displayed in the delimiter configuration interface as a delimiter can be performed by the user selecting at least one symbol in the symbol set displayed in the delimiter configuration interface and clicking the "Add to Delimiter Set" control in the current interface; or it can be performed by the user dragging at least one symbol in the symbol set displayed in the delimiter configuration interface to a specified area; in addition, other operations that can achieve this function can also be performed, which are not specifically limited here in this embodiment.

[0086] For example, the delimiter configuration interface displays an "Add to delimiter set" control. The user can select at least one symbol from the symbol set displayed in the delimiter configuration interface, and by clicking the "Add to delimiter set" control in the current interface, trigger the electronic device to add the selected at least one symbol to the delimiter set.

[0087] For example, the delimiter configuration interface can display a specified area corresponding to the delimiter set. The specified area can be displayed in the form of a rectangle, folder, etc. The user can select at least one symbol in the symbol set displayed in the delimiter configuration interface and drag the selected symbol to the specified area corresponding to the delimiter set. This drag-and-drop operation triggers the electronic device to add the selected at least one symbol to the delimiter set.

[0088] Step S203: In response to the operation of removing at least one delimiter from the currently configured delimiter set, delete at least one delimiter from the currently configured delimiter set.

[0089] In this embodiment, users are also allowed to remove any delimiter that has been added to the delimiter set from the delimiter set. The removed delimiter will no longer be used as a delimiter.

[0090] The operation of removing at least one delimiter from the currently configured delimiter set can be an operation where the user selects at least one delimiter from the delimiter set and clicks the "Remove from Delimiter Set" control on the current interface; or it can be an operation where the user selects at least one delimiter from the delimiter set and drags the selected at least one delimiter to outside the specified area corresponding to the delimiter set; in addition, other operations that can achieve this function can also be used, which are not specifically limited here in this embodiment.

[0091] For example, the delimiter configuration interface displays a "Remove from Delimiter Set" control. Users can select at least one delimiter from the delimiter set and, by clicking the "Remove from Delimiter Set" control in the current interface, trigger the electronic device to delete the selected at least one delimiter from the currently configured delimiter set.

[0092] For example, the delimiter configuration interface can display a specified area corresponding to the delimiter set. The specified area can be displayed in the form of a rectangle, folder, etc. The user can select at least one delimiter in the delimiter set and drag the selected at least one delimiter outside the specified area corresponding to the delimiter set. This drag operation triggers the electronic device to delete the selected at least one delimiter from the currently configured delimiter set.

[0093] Optionally, the original delimiters in the default delimiter set can be configured to be non-deletable delimiters to ensure the correct execution of SQL statement conversion.

[0094] The method in this embodiment, when applied to the conversion of SQL of different primitive types, allows users or relevant technicians to flexibly configure and adjust the delimiter set used during SQL conversion based on the symbol set used by the current primitive type, thereby increasing the applicability of the solution.

[0095] Figure 3 This document provides a flowchart illustrating the configuration of key commands and conversion programs as part of an exemplary embodiment of this application. In an optional embodiment, before performing SQL statement conversion, conversion programs corresponding to various SQL statements of the original type are pre-developed, key commands corresponding to these statements are configured, a key command set is constructed, and the calling rules for the conversion programs corresponding to each key command are configured. The key commands in the key command set and their corresponding conversion programs can be flexibly updated subsequently.

[0096] Specifically, such as Figure 3 As shown, the configuration process for key commands and the conversion program is as follows:

[0097] Step S301: Obtain a conversion program for at least one SQL statement of the original type.

[0098] In this embodiment, different types of SQL statements refer to SQL statements based on different syntax specifications / rules. For example, different types of databases support the use of different SQL statements. For any type of SQL statement (including primitive types and target types), there are SQL statements capable of implementing various functions. For example, SQL statements used to implement various functions such as data querying, data insertion, data deletion, data grouping, filtering, and sorting. The syntax structure of SQL statements used to implement the same function / operation in different types of SQL statements may differ.

[0099] In the example application scenario where the user changes the applicable database, the original type of SQL statement can be the SQL statement supported by the original database used by the user before the change, and the target type of SQL statement can be the SQL statement supported by the target database used by the user after the change.

[0100] For example, when a user changes their database from Teradata to Hive, the original type of SQL statement can be Teradata SQL statement supported by the Teradata database, and the target type of SQL statement can be Hive SQL statement used by the Hive database. The solution in this application converts the Teradata SQL statement used by the Teradata database into the Hive SQL statement used by the Hive database.

[0101] In addition, the SQL statement of the original type can also be any SQL statement other than Teradata SQL statement, and the SQL statement of the target type can also be any SQL statement other than Hive SQL statement. This embodiment does not make specific limitations here.

[0102] In this embodiment, a conversion program for a type of SQL statement in the original type is used to convert that type of SQL statement into a target type SQL statement with the same function. A conversion program for at least one type of SQL statement in the original type can be developed by a person skilled in both the original type SQL and the target type SQL, and the calling rules of the conversion program can be configured.

[0103] For example, in Teradata SQL, the SQL statement used to delete data is the DELETE statement. The syntax of the DELETE statement is: DELETE FROM table_name WHERE condition. Here, table_name refers to the name of the data table, and condition represents the condition that the data to be deleted must meet.

[0104] However, Hive SQL does not directly support the DELETE statement for deleting data. Based on Hive SQL's syntax rules, the same functionality can be achieved using the INSERT OVERWRITE statement combined with query conditions. The specific process is as follows:

[0105] First, create a temporary table using the following Hive SQL statement to store the data to be retained after executing the DELETE statement in the Teradata database:

[0106] CREATE TABLE temp_table AS

[0107] SELECT*FROM table_name WHERE NOT condition;

[0108] The `SELECT * FROM table_name WHERE NOT condition` statement retrieves data from the `table_name` table that does not meet the `condition` condition; in other words, it retains the data after executing the `DELETE` statement on the `table_name` table. `temp_table` is the name of the temporary table being created.

[0109] Then, the data from the temporary table is written back to the original table (named table_name) using the following INSERT OVERWRITE statement in Hive SQL:

[0110] INSERT OVERWRITE TABLE table_name SELECT*FROM temp_table;

[0111] Therefore, the same functionality as the DELETE statement in Teradata SQL can be achieved through multiple Hive SQL statements.

[0112] For example, in Teradata SQL, QUALIFY is a clause used to filter results, and it can be used with window functions. The QUALIFY clause allows filtering of results based on the window function's outcome after computation. A SELECT statement using the QUALIFY clause is as follows:

[0113] SELECT column1, column2, ...

[0114] FROM table_name

[0115] QUALIFY ROW_NUMBER()OVER(PARTITION BY column1 ORDER BY column2)=1;

[0116] In this SELECT statement example, the QUALIFY clause uses the ROW_NUMBER() window function to number the rows in each column1 partition and sort them according to the sort order of column2. Then, the QUALIFY clause filters out the first row in each partition.

[0117] However, in Hive SQL, the Hive database does not directly use the QUALIFY clause. Based on Hive SQL's syntax rules, the same functionality can be achieved using subqueries and window functions, as follows:

[0118] SELECT column1, column2, ...

[0119] FROM(

[0120] SELECT column1,column2,...,ROW_NUMBER()OVER(PARTITION BYcolumn1 ORDERBY column2)as rn

[0121] FROM table_name

[0122] )t

[0123] WHERErn = 1;

[0124] The subquery uses the ROW_NUMBER() window function to number the rows in each column1 partition and sorts them according to the sort order of column2. Then, the outer query filters out the first row in each partition.

[0125] Step S302: Select at least one keyword corresponding to an SQL statement as a key command, construct a key command set, and configure the calling rules of the conversion program corresponding to each key command.

[0126] In this embodiment, for at least one SQL statement in the original type SQL that already has a corresponding conversion program, the keywords corresponding to the SQL statement can be used as key commands to construct a key command set. The key command set includes all existing key commands. Keywords that can serve as key commands refer to SQL statements that can uniquely identify a type of function, such as SELECT, INSERT, and DELETE.

[0127] Furthermore, to achieve automatic SQL statement conversion, it is necessary to configure the calling rules for the conversion program corresponding to each key command in the key command set. These calling rules include information required to call and execute the conversion program, such as the calling interface and input parameters.

[0128] Step S303: In response to the key command configuration instruction, display the key command configuration interface and display the keywords used for original type retention in the key command configuration interface.

[0129] In this embodiment, users and relevant technical personnel can adjust the configured key command set and key command invocation rules.

[0130] Specifically, technical personnel can send key command configuration instructions to electronic devices by pre-setting command lines or triggering controls on the visual interface (e.g., clicking the "Configure Key Command Set" button, link, etc.).

[0131] In response to receiving a key command configuration instruction, the electronic device displays the key command configuration interface through the client and displays the keywords used for original type retention through the key command configuration interface.

[0132] Step S304: In response to the operation of identifying at least one keyword displayed in the key command configuration interface as a key command, add at least one keyword to the key command set and configure the calling rules of the conversion program corresponding to each of the at least one keyword.

[0133] After the relevant technical personnel obtain the conversion program for at least one SQL statement of the original type that has been developed, they can select the keywords corresponding to the original type used based on the keywords displayed in the key command configuration interface, and add them to the key command set.

[0134] The operation of identifying at least one keyword displayed in the key command configuration interface as a key command can be, for example, selecting at least one keyword displayed in the key command configuration interface and clicking the "Add as Key Command" control in the current interface; or, dragging at least one keyword displayed in the key command configuration interface to a specified area; or, other operations that can achieve this function, which are not specifically limited here in this embodiment.

[0135] For example, a "Add as Key Command" control is displayed in the key command configuration interface. When a relevant technician selects at least one keyword displayed in the key command configuration interface, the key command configuration interface provides a calling rule configuration area for the conversion program corresponding to the at least one keyword. The relevant technician can configure the calling rules corresponding to the keyword in the calling rule configuration area. Then, by clicking the "Add as Key Command" control in the current interface, the relevant technician triggers the electronic device to add the selected at least one keyword to the key command set and store the calling rules of the conversion program corresponding to each of the at least one keyword.

[0136] For example, the key command configuration interface can display a designated area corresponding to the key command set. This designated area can be displayed as a rectangle, folder, or similar format. Technical personnel can select at least one keyword displayed on the key command configuration interface and drag it to the designated area corresponding to the key command set. This drag-and-drop operation triggers the electronic device to add the selected keyword to the key command set. For newly added key commands, the key command configuration interface provides a configuration area for the calling rules of the conversion program corresponding to at least one keyword. Technical personnel can configure the calling rules corresponding to the key commands in this area. Then, by clicking the "Save Calling Rules" control on the current interface, technical personnel trigger the electronic device to store the calling rules of the conversion program corresponding to the key commands.

[0137] In this embodiment, skilled personnel can flexibly configure and adjust the key commands for SQL conversion and the calling rules of the corresponding conversion programs. When the conversion program corresponding to a key command changes, the SQL statement conversion process is not affected as long as the calling rules of the conversion program remain unchanged. If the calling rules of the conversion program change, only the calling rules of the conversion program configured for the relevant key command need to be updated, thus improving the scalability and reliability of SQL statement conversion.

[0138] Figure 4 This is a schematic diagram of the structure of an SQL statement conversion device provided in an embodiment of this application. The SQL statement conversion device provided in this embodiment can execute the processing flow provided in the SQL statement conversion method embodiment. Figure 4 As shown, the SQL statement conversion device 40 includes: a module 41 for obtaining the SQL statement to be converted, a module 42 for splitting the SQL statement, a module 43 for converting the SQL statement, and a module 44 for outputting the target SQL statement.

[0139] Specifically, the SQL statement acquisition module 41 is used to acquire the first SQL statement of the original type to be converted.

[0140] SQL statement segmentation module 42 is used to segment the first SQL statement using the segmentation symbol set corresponding to the original type, so as to obtain the content fragment of the first SQL statement.

[0141] The SQL statement conversion module 43 is used to find the key command that matches the content fragment of the first SQL statement in the key command set corresponding to the original type, call the conversion program corresponding to the key command, and convert the first SQL statement into a second SQL statement of the target type with the same function.

[0142] The target SQL statement output module 44 is used to output the second SQL statement of the target type.

[0143] In an optional embodiment, when implementing the first SQL statement for obtaining the original type to be converted, the SQL statement acquisition module 41 is further configured to:

[0144] Receive SQL statement information submitted by the user to be converted. The SQL statement information contains at least one SQL statement of primitive type, and adjacent SQL statements are separated by a preset delimiter. Use the preset delimiter to split the SQL statement information to obtain at least one first SQL statement of primitive type.

[0145] In an optional embodiment, when receiving the SQL statement to be converted submitted by the user, the SQL statement acquisition module 41 is further configured to:

[0146] The input interface displays the SQL statement to be converted. The input interface provides an input area for the original type of SQL statement and displays prompts. The prompts are used to remind the user to separate adjacent SQL statements with a preset delimiter. The interface also receives SQL statement information edited and submitted by the user through the input interface.

[0147] In an optional embodiment, when receiving the SQL statement to be converted submitted by the user, the SQL statement acquisition module 41 is further configured to:

[0148] Receive SQL files uploaded by users. The SQL files record the original types of SQL statements to be converted. Different SQL statements in the SQL files are separated by preset delimiters. Read the contents of the SQL files to obtain the SQL statement information.

[0149] In an optional embodiment, the SQL statement conversion device 40 further includes: a delimiter set configuration module, used for:

[0150] In response to a delimiter set configuration command, a delimiter configuration interface is displayed, showing the symbol set used by the original type and the currently configured delimiter set. In response to an operation that identifies at least one symbol in the symbol set displayed in the delimiter configuration interface as a delimiter, at least one symbol is added to the delimiter set. In response to an operation that removes at least one delimiter from the currently configured delimiter set, at least one delimiter is deleted from the currently configured delimiter set.

[0151] In an optional embodiment, the SQL statement conversion device 40 further includes: a key command related configuration module, used for:

[0152] Obtain the conversion program for at least one SQL statement of the original type; construct a key command set by taking the keywords corresponding to at least one SQL statement as key commands, and configure the calling rules of the conversion program corresponding to each key command.

[0153] In an optional embodiment, the key command-related configuration module is further configured to:

[0154] In response to a key command configuration instruction, the key command configuration interface is displayed, and the keywords used for preserving the original type are displayed in the key command configuration interface; in response to the operation of determining at least one keyword displayed in the key command configuration interface as a key command, at least one keyword is added to the key command set, and the calling rules of the conversion program corresponding to each of the at least one keyword are configured.

[0155] The apparatus provided in this application embodiment can be specifically used to execute the solution provided in any of the above method embodiments. The specific functions and technical effects that can be achieved will not be elaborated here.

[0156] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an example embodiment of this application. Figure 5 As shown, the electronic device 50 includes a processor 501 and a memory 502 communicatively connected to the processor, the memory 502 storing computer-executed instructions.

[0157] The processor 501 executes computer execution instructions stored in the memory 502 to implement the method provided in any of the above method embodiments. The specific functions and technical effects that can be achieved will not be described here.

[0158] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the method provided in any of the above method embodiments.

[0159] This application also provides a computer program product, which includes: a computer program stored in a readable storage medium, at least one processor of an electronic device can read the computer program from the readable storage medium, and the at least one processor executes the computer program to cause the electronic device to perform the method provided in any of the above method embodiments.

[0160] In the several embodiments provided in this application, it should be understood that the disclosed apparatus and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between apparatuses or units may be electrical, mechanical, or other forms.

[0161] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0162] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or in a combination of hardware and software functional units.

[0163] The integrated units implemented as software functional units described above can be stored in a computer-readable storage medium. These software functional units, stored in a storage medium, include several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) or processor to execute some steps of the methods of the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0164] Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the above-described division of functional modules is merely an example. In practical applications, the above functions can be assigned to different functional modules as needed, that is, the internal structure of the device can be divided into different functional modules to complete all or part of the functions described above. The specific working process of the device described above can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here.

[0165] Other embodiments of this application will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of this application that follow the general principles of this application and include common knowledge or customary techniques in the art not disclosed herein. The specification and examples are to be considered exemplary only, and the true scope and spirit of this application are indicated by the following claims.

[0166] It should be understood that this application is not limited to the precise structure described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of this application is limited only by the appended claims.

Claims

1. A method of converting SQL statements, characterized by, The method comprises the following steps: obtaining a first SQL statement of an original type to be converted; segmenting the first SQL statement using a segmentation symbol set corresponding to the original type to obtain a content segment of the first SQL statement; in a key command set corresponding to the original type, finding a first key command matched by the content segment of the first SQL statement, and calling a conversion program corresponding to the first key command to convert the first SQL statement into a second SQL statement of a target type with the same function; outputting the second SQL statement of the target type; wherein the second SQL statement does not include the first key command, but includes a second key command, the first key command is a key command supported by a database of the original type, the second key command is a key command supported by a database of the target type, and the database of the target type does not support the first key command; further comprising: obtaining a conversion program of at least one SQL statement of an original type; constructing a key command set by taking keywords corresponding to the at least one SQL statement as key commands, and configuring calling rules of conversion programs corresponding to the key commands; in response to a key command configuration instruction, displaying a key command configuration interface, and displaying keywords reserved for use by the original type in the key command configuration interface; in response to an operation of determining at least one keyword displayed in the key command configuration interface as a key command, adding the at least one keyword to the key command set, and configuring calling rules of conversion programs corresponding to the at least one keyword respectively.

2. The method of claim 1, wherein, The method of obtaining a first SQL statement of an original type to be converted comprises: receiving SQL statement information to be converted submitted by a user, wherein the SQL statement information contains at least one SQL statement of an original type, and adjacent SQL statements are separated by a preset delimiter; segmenting the SQL statement information using the preset delimiter to obtain at least one first SQL statement of an original type.

3. The method of claim 2, wherein, The method of receiving SQL statement information to be converted submitted by a user comprises: displaying an input interface of SQL statements to be converted, the input interface providing an input area of SQL statements of an original type, and displaying prompt information for prompting a user to separate adjacent SQL statements in a plurality of SQL statements to be input by using a preset delimiter; receiving SQL statement information edited and submitted by a user through the input interface.

4. The method of claim 2, wherein, The method of receiving SQL statement information to be converted submitted by a user comprises: receiving a SQL file uploaded by a user, wherein the SQL file records SQL statements of an original type to be converted, and different SQL statements in the SQL file are separated by a preset delimiter; reading the content of the SQL file to obtain SQL statement information.

5. The method according to any one of claims 1 to 4, characterized in that, Further comprising: in response to a segmentation symbol set configuration instruction, displaying a segmentation symbol configuration interface, and displaying a symbol set used by the original type and a currently configured segmentation symbol set in the segmentation symbol configuration interface; In response to an operation of determining at least one symbol displayed in the symbol set of the split symbol configuration interface as a split symbol, the at least one symbol is added to the split symbol set; In response to an operation of removing at least one split symbol in the currently configured split symbol set, the at least one split symbol is deleted from the currently configured split symbol set.

6. An SQL statement conversion apparatus characterized by comprising: The method comprises the following steps: an SQL statement to be converted obtaining module, configured to obtain a first SQL statement of an original type to be converted; an SQL statement splitting module, configured to split the first SQL statement using a split symbol set corresponding to the original type to obtain a content segment of the first SQL statement; an SQL statement converting module, configured to search for a first key command matched by the content segment of the first SQL statement in a key command set corresponding to the original type, and call a conversion program corresponding to the first key command to convert the first SQL statement into a second SQL statement of a target type with the same function; a target SQL statement output module, configured to output the second SQL statement of the target type; wherein the second SQL statement does not include the first key command but includes a second key command, the first key command is a key command supported by a database of the original type, the second key command is a key command supported by a database of the target type, and the database of the target type does not support the first key command; further comprising a key command related configuration module, configured to: obtain a conversion program of at least one SQL statement of the original type; construct a key command set by taking a keyword corresponding to the at least one SQL statement as a key command, and configure a calling rule of a conversion program corresponding to each key command; in response to a key command configuration instruction, display a key command configuration interface, and display a keyword reserved for use by the original type in the key command configuration interface; in response to an operation of determining at least one keyword displayed in the key command configuration interface as a key command, add the at least one keyword to the key command set, and configure a calling rule of a conversion program corresponding to the at least one keyword respectively.

7. An electronic device, comprising: The method comprises the following steps: a processor, and a memory connected to the processor in communication; the memory stores computer execution instructions; the processor executes the computer execution instructions stored in the memory to implement the method according to any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that, The computer readable storage medium stores computer execution instructions, and the computer execution instructions are executed by the processor to implement the method according to any one of claims 1 to 5.

Images