Table data processing method and device based on kettle

Abstract

The present invention discloses a table data processing method and device based on kettle, including: obtaining a table data processing configuration table, the table data processing configuration table at least including a table name, an SQL statement and parameter information corresponding to a target table, the parameter information including parameters configured when executing the data processing corresponding to the SQL statement on the target table in kettle; generating a target kettle program corresponding to the target table according to the table data processing configuration table; running the target kettle program to execute the data processing corresponding to the SQL statement on the target table. It can be seen that the present invention first obtains the table data processing configuration table, and then executes the corresponding data processing on the target table according to the table data processing configuration table, thereby realizing the configurability of table data processing, improving the code quality of table data processing, and facilitating later maintenance. The present invention also relates to the field of blockchain technology.

Description

Technical Field

[0001] The present invention relates to the field of computer operation and maintenance testing, and in particular to a kettle-based table data processing method and device. Background Art

[0002] Database technology is a common technology in modern computer technology, usually used to store data involved in the operation of the computer. SQL language is a common database language. Computer maintenance personnel can use SQL language to add, delete, modify, query and other operations on the data in the database, thereby realizing the maintenance of the database. However, the database usually stores a large amount of data and maintaining the database may involve a large amount of data processing (for example, synchronizing data from different tables into one table, docking between different programs, database migration data, database backup data, etc.). If the corresponding SQL statement needs to be written to operate each time the table data is processed, it is easy to cause the system code to be scattered, affecting the system code quality, which is not conducive to the later maintenance of the system and will affect the system performance. Summary of the Invention

[0003] The technical problem to be solved by the present invention is that when maintaining data in a database, since databases of different programs or different scenarios usually have a certain degree of differences, when performing database maintenance, operation and maintenance personnel are usually required to write the SQL statements used for the maintenance to process the data. This database maintenance method is time-consuming and labor-intensive, and is prone to repeated development problems.

[0004] In order to solve the above technical problems, the first aspect of the present invention discloses a table data processing method based on kettle, the method comprising:

[0005] Obtaining a table data processing configuration table, wherein the table data processing configuration table includes at least a table name, an SQL statement, and parameter information corresponding to a target table, wherein the parameter information includes parameters configured when executing data processing corresponding to the SQL statement on the target table in a kettle;

[0006] Generate a target kettle program corresponding to the target table according to the table data processing configuration table;

[0007] Run the target kettle program to implement data processing corresponding to the SQL statement on the target table.

[0008] As an optional implementation, in the first aspect of the present invention, there are multiple target tables, and in the table data processing configuration table, each target table has a corresponding table name, SQL statement and parameter information;

[0009] Furthermore, generating a target kettle program corresponding to the target table according to the table data processing configuration table includes:

[0010] Generate a target kettle program corresponding to each target table according to the table name, SQL statement and parameter information corresponding to each target table in the table data processing configuration table;

[0011] And, running the target kettle program includes:

[0012] The target kettle programs corresponding to the target tables are run in parallel.

[0013] As an optional embodiment, in the first aspect of the present invention, generating a target kettle program corresponding to each target table according to the table name, SQL statement and parameter information corresponding to each target table in the table data processing configuration table includes:

[0014] Determining whether the parameter information corresponding to each target table includes splicing information, where the splicing information at least includes a name of the splicing table;

[0015] When it is determined that the parameter information corresponding to the target table includes the splicing information, the SQL statement and parameter information corresponding to the splicing table are determined from the table data processing configuration table according to the name of the splicing table;

[0016] Generate a target kettle program corresponding to the target table according to the table name corresponding to the target table and the SQL statement and parameter information corresponding to the spliced ​​table;

[0017] When it is determined that the parameter information corresponding to the target table does not include the splicing information, a target kettle program corresponding to the target table is generated according to the table name, SQL statement and parameter information corresponding to the target table.

[0018] As an optional implementation, in the first aspect of the present invention, running the target kettle program includes:

[0019] Determining whether the target kettle program is used to execute an operation of inserting data into multiple tables;

[0020] When it is determined that the target kettle program is used to execute the operation of inserting data into the multiple tables, the target data that needs to be inserted into the multiple tables is determined according to the target kettle program, and multiple target data insertion operations are run in parallel, wherein one target data insertion operation corresponds to one table, and each target data insertion operation is used to insert the target data into the table corresponding to the target data insertion operation.

[0021] As an optional implementation, in the first aspect of the present invention, determining whether the target kettle program is used to perform an operation of inserting data into multiple tables includes:

[0022] Determine whether the number of target SQL statements in the SQL statements corresponding to the target kettle program is greater than or equal to a first preset number threshold, where the target SQL statement refers to an SQL statement for performing an operation of inserting data into a table;

[0023] When it is determined that the number of the target SQL statements is greater than or equal to the first preset number threshold, determining that the target kettle program is used to execute an operation of inserting data into multiple tables;

