UDF query method and system based on Spark SQL

Abstract

The present invention discloses a global UDF query method and system based on Spark SQL, and relates to the field of function configuration. The steps of the method include: registering the required UDF under the default library, and querying the UDF under the default library when receiving a UDF query request including a function name issued by a user. Before the UDF query, the present invention pre-registers the required UDF under the default library and uses it as a global function. If the user does not query the UDF under the current library, the user can query under the default library. Therefore, the user only needs to register a custom function under the default library, and it can be used under other databases. The user does not need to enter the database name when querying, and the query process is relatively simple.

Description

Technical Field

[0001] The present invention relates to the field of function configuration, and in particular to a global UDF (User Defined Function, a user-defined function used in an Apache Spark framework) query method and system based on Spark SQL (Structured Query Language, Structured Query Language). Background Art

[0002] According to the SQL standard, Catalog and Schema are both abstract concepts in the SQL environment. In relational databases, Catalog is a broad concept, which can usually be understood as a container or a level in the database object namespace, mainly used to resolve naming conflicts and other issues.

[0003] In the Spark SQL system, Catalog is mainly used for the unified management of various function resource information and metadata information (databases, data tables, data views, data partitions and functions, etc.).

[0004] Specifically, the Catalog system in Spark SQL is implemented with SessionCatalog as the main body, which is provided to external calls through SparkSession (Spark program entry). Generally, one SparkSession corresponds to one SessionCatalog. In essence, SessionCatalog acts as a proxy, encapsulating the underlying metadata information, temporary table information, view information, and function information. The construction parameters of SessionCatalog include 6 parts, in addition to CatalystConf and Configuration that pass in Spark SQL and Hadoop configuration information, it also involves the following 4 aspects.

[0005] GlobalTempViewManager (global temporary view management): corresponds to the commonly used createGlobalTempView method in DataFrame, performs cross-session view management, and provides atomic operations on global views, including creation, update, deletion, and renaming.

[0006] FunctionResourceLoader: In addition to the built-in functions, Spark SQL also supports user-defined functions and various functions in Hive. These functions are often provided through Jar packages or file types. FunctionResourceLoader is mainly used to load these two types of resources to provide function calls.

[0007] FunctionRegistry (Function Registration Interface): used to implement functions such as registration (Register), search (Lookup) and deletion (Drop).

[0008] ExternalCatalog (external system catalog): an interface for managing databases, tables, partitions, and functions. In Spark SQL, there are two specific implementations: InMemoryCatalog and HiveExternalCatalog. The former stores the above information in memory and is generally used for testing or relatively simple SQL processing; the latter uses the Hive original database to achieve persistent management and is widely used in production environments.

[0009] In general, SessionCatalog is the entry point for managing all the basic information mentioned above. In addition to the above construction parameters, it also includes a mutable type HashMap to manage temporary table information, and the currentDb member variable to refer to the database name corresponding to the current operation. SessionCatalog plays an important role in the entire process of Spark SQL and is used in the subsequent logical operator stage and physical operator stage.

[0010] UDF allows developers to define their own functions according to their needs and apply them to Spark's data processing flow. UDF can be used for operations such as data conversion, filtering, aggregation, and complex data processing and analysis tasks.

[0011] The UDF search process of Spark SQL includes: the user searches for the function in the specified database by the database name and function name. If a public UDF needs to be used by multiple users, there are generally two common practices:

[0012] Method 1: Register the UDF in a database and then authorize it to multiple users.

[0013] Method 2: Register the UDF in the database of each user.

[0014] Both method 1 and method 2 require the user to specify the database name; method 1 requires the user to be granted relevant permissions for the specified database, thereby increasing the permission maintenance process; method 2 requires the UDF to be registered in each user database, increasing the registration and maintenance process.

[0015] Therefore, the existing method of providing a public UDF to multiple users not only has many operation procedures, but is also not conducive to maintenance and management. Summary of the invention

[0016] In view of the defects in the prior art, the present invention solves the technical problem of how to simplify the operation process and subsequent maintenance and management work when a common UDF is provided to multiple users.

[0017] To achieve the above objectives, in a first aspect, an embodiment of the present application provides a UDF query method based on Spark SQL, comprising the following steps: registering the required UDF under a default library, and upon receiving a UDF query request including a function name from a user, querying the UDF under the default library.

[0018] In combination with the first aspect, in one implementation, when a UDF query request including a function name is received from a user, a specific process of querying the UDF in the default library includes: querying the UDF in the current library; if there is no UDF in the current library, querying the UDF in the default library.

[0019] In combination with the first aspect, in one implementation, the UDF query request also includes a database name. When the UDF query request including a function name is received from a user, the specific process of querying the UDF under the default library includes: first querying the UDF in the corresponding designated database according to the database name specified in the UDF query request; if there is no UDF under the designated database, querying the UDF under the current library; if there is no UDF under the current library, querying the UDF under the default library.

[0020] In combination with the first aspect, in one implementation, the process of querying the UDF in the default library includes: determining whether the global UDF query function is enabled, and if so, querying the UDF in the default library, and otherwise returning the query result.

[0021] In conjunction with the first aspect, in one implementation, the process of the method includes:

[0022] S1: Register the required UDF as a global function under the default library;

[0023] S2: After receiving the UDF query request from the user, determine whether the UDF query request includes the specified database name. If so, go to S3; otherwise, go to S4;

[0024] S3: Determine whether the current UDF exists in the specified database. If so, use the UDF to query; otherwise, go to S5;

[0025] S4: Determine whether the current UDF exists in the current database. If so, use the UDF to query; otherwise, go to S5;

[0026] S5: Determine whether the global UDF query function is enabled. If so, go to S6. Otherwise, report an error indicating that the current UDF does not exist.

[0027] S6: Determine whether the current UDF exists in the default library. If so, use the UDF to query. Otherwise, an error message is displayed indicating that the current UDF does not exist.

[0028] In combination with the first aspect, in one implementation, the function for querying UDF is the listFunctions function of SessionCatalog. After the listFunctions function queries the UDF, it adds the UDF to the function identifier list.

[0029] In combination with the first aspect, in one implementation, when querying a UDF, the listFunctions function determines whether the UDF identifier exists in the corresponding database through the isPersistentFunction function of SessionCatalog.

[0030] In combination with the first aspect, in one implementation, the method further includes the following steps: when an unregistered UDF is queried, the lookupFunction function of SessionCatalog registers the UDF in a registry.

[0031] In a second aspect, an embodiment of the present application provides a storage medium having a computer program stored thereon, and the computer program, when executed, implements the method provided in the first aspect.

[0032] In a third aspect, an embodiment of the present application provides a UDF query system based on Spark SQL, which is used in the method provided in the first aspect.

[0033] Compared with the prior art, the advantages of the present invention are:

[0034] Before the UDF query, the present invention pre-registers the required UDF in the default library and uses it as a global function. On this basis, if the user fails to query the UDF in the current library, he can query in the default library; the user only needs to register the custom function in the default library to use it in other databases. The user does not need to enter the database name when querying, and the query process is relatively simple. Compared with the first method of registering UDF in a certain database and then authorizing multiple users in the prior art, the present invention does not need to perform authorization operations for multiple users; compared with the second method of registering UDF in the database where each user is located in the prior art, the present invention does not need to repeatedly register UDF in multiple databases, which is convenient for maintenance and management.

[0035] Therefore, this aspect can realize the query and use requirements of UDF in multi-tenant / multi-user scenarios on the basis of simplifying the query process and maintenance process, and is suitable for promotion. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0037] Figure 1 The figure is a flowchart of a UDF query method based on Spark SQL in an embodiment of the present invention. DETAILED DESCRIPTION

[0038] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0039] The flowcharts shown in the accompanying drawings are only examples and do not necessarily include all the contents and operations / steps, nor must they be executed in the order described. For example, some operations / steps may also be decomposed, combined or partially merged, so the actual execution order may change according to actual conditions.

[0040] The Spark SQL-based UDF query method in the embodiment of the present invention includes the following steps: registering the required UDF as a global function in a default library (default), and querying the UDF in the default library when receiving a UDF query request including a function name from a user.

[0041] It can be seen that before the UDF query, the present invention pre-registers the required UDF under the default library and uses it as a global function. On this basis, if the user fails to query the UDF under the current library, he can query under the default library; the user only needs to register the custom function under the default library to use it under other databases. The user does not need to enter the database name when querying, and the query process is relatively simple. Compared with the method 1 in the prior art of registering UDF under a certain database and then authorizing multiple users, the present invention does not need to perform authorization operations for multiple users; compared with the method 2 in the prior art of registering UDF under the database where each user is located, the present invention does not need to repeatedly register UDF under multiple databases, which is convenient for maintenance and management.

[0042] Therefore, this aspect can realize the query and use requirements of UDF in multi-tenant / multi-user scenarios on the basis of simplifying the query process and maintenance process, and is suitable for promotion.

[0043] Preferably, when a UDF query request including a function name is received from a user, the specific process of querying the UDF in the default library includes: querying the UDF in the current library, and if there is no UDF in the current library, querying the UDF in the default library.

[0044] It can be seen that the present invention first searches for UDF in the current library, and if no UDF is found, searches in the default library. In actual implementation, only the query logic of the function needs to be changed, and the user can directly use the existing query statement without modifying the query statement. At the same time, such implementation can also avoid the uncertainty of functions with the same name, thereby improving the query accuracy; for example, the current library and the default library both have UDFs with the same name, but the user actually needs the UDF in the current library.

[0045] Preferably, the above-mentioned UDF query request also includes a database name. When the above-mentioned UDF query request including a function name sent by the user is received, the specific process of querying UDF under the default library includes: first querying UDF in the corresponding specified database according to the database name specified in the UDF query request; if there is no UDF under the specified database, querying UDF under the current library; if there is no UDF under the current library, querying UDF under the default library.

[0046] It can be seen that the present invention is "compatible" with the existing current library and specified library query methods based on the default library, and defines the specific query logic accordingly (first check the specified library, then check the current library, and finally check the default library).

[0047] Preferably, the above process of querying UDF under the default library includes: determining whether the global UDF query function is enabled, if so, querying UDF under the default library, otherwise returning the query result (for example, an error function does not exist); the effect of such implementation is equivalent to providing a global UDF query function switch for the user, thereby implementing different query logics according to different user needs, for example, some users only need UDF under the current library or the specified library. Therefore, the present invention is more flexible when querying, and optimizes and improves the user experience.

[0048] Preferably, the function for querying UDF is the listFunctions function of SessionCatalog, which lists all matching functions in the specified database, including temporary functions, system functions, and user-defined functions, and returns the function identifier and the scope in which it is defined. After the listFunctions function finds the UDF, it adds the UDF to the function identifier list and returns it.

[0049] Preferably, when querying UDF, the listFunctions function will use the isPersistentFunction function of SessionCatalog to determine whether the UDF identifier exists in the corresponding database. For example, when the global UDF query function is enabled, if the isPersistentFunction function does not exist in the current database, it will continue to determine whether it exists in the default database.

[0050] Preferably, the method further comprises the following steps: when an unregistered UDF is found, the lookupFunction function of the SessionCatalog registers the UDF in a registry (FunctionRegistry).

[0051] See below Figure 1 As shown, the method of the present invention is described by means of an embodiment.

[0052] S1: Register the required UDF as a global function under the default library.

[0053] S2: After receiving the UDF query request from the user, determine whether the UDF query request includes the specified database name. If so, go to S3; otherwise, go to S4.

[0054] S3: Determine whether the current UDF exists in the specified database. If so, use the UDF to query; otherwise, go to S5.

[0055] S4: Determine whether the current UDF exists in the current library. If so, use the UDF to query; otherwise, go to S5.

[0056] S5: Determine whether the global UDF query function is enabled. If so, go to S6. Otherwise, report an error indicating that the current UDF does not exist.

[0057] S6: Determine whether the current UDF exists in the default library. If so, use the UDF to query. Otherwise, an error message is displayed indicating that the current UDF does not exist.

[0058] The Spark SQL-based UDF query system in the embodiment of the present invention is used to execute the above method.

[0059] The embodiment of the present invention further provides a storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the above method is implemented. It should be noted that the storage medium includes various media that can store program codes, such as a USB flash drive, a mobile hard disk, a ROM (Read-Only Memory), a RAM (Random Access Memory), a magnetic disk or an optical disk.

[0060] An embodiment of the present invention further provides an electronic device, including a memory and a processor, wherein the memory stores a computer program that runs on the processor, and the processor implements the above method when executing the computer program.

