A method for transforming Oracle stored procedures and related devices

Abstract

The embodiment of this application belongs to the technical field of infrastructure operation and maintenance, is applied to the field of Oracle stored procedure optimization, and relates to a method for transforming an Oracle stored procedure and related devices, including connecting to an Oracle target database; obtaining the output results obtained by executing each SQL operation statement; according to a preset Spark SQL conversion template, converting all SQL operation statements into corresponding RDD operation statements; using Spark as the computing engine and Hudi as the storage component to execute each RDD operation statement, obtaining the corresponding output results, and performing consistency verification on the output results; based on the verification results, determining whether to adopt a parallel running method to perform output stability verification on the Oracle stored procedure execution program; if so, according to the output stability verification results, determining whether to adopt a single-track running method to execute the Oracle stored procedure. By using the Spark computing engine and the Hudi caching component to improve the Oracle stored procedure, the processing efficiency when using the Oracle database to implement stored procedures in a big data scenario is improved.

Description

Technical Field

[0001] This application relates to the technical field of infrastructure operation and maintenance, and particularly to a method for transforming Oracle stored procedures and related devices. Background Art

[0002] Traditional financial industries such as insurance, securities, and banking widely use Oracle as a storage database. The reasons are as follows: 1. Compared with other OLTP components, the processing speed is extremely fast. 2. The security level is high, supporting snapshots and perfect recovery, and can ensure data consistency. 3. The data backup and failover mechanisms are perfect, and the stability is good. Due to the excellent performance of Oracle, there are not many alternative solutions for the banking and insurance industries in terms of technology selection. Since a large amount of production data is stored in Oracle, in the activities of production and operation, we need to perform various data migrations, data processing, data extraction, etc. Oracle provides stored procedures for users to assist in completing custom logical calculations on data.

[0003] Although Oracle plays an important role in the traditional financial industry, with the development of technology and the times, some drawbacks have gradually emerged. Especially for the calculation of large datasets, it has become increasingly unable to cope. The execution efficiency of stored procedures is significantly far behind that of big data computing engines such as MR. For the calculation of large datasets, the execution efficiency is relatively slow, resulting in a relatively low freshness of data output, seriously affecting the data usage experience of downstream users. Therefore, there is still a problem of relatively slow execution efficiency when using Oracle databases to implement stored procedures in big data scenarios in the prior art. Summary of the Invention

[0004] The purpose of the embodiments of this application is to propose a method for transforming Oracle stored procedures and related devices to solve the problem of relatively slow execution efficiency when using Oracle databases to implement stored procedures in big data scenarios in the prior art.

[0005] To solve the above technical problems, the embodiments of this application provide a method for transforming Oracle stored procedures, adopting the following technical solutions:

[0006] A method for transforming Oracle stored procedures includes the following steps:

[0007] Step 201, connect to the started Oracle target database according to the pre-created Oracle database connection command;

[0008] Step 202, obtain all SQL operation statements when performing SQL operations on the target database in the first state, and the output results obtained by executing each SQL operation statement. The first state is the state where Spark is not used as the computing engine and Hudi is not used as the storage component;

[0009] Step 203, according to a preset Spark SQL conversion template, perform syntax conversion on the SQL operation statements, and convert all the SQL operation statements into corresponding RDD operation statements;

[0010] Step 204, in the second state, execute each of the RDD operation statements to obtain the corresponding output results. The second state represents the state where Spark is used as the computing engine and Hudi is used as the storage component;

[0011] Step 205, perform consistency verification on the output results obtained by executing each SQL operation statement and the output results obtained by executing each of the RDD operation statements, and obtain the verification result;

[0012] Step 206, based on the verification result, determine whether to perform output stability verification on the Oracle stored procedure execution program in the second state in a parallel running manner;

[0013] Step 207, if so, according to the output stability verification result, determine whether to execute the Oracle stored procedure in a single-track running manner.

[0014] Further, the connection command includes at least the service address of the target database, the user name for connection, and the user password. The step of connecting to the started Oracle target database according to the pre-created Oracle database connection command specifically includes:

[0015] Obtain the service address of the target database, the user name for connection, and the user password, and generate a connection command line;

[0016] Call a preset database connection interface, and use the connection command line as the parameter of the connection interface to complete the connection operation to the Oracle target database.

[0017] Further, the step of obtaining all SQL operation statements when performing SQL operations on the target database in the first state, and the output results obtained by executing each SQL operation statement specifically includes:

[0018] Successively obtain each SQL operation statement in all the SQL operation statements;

[0019] Perform stored procedure operations on the target database through the respective SQL operation statements, and obtain the stored procedure operation results corresponding to the respective SQL operation statements;

[0020] Send the stored procedure operation results corresponding to the respective SQL operation statements to a preset Hive storage component for caching;

[0021] Adopt the MD5 encryption rule to perform MD5 value conversion on the stored procedure operation results cached in the Hive storage component, and obtain the MD5 values corresponding to the respective SQL operation statements.

[0022] Further, before performing the step of performing syntax conversion on the SQL operation statements according to a preset Spark SQL conversion template and converting all the SQL operation statements into corresponding RDD operation statements, the method further includes:

[0023] Upgrade and transform the computing engine in the first state according to a preset computing engine framework, and upgrade the Oracle stored procedure execution program from the first state to an intermediate state, where the preset computing engine framework is the Spark computing engine framework;

[0024] The step of performing syntax conversion on the SQL operation statements according to a preset Spark SQL conversion template and converting all the SQL operation statements into corresponding RDD operation statements specifically includes:

[0025] Sequentially input each SQL operation statement in all the SQL operation statements into the Spark SQL conversion template in the Spark computing engine framework;