[0024] When it is determined that the number of the target SQL statements is not greater than or equal to the first preset number threshold, it is determined that the target kettle program is not used to execute an operation of inserting data into multiple tables.

[0025] As an optional implementation, in the first aspect of the present invention, after determining that the number of the target SQL statements is not greater than or equal to the first preset number threshold, and before determining that the target kettle program is not used to execute an operation of inserting data into multiple tables, the method further includes:

[0026] Determining whether the number of tables pointed to by the target SQL statement is greater than or equal to a second preset number threshold;

[0027] When it is determined that the number of tables pointed to by the target SQL statement is greater than or equal to the second preset number threshold, triggering the execution of the operation of determining that the target kettle program is used to execute the operation of inserting data into multiple tables;

[0028] When it is determined that the number of tables pointed to by the target SQL statement is not greater than or equal to the second preset number threshold, the operation of determining that the target kettle program is not used to execute the operation of inserting data into multiple tables is triggered.

[0029] As an optional embodiment, in the first aspect of the present invention, after the step of running the target kettle program to perform data processing corresponding to the SQL statement on the target table, the method further includes:

[0030] Determine whether the SQL statement in the target kettle program is completely executed;

[0031] When it is determined that the SQL statements in the target kettle program have not been completely executed, a warning prompt is output, and the warning prompt includes all SQL statements that have not been successfully executed.

[0032] A second aspect of the present invention discloses a kettle-based table data processing device, comprising:

[0033] An acquisition module is used to acquire a table data processing configuration table, wherein the table data processing configuration table includes at least a table name, an SQL statement, and parameter information corresponding to a target table, wherein the parameter information includes parameters configured when executing data processing corresponding to the SQL statement on the target table in a kettle;

[0034] A generating module, configured to generate a target kettle program corresponding to the target table according to the table data processing configuration table;

[0035] The running module is used to run the target kettle program to implement data processing corresponding to the SQL statement on the target table.

[0036] As an optional implementation, in the second aspect of the present invention, there are multiple target tables, and in the table data processing configuration table, each target table has a corresponding table name, SQL statement and parameter information;

[0037] And, the specific manner in which the generating module generates the target kettle program corresponding to the target table according to the table data processing configuration table is:

[0038] Generate a target kettle program corresponding to each target table according to the table name, SQL statement and parameter information corresponding to each target table in the table data processing configuration table;

[0039] And, the specific method of the running module running the target kettle program is:

[0040] The target kettle programs corresponding to the target tables are run in parallel.

[0041] As an optional implementation, in the second aspect of the present invention, the specific manner in which the generation module generates the target kettle program corresponding to each target table according to the table name, SQL statement, and parameter information corresponding to each target table in the table data processing configuration table is:

[0042] Determining whether the parameter information corresponding to each target table includes splicing information, where the splicing information at least includes a name of the splicing table;

[0043] When it is determined that the parameter information corresponding to the target table includes the splicing information, the SQL statement and parameter information corresponding to the splicing table are determined from the table data processing configuration table according to the name of the splicing table;

[0044] Generate a target kettle program corresponding to the target table according to the table name corresponding to the target table and the SQL statement and parameter information corresponding to the spliced ​​table;

[0045] When it is determined that the parameter information corresponding to the target table does not include the splicing information, a target kettle program corresponding to the target table is generated according to the table name, SQL statement and parameter information corresponding to the target table.

[0046] As an optional implementation, in the second aspect of the present invention, the specific manner in which the running module runs the target kettle program is:

[0047] Determining whether the target kettle program is used to execute an operation of inserting data into multiple tables;

[0048] When it is determined that the target kettle program is used to execute the operation of inserting data into the multiple tables, the target data that needs to be inserted into the multiple tables is determined according to the target kettle program, and multiple target data insertion operations are run in parallel, wherein one target data insertion operation corresponds to one table, and each target data insertion operation is used to insert the target data into the table corresponding to the target data insertion operation.

[0049] As an optional implementation, in the second aspect of the present invention, the specific manner in which the running module determines whether the target kettle program is used to execute the operation of inserting data into multiple tables is:

[0050] Determine whether the number of target SQL statements in the SQL statements corresponding to the target kettle program is greater than or equal to a first preset number threshold, where the target SQL statement refers to an SQL statement for performing an operation of inserting data into a table;

[0051] When it is determined that the number of the target SQL statements is greater than or equal to the first preset number threshold, determining that the target kettle program is used to execute an operation of inserting data into multiple tables;

[0052] When it is determined that the number of the target SQL statements is not greater than or equal to the first preset number threshold, it is determined that the target kettle program is not used to execute an operation of inserting data into multiple tables.

[0053] As an optional embodiment, in the second aspect of the present invention, the device further includes:

