Hybrid SQL script file generation method, execution method and device

Abstract

The present invention provides a hybrid SQL script file generation method, execution method and device, which can be used in the field of financial technology. The method includes determining multiple code segments to be executed and code execution information; forming description information for each code segment according to the code execution information and a preset identification symbol; and arranging the multiple code segments and corresponding description information in sequence according to a preset execution order to form a hybrid SQL script. The present invention can provide a script that can be operated in heterogeneous databases and outside the database, thereby improving the efficiency and flexibility of data processing.

Description

Technical Field

[0001] The present invention relates to the field of Internet technology, in particular to the field of financial technology, and more particularly to a method and device for generating a hybrid SQL script file. Background Art

[0002] In an information system centered on data processing, data processing personnel usually compile multiple database-related SQL files, which are then interpreted and executed by tools provided by the database system. Different database systems provide different executors for executing SQL files, and the formats of SQL files in different databases are also different. For example, Oracle provides sqlplus to execute its PL / SQL files, and hive provides beeline to execute SQL files specific to big data. When an application system needs to access multiple libraries at the same time, and SQL instructions need to be executed crosswise in multiple libraries, the traditional single SQL file will hardly meet the requirements. In addition, when data interaction between multiple libraries is required, or the data needs to be calculated outside the library, traditional SQL files cannot express this and can only be achieved by compiling additional applications. In these complex scenarios, traditional SQL files and their professional executors cannot meet the flexible expansion requirements of business operations. Summary of the invention

[0003] One object of the present invention is to provide a hybrid SQL script file generation method, providing a script that can be operated in heterogeneous databases and outside the database, thereby improving the efficiency and flexibility of data processing. Another object of the present invention is to provide a server, a terminal, a computer device and a readable medium.

[0004] In order to achieve the above objectives, the present invention discloses a hybrid SQL script file generation method, comprising:

[0005] Determine multiple code segments to be executed and code execution information;

[0006] Forming description information of each code segment according to the code execution information and a preset identification symbol;

[0007] The multiple code segments and corresponding description information are sequentially arranged according to a preset execution order to form a hybrid SQL script.

[0008] Preferably, the description information is a comment;

[0009] The description information of each code segment formed according to the code execution information and the preset identification symbol specifically includes:

[0010] Determine the parameter value corresponding to the preset identification symbol according to the code execution information;

[0011] Obtaining description content information according to the preset identification symbol and the corresponding parameter value;

[0012] The description information is formed according to the description content information and preset annotation format symbols.

[0013] Preferably, the description information is an identification string;

[0014] The description information of each code segment formed according to the code execution information and the preset identification symbol specifically includes:

[0015] Determine the parameter value corresponding to the preset identification symbol according to the code execution information;

[0016] Obtaining description content information according to the preset identification symbol and the corresponding parameter value;

[0017] The description information is formed according to the description content information and a preset identification string format symbol.

[0018] Preferably, the description information includes comments and identification strings;

[0019] The description information of each code segment formed according to the code execution information and the preset identification symbol specifically includes:

[0020] Determine, according to the code execution information, parameter values ​​corresponding to the preset identification symbols of the annotation and identification string;

[0021] Obtaining description content information according to the preset identification symbol and the corresponding parameter value;

[0022] The description information in the form of annotation is formed according to the description content information and preset annotation format symbols, and the description information in the form of identification string is formed according to the description content information and preset identification string format symbols.

[0023] Preferably, the code execution information includes the code type. If the code type is an out-of-library operation;

[0024] The code segment includes a command type, a parameter and a corresponding parameter value set in a preset data exchange format.

[0025] Preferably, the step of sequentially arranging the multiple code segments and the corresponding description information according to a preset execution order to form a hybrid SQL script specifically includes:

[0026] Set a separator for each code segment and the corresponding description information of the code segment;

[0027] For each code segment, an execution unit is formed according to the order of the separator, the description information and the code segment;

[0028] All execution units are arranged in sequence according to a preset execution order to obtain the hybrid SQL script.

[0029] The present invention also discloses a method for executing the hybrid SQL script file as described above, comprising:

[0030] Parsing the hybrid SQL script to obtain multiple code segments and corresponding description information;

[0031] Determine the code execution information corresponding to the description information of each code segment;

[0032] The code segments are executed in sequence according to the execution order of the multiple code segments and the code execution information of each code segment.

[0033] Preferably, parsing the hybrid SQL script to obtain multiple code segments and corresponding description information specifically includes:

[0034] Identify delimiters in the hybrid SQL script;

[0035] Extract the execution unit corresponding to each separator in turn;

[0036] Each execution unit is parsed to obtain a plurality of code segments in an expected execution order and corresponding description information.

[0037] Preferably, the determining of the code execution information corresponding to the description information of each code segment specifically includes:

[0038] The description information of each code segment is parsed according to a preset identification symbol to obtain code execution information of the code segment, where the code execution information includes a code type, execution parameters, and corresponding parameter values.

[0039] Preferably, executing each code segment in sequence according to the execution order of the multiple code segments and the code execution information of each code segment specifically includes:

[0040] Obtain and execute each code segment in sequence according to the execution order, wherein executing the code segment includes the following steps:

[0041] determining a code type of the code segment according to the code execution information of each code segment;

[0042] If the code type is in-library operation, the code segment is sent to a corresponding database for in-library operation according to the parameter value of the execution parameter;

[0043] If the code type is an off-library operation, the code segment is parsed according to a preset data exchange format to obtain the command type, the parameters in the command type and the corresponding parameter values, and the parameters and the corresponding parameter values ​​are transmitted to the off-library operation unit corresponding to the command type for off-library operation.

[0044] The present invention also discloses a hybrid SQL script file generating device, comprising:

[0045] An information acquisition module, used to determine multiple code segments to be executed and code execution information;

[0046] A description information setting module, used for forming description information of each code segment according to the code execution information and a preset identification symbol;

[0047] The script generation module is used to sequentially arrange the multiple code segments and corresponding description information according to a preset execution order to form a hybrid SQL script.

[0048] The present invention also discloses an execution device of the hybrid SQL script file as described above, comprising:

[0049] A script parsing module, used for parsing the hybrid SQL script to obtain multiple code segments and corresponding description information;

[0050] A description information parsing module, used to determine the code execution information corresponding to the description information of each code segment;

[0051] The script execution module is used to execute each code segment in sequence according to the execution order of multiple code segments and the code execution information of each code segment.

[0052] The present invention also discloses a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor.

[0053] When the processor executes the program, the method described above is implemented.

[0054] The present invention also discloses a computer readable medium having a computer program stored thereon.

[0055] When the program is executed by a processor, the method described above is implemented.

[0056] The hybrid SQL script file generation method of the present invention determines multiple code segments to be executed and code execution information; forms description information of each code segment according to the code execution information and a preset identification symbol; and sequentially sets the multiple code segments and the corresponding description information according to a preset execution order to form a hybrid SQL script. Thus, the hybrid SQL script generated by the present invention sets multiple code segments in one file, sets description information for each code segment, so that when the hybrid SQL script is executed, each code segment is extracted through the description information, and the description information corresponding to each code segment can be parsed, and the code execution information of each code segment obtained by parsing is used to perform corresponding operations. It can be seen from this that the hybrid SQL script of the present invention allows the code segments of the in-library operation and the code segments of the out-library operation of multiple different databases to be placed in one file, and the parsing and execution mode of the code segments is set in the form of description information, and the database executor or out-library operation unit corresponding to each code segment is called by the description information to process the code segments. The present invention sets multiple files of in-library operation and out-library operation in one script, supports multi-library operation of homogeneous databases or heterogeneous databases, and also supports mixed operation of in-library and out-library, thereby improving the efficiency and flexibility of data processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0057] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0058] Figure 1 A schematic diagram showing the execution of multiple SQL files and out-of-library operation logic in the prior art;

[0059] Figure 2 A flow chart showing a specific embodiment of the hybrid SQL script file generation method of the present invention;

[0060] Figure 3 One of the flowcharts of a specific embodiment S200 of the hybrid SQL script file generation method of the present invention is shown;

[0061] Figure 4 The second flowchart of the specific embodiment S200 of the hybrid SQL script file generation method of the present invention is shown;

[0062] Figure 5 The third flowchart of the specific embodiment S200 of the hybrid SQL script file generation method of the present invention is shown;

[0063] Figure 6A flowchart showing a specific embodiment S300 of the hybrid SQL script file generation method of the present invention;

[0064] Figure 7 A flowchart showing a specific embodiment of a method for executing a hybrid SQL script file of the present invention;

[0065] Figure 8 A flowchart showing a specific embodiment S400 of the method for executing a hybrid SQL script file of the present invention;

[0066] Fig. 9 A structural diagram showing a specific embodiment of the hybrid SQL script file generating device of the present invention;

[0067] Fig.10 A structural diagram showing a specific embodiment of a hybrid SQL script file execution device of the present invention;

[0068] Fig.11 A schematic diagram showing the structure of a computer device suitable for implementing an embodiment of the present invention is shown. DETAILED DESCRIPTION

[0069] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. 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.

[0070] It should be noted that the hybrid SQL script file generation method and device disclosed in the present application can be used in the field of financial technology, and can also be used in any field other than the field of financial technology. The application field of the hybrid SQL script file generation method and device disclosed in the present application is not limited.

[0071] In one or more embodiments of the present invention, the SQL file refers to a text file compiled using the database structured query language (SQL) grammar, and such a file usually has a suffix of .sql.

[0072] In one or more embodiments of the present invention, off-database operations refer to part of the business logic that does not run in the database system but runs in a host outside the database in a system with a database as the operation center. However, this part of the logic is usually related to the database operation. This part of the operation that is not in the database is referred to as off-database operation.

[0073] In one or more embodiments of the present invention, the SQL file executor is a tool provided by a database manufacturer that can directly execute a single SQL file. The SQL file executed by this tool is usually only applicable to a specific database system.

[0074] In one or more embodiments of the present invention, a homogeneous database refers to a plurality of database systems existing in one application system, and each database system is of the same type, such as Oracle or MySQL.

[0075] In one or more embodiments of the present invention, a heterogeneous database refers to a situation in which there are multiple database systems in one application system, and the types of each database system are different, for example, an application system is composed of a Hive big data platform and an Oracle database.

[0076] In one or more embodiments of the present invention, a hybrid SQL script file refers to a script file composed of code segments representing multiple database system in-library operations and / or out-library operation logics. This script file contains multiple database system SQL code segments and some out-library operation logics in a single file. When executed, it will be interspersed in multiple database systems and out-library hosts in sequence, thereby achieving the purpose of completing a certain operation task as a whole. The multiple database systems can be heterogeneous database systems or multiple homogeneous database systems.

