Sqoop Command with Secure Password

Sqoop commands allows you to exchange the data between Hadoop and relational databases such as Netezza, Oracle etc. Sqoop required the password to connect to various databases and of course it has to be secured. In this article, we will discuss on various ways to execute the Sqoop Command with Secure Password.

Read:

• Sqoop import Relational Database Table into HBase Table
• Import data using Sqoop
• Export data using Sqoop
• Sqoop Architecture – Mappers with No Reducers

Sqoop Command with Secure Password

Below are the some of the methods that we can use to secure the password:

There are three approaches that are widely used:

Use — password Option with Sqoop Command

Simple way is to use the — password option while executing sqoop while testing the Sqoop import or export and sqoop security is not a constraint. Again, this is not the good idea if you are connecting to production environment.

Below is the example using — password Option:

$sqoop import --connect jdbc:netezza://localhost/MYDB \
--username testuser \
--password **** \
--table ORDERS

Problem with above approach is it logs password in Shell history.

Use — P Option with Sqoop Command

Sqoop supports reading passwords via standard input (STDIN). You can use this option by with help of — P options with sqoop command. Sqoop will prompt you for the password.

Below is the example of using — P option:

$sqoop import --connect jdbc:netezza://localhost/MYDB \
--username testuser -- P --table ORDERS

Use – password-file Option with Sqoop Command

Best Approach secure your password and execute Sqoop command without getting password prompt is to use the — password-file option. You can keep the password file in the system where you are executing the Sqoop command or you can copy that file to HDFS directory and use the path in the sqoop command. It is better if you create the file in HDFS system so that that will be shared across multiple users.

• Read: Hadoop HDFS Architecture Introduction and Design

$echo -n "password" > /etc/sqoop/conf/passwords/mysql-pass.txt

$chmod 400 /etc/sqoop/conf/passwords/mysql-pass.txt

$sqoop import --connect jdbc:netezza://localhost/MYDB 
--username testuser \
--table ORDERS \
--password-file /etc/sqoop/conf/passwords/mysql-pass.txt