Method, system, device and medium for quickly deleting temporary tables of databases

Abstract

The present invention discloses a method, system, device and medium for quickly deleting a database temporary table, and belongs to the technical field of database technology. The technical problem to be solved by the present invention is how to realize the rapid deletion of data in the process of database data flow, shorten the time consumption of data management, and improve the efficiency of data management. The technical scheme is: a new Java interface and an implementation class corresponding to the Java interface are created; the Java interfaces are IDeleteTmpTable interface and DeleteTmpTable interface, a dataSync method is defined in the IDeleteTmpTable interface, and the dataSync method is implemented in the DeleteTmpTable interface; in the dataSync method, the basic information of the temporary tables generated in batches during the data collection process is queried through the system table of the database, a SQL statement is defined and executed, and a result set is returned.

Description

Technical Field

[0001] The invention relates to the technical field of databases, and in particular to a method, system, equipment and medium for quickly deleting a temporary table of a database. Background Art

[0002] In order to improve the efficiency of database use and the accuracy of statistical tables, useless backup tables and temporary tables are usually deleted. In the prior art, backup tables and temporary tables are deleted manually by writing SQL statements one by one, and the generated temporary tables are processed one by one, which cannot complete the deletion work quickly in batches. This processing solution is simple, but it requires manual processing. Once the temporary table data is huge and generated at any time, manual intervention is required all the time, which is inefficient and has high labor costs.

[0003] When using GPSS to insert data in batches, a temporary table will be generated for every 5,000 data items. The data collection process will continue to operate tables with a large amount of data, so more temporary tables will be generated. When the number of temporary tables reaches more than 200, operating the temporary tables through the database connection tool will be abnormally slow, affecting data management efficiency.

[0004] In summary, the existing data governance and data synchronization process has the following shortcomings:

[0005] ①Data clearing in data governance, data synchronization and other tasks takes a long time;

[0006] ② Manual processing is inefficient and costly;

[0007] ③ During the database data flow process, a large number of temporary tables are generated in real time, affecting the query speed and statistical accuracy.

[0008] Therefore, how to achieve rapid deletion of data during database data flow, shorten data management time, and improve data management efficiency is a technical problem that needs to be solved urgently. Summary of the invention

[0009] The technical task of the present invention is to provide a method, system, device and medium for quickly deleting temporary tables in a database to solve the problem of how to quickly delete data during the data flow process of the database, shorten the time spent on data management, and improve the efficiency of data management.

[0010] The technical task of the present invention is achieved in the following way: a method for quickly deleting a temporary table in a database, the method is as follows:

[0011] Create a new Java interface and its corresponding implementation class. The Java interfaces are IDeleteTmpTable and DeleteTmpTable. Define the dataSync method in the IDeleteTmpTable interface and implement the dataSync method in the DeleteTmpTable interface.

[0012] In the dataSync method, query the basic information of the temporary tables generated in batches during the data collection process through the database's own system table information_schema.pg_tables, define and execute SQL statements, and return a List <map>Result collection;

[0013] Iterating over a List <map>Result set, query the schema and table name information of each temporary table, and splice them into fields in the form of <schema.table name>;

[0014] Combine the fields of <schema.table name> into a set of executable SQL statements for deleting tables in a for loop;

[0015] Use the workflow tool to configure a timer and schedule the DeleteTmpTable interface through the timer to achieve the purpose of deleting temporary tables in batches in a scheduled cycle.

[0016] Preferably, when the database adopts MySQL database, the defined sql statement is: String sql = "selectschemaname,tablename,tableowner,tablespace,hasindexes,hasrules,hastriggersfrom pg_tables where tablename like 'gpssext_%'".

[0017] Preferably, when the database adopts MySQL database, the table deletion SQL statement is drop external tablegpssext_9a935c2494b95850898b7a3838050fbc.

[0018] As a preferred method, the timed cycle batch deletion of temporary tables adopts a small number of multiple batches, specifically:

[0019] A commit is made every 200 deleted records, and efficient temporary table deletion is performed in small batches to improve the efficiency of temporary table deletion.

[0020] Preferably, the basic information of the temporary table includes the table name, the mode to which it belongs, the table owner, whether it has an index, and a hash rule.

[0021] A database temporary table quick deletion system, the system is used to implement the database temporary table quick deletion method as mentioned above; the system comprises:

[0022] Create a new module to create a Java interface and the corresponding implementation class of the Java interface; the Java interface is the IDeleteTmpTable interface and the DeleteTmpTable interface, define the dataSync method in the IDeleteTmpTable interface, and implement the dataSync method in the DeleteTmpTable interface;