[0077] In one or more embodiments of the present invention, the hybrid SQL script file executor refers to an execution module of the script file of the present invention, which can parse the hybrid SQL script file and execute each code segment in the script in sequence.

[0078] In the prior art, when data processing includes SQL files that need to be executed in multiple databases and / or logic files that are operated outside the database, each SQL file needs to be executed using a corresponding SQL executor. Then, SQL files of heterogeneous databases need to be executed by different SQL executors, and logic files that are operated outside the database need to be executed by corresponding external database operation programs. Figure 1As shown, for a data processing file including multiple SQL files and logic files, a dedicated job scheduler is usually required to execute each SQL file in a predetermined order and call an external computing program to execute the logic file to execute a complete data processing job, that is, the job scheduler processes 1.sql, 2.sql, logic file 1, 3.sql and logic file 2 in sequence through the SQL executor of each database (DB1, DB2) or calls an external computing unit in a predetermined order. Among them, command line execution tools corresponding to different databases are usually used when executing SQL files, such as sqlplus provided by Oracle, mysql provided by Mysql, beeline provided by HIVE, etc.

[0079] It should be noted that SQL files, such as Figure 1 The 1.sql, 2.sql, etc. shown in the figure contain a series of SQL instructions, including DDL instructions and DML instructions. Different instructions are separated by semicolons (;) or other database-specific separators. When executed, each SQL instruction is executed in sequence. An SQL file is usually executed by a database system, and its instructions usually only support one type of database system. For example, an SQL file using PL / SQL syntax can only run in Oracle or Oracle-compatible database systems.

[0080] The off-database operation unit includes off-database operation programs used to complete specific tasks, such as file parsing and storage, data transmission between different databases, etc. The off-database operation unit cannot be implemented using SQL files, but needs to be implemented using a dedicated programming language, such as Java, Python and other statements.

[0081] Therefore, the script execution scenarios in the prior art may include the following: (1) There are multiple homogeneous databases in an application system and data operations need to be interspersed in multiple databases, that is, multiple SQL executors are required to execute the script; (2) There are one or more homogeneous databases in an application system and the combination of in-database operations and out-database operations is required, that is, one or more SQL executors and out-database operation units are required to execute the script; (3) There are multiple heterogeneous databases in an application system and data operations need to be interspersed in multiple databases, that is, multiple SQL executors of different databases are required to execute the script; (4) There are multiple heterogeneous databases in an application system and the combination of in-database operations and out-database operations is required, that is, multiple SQL executors of different databases and out-database operation units are required to execute the script.

[0082] In summary, in the scenario of mixed operations of multiple database SQL files and out-of-library operations, the main disadvantages of the existing technical solutions are: (1) Since SQL files can only be used for specific purposes, mixed operations of multiple SQL files are not flexible: the operation order of multiple database systems can only be based on SQL files. Therefore, for applications that require more dependencies, only a larger number of SQL files can be set, and the in-library operations contained in each file will become very small, which will increase the complexity of scheduling and make the management of SQL files inflexible. (2) The out-of-library operation unit and the in-library SQL file execution unit cannot be effectively integrated: Since the out-of-library operation unit and the in-library operation of the SQL file are completely independent, the effective integration of the two cannot be achieved, and a dedicated job scheduler is required to control the operation process.

[0083] According to one aspect of the present invention, this embodiment discloses a method for generating a hybrid SQL script file.

[0084] like Figure 2 As shown, in this embodiment, the method includes:

[0085] S100: Determine multiple code segments to be executed and code execution information.

[0086] S200: Forming description information of each code segment according to the code execution information and a preset identification symbol.

[0087] S300: Arrange the multiple code segments and corresponding description information in sequence according to a preset execution order to form a hybrid SQL script.

[0088] The hybrid SQL script file generation method of the present invention determines multiple code segments to be executed and code execution information; forms description information of each code segment according to the code execution information and a preset identification symbol; and sequentially sets the multiple code segments and the corresponding description information according to a preset execution order to form a hybrid SQL script. Thus, the hybrid SQL script generated by the present invention sets multiple code segments in one file, sets description information for each code segment, so that when the hybrid SQL script is executed, each code segment is extracted through the description information, and the description information corresponding to each code segment can be parsed, and the code execution information of each code segment obtained by parsing is used to perform corresponding operations. It can be seen from this that the hybrid SQL script of the present invention allows the code segments of the in-library operation and the code segments of the out-library operation of multiple different databases to be placed in one file, and the parsing and execution mode of the code segments is set in the form of description information, and the database executor or out-library operation unit corresponding to each code segment is called by the description information to process the code segments. The present invention sets multiple files of in-library operation and out-library operation in one script, supports multi-library operation of homogeneous databases or heterogeneous databases, and also supports mixed operation of in-library and out-library, thereby improving the efficiency and flexibility of data processing.

[0089] In a preferred embodiment, Figure 3 As shown, the description information is a comment. The step S200 forms description information for each code segment according to the code execution information and the preset identification symbol, specifically including:

[0090] S211: Determine a parameter value corresponding to the preset identification symbol according to the code execution information.

[0091] S212: Obtain description content information according to the preset identification symbol and the corresponding parameter value.

[0092] S213: forming the description information according to the description content information and preset annotation format symbols.

