CN113934748A

CN113934748A - Mixed type SQL script file generation method, execution method and device

Info

Publication number: CN113934748A
Application number: CN202111197771.0A
Authority: CN
Inventors: 杜海亮
Original assignee: Industrial and Commercial Bank of China Ltd ICBC; ICBC Technology Co Ltd
Current assignee: Industrial and Commercial Bank of China Ltd ICBC; ICBC Technology Co Ltd
Priority date: 2021-10-14
Filing date: 2021-10-14
Publication date: 2022-01-14

Abstract

The invention provides a generating method, an executing method and a device of a mixed type SQL script file, which can be used in the technical field of finance, wherein the method comprises the steps of determining a plurality of code segments to be executed and code executing information; forming description information of each code segment according to the code execution information and a preset identification symbol; the plurality of code segments and the corresponding description information are sequentially arranged according to the preset execution sequence to form the hybrid SQL script.

Description

Mixed type SQL script file generation method, execution method and device

Technical Field

The invention relates to the technical field of internet, in particular to the technical field of finance, and particularly relates to a method and a device for generating a hybrid SQL script file.

Background

In an information system with data processing as a center, usually, a data processing person will compile a plurality of SQL files related to a database, and then the SQL files are interpreted and executed by a tool provided by a database system, different database systems respectively provide different executors for execution of the SQL files, and formats of the SQL files of different databases are different. For example, Oracle offers sqlplus to execute its PL/SQL files, hive offers beeline to execute the SQL files specific to big data. When an application system needs to access a plurality of libraries simultaneously, and SQL instructions need to be executed across the plurality of libraries, the traditional single SQL file is difficult to satisfy. In addition, data interaction is needed among a plurality of libraries, or data needs to be operated outside the libraries, and a traditional SQL file cannot be expressed and can be realized only by compiling an additional application program. Under the complex scenes, the traditional SQL file and the professional executor thereof cannot meet the flexible expansion requirement of business operation.

Disclosure of Invention

The invention aims to provide a hybrid SQL script file generation method, which provides a script capable of operating in a heterogeneous database and outside the database and improves 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.

In order to achieve the above object, the present invention discloses a method for generating a hybrid SQL script file, which comprises:

determining a plurality of code segments to be executed and code execution information;

forming description information of each code segment according to the code execution information and a preset identification symbol;

and sequentially setting the plurality of code segments and the corresponding description information according to a preset execution sequence to form a hybrid SQL script.

Preferably, the description information is an annotation;

the forming of the description information of each code segment according to the code execution information and the preset identifier specifically includes:

determining a parameter value corresponding to the preset identifier according to the code execution information;

obtaining explanatory content information according to the preset identification symbols and the corresponding parameter values;

and forming the description information according to the description content information and a preset annotation format symbol.

Preferably, the description information is an identification string;

and forming the description information according to the description content information and a preset identification string format symbol.

Preferably, the description information includes a comment and an identification string;

determining parameter values corresponding to preset identification symbols of the annotation and the identification string according to the code execution information;

and forming explanatory information of the annotation form according to the explanatory content information and a preset annotation format symbol, and forming explanatory information of the identification string form according to the explanatory content information and a preset identification string format symbol.

Preferably, the code execution information includes a code type, and if the code type is an out-of-library operation;

the code segment comprises a command type, a parameter and a corresponding parameter value which are set by a preset data exchange format.

Preferably, the step of sequentially setting the plurality of code segments and the corresponding description information according to a preset execution sequence to form the hybrid SQL script specifically includes:

setting separators for each code segment and the description information corresponding to the code segment;

forming an execution unit for each code segment in the order of the delimiter, the specification information and the code segment;

and sequentially arranging all execution units according to a preset execution sequence to obtain the hybrid SQL script.

The invention also discloses an execution method of the hybrid SQL script file, which comprises the following steps:

analyzing the mixed SQL script to obtain a plurality of code segments and corresponding description information;

determining code execution information corresponding to the description information of each code segment;

and executing the code sections in sequence according to the execution sequence of the code sections and the code execution information of each code section.

Preferably, the analyzing the hybrid SQL script to obtain the plurality of code segments and the corresponding description information specifically includes:

identifying a separator in the hybrid SQL script;