[0026] Obtain the output result after being converted by the Spark SQL conversion template, and use the output result after being converted by the Spark SQL conversion template as the RDD operation statement.

[0027] Further, before performing the step of executing each of the RDD operation statements in the second state to obtain corresponding output results, the method further includes:

[0028] Upgrade and transform the result storage component in the intermediate state according to a preset result storage component, and upgrade the Oracle stored procedure execution program from the intermediate state to the second state, where the preset result storage component is the Hudi storage component;

[0029] The step of executing each of the RDD operation statements in the second state to obtain corresponding output results specifically includes:

[0030] Obtain each of the RDD operation statements in sequence;

[0031] Perform stored procedure operations on the target database through each of the RDD operation statements, and obtain the stored procedure operation results corresponding to each of the RDD operation statements;

[0032] Send the stored procedure operation results corresponding to each of the RDD operation statements to a preset Hudi storage component for caching;

[0033] Adopt the MD5 encryption rule to convert the MD5 value of the stored procedure operation results cached in the Hudi storage component, and obtain the MD5 values corresponding to each of the RDD operation statements respectively.

[0034] Further, the step of performing consistency verification on the output results obtained by executing each SQL operation statement and the output results obtained by executing each of the RDD operation statements to obtain a verification result specifically includes:

[0035] Add the MD5 values corresponding to each of the SQL operation statements as set elements to a preset first result set;

[0036] Add the MD5 values corresponding to each of the RDD operation statements as set elements to a preset second result set;

[0037] Call a preset cosine similarity function to calculate the similarity of the elements in the first result set and the second result set;

[0038] Obtain the output result of the cosine similarity function;

[0039] Compare the output result of the cosine similarity function with a preset similarity threshold;

[0040] Judge whether the output result of the cosine similarity function meets the preset similarity threshold;

[0041] If it meets, the consistency verification is successful;

[0042] If it does not meet, the consistency verification fails.

[0043] Further, the step of judging whether to perform output stability verification on the Oracle stored procedure execution program in the second state in a parallel running manner based on the verification result specifically includes:

[0044] If the consistency verification is successful, it is necessary to perform Oracle stored procedure stability verification in a parallel running manner in the second state;

[0045] If the consistency verification fails, there is a certain error in the transformation of the Oracle stored procedure this time, and a transformation adjustment prompt is sent to the preset monitoring end;

[0046] The steps of verifying the stability of the Oracle stored procedure by adopting the parallel running mode in the second state specifically include:

[0047] Taking the output results corresponding to the respective SQL operation statements and the respective RDD operation statements within the same time period as the data pairs to be verified;

[0048] Repeatedly obtain the output results corresponding to the respective SQL operation statements and the respective RDD operation statements within the preset monitoring time period, and obtain a certain number of data pairs to be verified;

[0049] Repeatedly execute step 205 to perform consistency verification on the certain number of data pairs to be verified, and obtain the number of data pairs to be verified with successful consistency verification;

[0050] According to the preset proportional algorithm, obtain the proportional value between the number of data pairs to be verified with successful consistency verification and the number of all data pairs to be verified;

[0051] If the proportional value meets the preset proportional threshold, the Oracle stored procedure execution program has output stability;

[0052] If the proportional value does not meet the preset proportional threshold, the Oracle stored procedure execution program does not have output stability.

[0053] Further, the steps of judging whether to execute the Oracle stored procedure in the single-track running mode according to the output stability verification result specifically include:

[0054] If the Oracle stored procedure execution program has output stability, only execute the Oracle stored procedure in the single-track running mode, where the single-track running mode refers to a program running mode that only uses Spark as the computing engine and Hudi as the storage component;

[0055] If the Oracle stored procedure execution program does not have output stability, there is a certain error in the transformation of the Oracle stored procedure this time, and a transformation adjustment prompt is sent to the preset monitoring end.

[0056] To solve the above technical problems, an embodiment of the present application further provides a device for transforming an Oracle stored procedure, and adopts the following technical solutions:

[0057] A device for transforming an Oracle stored procedure includes:

[0058] An Oracle database connection module, which is used to connect to a started Oracle target database according to a pre-created Oracle database connection command;

[0059] An SQL statement execution result acquisition module, which is used to acquire all SQL operation statements when performing SQL operations on the target database in the first state, and the output results obtained by executing each SQL operation statement. The first state is the state where Spark is not used as the computing engine and Hudi is not used as the storage component;

[0060] An RDD statement conversion module, which is used to perform syntax conversion on the SQL operation statements according to a preset Spark SQL conversion template, and convert all the SQL operation statements into corresponding RDD operation statements;

[0061] An RDD statement execution result acquisition module, which is used to execute each of the RDD operation statements to obtain corresponding output results in the second state. The second state represents the state where Spark is used as the computing engine and Hudi is used as the storage component;

[0062] A consistency verification module, which is used to perform consistency verification on the output results obtained by executing each SQL operation statement and the output results obtained by executing each of the RDD operation statements to obtain a verification result;

[0063] An output stability verification module, which is used to determine whether to perform output stability verification on the Oracle stored procedure execution program in the second state in a parallel running mode based on the verification result;

[0064] A single-track running mode judgment module, which is used to, if so, determine whether to use the single-track running mode to execute the Oracle stored procedure according to the output stability verification result.

[0065] To solve the above technical problems, an embodiment of the present application further provides a computer device, which adopts the following technical solution:

[0066] A computer device includes a memory and a processor. Computer-readable instructions are stored in the memory. When the processor executes the computer-readable instructions, the steps of the method for transforming the Oracle stored procedure described above are implemented.

[0067] To solve the above technical problems, an embodiment of the present application further provides a computer-readable storage medium, which adopts the following technical solution:

[0068] A computer-readable storage medium stores computer-readable instructions thereon, and when the computer-readable instructions are executed by a processor, the steps of the method for transforming an Oracle stored procedure as described above are implemented.

[0069] Compared with the prior art, the embodiments of the present application mainly have the following beneficial effects:

[0070] In the method for transforming an Oracle stored procedure according to the embodiments of the present application, by connecting to an Oracle target database; obtaining the output results obtained by executing each SQL operation statement; according to a preset Spark SQL conversion template, converting all SQL operation statements into corresponding RDD operation statements; using Spark as a computing engine and Hudi as a storage component to execute each RDD operation statement, obtaining the corresponding output results, and performing consistency verification on the output results; based on the verification results, determining whether to adopt a parallel running mode to perform output stability verification on the Oracle stored procedure execution program; if so, according to the output stability verification results, determining whether to adopt a single-track running mode to execute the Oracle stored procedure. By using the Spark computing engine and the Hudi caching component to improve the Oracle stored procedure, the processing efficiency when implementing the stored procedure using the Oracle database in a big data scenario is improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0071] In order to more clearly illustrate the solutions in the present application, the following will briefly introduce the drawings required for the description of the embodiments of the present application. Obviously, the drawings in the following description are some embodiments of the present application. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0072] Figure 1 is an exemplary system architecture diagram to which the present application can be applied;

[0073] Figure 2 A flowchart of an embodiment of the method for transforming an Oracle stored procedure according to the present application;

[0074] Figure 3 is Figure 2 A flowchart of a specific embodiment of step 202 shown;

[0075] Figure 4 is Figure 2 A flowchart of a specific embodiment of step 204 shown;

[0076] Figure 5 is Figure 2 A flowchart of a specific embodiment of step 205 shown;

[0077] Figure 6 is Figure 2 A flowchart of a specific embodiment of step 206 shown;

[0078] Figure 7 is Figure 6 A flowchart of a specific embodiment of step 601 shown;

[0079] Figure 8 A schematic structural diagram of an embodiment of an apparatus for modifying an Oracle stored procedure according to the present application;

[0080] Figure 9 A schematic structural diagram of an embodiment of a computer device according to the present application. Detailed implementation manners

[0081] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the technical field to which this application belongs; the terms used in the specification of this application are only for the purpose of describing specific embodiments and are not intended to limit this application; the terms "including" and "having" and any variations thereof in the specification and claims of this application and the above drawings are intended to cover non-exclusive inclusion. The terms "first", "second", etc. in the specification and claims of this application or the above drawings are used to distinguish different objects and not to describe a specific order.

[0082] Referring to "embodiments" herein means that a particular feature, structure, or characteristic described in connection with the embodiments can be included in at least one embodiment of this application. The phrase appears in various places in the specification and does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment mutually exclusive with other embodiments. Those skilled in the art will explicitly and implicitly understand that the embodiments described herein can be combined with other embodiments.

[0083] In order to enable those skilled in the art of this technology to better understand the solution of this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings.

[0084] As Figure 1 shown, the system architecture 100 may include terminal devices 101, 102, 103, a network 104, and a server 105. The network 104 is used to provide a medium for communication links between the terminal devices 101, 102, 103 and the server 105. The network 104 may include various connection types, such as wired, wireless communication links, or fiber optic cables, etc.

[0085] Users can use terminal devices 101, 102, and 103 to interact with server 105 via network 104 to receive or send messages, etc. Various communication client applications can be installed on terminal devices 101, 102, and 103, such as web browser applications, shopping applications, search applications, instant messaging tools, email clients, social platform software, etc.

[0086] Terminal devices 101, 102, and 103 can be various electronic devices with a display screen and supporting web browsing, including but not limited to smartphones, tablets, e-book readers, MP3 players (Moving Picture Experts Group Audio Layer III), MP4 (Moving Picture Experts Group Audio Layer IV) players, laptop computers, desktop computers, and so on.

[0087] Server 105 can be a server that provides various services, such as a background server that supports the pages displayed on terminal devices 101, 102, and 103.

[0088] It should be noted that the method for transforming Oracle stored procedures provided in the embodiments of this application is generally executed by the server / terminal device. Correspondingly, the device for transforming Oracle stored procedures is generally set in the server / terminal device.

[0089] It should be understood that Figure 1 the numbers of terminal devices, networks, and servers in

[0090] are merely illustrative. According to the implementation requirements, there can be any number of terminal devices, networks, and servers.

[0091] Oracle Database, also known as Oracle RDBMS, or simply Oracle, is a relational database management system of Oracle Corporation. It has been in a leading position in the database field. It can be said that the Oracle database system is a popular relational database management system in the world. The system has good portability, is easy to use, and has strong functions, and is suitable for various large, medium, small, and microcomputer environments. It is a high-efficiency, reliable, and high-throughput database solution and is often used as a database support tool in big data business environments.

[0092] However, with the development of technology and the times, some drawbacks of Oracle databases have gradually emerged. Especially when it comes to the calculation of large datasets, it has become increasingly unable to cope. The execution efficiency of stored procedures is significantly far behind that of big data computing engines such as MR and Spark.

[0093] Spark is a fast and general-purpose computing engine designed specifically for large-scale data processing. Spark is a general parallel framework similar to Hadoop MapReduce open-sourced by the UCBerkeley AMP lab (AMP Laboratory at the University of California, Berkeley). Spark has the advantages of Hadoop MapReduce; however, different from MapReduce, the intermediate output results of Jobs can be saved in memory, thus eliminating the need to read and write HDFS. Therefore, Spark is better suited for algorithms such as data mining and machine learning that require iterative MapReduce, and Spark is suitable for processing offline and streaming big data.