[0093] Specifically, it is understandable that data processing personnel need to compile the code segments to be executed in advance and configure the code execution information of the code segments. The code execution information may include the code type (in-library operation or out-library operation) and the execution parameters corresponding to different code types and the corresponding parameter values.

[0094] In one or more embodiments of the present invention, for a code segment of an in-library operation, the code segment includes at least one SQL statement, the code type of the code segment of the SQL statement is sql, and the execution parameters may include at least one of the parameters such as code number, code name, execution mode (separate execution or overall execution), execution statement type (SQL statement execution or stored procedure statement execution), database name, and database type. The executor that executes the hybrid SQL script can determine the number and name of the out-library operation unit that runs the code segment through these execution parameters, and whether it needs to be run separately, and can also determine the database in which the code is executed and the execution conditions and other information.

[0095] In one or more embodiments of the present invention, for the code segment of the out-of-library operation, the data processing personnel can set the command type of the out-of-library operation and the parameter values ​​of each parameter in the command type according to the preset data exchange format. For example, if JSON is used as the preset data exchange format, the code type of the code segment of the out-of-library operation is JSON. Then the data processing personnel need to set the command type of the out-of-library operation and each parameter in the command type and the corresponding parameter values ​​into a JSON statement according to the format requirements of the JSON statement to form the code segment of the out-of-library operation. Therefore, when executing, the code segment can be parsed according to the code type to obtain the parameter value, and the parameter value obtained by the parsing can be transmitted to the out-of-library operation unit for processing, so as to achieve the purpose of calling the out-of-library operation unit to perform the corresponding out-of-library operation. Similarly, the execution parameters of the code segment of the out-of-library operation may include at least one of the parameters such as the code number and the code name.

[0096] In addition, the execution parameters of the out-of-library operation can also be set in the same way as the execution parameters of the in-library operation, that is, the execution parameters of the out-of-library operation also include at least one of the parameters such as code number, code name, execution mode (separate execution or overall execution), execution statement type (SQL statement execution or stored procedure statement execution), database name and database type. Among them, for the code segment whose code type is out-of-library operation, the execution parameters that are not used during execution can be not set or pre-set as default parameters to ensure the overall consistency of the SQL file and improve the generation efficiency of the hybrid script.

[0097] In this preferred embodiment, the description information can be in the form of comments, that is, the description information is set as a pseudo-comment of the code segment in the header of the corresponding code segment. It can be understood that in the prior art, SQL files generally set a method for expressing comments. When the SQL file is executed, the commented part is not executed as an SQL statement, but is usually provided to programmers or users as a code explanation. Therefore, the code execution information of each code segment is set in the form of comments, and the necessary code execution information is provided to the executor without affecting the operation of the code segment, so that multiple code segments can be set in one SQL file.

[0098] In a specific example, the preset identifiers include NO (code number), NAME (code name), SEP (execution mode), CALL (execution statement type), ds (database name), format (code type) and filter (database type). According to the code execution information, the parameter values ​​corresponding to NO, NAME, SEP, CALL, ds, format and filter are determined to be 4, write to the hit model result table, YES, NO, ap, sql and oracle respectively. Then, the description content information is obtained according to the preset identifiers and the corresponding parameter values:

[0099] NO:4NAME: Write hit model result table SEP:YES CALL:NO

[0100] ds:ap format:sql,filter:oracle

[0101] SQL files generally use "--" as a method of expressing line comments. Therefore, the description information can be formed according to the above description content information and the preset comment format symbol "--", as follows:

[0102] --NO:4NAME: Write hit model result table SEP:YES CALL:NO

[0103] --ds:ap format:sql filter:oracle

[0104] Among them, NO means that the operation order of this code segment is 4; NAME: represents the name of this code segment; SEP: takes the value of YES or NO, indicating that if this module contains multiple SQL statements, YES means executing them separately, and NO means executing them as a whole; CALL: takes the value of YES or NO, YES means that this module is executed using ordinary SQL statements, and NO means that this module is executed using stored procedure statements. ds: represents the name of the database to be executed. As an identifier that can distinguish different databases, this identifier can be used to obtain the connection information of the corresponding database (such as URL, user name, password, etc.). The database connection information can be pre-configured in the executor that executes the mixed SQL script. format: takes the value of sql or json, which identifies the code type of the code segment as SQL or JSON format. SQL is completed by the database for in-library operations, and JSON is interpreted by the executor for out-library operations. filter: can take the value of database type or all, which is used to be compatible with the conditional options of multiple databases. The following code segment will only be run if the current database type is the database type matched by the filter, otherwise it will be skipped and not executed. all corresponds to that all database systems can adapt to the following code segment.

[0105] In this preferred embodiment, when the hybrid SQL script only needs to be executed in one database, the description information is set in comment format. When a single database is executed, there is no need for an executor to parse the file. The hybrid SQL script can be directly sent to the database for execution. The description information set in the comment will not affect the execution of the database, thereby improving the flexibility of the hybrid SQL script execution and improving data processing efficiency.

[0106] In a preferred embodiment, Figure 4 As shown, the description information is an identification string. The step S200 forms description information of each code segment according to the code execution information and the preset identification symbol, specifically including:

[0107] S221: Determine a parameter value corresponding to the preset identification symbol according to the code execution information.

[0108] S222: Obtain description content information according to the preset identification symbol and the corresponding parameter value.

