Data warehousing method and computer readable storage medium

Abstract

This invention discloses a data import method and a computer-readable storage medium. The method includes: acquiring data to be imported according to a preset period; storing the data to be imported as a temporary file according to a preset storage format, and uploading the temporary file to a distributed object storage system; creating an S3 external table based on the fields of a target table in the database, and setting the storage path of the S3 external table to the storage path of the distributed object storage system; and importing the data from the temporary file into the target table through the S3 external table. This invention can improve the import speed of batch data and reduce the load on the master node.

Description

Technical Field

[0001] This invention relates to the field of data storage technology, and in particular to a data import method and a computer-readable storage medium. Background Technology

[0002] For Greenplum databases, when importing data in batches, using the traditional "insert into target table values()" method is not only slow when the data volume is large, but also puts a lot of pressure on the Greenplum master node. In addition, if the imported data needs to overwrite the original table data, the method of clearing the table before importing will cause the table data to be empty during the import process. If the target table has an index, the import speed will be much slower.

[0003] Therefore, improving the speed of batch data import in Greenplum databases has become a problem to be solved. Summary of the Invention

[0004] The technical problem to be solved by the present invention is to provide a data import method and a computer-readable storage medium, which can improve the import speed of batch data and reduce the load on the master node.

[0005] To solve the above-mentioned technical problems, the technical solution adopted by the present invention is: a data entry method, comprising:

[0006] According to the preset cycle, obtain the data to be entered into the database;

[0007] According to the preset storage format, the data to be stored is stored as a temporary file, and the temporary file is uploaded to the distributed object storage system;

[0008] Based on the fields of the target table in the database, create an S3 external table and set the storage path of the S3 external table to the storage path of the distributed object storage system.

[0009] The data in the temporary file is imported into the target table through the S3 external table.

[0010] The present invention also proposes a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described above.

[0011] The beneficial effects of this invention are as follows: by using the S3 external table to import batch data, the parallel loading of data by computing nodes can be effectively utilized, thereby improving the import speed of batch data. Furthermore, since the data does not pass through the master node, the load on the master node is reduced, and the import of data is not limited by the bottleneck of the master node. Attached Figure Description

[0012] Figure 1 This is a flowchart of a data entry method according to the present invention;

[0013] Figure 2 This is a flowchart of the method according to Embodiment 1 of the present invention. Detailed Implementation

[0014] To explain the technical content, objectives, and effects of the present invention in detail, the following description is provided in conjunction with the embodiments and accompanying drawings.

[0015] Please see Figure 1 A data import method, including:

[0016] According to the preset cycle, obtain the data to be entered into the database;

[0017] According to the preset storage format, the data to be stored is stored as a temporary file, and the temporary file is uploaded to the distributed object storage system;

[0018] Based on the fields of the target table in the database, create an S3 external table and set the storage path of the S3 external table to the storage path of the distributed object storage system.

[0019] The data in the temporary file is imported into the target table through the S3 external table.

[0020] As can be seen from the above description, the beneficial effects of the present invention are: it can improve the import speed of batch data and reduce the load on the master node.

[0021] Furthermore, before importing the data from the temporary file into the target table through the S3 external table, the process further includes:

[0022] Determine whether the target table contains an index;

[0023] If not, then perform the step of importing the data in the temporary file into the target table through the external table of S3;

[0024] If so, a temporary table is created in the database, and the data in the temporary file is imported into the temporary table through the S3 external table;

[0025] Create the same index in the temporary table based on the index in the target table;

[0026] Delete the target table and rename the temporary table to the name of the target table.

[0027] As described above, for target tables with indexes, directly importing data will reduce the data import speed. Therefore, by creating a temporary table and then creating the index after importing the data, the data import speed can be improved.

[0028] Furthermore, before importing the data from the temporary file into the target table through the S3 external table, the process further includes:

[0029] The data in the temporary file is compressed.