sequentially extracting the execution units corresponding to each separator;

and analyzing each execution unit to obtain a plurality of code segments of the expected execution sequence and corresponding description information.

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

and analyzing the description information of each code segment according to a preset identifier to obtain code execution information of the code segment, wherein the code execution information comprises a code type, an execution parameter and a corresponding parameter value.

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

sequentially acquiring and executing each code segment according to the execution sequence, wherein the execution code segment comprises the following steps:

determining code types of the code segments 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 the out-of-library operation, analyzing the code segment according to a preset data exchange format to obtain a command type, each parameter in the command type and a corresponding parameter value, and transmitting the parameter and the corresponding parameter value to an out-of-library operation unit corresponding to the command type to perform the out-of-library operation.

The invention also discloses a device for generating the mixed SQL script file, which comprises the following components:

the information acquisition module is used for determining a plurality of code segments to be executed and code execution information;

the description information setting module is used for forming description information of each code segment according to the code execution information and a preset identification symbol;

and the script generation module is used for sequentially setting the code segments and the corresponding description information according to a preset execution sequence to form a mixed type SQL script.

The invention also discloses an executing device of the hybrid SQL script file, which comprises the following components:

the script analysis module is used for analyzing the mixed SQL script to obtain a plurality of code segments and corresponding description information;

the description information analysis module is used for determining code execution information corresponding to the description information of each code segment;

and the script execution module is used for sequentially executing the code segments according to the execution sequence of the code segments and the code execution information of each code segment.

The invention also discloses a computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor,

the processor, when executing the program, implements the method as described above.

The invention also discloses a computer-readable medium, having stored thereon a computer program,

which when executed by a processor implements the method as described above.

The hybrid SQL script file generation method determines a plurality of code segments to be executed and code execution information; forming description information of each code segment according to the code execution information and a preset identification symbol; and sequentially setting the plurality of code segments and the corresponding description information according to a preset execution sequence to form a hybrid SQL script. Therefore, the hybrid SQL script generated by the invention sets a plurality of code segments in one file, and sets the description information for each code segment, so that when the hybrid SQL script is executed, each code segment is extracted through the description information, the description information corresponding to each code segment can be analyzed, and the code execution information of each code segment obtained through analysis is correspondingly operated. Therefore, the hybrid SQL script of the invention allows the code segments of the in-library operation and the code segments of the out-library operation of a plurality of different databases to be placed in a file, sets the analysis and execution modes of the code segments through the form of the description information, and calls the database executors or the out-library operation units corresponding to the code segments through the description information to process the code segments. The invention arranges a plurality of files of in-library operation and out-library operation in one script, supports multi-library operation of isomorphic databases or heterogeneous databases, and also supports mixed operation of in-library and out-library, thereby improving the efficiency and flexibility of data processing.

Drawings

In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments of the present invention, and for those skilled in the art, other drawings can be obtained according to the drawings without creative efforts.

FIG. 1 is a diagram illustrating the execution of a plurality of SQL files and off-library operation logic in the prior art;

FIG. 2 is a flowchart illustrating a method for generating a hybrid SQL script file according to a specific embodiment of the present invention;

FIG. 3 is a flowchart of a hybrid SQL script file generation method S200 according to a specific embodiment of the present invention;

FIG. 4 is a second flowchart of a hybrid SQL script file generation method S200 according to a specific embodiment of the present invention;

FIG. 5 is a third flowchart of a hybrid SQL script file generation method S200 according to a specific embodiment of the present invention;

FIG. 6 is a flowchart of a specific embodiment S300 of the method for generating a hybrid SQL script file according to the invention;

FIG. 7 is a flowchart illustrating a method of executing a hybrid SQL script file according to a specific embodiment of the invention;

FIG. 8 is a flowchart of a specific embodiment S400 of a method for executing a hybrid SQL script file according to the invention;

FIG. 9 is a block diagram illustrating an exemplary embodiment of a hybrid SQL script file generating device according to the invention;

FIG. 10 is a block diagram illustrating an exemplary embodiment of an apparatus for executing a hybrid SQL script file according to the invention;

FIG. 11 illustrates a schematic block diagram of a computer device suitable for use in implementing embodiments of the present invention.

