Method for executing sql (structured query language) in green database in parallel

Abstract

The invention discloses a method for executing sql (structured query language) in parallel in a green database. The method provided by the invention comprises the following steps: step 1, installing plpython extension for the green plums; 1-1, when the installation is started from the greenplum7, plpython is carried by the greenplum7, and a source code needs to be downloaded by the greenplum7 to be compiled and installed by the greenplum7; and 1-2, after installation, expansion of python3 is created through create extension plpython3u, so that a storage process of gp can be written through python subsequently and conveniently. And step 2, writing a common function, and realizing parallel calling of multiple sql statements. 2-1, defining a common function; 2-2, acquiring database connection information; 2-3, setting the database to be free from the own IP password; 2-4, assembling a psql command execution function; and 2-5, starting the thread pool to execute parallel execution. According to the method, a database administrator only needs to create an exesqls common function at one time, other developers can realize multi-sql parallel execution which cannot be supported by the database through simple sql statements, and other languages such as python are not needed.

Description

Technical Field

[0001] This application relates to the field of information technology, and particularly to a method for parallel execution of SQL in a Greenplum database. Background Art

[0002] The advent of the "big data" era has had an impact that cannot be ignored on the development of society and humanity. More and more industries are shifting towards data-driven, and data has become the "new" productive force. From clothing, food, shelter, and transportation to entertainment, education, medicine, and healthcare, data has penetrated all aspects of our lives and work. Technologies such as machine learning and artificial intelligence that rely on the development of big data are even more popular. In this era of explosive growth of data information, rapid data collection and accurate data mining provide the necessary conditions for efficient data application; as data assets grow, the migration and synchronization of data in different cluster environments are the key points that database operation and maintenance personnel and developers need to focus on.

[0003] The big data industry is developing rapidly. For configuration data such as rule engines, each rule has a database record. If there are multiple rules, when parsing these rules, each rule is a SQL statement. Even when multiple SQL statements are submitted to the database for execution together, they are executed serially. Using the pg_sleep function to simulate 3 SQL statements with different durations, it can be seen that the execution duration is 6 seconds, and these 3 SQL statements are executed serially. To execute in parallel, conventional SQL cannot achieve this, and multi-threaded concurrency technology of other programming languages such as Python needs to be used. However, in this way, all external functions originally provided in the form of SQL stored procedures cannot be applied, and all calling methods need to be modified, which seriously violates the principles of function cohesion and decoupling. Since databases with pg kernels such as Greenplum support plpython, writing stored procedures in Python, but directly encapsulating the above code into a plpy stored procedure for calling is not feasible because multi-threading within the stored procedure is not supported, which will cause the cluster to crash and enter the recovery mode.

[0004] In summary, the present invention has the following requirements for the concurrent execution of SQL in a Greenplum database: a convenient parallel method, easy deployment, and no risk of out-of-control permissions. Summary of the Invention

[0005] To solve the problems in the background art, the present invention proposes a method for parallel execution of SQL in a Greenplum database.

[0006] The technical solution adopted by the present invention is:

[0007] Step 1: Install the plpython extension for Greenplum.

[0008] 1-1. Since plpython comes with the installation of Greenplum 7, Greenplum needs to be downloaded and installed by compiling the source code.

[0009] 1-2. After installation, create an extension for Python 3 through "create extension plpython3u" to facilitate writing Greenplum stored procedures in Python later.

[0010] Step 2: Write a common function to call multiple SQL statements in parallel.

[0011] 2-1. Define the common function: exesqls(sql_array text[], parallels_num int). This function has two input parameters. sql_array passes in multiple SQL statements to be executed, passed in as an array of SQL statements, and parallels_num is the degree of parallelism. For example, if 10 SQL statements are passed in and 4 degrees of parallelism are enabled, that is, 4 SQL statements are executed simultaneously, and when a task ends, another subsequent SQL statement is pulled up for execution.

[0012] This function is the core step of the present invention. Because SQL itself and stored procedures are single-threaded and do not have the ability to execute multiple SQL statements natively, in fact, parallelism is achieved through other means, but it is encapsulated in this function to enable other developers to use SQL parallelism more easily.

[0013] 2-2. Obtain database connection information;