[0054] A judgment module is used to judge whether the number of tables pointed to by the target SQL statement is greater than or equal to a second preset number threshold after the running module judges that the number of the target SQL statements is not greater than or equal to the first preset number threshold and before determining that the target kettle program is not used to execute the operation of inserting data into multiple tables; when it is judged that the number of tables pointed to by the target SQL statement is greater than or equal to the second preset number threshold, trigger the running module to execute the operation of determining that the target kettle program is used to execute the operation of inserting data into multiple tables; when it is judged that the number of tables pointed to by the target SQL statement is not greater than or equal to the second preset number threshold, trigger the running module to execute the operation of determining that the target kettle program is not used to execute the operation of inserting data into multiple tables.

[0055] As an optional embodiment, in the second aspect of the present invention, the judgment module is further configured to judge whether the SQL statement in the target kettle program is completely executed after the execution module runs the target kettle program to execute data processing corresponding to the SQL statement on the target table;

[0056] And, the device further comprises:

[0057] The output module is used to output a warning prompt when the judgment module determines that the SQL statement in the target kettle program has not been completely executed, and the warning prompt includes all SQL statements that have not been successfully executed.

[0058] A third aspect of the present invention discloses another kettle-based table data processing device, the device comprising:

[0059] a memory storing executable program code;

[0060] a processor coupled to the memory;

[0061] The processor calls the executable program code stored in the memory to execute part or all of the steps in the kettle-based table data processing method disclosed in the first aspect of the present invention.

[0062] The fourth aspect of the present invention discloses a computer storage medium, which stores computer instructions. When the computer instructions are called, they are used to execute some or all of the steps in the kettle-based table data processing method disclosed in the first aspect of the present invention.

[0063] Compared with the prior art, the embodiments of the present invention have the following beneficial effects:

[0064] In an embodiment of the present invention, by first determining a table data processing configuration table, and then performing corresponding data processing on the target table according to the table data processing configuration table, the configurability of table data processing is achieved, the code quality of table data processing is improved, and later maintenance is facilitated. It can also reduce the need to manually write SQL statements when performing table data processing, and solve the problems of existing database maintenance methods that are time-consuming and labor-intensive, and easily lead to repeated development. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0066] Figure 1 This is a flowchart of a kettle-based table data processing method disclosed in an embodiment of the present invention;

[0067] Figure 2 This is a schematic structural diagram of a kettle-based table data processing device disclosed in an embodiment of the present invention;

[0068] Figure 3 It is a structural diagram of another kettle-based table data processing device disclosed in an embodiment of the present invention;

[0069] Figure 4 It is a structural diagram of a computer storage medium disclosed in an embodiment of the present invention. DETAILED DESCRIPTION

[0070] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. All other embodiments obtained by ordinary technicians in this field based on the embodiments of the present invention without making any creative efforts shall fall within the scope of protection of the present invention.

[0071] The terms "first," "second," and so on, in the description and claims of the present invention and the accompanying drawings are used to distinguish between different items, not to describe a specific order. Furthermore, the terms "including," "having," and any variations thereof, are intended to cover non-exclusive inclusions. For example, a process, method, apparatus, product, or end comprising a series of steps or elements is not limited to the listed steps or elements but may optionally include steps or elements not listed therein, or may optionally include other steps or elements inherent to such process, method, product, or end.

[0072] References herein to "embodiments" mean that a particular feature, structure, or characteristic described in connection with the embodiments may be included in at least one embodiment of the present invention. The appearance of this phrase in various places in the specification does not necessarily refer to the same embodiment, nor does it constitute a separate or alternative embodiment that is mutually exclusive of other embodiments. It is understood, both explicitly and implicitly, by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0073] The present invention discloses a table data processing method and device based on Kettle. By first obtaining a table data processing configuration table and then performing corresponding data processing on a target table according to the table data processing configuration table, the table data processing can be configured, the code quality of the table data processing is improved, and the subsequent maintenance is facilitated. The method can also reduce the need for manual SQL statement writing when performing table data processing, thereby solving the problems of existing database maintenance methods that are time-consuming and labor-intensive and easily lead to repeated development. Detailed descriptions are given below.

[0074] Example 1

[0075] See also Figure 1 , Figure 1 This is a flowchart of a kettle-based table data processing method disclosed in an embodiment of the present invention. Figure 1 As shown, the kettle-based table data processing method may include the following operations:

[0076] 101. Get table data processing configuration table.

[0077] In the above step 101, the table data processing configuration table includes at least the table name, SQL statement and parameter information corresponding to the target table, and the parameter information includes the parameters configured when executing the data processing corresponding to the SQL statement on the target table in kettle. By providing a configuration interface for configuring the table data processing configuration table in the interactive interface, and then the operation and maintenance personnel fill in the content of the table data processing configuration table (i.e., the table name, SQL statement and parameter information) in the configuration interface, the table data processing configuration table can be determined according to the content filled in by the operation and maintenance personnel in the configuration interface. In kettle, to realize the addition, deletion, modification and query of data in the table in the database, it is necessary to use SQL statements. SQL statements are a special programming statement for operating data in the database. Each table in the database will have a unique table name. The table name can be used to locate the specified table, and then the data in the specified table can be operated with the help of SQL statements. For example, the SQL statement "SELECT name, country FROM Websites" queries the data in the name and country fields in the Websites table, where the table name is Websites, the SQL statement is "SELECT * FROM *", and name and country are fields in the Websites table, which can be stored in the parameter information. Here, table data refers to the data stored in the table of the database. Kettle is a tool for managing databases. When executing data processing corresponding to SQL statements on the target table in kettle, it is necessary to set some necessary parameters, such as environment variables, positional parameters, named parameters, and related field names of the table. These necessary parameters can be stored in the parameter information. By storing the data or parameters that need to be configured in the table data processing configuration table, the configuration of the table data processing of the database can be achieved.

[0078] 102. Generate the target kettle program corresponding to the target table according to the table data processing configuration table.

[0079] In the above step 102, kettle is an ETL tool set, which can be used to manage the data in the database and use a graphical interface to describe the processing operations performed by the user on the data in the database.

[0080] The kettle tool consists of the following four parts:

[0081] SPOON: Used to design ETL transformation processes through a graphical interface.

[0082] PAN: Used to run ETL transformations designed by Spoon in batches (e.g. using a time scheduler). Pan is a background execution program with no graphical interface.

[0083] CHEF: Used to create jobs. Jobs allow for each transformation, task, script, etc., making it easier to automate the complex work of updating a data warehouse.

[0084] KITCHEN: Used to batch run tasks designed by Chef (for example, using a time scheduler). KITCHEN is also a background program.

[0085] A Transformation, or ETL conversion process, can be understood as assembling one or more different data source groups (i.e., different databases) into a data pipeline, performing preset data processing operations on the data in the data source group within the data pipeline, and ultimately outputting the data to a specific location (such as a file or database). A target kettle program can refer to a Transformation. Within a Transformation, you can implement common database processing operations such as inputting data from local text files, inputting data from database tables, outputting processed results to text files, querying the target table to return the required result fields based on set query conditions, and calling database stored procedures. After completing the configuration of the table data processing configuration table, the SQL statements and parameter information configured in the table data processing configuration table are filled into the Transformation to generate the specified Transformation (i.e., the target kettle program). The generated Transformation can then perform the corresponding data processing operations on the target table according to the SQL statements configured in the table data processing configuration table.

[0086] 103. Run the target kettle program to implement data processing corresponding to the SQL statement executed on the target table.

[0087] In the above step 103, the target kettle program is run, so that the data in the table in the database can be processed.

[0088] It can be seen that implementation Figure 1The described kettle-based table data processing method first obtains the table data processing configuration table, and then performs corresponding data processing on the target table according to the table data processing configuration table, thereby realizing the configurability of table data processing, replacing the manual code writing of operation and maintenance personnel for database maintenance, improving the code quality of table data processing, facilitating later maintenance, and reducing the need to manually write SQL statements when performing table data processing, thereby solving the problems of existing database maintenance methods that are time-consuming and labor-intensive, and easily lead to repeated development.

[0089] In an optional embodiment, there are multiple target tables, and in the table data processing configuration table, each target table has a corresponding table name, SQL statement, and parameter information;

[0090] And, generate the target kettle program corresponding to the target table according to the table data processing configuration table, including:

[0091] Generate the target kettle program corresponding to each target table according to the table name, SQL statement and parameter information corresponding to each target table in the table data processing configuration table;

[0092] And, run the target kettle program, including:

[0093] Run multiple target kettle programs corresponding to multiple target tables in parallel.

[0094] In this optional embodiment, batch processing of data can be achieved through configuration. When different data processing needs to be performed on each target table in multiple target tables, the corresponding SQL statement and parameter information can be configured for each target table in the table data processing configuration table, and a kettle program corresponding to each target table is generated accordingly. Then, multiple kettle programs are run in parallel to achieve parallel processing of data from multiple target tables, thereby improving processing efficiency. Specifically, a stored procedure corresponding to each target table can also be created based on the SQL statement corresponding to each target table configuration. The stored procedure contains all SQL statements corresponding to the target table. Then, a connection is opened for the stored procedure corresponding to each target table to execute. One connection is used to execute one stored procedure. In this way, parallel processing of data from multiple target tables can also be achieved.

[0095] It can be seen that the implementation of this optional embodiment can achieve parallel execution of table data processing when the table data processing configuration table is used to process data of multiple tables, thereby improving processing efficiency.

[0096] In this optional embodiment, further optionally, generating a target kettle program corresponding to each target table according to the table name, SQL statement and parameter information corresponding to the target table in the table data processing configuration table includes:

[0097] Determine whether the parameter information corresponding to each target table includes splicing information, where the splicing information at least includes the name of the splicing table;

[0098] When it is determined that the parameter information corresponding to the target table includes splicing information, the SQL statement and parameter information corresponding to the splicing table are determined from the table data processing configuration table according to the name of the splicing table;

[0099] Generate the target kettle program corresponding to the target table according to the table name corresponding to the target table and the SQL statement and parameter information corresponding to the spliced ​​table;

[0100] When it is determined that the parameter information corresponding to the target table does not include splicing information, a target kettle program corresponding to the target table is generated according to the table name, SQL statement and parameter information corresponding to the target table.

[0101] In this further optional embodiment, if it is necessary to perform the same data processing operation on two tables with different names, the name of the table pointed to by the data processing operation in the generated kettle program can be replaced, and then the kettle program with the replaced table name can be run to perform the same data processing operation on the two tables with different names, thereby achieving the splicing of SQL statements between different tables. This can simplify the code, improve code quality, and facilitate later maintenance. For example, to query the data of the name and country fields in the Websites table and the test table, the SQL statement used to query the data in the Websites table is "SELECT name, country FROM Websites". Then, when querying the test table, instead of filling in the statement "SELECT name, country FROM test" in the configuration table, the splicing information in the parameter information corresponding to the test table is filled in with the Websites table name, and then the SQL statement corresponding to the Websites table "SELECT name, country FROM Websites" is found by the Websites table name, and the table name in the statement is replaced to obtain the SQL statement corresponding to the test table "SELECT name, country FROM test".

[0102] It can be seen that by implementing this further optional embodiment, by realizing the splicing between SQL statements of different tables, it is possible to reuse SQL statements, simplify the code, improve the code quality of table data processing, and facilitate later maintenance.

[0103] In another optional embodiment, running the target kettle program includes:

[0104] Determine whether the target kettle program is used to insert data into multiple tables;

[0105] When it is determined that the target kettle program is used to execute the operation of inserting data into multiple tables, the target data that needs to be inserted into the multiple tables is determined according to the target kettle program, and multiple target data insertion operations are executed in parallel, wherein one target data insertion operation corresponds to one table, and each target data insertion operation is used to insert the target data into the table corresponding to the target data insertion operation.

[0106] In another optional embodiment, an insert statement in an SQL statement is used to insert data into a table. By determining whether the SQL statement corresponding to the target table contains insert statements for different tables, it is possible to determine whether the target kettle program is used to execute the operation of inserting data into multiple tables. After determining the target data to be inserted, when inserting the target data into different tables, the insert operation is parallelized to improve data processing efficiency.

[0107] It can be seen that by implementing this other optional embodiment, after determining the target data to be inserted, when inserting the target data into different tables, the insertion operation can be processed in parallel, thereby improving data processing efficiency.

[0108] In another optional embodiment, further optionally, determining whether the target kettle program is used to execute the operation of inserting data into multiple tables includes:

[0109] Determine whether the number of target SQL statements in the SQL statements corresponding to the target kettle program is greater than or equal to a first preset number threshold, where the target SQL statement refers to an SQL statement used to perform an operation of inserting data into a table;

[0110] When it is determined that the number of target SQL statements is greater than or equal to a first preset number threshold, determining that the target kettle program is used to execute an operation of inserting data into multiple tables;

[0111] When it is determined that the number of the target SQL statements is not greater than or equal to the first preset number threshold, it is determined that the target kettle program is not used to execute an operation of inserting data into multiple tables.

[0112] In this further optional embodiment, the first preset number threshold value may be set to 3. The target SQL statement includes at least an insert statement, and the insert statement is generally used in SQL statements to insert data into a table. Therefore, by determining whether the number of insert statements in the SQL statement corresponding to the target kettle program is greater than or equal to the first preset number threshold value, it can be determined whether there are multiple operations of inserting data into the table in the target kettle program.

[0113] It can be seen that the implementation of this further optional embodiment can accurately determine whether the kettle program is used to execute the operation of inserting data into multiple tables, providing a basis for subsequent parallel processing.

[0114] In this further optional embodiment, further optionally, after determining that the number of target SQL statements is not greater than or equal to the first preset number threshold, and before determining that the target kettle program is not used to execute the operation of inserting data into multiple tables, the kettle-based table data processing method further includes:

[0115] Determining whether the number of tables pointed to by the target SQL statement is greater than or equal to a second preset number threshold;

[0116] When it is determined that the number of tables pointed to by the target SQL statement is greater than or equal to a second preset number threshold, triggering execution of an operation of determining that the target kettle program is used to execute an operation of inserting data into multiple tables;

[0117] When it is determined that the number of tables pointed to by the target SQL statement is not greater than or equal to the second preset number threshold, an operation of determining that the target kettle program is not used to execute an operation of inserting data into multiple tables is triggered.

[0118] In this further optional embodiment, the second preset quantity threshold value can be set to 3. In SQL statements, an insert statement is usually only used to insert data into one table, but there are some special insert statements that can insert data into multiple tables at the same time in one insert statement, for example, insert all and insert first statements. For another example, insert all into stu1(id, name, sex) into stu2(id, name, sex), the operation performed by this statement is to insert data (id, name, sex) into the stu1 table and the stu2 table at the same time. When there are similar special insert statements in the SQL statements corresponding to the target kettle program, it is obviously not accurate to continue to judge whether the target kettle program is used to insert data into multiple tables based on the number of insert statements. Therefore, when the number of insert statements is not greater than or equal to three, continue to judge whether the number of tables pointed to by the insert statement is greater than or equal to three. When it is judged that the number of tables pointed to by the insert statement is greater than or equal to three, it is determined that the target kettle program is used to execute data insertion into multiple tables. This can make the judgment result more accurate.

[0119] It can be seen that the implementation of this further optional embodiment can more accurately determine whether the kettle program is used to execute the operation of inserting data into multiple tables, providing a basis for subsequent parallel processing.

[0120] In yet another optional embodiment, after running the target Kettle program to implement the step of executing data processing corresponding to the SQL statement on the target table, the Kettle-based table data processing method further includes:

[0121] Determine whether the SQL statement in the target kettle program is completely executed;

[0122] When it is determined that the SQL statements in the target kettle program have not been completely executed, a warning prompt is output, which includes all the SQL statements that have not been successfully executed.

[0123] In another optional embodiment, since SQL statements have specific grammatical requirements, if the SQL statements entered in the table data processing configuration table do not conform to the grammatical rules during configuration, the SQL statements may not be successfully executed. After the table data processing is configured using the table data processing configuration table, if the execution of the SQL statements in the table data processing configuration table is not monitored, it is easy for maintenance personnel to enter incorrect SQL statements in the table data processing configuration table, resulting in some SQL statements failing to execute successfully, but the maintenance personnel are unaware of this. Therefore, when the SQL statements in the table data processing configuration table cannot be successfully executed, the unsuccessful SQL statements are output. This helps the maintenance personnel know the execution status of the SQL statements in the table data processing configuration table, making maintenance more convenient.

[0124] It can be seen that implementing this further optional embodiment and outputting the SQL statements that have not been fully executed in the target kettle program is helpful for maintenance personnel to know the execution status of the SQL statements in the table data processing configuration table, which is more convenient for maintenance.

[0125] It can be seen that implementation Figure 1 The described kettle-based table data processing method first obtains a table data processing configuration table, and then performs corresponding data processing on the target table according to the table data processing configuration table, thereby achieving configurability of table data processing, improving the code quality of table data processing, and facilitating subsequent maintenance. In addition, since the SQL statements for processing table data usually have a large number of similar or identical statements, the configuration of table data processing can achieve the reuse of SQL statements and simplify the process of table data processing. It also achieves parallel execution of SQL statements, improving the processing efficiency of table data. By implementing splicing between SQL statements of different tables, it is possible to reuse SQL statements, simplify the code, improve the code quality of table data processing, and facilitate subsequent maintenance.

[0126] Optionally, it is also possible to upload the running result information of the kettle-based table data processing method to the blockchain.

[0127] Specifically, the results of the Kettle-based table data processing method generate corresponding execution result information. Specifically, this execution result information is obtained by running the target Kettle program and indicates the execution status of the Kettle program, for example, whether the SQL statement was fully executed successfully, which SQL statements were not successfully executed, etc. Uploading this execution result information to the blockchain ensures its security and fairness and transparency to users. Users can download this execution result information from the blockchain to verify whether the execution results of the Kettle-based table data processing method have been tampered with. The blockchain referred to in this example is a new application model of computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanisms, and encryption algorithms. Blockchain is essentially a decentralized database, a string of data blocks generated using cryptographic methods. Each data block contains information about a batch of network transactions, which is used to verify the validity of the information (to prevent counterfeiting) and generate the next block. Blockchain can include the blockchain underlying platform, the platform product service layer, and the application service layer.

[0128] Example 2

[0129] See also Figure 2 , Figure 2 This is a schematic diagram of the structure of a kettle-based table data processing device disclosed in an embodiment of the present invention. Figure 2 As shown, the kettle-based table data processing device may include:

[0130] The acquisition module 201 is used to acquire a table data processing configuration table, which includes at least a table name, an SQL statement, and parameter information corresponding to a target table. The parameter information includes parameters configured when executing data processing corresponding to the SQL statement on the target table in the kettle;

[0131] A generating module 202 is used to generate a target kettle program corresponding to a target table according to the table data processing configuration table;