[0023] The query and definition module is used in the dataSync method to query the basic information of the temporary tables generated in batches during the data collection process through the database's own system table information_schema.pg_tables, define and execute SQL statements, and return a List <map>The result set includes the basic information of the temporary table, including the name of the temporary table, the mode it belongs to, the table owner, whether it has an index, and the hash rule.

[0024] Splicing module, used to traverse the List <map>Result set, query the schema and table name information of each temporary table, and splice them into fields in the form of <schema.table name>;

[0025] The combination module is used to combine the fields of <schema.table name> into a set of executable table deletion SQL statements in a for loop;

[0026] The timing scheduling module is used to configure the timer using the workflow tool, and schedule the DeleteTmpTable interface through the timer to achieve the purpose of deleting temporary tables in batches in a timed cycle.

[0027] Preferably, when the database adopts MySQL database, the defined sql statement is: String sql = "selectschemaname,tablename,tableowner,tablespace,hasindexes,hasrules,hastriggersfrom pg_tables where tablename like 'gpssext_%'";

[0028] The SQL statement for deleting a table is drop external table gpssext_9a935c2494b95850898b7a3838050fbc.

[0029] Preferably, the timed cycle batch deletion of temporary tables adopts a small number of batches, specifically:

[0030] A commit is made every 200 deleted records, and efficient temporary table deletion is performed in small batches to improve the efficiency of temporary table deletion.

[0031] An electronic device comprising: a memory and at least one processor;

[0032] Wherein, the memory stores a computer program;

[0033] The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the database temporary table fast deletion method as described above.

[0034] A computer-readable storage medium stores a computer program, which can be executed by a processor to implement the database temporary table quick deletion method as described above.

[0035] The database temporary table quick deletion method, system, device and medium of the present invention have the following advantages:

[0036] (I) Researchers and developers use the present invention to develop corresponding data governance functions for users to use. Implementers can use the present invention to quickly respond to customer requirements during data governance and data synchronization to meet user needs. Therefore, the present invention can be used by both researchers and implementers. According to the data governance requirements raised by users, the present invention can be used to quickly clean up data, achieve data governance effects, and quickly deliver to users for use;

[0037] (ii) The present invention can be applied to the tobacco industry to quickly delete temporary tables in the Greenplum database during data processing such as data management and data synchronization;

[0038] (III) The present invention can reduce manual intervention, shorten data management time, respond quickly to customer needs, and realize rapid deletion of data by obtaining basic information of database temporary tables through system tables, batch generating executable SQL statements through scripts, configuring timed scheduling tasks through workflows, and efficiently executing deletion commands in small quantities and batches. This reduces manual intervention, shortens data management time, and improves data management efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The present invention is further described below in conjunction with the accompanying drawings.

[0040] Attached Figure 1 A flowchart of a method for quickly deleting a database temporary table. DETAILED DESCRIPTION

[0041] The method, system, device and medium for quickly deleting a temporary table in a database of the present invention are described in detail below with reference to the accompanying drawings and specific embodiments of the specification.

[0042] Embodiment 1: As attached Figure 1 As shown, this embodiment provides a method for quickly deleting a database temporary table, and the method is specifically as follows:

[0043] S1. Create a new Java interface and the corresponding implementation class of the Java interface; the Java interface is the IDeleteTmpTable interface and the DeleteTmpTable interface, define the dataSync method in the IDeleteTmpTable interface, and implement the dataSync method in the DeleteTmpTable interface;

[0044] The key code that defines the dataSync method in the IDeleteTmpTable interface is as follows:

[0045] public interface IDeleteTmpTable {

[0046] public void dataSync();

[0047] };

[0048] The key code to implement the dataSync method in the DeleteTmpTable interface is as follows:

[0049] public class DeleteTmpTable implements IDeleteTmpTable{

[0050] @Override

[0051] public void dataSync() {

[0052] / / TODO

[0053] }

[0054] };

[0055] S2. In the dataSync method, query the basic information of the temporary tables generated in batches during the data collection process through the system table information_schema.pg_tables of the database, define and execute SQL statements, and return a List <map>Result collection (collection result);

[0056] S3, traversing the List <map>Result set, query the schema and table name information of each temporary table, and splice them into fields in the form of <schema.table name>; among them, traverse List <map>The result set is as follows:

[0057] for (Map map : result) {

[0058] String schema = (String) map.get("schemaname");

[0059] String tableName = (String) map.get("tablename");

[0060] String table = schema + "." + tableName;

[0061] };

[0062] S4. Combine the fields of <schema.table name> into a set of executable SQL statements for deleting the table in a for loop;

[0063] S5. Use the workflow tool to configure a timer, and use the timer to schedule the DeleteTmpTable interface to achieve the purpose of deleting temporary tables in batches in a scheduled cycle.

[0064] When the database in this embodiment adopts MySQL database, the defined sql statement is: String sql = "select schemaname,tablename,tableowner,tablespace,hasindexes,hasrules,hastriggers from pg_tables where tablename like 'gpssext_%'".

[0065] When the database in this embodiment adopts MySQL database, the table deletion SQL statement is drop external tablegpssext_9a935c2494b95850898b7a3838050fbc.

[0066] In this embodiment, the timed cyclic batch deletion of temporary tables adopts a small number of multiple batches method, specifically:

[0067] A commit is made every 200 deleted records, and efficient temporary table deletion is performed in small batches to improve the efficiency of temporary table deletion.

[0068] The basic information of the temporary table in this embodiment includes the table name, the mode to which it belongs, the table owner, whether there is an index, and the hash rule.

[0069] Embodiment 2: A database temporary table quick deletion system, the system is used to implement the database temporary table quick deletion method in embodiment 1; the system comprises:

[0070] Create a new module to create a Java interface and the corresponding implementation class of the Java interface; the Java interface is the IDeleteTmpTable interface and the DeleteTmpTable interface, define the dataSync method in the IDeleteTmpTable interface, and implement the dataSync method in the DeleteTmpTable interface;

[0071] The key code that defines the dataSync method in the IDeleteTmpTable interface is as follows:

[0072] public interface IDeleteTmpTable {

[0073] public void dataSync();

[0074] };

[0075] The key code to implement the dataSync method in the DeleteTmpTable interface is as follows:

[0076] public class DeleteTmpTable implements IDeleteTmpTable{

[0077] @Override

[0078] public void dataSync() {

[0079] / / TODO

[0080] }

[0081] };

[0082] The query and definition module is used in the dataSync method to query the basic information of the temporary tables generated in batches during the data collection process through the database's own system table information_schema.pg_tables, define and execute SQL statements, and return a List <map>The result set includes the basic information of the temporary table, including the name of the temporary table, the mode it belongs to, the table owner, whether it has an index, and the hash rule.

[0083] Splicing module, used to traverse the List <map>Result set, query the schema and table name information of each temporary table, and splice them into fields in the form of <schema.table name>; among them, traverse List <map>The result set is as follows:

[0084] for (Map map : result) {

[0085] String schema = (String) map.get("schemaname");

[0086] String tableName = (String) map.get("tablename");

[0087] String table = schema + "." + tableName;

[0088] };

[0089] The combination module is used to combine the fields of <schema.table name> into a set of executable table deletion SQL statements in a for loop;

[0090] The timing scheduling module is used to configure the timer using the workflow tool, and schedule the DeleteTmpTable interface through the timer to achieve the purpose of deleting temporary tables in batches in a timed cycle.

[0091] When the database in this embodiment adopts MySQL database, the defined sql statement is: String sql = "select schemaname, tablename, tableowner, tablespace, hasindexes, hasrules, hastriggers from pg_tables where tablename like 'gpssext_%'";

[0092] The SQL statement for deleting a table is drop external table gpssext_9a935c2494b95850898b7a3838050fbc.

[0093] In this embodiment, the timed cyclic batch deletion of temporary tables adopts a small number of multiple batches method, specifically:

[0094] A commit is made every 200 deleted records, and efficient temporary table deletion is performed in small batches to improve the efficiency of temporary table deletion.

[0095] Embodiment 3: This embodiment also provides an electronic device, including: a memory and a processor;

[0096] Wherein, the memory stores computer-executable instructions;

[0097] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the database temporary table fast deletion method in any embodiment of the present invention.

[0098] The processor may be a central processing unit (CPU), other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The processor may be a microprocessor or any conventional processor, etc.

[0099] The memory can be used to store computer programs and / or modules. The processor realizes various functions of the electronic device by running or executing the computer programs and / or modules stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, at least one application required for a function, etc.; the data storage area can store data created according to the use of the terminal, etc. In addition, the memory can also include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (SMC), a secure digital (SD) card, a flash memory card, at least one disk storage period, a flash memory device, or other volatile solid-state storage devices.

[0100] Embodiment 4: This embodiment also provides a computer-readable storage medium, in which a plurality of instructions are stored, and the instructions are loaded by a processor, so that the processor executes the database temporary table fast deletion method in any embodiment of the present invention. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code that implements the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0101] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0102] Examples of storage media for providing program code include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RYM, DVD-RW, DVD+RW), magnetic tapes, non-volatile memory cards, and ROMs. Alternatively, program code may be downloaded from a server computer via a communication network.

[0103] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0104] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0105] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.< / map> < / map> < / map> < / map> < / map> < / map> < / map> < / map> < / map> < / map>

Claims

1. A method for quickly deleting a database temporary table, characterized in that: The method is as follows: Create a new Java interface and its corresponding implementation class. The Java interfaces are IDeleteTmpTable and DeleteTmpTable. Define the dataSync method in the IDeleteTmpTable interface and implement the dataSync method in the DeleteTmpTable interface. In the dataSync method, query the basic information of the temporary tables generated in batches during the data collection process through the database's own system table information_schema.pg_tables, define and execute SQL statements, and return a List <map> Result collection;< / map> Iterating over a List <map> Result set, query the schema and table name information of each temporary table, and splice them into fields in the form of <schema.table name>;< / map> Combine the fields of <schema.table name> into a set of executable SQL statements for deleting tables in a for loop; Use the workflow tool to configure a timer and schedule the DeleteTmpTable interface through the timer to achieve the purpose of deleting temporary tables in batches in a scheduled cycle.

2. The method for quickly deleting a database temporary table according to claim 1, characterized in that: When the database uses MySQL database, the defined sql statement is: String sql = "select schemaname,tablename,tableowner,tablespace,hasindexes,hasrules,hastriggers from pg_tables wheretablename like 'gpssext_%'".

3. The method for quickly deleting a database temporary table according to claim 1, characterized in that: When the database uses MySQL, the SQL statement for deleting a table is drop external table gpssext_9a935c2494b95850898b7a3838050fbc.

4. The method for quickly deleting a database temporary table according to claim 1, characterized in that: The timed cycle batch deletion of temporary tables adopts a small number of multiple batches method, specifically: A commit is made every 200 deleted records, and efficient temporary table deletion is performed in small batches.

5. The method for quickly deleting a database temporary table according to any one of claims 1 to 4, characterized in that: The basic information of a temporary table includes the table name, mode, owner, index, and hash rule.

6. A database temporary table quick deletion system, characterized in that: The system is used to implement the database temporary table quick deletion method according to any one of claims 1 to 5; the system comprises: Create a new module to create a Java interface and the corresponding implementation class of the Java interface; the Java interface is the IDeleteTmpTable interface and the DeleteTmpTable interface, define the dataSync method in the IDeleteTmpTable interface, and implement the dataSync method in the DeleteTmpTable interface; The query and definition module is used in the dataSync method to query the basic information of the temporary tables generated in batches during the data collection process through the database's own system table information_schema.pg_tables, define and execute SQL statements, and return a List <map> The result set includes the basic information of the temporary table, including the name of the temporary table, the mode it belongs to, the table owner, whether it has an index, and the hash rule.< / map> Splicing module, used to traverse the List <map> Result set, query the schema and table name information of each temporary table, and splice them into fields in the form of <schema.table name>;< / map> The combination module is used to combine the fields of <schema.table name> into a set of executable table deletion SQL statements in a for loop; The timing scheduling module is used to configure the timer using the workflow tool, and schedule the DeleteTmpTable interface through the timer to achieve the purpose of deleting temporary tables in batches in a timed cycle.

7. The database temporary table quick deletion system according to claim 6, characterized in that: When the database uses MySQL database, the defined sql statement is: String sql = "select schemaname,tablename,tableowner,tablespace,hasindexes,hasrules,hastriggers from pg_tables wheretablename like 'gpssext_%'"; The SQL statement for deleting a table is drop external table gpssext_9a935c2494b95850898b7a3838050fbc.

8. The database temporary table quick deletion system according to claim 6 or 7, characterized in that: The timed cycle batch deletion of temporary tables adopts a small number of multiple batches method, specifically: A commit is made every 200 deleted records, and efficient temporary table deletion is performed in small batches.

9. An electronic device, characterized in that: include: memory and at least one processor; Wherein, the memory stores a computer program; The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the database temporary table fast deletion method according to any one of claims 1 to 5.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which can be executed by a processor to implement the database temporary table fast deletion method according to any one of claims 1 to 5.

Images