CN110287211B - Execution method of dynamic SQL (structured query language) statement based on big data platform - Google Patents
Execution method of dynamic SQL (structured query language) statement based on big data platform Download PDFInfo
- Publication number
- CN110287211B CN110287211B CN201910584160.8A CN201910584160A CN110287211B CN 110287211 B CN110287211 B CN 110287211B CN 201910584160 A CN201910584160 A CN 201910584160A CN 110287211 B CN110287211 B CN 110287211B
- Authority
- CN
- China
- Prior art keywords
- script
- xsql
- execution
- executing
- sql statement
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24553—Query execution of query operations
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2458—Special types of queries, e.g. statistical queries, fuzzy queries or distributed queries
- G06F16/2477—Temporal data queries
Abstract
The invention discloses a method for executing dynamic SQL (structured query language) statements based on a big data platform, which belongs to the technical field of SQL statement execution and solves the problems that abnormal programs cannot be positioned and optimized and scripts for executing complex logics cannot be simply called when scripts for executing simple logics or scripts for executing complex logics in the prior art. Constructing an xsql script with a label; calculating values of all time variables in the date parameter according to the date parameter of the xsql script operation, and storing the values into global variable data; and acquiring the value of the current time variable from the global variable data, executing the corresponding SQL statement according to the execution logic of the label of the SQL statement in the xsql script based on the driver which is connected with the database and can return the running related information and the storage path of the xsql script, and recording the execution result into a log file according to the log path of the operation of the xsql script. The invention is used for realizing the processing of the complex logic script.
Description
Technical Field
A method for executing dynamic SQL statements based on a big data platform is used for processing complex logic scripts and belongs to the technical field of SQL statement execution.
Background
When a star ring big data initiator batch script is developed, a perl packaged and executed SQL logic module is submitted to a database for execution in a bean mode, and finally an execution result (success/failure) is returned. The specific execution process comprises the following steps: perl pre-processes the SQL logic model, replaces variables in the SQL logic model with actual values, then uses a beeline mode receptor to connect the database, and submits the variables to the receptor database for execution.
Sometimes, for some complex requirements, ETL developers need to write more perl programs to implement complex logic, such as if judgment, for loop, etc. This allows the ETL developer to not only be familiar with the SQL language, but also have some knowledge of perl. Meanwhile, the return result of the program execution written in this way is only in a success state and a failure state, and information such as the execution start time, the execution end time, the influence record number and the like of each section of logic cannot be captured.
If the logic is simple, that is, only sequential execution is performed, there is no special logic (such as for loop, if judgment, goto tune away, etc.), and only the steps are many, the existing processing method mainly includes:
1. and splitting the logic steps into N sections, respectively packaging each section of program by using perl script, and submitting the program to a database for execution through bean.
2. And packaging the whole logic into a logic block, packaging the whole logic block into a logic processing unit, and submitting the logic processing unit into a database for execution through bean.
According to the two methods, for the simple multi-step processing logic, if the sql is integrally packaged and submitted to a database for execution in a beeline mode, the method can only obtain the return result of the program execution, cannot obtain the return state of the program execution in the specific steps, and starts to execute time and execute finish time.
If SQL is separately distributed according to each step, SQL is submitted to a database for execution through bean, and the next statement logic is submitted after a result is returned, although the method can return the starting time, the ending time and the execution state of each step of execution, the influence record number of the program cannot be captured. Meanwhile, in the program written by the method, developers need to master the writing skill of the SQL sentences and also need the perl language development capability, and the written code amount (code line number) is very large.
If the logic is complex, such as complex logic of loop, branch judgment and the like, the existing processing method is to implement the complex logic through perl language, nest SQL into perl script, and submit the SQL to the database for execution through beeline.
If the sql logic statement is nested in the perl script and submitted to the database for execution through the beeline, only the execution result (success and failure) of the whole logic block can be obtained, and the execution state and execution time of each step cannot be obtained and the state is returned. Therefore, the program problem troubleshooting and the program optimization are very inconvenient. Similarly, developers are required to not only master the writing skills of SQL statements, but also to develop the capabilities in the perl language.
Disclosure of Invention
In view of the above research problems, an object of the present invention is to provide a dynamic SQL execution method based on a star ring big data platform, which solves the problems that in the prior art, when a simple logic script is executed or a complex logic script is executed, an abnormal program cannot be located and optimized, and the complex logic script cannot be simply called.
In order to achieve the purpose, the invention adopts the following technical scheme:
a method for executing dynamic SQL statements based on a big data platform comprises the following steps:
step 1: constructing an xsql script with a label, namely, parts of SQL sentences in the xsql script have corresponding labels, and storing the xsql script with the label after construction;
and 2, step: acquiring a date parameter of the operation of the xsql script, a driver which is connected with the database and can return operation related information, a storage path of the xsql script and a log path of the operation of the xsql script;
and step 3: calculating values of all time variables in the date parameter according to the date parameter of the xsql script operation, and storing the values into global variable data;
and 4, step 4: obtaining the value of the current time variable from the global variable data, executing the corresponding SQL sentence according to the execution sequence of the SQL sentences in the xsql script and the execution logic of the label based on the storage path of the driver and the xsql script which are connected with the database and can return the running related information, and recording the execution result into a log file according to the log path of the xsql script, wherein the execution logic is that the SQL sentences corresponding to the label are executed under the condition that the label is called, otherwise, the SQL sentences are executed according to the sequence of the SQL sentences.
Further, the SQL statement in step S1 includes variable definition, for loop, if judgment, data export function, and a function that the ble statement block implements logic tuning function, sleep function, and call another xsql script.
Further, in the step 2, the date range parameter of the xsql script is the date of T-1 day, wherein T day represents today's date, and T-1 day represents yesterday's date.
Further, in step 2, the relevant information in the driver connected to the database and capable of returning the running relevant information includes a start time, an end time, an execution state, a running time, the number of affected records, and a final state of executing the xsql script.
Further, in the step 2, a database in a driver which is connected with the database and can return operation related information is an inceptor of a star ring big data platform, and the driver is an impyla driver.
Further, in the step 2, the storage path of the xsql script is an absolute path or a relative path.
Further, constructing an xsql script with a label and storing the xsql script in a memory of the server, reading a date parameter of the running of the xsql script by a processor in the server, calculating values of all time variables in the date parameter and storing the values into global variable data of the memory, acquiring the value of a current date variable from the global variable data of the memory by the processor before the xsql script is executed, calling a corresponding SQL statement stored in the memory to execute according to an execution logic of the label of the SQL statement in the xsql script based on a driver which is connected with the database and can return running related information and a storage path of the xsql script, and storing an execution result into a log file of the memory according to the log path of the running of the xsql script.
Compared with the prior art, the invention has the beneficial effects that:
1. in the prior art, the execution of the SQL statement cannot capture the starting time and the ending time of the execution of the specific SQL statement, influence the number of records, the execution state, the time consumption of operation and the like, and only the execution result state of the whole program block can be returned; the method not only can feed back the final state of the whole execution, but also can capture and record the execution starting time, the execution ending time, the execution state, the influence record number and the like of each SQL statement, and the method has a great reference value for problem positioning and optimization of the program.
2. When the SQL statement is executed, the impyla driver can return the execution start time, the execution end time and the execution state of SQL to influence the information of the number of records and the like, so the impyla driver collects impyla as the driver; the Python is adopted to write the xsql script, so that the complicated logic of the program can be flexibly realized;
3. in the prior art, a program realizes complex logic, such as for and if judgment, and very complex codes need to be written; the invention can realize functions like for, if, table and the like only by adding proper tags in SQL sentences, simplifies the compiling mode of calling programs, reduces the code compiling difficulty, and also provides the functions of data export, dormancy and calling of another xsql script, thereby realizing complex logic through simple tag calling (namely only compiling tag calling).
Drawings
Is composed of
Detailed Description
The invention will now be further described by way of specific embodiments.
A method for executing dynamic SQL statements based on a big data platform comprises the following steps:
step 1: constructing an xsql script with a label, namely, parts of SQL sentences in the xsql script have corresponding labels, and storing the xsql script with the label after construction;
step 2: acquiring a date parameter of the operation of the xsql script, a driver which is connected with the database and can return to the operation of related information, a storage path of the xsql script and a log path of the operation of the xsql script;
and step 3: calculating values of all time variables in the date parameter according to the date parameter of the xsql script operation, and storing the values into global variable data;
and 4, step 4: obtaining the value of the current time variable from the global variable data, executing the corresponding SQL sentence according to the execution sequence of the SQL sentences in the xsql script and the execution logic of the label based on the storage path of the driver and the xsql script which are connected with the database and can return the running related information, and recording the execution result into a log file according to the log path of the xsql script, wherein the execution logic is that the SQL sentences corresponding to the label are executed under the condition that the label is called, otherwise, the SQL sentences are executed according to the sequence of the SQL sentences.
Further, the SQL statement in step S1 includes variable definition, for loop, if judgment, data export function, and a function that a ble statement block implements a logic tuning function, a sleep function, and a function of calling another xsql script, which are all commonly used underlying functions.
Further, in the step 2, the date range parameter of the xsql script operation is the date of T-1 day, wherein T day represents today's date, and T-1 day represents yesterday's date.
Further, in step 2, the relevant information in the driver connected to the database and capable of returning the running relevant information includes the start time, the end time, the execution state, the running time, the number of the affected records, and the final state of the xsql script.
Further, in the step 2, the database in the driver which is connected with the database and can return the running related information is a star ring big data platform initiator, and the driver is an impyla driver.
Further, in the step 2, the storage path of the xsql script is an absolute path or a relative path.
Further, constructing an xsql script with a label and storing the xsql script in a memory of the server, reading a date parameter of the running of the xsql script by a processor in the server, calculating values of all time variables in the date parameter and storing the values into global variable data of the memory, acquiring the value of a current date variable from the global variable data of the memory by the processor before the xsql script is executed, calling a corresponding SQL statement stored in the memory to execute according to an execution logic of the label of the SQL statement in the xsql script based on a driver which is connected with the database and can return running related information and a storage path of the xsql script, and storing an execution result into a log file of the memory according to the log path of the running of the xsql script.
Examples
Constructing an xsql script with a label, namely, parts of SQL sentences in the xsql script have corresponding labels, and storing the xsql script with the label after construction; the method comprises the following specific steps:
the program is saved as: student. Xsql
The execution command is as follows: python xsql. Py-c/edwetl/xsql/config/edw _ db _ connection-f student. Xsql-l student. Log-d 20190601
Acquiring a driver (-c designation) which is connected with the database and can return running related information, a storage path (-f designation) of the xsql script, a log path (-l designation) of the xsql script running, and a date parameter (-d designation) of the xsql script running;
according to the date parameter of the operation of the xsql script specified by the-d, calculating a time variable value possibly used by the program in advance, and storing the time variable value into global variable data;
and acquiring the value of the current time variable from the global variable data, executing the corresponding SQL statement according to the execution logic of the label of the SQL statement in the xsql script based on the driver which is connected with the database and can return the running related information and the storage path of the xsql script, and recording the execution result into a log file according to the log file information. The specific execution logic is as follows:
1. executing the sequence #1, and calculating a custom variable to obtain a variable $ { SCHOOLNAME };
2, executing a sequence #2, calculating the value of the V1 variable, and then transferring to an executing sequence #3 through a GOTO statement, namely the statement labeled as LABEL _ 1;
3 execution order #3; firstly, executing a sequence #4, calculating a variable CLASSNAME, then executing a sequence #5, and jumping to a statement labeled LABEL _2 in the execution sequence #5, namely an execution sequence #6;
4, entering an execution sequence #6, firstly executing a sequence #7 after entering, calculating variables STUDENTNAME and SEX, then executing a sequence #8, and jumping to a statement labeled as LABEL _3 in the execution sequence #8, namely an execution sequence #9;
5. entering LABEL _3, executing an if statement, if the $ { SEX } = = = 'M' condition is met, executing a sequence #10.1 module, otherwise, executing a sequence #10.2;
6. when the execution sequence #9 is finished, the program automatically returns to the execution sequence #8 to execute the next cycle;
7. when the execution sequence #8 is finished, the program automatically returns to the execution sequence #5 module to execute the next cycle;
8. when the execution sequence #5 is finished, the program automatically returns to the execution sequence #2 to execute the next cycle;
9. and finishing the whole loop of the sequence #2 to be executed and finishing the program operation.
The program and the execution flow #1, #2, #3, etc. are added to the corresponding positions for convenience of description, and represent the execution sequence.
The above are merely representative examples of the many specific applications of the present invention, and do not limit the scope of the invention in any way. All the technical solutions formed by the transformation or the equivalent substitution fall within the protection scope of the present invention.
Claims (6)
1. A method for executing dynamic SQL statements based on a big data platform is characterized by comprising the following steps:
step 1: constructing an xsql script with a label, namely, parts of SQL sentences in the xsql script have corresponding labels, and storing the xsql script with the label after construction;
step 2: acquiring a date parameter of the operation of the xsql script, a driver which is connected with the database and can return operation related information, a storage path of the xsql script and a log path of the operation of the xsql script;
and 3, step 3: calculating values of all time variables in the date parameter according to the date parameter operated by the xsql script, and storing the values into global variable data;
and 4, step 4: acquiring a value of a current time variable from global variable data, executing a corresponding SQL (structured query language) statement according to an execution sequence of the SQL statement in the xsql script and an execution logic of a label based on a driver which is connected with a database and can return operation related information and a storage path of the xsql script, and recording an execution result into a log file according to a log path of the xsql script operation, wherein the execution logic is that the SQL statement corresponding to the label is executed under the condition that the label is called, otherwise, the execution is carried out according to the sequence of the SQL statement;
in step 2, the relevant information in the driver which is connected with the database and can return the running relevant information includes the starting time, the ending time, the execution state, the running time consumption, the number of the affected records and the final state of the executed xsql script.
2. The method according to claim 1, wherein the SQL statement in step S1 includes variable definition, for loop, if judgment, data export function, and function of a ble statement block implementing logic tuning function, sleep function, and function of calling another xsql script.
3. The method for executing the dynamic SQL statement based on the big data platform according to claim 1, wherein in step 2, the date range parameter of the xsql script is the date of T-1 day, where T day represents today's date and T-1 day represents yesterday's date.
4. The method for executing the dynamic SQL statement based on the big data platform according to any one of claims 1 to 3, wherein in the step 2, the database in the driver connected to the database and capable of returning the operation related information is an initiator of the star ring big data platform, and the driver is an impyla driver.
5. The method for executing the dynamic SQL statement based on the big data platform according to any claim 4, wherein in the step 2, the storage path of the xsql script is an absolute path or a relative path.
6. The method according to any one of claims 4, wherein a tagged xsql script is constructed and stored in a memory of the server, a processor in the server reads a date parameter of the xsql script operation, calculates values of all time variables in the date parameter, stores the values in global variable data of the memory, obtains the value of a current date variable from the global variable data of the memory before executing the xsql script, calls a driver capable of returning operation-related information and a storage path of the xsql script connected to the database, executes the SQL statement stored in the memory according to an execution logic of a tag of the SQL statement in the xsql script, and stores an execution result in a log file of the memory according to the log path of the xsql script operation.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910584160.8A CN110287211B (en) | 2019-07-01 | 2019-07-01 | Execution method of dynamic SQL (structured query language) statement based on big data platform |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910584160.8A CN110287211B (en) | 2019-07-01 | 2019-07-01 | Execution method of dynamic SQL (structured query language) statement based on big data platform |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110287211A CN110287211A (en) | 2019-09-27 |
| CN110287211B true CN110287211B (en) | 2022-11-04 |
Family
ID=68020339
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910584160.8A Active CN110287211B (en) | 2019-07-01 | 2019-07-01 | Execution method of dynamic SQL (structured query language) statement based on big data platform |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110287211B (en) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111737280A (en) * | 2020-06-22 | 2020-10-02 | 中国银行股份有限公司 | Method and device for optimizing SQL storage process script |
| CN112527791A (en) * | 2021-02-09 | 2021-03-19 | 中关村科学城城市大脑股份有限公司 | Intelligent urban brain big data system |
| CN113961588A (en) * | 2021-10-27 | 2022-01-21 | 北京科杰科技有限公司 | Big data SQL program design method based on configurable parameters |
| CN114416197A (en) * | 2021-12-28 | 2022-04-29 | 成都易达数安科技有限公司 | Development-free scripted configuration method of APP background interface based on xml |
Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| GB9526096D0 (en) * | 1995-12-20 | 1996-02-21 | British Telecomm | Specifying indexes for relational databases |
| CN103514166A (en) * | 2012-06-15 | 2014-01-15 | 阿里巴巴集团控股有限公司 | Method and device for SQL inspection |
| CN105139148A (en) * | 2009-03-31 | 2015-12-09 | 亚马逊技术股份有限公司 | Control Service for Relational Data Management |
| CN105760286A (en) * | 2016-02-17 | 2016-07-13 | 中国工商银行股份有限公司 | Application database dynamic property detection method and detection device |
| CA3001304A1 (en) * | 2015-06-05 | 2016-07-28 | C3 Iot, Inc. | Systems, methods, and devices for an enterprise internet-of-things application development platform |
| CN106021071A (en) * | 2016-05-04 | 2016-10-12 | 北京思特奇信息技术股份有限公司 | Method and system for monitoring SQL operation process |
| CN108763259A (en) * | 2018-04-03 | 2018-11-06 | 四川新网银行股份有限公司 | A kind of big data platform data load converting algorithm |
Family Cites Families (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7310777B2 (en) * | 2002-10-18 | 2007-12-18 | Computer Associates Think, Inc. | User interface for viewing performance information about transactions |
| US7283993B2 (en) * | 2004-04-29 | 2007-10-16 | International Business Machines Corporation | Methods, systems, and media for handling errors in script files |
| US7703005B2 (en) * | 2004-05-21 | 2010-04-20 | Bea Systems, Inc. | Method to generate scripts from XML |
| US8676749B2 (en) * | 2008-07-31 | 2014-03-18 | Sybase, Inc. | Statement logging in databases |
| US9860229B2 (en) * | 2015-01-19 | 2018-01-02 | Sas Institute Inc. | Integrated data extraction and retrieval system |
| US10558653B2 (en) * | 2017-06-07 | 2020-02-11 | Sap Se | Dynamic, variable-data attribute data service |
-
2019
- 2019-07-01 CN CN201910584160.8A patent/CN110287211B/en active Active
Patent Citations (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| GB9526096D0 (en) * | 1995-12-20 | 1996-02-21 | British Telecomm | Specifying indexes for relational databases |
| CN105139148A (en) * | 2009-03-31 | 2015-12-09 | 亚马逊技术股份有限公司 | Control Service for Relational Data Management |
| CN103514166A (en) * | 2012-06-15 | 2014-01-15 | 阿里巴巴集团控股有限公司 | Method and device for SQL inspection |
| CA3001304A1 (en) * | 2015-06-05 | 2016-07-28 | C3 Iot, Inc. | Systems, methods, and devices for an enterprise internet-of-things application development platform |
| CN105760286A (en) * | 2016-02-17 | 2016-07-13 | 中国工商银行股份有限公司 | Application database dynamic property detection method and detection device |
| CN106021071A (en) * | 2016-05-04 | 2016-10-12 | 北京思特奇信息技术股份有限公司 | Method and system for monitoring SQL operation process |
| CN108763259A (en) * | 2018-04-03 | 2018-11-06 | 四川新网银行股份有限公司 | A kind of big data platform data load converting algorithm |
Non-Patent Citations (3)
| Title |
|---|
| Real-Time SQL的设计和实现;国志宏;《中国优秀硕士学位论文全文数据库信息科技辑》;20040315(第3期) * |
| SQLServer 查看SQL语句的执行时间;suxuelian;《https://blog.csdn.net/suxuelian/article/details/80198415?ops_request_misc》;20180514;全文 * |
| 钢坯端面喷号中WinCC监控系统与数据库的开发;段培培;《中国优秀硕士学位论文全文数据库工程科技Ⅰ辑》;20170215(第2期);全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110287211A (en) | 2019-09-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110287211B (en) | Execution method of dynamic SQL (structured query language) statement based on big data platform | |
| Aiken et al. | A development environment for horizontal microcode | |
| CN101196926A (en) | Database access platform and access method thereof | |
| CN109799976B (en) | Real-time wind control variable calculation method based on distributed stream type calculation engine | |
| US11288062B2 (en) | Automatic source code refactoring | |
| CN111597243A (en) | Data warehouse-based abstract data loading method and system | |
| CN103823680A (en) | Development method and device for game service logic engine | |
| CN110516000B (en) | Workflow management system supporting complex workflow structure | |
| CN110610698A (en) | Voice labeling method and device | |
| Daniel et al. | Gremlin-ATL: a scalable model transformation framework | |
| CN111708564A (en) | Multi-model management method, system, medium and server | |
| CN109657803A (en) | The building of machine learning model | |
| US20220206927A1 (en) | Test package analyzer | |
| CN113841135A (en) | Service management in DBMS | |
| CN104834742A (en) | ETL architecture management method based on SCA | |
| CN111142848A (en) | Software program compiling method, software program executing method and software program compiling device | |
| US20080288950A1 (en) | Concurrent Management of Adaptive Programs | |
| CN109558582B (en) | Visual angle-based sentence emotion analysis method and device | |
| Su | Application program conversion due to data base changes | |
| CN117075907B (en) | Application program compiling method, system, compiler and storage medium | |
| US20110137959A1 (en) | Representing relational schema information using generic meta schemas | |
| CN105700854B (en) | Run the method and device of application task | |
| CN117520690B (en) | UI (user interface) automation method and related device based on CSS (CSS style) style prerendering contrast | |
| US20240028594A1 (en) | Query refactoring framework | |
| CN117234466B (en) | Enterprise management software development method, system, equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |