Example Typical Scenario: Migrating Data from Hive to DLI

This section describes how to use CDM's data synchronization to migrate data from MRS Hive to DLI. All other MRS Hadoop component data can be synchronized bidirectionally with DLI using CDM.

Prerequisites

You have created a DLI SQL queue.

Set Type to For SQL when buying a queue.
You have created an MRS security cluster that contains the Hive component.
- In this example, the MRS cluster and component versions are as follows:
  - Cluster version: MRS 3.1.0
  - Hive version: 3.1.0
  - Hadoop version: 3.1.1
- In this example, Kerberos authentication is enabled when the MRS cluster is created.
You have created a CDM cluster. For how to create a CDM cluster, see Creating a CDM Cluster.
- To connect the cluster to an on-premises database as the destination data source, you can use either Internet or Direct Connect. If the Internet is used, make sure that an EIP has been bound to the CDM cluster, the security group of CDM allows outbound traffic from the host where the on-premises data source is located, the host where the data source is located can access the Internet, and the connection port has been enabled in the firewall rules.
- To successfully connect to cloud services like MRS and GaussDB (DWS) as data sources, the following requirements must be met:
  i. If the CDM cluster and the cloud service are in different regions, they must be connected through either the Internet or Direct Connect. If the Internet is used, make sure that an EIP has been bound to the CDM cluster, the host where the data source is located can access the Internet, and the port has been enabled in the firewall rules.
  
  ii. If the CDM cluster and the cloud service are in the same region, instances in the same VPC, subnet, and security group can communicate with each other by default. If the CDM cluster and the cloud service are in the same VPC but in different subnets or security groups, you must configure routing and security group rules.
  
  For how to configure routing rules, see Configure routes. For how to configure security group rules, see Security Group Configuration Examples.
  
  iii. The cloud service instance and the CDM cluster belong to the same enterprise project. If they do not, change the enterprise project of the workspace.
In this example, the VPC, subnet, and security group of the CDM cluster match those of the MRS cluster.

Step 1: Prepare Data

Create a Hive table in the MRS cluster and insert data into the table.
1. Log in to MRS Manager by referring to Accessing FusionInsight Manager.
2. Click System and choose Permission > Role. On the displayed page, set the following parameters:
  - Role Name: Enter a role name, for example, hivetestrole.
  - Configure Resource Permission: Select the MRS cluster name and then Hive. Select Hive Admin Privilege.
    Figure 1 Creating a Hive role
    
    For how to create a role, see Creating a Role.
3. Click System and choose Permission > User. On the displayed page, set the following parameters:
  1. Username: Enter a username. In this example, enter hivetestusr.
  2. User Type: Select Human-Machine.
  3. Password and Confirm Password: Enter the current user's password and confirm it by entering it again.
  4. User Group and Primary Group: Select supergroup.
  5. Role: Select the role created in 2 and the Manager_viewer role.
    Figure 2 Creating a Hive user
4. Download and install the Hive client by referring to Installing an MRS Client. For example, the current Hive client is installed in the /opt/hiveclient directory of the active MRS node.
5. Go to the client installation directory as user root.
  Example: cd /opt/hiveclient
6. Configure environment variables.
  source bigdata_env
7. Authenticate the user created in 3 as Kerberos authentication has been enabled for the cluster:
  kinit Username in 3
  
  Example: kinit hivetestusr
8. Connect to Hive.
  beeline
9. Create a table and insert data into it.
  Create a table.
```
create table user_info(id string,name string,gender string,age int,addr string);
```
  Insert data into the table.
```
insert into table user_info(id,name,gender,age,addr) values("12005000201","A","Male",19,"City A");
insert into table user_info(id,name,gender,age,addr) values("12005000202","B","Male",20,"City B");
insert into table user_info(id,name,gender,age,addr) values("12005000202","B","Male",20,"City B");
```
  In the preceding example, data is migrated by creating a table and inserting data into the table. To migrate existing Hive databases and table data, obtain information about them.
  - Run the following command on the Hive client to obtain database information:
    show databases
  - Switch to the Hive database to migrate.
    use Hive database name
  - Displays information about all tables in the current database.
    show tables
  - Query the Hive table creation statement.
    show create table Hive table name
    
    The table creation statement obtained from the query needs to be modified to match DLI's syntax before running it on DLI.
Create a database and table on DLI.
1. Log in to the DLI management console. In the navigation pane on the left, choose SQL Editor. On the displayed page, set Engine to Spark and Queues to the created SQL queue.
  Create a database, for example, testdb. For the syntax to create a DLI database, see Creating a Database.
```
create database testdb;
```
2. Create a table in the database.
  
  You need to modify the table creation statement obtained by running show create table hive_table_name to comply with DLI's table creation syntax. For the table creation syntax, see Creating a DLI Table Using the DataSource Syntax.
```
create table user_info(id string,name string,gender string,age int,addr string);
```

Step 2: Migrate Data

Create a CDM connection.

