A method to accelerate data migration from data warehouse to doris

Abstract

The present invention discloses a method for accelerating the migration of data warehouse data to Doris, comprising creating a Hive table corresponding to a data table to be imported in Doris; obtaining a file path of the data table to be imported and calculating the number of files of dynamic partitions, re-sharding the files in the file path based on the number of files of dynamic partitions, and writing the re-sharded files to the tmp directory of HDFS; obtaining a field list of the Hive table; reading a cluster configuration file of Hadoop; obtaining a file list of the re-sharded files, listing every 10 files as a group, matching 10 files in each group, constructing an HDFS asynchronous import SQL statement based on the table fields in the field list of the Hive table and the cluster configuration file of Hadoop, and executing an import task to import each group of files into Doris through the HDFS asynchronous import SQL statement. The present invention reduces the time of data import and relocation and improves the migration rate.

Description

Technical Field

[0001] The present invention relates to the technical field of data migration, and in particular to a method for accelerating the migration of data from a data warehouse to Doris. Background Art

[0002] In the field of big data analysis, traditional data warehouses are based on Hive of the Hadoop system to process business wide tables. The upper layer of users of these processed tables is the business system, user report system, indicator system, etc. However, since Hive is based on the ecosystem of the Hadoop system, the MR engine used in the underlying calculation cannot meet the low-latency data query of the business. In order to meet the upper-level business, it is often necessary to introduce the data of the data warehouse into an acceleration layer, so that the data processed by Hive can be imported into the accelerated OLap engine. At present, the Doris engine in the field of big data has become a rising star, but how to import Hive data into Doris efficiently and stably has become a huge problem faced by data development engineers. Although Doris officially provides various methods for importing data into Doris, the stability and reliability in practice are very unsatisfactory. Summary of the invention

[0003] The purpose of the present invention is to provide a method for accelerating the migration of data warehouse data to Doris, so as to at least solve some of the above-mentioned technical problems.

[0004] To achieve the above purpose, the technical solution adopted by the present invention is as follows:

[0005] A method for accelerating the migration of data warehouse data to Doris includes the following steps:

[0006] S1. Create a hive table corresponding to the data table to be imported in doris;

[0007] S2. Obtain the file path of the data table to be imported and calculate the number of files in the dynamic partition, re-shard the files in the file path based on the number of files in the dynamic partition, and write the re-sharded files to the tmp directory of hdfs;

[0008] S3. Get the field list of the hive table;

[0009] S4, read the Hadoop cluster configuration file;

[0010] S5. Get the file list of the re-sharded files, list every 10 files as a group, match the 10 files in each group, construct the HDFS asynchronous import SQL statement based on the table fields in the field list of the Hive table and the Hadoop cluster configuration file, and execute the import task through the HDFS asynchronous import SQL statement to import each group of files into Doris.

[0011] Furthermore, in S1, the table fields of the hive table are consistent with the table fields of the data table to be imported.

[0012] Furthermore, in S2, the number of files in the dynamic partition is calculated as follows: the spark engine is used to read all files under the file path of the data table to be imported and obtain the number of files; the number of files is divided by a preset merge base to obtain a quotient a and a remainder b, if the remainder b is equal to 0, the number of files in the dynamic partition is a, otherwise the number of files in the dynamic partition is a+1; the number of files in the dynamic partition is recorded as c and passed to the coalesce operator of the spark engine to re-shard the files in the file path.

[0013] Furthermore, the merging cardinality is an integer multiple of 10.

[0014] Furthermore, in the S3, a field list of the hive table is obtained by linking to the metadata service of hive.

[0015] Furthermore, in S4, the Hadoop cluster configuration file is a hdfs-site.xml file.

[0016] Furthermore, in S5, a regular expression is used to match 10 files in each group.

[0017] Furthermore, each group of files constructs an SQL statement for HDFS asynchronous import, one SQL statement corresponds to one import task, and each group of files is imported into Doris through one import task.

[0018] Furthermore, in S5, constructing the SQL statement for HDFS asynchronous import includes: replacing the file path parameter in the SQL statement template with the tmp directory, replacing the label name parameter with the globally unique ID, replacing the column name parameter with the table field in the field list of the hive table, replacing the doris name parameter with the table name of the hive table, and writing the field of the Hadoop cluster configuration file into the SQL statement template; the generation rule of the globally unique ID is: the table name of the doris table plus the timestamp of the current system plus _load, and the table fields in the field list of the hive table are separated by commas.