Detailed Description

The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.

It should be noted that the hybrid SQL script file generation method and apparatus disclosed in the present application may be used in the field of financial technology, and may also be used in any field other than the field of financial technology.

In one or more embodiments of the invention, an SQL file refers to a text file compiled using a database Structured Query Language (SQL) grammar, which is usually suffixed with a.sql.

In one or more embodiments of the present invention, the extra-library operation refers to a part of business logic that is not run in the database system but is run in a host outside the database in a system that uses the database as an operation center, but the part of logic is usually related to the operation of the database, and the part of operation that is not in the database is referred to as extra-library operation.

In one or more embodiments of the invention, an SQL file executor is a tool provided by a database manufacturer that can directly execute a single SQL file, and the SQL file executed by such a tool is generally applicable only to a specific database system.

In one or more embodiments of the invention, a homogeneous database means that there are multiple database systems in one application system, and each database system is of the same type, like Oracle or Mysql.

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

In one or more embodiments of the present invention, a hybrid SQL script file refers to a script file composed of code segments represented by a plurality of database system internal operation and/or external operation logics, and the script file includes a plurality of data system SQL code segments and some external operation logics in a single file, and when executed, the script file is sequentially executed in a plurality of database systems and external hosts, thereby achieving the purpose of integrally completing a certain operation task. The plurality of database systems may be heterogeneous database systems or a plurality of homogeneous database systems.

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, and may parse the hybrid SQL script file and sequentially execute each code segment in the script.

In the prior art, when data processing includes SQL files that need to be executed in multiple databases and/or logic files that operate outside the databases, where each SQL file needs to be executed by using a corresponding SQL actuator, the SQL files of the heterogeneous databases need to be executed by different SQL actuators, and the logic files of the off-library operations need to be executed by corresponding off-library operation programs. Thus, as shown in fig. 1, for a data processing file including a plurality of SQL files and logic files, it is generally necessary for a dedicated job scheduler to execute each SQL file and call out an out-of-library operation program to execute a complete data processing job in a predetermined order, that is, the job scheduler sequentially processes files such as 1.SQL,2.SQL, logic files 1, 3.SQL, and logic file 2 in a predetermined order by SQL executors or call out-of-library operation units of each database (DB1, DB 2). The SQL file is executed by a command line execution tool corresponding to different databases, such as SQL plus provided by Oracle, Mysql provided by Mysql, beeline provided by HIVE, and so on.

It should be noted that, an SQL file, such as 1.SQL,2.SQL shown in fig. 1, includes a series of SQL instructions, including a DDL instruction and a DML instruction, different instructions are divided by a semicolon (;) or other database-specific dividers, and when executed, each SQL instruction is executed in sequence. An SQL file is usually executed by a database system, and its instructions usually support only one type of database system, for example, an SQL file using the syntax of PL/SQL can only run in Oracle or an Oracle-compatible database system.

The off-library operation unit includes off-library operation programs for completing specific tasks, such as file parsing and warehousing, inter-library data transmission, and the like, and cannot be implemented using an SQL file but needs to be implemented using a dedicated program language, such as Java, Python, and other statements.

Thus, scenarios of script execution in the prior art may include the following: (1) a plurality of isomorphic databases exist in an application system and a scene that data operation needs to be performed in the plurality of databases in an interpenetration mode, namely a plurality of SQL actuators are needed to execute scripts; (2) the method comprises the following steps that 1 or more isomorphic databases exist in an application system, and a scene of combining in-library operation and out-library operation is needed, namely, one or more SQL actuators and out-library operation units are needed to execute scripts; (3) a plurality of heterogeneous databases exist in an application system, and a scene that data operation needs to be performed in the databases in an interpenetration mode is needed, namely SQL executors of a plurality of different databases are needed to execute scripts; (4) a plurality of heterogeneous databases exist in an application system, and a scenario combining in-library operation and out-library operation is required, that is, a plurality of SQL executors of different databases and out-library operation unit execution scripts are required.