[0109] S223: Forming the description information according to the description content information and a preset identification string format symbol.

[0110] Specifically, it can be understood that the description information can also be set in the form of an identification string at the head of the corresponding code segment in the hybrid SQL script. For the above specific example, the preset identification symbols include NO (code number), NAME (code name), SEP (execution mode), CALL (execution statement type), ds (database name), format (code type) and filter (database type). According to the code execution information, the parameter values ​​corresponding to NO, NAME, SEP, CALL, ds, format and filter are determined to be 4, write to the hit model result table, YES, NO, ap, sql and oracle respectively. The description content information is obtained according to the preset identification symbols and the corresponding parameter values:

[0111] NO:4NAME: Write hit model result table SEP:YES CALL:NO

[0112] ds:ap format:sql filter:oracle

[0113] The preset identification string format symbol of the identification string can be "[]", of course, in other implementations, other symbols can also be used. Therefore, the description information can be formed according to the above description content information and the preset annotation format symbol "[]", as follows:

[0114] [NO=4,NAME=write hit model result table,SEP=YES,CALL=NO]

[0115] [ds=ap,format=sql,filter=oracle].

[0116] In this preferred embodiment, after acquiring the description information, the executor can parse the description information according to the formal method of the description information to obtain the description content information, and parse the description content information according to the formal method of the description content information to obtain the execution parameters. Further, according to the execution parameters obtained by parsing, the execution operation of the code segment is performed in the library or outside the library.

[0117] In a preferred embodiment, Figure 5 As shown, the description information includes comments and identification strings. The step S200 forms description information for each code segment according to the code execution information and the preset identification symbol, specifically including:

[0118] S231: Determine parameter values ​​corresponding to preset identification symbols of the annotation and identification string according to the code execution information.

[0119] S232: Obtain description content information according to the preset identification symbol and the corresponding parameter value.

[0120] S233: forming the description information in the form of annotation according to the description content information and preset annotation format symbols, and forming the description information in the form of identification string according to the description content information and preset identification string format symbols.

[0121] Specifically, it is understood that the description information can also be expressed in the form of a mixture of comments and identification strings. For example, for the above specific example, the description content information of NO, NAME, SEP and CALL can be set in the form of comments, and the description content information of the filter of ds and format can be set in the form of identification strings, so that the obtained description information is:

[0122] --NO:4NAME: Write hit model result table SEP:YES CALL:NO

[0123] [ds=ap,format=sql,filter=oracle].

[0124] In a preferred embodiment, the code execution information includes the code type, and if the code type is an out-of-library operation, the code segment includes the command type, parameters and corresponding parameter values ​​set in a preset data exchange format.

[0125] Specifically, it can be understood that for the out-of-library operation logic code that needs to be called to execute by the out-of-library operation unit, the relevant parameters and parameter values ​​of the out-of-library operation need to be transmitted to the out-of-library operation unit. In order to set the out-of-library operation logic code and the in-library operation in one script file, the present invention sets a preset data exchange format, and the data processing personnel sets the relevant parameters and parameter values ​​of the out-of-library operation according to the preset data exchange format to form a code segment. Therefore, when executing, the code segment can be parsed according to the code type to obtain the parameter value, and the parameter value obtained by parsing is transmitted to the out-of-library operation unit for processing, so as to achieve the purpose of calling the out-of-library operation unit to perform the corresponding out-of-library operation.

[0126] Therefore, for the code segment of the out-of-library operation, the data processing personnel sets the command type of the out-of-library operation and the parameter values ​​of each parameter in the command type according to the preset data exchange format. For example, if JSON is used as the preset data exchange format, the code type of the code segment of the out-of-library operation is JSON. Then the data processing personnel need to set the command type of the out-of-library operation and each parameter in the command type and the corresponding parameter values ​​into a JSON statement according to the format requirements of the JSON statement to form the code segment of the out-of-library operation. Of course, in actual applications, the preset data exchange format can also adopt XML, K-VALUE or INI format, and the present invention is not limited to this.

[0127] In a specific example, since SQL statements cannot be used to represent the external operation logic, different external operation logics need to be identified in the script file using JSON. In this specific example, the description information and code segments in JSON format are:

[0128] --NO:8NAME: Synchronize result table to TP library SEP:YES CALL:NO

[0129] [ds=,format=json,filter=all]

[0130]

[0131] Among them, cmd: is the command type, which identifies different types of commands. In this example, the command is dbTransfer, which means to realize cross-database data transfer. From, to, and tableName respectively represent the parameters of the command. Different commands can correspond to different parameters. In this example, the parameter values ​​corresponding to From, to, and tableName are "ap", "tp", and "["BD_SUS_HIT_MODEL", "BD_SUS_HIT_MODEL_T"]". Therefore, by setting different commands, the executor forms an extensible interface for off-database operations, and can improve the off-database logical operation capability of the executor by continuously expanding commands and off-database operation units with different functions.

[0132] In a preferred embodiment, Figure 6 As shown, the step S300 sequentially arranges the multiple code segments and the corresponding description information according to a preset execution order to form a hybrid SQL script, specifically including:

[0133] S310: Setting a separator for each code segment and the description information corresponding to the code segment.

[0134] S320: For each code segment, forming an execution unit in the order of the separator, the description information, and the code segment.

[0135] S330: Arrange all execution units in sequence according to a preset execution order to obtain the hybrid SQL script.