[0061] It will be appreciated by those skilled in the art that all or some of the steps, systems, and functional modules / units in the methods disclosed above may be implemented as software, firmware, hardware, and appropriate combinations thereof. In a hardware implementation, the division between the functional modules / units mentioned in the above description does not necessarily correspond to the division of physical components; for example, a physical component may have multiple functions, or a function or step may be performed by several physical components in cooperation. Some or all physical components may be implemented as software executed by a processor, such as a central processing unit, a digital signal processor, or a microprocessor, or may be implemented as hardware, or may be implemented as an integrated circuit, such as an application-specific integrated circuit. Such software may be distributed on a computer-readable storage medium, which may include a computer-readable storage medium (or a non-transitory medium) and a communication medium (or a temporary medium).

[0062] As is known to those of ordinary skill in the art, the term computer-readable storage media includes volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storing information (such as computer-readable instructions, data structures, program modules or other data). Computer-readable storage media include, but are not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital versatile disk (DVD) or other optical disk storage, magnetic cassettes, magnetic tapes, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to store the desired information and can be accessed by a computer. In addition, it is known to those of ordinary skill in the art that communication media typically contain computer-readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transmission mechanism, and may include any information delivery medium.

[0063] Exemplarily, the computer-readable storage medium may be an internal storage unit of the electronic device of the aforementioned embodiment, such as a hard disk or memory of the electronic device. The computer-readable storage medium may also be an external storage device of the electronic device, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (SecureDigital, SD) card, a flash card (Flash Card), etc. equipped on the electronic device.

[0064] The above are only specific implementations of the embodiments of the present invention, but the protection scope of the embodiments of the present invention is not limited thereto. Any technician familiar with the technical field can easily think of various equivalent modifications or replacements within the technical scope disclosed in the embodiments of the present invention, and these modifications or replacements should be included in the protection scope of the embodiments of the present invention. Therefore, the protection scope of the embodiments of the present invention shall be based on the protection scope of the claims.

Claims

1. A UDF query method based on Spark SQL, characterized in that: The method comprises the following steps: registering a required UDF under a default library, and upon receiving a UDF query request including a function name from a user, querying the UDF under the default library; When a UDF query request including a function name is received from a user, a specific process of querying the UDF in the default library includes: querying the UDF in the current library, and if there is no UDF in the current library, querying the UDF in the default library; The UDF query request also includes a database name. When the UDF query request including a function name is received from a user, a specific process of querying the UDF in the default library includes: firstly querying the UDF in the corresponding designated database according to the database name specified in the UDF query request; if there is no UDF in the designated database, querying the UDF in the current library; if there is no UDF in the current library, querying the UDF in the default library; The process of querying UDF in the default library includes: determining whether the global UDF query function is enabled, if so, querying UDF in the default library, otherwise returning the query result.

2. The UDF query method based on Spark SQL according to claim 1, characterized in that: The process of this method includes: S1: Register the required UDF as a global function under the default library; S2: After receiving the UDF query request from the user, determine whether the UDF query request includes the specified database name. If so, go to S3; otherwise, go to S4; S3: Determine whether the current UDF exists in the specified database. If so, use the UDF to query; otherwise, go to S5; S4: Determine whether the current UDF exists in the current database. If so, use the UDF to query; otherwise, go to S5; S5: Determine whether the global UDF query function is enabled. If so, go to S6. Otherwise, report an error indicating that the current UDF does not exist. S6: Determine whether the current UDF exists in the default library. If so, use the UDF to query. Otherwise, an error message is displayed indicating that the current UDF does not exist.

3. The UDF query method based on Spark SQL according to claim 1 or 2, characterized in that: The function for querying UDF is the listFunctions function of SessionCatalog. After the listFunctions function queries the UDF, it adds the UDF to the function identifier list.

4. The Spark SQL-based UDF query method according to claim 3, characterized in that: When querying UDF, the listFunctions function will use the isPersistentFunction function of SessionCatalog to determine whether the UDF identifier exists in the corresponding database.

5. The Spark SQL-based UDF query method according to claim 4, characterized in that: The method further includes the following steps: when an unregistered UDF is found, the lookupFunction function of the SessionCatalog registers the UDF in the registry.

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

7. A UDF query system based on Spark SQL, characterized by: The system is used to execute the method according to any one of claims 1 to 5.

Images