Create a connection to link CDM to MRS Hive.

Log in to the CDM console. In the navigation pane on the left, choose Cluster Management. On the displayed page, locate the created CDM cluster and click Job Management in the Operation column.
On the Job Management page, click the Links tab. On this tab page, click Create Link. On the displayed page, select MRS Hive and click Next.
Figure 3 Selecting the MRS Hive connector

Configure the connection as follows:

**Table 1** MRS Hive connection configurations
Parameter	Value
Name	Name of the MRS Hive data source, for example, source_hive.
Manager IP	Manager IP address, which is automatically filled in after you click Select next to the text box and select the MRS Hive cluster.
Authentication Method	Set it to KERBEROS if Kerberos authentication is enabled for the MRS cluster or to SIMPLE if the MRS cluster is a common cluster. In this example, set it to KERBEROS.
Hive Version	Set it to the Hive version you selected when creating the MRS cluster. Select HIVE_3_X if the current Hive version is 3.1.0.
Username	Name of the MRS Hive user created in 3.
Password	Password of the MRS Hive user.

Retain the default values for other parameters.

Figure 4 Configuring the connection to MRS Hive

Click Save.

Create a connection to link CDM to DLI.
1. Log in to the CDM console. In the navigation pane on the left, choose Cluster Management. On the displayed page, locate the created CDM cluster and click Job Management in the Operation column.
2. On the Job Management page, click the Links tab, and click Create Link. On the displayed page, select Data Lake Insight and click Next.
  Figure 5 Selecting the DLI connector
3. Set the connection parameters.
  Figure 6 Setting connection parameters
  
  Click Save.

Create a CDM migration job.

Log in to the CDM console. In the navigation pane on the left, choose Cluster Management. On the displayed page, locate the created CDM cluster and click Job Management in the Operation column.
On the Job Management page, click the Table/File Migration tab. On the displayed tab, click Create Job.

On the Create Job page, set job parameters.

Figure 7 Setting CDM job parameters

Job Name: Name of the data migration job, for example, hive_to_dli.

Set the parameters in the Source Job Configuration area as follows:

**Table 2** Source job parameters
Parameter	Value
Source Link Name	Select the name of the data source created in 1.a.
Database Name	Select the name of the MRS Hive database you want to migrate to DLI. For example, the default database.
Table Name	Select the name of the Hive table. In this example, a database created on DLI and the user_info table are selected.
readMode	In this example, set it to HDFS. The options are described as follows: There are two read modes available: HDFS and JDBC. HDFS is used by default. If you do not need to use the WHERE condition to filter data or add fields on the field mapping page, select HDFS. When using the HDFS mode, data reading performance is good, but it does not support filtering data using WHERE conditions or adding fields on the field mapping page. The JDBC mode allows you to use WHERE conditions to filter data or add fields on the field mapping page.

For details about parameter settings, see From Hive.

Set the parameters in the Destination Job Configuration area as follows:

**Table 3** Destination job parameters
Parameter	Value
Destination Link Name	Select the DLI data source connection created in 1.b.
Resource Queue	Select a created DLI SQL queue.
Database Name	Select a created DLI database. In this example, database testdb created in Create a database and table on DLI is selected.
Table Name	Select the name of a table in the database. In this example, table user_info created in Create a database and table on DLI is selected.
Clear data before import	Whether to clear data in the destination table before data import. In this example, set it to No. If set to Yes, data in the destination table will be cleared before the task is started.

For details about parameter settings, see To DLI.

Click Next. The Map Field page is displayed. CDM automatically matches the source and destination fields.
- You can drag any unmatched fields to match them.
- If the type is automatically created at the migration destination, you need to configure the type and name of each field.
- CDM allows for field conversion during migration. For details, see Field Conversion.
Figure 8 Field mapping
Click Next and set task parameters. Typically, retain the default values for all parameters.
In this step, you can configure the following optional features:
- Retry Upon Failure: If the job fails to be executed, you can determine whether to automatically retry. Retain the default value Never.
- Group: Select the group to which the job belongs. The default group is DEFAULT. On the Job Management page, jobs can be displayed, started, or exported by group.
- Scheduled Execution: For how to configure scheduled execution, see Scheduling Job Execution. Retain the default value No.
- Concurrent Extractors: Enter the number of extractors to be concurrently executed. Retain the default value 1.
- Write Dirty Data: Set this parameter if data that fails to be processed or filtered out during job execution needs to be written to OBS. Before writing dirty data, create an OBS link. You can view the data on OBS later. Retain the default value No, meaning dirty data is not recorded.
Click Save and Run. On the Job Management page, you can view the job execution progress and result.
Figure 9 Job progress and execution result

Step 3: Query Results

Once the migration job is complete, check whether the Hive table data has been migrated to the user_info table. Specifically, do as follows: Log in to the DLI management console and choose SQL Editor. On the displayed page, set Engine to Spark, Queues to the created SQL queue, and Databases to the database created in 1. Then, execute the following query statement: