Data cold and hot switching method for Hive data warehouse

Abstract

The invention discloses a data cold and hot switching method for a Hive data warehouse. The method comprises the steps that cold data of a data table to be backed up and partition paths of the cold data on an HDFS are determined based on a backup period; downloading a partition path of the cold data on the HDFS and compressing the partition path into a zip file; the zip file is uploaded to an oss file server; deleting the partition path of the cold data in the HDFS; querying partition information of each partition of the data table to be backed up in a backup period, storing the partition information of one partition in a json file, and uploading the json file to an oss file server; downloading the zip file and a json file corresponding to the data table to be recovered to a local server; and the zip file is decompressed, and the downloaded json file is read and analyzed. According to the method, data cold and hot switching is constructed by utilizing the HDFS and Hive underlying principles, and the method has the characteristics of high speed, high backup flexibility, high backup safety and low cost.

Description

Technical Field

[0001] The present invention relates to the technical field of data warehouses, and in particular to a method for switching data from hot to cold in a Hive data warehouse. Background Art

[0002] In the field of big data warehouse construction, the data warehouse must reflect the historical status of data, which places great demands on Hive's storage capacity. As time goes by, the partitioned data in the data warehouse increases, resulting in increased storage pressure on HDFS, increased file management pressure on NameNode, and increased management pressure on Hive MetaStore. In order to ensure the stability of the data warehouse, the cluster data needs to be migrated to external storage media and this part of the data is positioned as cold data. However, in actual scenarios, for example, if the data of last year is moved to external media, and the business party temporarily wants to view the data of a certain day a year ago, this poses a challenge to the rapid recovery of data. Summary of the invention

[0003] The present invention provides a method for hot-cold switching of Hive data warehouse data to at least solve the above technical problems.

[0004] To achieve the above purpose, the technical solution adopted by the present invention is as follows: A method for switching between hot and cold data in a Hive data warehouse comprises the following steps: S1. Determine the cold data of the data table to be backed up and the partition path of the cold data on HDFS based on the backup cycle; S2, download the partition path of the cold data on HDFS and compress it into a zip file; S3, upload the zip file to the OSS file server; S4, delete the partition path of cold data in HDFS; S5. Query the partition information of each partition of the data table to be backed up within the backup cycle, save the partition information of one partition in a json file and upload it to the oss file server to complete the data backup; S6. Download the zip file and the json file corresponding to the data table to be restored to the local server; S7. Decompress the zip file, read and parse the downloaded json file to complete data recovery.

[0005] Furthermore, the S1 includes: S11, subtracting the preset backup period from the current date to obtain the backup time; S12, inputting the table name of the data table to be backed up to obtain the corresponding Hive table to be backed up in HDFS; S13, determining the data of the Hive table to be backed up at the backup time as cold data, and obtaining the partition path of the cold data on HDFS.

[0006] Furthermore, the S5 includes: S51. Find out the TBL_ID in the TBLS metadata table of the Hive Metastore database according to the table name of the data table to be backed up; S52. Query the partition information of the data table to be backed up in the PARTITIONS data table through TBL_ID; S53. Filter out the SD_ID of each partition within the backup time in the partition information; S54. Query the partition information of the corresponding partition on HDFS in the SDS metadata table according to SD_ID; S55. Save each partition information in a json file and upload it to the oss file server.

[0007] Furthermore, the key value in the json file is the same as the column name of the SDS metadata table, and the value is the partition information.

[0008] Furthermore, in S55, the json file and the zip file are uploaded to the same path of the oss file server.

[0009] Furthermore, the S7 includes: S71, creating a partition path of the data table to be recovered under the HDFS storage path; S72, decompressing the zip file to obtain a decompressed file, and uploading the decompressed file to the partition path of the data table to be recovered; S73, reading and parsing the downloaded json file, and constructing a data insert statement; S74, executing the data insert statement in the mysql database to complete data recovery.

[0010] Furthermore, in S72, the decompressed file is uploaded to the partition path of the data table to be restored through the put command of HDFS.

[0011] Furthermore, in S2, an HDFS command is used to download the partition path of the cold data on the HDFS to a directory in the local file system.

[0012] Compared with the prior art, the present invention has the following beneficial effects: The present invention uses the underlying principles of HDFS and Hive to build data hot and cold switching, that is, backing up cold data to the OSS file server to release storage space on HDFS; and restoring the backup data to hot data in HDFS to achieve data hot and cold switching. Compared with existing data backup methods, the present invention has the characteristics of fast speed, high backup flexibility, high backup security and low cost. BRIEF DESCRIPTION OF THE DRAWINGS

[0013] Figure 1 Flow chart of the method of the present invention DETAILED DESCRIPTION

