CN111581088B

CN111581088B - Spark-based SQL program debugging method, device, equipment and storage medium

Info

Publication number: CN111581088B
Application number: CN202010355796.8A
Authority: CN
Inventors: 王成龙
Original assignee: Shanghai Zhongtongji Network Technology Co Ltd
Current assignee: Shanghai Zhongtongji Network Technology Co Ltd
Priority date: 2020-04-29
Filing date: 2020-04-29
Publication date: 2023-09-15
Anticipated expiration: 2040-04-29
Also published as: CN111581088A

Abstract

The application relates to a Spark-based SQL program debugging method, device, equipment and storage medium. The method comprises the following steps: acquiring SQL operation request information sent by a user; the SQL running request information comprises SQL program sentences to be debugged; calling an object through a corresponding preset SQL debugging interface to run an SQL program statement to be debugged, and obtaining a running result; and returning the running result to the interface caller through a preset SQL debugging interface. By adopting the method, the device or the equipment, the SQL program statement can be executed through the preset SQL debugging interface, the SQL program statement is debugged, repeated packaging and uploading to the cluster are not needed for debugging, and the SQL debugging efficiency is improved.

Description

Spark-based SQL program debugging method, device, equipment and storage medium

Technical Field

The application relates to the technical field of software development, in particular to a Spark-based SQL program debugging method, device, equipment and storage medium.

Background

With the development of intelligent technology, the development of software programs is becoming mature. During the development of software programs, people often need to develop based on computational engines. Such as Spark-based software development. The development based on Spark is mostly based on Spark SQL, and in the development process, the correctness of the program needs to be detected, and often, whether the SQL program has errors or not is judged by executing the program written by a developer.

In the process of program development, a developer writes a long SQL program statement each time, and in this case, grammar errors are very easy to occur, or the queried data after the SQL program statement is executed is inaccurate. At this time, the SQL program statement needs to be debugged. In the prior art, debugging of SQL program sentences is usually performed by compiling and packaging and sending the SQL program sentences to a cluster for running so as to detect whether the SQL program sentences have errors. If the result is wrong, the developer modifies the result, but the SQL program statement needs to be repackaged and issued to the cluster to run each time, and whether the grammar is wrong or whether the execution result is accurate is observed again. Thus, a lot of time is wasted in SQL development and package debugging. If the thriftserver service is started, the cluster resources are occupied for a long time, and a temporary table registered in Spark cannot be supported.

Disclosure of Invention

In view of the above, the present application aims to overcome the defects of the prior art, and provide a method, a device, equipment and a storage medium for debugging a Spark-based SQL program.

In order to achieve the above purpose, the application adopts the following technical scheme:

a Spark-based SQL program debugging method comprises the following steps:

acquiring SQL operation request information sent by a user; the SQL operation request information comprises SQL program sentences to be debugged;

calling an object to run the SQL program statement to be debugged through a corresponding preset SQL debugging interface to obtain a running result;

and returning the operation result to an interface caller through the preset SQL debugging interface.

Optionally, the SQL operation request information further includes: a required interface address;

further comprises: determining the preset SQL debugging interface according to the required interface address; the preset SQL debugging interface is a custom restful interface.

Optionally, the step of calling the object through the corresponding preset SQL debugging interface to run the SQL program statement to be debugged to obtain a running result includes:

calling a SparkSession object through the corresponding restful interface;

and the SparkSession object runs the SQL program statement to be debugged to obtain the running result.

Optionally, the method further comprises:

setting a service task and a corresponding interface address; the service tasks comprise SQL running services;

storing the service task and the corresponding interface address;

and sending the service task to a user for selection.

Optionally, the method further comprises:

and receiving an instruction of the SQL operation service selected by a user, and generating the SQL operation request information.

A Spark-based debugging device for an SQL program, comprising:

the information acquisition module is used for acquiring SQL operation request information sent by a user; the SQL operation request information comprises SQL program sentences to be debugged;

the operation module is used for calling an object through a corresponding preset SQL debugging interface to operate the SQL program statement to be debugged, so that an operation result is obtained;

and the result return module is used for returning the running result to an interface caller through the preset SQL debugging interface.

further comprises: the required interface determining module is used for determining the preset SQL debugging interface according to the required interface address; the preset SQL debugging interface is a restful interface.