In summary, in the scenario of hybrid operation of multiple database SQL files and out-of-library operations, the main disadvantages of the prior art scheme are as follows: (1) because the SQL file can only be used specially, the mixed operation of a plurality of SQL files is not flexible: the operation sequence of a plurality of database systems can only be based on SQL files as a unit, thus, only a plurality of SQL files can be set for the application which needs more front and back dependency relationship, the operation in the database contained in each file becomes very small, the complexity of scheduling is increased, and the management of the SQL files is not flexible. (2) The out-of-library operation unit and the in-library SQL file execution unit cannot be effectively fused: because the operation in the external operation unit and the SQL file is completely independent, the two operations can not be effectively fused, and a special job scheduler is needed to control the operation process.

According to one aspect of the invention, the embodiment discloses a method for generating a hybrid SQL script file.

As shown in fig. 2, in this embodiment, the method includes:

s100: a plurality of code segments to be executed and code execution information are determined.

S200: and forming the description information of each code segment according to the code execution information and the preset identification symbol.

S300: and sequentially setting the plurality of code segments and the corresponding description information according to a preset execution sequence to form a hybrid SQL script.

In a preferred embodiment, the explanatory information is a comment, as shown in fig. 3. The step S200 of forming the instruction information of each code segment according to the code execution information and the preset identifier specifically includes:

s211: and determining a parameter value corresponding to the preset identifier according to the code execution information.

S212: and obtaining the explanatory content information according to the preset identifier and the corresponding parameter value.

S213: and forming the description information according to the description content information and a preset annotation format symbol.

Specifically, it is understood that the data processing personnel needs to pre-program the code segments to be executed and configure the code execution information of the code segments. The code execution information may include a code type (in-library operation or out-library operation), and execution parameters and corresponding parameter values corresponding to different code types.

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 parameter may include at least one of a code number, a code name, an execution manner (separate execution or overall execution), an execution statement type (SQL statement execution or stored procedure statement execution), a database name, and a database type. The executor executing the hybrid SQL script can determine the number, name, split operation and other information of the out-of-library operation unit of the operation code segment and determine the execution condition and other information in which database the code is executed.

In one or more embodiments of the invention, for a code segment of an out-of-library operation, a data processing person may set a command type of the out-of-library operation and a parameter value of each parameter in the command type according to a preset data exchange format. For example, if JSON is used as the predetermined data exchange format, the code type of the code segment for the out-of-library operation is JSON. The data processing personnel need to set the command type of the out-of-library operation, each parameter in the command type and the corresponding parameter value into a code segment of the out-of-library operation formed by the JSON statement according to the format requirement of the JSON statement. Therefore, when the method is executed, the code segment can be analyzed according to the code type to obtain the parameter value, and the parameter value obtained through analysis is transmitted to the out-of-library operation unit to be processed, so that the aim of calling the out-of-library operation unit to perform corresponding out-of-library operation is fulfilled. Similarly, the execution parameters of the code segment for the out-of-library operation may include at least one of a code number and a code name.

In addition, the execution parameters of the out-of-library operation may also be set the same 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 of code number, code name, execution mode (execution respectively or execution in whole), execution statement type (SQL statement execution or stored procedure statement execution), database name, and database type. For the code segment with the code type of out-of-library operation, the execution parameters which are not used in the execution can be set as default parameters in advance or not, so that the overall consistency of the SQL file is ensured, and the generation efficiency of the mixed script is improved.

In the preferred embodiment, the specification information may take the form of a comment, i.e. the specification information is provided as a pseudo comment for a code segment at the head of the corresponding code segment. It is understood that in the prior art, the SQL file generally sets a representation method of the annotation, and when the SQL file is executed, the part of the annotation is not executed as the SQL statement, and is usually provided to the programmer or user as a description interpretation of the code. Therefore, the code execution information of each code segment is set into a comment form, the necessary code execution information is provided for the executor while the operation of the code segment is not influenced, and the purpose that a plurality of code segments can be arranged in one SQL file is achieved.

In one embodiment, the default identifier includes NO (code number), NAME (code NAME), SEP (execution form), CALL (execution statement type), ds (database NAME), format (code type), and filter (database type). And determining that the corresponding parameter values of NO, NAME, SEP, CALL, ds, format and filter are 4 respectively, writing in a hit model result table, YES, NO, ap, sql and oracle according to the code execution information. Obtaining the description content information according to the preset identifier and the corresponding parameter value:

NO 4NAME write hit model results Table SEP YES CALL NO

ds:ap format:sql,filter:oracle

The SQL file generally uses "- -" as a method for representing a line annotation, so that the explanatory information can be formed according to the explanatory content information and a preset annotation format symbol "- -" as follows:

- -NO:4NAME write hit model result Table SEP: YES CALL: NO

--ds:ap format:sql filter:oracle

Wherein, NO represents that the operation sequence of the code segment is 4; NAME: the name representing this code segment; SEP: if the value is YES or NO, the module is indicated to contain a plurality of SQL sentences, YES represents separate execution, and NO represents overall execution; CALL: taking a value of YES or NO, YES indicating that the module is executed by using a common SQL statement, and NO indicating that the module is executed by using a statement of a stored procedure. ds: the name of the executed database is used as an identifier for distinguishing different databases, the identifier can be used for acquiring connection information (such as URL, user name, password and the like) of the corresponding database, and the connection information of the database can be configured in the executor executing the hybrid SQL script in advance. And (3) format: and (3) taking the value of SQL or JSON, identifying the code type of the code segment as SQL or JSON format, completing the in-library operation of SQL by the database, and completing the out-library operation of JSON by the interpretation of an actuator. A filter: the value can be taken as a database type or all to be used for compatible with conditional options of multiple databases, the following code segment is operated only if the type of the current database is the database type matched with the filter, otherwise, the execution is directly skipped, and all database systems corresponding to all the all can adapt to the following code segment.

In the preferred embodiment, when the hybrid SQL script is executed in only one database, the description information is set to be in the annotation format, and when a single database is executed, the hybrid SQL script can be directly sent to the database for execution without an executor analyzing a file, and the description information set in the annotation does not affect the execution of the database, thereby improving the execution flexibility of the hybrid SQL script and improving the data processing efficiency.

In a preferred embodiment, as shown in fig. 4, the description information is an identification string. The step S200 of forming the instruction information of each code segment according to the code execution information and the preset identifier specifically includes:

s221: and determining a parameter value corresponding to the preset identifier according to the code execution information.

S222: and obtaining the explanatory content information according to the preset identifier and the corresponding parameter value.

S223: and forming the description information according to the description content information and a preset identification string format symbol.

In particular, it is understood that the specification information may also be provided 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 identifier includes NO (code number), NAME (code NAME), SEP (execution type), CALL (execution statement type), ds (database NAME), format (code type), and filter (database type). And determining that the corresponding parameter values of NO, NAME, SEP, CALL, ds, format and filter are 4 respectively, writing in a hit model result table, YES, NO, ap, sql and oracle according to the code execution information. Obtaining the description content information according to the preset identifier and the corresponding parameter value:

NO 4NAME write hit model results Table SEP YES CALL NO

ds:ap format:sql filter:oracle

The preset identification string format symbol of the identification string may be in the form of "[ ]", although in other embodiments, other symbols may be used. Thus, the explanatory information can be formed from the explanatory content information and the preset annotation format symbol "[ ]", as follows:

[ NO-4, NAME-write hit model results table, SEP-YES, CALL-NO ]

[ds＝ap,format＝sql,filter＝oracle]。

In this preferred embodiment, after the executor acquires the description information, the description information may be analyzed according to a format method of the description information to obtain the description content information, and the execution parameter may be analyzed from the description content information according to a format method of the description content information. Further, the execution operation of the in-library operation or the out-library operation is performed on the code segment according to the execution parameters obtained by the analysis.

In a preferred embodiment, the explanatory information includes comments and identification strings, as shown in FIG. 5. The step S200 of forming the instruction information of each code segment according to the code execution information and the preset identifier specifically includes:

s231: and determining parameter values corresponding to the preset identification symbols of the annotation and the identification string according to the code execution information.

S232: and obtaining the explanatory content information according to the preset identifier and the corresponding parameter value.

S233: and forming explanatory information of the annotation form according to the explanatory content information and a preset annotation format symbol, and forming explanatory information of the identification string form according to the explanatory content information and a preset identification string format symbol.

In particular, it is to be understood that the descriptive information may also be represented in the form of a mixture of annotations and identification strings. For example, for the above specific example, the description content information of NO, NAME, SEP, and CALL may be set as a comment form, and the description content information of the filter of ds and format may be set as an identification string form, so that the obtained description information is:

- -NO:4NAME write hit model result Table SEP: YES CALL: NO

[ds＝ap,format＝sql,filter＝oracle]。

In a preferred embodiment, the code execution information includes a code type, and if the code type is an out-of-library operation. The code segment comprises a command type, a parameter and a corresponding parameter value which are set by a preset data exchange format.

Specifically, it can be understood that, for the out-of-library operation logic code that needs to be executed 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 logic code of the out-of-library operation and the in-library operation in a script file, the invention sets a preset data exchange format, and a data processor 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 the method is executed, the code segment can be analyzed according to the code type to obtain the parameter value, and the parameter value obtained through analysis is transmitted to the out-of-library operation unit to be processed, so that the aim of calling the out-of-library operation unit to perform corresponding out-of-library operation is fulfilled.

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 value of each parameter in the command type according to the preset data exchange format. For example, if JSON is used as the predetermined data exchange format, the code type of the code segment for the out-of-library operation is JSON. The data processing personnel need to set the command type of the out-of-library operation, each parameter in the command type and the corresponding parameter value into a code segment of the out-of-library operation formed by the JSON statement according to the format requirement of the JSON statement. Of course, in practical applications, the preset data exchange format may also adopt XML, K-VALUE or INI, and the present invention is not limited thereto.

In one specific example, since the out-of-library operation logic cannot be represented using SQL statements, different out-of-library operation logic needs to be identified in the script file by JSON. In this specific example, the specification information and the code segments in the JSON form are:

- -NO 8NAME synchronization results table to TP library SEP YES CALL NO

[ds＝,format＝json,filter＝all]

Wherein cmd is a command type and identifies different types of commands, and in this example, the command is dbTransfer, which means that cross-library data transfer is realized. From, to, and tableName represent parameters of the command, respectively, and different commands may correspond to different parameters, in this example, parameter values for From, to, and tableName are "ap", "tp", and "[" BD _ SUS _ HIT _ mode "," BD _ SUS _ HIT _ mode _ T "], respectively. Therefore, the executor forms an expandable interface for out-of-library operation by setting different commands, and can realize different functional commands and out-of-library operation units by continuous expansion, thereby improving the out-of-library logic operation capability of the executor.

In a preferred embodiment, as shown in fig. 6, the step S300 of sequentially setting the plurality of code segments and the corresponding description information according to a preset execution sequence to form a hybrid SQL script specifically includes:

s310: and setting separators for each code segment and the description information corresponding to the code segment.

S320: for each code segment, execution units are formed in the order of delimiters, specification information, and code segments.

S330: and sequentially arranging all execution units according to a preset execution sequence to obtain the hybrid SQL script.

Specifically, it can be understood that when multiple code segments for in-library operation and out-library operation are arranged in the same hybrid SQL script, the executor needs to extract multiple code segments for separate execution during parsing. Therefore, in order to facilitate the executor to analyze and obtain each independent code segment, a separator can be preset to distinguish the current code segment from the previous and next code segments. Specifically, in the preferred embodiment, the delimiters and the description information are sequentially arranged at the headers of different code segments to form an independent execution unit, and the plurality of execution units are sequentially arranged according to the execution order to form the hybrid SQL script. For example, in one specific example, in order not to destroy the characteristics of the overall SQL file-based file format, the separator can take the following annotation form:

--###[BRAINS]###

of course, in other embodiments, other types of separators may be used, and the present invention is not limited thereto.

The hybrid SQL script formed using the above separators may be, for example:

--###[BRAINS]###

NO 1NAME examine and create temporary Table SEP YES CALL NO

[ds＝ap,format＝sql,filter＝oracle]

CREATE TABLE BD_SUS_HIT_MODEL_tmp_001as SELECT*FROM BD_SUS_HIT_MODEL where 1＝0nologging；

--###[BRAINS]###

2NAME write hit model temporary Table SEP YES CALL NO

[ds＝tp,format＝sql,filter＝hive]

insert into BD_SUS_HIT_MODEL_tmp_001

select*from tmp_hive_tab；