[0030] As described above, this method can improve data import efficiency, reduce database I / O, and save storage space.

[0031] Furthermore, the step of obtaining the data to be stored according to a preset period specifically involves:

[0032] If the data is streaming data, the streaming data will be published to the distributed messaging system, and the streaming data will be retrieved in batches from the distributed messaging system according to the preset period to obtain the data to be stored in the database.

[0033] If the data is file data, the file data will be downloaded according to the preset period and used as data to be added to the database.

[0034] Furthermore, after uploading the temporary file to the distributed object storage system, the process further includes:

[0035] Delete local temporary files.

[0036] As described above, this saves local storage space and avoids data redundancy.

[0037] Furthermore, after importing the data from the temporary file into the target table through the S3 external table, the process further includes:

[0038] Delete the S3 external table.

[0039] Furthermore, it also includes:

[0040] When exporting data, an S3 writable external table is created based on the fields of the table containing the data to be exported in the database, and the storage path of the S3 external table is set to the storage path of the distributed object storage system.

[0041] The data to be exported is inserted into the S3 writable external table.

[0042] As described above, data can be exported through S3 external tables.

[0043] The present invention also proposes a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described above.

[0044] Example 1

[0045] Please refer to Figure 2 Embodiment 1 of the present invention is a data import method applicable to the Greenplum database. For example... Figure 2 As shown, it includes the following steps:

[0046] S1: Obtain the data to be entered into the database according to the preset cycle.

[0047] Specifically, for streaming data (data packets reported in real time by networked offline devices, typically in string format, and usually reported intermittently; typically imported into a time-partitioned table), the data is first published to Kafka (a distributed messaging system). Then, consumers retrieve the streaming data in batches from the distributed messaging system according to a preset period and a preset data volume, obtaining the data to be stored. By publishing to Kafka, traffic smoothing can be achieved, and Kafka can improve message processing capabilities.

[0048] For file data (which is generally data reported by offline devices, uploaded infrequently, and with a pre-agreed file format; usually imported into a regular table and a time-partitioned table), the file data is downloaded according to a preset period and used as data to be entered into the database.

[0049] S2: According to the preset storage format, the data to be stored is stored as a temporary file, and the temporary file is uploaded to the distributed object storage system.

[0050] For example, each row represents one piece of data, and data from different fields are separated by delimiters (such as commas). The content of each field cannot contain delimiters.

[0051] Furthermore, for file data, the maximum data size of a single temporary file can be controlled so that the number of temporary files is greater than or equal to the number of compute nodes in the Greenplum database, thus making full use of all compute nodes.

[0052] Furthermore, after uploading the temporary files to OSS (Object Storage Service), the local temporary files are deleted.

[0053] S3: Based on the fields of the target table in the database, create an external table S3, and set the storage path of the external table S3 to the storage path of the distributed object storage system.

[0054] Furthermore, the field order and type in the S3 external table must be consistent with those in the temporary file.

[0055] By setting the path of the S3 external table to the path of OSS, temporary files in OSS can be accessed through the S3 external table. This allows data in the temporary files to be read via the S3 protocol and imported into the target table or temporary table.

[0056] S4: Determine whether the target table contains an index. If yes, proceed to step S6; otherwise, proceed to step S5.

[0057] S5: Import the data from the temporary file into the target table through the external table of S3.

[0058] Specifically, data import can be achieved through "insert into target table select * from S3 external table".

[0059] Furthermore, after the data is imported, the S3 external table is deleted.

[0060] Furthermore, before importing data, the data in the temporary file can be compressed. In addition, S3 external tables can be designed as columnar storage or compressed storage, which can improve data import efficiency, reduce database I / O, and save storage space.

[0061] S6: Create a temporary table in the database, and import the data from the temporary file into the temporary table through the external table in S3.

[0062] Specifically, if the target table is a regular table, a temporary table is created based on the target table's structure. No index is created in the temporary table before importing data into it, and then the data from the temporary file is imported into the temporary table.

[0063] S7: Create the same index in the temporary table as the index in the target table; that is, after importing data, create the same index structure in the temporary table as in the target table.

[0064] By importing the data first and then creating the index, the data import speed can be further improved.

[0065] S8: Delete the target table and rename the temporary table to the name of the target table.

[0066] The above steps enable batch data import. Similarly, data exported to files is also exported through an S3 external table. Specifically, when exporting data, an S3 writable external table is created based on the fields of the table containing the data to be exported in the database, and the storage path of the S3 external table is set to the storage path of the distributed object storage system. Then, the data to be exported is inserted into the S3 writable external table using the command "insert external table select columns from data source table".

[0067] This embodiment can effectively utilize computing nodes to load data in parallel, resulting in fast data import speed. Furthermore, the data does not pass through the master node during import, which not only reduces the burden on the master node but also ensures that data import is not limited by the bottleneck of the master node.

[0068] Example 2

[0069] This embodiment is a computer-readable storage medium corresponding to the above embodiments, which stores a computer program. When the program is executed by a processor, it implements the various steps of a data entry method as described in the above embodiments and achieves the same technical effect, which will not be repeated here.

[0070] In summary, the data import method and computer-readable storage medium provided by this invention can import batch data through an S3 external table, effectively utilize computing nodes to load data in parallel, improve the import speed of batch data, and reduce the load on the master node by eliminating the data passing through the master node, thus ensuring that the data import is not limited by the bottleneck of the master node.

[0071] The above description is merely an embodiment of the present invention and does not limit the patent scope of the present invention. Any equivalent modifications made based on the content of the present invention specification and drawings, or direct or indirect applications in related technical fields, are similarly included within the patent protection scope of the present invention.

Claims

1. A data warehousing method characterized by, The method comprises the following steps: acquiring data to be stored in a database according to a preset period; storing the data to be stored in a temporary file according to a preset storage format, and uploading the temporary file to a distributed object storage system; creating an S3 external table according to fields of a target table in the database, and setting a storage path of the S3 external table as a storage path of the distributed object storage system; importing data in the temporary file into the target table through the S3 external table. Before the step of importing data in the temporary file into the target table through the S3 external table, the method further comprises the following steps: judging whether the target table contains an index; if not, performing the step of importing data in the temporary file into the target table through the S3 external table; if yes, creating a temporary table in the database, and importing data in the temporary file into the temporary table through the S3 external table; creating the same index in the temporary table according to the index in the target table; deleting the target table, and renaming the temporary table as a table name of the target table.

2. The data warehousing method of claim 1, wherein, Before the step of importing data in the temporary file into the target table through the S3 external table, the method further comprises the following step: compressing data in the temporary file.

3. The data warehousing method of claim 1, wherein, The step of acquiring data to be stored in a database according to a preset period specifically comprises the following steps: if the data is streaming data, publishing the streaming data to a distributed message system, and acquiring the streaming data from the distributed message system in batches according to a preset period to obtain the data to be stored; if the data is file data, downloading the file data according to a preset period as the data to be stored.

4. The data warehousing method of claim 1, wherein, After the step of uploading the temporary file to the distributed object storage system, the method further comprises the following step: deleting the temporary file locally.

5. The data warehousing method of claim 1, wherein, After the step of importing data in the temporary file into the target table through the S3 external table, the method further comprises the following step: deleting the S3 external table.

6. The data warehousing method of claim 1, wherein, The method further comprises the following steps: when data is to be exported, creating an S3 writable external table according to fields of a table in which the data to be exported is located in the database, and setting a storage path of the S3 writable external table as a storage path of the distributed object storage system; inserting the data to be exported into the S3 writable external table.

7. A computer-readable storage medium having stored thereon a computer program, characterized in that, The program is executed by a processor to implement the method according to any one of claims 1-6.

Images