Optionally, the operation module includes:

an object calling unit, configured to call a SparkSession object through the corresponding restful interface;

and the SQL running unit is used for running the SQL program statement to be debugged by the sparkSession object to obtain the running result.

A Spark-based debugging device for an SQL program, comprising:

a processor, and a memory coupled to the processor;

the memory is used for storing a computer program, and the computer program is at least used for executing the debugging method of the SQL program based on Spark;

the processor is configured to invoke and execute the computer program in the memory.

A storage medium storing a computer program which, when executed by a processor, implements the steps of the Spark-based SQL program debugging method described above.

The technical scheme provided by the application can comprise the following beneficial effects:

the application discloses a Spark-based SQL program debugging method, which comprises the following steps: acquiring SQL operation request information sent by a user; the SQL running request information comprises SQL program sentences to be debugged; calling an object to run the SQL program statement to be debugged through a corresponding preset SQL debugging interface to obtain a running result; and returning the running result to the interface caller through a preset SQL debugging interface. In the method, after receiving SQL operation request information of a user, a preset SQL debugging interface is called, an object is called in a corresponding internal interface to operate SQL program sentences to be debugged, an operation result is obtained, the operation result is returned to an interface caller, and the result is fed back to the user for the user to refer to further debugging SQL programs. In the method, SQL debugging is carried out without depending on clusters outside the Spark engine, SQL debugging can be realized only through Spark built-in interface service, and SQL sentences are not required to be packaged and sent again and again in the SQL debugging process, so that the debugging efficiency of the SQL program is greatly improved.

Drawings

In order to more clearly illustrate the embodiments of the application or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the application, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.

FIG. 1 is a flowchart of a method for debugging a Spark-based SQL program according to an embodiment of the application;

FIG. 2 is a flowchart of a debugging method for a Spark-based SQL procedure according to another embodiment of the application;

FIG. 3 is a block diagram of a debugging device for a Spark-based SQL program according to an embodiment of the application;

fig. 4 is a structural diagram of a debugging device of a Spark-based SQL program according to an embodiment of the present application.

Detailed Description

In order to make the objects, technical solutions and advantages of the present application more apparent, the technical solutions of the present application will be described in detail below. It will be apparent that the described embodiments are only some, but not all, embodiments of the application. All other embodiments, based on the examples herein, which are within the scope of the application as defined by the claims, will be within the scope of the application as defined by the claims.

In the prior art, in the development process of Spark SQL, SQL sentences are often solidified into codes, compiled and packed and then submitted to cluster execution, and the method has the advantages of supporting the association of a temporary table and other tables (hive tables and the like), and has the defects that the correctness of SQL is verified by repackaging and release every time, and time is wasted.

In addition, SQL verification can be performed in a ThriftServer mode, and a task is submitted to be put on a cluster for running for a long time. The method has the advantages of supporting different SQL sentences and not needing to be published in a packing way. The disadvantage is that Spark Streaming and Spark temporary tables are not supported.

In order to solve the SQL debugging problem in the prior art, the application provides an SQL debugging mode based on Spark, and the details are as follows.

Fig. 1 is a flowchart of a debugging method of a Spark-based SQL program according to an embodiment of the present application. Referring to fig. 1, a method for debugging a Spark-based SQL program includes:

step 101: acquiring SQL operation request information sent by a user; the SQL running request information comprises SQL program sentences to be debugged;

step 102: calling an object through a corresponding preset SQL debugging interface to run an SQL program statement to be debugged, and obtaining a running result; the preset SQL debugging interface is an independent research and development interface and a custom interface, and the SQL debugging interface is exposed on the interface service, so that other systems can realize the SQL online debugging function by calling the interface.

Step 103: and returning the running result to the interface caller through a preset SQL debugging interface.

After receiving an SQL debugging request sent by a user, calling an object to run an SQL program statement to be debugged through a corresponding preset SQL debugging interface to obtain a running result and returning the running result. The running result may be a program running result or may be abnormal information during the running of the program. When the SQL program statement has no error, the running result is the program running result, and when the SQL program statement has error, the abnormal information is displayed. And meanwhile, when the SQL program statement has a logic problem, abnormal information is displayed.