[0136] Specifically, it can be understood that when multiple code segments for in-library operations and out-library operations are set in the same mixed SQL script, the executor needs to extract the multiple code segments separately and execute them separately during parsing and execution. Therefore, in order to facilitate the executor to parse each independent code segment, a separator can be preset to distinguish the current code segment from the previous and next code segments. Specifically, in this preferred embodiment, separators and description information are set in sequence at the headers of different code segments to form an independent execution unit, and multiple execution units are arranged in sequence according to the execution order to form a mixed SQL script. For example, in a specific example, in order not to destroy the overall file format characteristics based on SQL files, the separator can be in the following comment form:

[0137] --###[BRAINS]###

[0138] Of course, in other implementations, other forms of separators may also be used, and the present invention is not limited to this.

[0139] The mixed SQL script formed by using the above separators may be, for example:

[0140] --###[BRAINS]###

[0141] --NO:1NAME: Check and create temporary table SEP:YES CALL:NO

[0142] [ds=ap,format=sql,filter=oracle]

[0143] CREATE TABLE BD_SUS_HIT_MODEL_tmp_001as SELECT*FROM BD_SUS_HIT_MODELwhere 1=0nologging;

[0144] --###[BRAINS]###

[0145] --NO:2NAME: Write hit model temporary table SEP:YES CALL:NO

[0146] [ds=tp,format=sql,filter=hive]

[0147] insert into BD_SUS_HIT_MODEL_tmp_001

[0148] select * from tmp_hive_tab;

[0149] delete from tmp_hive_tab where data_date=${BATCH_DATE};

[0150] --###[BRAINS]###

[0151] --NO:3NAME: Write hit model result table SEP:YES CALL:NO

[0152] [ds=ap,format=sql,filter=oracle]

[0153] insert into BD_SUS_HIT_MODEL

[0154] (data_date,batch_id,model_oid,obj_type,cst_type,cst_no,hit_score,hit_count,

[0155] hit_rule_str,cfg_model_score,cfg_least_hit_cnt,cfg_score_count_logic,update_time)

[0156] SELECT * from bd_sus_tmp;

[0157] --###[BRAINS]###

[0158] --NO:4NAME: Synchronize result table to TP library SEP:YES CALL:NO

[0159] [ds=,format=json,filter=all]

[0160]

[0161] The present invention provides a hybrid SQL script. The hybrid SQL script can support multiple SQL statements of multiple homogeneous or heterogeneous database systems, and support adding the representation code of the out-of-library operation unit to the script, and can support the SQL operation of multiple databases and the operation logic of the out-of-library operation unit in a single file. Specifically, (1) the transformation from multiple SQL files to a single SQL script enhances the flexibility of multi-library operations: the unit of database execution is changed from a separate SQL file to a code block, each code block can be executed on a single database system, and the execution order of each code is directly written in the script file without the need to be controlled in the executor, which greatly enhances the flexibility of multi-library operations. (2) The complexity of hybrid operations of out-of-library and in-library operations is simplified: the out-of-library operations are embedded in the script using a unified JSON text, which can achieve seamless connection with the in-library SQL. The in-library and out-of-library logic can also easily realize integrated operations based on data through contextual relationships and shared libraries. (3) The complexity of cross-library operations of heterogeneous databases is simplified: by configuring and specifying different database types in the script file, hybrid operations of different types of databases such as relational databases, big data platforms, and NOSQL can be supported. In summary, the present invention can effectively overcome the deficiencies in the prior art and provide a method for uniformly representing and executing multi-database SQL and out-of-database operations that is simple, applicable, flexible, diverse, and extensible.

[0162] Based on the same principle, the present invention also discloses a method for executing a hybrid SQL script file as described in this embodiment. Figure 7 As shown, in this embodiment, the method includes:

[0163] S400: Parsing the hybrid SQL script to obtain multiple code segments and corresponding description information.

[0164] S500: Determine code execution information corresponding to the description information of each code segment.

[0165] S600: Execute each code segment in sequence according to the execution order of the multiple code segments and the code execution information of each code segment.

[0166] In a preferred embodiment, Figure 8 As shown, S400 parses the hybrid SQL script to obtain multiple code segments and corresponding description information, specifically including:

[0167] S410: Identify delimiters in the mixed SQL script.

[0168] S420: Extract the execution unit corresponding to each separator in sequence.

[0169] S430: Parse each execution unit to obtain multiple code segments in an expected execution order and corresponding description information.

[0170] In a preferred embodiment, the code execution information corresponding to the description information of each code segment determined in S500 specifically includes:

[0171] The description information of each code segment is parsed according to a preset identification symbol to obtain code execution information of the code segment, where the code execution information includes a code type, execution parameters, and corresponding parameter values.

[0172] In a preferred embodiment, the step S600 sequentially executing each code segment according to the execution order of the multiple code segments and the code execution information of each code segment specifically includes:

[0173] Obtain and execute each code segment in sequence according to the execution order, wherein executing the code segment includes the following steps:

[0174] determining a code type of the code segment according to the code execution information of each code segment;

[0175] If the code type is in-library operation, the code segment is sent to a corresponding database for in-library operation according to the parameter value of the execution parameter;

[0176] If the code type is an off-library operation, the code segment is parsed according to a preset data exchange format to obtain the command type, the parameters in the command type and the corresponding parameter values, and the parameters and the corresponding parameter values ​​are transmitted to the off-library operation unit corresponding to the command type for off-library operation.