[0132] The running module 203 is used to run the target kettle program to implement data processing corresponding to the SQL statement executed on the target table.

[0133] For the detailed description of the above-mentioned kettle-based table data processing device, reference may be made to the detailed description of the above-mentioned kettle-based table data processing method, which will not be repeated here.

[0134] Example 3

[0135] See also Figure 3 , Figure 3This is a schematic diagram of the structure of another kettle-based table data processing device disclosed in an embodiment of the present invention. Figure 3 As shown, the device may include:

[0136] A memory 301 storing executable program code;

[0137] a processor 302 coupled to the memory 301;

[0138] The processor 302 calls the executable program code stored in the memory 301 to execute the steps of the kettle-based table data processing method disclosed in the first embodiment of the present invention.

[0139] Example 4

[0140] The embodiment of the present invention discloses a computer storage medium 401, which stores computer instructions. When the computer instructions are called, they are used to execute the steps of the kettle-based table data processing method disclosed in the first embodiment of the present invention.

[0141] The device embodiments described above are merely illustrative, wherein the modules described as separate components may or may not be physically separate, and the components shown as modules may or may not be physical modules, i.e., they may be located in one place or distributed across multiple network modules. Some or all of the modules may be selected based on actual needs to achieve the objectives of the present embodiment. Those skilled in the art can understand and implement the present invention without inventive effort.

[0142] Through the detailed description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus the necessary general hardware platform, or of course, by means of hardware. Based on this understanding, the above technical solution, in essence, or the portion that contributes to the prior art, can be embodied in the form of a software product, which can be stored in a computer-readable storage medium, including a read-only memory (ROM), a random access memory (RAM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), a one-time programmable read-only memory (OTPROM), an electronically erasable programmable read-only memory (EEPROM), a compact disc read-only memory (CD-ROM) or other optical disc storage, magnetic disk storage, magnetic tape storage, or any other computer-readable medium capable of carrying or storing data.

[0143] Finally, it should be noted that the kettle-based table data processing method and device disclosed in the embodiment of the present invention only disclose the preferred embodiments of the present invention, which are only used to illustrate the technical solutions of the present invention, rather than to limit them. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments can still be modified, or some of the technical features therein can be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the various embodiments of the present invention.

Claims

1. A table data processing method based on kettle, characterized in that: The method comprises: Obtaining a table data processing configuration table, wherein the table data processing configuration table includes at least a table name, an SQL statement, and parameter information corresponding to a target table, wherein the parameter information includes parameters configured when executing data processing corresponding to the SQL statement on the target table in Kettle. The table data processing configuration table is obtained by manually configuring through a preset graphical interactive interface; Generate a target kettle program corresponding to the target table according to the table data processing configuration table; Run the target kettle program to execute the data processing corresponding to the SQL statement on the target table, and upload the data processing results to the blockchain; There are multiple target tables, and in the table data processing configuration table, each target table has a corresponding table name, SQL statement, and parameter information; and generating a target kettle program corresponding to the target table according to the table data processing configuration table includes: generating a target kettle program corresponding to the target table according to the table name, SQL statement, and parameter information corresponding to each target table in the table data processing configuration table; and running the target kettle program includes: running multiple target kettle programs corresponding to multiple target tables in parallel; Wherein, the generating of the target kettle program corresponding to the target table according to the table name, SQL statement and parameter information corresponding to each target table in the table data processing configuration table includes: judging whether the parameter information corresponding to each target table contains splicing information, wherein the splicing information at least includes the name of the spliced ​​table; when judging that the parameter information corresponding to the target table contains the splicing information, determining the SQL statement and parameter information corresponding to the spliced ​​table from the table data processing configuration table according to the name of the spliced ​​table; generating the target kettle program corresponding to the target table according to the table name corresponding to the target table and the SQL statement and parameter information corresponding to the spliced ​​table; when judging that the parameter information corresponding to the target table does not contain the splicing information, generating the target kettle program corresponding to the target table according to the table name, SQL statement and parameter information corresponding to the target table; If the same data processing operation is performed on two tables with different names, the name of the table pointed to by the data processing operation in the target kettle program is replaced, and the target kettle program with the replaced table name is run; Wherein, the running of the target kettle program further includes: determining whether the target kettle program is used to execute the operation of inserting data into multiple tables; when it is determined that the target kettle program is used to execute the operation of inserting data into the multiple tables, determining the target data to be inserted into the multiple tables according to the target kettle program, and running multiple target data insertion operations in parallel, wherein one target data insertion operation corresponds to one table, and each target data insertion operation is used to insert the target data into the table corresponding to the target data insertion operation; The step of determining whether the target kettle program is used to insert data into multiple tables comprises: determining whether the number of target SQL statements in the SQL statements corresponding to the target kettle program is greater than or equal to a first preset number threshold, wherein the target SQL statement refers to an SQL statement used to insert data into a certain table; when it is determined that the number of target SQL statements is greater than or equal to the first preset number threshold, determining that the target kettle program is used to insert data into multiple tables; When it is determined that the number of the target SQL statements is not greater than or equal to the first preset number threshold, it is determined that the target kettle program is not used to execute an operation of inserting data into multiple tables.