The SQL operation request information further comprises: the required interface address. Determining a preset SQL debugging interface according to the required interface address; the preset SQL debugging interface is a restful interface. When the user requests SQL debugging, the transmitted request instruction contains the address of the corresponding interface responsible for running the SQL program statement. And directly calling a corresponding interface according to the interface address, then directly calling a Spark SQL engine inside the interface to complete SQL calculation tasks, and returning an execution result or wrong stack information to a caller.

By adopting the method, spark Streaming can be supported, a temporary table is supported, a code is not required to be modified, SQL program sentences directly execute verification errors, and repeated packaging and uploading are not required until the program is error-free. A great deal of SQL development and debugging time is saved.

Based on the above embodiments, the present application also discloses another detailed embodiment of the debugging method, which specifically includes the following cases:

fig. 2 is a flowchart of a debugging method of a Spark-based SQL program according to another embodiment of the present application. A Spark-based SQL program debugging method comprises the following steps:

step 201: acquiring SQL operation request information sent by a user; the SQL running request information comprises SQL program sentences to be debugged;

step 202: calling a SparkSession object through a corresponding restful interface;

step 203: the SparkSession object runs the SQL program statement to be debugged, and a running result is obtained.

Step 204: and returning the operation result to the interface caller through the restful interface.

In the embodiment, the driver starts a restful interface service through a sub-thread to provide an interface of the SQL program statement debugging function. The entry of the interface is SQL statement developed by the caller, in the interface, the SQL program statement is executed by calling the sparkSession object of the current task, and the execution result or abnormal information is returned to the interface caller through the restful interface, so that SQL debugging work is flexibly completed.

On the basis of the above embodiment, the method further comprises:

storing the service task and the corresponding interface address;

and sending the service task to a user for selection.

In the application, a task corresponding to an interface, namely a task of presetting an interface address is preset in a Spark framework, and the task and the corresponding interface address are stored in a database. When a user requests SQL operation verification in Spark, spark provides a plurality of task options for the user to select, and after the user selects the SQL verification option, spark determines a corresponding interface address according to the task, and then invokes the interface to execute the SQL program statement operation verification.

In the embodiment, the SQL program statement is transferred to the running Spark task in the mode of interface call, the debugging of the SQL program statement is realized through the built-in restful interface service without modifying codes and repackaging and publishing, and the debugging efficiency of the SQL program statement is provided.

The embodiment of the application also provides a device for debugging the SQL program based on Spark. Please see the examples below.

Fig. 3 is a block diagram of a debugging device of a Spark-based SQL program according to an embodiment of the present application. Referring to fig. 3, a device for debugging a Spark-based SQL program includes:

the information acquisition module 301 is configured to acquire SQL operation request information sent by a user; the SQL running request information comprises SQL program sentences to be debugged;

the operation module 302 is configured to invoke an object to operate the SQL program statement to be debugged through a corresponding preset SQL debugging interface, so as to obtain an operation result;

and the result returning module 303 is configured to return the running result to the interface caller through the preset SQL debug interface.

Wherein, the operation module includes:

On the basis of the above embodiment, the SQL operation request information further includes: a required interface address;

the device further comprises: the required interface determining module is used for determining the preset SQL debugging interface according to the required interface address; the preset SQL debugging interface is a custom restful interface.

In the device, the SQL program statement is operated by calling the Spark internal restful interface to obtain an operation result, so that the SQL program statement is debugged, the device does not need to pack and upload the SQL program statement, does not need to rely on an external cluster to carry out SQL debugging, and saves SQL debugging time.

In order to more clearly introduce a hardware system for implementing the embodiment of the present application, the embodiment of the present application further provides a device for debugging the Spark-based SQL program, which corresponds to the method for debugging the Spark-based SQL program provided by the embodiment of the present application. Please see the examples below.

Fig. 4 is a structural diagram of a debugging device of a Spark-based SQL program according to an embodiment of the present application. Referring to fig. 4, a debugging device for a Spark-based SQL program includes:

a process 401, and a memory 402 connected to the processor 401;

the memory 402 is configured to store a computer program, where the computer program is configured to at least perform the above-described method for debugging a Spark-based SQL program;

the processor 401 is used to call and execute a computer program in the memory 402.