[0019] Furthermore, S5 also includes: reading the progress of the import task every 30 seconds, and sending an alarm message if the import task fails.

[0020] Compared with the prior art, the present invention has the following beneficial effects:

[0021] The present invention can shorten the original import time of 3 hours to 5 minutes, reduce the time of data import and migration, and improve the migration rate. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] Figure 1 The figure is a flow chart of the method of the present invention. DETAILED DESCRIPTION

[0023] Explanation of terms

[0024] Doris is a distributed SQL database based on massively parallel processing technology.

[0025] Hadoop is a distributed system infrastructure developed by the Apache Foundation.

[0026] ‌Hive is a data warehouse tool based on Hadoop‌, which can map structured data files into a database table and provide SQL-like query functions.

[0027] The MR engine is a data processing engine commonly used in the Hadoop ecosystem.

[0028] ‌OLAP engine is an Online Analytical Processing (OLAP) engine that is used to process large amounts of data and provide fast query and analysis results.

[0029] The tmp directory is a temporary directory used to store temporary files.

[0030] ‌Spark engine is a fast, general and scalable big data analysis engine.

[0031] The coalesce operator is a common operator used in data processing. It can be used to merge multiple data streams or merge multiple small files into one large file.

[0032] ‌The hdfs-site.xml file is a core configuration file in Hadoop, which is used to define the configuration parameters of hdfs (Hadoop Distributed File System).

[0033] Tez is an open source computing framework that supports DAG jobs. It can convert multiple dependent jobs into one job, thereby greatly improving the performance of DAG jobs.

[0034] 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.

[0035] like Figure 1 As shown, the present invention provides a method for accelerating the migration of data warehouse data to Doris, comprising the following steps:

[0036] S1. Create a hive table corresponding to the data table to be imported in doris;

[0037] S2. Obtain the file path of the data table to be imported and calculate the number of files in the dynamic partition, re-shard the files in the file path based on the number of files in the dynamic partition, and write the re-sharded files to the tmp directory of hdfs;

[0038] S3. Get the field list of the hive table;

[0039] S4, read the Hadoop cluster configuration file;

[0040] S5. Get the file list of the re-sharded files, list every 10 files as a group, match the 10 files in each group, construct the HDFS asynchronous import SQL statement based on the table fields in the field list of the Hive table and the Hadoop cluster configuration file, and execute the import task through the HDFS asynchronous import SQL statement to import each group of files into Doris.

[0041] The present invention can shorten the original import time of 3 hours to 5 minutes, reduce the time of data import and migration, and improve the migration rate.

[0042] In one embodiment, in S1, the table fields of the hive table are consistent with the table fields of the data table to be imported. Since the metadata in the hive table is read from MySQL, that is, the table field information of the hive table is stored in MySQL, it is necessary to obtain the table fields of the data table to be imported, and then construct the table creation statement of Doris to create the corresponding hive table in Doris. For this purpose, the table fields of the two tables need to be consistent.

[0043] The data processed by the hive table is stored in the hdfs distributed file system. The data storage path of each table is unique. Since hive uses Tez to run, the number of outputs is uncertain and changes dynamically. Therefore, the files under the file path need to be re-sharded.

[0044] In one embodiment, in S2, the number of files in the dynamic partition is calculated as follows: the spark engine is used to read all files under the file path of the data table to be imported and obtain the number of files; the number of files is divided by a preset merging base to obtain a quotient a and a remainder b, if the remainder b is equal to 0, the number of files in the dynamic partition is a, otherwise the number of files in the dynamic partition is a+1; the number of files in the dynamic partition is recorded as c and passed to the coalesce operator of the spark engine to re-shard the files in the file path. Preferably, the merging base is an integer multiple of 10.

[0045] In one embodiment, a field list of a hive table is obtained by linking to a metadata service of hive, and the fields in the field list of the hive table are used to subsequently construct an SQL statement for hdfs asynchronous import (hdfs-broker-_load).

[0046] In one embodiment, the cluster configuration file of Hadoop is a hdfs-site.xml file, and the hdfs-site.xml file is used to subsequently construct a SQL statement for HDFS asynchronous import.

[0047] In one embodiment, in S5, a regular expression is used to match 10 files in each group. Since the file list of the re-sharded files is in the format of part-00001.parquet, part-00002.parquet, part-00003.parquet......, and parquet is the original file data stored in the hdfs in the hive table, the first 10 files can be obtained by using the regular expression You can get the match. Similarly, if you get the 11th to 20th files through the regular expression Get the match, and so on until all files are matched.