2. The kettle-based table data processing method according to claim 1, characterized in that: After determining that the number of the target SQL statements is not greater than or equal to the first preset number threshold, and before determining that the target kettle program is not used to execute an operation of inserting data into multiple tables, the method further includes: Determining whether the number of tables pointed to by the target SQL statement is greater than or equal to a second preset number threshold; When it is determined that the number of tables pointed to by the target SQL statement is greater than or equal to the second preset number threshold, triggering the execution of the operation of determining that the target kettle program is used to execute the operation of inserting data into multiple tables; When it is determined that the number of tables pointed to by the target SQL statement is not greater than or equal to the second preset number threshold, the operation of determining that the target kettle program is not used to execute the operation of inserting data into multiple tables is triggered.

3. The kettle-based table data processing method according to any one of claims 1-2, characterized in that: After the step of running the target kettle program to implement data processing corresponding to the SQL statement on the target table, the method further includes: Determine whether the SQL statement in the target kettle program is completely executed; When it is determined that the SQL statements in the target kettle program have not been completely executed, a warning prompt is output, and the warning prompt includes all SQL statements that have not been successfully executed.

4. A table data processing device based on kettle, characterized in that: The device comprises: an acquisition module, configured to acquire a table data processing configuration table, wherein the table data processing configuration table includes at least a table name, an SQL statement, and parameter information corresponding to a target table, wherein the parameter information includes parameters configured when executing data processing corresponding to the SQL statement on the target table in a kettle, and the table data processing configuration table is obtained by manually configuring through a preset graphical interactive interface; A generating module, configured to generate a target kettle program corresponding to the target table according to the table data processing configuration table; The running module is used to run the target kettle program to realize the data processing corresponding to the SQL statement on the target table and upload the data processing result to the blockchain; there are multiple target tables, and in the table data processing configuration table, each target table has a corresponding table name, SQL statement and parameter information; and the specific method of the generation module to generate the target kettle program corresponding to the target table according to the table data processing configuration table is: according to the table name, SQL statement and parameter information corresponding to each target table in the table data processing configuration table, the target kettle program corresponding to the target table is generated; and the running module runs the target kettle program. The specific method of the e program is as follows: running multiple target kettle programs corresponding to multiple target tables in parallel; the specific method of the generation module generating the target kettle program corresponding to the target table according to the table name, SQL statement and parameter information corresponding to each target table in the table data processing configuration table is as follows: judging whether the parameter information corresponding to each target table contains splicing information, and the splicing information at least includes the name of the splicing table; when it is judged that the parameter information corresponding to the target table contains the splicing information, the SQL statement and parameter information corresponding to the splicing table are determined from the table data processing configuration table according to the name of the splicing table; The corresponding SQL statement and parameter information generate the target kettle program corresponding to the target table; when it is determined that the parameter information corresponding to the target table does not contain the splicing information, the target kettle program corresponding to the target table is generated according to the table name, SQL statement and parameter information corresponding to the target table; if the same data processing operation is performed on two tables with different names, the name of the table pointed to by the data processing operation in the target kettle program is replaced, and the target kettle program with the replaced table name is run; the running of the target kettle program also includes: determining whether the target kettle program is used to execute the operation of inserting data into multiple tables; when it is determined that the target kettle program is used to execute the operation of inserting data into multiple tables When the tle program is used to execute the operation of inserting data into the multiple tables, the target data to be inserted into the multiple tables is determined according to the target kettle program, and multiple target data insertion operations are run in parallel, wherein one target data insertion operation corresponds to one table, and each target data insertion operation is used to insert the target data into the table corresponding to the target data insertion operation; the determining whether the target kettle program is used to execute the operation of inserting data into the multiple tables includes: determining whether the number of target SQL statements in the SQL statement corresponding to the target kettle program is greater than or equal to a first preset number threshold, the target SQL statement refers to an SQL statement used to execute the operation of inserting data into a certain table;When it is determined that the number of the target SQL statements is greater than or equal to the first preset number threshold, the target kettle program is determined to be used to execute the operation of inserting data into multiple tables; when it is determined that the number of the target SQL statements is not greater than or equal to the first preset number threshold, the target kettle program is determined not to be used to execute the operation of inserting data into multiple tables.

5. A table data processing device based on kettle, characterized in that: The device comprises: a memory storing executable program code; a processor coupled to the memory; The processor calls the executable program code stored in the memory to execute the kettle-based table data processing method according to any one of claims 1 to 3.

6. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the kettle-based table data processing method according to any one of claims 1 to 3 is implemented.

Images