Using Kettle to Migrate Small Tables from AWS Redshift to a DWS Cluster

Kettle Overview

Kettle is an open-source Extract, Transform, Load (ETL) tool, also known as KDE Extraction, Transportation, Transformation, and Loading Environment. It provides a GUI that enables you to design and configure ETL processes by dragging and dropping components. Kettle supports multiple data sources and destinations, including relational databases, files, APIs, and Hadoop. It offers extensive conversion and cleansing capabilities such as data format transformation, filtering, merging, and computing.

Key features include:

• Building data pipelines with drag-and-drop operations, no coding required
• Connecting to multiple data sources
• Visualizing data pipelines ·
• Developing pipelines using templates
• Visualizing scheduled tasks
• Comprehensive Hadoop support

• DWS must bind an EIP before it can connect to Kettle.
• Both Kettle and CDM are suitable for batch processing. For smaller data volumes or table quantities, Kettle is recommended; otherwise, use CDM.
• Kettle can export data from and import data into databases.
• Kettle can synchronize data in real-time using triggers, timestamp fields, and Kafka.

This practice takes about 90 minutes. For example, migrating Redshift involves the following operations:

1. Prerequisites
2. Migrating Metadata: Use the DSC tool to migrate metadata.
3. Creating a Transformation Task and Configuring Source and Destination Databases: Create a transformation task and configure the source and destination databases.
4. Migrating Data: Perform full migration and incremental migration.
5. Executing Migration Jobs Concurrently: Create a job to concurrently execute transformation tasks for simultaneous table migration.
6. Optimizing the Migration Job: Adjust the Kettle memory size and the number of jobs to improve the migration efficiency.
7. Checking Table Data: Verify data consistency after migration.

Prerequisites

• You have purchased a DWS cluster, bound an EIP to the cluster, and created the destination database dws_vd.
• You have obtained the DSC tool package and downloaded and installed DSC.
• You have obtained the DataCheck tool package and downloaded and installed DataCheck.
• You have obtained the Kettle tool package and the dws-client plugin and configured Kettle. For details, see "Preparing the Kettle Environment" and "Installing the dws-kettle-plugin Custom Plug-in" in Using Kettle to Import Data.
• JDK 1.8 has been installed and environment variables have been configured.

Migrating Metadata

Use the DSC migration tool developed by Huawei Cloud to convert the DDL statements of the sample table exported from the source end in Redshift to executable SQL statements in DWS.

1. Decompress the obtained DSC tool package and save the files to be converted to the input directory of the DSC.
   

   The Redshift table structure design does not include indexes. Performance is mainly influenced by the data distribution key (DISTSTYLE) and sort key (SORTKEY) specified in the DDL.

   Figure 1 DDL statement
   

2. Customize the features-pg.properties file.
3. Go to the DSC directory and run the runDSC script. In Windows, double-click runDSC.bat. Run runDSC.sh in Linux.
4. Convert the syntax.

   1	runDSC.bat -S postgres

5. View the result and conversion information.
6. Connect to DWS, execute the DDL statement converted in the previous step to create a table.
   

   For more information about DSC, see DSC Tool Guide.

Creating a Transformation Task and Configuring Source and Destination Databases

1. After deploying Kettle, double-click the Spoon script in the data-integration directory of Kettle to start Kettle.
   Figure 2 Starting Kettle
   

   The following page is displayed.

   Figure 3 Kettle GUI
   

2. Choose File > New > Transformation to create a new transformation.
   Figure 4 Creating a transformation task
   

3. Click Database connection under View, right-click New, and configure the source and destination database connections.
   • The connection configuration of the redshift-VD database (source) is as follows:
     • Custom connection URL: jdbc: redshift://dwdsi-simulacao.cslagxelrdnm.us-east-1.redshift.amazonaws.com:xxxx/dsidw
     • Username: xxxxxxx
     • Password: xxxxxxx
     Figure 5 Configuring the source
     
   • The connection configuration of the DWS-VD database (destination) is as follows:
     • Host Name: EIP of the DWS cluster.
     • Database Name: dws_vd
     • Username: dbadmin
     • Password: password of user dbadmin.
     Figure 6 Configuring the destination
     
     Figure 7 options
     
     

     After connecting the databases, right-click the source and destination links and click Share. This prevents the need to reconfigure database links in future tasks.

4. Drag the Table input and DWS TableOutput components from the Input and Output directories under Design to the right panel separately.
5. Right-click the connection line of the Table input component to connect the two components.
   Figure 8 Connecting components
   

Migrating Data

Full data migration

