Structured query statement processing method and device and computer equipment

Abstract

The invention relates to a structured query statement processing method and device and computer equipment. The method comprises the following steps: packaging a structured query statement in a job package based on a self-defined application program, wherein the self-defined application program is used for receiving and processing the structured query statement; and submitting the job package to a resource manager for execution in a cluster mode based on a command line tool, wherein the job package comprises the parameters of the structured query statement and the parameters of the command line tool. By adopting the method, the load of a client machine can be reduced.

Description

Technical Field

[0001] The present application relates to the technical field of big data processing, and in particular to a method, device, and computer equipment for processing structured query statements. Background Art

[0002] In big data development, you want to submit structured query statements directly through command line tools, but structured query statements do not support cluster mode, which requires starting working nodes locally, occupying a large amount of memory, increasing server load, and reducing system stability. Summary of the invention

[0003] Based on this, it is necessary to provide a method, device and computer equipment for processing structured query statements in response to the above technical problems.

[0004] In a first aspect, the present application provides a method for processing a structured query statement, comprising:

[0005] Encapsulating the structured query statement in a job package based on a custom application, wherein the custom application is used to receive and process the structured query statement;

[0006] The job package is submitted to the resource manager in cluster mode for execution based on a command line tool, and the job package includes parameters of a structured query statement and parameters of the command line tool.

[0007] In one embodiment, the customized application includes a cluster resource manager, a working node for running a job package, an application task control node, and an execution process on the working node responsible for a specific task.

[0008] In one embodiment, submitting the job package to the resource manager in cluster mode for execution based on a command line tool includes:

[0009] In the cluster mode, the command line tool starts a task control node on any node in the cluster.

[0010] In one embodiment, the customized application includes:

[0011] A main class, wherein the main class is used to receive a structured query statement as a parameter.

[0012] In one embodiment, the method further comprises:

[0013] The task identifier of the resource manager is parsed by a regular expression to obtain the execution log of the resource manager.

[0014] In one embodiment, the method further comprises:

[0015] Abnormal data in the execution log is identified and located based on the execution log.

[0016] In a second aspect, the present application also provides a device for processing structured query statements, comprising:

[0017] A packaging module, used to package the structured query statement in a job package based on a custom application program, wherein the custom application program is used to receive and process the structured query statement;

[0018] The execution module is used to submit the job package to the resource manager for execution in cluster mode based on the command line tool, and the job package includes parameters of the structured query statement and parameters of the command line tool.

[0019] In one embodiment, the customized application includes a cluster resource manager, a working node for running a job package, an application task control node, and an execution process on the working node responsible for a specific task.

[0020] In one embodiment, submitting the job package to the resource manager in cluster mode for execution based on a command line tool includes:

[0021] In the cluster mode, the command line tool starts a task control node on any node in the cluster.

[0022] In one embodiment, the customized application includes:

[0023] A main class, wherein the main class is used to receive a structured query statement as a parameter.

[0024] In one embodiment, the device further comprises:

[0025] The task identifier of the resource manager is parsed by a regular expression to obtain the execution log of the resource manager.

[0026] In one embodiment, the device further comprises:

[0027] Abnormal data in the execution log is identified and located based on the execution log.

[0028] In a third aspect, the present disclosure further provides a computer device, wherein the computer device comprises a memory and a processor, wherein the memory stores a computer program, and when the processor executes the computer program, the steps of the method for processing structured query statements are implemented.

[0029] In a fourth aspect, the present disclosure further provides a computer-readable storage medium having a computer program stored thereon, and when the computer program is executed by a processor, the steps of the method for processing structured query statements are implemented.

[0030] In a fifth aspect, the present disclosure further provides a computer program product, which includes a computer program, and when the computer program is executed by a processor, the steps of the method for processing structured query statements are implemented.

[0031] The method, device, and computer device for processing structured query statements can encapsulate structured query statements in a job package by writing a custom application, and submit it to the cluster in cluster mode through a command line tool. The command line tool starts a working node (driver) process in the cluster, indicating that the driver is also running on a node in the cluster, rather than on the client machine that submits the job, which reduces the load on the client machine and allows the entire job to run completely within the cluster. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0033] Figure 1 An application environment diagram of a method for processing structured query statements in an embodiment;

