Saving the password to Hive Metastore

Learn how Sqoop stores the password in the Hive Metastore (HMS) when the Sqoop job is saved and how this password is retrieved from the metastore when the job is run.

Along with securely providing the Hive password to Sqoop, it is essential that the password is safely persisted in the Hive metastore when saving a Sqoop job related to Hive.

The following sections describe how Sqoop stores the Hive password in HMS based on the secure option that you have chosen to provide the password during the Sqoop-Hive import process:

Hive password is provided through a file

When you save the Hive password in a file and then set up Sqoop to use this password for Sqoop-Hive import processes, Sqoop stores the path of the password file in HMS when the Sqoop job is saved.

On running the Sqoop job, the file path is retrieved from HMS and the content of the file is used as the Hive password during the job execution.

Hive password is provided through an alias

When you use an alias to represent the Hive password during the Sqoop-Hive import process, Sqoop stores the alias in the HMS when the Sqoop job is saved.

On running the Sqoop job, the value of the alias is retrieved from the HMS and the value is read by Sqoop. This value is then used to obtain the actual Hive password that is stored in a Credential Provider facility and referenced by a provider path. Once the password is acquired, it is used as the Hive password during the job execution.

Hive password is provided in an insecure way

When the Hive password is provided using one of the following arguments, Sqoop does not store the raw or unencrypted password in the HMS for security reasons and instead prompts the user to enter the Hive password.

  • --hive-password
  • --hs2-password
  • -promptHivePassword

However, if you still want to save the password in HMS although it is insecure, you can set the sqoop.hive.store-raw-password to true either by using this as an argument in your Sqoop import command or by adding this to Cloudera Manager's Advanced Configuration Snippet (Safety Valve) for sqoop-site.xml. This ensures that the password is persisted in HMS and you can run the Sqoop job without having to enter the password.

Setting the sqoop.hive.store-raw-password property through Cloudera Manager
From Cloudera Manager, go to Clusters > SQOOP CLIENT > Configuration and search for 'Gateway Advanced Configuration Snippet (Safety Valve) for sqoop-site.xml'.

Property to store raw Hive password in HMS during a Sqoop-Hive import
Setting the sqoop.hive.store-raw-password property through the Sqoop command
/opt/cloudera/parcels/CDH/bin/sqoop job \
 -Dsqoop.hive.store-raw-password=true \
 --meta-connect "jdbc:postgresql://db.foo.com:5432/employees" \
 --meta-username [***USERNAME***] \
 --meta-password [***PASSWORD***] \
 --create myjob -- import \
 --connection-manager org.apache.sqoop.manager.PostgresqlManager \
 --connect "jdbc:postgresql://db.foo.com:5432/employees" \
 --username [***USERNAME***] \
 --password [***PASSWORD***] \
 --table employees \
 --warehouse-dir /user/hrt_qa/test-sqoop \
 --hive-import \
 --delete-target-dir \
 --hive-overwrite \
 --external-table-dir hdfs:///warehouse/tablespace/external/hive/employees \
 --hs2-url "jdbc:hive2://[***HOST***]:[***PORT***];serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;transportMode=http;httpPath=cliservice;ssl=true;sslTrustStore=[***TRUSTSTORE PATH***];trustStorePassword=[***TRUSTSTORE PASSWORD***]" \
 --hive-user guest \
 --hive-password [***PASSWORD***] \
 -m 1