[0177] Based on the same principle, the present invention also discloses a hybrid SQL script file generation device. Fig. 9 As shown, in this embodiment, the device includes an information acquisition module 11, a description information setting module 12 and a script generation module 13.

[0178] The information acquisition module 11 is used to determine a plurality of code segments to be executed and code execution information.

[0179] The description information setting module 12 is used to form description information of each code segment according to the code execution information and a preset identification symbol.

[0180] The script generation module 13 is used to sequentially arrange the multiple code segments and corresponding description information according to a preset execution order to form a hybrid SQL script.

[0181] Since the principle of solving the problem by the device is similar to that of the above method, the implementation of the device can refer to the implementation of the method and will not be repeated here.

[0182] Based on the same principle, the present invention also discloses an execution device of a hybrid SQL script file as described in this embodiment. Fig.10As shown, in this embodiment, the device includes a script parsing module 21, a description information parsing module 22 and a script execution module 23.

[0183] The script parsing module 11 is used to parse the hybrid SQL script to obtain multiple code segments and corresponding description information.

[0184] The description information parsing module 12 is used to determine the code execution information corresponding to the description information of each code segment.

[0185] The script execution module 13 is used to execute each code segment in sequence according to the execution order of the multiple code segments and the code execution information of each code segment.

[0186] Since the principle of solving the problem by the device is similar to that of the above method, the implementation of the device can refer to the implementation of the method and will not be repeated here.

[0187] The systems, devices, modules or units described in the above embodiments may be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer device, and specifically, the computer device may be, for example, a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email device, a game console, a tablet computer, a wearable device, or a combination of any of these devices.

[0188] In a typical example, the computer device specifically includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, the method executed by the client as described above is implemented; or, when the processor executes the program, the method executed by the server as described above is implemented.

[0189] Reference below Fig.11 , which shows a schematic diagram of the structure of a computer device 600 suitable for implementing an embodiment of the present application.

[0190] like Fig.11 As shown, the computer device 600 includes a central processing unit (CPU) 601, which can perform various appropriate operations and processes according to a program stored in a read-only memory (ROM) 602 or a program loaded from a storage part 608 to a random access memory (RAM) 603. In the RAM 603, various programs and data required for the operation of the system 600 are also stored. The CPU 601, the ROM 602, and the RAM 603 are connected to each other via a bus 604. An input / output (I / O) interface 605 is also connected to the bus 604.

[0191] The following components are connected to the I / O interface 605: an input section 606 including a keyboard, a mouse, etc.; an output section 607 including a cathode ray tube (CRT), a liquid crystal feedback device (LCD), etc., and a speaker, etc.; a storage section 608 including a hard disk, etc.; and a communication section 609 including a network interface card such as a LAN card, a modem, etc. The communication section 609 performs communication processing via a network such as the Internet. A drive 610 is also connected to the I / O interface 606 as needed. A removable medium 611, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 610 as needed, so that a computer program read therefrom is installed as needed as the storage section 608.

[0192] In particular, according to an embodiment of the present invention, the process described above with reference to the flowchart can be implemented as a computer software program. For example, an embodiment of the present invention includes a computer program product, which includes a computer program tangibly contained on a machine-readable medium, and the computer program includes program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through the communication part 609, and / or installed from the removable medium 611.

[0193] Computer readable media include permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. Information can be computer readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disk read-only memory (CD-ROM), digital versatile disk (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include temporary computer readable media (transitory media), such as modulated data signals and carrier waves.

[0194] For the convenience of description, the above device is described in terms of functions and is described separately in various units. Of course, when implementing the present application, the functions of each unit can be implemented in the same or multiple software and / or hardware.

[0195] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a 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 generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0196] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0197] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0198] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0199] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0200] The present application may be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application may also be practiced in distributed computing environments where tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules may be located in local and remote computer storage media, including storage devices.

[0201] Each embodiment in this specification 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 system 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.

[0202] The above is only an embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the claims of the present application.

Claims

1. A hybrid SQL script file generation method, characterized in that: include: Determine multiple code segments to be executed and code execution information, the code execution information including code type, execution parameters corresponding to different code types and corresponding parameter values, wherein the code type includes in-library operation and out-library operation; when the code type is in-library operation, the code segment includes at least one SQL statement, and the execution parameters include at least one of a code number, a code name, an execution mode, an execution statement type, a database name and a database type parameter; when the code type is out-library operation, the code segment includes a command type, a parameter and a corresponding parameter value set in a preset data exchange format, and the execution parameters include at least one of a code number, a code name, an execution mode, an execution statement type, a database name and a database type parameter; Forming description information of each code segment according to the code execution information and a preset identification symbol, wherein the description information is a comment and / or an identification string; The multiple code segments and corresponding description information are sequentially arranged according to a preset execution order to form a hybrid SQL script.

2. The hybrid SQL script file generation method according to claim 1, characterized in that: The description information is a comment; The description information of each code segment formed according to the code execution information and the preset identification symbol specifically includes: Determine the parameter value corresponding to the preset identification symbol according to the code execution information; Obtaining description content information according to the preset identification symbol and the corresponding parameter value; The description information is formed according to the description content information and preset annotation format symbols.

