Requirements for Hive ACID

Capture	Hub	Integrate

This section describes the requirements, access privileges, and other features of HVR when using Hive ACID (Atomicity, Consistency, Isolation, Durability) for replication. For information about compatibility and supported versions of Hive ACID with HVR platforms, see Platform Compatibility Matrix.

For the Capabilities supported by HVR on Hive ACID, see Capabilities for Hive ACID.

For information about the supported data types and mapping of data types in source DBMS to the corresponding data types in target DBMS or file format, see Data Type Mapping.

ODBC Connection

HVR uses ODBC connection to the Hive ACID server. The HortonWorks ODBC driver or Cloudera ODBC driver must be installed on the machine from which HVR connects to the Hive ACID server.

For information about the supported ODBC driver version, refer to the HVR release notes (hvr.rel) available in hvr_home directory or the download page.

HVR can deliver changes into Hive ACID tables as a target location for its refresh and integration. Delivery of changes into Hive ACID tables for Hive versions before 2.3 is only supported if the following action is defined:

For file formats (JSON and Avro), the following action definition is required to handle certain limitations when execute any SQL statement against Hive external tables (due to compatibility issues with Hive 3 Metastore and Hive ODBC drivers):

Location Connection

This section lists and describes the connection details required for creating Hive ACID location in HVR.

SSL Options

Hive ACID on Amazon Elastic MapReduce (EMR)

To enable Hive ACID on Amazon EMR,

1. Add the following configuration details to the hive-site.xml file available in /etc/hive/conf on Amazon EMR:

   <!-- Hive ACID support -->
   <property>
      <name>hive.compactor.initiator.on</name>
      <value>true</value>
   </property>
   <property>
      <name>hive.compactor.worker.threads</name>
      <value>10</value>
   </property>
   <property>
      <name>hive.support.concurrency</name>
      <value>true</value>
   </property>
   <property>
      <name>hive.txn.manager</name>
      <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
   </property>
   <property>
      <name>name>hive.enforce.bucketing</name>
      <value>true</value>
   </property>
   <property>
      <name>hive.exec.dynamic.partition.mode</name>
      <value>nostrict</value>
   </property>
   <!-- Hive ACID support end -->
   

2. Save the modified hive-site.xml file.

3. Restart Hive on Amazon EMR.
   For more information on restarting a service in Amazon EMR, refer to How do I restart a service in Amazon EMR? in AWS documentation.

Integrate and Refresh Target

HVR supports integrating changes into Hive ACID location. This section describes the configuration requirements for integrating changes (using Integrate and refresh) into Hive ACID location. For the list of supported Hive ACID versions, into which HVR can integrate changes, see Integrate changes into location in Capabilities.

Burst Integrate and Bulk Refresh

While HVR Integrate is running with parameter /Burst and Bulk Refresh, HVR can stream data into a target database straight over the network into a bulk loading interface specific for each DBMS (e.g. direct-path-load in Oracle), or else HVR puts data into a temporary directory (‘staging file') before loading data into a target database.

For best performance, HVR performs Integrate with /Burst and Bulk Refresh on Hive ACID location using staging files. HVR implements Integrate with /Burst and Bulk Refresh (with file staging) into Hive ACID as follows:

1. HVR first creates Hive external tables using Amazon/HortonWorks Hive ODBC driver
2. HVR then stages data into:
   • S3 using AWS S3 REST interface (cURL library) or
   • HDFS/Azure Blob FS/Azure Data Lake Storage using HDFS-compatible libhdfs API
3. HVR uses Hive SQL commands 'merge' (Integrate with /Burst) or 'insert into' (Bulk Refresh) against the Hive external tables linked to S3/HDFS/Azure Blob FS/Azure Data Lake Storage to ingest data into ACID Hive managed tables.

The following is required to perform Integrate with parameter /Burst and Bulk Refresh into Hive ACID:

1. HVR requires an AWS S3 or HDFS/Azure Blob FS/Azure Data Lake Storage location to store temporary data to be loaded into Hive ACID.

   If AWS S3 is used to store temporary data then HVR requires the AWS user with 'AmazonS3FullAccess' policy to access this location. For more information, refer to the following AWS documentation:

   • Amazon S3 and Tools for Windows PowerShell
   • Managing Access Keys for IAM Users
   • Creating a Role to Delegate Permissions to an AWS Service

2. Define action LocationProperties on Hive ACID location with the following parameters:

   • /StagingDirectoryHvr: the location where HVR will create the temporary staging files. The format for AWS S3 is s3://S3 Bucket/Directory and for HDFS is hdfs://NameNode:Port/Directory
   • /StagingDirectoryDb: the location from where Hive ACID will access the temporary staging files.
     If /StagingDirectoryHvr is an AWS S3 location then the value for /StagingDirectoryDb should be same as /StagingDirectoryHvr.
   • /StagingDirectoryCredentials: the AWS security credentials. The supported formats are 'aws_access_key_id="key";aws_secret_access_key="secret_key"' or 'role="AWS_role"'. How to get your AWS credential or Instance Profile Role can be found on the AWS documentation web page.

3. Since HVR uses CSV file format for staging, the following action definitions are required to handle certain limitations of the Hive deserialization implementation:

   Group	Table	Action
   Hive ACID	*	TableProperties/CharacterMapping="\x00>\\0;\n>\\n;\r>\\r;">\""
   Hive ACID	*	TableProperties/MapBinary=BASE64