[0034] Figure 2 A schematic diagram of a flow chart of a method for processing a structured query statement in an embodiment;

[0035] Figure 3 is a structural block diagram of a device for processing structured query statements in one embodiment;

[0036] Figure 4 is an internal structure diagram of a computer device in one embodiment;

[0037] Figure 5 The figure is a diagram of the internal structure of a server in one embodiment. DETAILED DESCRIPTION

[0038] In order to make the purpose, technical solution and advantages of the present application more clearly understood, the present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not used to limit the present application.

[0039] The method for processing structured query statements provided in the embodiments of the present application can be applied to Figure 1 In the application environment shown. Among them, the terminal 102 communicates with the server 104 through the network. The data storage system can store the data that the server 104 needs to process. The data storage system can be integrated on the server 104, or it can be placed on the cloud or other network servers. Among them, the terminal 102 can be, but is not limited to, various personal computers, laptops, smart phones, tablets, Internet of Things devices and portable wearable devices. The Internet of Things devices can be smart speakers, smart TVs, smart air conditioners, smart car devices, projection devices, etc. Portable wearable devices can be smart watches, smart bracelets, head-mounted devices, etc. The head-mounted device can be a virtual reality (VR) device, an augmented reality (AR) device, smart glasses, etc. The server 104 can be an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides cloud computing services.

[0040] In an exemplary embodiment, Figure 2 As shown, a method for processing a structured query statement is provided, comprising the following steps. Among them:

[0041] S202: Encapsulating the structured query statement in a job package based on a custom application, where the custom application is used to receive and process the structured query statement.

[0042] The customized application can be Spark, which is a fast, general, and scalable big data analysis engine. Spark is based on in-memory computing, which significantly improves the real-time performance of data processing in a big data environment while ensuring high fault tolerance and high scalability.

[0043] Customized applications are used to receive and process structured query statements. Receiving structured query statements means that the application has an interface or mechanism to receive SQL or HQL query statements (structured query statements) from external sources, such as command line parameters, configuration files, API (Application Programming Interface) requests, etc., which can be data operation instructions that users want to perform on large data sets. Processing structured query statements means that after receiving the query statements, the application will parse these statements and use built-in logic or integrated libraries, such as Apache SparkSQL (Spark application structured query statements) to compile and optimize them so that they can be efficiently executed in a distributed computing environment.

[0044] The processed query statements are converted into one or more independently executable tasks or jobs, which can be packaged into a JAR file (job package). This JAR file contains the association relationship of all query statements and can be submitted to the cluster management system, such as YARN (cluster management system), as an independent unit for operation.

[0045] S204: Submitting the job package to a resource manager in a cluster mode for execution based on a command line tool, wherein the job package includes parameters of a structured query statement and parameters of the command line tool.

[0046] The command line tool can be spark-submit, which is a tool for launching Spark applications. It can configure many parameters, such as specifying the main class, setting runtime environment variables, parameters passed to the application, etc. When using spark-submit, different deployment modes can be specified, including local mode and cluster mode.

[0047] Resource manager Yarn is a general resource management system that provides unified resource management and scheduling for upper-layer applications, improving the benefits of cluster utilization, unified resource management, and data sharing. YARN can separate the two main functions of JobTracker (background service process), resource management and job scheduling / monitoring, and create a global ResourceManager (RM, resource management) and several ApplicationMasters (application managers) for applications.

[0048] Submit the job package to the cluster resource manager (Apache YARN) through the command line tool (spark-submit) and choose to run in cluster mode. In this mode, the Driver program is also started on the cluster node instead of on the client machine where the job is submitted. This can fully utilize cluster resources and reduce the impact on client machines.

[0049] In the above structured query processing method, by writing a custom application, the structured query statement query can be encapsulated in a job package and submitted to the cluster in cluster mode through the command line tool. The command line tool will start the working node (driver) process in the cluster, which means that the driver is also running on a node in the cluster, rather than on the client machine that submits the job, which reduces the load on the client machine and allows the entire job to run completely within the cluster.