The device can directly call the restful interface in Spark to run SQL sentences, so that SQL debugging time is greatly saved, and debugging efficiency is improved.

On the basis of the above, the application also discloses a storage medium, wherein the storage medium stores a computer program, and when the computer program is executed by a processor, each step in the SQL program debugging method based on Spark is realized.

It is to be understood that the same or similar parts in the above embodiments may be referred to each other, and that in some embodiments, the same or similar parts in other embodiments may be referred to.

It should be noted that in the description of the present application, the terms "first," "second," and the like are used for descriptive purposes only and are not to be construed as indicating or implying relative importance. Furthermore, in the description of the present application, unless otherwise indicated, the meaning of "plurality" means at least two.

Any process or method descriptions in flow charts or otherwise described herein may be understood as representing modules, segments, or portions of code which include one or more executable instructions for implementing specific logical functions or steps of the process, and further implementations are included within the scope of the preferred embodiment of the present application in which functions may be executed out of order from that shown or discussed, including substantially concurrently or in reverse order, depending on the functionality involved, as would be understood by those reasonably skilled in the art of the present application.

It is to be understood that portions of the present application may be implemented in hardware, software, firmware, or a combination thereof. In the above-described embodiments, the various steps or methods may be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented in hardware, as in another embodiment, may be implemented using any one or combination of the following techniques, as is well known in the art: discrete logic circuits having logic gates for implementing logic functions on data signals, application specific integrated circuits having suitable combinational logic gates, programmable Gate Arrays (PGAs), field Programmable Gate Arrays (FPGAs), and the like.

Those of ordinary skill in the art will appreciate that all or a portion of the steps carried out in the method of the above-described embodiments may be implemented by a program to instruct related hardware, where the program may be stored in a computer readable storage medium, and where the program, when executed, includes one or a combination of the steps of the method embodiments.

In addition, each functional unit in the embodiments of the present application may be integrated in one processing module, or each unit may exist alone physically, or two or more units may be integrated in one module. The integrated modules may be implemented in hardware or in software functional modules. The integrated modules may also be stored in a computer readable storage medium if implemented in the form of software functional modules and sold or used as a stand-alone product.

The above-mentioned storage medium may be a read-only memory, a magnetic disk or an optical disk, or the like.

In the description of the present specification, a description referring to terms "one embodiment," "some embodiments," "examples," "specific examples," or "some examples," etc., means that a particular feature, structure, material, or characteristic described in connection with the embodiment or example is included in at least one embodiment or example of the present application. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiments or examples. Furthermore, the particular features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.

While embodiments of the present application have been shown and described above, it will be understood that the above embodiments are illustrative and not to be construed as limiting the application, and that variations, modifications, alternatives and variations may be made to the above embodiments by one of ordinary skill in the art within the scope of the application.

Claims

1. The method for debugging the SQL program based on Spark is characterized by comprising the following steps of:

returning the operation result to an interface caller through the preset SQL debugging interface;

the SQL operation request information further comprises: a required interface address;

further comprises: determining the preset SQL debugging interface according to the required interface address; the preset SQL debugging interface is a user-defined restful interface service;

the step of calling an object through a corresponding preset SQL debugging interface to run the SQL program statement to be debugged to obtain a running result comprises the following steps:

calling a SparkSession object through the corresponding restful interface;

2. The method for debugging Spark-based SQL procedure of claim 1, further comprising:

storing the service task and the corresponding interface address;

and sending the service task to a user for selection.

3. The method for debugging Spark-based SQL procedure of claim 2, further comprising:

4. A Spark-based debugging device for an SQL program, comprising:

the result return module is used for returning the running result to an interface caller through the preset SQL debugging interface; the SQL operation request information further comprises: a required interface address;

further comprises: the required interface determining module is used for determining the preset SQL debugging interface according to the required interface address; the preset SQL debugging interface is a custom restful interface;

the operation module further includes:

5. A Spark-based debugging device for an SQL program, comprising:

a processor, and a memory coupled to the processor;

the memory is used for storing a computer program at least for executing the debugging method of the Spark-based SQL program according to any one of claims 1-3;

6. A storage medium storing a computer program which, when executed by a processor, implements the steps of the Spark-based SQL program debugging method of any one of claims 1-3.