[0048] In one embodiment, each group of files constructs an SQL statement for HDFS asynchronous import, one SQL statement corresponds to an import task, and each group of files is imported into Doris through an import task. If there are 100 re-sharded files, 10 files are grouped together, 10 SQL statements for HDFS asynchronous import are constructed, and 10 import tasks are executed. S5 is a loop operation, that is, a group of 10 files is matched, an SQL statement for HDFS asynchronous import is constructed, and then the Doris cluster is linked to execute the import task until all files are imported into Doris.

[0049] In one embodiment, constructing the SQL statement for HDFS asynchronous import includes: replacing the file path parameter {input_path} in the SQL statement template with the tmp directory, replacing the label name parameter {lable_name} with the global unique ID, replacing the column name parameter {col_names} with the table field in the field list of the hive table, replacing the doris name parameter {doris_table} with the table name of the hive table, and writing the fields of the Hadoop cluster configuration file into the SQL statement template; the generation rule of the global unique ID is: the table name of the doris table plus the timestamp of the current system plus _load, such as material_biz_price_1709939205801_load, and the table fields in the field list of the hive table are separated by commas, such as col1, col2, col3, col4, col5. Furthermore, finally, the fields such as fs.defaultFS and dfs.nameservices in the hdfs-site.xml file are written into the SQL statement template to complete the construction of the SQL statement.

[0050] 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 accelerating data migration from a data warehouse to Doris, characterized in that: The following steps are involved: S1. Create a hive table corresponding to the data table to be imported in doris; S2. Obtain the file path of the data table to be imported and calculate the number of files in the dynamic partition, re-shard the files in the file path based on the number of files in the dynamic partition, and write the re-sharded files to the tmp directory of hdfs; S3. Get the field list of the hive table; S4, read the Hadoop cluster configuration file; S5. Get the file list of the re-sharded files, list every 10 files as a group, match the 10 files in each group, construct the HDFS asynchronous import SQL statement based on the table fields in the field list of the Hive table and the Hadoop cluster configuration file, and execute the import task through the HDFS asynchronous import SQL statement to import each group of files into Doris.

2. The method for accelerating data migration from a data warehouse to Doris according to claim 1, characterized in that: In S1, the table fields of the hive table are consistent with the table fields of the data table to be imported.

3. The method for accelerating data migration from a data warehouse to Doris according to claim 1, characterized in that: In S2, the number of files in the dynamic partition is calculated as follows: the spark engine is used to read all files under the file path of the data table to be imported and obtain the number of files; the number of files is divided by a preset merging base to obtain a quotient a and a remainder b. If the remainder b is equal to 0, the number of files in the dynamic partition is a, otherwise the number of files in the dynamic partition is a+1; the number of files in the dynamic partition is recorded as c and passed to the coalesce operator of the spark engine to re-shard the files in the file path.

4. The method for accelerating data migration from a data warehouse to Doris according to claim 3 is characterized in that: The merging cardinality is an integer multiple of 10.

5. The method for accelerating data migration from a data warehouse to Doris according to claim 1, characterized in that: In the S3, the field list of the hive table is obtained by linking the metadata service of hive.

6. The method for accelerating data migration from a data warehouse to Doris according to claim 1, characterized in that: In the S4, the Hadoop cluster configuration file is the hdfs-site.xml file.

7. The method for accelerating data migration from a data warehouse to Doris according to claim 1, characterized in that: In S5, a regular expression is used to match 10 files in each group.

8. The method for accelerating data migration from a data warehouse to Doris according to claim 1, characterized in that: Each group of files constructs an SQL statement for HDFS asynchronous import. One SQL statement corresponds to one import task, and each group of files is imported into Doris through one import task.

9. The method for accelerating data migration from a data warehouse to Doris according to claim 1, characterized in that: In S5, constructing the SQL statement for HDFS asynchronous import includes: replacing the file path parameter in the SQL statement template with the tmp directory, replacing the label name parameter with the globally unique ID, replacing the column name parameter with the table field in the field list of the hive table, replacing the doris name parameter with the table name of the hive table, and writing the field of the Hadoop cluster configuration file into the SQL statement template; the generation rule of the globally unique ID is: the table name of the doris table plus the timestamp of the current system plus _load, and the table fields in the field list of the hive table are separated by commas.

10. The method for accelerating data migration from a data warehouse to Doris according to claim 1, characterized in that: The S5 also includes: reading the progress of the import task every 30 seconds, and sending an alarm message if the import task fails.

Images