[0094] Spark SQL is a template in the Spark suite that converts data calculation tasks into RDD calculations in the form of SQL. Hive only supports SQL-form calculations and does not support the storage of RDD calculation results. Therefore, we selected Hudi as the caching component, and Hudi supports the storage of RDD-form calculation results.

[0095] Hudi is the abbreviation of Hadoop Updates and Incrementals. It is a Data Lakes solution developed and open-sourced by Uber. Hudi is used to build a streaming data lake with incremental data pipelines on top of the managed database layer, and it is optimized for both lake engines and regular batch processing. In short, Hudi is a scan-optimized data storage abstraction for analytical business, which enables DFS datasets to support changes within minutes of latency and also supports incremental processing of this dataset by downstream systems.

[0096] Continue to refer to Figure 2 , which shows a flowchart of an embodiment of a method for transforming an Oracle stored procedure according to the present application. The method for transforming an Oracle stored procedure includes the following steps:

[0097] Step 201, connect to the started Oracle target database according to the pre-created Oracle database connection command.

[0098] In this embodiment, the connection command at least includes the service address of the target database, the username for connection, and the user password. The step of connecting to the started Oracle target database according to the pre-created Oracle database connection command specifically includes: obtaining the service address of the target database, the username for connection, and the user password, and generating a connection command line; calling a preset database connection interface and using the connection command line as a parameter of the connection interface to complete the connection operation to the Oracle target database.

[0099] Establish a connection relationship with the target database through the service address of the Oracle target database, the username for connection, and the user password. In the case of connection, improve the stored procedure of the target database.

[0100] Step 202: Obtain all SQL operation statements when performing SQL operations on the target database in the first state, and the output results obtained by executing each SQL operation statement.

[0101] In this embodiment, the first state is a state where Spark is not used as the computing engine and Hudi is not used as the storage component.

[0102] Continue to refer to Figure 3 , Figure 3 Yes Figure 2 is a flowchart of a specific embodiment of step 202 shown in

[0103] Step 301: Sequentially obtain each SQL operation statement in all the SQL operation statements.

[0104] Step 302: Perform a stored procedure operation on the target database through each SQL operation statement to obtain the stored procedure operation result corresponding to each SQL operation statement.

[0105] Step 303: Send the stored procedure operation results corresponding to each SQL operation statement to a preset Hive storage component for caching.

[0106] Step 304: Adopt the MD5 encryption rule to perform MD5 value conversion on the stored procedure operation results cached in the Hive storage component to obtain the MD5 values corresponding to each SQL operation statement.

[0107] In this embodiment, the stored procedure operation refers to the process of operating on the target database according to preset database operation statements. Before the transformation, the Hive storage component was used to cache the stored procedure operation results corresponding to each SQL operation statement, and the MD5 value was obtained. The MD5 value was obtained by using MD5 conversion, which ensured the format unity of the stored procedure operation results.

[0108] Step 203: According to the preset Spark SQL conversion template, perform syntax conversion on the SQL operation statements, and convert all the SQL operation statements into corresponding RDD operation statements.

[0109] In this embodiment, before performing the step of performing syntax conversion on the SQL operation statements according to the preset Spark SQL conversion template and converting all the SQL operation statements into corresponding RDD operation statements, the method further includes: upgrading and transforming the computing engine in the first state according to the preset computing engine framework, and upgrading the Oracle stored procedure execution program from the first state to an intermediate state, where the preset computing engine framework is the Spark computing engine framework.

[0110] By introducing the Spark computing engine framework, it is convenient to improve the operation efficiency of the Oracle database during stored procedure operations and enhance the storage and computing capabilities of the Oracle database in big data business scenarios.

[0111] In this embodiment, the step of performing syntax conversion on the SQL operation statements according to the preset Spark SQL conversion template and converting all the SQL operation statements into corresponding RDD operation statements specifically includes: sequentially inputting each SQL operation statement in all the SQL operation statements into the Spark SQL conversion template in the Spark computing engine framework; obtaining the output result after being converted by the Spark SQL conversion template, and using the output result after being converted by the Spark SQL conversion template as the RDD operation statement.

[0112] By using the Spark SQL template in the Spark computing engine to convert all the SQL operation statements into RDD operation statements, it is convenient for the Spark computing engine to process streaming data and iterative data in combination with the stored procedures of the Oracle target database in a big data business environment, thereby improving the processing efficiency.

[0113] Step 204: In the second state, execute each of the RDD operation statements to obtain the corresponding output result, where the second state represents the state with Spark as the computing engine and Hudi as the storage component.

[0114] In this embodiment, before performing the step of executing each of the RDD operation statements to obtain the corresponding output results in the second state, the method further includes: upgrading and transforming the result storage component in the intermediate state according to a preset result storage component, and upgrading the Oracle stored procedure execution program from the intermediate state to the second state, where the preset result storage component is a Hudi storage component.

[0115] By introducing the Hudi storage component and combining the characteristics of Hudi that can manage ultra-large datasets and support data changes, the transformation of the Oracle stored procedure is provided with a running environment support.

[0116] Continue to refer to Figure 4 , Figure 4 Yes Figure 2 is a flowchart of a specific embodiment of step 204 shown in

[0117] Step 401, sequentially obtain each of the RDD operation statements;

[0118] Step 402, perform stored procedure operations on the target database through each of the RDD operation statements to obtain the stored procedure operation results corresponding to each of the RDD operation statements;

[0119] Step 403, send the stored procedure operation results corresponding to each of the RDD operation statements to a preset Hudi storage component for caching;

[0120] Step 404, use the MD5 encryption rule to convert the MD5 value of the stored procedure operation results cached in the Hudi storage component to obtain the MD5 values corresponding to each of the RDD operation statements respectively.