3. The hybrid SQL script file generation method according to claim 1, characterized in that: The description information is an identification string; The description information of each code segment formed according to the code execution information and the preset identification symbol specifically includes: Determine the parameter value corresponding to the preset identification symbol according to the code execution information; Obtaining description content information according to the preset identification symbol and the corresponding parameter value; The description information is formed according to the description content information and a preset identification string format symbol.

4. The hybrid SQL script file generation method according to claim 1, characterized in that: The description information includes comments and identification strings; The description information of each code segment formed according to the code execution information and the preset identification symbol specifically includes: Determine, according to the code execution information, parameter values ​​corresponding to the preset identification symbols of the annotation and identification string; Obtaining description content information according to the preset identification symbol and the corresponding parameter value; The description information in the form of annotation is formed according to the description content information and preset annotation format symbols, and the description information in the form of identification string is formed according to the description content information and preset identification string format symbols.

5. The hybrid SQL script file generation method according to claim 1, characterized in that: The step of sequentially arranging the multiple code segments and the corresponding description information according to a preset execution order to form a hybrid SQL script specifically includes: Set a separator for each code segment and the corresponding description information of the code segment; For each code segment, an execution unit is formed according to the order of the separator, the description information and the code segment; All execution units are arranged in sequence according to a preset execution order to obtain the hybrid SQL script.

6. A method for executing a hybrid SQL script file, characterized in that: include: Parsing the hybrid SQL script to obtain multiple code segments and corresponding description information, wherein the description information is comments and / or identification strings; Determine the code execution information corresponding to the description information of each code segment, the code execution information including the code type, execution parameters corresponding to different code types and corresponding parameter values, wherein the code type includes in-library operation and out-library operation, when the code type is in-library operation, the code segment includes at least one SQL statement, the execution parameters include at least one of a code number, a code name, an execution mode, an execution statement type, a database name and a database type parameter; when the code type is out-library operation, the code segment includes a command type, a parameter and a corresponding parameter value set in a preset data exchange format, and the execution parameters include at least one of a code number, a code name, an execution mode, an execution statement type, a database name and a database type parameter; The code segments are executed in sequence according to the execution order of the multiple code segments and the code execution information of each code segment.

7. The method for executing a hybrid SQL script file according to claim 6, characterized in that: Parsing the hybrid SQL script to obtain multiple code segments and corresponding description information specifically includes: Identify delimiters in the hybrid SQL script; Extract the execution unit corresponding to each separator in turn; Each execution unit is parsed to obtain a plurality of code segments in an expected execution order and corresponding description information.

8. The method for executing a hybrid SQL script file according to claim 6, characterized in that: The code execution information corresponding to the description information of each code segment is determined to specifically include: The description information of each code segment is parsed according to a preset identification symbol to obtain code execution information of the code segment, wherein the code execution information includes a code type, execution parameters corresponding to different code types, and corresponding parameter values.

9. The method for executing a hybrid SQL script file according to claim 6, characterized in that: The executing of each code segment in sequence according to the execution order of the multiple code segments and the code execution information of each code segment specifically includes: Obtain and execute each code segment in sequence according to the execution order, wherein executing the code segment includes the following steps: determining a code type of the code segment according to the code execution information of each code segment; If the code type is in-library operation, the code segment is sent to a corresponding database for in-library operation according to the parameter value of the execution parameter; If the code type is an off-library operation, the code segment is parsed according to a preset data exchange format to obtain the command type, the parameters in the command type and the corresponding parameter values, and the parameters and the corresponding parameter values ​​are transmitted to the off-library operation unit corresponding to the command type for off-library operation.

10. A hybrid SQL script file generating device, characterized in that: include: An information acquisition module, used to determine multiple code segments to be executed and code execution information, wherein the code execution information includes code type, execution parameters corresponding to different code types, and corresponding parameter values, wherein the code type includes in-library operation and out-library operation; when the code type is in-library operation, the code segment includes at least one SQL statement, and the execution parameters include at least one of a code number, a code name, an execution mode, an execution statement type, a database name, and a database type parameter; when the code type is out-library operation, the code segment includes a command type, a parameter, and a corresponding parameter value set in a preset data exchange format, and the execution parameters include at least one of a code number, a code name, an execution mode, an execution statement type, a database name, and a database type parameter; A description information setting module, used to form description information of each code segment according to the code execution information and a preset identification symbol, wherein the description information is a comment and / or an identification string; The script generation module is used to sequentially arrange the multiple code segments and corresponding description information according to a preset execution order to form a hybrid SQL script.

11. A hybrid SQL script file execution device, characterized in that: include: A script parsing module, used for parsing the hybrid SQL script to obtain multiple code segments and corresponding description information, wherein the description information is comments and / or identification strings; A description information parsing module is used to determine the code execution information corresponding to the description information of each code segment, wherein the code execution information includes the code type, execution parameters corresponding to different code types, and corresponding parameter values, wherein the code type includes in-library operation and out-library operation, when the code type is in-library operation, the code segment includes at least one SQL statement, and the execution parameters include at least one of a code number, a code name, an execution mode, an execution statement type, a database name, and a database type parameter; when the code type is an out-library operation, the code segment includes a command type, a parameter, and a corresponding parameter value set in a preset data exchange format, and the execution parameters include at least one of a code number, a code name, an execution mode, an execution statement type, a database name, and a database type parameter; The script execution module is used to execute each code segment in sequence according to the execution order of multiple code segments and the code execution information of each code segment.

12. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the program, the method according to any one of claims 1 to 9 is implemented.

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

Images