delete from tmp_hive_tab where data_date＝${BATCH_DATE}；

--###[BRAINS]###

3NAME write hit model result Table SEP YES CALL NO

[ds＝ap,format＝sql,filter＝oracle]

insert into BD_SUS_HIT_MODEL

(data_date,batch_id,model_oid,obj_type,cst_type,cst_no,hit_score,hit_count,

hit_rule_str,cfg_model_score,cfg_least_hit_cnt,cfg_score_count_logic,update_time)

SELECT*from bd_sus_tmp；

--###[BRAINS]###

- -NO:4NAME synchronization results table to TP library SEP: YES CALL: NO

[ds＝,format＝json,filter＝all]

The invention provides a hybrid SQL script. The mixed SQL script can support a plurality of sections of SQL sentences of a plurality of isomorphic or heterogeneous database systems, simultaneously supports the addition of the representation codes of the out-of-library operation units in the script, and can support the operation logics of a plurality of database SQL operations and out-of-library operation units in a single file. Specifically, (1) convert from many SQL files to single SQL script, have strengthened the flexibility of many storehouses operation: the unit of database execution is changed from a single SQL file into one code block, each code block can be executed on a single database system, the execution sequence of each code is directly written in a script file, control in an actuator is not needed, and the flexibility of multi-library operation is greatly enhanced. (2) The complexity of the mixed operation outside and inside the library is simplified: the operation outside the database uses the uniform JSON text to be embedded into the script, the seamless connection with the SQL inside the database can be realized, and the logic inside and outside the database can easily realize the integrated operation based on the data through the context and the shared database (3) the complexity of the operation of the heterogeneous database across the database is simplified: different database types are configured and designated in the script file, so that the mixed operation of different types of databases such as a relational database, a big data platform, NOSQL and the like can be supported. In a word, the invention can effectively overcome the defects in the prior art and provides a simple, applicable, flexible and diverse method for uniformly representing and executing the SQL and the out-of-library operation of multiple databases with expansion capability.

Based on the same principle, the invention also discloses an execution method of the hybrid SQL script file. As shown in fig. 7, in this embodiment, the method includes:

s400: and analyzing the hybrid SQL script to obtain a plurality of code segments and corresponding description information.

S500: and determining code execution information corresponding to the description information of each code segment.

S600: and executing the code sections in sequence according to the execution sequence of the code sections and the code execution information of each code section.

In a preferred embodiment, as shown in fig. 8, the step S400 of analyzing the hybrid SQL script to obtain a plurality of code segments and corresponding description information specifically includes:

s410: separators in the hybrid SQL script are identified.

S420: and sequentially extracting the execution units corresponding to each separator.

S430: and analyzing each execution unit to obtain a plurality of code segments of the expected execution sequence and corresponding description information.

In a preferred embodiment, the step S500 of determining the code execution information corresponding to the description information of each code segment specifically includes:

In a preferred embodiment, the step S600 of executing the code segments in sequence according to the execution order of the code segments and the code execution information of each code segment specifically includes:

Based on the same principle, the invention also discloses a device for generating the hybrid SQL script file. As shown in fig. 9, in the present embodiment, the apparatus includes an information acquisition module 11, an explanatory information setting module 12, and a script generation module 13.

The information obtaining module 11 is configured to determine a plurality of code segments to be executed and code execution information.

The instruction information setting module 12 is configured to form instruction information of each code segment according to the code execution information and a preset identifier.

The script generating module 13 is configured to sequentially set the plurality of code segments and the corresponding description information according to a preset execution sequence to form a hybrid SQL script.

Since the principle of the device for solving the problems is similar to the method, the implementation of the device can refer to the implementation of the method, and the detailed description is omitted here.

Based on the same principle, the invention also discloses an execution device of the hybrid SQL script file. As shown in fig. 10, in the present embodiment, the apparatus includes a script parsing module 21, an explanatory information parsing module 22, and a script execution module 23.

The script analysis module 11 is configured to analyze the hybrid SQL script to obtain a plurality of code segments and corresponding description information.

The description information parsing module 12 is configured to determine code execution information corresponding to the description information of each code segment.

The script execution module 13 is used for executing the code segments in sequence according to the execution order of the code segments and the code execution information of each code segment.

