Cloudera Docs

Configuring custom Hive table properties

Learn how to specify custom Hive table properties for the CREATE TABLE statement that Sqoop uses during the Hive table creation process.

You can configure the required property either through Cloudera Manager or by using the --hive-table-property argument in your Sqoop import command.
For example, if you have specified the custom Hive table properties with the "transactional=true" and "transactional_properties=insert_only" key-value pairs, the Hive CREATE TABLE statement is constructed as follows:
CREATE TABLE foo ....
....
TBLPROPERTIES ('transactional'='true', 'transactional_properties'='insert_only');
Order of precedence
The configuration set through the Sqoop argument in the command line takes precedence over the configuration specified through Cloudera Manager. If custom Hive table properties are specified through both the command line and Cloudera Manager, the command line Sqoop argument does not entirely overwrite the Cloudera Manager configuration. Instead, the distinct values are retained and the values with matching keys are replaced with arguments specified through the command line Sqoop argument.
  1. If you are specifying the custom Hive table properties through Cloudera Manager, perform the following steps:
    1. In Cloudera Manager, click Clusters and then select the SQOOP_CLIENT-1 service.
    2. From the Sqoop service, go to the Configuration tab and search for sqoop.hive-table-properties.
    3. Click and specify the custom Hive table property and its value.

      Configuring custom Hive table properties for Sqoop Hive CREATE TABLE statement
    4. Click Save Changes.
  2. If you are specifying the custom Hive table properties through the Sqoop argument, specify the required table properties using the --hive-table-property argument while constructing the Sqoop import command. 
    /opt/cloudera/parcels/CDH/bin/sqoop import \
  -Dsqoop.beeline.env.preserve=KRB5CCNAME \
  --connection-manager org.apache.sqoop.manager.MySQLManager \
  --connect jdbc:mysql://db.foo.com:3306/employees \
  --username [***USERNAME***] \
  --password [***PASSWORD***] \
  --table employees \
  --warehouse-dir /user/hrt_qa/test-sqoop \
  --hive-import \
  --delete-target-dir \
  --hive-overwrite \
  --hive-table-property transactional=true
  --hive-table-property transactional_properties=insert_only