[0050] In an exemplary embodiment, the customized application includes a cluster resource manager, a working node for running a job package, an application task control node, and an execution process on the working node responsible for a specific task.

[0051] The cluster resource manager is a system responsible for managing and allocating resources to all worker nodes in the cluster. It can decide which nodes should run what tasks and ensure that these tasks get the required computing resources.

[0052] Worker nodes are physical or virtual machines in the cluster that actually execute the tasks assigned to them. Multiple executors can run on each worker node, and each executor is responsible for processing a portion of the data. When a job is submitted to the cluster, the resource manager will start executors on different worker nodes as needed to parallelize task execution and improve efficiency.

[0053] In resource management systems such as YARN, the Application Master can be the Driver of the Spark application, responsible for communicating with the resource manager, requesting resources, and coordinating the execution process of the entire application. For Spark applications, the Driver node not only manages task scheduling, but is also responsible for parsing query statements, optimizing execution plans, collecting final results, etc. When deployed in cluster mode, the Driver will also run on a working node in the cluster, rather than on the client machine that submits the job.

[0054] In the above custom Spark application, the structured query statements are packaged into a JAR file and submitted to the cluster resource manager YARN through the spark-submit command. The resource manager selects the appropriate worker node to start the Driver process, and the Driver further coordinates the task allocation to the execution process on each worker node. The execution process is responsible for executing specific SQL / HQL query tasks, processing data, and feeding back the results to the Driver. Finally, the Driver collects all the results and provides output to the user.

[0055] In an exemplary embodiment, submitting the job package to the resource manager in cluster mode for execution based on a command line tool includes:

[0056] In the cluster mode, the command line tool starts a task control node on any node in the cluster.

[0057] The command line tool sends a submission request containing all necessary configurations and parameters to the resource manager. The resource manager selects the most suitable node to start the task control node based on the current cluster status and starts the task control node on the selected node, which can be any node in the cluster with sufficient resources.

[0058] In an exemplary embodiment, the customized application includes:

[0059] A main class, wherein the main class is used to receive a structured query statement as a parameter.

[0060] The job package can support all parameters supported by sparks-ql and does not affect the parameters of spark-submit.

[0061] In an exemplary embodiment, the method further comprises:

[0062] The task identifier of the resource manager is parsed by a regular expression to obtain the execution log of the resource manager.

[0063] In an exemplary embodiment, the method further comprises:

[0064] Abnormal data in the execution log is identified and located based on the execution log.

[0065] Submit the job to the YARN cluster. Use the spark-submit command to submit the JAR file packaged with the Spark application to the YARN cluster. The spark-submit output will contain a unique identifier called the application ID, which is used to track and manage application instances in YARN. You can obtain log information related to the application based on the application ID.

[0066] It should be understood that, although the various steps in the flowcharts involved in the above-mentioned embodiments are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence according to the order indicated by the arrows. Unless there is a clear explanation in this article, the execution of these steps does not have a strict order restriction, and these steps can be executed in other orders. Moreover, at least a part of the steps in the flowcharts involved in the above-mentioned embodiments can include multiple steps or multiple stages, and these steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a part of the steps or stages in other steps.

[0067] Based on the same inventive concept, the embodiment of the present application also provides a structured query statement processing device for implementing the structured query statement processing method involved above. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme recorded in the above method, so the specific limitations in the embodiments of the one or more structured query statement processing devices provided below can refer to the limitations of the structured query statement processing method above, and will not be repeated here.

[0068] In an exemplary embodiment, Figure 3 As shown, a structured query statement processing device 300 is provided, comprising:

[0069] The encapsulation module 302 is used to encapsulate the structured query statement in a job package based on a custom application program, wherein the custom application program is used to receive and process the structured query statement;

[0070] The execution module 304 is used to submit the job package to the resource manager for execution in cluster mode based on the command line tool, and the job package includes parameters of the structured query statement and parameters of the command line tool.

[0071] In one embodiment, the customized application includes a cluster resource manager, a working node for running a job package, an application task control node, and an execution process on the working node responsible for a specific task.

[0072] In one embodiment, submitting the job package to the resource manager in cluster mode for execution based on a command line tool includes:

[0073] In the cluster mode, the command line tool starts a task control node on any node in the cluster.