The systems, devices, modules or units illustrated in the above embodiments may be implemented by a computer chip or an entity, or by a product with certain functions. A typical implementation device is a computer device, which may be, for example, a personal computer, a laptop computer, a cellular telephone, 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.

In a typical example, the computer device specifically comprises a memory, a processor and a computer program stored on the memory and executable on the processor, the processor implementing the method performed by the client as described above when executing the program, or the processor implementing the method performed by the server as described above when executing the program.

Referring now to FIG. 11, shown is a schematic diagram of a computer device 600 suitable for use in implementing embodiments of the present application.

As shown in fig. 11, the computer apparatus 600 includes a Central Processing Unit (CPU)601 which can perform various appropriate works and processes according to a program stored in a Read Only Memory (ROM)602 or a program loaded from a storage section 608 into a Random Access Memory (RAM)) 603. In the RAM603, various programs and data necessary for the operation of the system 600 are also stored. The CPU601, ROM602, and RAM603 are connected to each other via a bus 604. An input/output (I/O) interface 605 is also connected to bus 604.

The following components are connected to the I/O interface 605: an input portion 606 including a keyboard, a mouse, and the like; an output section 607 including a Cathode Ray Tube (CRT), a liquid crystal feedback (LCD), and the like, and a speaker and the like; a storage section 608 including a hard disk and the like; and a communication section 609 including a network interface card such as a LAN card, a modem, or the like. The communication section 609 performs communication processing via a network such as the internet. The driver 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, or the like is mounted on the drive 610 as necessary, so that a computer program read out therefrom is mounted as necessary on the storage section 608.

In particular, according to an embodiment of the present invention, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the invention include a computer program product comprising a computer program tangibly embodied on a machine-readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 609, and/or installed from the removable medium 611.

Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, 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 disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device. As defined herein, a computer readable medium does not include a transitory computer readable medium such as a modulated data signal and a carrier wave.

For convenience of description, the above devices are described as being divided into various units by function, and are described separately. Of course, the functionality of the units may be implemented in one or more software and/or hardware when implementing the present application.

The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

It should also be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.

As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

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

The embodiments in the present specification are described in a progressive manner, and the same and similar parts among the embodiments are referred to each other, and each embodiment focuses on the differences from the other embodiments. In particular, for the system embodiment, since it is substantially similar to the method embodiment, the description is simple, and for the relevant points, reference may be made to the partial description of the method embodiment.

The above description is only an example of the present application and is not intended to limit the present application. Various modifications and changes may occur to those skilled in the art. 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 method for generating a hybrid SQL script file is characterized by comprising the following steps:

2. The method of generating a hybrid SQL script file according to claim 1, wherein the description information is a comment;

3. The method of generating a hybrid SQL script file according to claim 1, wherein the description information is an identification string;

4. The hybrid SQL script file generating method of claim 1, wherein the description information comprises comments and identification strings;

5. The method of generating a hybrid SQL script file according to claim 1, wherein the code execution information includes a code type, if the code type is an out-of-library operation;

6. The method for generating a hybrid SQL script file according to claim 1, wherein the sequentially setting the plurality of code segments and the corresponding description information according to a preset execution sequence to form a hybrid SQL script specifically comprises:

7. An execution method of a hybrid SQL script file is characterized by comprising the following steps:

8. The method for executing a hybrid SQL script file according to claim 7, wherein analyzing the hybrid SQL script to obtain a plurality of code segments and corresponding description information specifically comprises:

identifying a separator in the hybrid SQL script;

sequentially extracting the execution units corresponding to each separator;

9. The method for executing a hybrid SQL script file according to claim 7, wherein the determining the code execution information corresponding to the description information of each code segment specifically comprises:

10. The method for executing a hybrid SQL script file according to claim 9, wherein the executing the code segments in sequence according to the execution order of the code segments and the code execution information of each code segment specifically comprises:

11. A hybrid SQL script file generation apparatus, comprising:

12. An apparatus for executing a hybrid SQL script file, comprising:

13. A computer device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor,

the processor, when executing the program, implements the method of any of claims 1-10.

14. A computer-readable medium, having stored thereon a computer program,

the program when executed by a processor implementing the method according to any one of claims 1-10.