[0121] Since Hive cannot obtain the output results corresponding to the RDD statements, during the transformation, the Hudi storage component is used to cache the stored procedure operation results corresponding to each of the RDD operation statements, and the MD5 value is obtained. The MD5 conversion is used to obtain the MD5 value, which ensures the format unity of the stored procedure operation results.

[0122] Step 205, perform consistency verification on the output results obtained by executing each SQL operation statement and the output results obtained by executing each of the RDD operation statements to obtain the verification results.

[0123] Continue to refer to Figure 5 , Figure 5 Yes Figure 2 is a flowchart of a specific embodiment of step 205 shown in

[0124] Step 501, add the MD5 value corresponding to each of the SQL operation statements as a set element into a preset first result set;

[0125] Step 502, add the MD5 value corresponding to each of the RDD operation statements as a set element into a preset second result set;

[0126] Step 503, call a preset cosine similarity function to calculate the similarity of the elements in the first result set and the second result set;

[0127] Step 504, obtain the output result of the cosine similarity function;

[0128] Step 505, compare the output result of the cosine similarity function with a preset similarity threshold;

[0129] Step 506, determine whether the output result of the cosine similarity function meets the preset similarity threshold;

[0130] Step 507, if it meets, the consistency verification is successful;

[0131] Step 508, if it does not meet, the consistency verification fails.

[0132] After step 204 is executed, the stored procedure of the Oracle target database will cache the MD5 value corresponding to the executed SQL statement in the Hive cache component, while the Hudi cache component will cache the MD5 value corresponding to the executed RDD statement. At this time, through the way of comparing set elements and using the cosine similarity function, calculate the similarity between all MD5 values in the two sets, and obtain the consistency verification result according to the similarity.

[0133] Step 206, based on the verification result, determine whether to perform output stability verification on the Oracle stored procedure execution program in the second state by using the parallel running method.

[0134] Continue to refer to Figure 6 , Figure 6 Yes Figure 2 is a flowchart of a specific embodiment of step 206 shown, including:

[0135] Step 601, if the consistency verification is successful, it is necessary to perform Oracle stored procedure stability verification in the second state by using the parallel running method;

[0136] Step 602, if the consistency verification fails, there is a certain error in the transformation of the Oracle stored procedure this time, and a transformation adjustment prompt is sent to a preset monitoring end.

[0137] First, through a primary consistency verification, it is determined whether to adopt the parallel operation method for the stability verification of the Oracle stored procedure, avoiding blindly conducting stability verification in the case of failed consistency verification, obtaining the consistency verification result in real time, and if it fails, promptly sending a transformation and adjustment prompt to the monitoring end, which is more scientific and meets the requirements of the big data business scenario.

[0138] Continue to refer to Figure 7 , Figure 7 Yes Figure 6 is a flowchart of a specific embodiment of step 601 shown in

[0139] Step 701: Use the output results corresponding to each of the SQL operation statements and each of the RDD operation statements within the same time period as the data pairs to be verified.

[0140] Step 702: Repeatedly obtain the output results corresponding to each of the SQL operation statements and each of the RDD operation statements within a preset monitoring time period to obtain a certain number of data pairs to be verified.

[0141] Step 703: Repeatedly execute step 205 to perform consistency verification on the certain number of data pairs to be verified and obtain the number of data pairs to be verified with successful consistency verification.

[0142] Step 704: According to a preset proportional algorithm, obtain the ratio value between the number of data pairs to be verified with successful consistency verification and the number of all data pairs to be verified.

[0143] Step 705: If the ratio value meets the preset ratio threshold, the Oracle stored procedure execution program has output stability.

[0144] Step 706: If the ratio value does not meet the preset ratio threshold, the Oracle stored procedure execution program does not have output stability.

[0145] By adopting the parallel operation method for the stability verification of the Oracle stored procedure, obtaining the consistency verification results obtained multiple times within a preset time period, and based on the multiple verification results, identifying the stability of the program after improving the Oracle stored procedure, effectively ensuring the stability of the released program after improvement; avoiding releasing unstable improved programs and causing irreparable losses to the subsequent business processing process.

[0146] Step 207: If so, based on the output stability verification result, determine whether to execute the Oracle stored procedure in the single-track operation mode.

[0147] In this embodiment, the step of determining whether to execute the Oracle stored procedure in a single-track operation mode according to the output stability verification result specifically includes: if the Oracle stored procedure execution program has output stability, only execute the Oracle stored procedure in a single-track operation mode, where the single-track operation mode refers to a program operation mode that only uses Spark as the computing engine and Hudi as the storage component; if the Oracle stored procedure execution program does not have output stability and there is a certain error in the transformation of the Oracle stored procedure this time, a transformation adjustment prompt is sent to a preset monitoring end.

[0148] By determining that the Oracle stored procedure execution program has output stability and only executing the Oracle stored procedure in a single-track operation mode, the excessive consumption of processing resources in the concurrent operation mode is avoided.

[0149] This application connects to the Oracle target database; obtains the output results obtained by executing each SQL operation statement; converts all SQL operation statements into corresponding RDD operation statements according to a preset Spark SQL conversion template; uses Spark as the computing engine and Hudi as the storage component to execute each RDD operation statement, obtains the corresponding output results, and performs consistency verification on the output results; based on the verification results, determines whether to use a concurrent operation mode to verify the output stability of the Oracle stored procedure execution program; if so, determines whether to execute the Oracle stored procedure in a single-track operation mode according to the output stability verification result. By improving the Oracle stored procedure using the Spark computing engine and the Hudi cache component, the processing efficiency when implementing the stored procedure using the Oracle database in a big data scenario is improved.