[0014] Since SQL statements need to be executed through means other than SQL, database connection information is required. There are relevant functions in SQL to obtain the connection information of the current user, including IP, port, dbname, and username. Only the password cannot be directly obtained through relevant functions. Therefore, a password-free method needs to be used.

[0015] 2-3. Set the database to be password-free for its own IP;

[0016] The SQL statements executed by the exesqls function are executed in the background, that is, all within the Greenplum database. Therefore, in the pg_hba.conf file, set all usernames for 127.0.0.1 to be password-free. This is inherently secure, and the master-slave synchronization and backup of PostgreSQL itself are also implemented in this way.

[0017] 2-4. Assemble the psql command execution function;

[0018] Multiple methods have been tried, such as using psycopg2 in Python to execute. However, it is difficult to pass log information such as the internal print in Python to the plpython function, and it can only be output all at once after execution, making it impossible to obtain the real-time situation of the function, which is very inconvenient for development and debugging.

[0019] When the built-in command-line tool psql of Greenplum is executed, a separate database connection will be opened. In this way, it is possible to use the command-line tool psql to execute multiple SQL statements simultaneously, and the raisenotice log in the SQL can be passed to plpython in real time through subprocess.

[0020] 2-5. Start the thread pool to execute in parallel

[0021] Use the ThreadPoolExecutor in Python to start multiple threads, and each thread corresponds to a command-line tool psql to run an SQL statement, so as to complete the parallel execution of multiple input SQL statements in multiple threads.

[0022] Furthermore, after each thread completes the currently executed SQL statement, it will seamlessly execute the next SQL statement.

[0023] Furthermore, before the multiple SQL statements of the present invention are passed into the function, they need to be sorted from high to low according to the priority of each SQL statement, and then the sorted multiple SQL statements are passed into the function.

[0024] Furthermore, the priority of the SQL is set as needed, generally divided by running time, and the longer the running time, the higher the priority.

[0025] Advantages of the present invention:

[0026] For the present invention, the database administrator only needs to create the exesqls public function once, and other developers can use simple SQL statements to achieve the parallel execution of multiple SQLs that the database itself does not support, without the need to use other languages such as Python. Moreover, some interfaces are themselves called by others through SQL stored procedures, and in this case, it is impossible to implement them through third-party programming languages, or after implementation, they need to be repackaged into SQL stored procedures, resulting in a "nested dolls" phenomenon, which not only increases the development difficulty, but also multiple levels of nesting will affect the performance of the SQL.

[0027] In actual business, there are many scenarios that require calculating multiple business parameters. By slightly modifying the exesqls method, first execute the common logic of these business parameters, and then execute them in parallel. By starting 8 parallel executions, the duration is reduced to about 1 / 7 to 1 / 8 of the original.

[0028] Furthermore, there are some SQL statements that were originally a single one. However, due to the extremely large amount of data, reaching tens of millions or even hundreds of millions of records, they are directly split into multiple SQL statements through hash sharding and then executed in parallel using exesqls. This results in a much better performance improvement. Executing a single SQL statement with hundreds of millions of records takes a long time, several minutes or even longer. By splitting it into multiple SQL statements of around 1 - 2 million records each, it only takes a few seconds. However, if the split statements can only be executed serially, the improvement effect is not as obvious. In an actual scenario, there was a large SQL statement that took about 1 minute to execute. After splitting and parallelizing, it took less than 3 seconds, achieving a more than 20 - fold improvement. Detailed implementation

[0029] The present invention will be further described below in conjunction with embodiments.

[0030] To achieve a balance between permission control and convenient deployment, the plpy function stored procedure only implements "tool - like" functions. The exesqls function is deployed once by the database administrator (functions written in plpython can only be created by the administrator, and ordinary users can only call them). Other users only need to write ordinary SQL.

[0031] The programming code of the present invention is as follows:

[0032]

[0033]

[0034] Example 1:

[0035] Call test, 2 - way parallel:

[0036]

[0037]

[0038] The script starts with 2 parallel degrees for 3 incoming SQL statements. The 3 SQL statements wait for 3 s, 2 s, and 1 s respectively. The 2 threads execute the tasks that take 3 s and 2 s. When the 2 - s task ends, the 1 - s task is launched. The final execution result is 3 s. The execution duration for 2 - way parallel only takes 3 s, meeting the expectations.

