A Hive data source lets you read data from and write data to Hive. This topic describes how DataWorks syncs Hive data.
How it works
Hive is a data warehouse tool built on Hadoop, used for statistical analysis of large-scale structured log data. Hive maps structured data files to tables and provides SQL query capabilities. It functions as an SQL parsing engine that uses MapReduce for data analysis, HDFS for data storage, and YARN to run the MapReduce programs converted from HiveQL (HQL).
The Hive Reader plugin accesses the HiveMetastore service to obtain metadata for your configured tables. You can read data in either of the following ways:
Read data by using HDFS files
The Hive Reader plugin accesses the HiveMetastore service to get the HDFS file storage path, file format, and delimiters for your configured table. The plugin then reads the data directly from the underlying HDFS files.
Read data by using a Hive JDBC connection
The Hive Reader plugin connects to the HiveServer2 service to read data. This method supports data filtering with a where clause and allows you to read data directly by using SQL statements.
The Hive Writer plugin accesses the HiveMetastore service to get information such as the HDFS file storage path, file format, and delimiters for your configured table. It writes data to HDFS files and then runs a LOAD DATA SQL statement via a Hive JDBC client to load the data from the HDFS files into the Hive table.
The underlying logic of the Hive Writer plugin is the same as the HDFS Writer plugin. You can configure HDFS Writer parameters in the Hive Writer plugin, which are then passed to the HDFS Writer plugin.
Supported versions
Limitations
The Hive data source supports Serverless resource groups (recommended) and exclusive resource groups for Data Integration.
Only the TextFile, ORCFile, and ParquetFile formats are supported for reads.
During an offline sync to a Hive cluster, Data Integration creates temporary files on the server. These files are automatically deleted after the sync task is complete. You must monitor the HDFS directory file count to prevent it from reaching the upper limit, which can make the HDFS file system unavailable. DataWorks does not guarantee that the number of files remains within the HDFS directory limit.
NoteOn the server, you can modify the dfs.namenode.fs-limits.max-directory-items parameter to define the maximum number of non-recursive directories or files in a single directory. The default value is 1,048,576, and the value can range from 1 to 6,400,000. To resolve this issue, increase the value of the HDFS parameter dfs.namenode.fs-limits.max-directory-items or delete unnecessary files.
When you access a Hive data source, Kerberos authentication and
SSL authenticationare currently supported. If authentication is not required, select No Authentication for Authentication Method when you add a new data source.When you use DataWorks to access a Hive data source with Kerberos authentication, if both HiveServer2 and the metastore have Kerberos authentication enabled but use different principals, you must add the following configuration to the Extension Parameters field:
{ "hive.metastore.kerberos.principal": "<your metastore principal>" }
Supported data types
The Hive Reader plugin supports the following data types:
Category | Hive data type |
String | CHAR, VARCHAR, STRING |
Integer | TINYINT, SMALLINT, INT, INTEGER, BIGINT |
Floating-point | FLOAT, DOUBLE, DECIMAL |
Date and time | TIMESTAMP, DATE |
Boolean | BOOLEAN |
Prerequisites
The prerequisites vary based on the data source configuration mode.
Alibaba Cloud instance mode
If you want to sync an OSS-based table, select an Access Identity. You can use an Alibaba Cloud Account, a Alibaba Cloud RAM User, or a RAM Role. Ensure the selected identity has the required OSS permissions. Otherwise, the data sync fails due to insufficient read and write permissions.
The connectivity test does not verify data read and write permissions.
Connection string mode
Data Lake Formation (DLF)
If your Hive data source is an EMR cluster that uses Data Lake Formation (DLF) for metadata management, add the following to the Extension Parameters field when you configure the data source:
{"dlf.catalog.id" : "my_catalog_xxxx"}Replace my_catalog_xxxx with the value of the dlf.catalog.id parameter from your EMR Hive configuration.
High availability (HA)
If the EMR Hive cluster that you want to sync has high availability (HA) enabled, you need to enable the Enable High Availability Mode switch and configure the HA-related information in the Extension Parameters section in the following format. You can go to the EMR console, find the target cluster, and then click Cluster Services in the Operations column to obtain the relevant configuration values.
{
// The following code provides an example of HA configurations.
"dfs.nameservices":"testDfs",
"dfs.ha.namenodes.testDfs":"namenode1,namenode2",
"dfs.namenode.rpc-address.testDfs.namenode1": "",
"dfs.namenode.rpc-address.testDfs.namenode2": "",
"dfs.client.failover.proxy.provider.testDfs":"org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
// (Optional) If the underlying storage is OSS, configure the following parameters in Extension Parameters to connect to the OSS service.
"fs.oss.accessKeyId":"<yourAccessKeyId>",
"fs.oss.accessKeySecret":"<yourAccessKeySecret>",
"fs.oss.endpoint":"oss-cn-<yourRegion>-internal.aliyuncs.com"
}OSS external table
If the underlying storage is OSS, take note of the following:
Set defaultFS with the oss:// prefix. For example,
oss://bucketName.If you are syncing an OSS external table, you must add the OSS configuration to the Extension Parameters field when you configure the Hive data source.
{ "fs.oss.accessKeyId":"<yourAccessKeyId>", "fs.oss.accessKeySecret":"<yourAccessKeySecret>", "fs.oss.endpoint":"oss-cn-<yourRegion>-internal.aliyuncs.com" }If you are syncing an OSS-HDFS external table, you must add the OSS-HDFS configuration to the Extension Parameters field when you configure the Hive data source.
{ "fs.oss.accessKeyId":"<yourAccessKeyId>", "fs.oss.accessKeySecret":"<yourAccessKeySecret>", "fs.oss.endpoint":"cn-<yourRegion>.oss-dls.aliyuncs.com" }
CDH mode
If you want to configure a Hive data source in CDH mode, you must register the CDH cluster with DataWorks.
Create a data source
Before you develop a data sync task, create a data source in DataWorks. For instructions, see Data source management. For detailed parameter descriptions, see the inline hints on the configuration page.
The following sections describe the parameters for different Authentication Method.
Kerberos authentication
Parameter | Description |
keytab file | The .keytab file generated when the service principal is registered in the Kerberos environment. |
conf file | The
|
principal | An identity, which can be a user or a service, that has a unique name and an associated encryption key.
|
SSL authentication
Parameter | Description |
Truststore certificate file | The Truststore certificate file (for example, |
Truststore password | The password that you set when you generated the Truststore certificate file for SSL authentication. |
Keystore certificate file | The Keystore certificate file generated when you enable SSL authentication, such as the |
Keystore password | The password that you set when you generated the Keystore certificate file for SSL authentication. |
Sync task development
For information about the entry point for and the procedure of configuring a synchronization task, see the following configuration guides.
Single-table offline sync
For the procedure, see Configure a task in the codeless UI and Configure a task in the code editor.
For a complete list of parameters and script demos for the code editor, see Appendix: Script demos and parameter reference.
Full-database offline sync
For the procedure, see Offline sync task for an entire database.
Appendix: Script demos and parameter reference
Configure a batch synchronization task by using the code editor
If you want to configure a batch synchronization task by using the code editor, you must configure the related parameters in the script based on the unified script format requirements. For more information, see Script mode configuration. The following information describes the parameters that you must configure for data sources when you configure a batch synchronization task by using the code editor.
Reader script demos
You can read data by using HDFS files or a Hive JDBC connection.
Read data by using HDFS files
{ "type": "job", "steps": [ { "stepType": "hive", "parameter": { "partition": "pt1=a,pt2=b,pt3=c", // Partition information "datasource": "hive_not_ha_****", // Data source name "column": [ // Columns to read "id", "pt2", "pt1" ], "readMode": "hdfs", // Read mode "table": "part_table_1", "fileSystemUsername" : "hdfs", "hivePartitionColumn": [ { "type": "string", "value": "Partition Name 1" }, { "type": "string", "value": "Partition Name 2" } ], "successOnNoFile":true }, "name": "Reader", "category": "reader" }, { "stepType": "hive", "parameter": { }, "name": "Writer", "category": "writer" } ], "version": "2.0", "order": { "hops": [ { "from": "Reader", "to": "Writer" } ] }, "setting": { "errorLimit": { "record": "" // The number of error records allowed }, "speed": { "concurrent": 2, // The concurrency level for the task. "throttle": true, // Enables or disables throttling. If false, the mbps parameter is ignored. "mbps":"12" // The maximum transfer rate in MBps. } } }Read data by using a Hive JDBC connection
{ "type": "job", "steps": [ { "stepType": "hive", "parameter": { "querySql": "select id,name,age from part_table_1 where pt2='B'", "datasource": "hive_not_ha_****", // Data source name "session": [ "mapred.task.timeout=600000" ], "column": [ // Columns to read "id", "name", "age" ], "where": "", "table": "part_table_1", "readMode": "jdbc" // Read mode }, "name": "Reader", "category": "reader" }, { "stepType": "hive", "parameter": { }, "name": "Writer", "category": "writer" } ], "version": "2.0", "order": { "hops": [ { "from": "Reader", "to": "Writer" } ] }, "setting": { "errorLimit": { "record": "" }, "speed": { "concurrent": 2, // The concurrency level for the task. "throttle": true, // Enables or disables throttling. If false, the mbps parameter is ignored. "mbps":"12" // The maximum transfer rate in MBps. } } }
Reader parameters
Parameter | Description | Required | Default |
datasource | The name of the data source you created in DataWorks. | Yes | None |
table | The name of the table that you want to sync. Note The table name is case-sensitive. | Yes | None |
readMode | The read mode. Valid values:
Note
| No | None |
partition | The partition information of the Hive table.
| No | None |
session | The session-level configurations for reading data over a Hive JDBC connection. You can set client parameters such as | No | None |
column | The columns to read, for example
| Yes | None |
querySql | When you read data by using a Hive JDBC connection, you can directly configure the querySql parameter to read data. | No | None |
where | When you read data by using a Hive JDBC connection, you can use a where clause to filter data. | No | None |
fileSystemUsername | Data reads that use the HDFS method use the user configured on the Hive data source page by default. If anonymous login is configured on the data source page, the | No | None |
hivePartitionColumn | If you want to synchronize the values of partition fields downstream, you can switch to the code editor to configure the hivePartitionColumn parameter. | No | None |
successOnNoFile | In HDFS read mode, specifies whether the sync task is considered successful when the directory is empty. | No | None |
Writer script demo
{
"type": "job",
"steps": [
{
"stepType": "hive",
"parameter": {
},
"name": "Reader",
"category": "reader"
},
{
"stepType": "hive",
"parameter": {
"partition": "year=a,month=b,day=c", // Partition configuration
"datasource": "hive_ha_shanghai", // Data source
"table": "partitiontable2", // Destination table
"column": [ // Column configuration
"id",
"name",
"age"
],
"writeMode": "append" ,// Write mode
"fileSystemUsername" : "hdfs"
},
"name": "Writer",
"category": "writer"
}
],
"version": "2.0",
"order": {
"hops": [
{
"from": "Reader",
"to": "Writer"
}
]
},
"setting": {
"errorLimit": {
"record": ""
},
"speed": {
"throttle":true, // If you set throttle to false, the mbps parameter does not take effect, which indicates that no throttling is triggered. If you set throttle to true, throttling is enabled.
"concurrent":2, // The number of concurrent jobs.
"mbps":"12" // Throttling rate
}
}
}Writer parameters
Parameter | Description | Required | Default |
datasource | The data source name. It must match the name of the data source you created. | Yes | None |
column | The columns to write, for example,
| Yes | None |
table | The name of the Hive table to which you want to write data. Note The table name is case-sensitive. | Yes | None |
partition | The partition information of the Hive table.
| No | None |
writeMode | The write mode for Hive table data. After data is written to an HDFS file, the Hive Writer plugin executes the writeMode specifies the data loading behavior:
Note writeMode is a high-risk parameter. Pay close attention to the data output directory and the behavior of writeMode to prevent accidental data deletion. The data loading behavior requires the hiveConfig parameter. Verify your configuration. | Yes | None |
hiveConfig | You can configure more Hive extension parameters in hiveConfig, including hiveCommand, jdbcUrl, username, and password.
| Yes | None |
fileSystemUsername | When you write data to a Hive table, the user configured on the Hive data source page is used by default. If anonymous login is configured on the data source page, the admin account is used by default. If a permission issue occurs during the sync task, switch to the code editor and configure the fileSystemUsername parameter. | No | None |
enableColumnExchange | If you set this parameter to True, column reordering is enabled. Note This parameter is supported only for the Text format. | No | None |
nullFormat | Data Integration uses the nullFormat parameter to define which string values are treated as null. For example, if you configure Note The string "null" (the four characters n, u, l, l) is different from an actual null value. | No | None |