Data processing method and device, storage medium and electronic equipment
By creating parallel database threads and analysis processes in the database system and using shared memory to pass data, the problems of Python interpreter limitations and IPC communication overhead in the database system are solved, and more efficient data processing and query performance are achieved.
Patent Information
- Application Number
- CN202510712836.2
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-05-29
- Publication Date
- 2025-09-19
AI Technical Summary
Existing database systems face performance bottlenecks when executing predictive analytical queries, affecting data processing efficiency. This is especially true because the Python interpreter's global interpreter lock mechanism limits parallelism, and data serialization and network protocol encapsulation during IPC communication cause additional overhead.
By creating parallel database threads and analysis processes in the database system and binding shared memory to each thread, the data to be analyzed is written and read directly in the shared memory, avoiding the GIL mechanism limitations of the Python interpreter and reducing the data serialization and network protocol encapsulation overhead of IPC communication.
It improves the data processing efficiency of the database system. By directly transferring data in shared memory, it reduces additional communication overhead, achieves more efficient inter-process communication, and improves the parallelism and query efficiency of the database system.
Smart Images
Figure CN120670451A_ABST
Abstract
Description
Technical Field
[0001] This specification relates to the field of computer technology, and in particular to a data processing method, device, storage medium, and electronic device. Background Art
[0002] With the rapid development of artificial intelligence (AI) technology, the use of machine learning models for data analysis and prediction has been applied across various industries. For example, a pre-trained model can be used to analyze a user's historical transaction records to predict which products the user might be interested in.
[0003] When using models to analyze data, you first need to obtain the data to be analyzed. This data may be stored in a database, so it is necessary to first export the data from the database system and then input it into the model for analysis. However, because exporting data can severely impact the efficiency of the entire data processing process, in-database machine learning technology, which deeply integrates modern analytical queries with AI technology, has emerged.
[0004] In-database machine learning allows users to execute predictive analytical queries directly through query statements. Users can write the functions that need to call the model to analyze the data directly in the query statement. The database system does not need to export the data to be analyzed from the database, and can directly complete the analysis and processing of the data in the database.
[0005] However, current database systems still have performance bottlenecks when executing the above-mentioned predictive analytical queries, which affects the efficiency of data processing. Summary of the Invention
[0006] The embodiments of this specification provide a data processing method, device, storage medium, and electronic device to partially solve the problems existing in the above-mentioned prior art.
[0007] The embodiments of this specification adopt the following technical solutions:
[0008] This specification provides a data processing method, which is applied to a database system and includes:
[0009] Get the query statement containing the user-defined function (UDF);
[0010] Executing the query statement through a pre-established database thread to determine the data to be analyzed stored in the database, and writing the data to be analyzed into a shared memory bound to the database thread;
[0011] The data to be analyzed written in the shared memory is read by the analysis process bound to the database thread, and the UDF is executed to analyze and process the read data to be analyzed.
[0012] This specification provides a data processing device, which is applied to a database system and includes:
[0013] The acquisition module is used to obtain query statements containing user-defined functions (UDFs);
[0014] A determination module, configured to execute the query statement through a pre-established database thread to determine the data to be analyzed stored in the module, and write the data to be analyzed into a shared memory bound to the database thread;
[0015] An analysis module is configured to read the data to be analyzed written into the shared memory through an analysis process bound to the database thread, and execute the UDF to analyze and process the read data to be analyzed.
[0016] This specification provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the above-mentioned data processing method is implemented.
[0017] This specification provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, the above-mentioned data processing method is implemented.
[0018] At least one of the above technical solutions adopted in the embodiments of this specification can achieve the following beneficial effects:
[0019] The embodiments of this specification disclose a data processing method. When a database system executes a query statement containing a user-defined function (UDF), the query statement is executed through a database thread to determine the data to be analyzed stored by the database system itself. The database system then writes the data to be analyzed to a shared memory bound to the database thread. The analysis process bound to the database thread then reads the data written in the shared memory and executes the UDF based on the data to analyze and process the data to be analyzed. This method improves the communication efficiency of inter-process communication (IPC) between the database thread and the analysis process, thereby improving data processing efficiency. BRIEF DESCRIPTION OF THE DRAWINGS
[0020] The drawings described herein are used to provide a further understanding of this specification and constitute a part of this specification. The exemplary embodiments and descriptions of this specification are used to explain this specification and do not constitute an improper limitation of this specification. In the drawings:
[0021] Figure 1 A flow chart of a data processing method provided in an embodiment of this specification;
[0022] Figure 2 A schematic diagram of a data processing device provided in an embodiment of this specification;
[0023] Figure 3 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this specification. DETAILED DESCRIPTION
[0024] Predictive analytics queries are allowed to include UDFs in their query statements. Users can use UDFs to call machine learning models. This allows the database to analyze the data directly within the database using the UDFs in the query statements, without having to export the data to be analyzed.
[0025] When a database system analyzes the data to be analyzed using a user-defined function (UDF) included in a query statement, it must invoke the process corresponding to the computer code written in the UDF through a database thread. This means that the database system must provide the corresponding operating environment for the UDF. UDFs written in different computer codes also require the use of a corresponding interpreter to convert the UDF code into bytecode for execution. However, different computer codes correspond to different interpreter mechanisms. Some interpreters have a Global Interpreter Lock (GIL), which restricts the use of the interpreter to only one thread at a time, as is the case with Python.
[0026] When the UDF is Python code, the database system needs to use the Python runtime process to call the Python interpreter to convert the UDF into bytecode for execution. However, because Python has the GIL mechanism, even if there are multiple database threads in the database system, only one of these threads can run the UDF. This greatly limits the query efficiency and data processing efficiency of the database system.
[0027] To this end, the embodiments of this specification can create multiple Python interpreter instances through multiple Python runtime processes, achieving mutual isolation between the multiple Python interpreter instances and overcoming the limitations of Python's GIL mechanism. However, while this approach improves the parallelism of the database system, it also introduces IPC between the database threads and the Python runtime processes.
[0028] Even if the database thread and the Python runtime process run on the same device, the data to be analyzed by the database thread still needs to be transferred to the Python runtime process via IPC before analysis can be performed. However, IPC mandates that structured data be converted into a byte stream format through serialization and deserialization during transfer. It also requires that the data to be analyzed be encapsulated according to the network layer protocol. This additional overhead can still create performance bottlenecks for the database system.
[0029] To this end, the embodiments of this specification pre-create shared memory between the database thread and the Python runtime process to improve the IPC communication efficiency between the database thread and the Python runtime process. The Python runtime process can directly read the data to be analyzed determined by the database thread through the shared memory, thereby improving the performance bottleneck of the database system.
[0030] To make the objectives, technical solutions, and advantages of this specification more clear, the following will clearly and completely describe the technical solutions of this specification in conjunction with the specific embodiments of this specification and the corresponding drawings. Obviously, the embodiments described are only part of the embodiments of this specification, not all of the embodiments. Based on the embodiments in this specification, all other embodiments obtained by ordinary technicians in this field without making any creative efforts are within the scope of protection of this specification.
[0031] The technical solutions provided by the embodiments of this specification are described in detail below with reference to the accompanying drawings.
[0032] Figure 1 A data processing method flow chart provided in an embodiment of this specification includes the following steps:
[0033] S100: Obtain a query statement including a user-defined function (UDF).
[0034] In the embodiments of this specification, Figure 1 The device for processing the data stored in the database using the method shown may be a database system, specifically any electronic device for supporting the database system, such as a server, a computer, etc. For ease of description, they are collectively referred to as database systems below.
[0035] The database system can first obtain a query statement containing a UDF, such as a query statement input by a user. This query statement can specifically be an SQL statement. It should be noted that this query statement may include not only the UDF but also other database query operators. The UDF included in the query statement can be the function itself used to analyze and process data, or it can be the name of a function pre-created by the user for data analysis and processing.
[0036] For example, the query statement containing UDF obtained by the database is as follows:
[0037] SELECT count(h.prop_id)
[0038] FROM(listings 1JOIN hotels h ON 1.prop_id=h.prop_id)
[0039] JOIN searches s ON 1.srch_id=s.srch_id
[0040] WHERE h.prop_starrating>2AND predict(data)=TRUE
[0041] The function of the above query statement is roughly as follows: in a table stored in the database, query the number of hotels with a rating greater than 2 stars and that are preferred by consumers.
[0042] In this query, clauses like count, join, and where are all called operators. A particularly special operator is predict(data). This isn't a standard function in the database, but rather a user-defined function that predicts whether a consumer prefers a particular hotel based on their data.
[0043] After the database system obtains the query statement containing the UDF, it can determine the execution plan of the query statement through the query optimizer, and execute each operator in sequence according to the execution order of each operator in the query statement included in the execution plan.
[0044] S102: executing the query statement through a pre-established database thread to determine the data to be analyzed stored in the database, and writing the data to be analyzed into a shared memory bound to the database thread.
[0045] In the embodiment of this specification, the database system may create several parallel database threads when it is started, and create an analysis process bound to each database thread and a shared memory also bound to each database thread.
[0046] Specifically, upon startup, the database system can directly create a preset number of parallel database threads and explicitly call a communication startup interface. The communication startup interface functions to detect the current number of parallel database threads in the database system, create the same number of parallel analysis processes based on this number, and allocate a specified amount of memory space within the database system's free memory as shared memory. These database threads, analysis processes, and shared memory are then bound together, with one database thread being bound to one analysis process and one shared memory. In other words, in the embodiments of this specification, there is a one-to-one correspondence between database threads, analysis processes, and shared memory.
[0047] When binding a database thread and an analysis process, the database system first determines the database thread identifier for each database thread, then creates the analysis process using the database thread identifier as the process's accessible memory identifier. In other words, the accessible memory identifier of the created analysis process is the same as the database thread identifier. A process's accessible memory identifier indicates the memory accessible to that process.
[0048] When binding a database thread to shared memory, the database system first determines the database thread's ID for each database thread, allocates a memory space of a specified size within its own free memory, and then uses the database thread's ID as the memory ID for the allocated memory space. In other words, the ID of the memory space allocated by the database system is the ID of the database thread.
[0049] It should be noted that since the database thread and the analysis process need to access the same shared memory, the operation of scanning the table in the database according to the query statement (i.e., executing other operators in the query statement except UDF) and the operation of executing the UDF in the query statement to analyze the data to be analyzed need to be performed on the same device in the embodiment of this specification.
[0050] Through the above method, for a database thread, the accessible memory identifier of the analysis process created for the database thread is consistent with the identifier of the memory space allocated for the database thread, both of which are identifiers of the database thread. This means that in addition to being accessible to the database thread, the analysis process can also access the memory space through its accessible memory identifier. At this time, the memory space becomes a shared memory accessible to both the database thread and the analysis process, and the three are also bound one-to-one through the shared memory or the identifier of the database thread.
[0051] In step S102, when the database system executes each operator in the query statement according to the execution plan, it can determine the data to be analyzed stored in the database system by executing each operator before the operator containing the UDF. The data to be analyzed in the embodiments of this specification refers to the data that needs to be analyzed and processed by the UDF contained in the query statement.
[0052] After the data to be analyzed is determined, it can be written to a pre-created shared memory bound to the database thread executing the query statement. Specifically, based on the identifier of the database thread executing the query statement, the database system can determine, within each pre-created shared memory, the memory space identified by the identifier of the database thread, as the shared memory bound to the database thread, and then write the data to be analyzed into the determined shared memory.
[0053] Continuing with the previous example, the operator in the UDF is predict(data) = TRUE. Based on the other operators in the query, the database system determines that the data to be analyzed by the UDF is hotels with a rating greater than 2 in the database table named 1. Therefore, the database system writes this data to the shared memory bound to the database thread executing the query.
[0054] S104: Reading the data to be analyzed written into the shared memory through the analysis process bound to the database thread, and executing the UDF to analyze and process the read data to be analyzed.
[0055] After writing the data to be analyzed into the shared memory, the database system can read the data to be analyzed written into the shared memory bound to the database thread through the analysis process bound to the database thread when executing a query statement containing an operator containing a UDF. Specifically, the database system can use the accessible memory identifier of the analysis process bound to the database thread to determine, within each pre-created shared memory, the memory space identified by the accessible memory identifier as the shared memory bound to the database, and then use the analysis process to read the data to be analyzed written in step S102 from the determined shared memory.
[0056] After reading the data to be analyzed in the shared memory, the database system can pass the read data to be analyzed as the input parameter of the UDF in the query statement through the analysis process, and then call the corresponding interpreter through the analysis process, that is, through the analysis process, create an interpreter instance within the analysis process, convert the UDF after the parameter is passed into bytecode through the interpreter instance, and execute the bytecode to realize the analysis and processing of the data to be analyzed.
[0057] It should be noted that the analysis process pre-created by the database system must be created based on the computer language used to write the UDF. In other words, the created analysis process must provide an operating environment for the UDF based on the computer language used to write the UDF. All operations in step S104 are performed by the database system through the analysis process, while all operations in step S102 are performed by the database system through the database thread.
[0058] Continuing with the previous example, the operator containing the UDF is predict(data) = TRUE. However, predict() is only the function name, not the UDF function itself. Therefore, the database system can first search for the predict() function in its own database standard function library. Since the predict() function is a user-defined function, not a database standard function, the database system cannot find it in its own database standard function library and must search for it in the user-defined function list. The predict() function found is shown below:
[0059] CREATE PYTHON_UDF predict(data)RETURNS INTEGER{
[0060] #Inference context setup
[0061] import pickle,joblib,pypmml.Model
[0062] import numpy as np
[0063] from sklearn.preprocessing import StandardScaler
[0064] from sklearn.preprocessing import OneHotEncoder
[0065] from sklearn.tree import DecisionTreeClassifier
[0066] with open(' / path / to / scaler','rb')as f:
[0067] scaler = pickle.load(f)
[0068] with open(' / path / to / one_hot','rb')as f:
[0069] enc=joblib.load(f)
[0070] with open(' / path / to / qt_model','rb')as f:
[0071] model=pypmml.Model.fromFile(f)
[0072] #Data preprocessing
[0073] data = np.column_stack(data)
[0074] numerical,categorical=np.split(data,np.array([8]),1)
[0075] X=np.hstack((scaler.transform(numerical),enc.transform(categorical).toarray()))
[0076] #Model inference invocation
[0077] return model.predict(X)}
[0078] The function of the above UDF written in Python is to predict whether a consumer is interested in a certain hotel using a pre-trained decision tree model. The database system then uses the analysis process (i.e., the Python runtime process) to read the data to be analyzed from shared memory as the input parameter of the above predict() function. The Python runtime process then calls the Python interpreter, converts the passed predict() function into bytecode, and executes it to obtain the analysis result of whether the consumer is interested in each hotel with a level greater than 2 in the table named 1. The analysis result is TRUE or FALSE.
[0079] If the database system needs to execute other subsequent operators in the query statement after executing the UDF included in the query statement, the analysis results of the UDF on the data to be analyzed must be used as input parameters for the subsequent operators of the above UDF in the query statement. The subsequent operators are then executed through the database thread until the above query statement is completely executed.
[0080] Continuing with the above example, after the database system executes the operator predict(data) = TRUE in the query statement containing the UDF, it can continue to execute the count(h.prop_id) operator according to the execution plan. The result is the number of hotels in the table named 1 that have a rating greater than 2 and are of interest to the consumer.
[0081] Through the above method, the database thread can directly write the data to be analyzed into the shared memory for reading by the analysis process, which can improve the efficiency of IPC communication. There is no need to introduce additional overhead such as data serialization and deserialization, network layer protocol encapsulation, etc., which can effectively improve the data processing efficiency of the database system.
[0082] Moreover, the database system also creates analysis processes that are bound to each database thread. Each analysis process can independently create its own interpreter instance within the process, achieving separate isolation of each interpreter and avoiding the parallelism limitations brought by the GIL mechanism.
[0083] Furthermore, because the analysis process and the database thread actually need to execute different codes—the database thread executes SQL code, while the analysis process actually executes the computer language used to write user-defined functions (UDFs)—the data formats they can parse may not be exactly the same. Therefore, in step S102, when the database system writes the data to be analyzed into the corresponding shared memory via the database thread, it can convert the data to be analyzed into a common format that can be parsed by both the database thread and the analysis process, and then write the converted data into the corresponding shared memory.
[0084] Specifically, when the UDF code is written in Python, the analysis process pre-created by the database system may be a Python runtime process, and the above-mentioned general format includes the Arrow format.
[0085] It should be noted that although the SQL thread (i.e., database thread) and the Python runtime process can parse more than just Arrow format, when the SQL thread converts the data to be analyzed into Arrow format, it only needs to use the corresponding Arrow API to directly convert all the data to be analyzed into Arrow format and write it to shared memory. This does not require reading each piece of data to be analyzed one by one and then converting the format one by one. Therefore, converting the data to Arrow format can also improve data processing efficiency.
[0086] In the embodiment of this specification, when the database system is shut down, in order to ensure the stability of the system, all created shared memories may be released first, then all created analysis processes may be terminated, and finally the database system may be shut down.
[0087] The above is a data processing method provided in an embodiment of this specification. Based on the same idea, this specification also provides corresponding devices, storage media and electronic devices.
[0088] Figure 2 This is a schematic diagram of a data processing device provided in an embodiment of this specification, wherein the device is applied to a database system and includes:
[0089] The acquisition module 201 is used to acquire a query statement containing a user-defined function (UDF);
[0090] The determination module 202 is configured to execute the query statement through a pre-established database thread to determine the data to be analyzed stored in the database, and write the data to be analyzed into a shared memory bound to the database thread;
[0091] The analysis module 203 is configured to read the data to be analyzed written into the shared memory through the analysis process bound to the database thread, and execute the UDF to analyze and process the read data to be analyzed.
[0092] Optionally, the device further comprises:
[0093] The creation module 200 is used to create several database threads when the device is started; for each database thread, create an analysis process bound to the database thread and a shared memory bound to the database thread.
[0094] Optionally, the creation module 200 is specifically used to create an analysis process using the identifier of the database thread as the accessible memory identifier of the process; divide a memory space of a specified size in the free memory of the device, and use the identifier of the database thread as the memory identifier of the divided memory space of the specified size.
[0095] Optionally, the writing module 202 is specifically configured to convert the data to be analyzed into data in a common format that can be parsed by both the database thread and the analysis process; and write the converted data in the common format into a shared memory bound to the database thread.
[0096] Optionally, the UDF includes Python code;
[0097] The analysis process includes a Python runtime process;
[0098] The general format includes the Arrow format.
[0099] Optionally, the analysis module 203 is specifically configured to pass the read data to be analyzed into the UDF as an input parameter of the UDF; through the analysis process, call the Python interpreter corresponding to the analysis process, convert the passed UDF into bytecode, and execute it.
[0100] Optionally, the analysis module 203 is further configured to pass the analysis result of the data to be analyzed into the subsequent operator as an input parameter of the subsequent operator of the UDF in the query statement; and continue to execute the subsequent operator in the query statement until the query statement is executed.
[0101] This specification also provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, it can be used to execute the data processing method provided above.
[0102] Based on the above data processing method, the embodiment of this specification also provides Figure 3 The structural diagram of the electronic device shown in FIG. Figure 3 At the hardware level, the electronic device includes a processor, an internal bus, a network interface, memory, and non-volatile storage, and may also include other hardware required for its operations. The processor reads the corresponding computer program from the non-volatile storage into the memory and then runs it to implement the above-mentioned data processing method.
[0103] The foregoing is merely an example of the present invention and is not intended to limit the present invention. Various modifications and variations are possible for those skilled in the art. Any modifications, equivalent substitutions, or improvements made within the spirit and principles of the present invention are intended to be included within the scope of the claims of the present invention.
Claims
1. A data processing method, applied to a database system, comprising: Get the query statement containing the user-defined function (UDF); Executing the query statement through a pre-established database thread to determine the data to be analyzed stored in the database, and writing the data to be analyzed into a shared memory bound to the database thread; The data to be analyzed written in the shared memory is read by the analysis process bound to the database thread, and the UDF is executed to analyze and process the read data to be analyzed.
2. The method according to claim 1, further comprising: When the database system is started, several database threads are created; For each database thread, an analysis process bound to the database thread and a shared memory bound to the database thread are created.
3. The method of claim 2, wherein creating an analysis process bound to the database thread comprises: Create an analysis process using the database thread ID as the process's accessible memory ID. Create shared memory bound to the database thread, including: A memory space of a specified size is divided out from the free memory of the database system, and the identifier of the database thread is used as a memory identifier of the divided memory space of the specified size.
4. The method according to claim 2, wherein the step of writing the data to be analyzed into a shared memory bound to the database thread comprises: Converting the data to be analyzed into data in a common format that can be parsed by both the database thread and the analysis process; The converted data in the universal format is written into a shared memory bound to the database thread.
5. The method of claim 4, wherein the UDF comprises Python code; The analysis process includes a Python runtime process; The general format includes the Arrow format.
6. The method according to claim 5, wherein executing the UDF comprises: Passing the read data to be analyzed into the UDF as an input parameter of the UDF; Through the analysis process, the Python interpreter corresponding to the analysis process is called, the UDF after the parameter is passed is converted into bytecode, and then executed.
7. The method of claim 1, further comprising: The analysis result of the data to be analyzed is passed to the subsequent operator of the UDF in the query statement as an input parameter of the subsequent operator; Continue executing the subsequent operators in the query statement until the query statement is completed.
8. A data processing device, applied to a database system, comprising: The acquisition module is used to obtain the query statement containing the user-defined function UDF; A determination module, configured to execute the query statement through a pre-established database thread to determine the data to be analyzed stored in the module, and write the data to be analyzed into a shared memory bound to the database thread; An analysis module is configured to read the data to be analyzed written into the shared memory through an analysis process bound to the database thread, and execute the UDF to analyze and process the read data to be analyzed.
9. A computer-readable storage medium storing a computer program, wherein the computer program implements the method according to any one of claims 1 to 7 when executed by a processor.
10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor implements the method according to any one of claims 1 to 7 when executing the program.