[0039] Example 2:

[0040] Based on the program code of Example 1, but in the real scenario, it is not simply executing pg_sleep. For example, in rule parsing, there is a loop for each parameter, and multiple parameters can be parallelized. However, there are many common tables that need to be generated first before parallelization. Overall, it is divided into 2 functions: the pre function that executes the common part and the loop function that was originally going to loop. For the part that was originally going to loop, the common function exesqls is called.

[0041] ① The pre function is used to create one or more common tables, and its creation and procedure are as follows:

[0042]

[0043]

[0044] ② Design a script to first execute pre and then execute loop in parallel to complete the execution of multiple SQL statements.

[0045]

[0046] Those skilled in the art should understand that the embodiments in the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the embodiments in the embodiments of the present application can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the embodiments in the embodiments of the present application can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0047] These computer program instructions can also be loaded onto a computer or other programmable data processing device, so that a series of operation steps are executed on the computer or other programmable device to generate computer-implemented processing. Thus, the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one process or multiple processes in the flowchart and / or one block or multiple blocks in the block diagram.

[0048] Obviously, those skilled in the art can make various changes and modifications to the embodiments in the embodiments of the present application without departing from the spirit and scope of the embodiments in the embodiments of the present application. Thus, if these modifications and variations of the embodiments in the embodiments of the present application fall within the scope of the claims of the embodiments of the present application and their equivalent technologies, the embodiments of the present application also intend to include these changes and modifications.

Claims

1. A method for parallel execution of SQL in a Greenplum database, characterized in that, The steps include: Step 1. Install the plpython extension for greenplum; 1-1. Starting from greenplum7, plpython is included. Greenplum 6 needs to download the source code, compile and install it by yourself; 1-2. After installation, create a Python 3 extension using create extension plpython3u to facilitate subsequent writing of gp stored procedures using Python; Step 2: Write a public function to call multiple SQL statements in parallel.

2. The method for parallel execution of SQL in a Greenplum database according to claim 1, wherein Step 2 is implemented as follows: 2-1. Define the public function: exesqls(sql_array text[], parallels_numint), which has two input parameters: sql_array is used to pass in multiple sql statements to be executed, and parallels_num is the number of parallels. 2-2. Get database connection information; Because you need to execute SQL statements in a way other than SQL, you need the database connection information. Use SQL related functions to obtain the current user's connection information, including IP, port, dbname, and user name. 2-3. Set the database to be password-free for its own IP. Since the password cannot be directly obtained through sql-related functions, it is necessary to use the password-free method; the public function exesqls executes sql statements inside the gp database, so it is necessary to set all user names of 127.0.0.1 to be password-free in the pg_hba.conf file; 2-4. Assemble the psql command execution function; Since the command line tool psql that comes with gp will open a database connection separately when it is executed, you can use the command line tool psql to execute multiple SQL statements at the same time, and the raise notice log in the SQL can be passed to plpython in real time through subprocess; 2-5. Enable thread pool execution in parallel; Use Python's ThreadPoolExecutor to start multiple threads. Each thread corresponds to a command line tool psql to run a SQL statement, thereby completing multi-threaded parallel execution of multiple SQL statements passed in.

3. A method for parallel execution of SQL in a Greenplum database according to claim 2, wherein, After each thread completes the currently executed SQL statement, it will seamlessly execute the next SQL statement.

4. A method for parallel execution of SQL in a Greenplum database according to claim 2, characterized in that, Before passing multiple SQL statements into the public function exesqls, they need to be sorted from high to low according to the priority of each SQL statement, and then the sorted SQL array is passed into the function.

5. The method for parallel execution of SQL in a Greenplum database according to claim 4, characterized in that, The priority of the sql statement is set as needed.

6. A method for parallel execution of SQL in a Greenplum database according to claim 4, characterized in that, The longer the running time, the higher the priority, and the shorter the overall running time.

7. A method for parallel execution of SQL in a Greenplum database according to claim 4, characterized in that, The designed plpy function only implements "tool" functions and is deployed once by the administrator. Ordinary developers only need to write ordinary SQL to call it.

Images