[0074] In one embodiment, the customized application includes:

[0075] A main class, wherein the main class is used to receive a structured query statement as a parameter.

[0076] In one embodiment, the device further comprises:

[0077] The task identifier of the resource manager is parsed by a regular expression to obtain the execution log of the resource manager.

[0078] In one embodiment, the device further comprises:

[0079] Abnormal data in the execution log is identified and located based on the execution log.

[0080] Each module in the above-mentioned structured query statement processing device can be implemented in whole or in part by software, hardware, or a combination thereof. Each of the above-mentioned modules can be embedded in or independent of a processor in a computer device in the form of hardware, or can be stored in a memory in a computer device in the form of software, so that the processor can call and execute operations corresponding to each of the above modules.

[0081] In an exemplary embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as shown in FIG. Figure 4 As shown. The computer device includes a processor, a memory, an input / output interface (Input / Output, referred to as I / O) and a communication interface. The processor, the memory and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store job packages. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a method for processing a structured query statement is implemented.

[0082] In an exemplary embodiment, a computer device is provided. The computer device may be a terminal, and its internal structure diagram may be as shown in FIG. Figure 5 As shown. The computer device includes a processor, a memory, an input / output interface, a communication interface, a display unit and an input device. The processor, the memory and the input / output interface are connected via a system bus, and the communication interface, the display unit and the input device are connected to the system bus via the input / output interface. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The input / output interface of the computer device is used to exchange information between the processor and an external device. The communication interface of the computer device is used to communicate with an external terminal in a wired or wireless manner, and the wireless manner can be achieved through WIFI, a mobile cellular network, near field communication (NFC) or other technologies. When the computer program is executed by the processor, a method for processing a structured query statement is implemented. Those skilled in the art can understand that Figure 4 , Figure 5 The structure shown in the figure is only a block diagram of a part of the structure related to the solution of the present application, and does not constitute a limitation on the computer device to which the solution of the present application is applied. The specific computer device may include more or fewer components than those shown in the figure, or combine certain components, or have a different arrangement of components.

[0083] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant regulations.

[0084] A person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment method can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. As an illustration and not limitation, RAM can be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM). The database involved in each embodiment provided in this application may include at least one of a relational database and a non-relational database. Non-relational databases may include distributed databases based on blockchains, etc., but are not limited to this. The processor involved in each embodiment provided in this application may be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic device, a data processing logic device based on quantum computing, an artificial intelligence (AI) processor, etc., but are not limited to this.

[0085] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0086] The above-described embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the present application. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the attached claims.

Claims

1. A method for processing a structured query statement, characterized in that: The method comprises: Encapsulating the structured query statement in a job package based on a custom application, wherein the custom application is used to receive and process the structured query statement; The job package is submitted to the resource manager in cluster mode for execution based on a command line tool, and the job package includes parameters of a structured query statement and parameters of the command line tool.

2. The method according to claim 1, characterized in that The customized application includes a cluster resource manager, a working node for running a job package, an application task control node, and an execution process on the working node responsible for specific tasks.

3. The method according to claim 1, characterized in that: Submitting the job package to the resource manager in cluster mode for execution based on a command line tool includes: In the cluster mode, the command line tool starts a task control node on any node in the cluster.

4. The method according to claim 1, characterized in that The custom applications include: A main class, wherein the main class is used to receive a structured query statement as a parameter.

5. The method according to claim 1, characterized in that The method further comprises: The task identifier of the resource manager is parsed by a regular expression to obtain the execution log of the resource manager.

6. The method according to claim 5, characterized in that The method further comprises: Abnormal data in the execution log is identified and located based on the execution log.

7. A device for processing structured query statements, characterized in that: The device comprises: A packaging module, used to package the structured query statement in a job package based on a custom application program, wherein the custom application program is used to receive and process the structured query statement; The execution module is used to submit the job package to the resource manager for execution in cluster mode based on the command line tool, and the job package includes parameters of the structured query statement and parameters of the command line tool.

8. A computer device comprising a memory and a processor, wherein the memory stores a computer program, wherein: When the processor executes the computer program, the steps of the method according to any one of claims 1 to 6 are implemented.

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

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

Images