[0014] Glossary HDFS: distributed file system; Hive:distributed data engine based on HDFS; NameNode: A core component in HDFS that manages the file system metadata and coordinates client access to files; Hive MetaStore: background service for Hive metadata management; OSS file server: file server of Object Storage Service; In order to make the purpose, technical solution and advantages of the present invention more clearly understood, the present invention is further described in detail below in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present invention.

[0015] like Figure 1 As shown, the present invention provides a method for hot-cold switching of Hive data warehouse data, comprising the following steps: S1. Determine the cold data of the data table to be backed up and the partition path of the cold data on HDFS based on the backup cycle; S2, download the partition path of the cold data on HDFS and compress it into a zip file; S3, upload the zip file to the OSS file server; S4, delete the partition path of cold data in HDFS; S5. Query the partition information of each partition of the data table to be backed up within the backup cycle, save the partition information of one partition in a json file and upload it to the oss file server to complete the data backup; S6. Download the zip file and the json file corresponding to the data table to be restored to the local server; S7. Decompress the zip file, read and parse the downloaded json file to complete data recovery.

[0016] The present invention uses the underlying principles of HDFS and Hive to build data hot and cold switching, that is, backing up cold data to the OSS file server to release storage space on HDFS; and restoring the backup data to hot data in HDFS to achieve data hot and cold switching. Compared with existing data backup methods, the present invention has the characteristics of fast speed, high backup flexibility, high backup security and low cost.

[0017] S1-S5 of the present invention is a backup of data.

[0018] The S1 of the present invention includes: S11, subtracting the preset backup cycle from the current date to obtain the backup time; S12, inputting the table name of the data table to be backed up to obtain the corresponding Hive table to be backed up in HDFS; S13, determining the data of the Hive table to be backed up at the backup time as cold data, and obtaining the partition path of the cold data on HDFS. For example, the current date is 20230719, the backup cycle is 180 days, and the backup time is 20230121 and its previous dates, that is, the data of the Hive table to be backed up at 20230121 and its previous dates are all cold data, and the partition path of these cold data on HDFS is obtained.

[0019] S2 described in the present invention is to download the partition path of cold data on HDFS and compress it into a zip file. Preferably, the HDFS command is used to download the partition path of cold data on HDFS to a directory in the local file system. The directory is the target path. If the local file system does not have this directory, it is created during downloading.

[0020] The S3 described in the present invention is to upload the zip file to the OSS file server, back up the data to the OSS, and then delete the partition path of the cold data in the HDFS through S4, and delete the corresponding relationship between the Hive table to be backed up and the partition path in the Hive Metastore database, thereby releasing the storage space on the HDFS.

[0021] The S5 of the present invention is to query the partition information of each partition of the data table to be backed up within the backup cycle, save the partition information of a partition in a json file and upload it to the oss file server to complete the data backup. Specifically including: S51, according to the table name of the data table to be backed up, find out the TBL_ID in the TBLS metadata table of the Hive Metastore database; S52, query the partition information of the data table to be backed up in the PARTITIONS data table through TBL_ID; S53, filter out the SD_ID of each partition within the backup time in the partition information; S54, query the partition information of the corresponding partition on HDFS in the SDS metadata table according to SD_ID; S55, save each partition information in a json file and upload it to the oss file server, preferably, upload the json file and the zip file to the same path of the oss file server. For example, the table name of the data table to be backed up is ods_jc2_bd_bid_basic_info. Through the query statement: select TBL_ID,TBL_NAMEfrom tbls where TBL_NAME= 'ods_ jc2_ bd_ bid_ basic _info', the TBL_ID is 1474. Then, through TBL_ID=1474, the partition information of the data table to be backed up is queried in the PARTITIONS data table, including PART_ID, CREATE_TIME, LAST_ACCESS_TIME, PART_NAME, SD_ID, TBL_ID, and WRITE_ID. TBL_ID=1474 and PART_NAME are checked and filtered before 20230121. Finally, a partition corresponding to PART_NAME is df_d=20230120, and its corresponding SD_ID is 236260. Then, according to SD_ID=236260, the query statement is used: , query the partition information of the corresponding partition on HDFS, including SD_ID, CD_ID, input_format, IS_COMPRESSED, IS_STOREDASSUBDIRECTORIES, LOCATION, OUTPUT_FORMAT, SERDE_ID and NUM_BUCKETS; finally save this partition information in a json file and upload it to the oss file server. The key value in the json file is the same as the column name of the SDS metadata table, and the value is the partition information.

[0022] S6-S7 of the present invention is data recovery.

[0023] S6 of the present invention is to download the zip file and the json file corresponding to the data table to be restored to the local server.