[0150] For further reference Figure 8 , as an implementation of the method shown above Figure 2 , this application provides an embodiment of a device for transforming an Oracle stored procedure. This device embodiment corresponds to the method embodiment shown in Figure 2 , and this device can be specifically applied to various electronic devices.

[0151] As shown in Figure 8 , the device 800 for transforming an Oracle stored procedure described in this embodiment includes: an Oracle database connection module 801, an SQL statement execution result acquisition module 802, an RDD statement conversion module 803, an RDD statement execution result acquisition module 804, a consistency verification module 805, an output stability verification module 806, and a single-track operation mode determination module 807. Among them:

[0152] The Oracle database connection module 801 is used to connect to the started Oracle target database according to the pre-created Oracle database connection command;

[0153] The SQL statement execution result acquisition module 802 is used to acquire all SQL operation statements when performing SQL operations on the target database in the first state, and the output results obtained by executing each SQL operation statement. The first state is the state where Spark is not used as the computing engine and Hudi is not used as the storage component;

[0154] The RDD statement conversion module 803 is used to perform syntax conversion on the SQL operation statements according to the preset Spark SQL conversion template, and convert all the SQL operation statements into corresponding RDD operation statements;

[0155] The RDD statement execution result acquisition module 804 is used to execute each of the RDD operation statements in the second state to obtain the corresponding output results. The second state represents the state where Spark is used as the computing engine and Hudi is used as the storage component;

[0156] The consistency verification module 805 is used to perform consistency verification on the output results obtained by executing each SQL operation statement and the output results obtained by executing each of the RDD operation statements, and obtain the verification results;

[0157] The output stability verification module 806 is used to determine whether to perform output stability verification on the Oracle stored procedure execution program in the second state in a parallel running mode based on the verification results;

[0158] The single-track running mode judgment module 807 is used to, if so, determine whether to use the single-track running mode to execute the Oracle stored procedure according to the output stability verification results.

[0159] This application connects to the Oracle target database, obtains the output results obtained by executing each SQL operation statement, converts all SQL operation statements into corresponding RDD operation statements according to a preset Spark SQL conversion template, uses Spark as the computing engine and Hudi as the storage component to execute each RDD operation statement, obtains the corresponding output results, and performs consistency verification on the output results. Based on the verification results, it is determined whether to use the parallel operation method to perform output stability verification on the Oracle stored procedure execution program. If so, according to the output stability verification results, it is determined whether to use the single-track operation method to execute the Oracle stored procedure. By using the Spark computing engine and the Hudi caching component to improve the Oracle stored procedure, the processing efficiency of implementing the stored procedure using the Oracle database in the big data scenario is improved.

[0160] Those of ordinary skill in the art can understand that all or part of the processes in the methods of the above embodiments can be completed by instructing relevant hardware through computer-readable instructions, and the computer-readable instructions can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above methods. Among them, the foregoing storage medium can be a non-volatile storage medium such as a magnetic disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0161] It should be understood that although the steps in the flowchart of the accompanying drawings are shown in sequence according to the arrows, these steps do not necessarily have to be executed in the order indicated by the arrows. Unless otherwise clearly stated in this article, the execution of these steps has no strict order limit, and they can be executed in other orders. Moreover, at least some of the steps in the flowchart of the accompanying drawings may include multiple sub-steps or multiple stages. These sub-steps or stages do not necessarily have to be executed at the same time, but can be executed at different times, and their execution order does not necessarily have to be sequential, but can be executed alternately or alternately with at least a part of other steps or sub-steps or stages of other steps.

[0162] To solve the above technical problems, the embodiments of this application also provide computer equipment. For details, please refer to Figure 9 , Figure 9 which is the basic structural block diagram of the computer equipment in this embodiment.

[0163] The computer device 9 includes a memory 9a, a processor 9b, and a network interface 9c that are communicatively connected to each other via a system bus. It should be noted that only the computer device 9 with components 9a - 9c is shown in the figure, but it should be understood that it is not required to implement all the shown components, and more or fewer components can be implemented alternatively. Among them, those skilled in the art of the present technology can understand that the computer device here is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to microprocessors, application specific integrated circuits (ASICs), field - programmable gate arrays (FPGAs), digital signal processors (DSPs), embedded devices, etc.

[0164] The computer device can be a computing device such as a desktop computer, a notebook, a palm computer, and a cloud server. The computer device can perform human - computer interaction with the user through means such as a keyboard, a mouse, a remote control, a touchpad, or a voice - controlled device.

[0165] The memory 9a includes at least one type of readable storage medium, and the readable storage medium includes flash memory, hard disks, multimedia cards, card - type memories (such as SD or DX memories, etc.), random access memory (RAM), static random access memory (SRAM), read - only memory (ROM), electrically erasable programmable read - only memory (EEPROM), programmable read - only memory (PROM), magnetic memories, magnetic disks, optical disks, etc. In some embodiments, the memory 9a can be an internal storage unit of the computer device 9, such as the hard disk or memory of the computer device 9. In other embodiments, the memory 9a can also be an external storage device of the computer device 9, such as a plug - in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc., equipped on the computer device 9. Of course, the memory 9a can also include both the internal storage unit and the external storage device of the computer device 9. In this embodiment, the memory 9a is generally used to store the operating system and various application software installed on the computer device 9, such as computer - readable instructions for the method of modifying Oracle stored procedures. In addition, the memory 9a can also be used to temporarily store various data that have been output or will be output.

[0166] In some embodiments, the processor 9b may be a Central Processing Unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chips. The processor 9b is generally used to control the overall operation of the computer device 9. In this embodiment, the processor 9b is used to run the computer-readable instructions stored in the memory 9a or process data, such as running the computer-readable instructions of the method for transforming Oracle stored procedures.

