Batch synchronize EMR Hive table to MaxCompute with DataWorks - DataWorks - Alibaba Cloud - DataWorks

This topic describes how to perform batch synchronization from a single EMR Hive table to MaxCompute, covering best practices for data source configuration, network connectivity, and task configuration.

Background

Built on Hadoop, Hive is a data warehouse tool used to store, query, and analyze large-scale data. Hive maps structured data files to a database table and provides SQL query capabilities by converting SQL statements into MapReduce jobs. You can use Data Integration to synchronize data between Hive and other data sources.

Prerequisites

You have purchased a serverless resource group.
You have created Hive and MaxCompute data sources. For more information, see Configure a data source.
You have established network connectivity between the resource group and the data source. For more information, see Overview of network connectivity solutions.

Note
If you connect to an EMR cluster from an exclusive resource group over the public internet, you must add rules to the security group of the EMR cluster. These rules must allow inbound traffic from the exclusive resource group's EIP to the required EMR cluster ports, such as 10000, 9093, and 8020.

Limitations

Syncing source data to MaxCompute external tables is not supported.

Procedure

Step 1: Create a node and configure task

For instructions on how to create and configure a node in the Codeless UI, see Configure a task in the Codeless UI.

Step 2: Configure data source and destination

Configure data source (Hive)

The data source is a Hive table. The following table describes the key parameters.

Parameter	Description
Hive Read Method	Read data from HDFS files.: The Hive Reader plugin accesses the Hive Metastore service to get table metadata, such as its HDFS file path, file format, and delimiters, and reads data directly from the HDFS files. Read data using Hive JDBC (supports conditional filtering).: The Hive Reader plugin connects to the HiveServer2 service via JDBC to read data. This method allows you to use a `where` clause for data filtering. Note The HDFS method is more efficient. The JDBC method generates a MapReduce program, resulting in lower performance. Note that the HDFS method does not support conditional filtering or reading data from a view. Choose the method that best fits your needs.
Table	Select the Hive table that you want to synchronize. We recommend that the table has the same schema in both the development and production environments of the Hive data source. Note The UI displays the table list and schemas from the development environment of your Hive data source. If the table definitions differ between your development and production environments, the task might be configured correctly but fail in the production environment with an error, such as 'table not found' or 'column not found'.
Parquet schema	If the Hive table is stored in the Parquet format, you must configure the corresponding Parquet schema.

Configure data destination (MaxCompute)

The data destination is a MaxCompute table. The following table describes the key parameters.

Note

For parameters not described in the following table, you can use the default values.

Parameter	Description
Tunnel Resource Group	This is the MaxCompute data transfer resource, also known as a tunnel quota. By default, 'public transport resources' is selected, which is the free quota for MaxCompute. If your exclusive tunnel quota becomes unavailable due to expiration or an overdue payment, the task automatically switches to use the public transport resources at runtime.
Table	Select the destination MaxCompute table. If you use a standard DataWorks workspace, make sure that a MaxCompute table with the same name and schema exists in both the development and production environments. You can also click Generate Destination Table Schema. This action automatically creates a table and lets you manually edit the CREATE TABLE statement. Note Note the following: If the destination MaxCompute table does not exist in the development environment, you cannot find the table in the destination table drop-down list when you configure the batch synchronization node. If the destination MaxCompute table does not exist in the production environment, the synchronization task fails after it is published and run because the destination table cannot be found. If the table schemas in the development and production environments are inconsistent, the field mapping during the scheduled run may differ from the mapping that is configured in the node. This can cause data to be written incorrectly.
Partition Information	For a partitioned table, you must specify values for the partition columns. The value can be a fixed value, such as `ds=20220101`. The value can be a scheduling parameter, such as `ds=${bizdate}`. At runtime, the system automatically replaces the parameter with its scheduled value.
Write Method	Specify whether to overwrite existing data in the destination table or append new data.

Step 3: Configure and validate task

Field mapping: Typically, Map Fields with the Same Name or Map Fields in the Same Line is sufficient. If the order or names of the fields in the source and destination differ, you can manually adjust the mappings.
Channel control: Set Policy for Dirty Data Records to reject dirty data, which ensures data quality. You can use the default values for other parameters initially.

Step 4: Debug and run task

On the right side of the batch synchronization node configuration page, click Run Configuration. Set the Resource Group and Script Parameters for the debug run. Then, click Run in the top toolbar to test the synchronization pipeline.
In the left-side navigation pane, click , and then click to the right of Personal Directory to create a file with a .sql extension. Run the following SQL statement to query the data in the destination table and verify the data.
Note
- To query data in this way, you must bind the destination MaxCompute project as computing resources for DataWorks.
- On the right side of the .sql file editor, click Run Configuration, specify the data source Type, Computing Resources, and Resource Group, and then click Run in the top toolbar.
```
SELECT * FROM <your_maxcompute_table> WHERE pt=<your_partition> LIMIT 20;
```

Step 5: Configure scheduling and publish task

On the right side of the batch synchronization task, click Scheduling Settings. Configure the parameters for periodic runs as described in Scheduling configuration. Then, click Publish in the top toolbar. In the panel that appears, follow the on-screen instructions to publish the task.