[0024] S7 of the present invention is to decompress the zip file, read and parse the downloaded json file to complete data recovery. Specifically including: S71, create a partition path of the data table to be recovered under the HDFS storage path; S72, decompress the zip file to obtain a decompressed file, and upload the decompressed file to the partition path of the data table to be recovered; S73, read and parse the downloaded json file, and construct a data insert statement; S74, execute the data insert statement in the mysql database to complete data recovery. In one embodiment, the data table to be recovered is created as jc_dwd_tender_baseic_info20230120, and the corresponding partition path / warehouse / tablespace / managed / jc_dwd_tender_baseic_info / df_d=20230120 is created under the HDFS storage path; then the zip file is decompressed to obtain a decompressed file, and the decompressed file is uploaded to the partition path of the data table to be recovered through the HDFS put command; read and parse the downloaded json file, and construct a data insert statement based on the parsed content of the corresponding json file: Insert into sds(SD_ID,CD_ID,input_format,IS_COMPRESSED,IS_STOREDASSUBDIRECTORIES,LOCATION,OUTPUT_FORMAT,SERDE_ID,NUM_BUCKETS) values(236260,15594,'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat','','','hdfs: / / bdprodcluster / warehouse / tablespace / e xternal / hive / jc2.db / ods_jc2_bd_bid_basic_info / df_d=20230120',org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat,236260,-1);

[0025] Finally, execute the data insert statement in the MySQL database to complete data recovery.

[0026] The present invention uses python to implement the above process, and other programming languages ​​​​can also be used, such as java, go, etc.

[0027] Finally, it should be noted that the above embodiments are only preferred embodiments of the present invention to illustrate the technical solutions of the present invention, rather than limiting them, and certainly not limiting the patent scope of the present invention. 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 recorded in the aforementioned embodiments, or replace some or all of the technical features therein by equivalents. These modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present invention. In other words, any changes or modifications made to the main design concept and spirit of the present invention that have no substantive significance, and the technical problems they solve are still consistent with the present invention, should be included in the protection scope of the present invention. In addition, the direct or indirect application of the technical solutions of the present invention in other related technical fields is also included in the patent protection scope of the present invention.

Claims

1. A method for switching between hot and cold data in a Hive data warehouse, characterized in that: The following steps are involved: S1. Determine the cold data of the data table to be backed up and the partition path of the cold data on HDFS based on the backup cycle; S2, download the partition path of the cold data on HDFS and compress it into a zip file; S3, upload the zip file to the OSS file server; S4, delete the partition path of cold data in HDFS; S5. Query the partition information of each partition of the data table to be backed up within the backup cycle, save the partition information of one partition in a json file and upload it to the oss file server to complete the data backup; S6. Download the zip file and the json file corresponding to the data table to be restored to the local server; S7. Decompress the zip file, read and parse the downloaded json file to complete data recovery.

2. A method for hot-cold switching of Hive data warehouse data according to claim 1, characterized in that: The S1 includes: S11, subtracting the preset backup period from the current date to obtain the backup time; S12, inputting the table name of the data table to be backed up to obtain the corresponding Hive table to be backed up in HDFS; S13, determining the data of the Hive table to be backed up at the backup time as cold data, and obtaining the partition path of the cold data on HDFS.

3. The method for hot-cold switching of Hive data warehouse data according to claim 2, characterized in that: The S5 includes: S51. Find TBL_ID in the TBLS metadata table of the Hive Metastore database according to the table name of the data table to be backed up; S52. Query the partition information of the data table to be backed up in the PARTITIONS data table through TBL_ID; S53. Filter out the SD_ID of each partition within the backup time in the partition information; S54. Query the partition information of the corresponding partition on HDFS in the SDS metadata table according to SD_ID; S55. Save each partition information in a json file and upload it to the oss file server.

4. A method for hot-cold switching of Hive data warehouse data according to claim 3, characterized in that: The key value in the json file is the same as the column name of the SDS metadata table, and the value is the partition information.

5. A method for hot-cold switching of Hive data warehouse data according to claim 3, characterized in that: In S55, the json file and the zip file are uploaded to the same path of the oss file server.

6. A method for hot-cold switching of Hive data warehouse data according to claim 1, characterized in that: The S7 includes: S71, creating a partition path of the data table to be recovered under the HDFS storage path; S72, decompressing the zip file to obtain a decompressed file, and uploading the decompressed file to the partition path of the data table to be recovered; S73, reading and parsing the downloaded json file, and constructing a data insertion statement; S74, executing the data insertion statement in the mysql database to complete data recovery.

7. A method for hot-cold switching of Hive data warehouse data according to claim 6, characterized in that: In S72, the decompressed file is uploaded to the partition path of the data table to be restored through the put command of HDFS.

8. A method for hot-cold switching of Hive data warehouse data according to claim 1, characterized in that: In S2, HDFS commands are used to download the partition path of the cold data on HDFS to a directory in the local file system.

Images