1. Right-click Table Input and choose Edit from the shortcut menu. Select the source database link.
   Figure 9 Editing Table Input
   

2. Right-click DWS TableOutput and choose Edit from the shortcut menu. Select the destination database link. Select Truncate table and Specify database fields, click Get fields under Database fields to acquire the source and destination field mapping, and then click OK.
   Figure 10 Editing Table output
   
   Figure 11 Editing database fields
   

3. After the configuration, click Run to start the migration task.
   Figure 12 Running the task
   

Incremental data migration

The steps for incremental and full migration are essentially the same. The difference is that a WHERE condition is added to the source SQL statement, and Truncate Table is deselected in the destination configuration.

1. Right-click Table Input and choose Edit from the shortcut menu. Select the source database link.
   Figure 13 Editing Table Input
   

2. Right-click DWS TableOutput and choose Edit from the shortcut menu. Select the destination database link. Deselect Truncate table, click Get fields under Database fields to acquire the source and destination field mapping, and then click OK.
   Figure 14 Editing Table output
   

   

3. After the configuration, click Run to start the migration task.
   Figure 15 Running the task
   

Executing Migration Jobs Concurrently

Job task configuration combines the transformation of multiple tables into one task for faster execution.

1. Choose File > New > Job. Drag the target components to the panel and connect them using lines.
   Figure 16 Creating a job
   

2. Double-click Transformation and select the transformation task that has been configured and saved.
   Figure 17 Configuring a transformation
   

3. Right-click the Start component, select the Run Next Entries in Parallel component, and set concurrent task execution.
   Figure 18 Setting concurrency
   

   After the setting, a double slash will appear between the Start and Transformation components.

   Figure 19 Setting concurrency succeeded
   

4. Click Run to execute the conversion tasks concurrently.
   Figure 20 Executing the conversion tasks concurrently
   

Optimizing the Migration Job

1. Configure the Kettle memory.

   To increase the number of concurrent Kettle requests and the size of cached data, you can adjust the Kettle memory size.

   Use Notepad++ to open the Spoon.bat script and edit the memory content. It is recommended to set it to 60-80% of the host memory.

   Figure 21 Configuring the memory
   

2. Configure a job.
   • For tables with less than 10 million records, configure around 10 tables per job.
   • For larger tables with about 100 million records, configure 2 to 3 tables per job. This allows for individual task scheduling if any issues arise, enhancing efficiency.
   • For tables with over 100 million records, especially those with many fields, Kettle's extraction efficiency may be slow. Choose a migration method based on your specific service needs.
     Figure 22 Configuring a job
     
   • Group tasks with similar table data volumes into the same job. This ensures all tasks finish around the same time, preventing tail tasks from delaying subsequent jobs.
   • If a task error occurs, check the error logs. Issues are often due to source link restrictions causing disconnections. In such cases, restart Kettle and try again.

Checking Table Data

After the migration, check whether the data on the source and destination databases is consistent using DataCheck.

1. Download and unzip DataCheck-*.zip, and then go to the DataCheck-* directory to use it. For how to use the files in the directory, see Table 1.
2. Configure the tool package.
   • In Windows:
     Open the dbinfo.properties file in the conf folder and configure it based on your actual needs.

     

     You can use the command below in the tool to generate the ciphertext of src.passwd and dws.passwd.

     encryption.bat password

     

     After the command is executed, an encrypted file is generated in the local bin directory.

     

   • In Linux:

     The method of generating the ciphertext is similar to that for Windows. The command is sh encryption.sh Password. Other steps are the same.

     

3. Check data.

   In Windows:

   1. Open the check_input.xlsx file, enter the schemas, databases, source table, and destination table to be checked, and fill in Row Range with a data query statement to specify the query scope.
      

      • After configuring the source database name in the configuration file, the source database name in the check_input.xlsx file defaults to this. However, if a different source database name is specified in the check_input.xlsx file, it will take precedence.
      • The Check Strategy offers three levels: high, middle, and low. If unspecified, the default is low.
      • The Check Mode supports statistics (statistical value checks).

      The following figure shows the check_input.xlsx file for metadata comparison.

      Figure 23 check_input.xlsx
      
   2. Run the datacheck.bat command in the bin directory to execute the check tool.

      

   3. View the generated check result file check_input_result.xlsx.

      The following figure shows the statistical value check.

      

   In Linux:

   1. Edit and upload the check_input.xlsx file. Refer to the step 1 for Windows.
   2. Run the sh datacheck.sh command to execute the check tool.

      

   3. View the check result in the check_input_result.xlsx file. (The check result analysis is the same as that for Windows.)

Related Information

For more information, see DataCheck.