[0167] The network interface 9c may include a wireless network interface or a wired network interface, and this network interface 9c is generally used to establish a communication connection between the computer device 9 and other electronic devices.

[0168] The computer device proposed in this embodiment is applied to the technical field of Oracle stored procedure optimization. This application connects to the Oracle target database; obtains the output results obtained by executing each SQL operation statement; according to a preset SparkSQL conversion template, converts all SQL operation statements into corresponding RDD operation statements; uses Spark as the computing engine and Hudi as the storage component to execute each RDD operation statement, obtains the corresponding output results, and performs consistency verification on the output results; based on the verification results, determines whether to use the parallel running method to perform output stability verification on the Oracle stored procedure execution program; if so, according to the output stability verification results, determines whether to use the single-track running method to execute the Oracle stored procedure. By using the Spark computing engine and the Hudi caching component to improve the Oracle stored procedure, the processing efficiency when using the Oracle database to implement stored procedures in a big data scenario is improved.

[0169] This application also provides another implementation manner, that is, to provide a computer-readable storage medium storing computer-readable instructions that can be executed by a processor to cause the processor to execute the steps of the method for transforming an Oracle stored procedure as described above.

[0170] The computer-readable storage medium proposed in this embodiment is applied to the technical field of Oracle stored procedure optimization. This application connects to the Oracle target database; obtains the output results obtained by executing each SQL operation statement; converts all SQL operation statements into corresponding RDD operation statements according to a preset Spark SQL conversion template; uses Spark as the computing engine and Hudi as the storage component to execute each RDD operation statement, obtains the corresponding output results, and performs consistency verification on the output results; based on the verification results, determines whether to use the parallel running method to perform output stability verification on the Oracle stored procedure execution program; if so, determines whether to use the single-track running method to execute the Oracle stored procedure according to the output stability verification results. By using the Spark computing engine and the Hudi cache component to improve the Oracle stored procedure, the processing efficiency of using the Oracle database to implement stored procedures in a big data scenario is improved.

[0171] Through the description of the above embodiments, those skilled in the art can clearly understand that the above embodiment methods can be implemented by means of software plus a necessary general hardware platform. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of this application, in essence, or the part that contributes to the prior art can be embodied in the form of a software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disc) and includes several instructions to enable a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in various embodiments of this application.

[0172] Obviously, the above-described embodiments are only a part of the embodiments of this application, rather than all of the embodiments. The accompanying drawings show the preferred embodiments of this application, but do not limit the patent scope of this application. This application can be implemented in many different forms. On the contrary, the purpose of providing these embodiments is to make the understanding of the disclosed content of this application more thorough and comprehensive. Although this application has been described in detail with reference to the foregoing embodiments, for those skilled in the art, they can still modify the technical solutions described in the foregoing specific embodiments, or perform equivalent replacements on some of the technical features. Any equivalent structure directly or indirectly using the content of the specification and drawings of this application in other related technical fields is equally within the scope of the patent protection of this application.

Claims

1. A method for transforming Oracle stored procedures, characterized in that, Including the following steps: Step 201: Connect to the started Oracle target database according to the pre-created Oracle database connection command; Step 202: Obtain all SQL operation statements when performing SQL operations on the target database in the first state, and the output results obtained by executing each SQL operation statement. The first state is the state where Spark is not used as the computing engine and Hudi is not used as the storage component; Step 203: According to the preset Spark SQL conversion template, perform syntax conversion on the SQL operation statements, and convert all the SQL operation statements into corresponding RDD operation statements; Step 204: In the second state, execute each of the RDD operation statements to obtain the corresponding output results. The second state represents the state where Spark is used as the computing engine and Hudi is used as the storage component; Step 205: Perform consistency verification on the output results obtained by executing each SQL operation statement and the output results obtained by executing each of the RDD operation statements to obtain the verification result; Step 206: Based on the verification result, determine whether to perform output stability verification on the Oracle stored procedure execution program in the second state in a parallel running manner; Step 207: If so, determine whether to execute the Oracle stored procedure in a single-track running manner according to the output stability verification result.

2. The method for transforming an Oracle stored procedure according to claim 1, wherein The connection command includes at least the service address of the target database, the user name for connection, and the user password. The step of connecting to the started Oracle target database according to the pre-created Oracle database connection command specifically includes: Obtain the service address of the target database, the user name for connection, and the user password, and generate a connection command line; Call the preset database connection interface, and use the connection command line as the parameter of the connection interface to complete the connection operation on the Oracle target database.

3. The method for transforming an Oracle stored procedure according to claim 1, characterized in that, The step of obtaining all SQL operation statements when performing SQL operations on the target database in the first state, and the output results obtained by executing each SQL operation statement specifically includes: Obtain each SQL operation statement in all the SQL operation statements in sequence; Perform stored procedure operations on the target database through each SQL operation statement to obtain the stored procedure operation results corresponding to each SQL operation statement; Send the stored procedure operation results corresponding to each SQL operation statement to the preset Hive storage component for caching; Adopt the MD5 encryption rule to perform MD5 value conversion on the stored procedure operation results cached in the Hive storage component to obtain the MD5 values corresponding to each SQL operation statement.

4. The method for transforming an Oracle stored procedure according to claim 3, characterized in that Before performing the step of performing syntax conversion on the SQL operation statements according to the preset Spark SQL conversion template and converting all the SQL operation statements into corresponding RDD operation statements, the method further includes: Upgrade and transform the computing engine in the first state according to a preset computing engine framework, and upgrade the Oracle stored procedure execution program from the first state to an intermediate state, where the preset computing engine framework is the Spark computing engine framework; The step of performing syntax conversion on the SQL operation statements according to a preset Spark SQL conversion template and converting all the SQL operation statements into corresponding RDD operation statements specifically includes: Input each SQL operation statement in all the SQL operation statements into the Spark SQL conversion template in the Spark computing engine framework in sequence; Obtain the output result after being converted by the Spark SQL conversion template, and use the output result after being converted by the Spark SQL conversion template as the RDD operation statement.

5. The method for transforming an Oracle stored procedure according to claim 4, wherein Before performing the step of executing each RDD operation statement in the second state to obtain the corresponding output result, the method further includes: Upgrade and transform the result storage component in the intermediate state according to a preset result storage component, and upgrade the Oracle stored procedure execution program from the intermediate state to the second state, where the preset result storage component is the Hudi storage component; The step of executing each RDD operation statement in the second state to obtain the corresponding output result specifically includes: Obtain each RDD operation statement in sequence; Perform stored procedure operations on the target database through each RDD operation statement to obtain the stored procedure operation results corresponding to each RDD operation statement; Send the stored procedure operation results corresponding to each RDD operation statement to a preset Hudi storage component for caching; Use the MD5 encryption rule to perform MD5 value conversion on the stored procedure operation results cached in the Hudi storage component to obtain the MD5 values corresponding to each RDD operation statement respectively.

6. The method for transforming an Oracle stored procedure according to claim 5, wherein The step of performing consistency verification on the output results obtained by executing each SQL operation statement and the output results obtained by executing each RDD operation statement to obtain a verification result specifically includes: Use the MD5 values corresponding to each SQL operation statement as set elements and add them to a preset first result set; Use the MD5 values corresponding to each RDD operation statement as set elements and add them to a preset second result set; Call a preset cosine similarity function to calculate the similarity of the elements in the first result set and the second result set; Obtain the output result of the cosine similarity function; Compare the output result of the cosine similarity function with a preset similarity threshold; Judge whether the output result of the cosine similarity function meets the preset similarity threshold; If it meets, the consistency verification is successful; If it does not meet, the consistency verification fails.

7. The method for transforming an Oracle stored procedure according to any one of claims 1 to 6, characterized in that, The step of judging whether to perform output stability verification on the Oracle stored procedure execution program in the second state in a parallel running manner based on the verification result specifically includes: If the consistency verification is successful, it is necessary to verify the stability of the Oracle stored procedure in the second state by using the parallel running method; If the consistency verification fails, there is a certain error in the transformation of the Oracle stored procedure this time, and a transformation adjustment prompt is sent to the preset monitoring end; The steps of verifying the stability of the Oracle stored procedure by using the parallel running method in the second state specifically include: Taking the output results corresponding to the respective SQL operation statements and the respective RDD operation statements within the same time period as the data pairs to be verified; Repeatedly obtaining the output results corresponding to the respective SQL operation statements and the respective RDD operation statements within the preset monitoring time period, and obtaining a certain number of data pairs to be verified; Repeatedly execute step 205 to perform consistency verification on the certain number of data pairs to be verified, and obtain the number of data pairs to be verified with successful consistency verification; According to the preset proportional algorithm, obtain the proportional value between the number of data pairs to be verified with successful consistency verification and the number of all data pairs to be verified; If the proportional value meets the preset proportional threshold, the Oracle stored procedure execution program has output stability; If the proportional value does not meet the preset proportional threshold, the Oracle stored procedure execution program does not have output stability.

8. The method for transforming an Oracle stored procedure according to claim 7, wherein The steps of judging whether to execute the Oracle stored procedure in the single-track running mode according to the output stability verification result specifically include: If the Oracle stored procedure execution program has output stability, only execute the Oracle stored procedure in the single-track running mode, where the single-track running mode refers to the program running mode that only uses Spark as the computing engine and Hudi as the storage component; If the Oracle stored procedure execution program does not have output stability, there is a certain error in the transformation of the Oracle stored procedure this time, and a transformation adjustment prompt is sent to the preset monitoring end.

9. An apparatus for transforming an Oracle stored procedure, characterized in that Including: The Oracle database connection module is used to connect to the started Oracle target database according to the pre-created Oracle database connection command; The SQL statement execution result acquisition module is used to obtain all SQL operation statements when performing SQL operations on the target database in the first state, and the output results obtained by executing each SQL operation statement. The first state is the state where Spark is not used as the computing engine and Hudi is not used as the storage component; The RDD statement conversion module is used to perform syntax conversion on the SQL operation statements according to the preset Spark SQL conversion template, and convert all the SQL operation statements into corresponding RDD operation statements; The RDD statement execution result acquisition module is used to execute each of the RDD operation statements in the second state to obtain the corresponding output results. The second state represents the state where Spark is used as the computing engine and Hudi is used as the storage component; A consistency verification module is used to perform consistency verification on the output results obtained by executing each SQL operation statement and the corresponding output results obtained by executing each of the RDD operation statements, and obtain a verification result; An output stability verification module is used to determine, based on the verification result, whether to perform output stability verification on the Oracle stored procedure execution program in a parallel running mode in the second state; A single-track running mode determination module is used to, if so, determine whether to execute the Oracle stored procedure in a single-track running mode according to the output stability verification result.

10. A computer device includes a memory and a processor. Computer-readable instructions are stored in the memory. When the processor executes the computer-readable instructions, the steps of the method for transforming an Oracle stored procedure according to any one of claims 1 to 8 are implemented.

11. A computer-readable storage medium, characterized in that, Computer-readable instructions are stored on the computer-readable storage medium. When the computer-readable instructions are executed by a processor, the steps of the method for transforming an Oracle stored procedure according to any one of claims 1 to 8 are implemented.

Images