Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

Solved Go to solution

how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

In below command each and every job i have to mentioned connection,username,password,driver i need dynamically change when we change the database connection

i need to use same job for other SQL databases or NOSQL databases

without creation new job only i have passing the parameters in the sqoop job (parameter are connection,driver connection,username,target directory)

sqoop job --create incjob12 --import--connect jdbc:mysql://localhost/test --driver com.mysql.jdbc.Driver --username it1 --password hadoop --table st1 --incremental lastmodified -check-column ts --target-dir sqin -m 1 --merge-key id

what all the thing i highlighted above i have to enter dynamically parameters to connect different databases

please share me how to create sqoop framework by passing our own parameters

please suggest me how to complete this frame work to connect different client connectors

today i need to complete this task please suggest me

thanks in advance

swathi.T

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

Guru

I am not sure exactly what your requirements are, but here goes...

1. You can set these parameters as OS environment variables for each of your db connections. For example, MYSQL_CONN=xx, MYSQL_DRIVER=xx, MYSQL_UU=xx, MYSQL_PWD=xx, MYSQL_TARGDIR=xx, ORA_CONN=xx, ORA_DRIVER=xx, ORA_UU=xx, etc.

Set these by using the export command, eg. export MYSQL_CONN=xx

Then you simply call the db params you want on the command line, eg.

sqoop job --create incjob12 --import--connect $MYSQL_CONN --driver $MYSQL_DRIVER --username $MYSQL_UU --password $MYSQL_PWD  ...

2. You can do the same thing but with all params related to a db set as a single OS environment variable. Eg.

set $MYSQL="--connect jdbc:mysql://localhost/test --driver com.mysql.jdbc.Driver --username it1 --password hadoop"

and then run your sqoop job as

sqoop job --create incjob12 --import "$MYSQL" --table st1 --incremental lastmodified -check-column ts --target-dirsqin -m 1 --merge-key id

Note the quotes in both setting and invoking the OS environment variable MYSQL. This is because there are spaces in the value.

If I am not understanding your requirements, let me know.

View solution in original post

8 REPLIES 8
Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

Guru

I am not sure exactly what your requirements are, but here goes...

1. You can set these parameters as OS environment variables for each of your db connections. For example, MYSQL_CONN=xx, MYSQL_DRIVER=xx, MYSQL_UU=xx, MYSQL_PWD=xx, MYSQL_TARGDIR=xx, ORA_CONN=xx, ORA_DRIVER=xx, ORA_UU=xx, etc.

Set these by using the export command, eg. export MYSQL_CONN=xx

Then you simply call the db params you want on the command line, eg.

sqoop job --create incjob12 --import--connect $MYSQL_CONN --driver $MYSQL_DRIVER --username $MYSQL_UU --password $MYSQL_PWD  ...

2. You can do the same thing but with all params related to a db set as a single OS environment variable. Eg.

set $MYSQL="--connect jdbc:mysql://localhost/test --driver com.mysql.jdbc.Driver --username it1 --password hadoop"

and then run your sqoop job as

sqoop job --create incjob12 --import "$MYSQL" --table st1 --incremental lastmodified -check-column ts --target-dirsqin -m 1 --merge-key id

Note the quotes in both setting and invoking the OS environment variable MYSQL. This is because there are spaces in the value.

If I am not understanding your requirements, let me know.

View solution in original post

Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

thanks for giving reply

1. but i need to call dynamically this following parameter for oracle/sql means

"--connect jdbc:mysql://localhost/test --driver com.mysql.jdbc.Driver --username it1 --password hadoop" can i able to get the input from the user as like entering the details in the pop up box or as a input parameter

2. also if i has ten tables(t1,t2,t3,...t10) but i need to load only t1, t3, t5, t6, t10 table alone , is their any possibilities to do this

Thanks in advance...

Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

Guru

[answer #2]

In this case I would put your sqoop command in a shell script and then pass parameters to that. It is similar to the question above, but everyone uses the same shell script but passes different parameters to it, which are picked up by the sqoop command text in the script.

Example

Script name: sqoop_job.sh

To run script:

./sqoop_job.sh   "$MYSQL"  st1  ts  sqi 

Script body:

sqoop job --create incjob12 --import ${1} --table ${2} --incremental lastmodified -check-column ${3} --target-dir ${4} -m 1 --merge-key id

For more on shell scripting and passing parameters:

http://linuxcommand.org/writing_shell_scripts.php

Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

thank u greg

but i am not clear about this, if i has ten tables(t1,t2,t3,...t10) but i need to load only t1, t3, t5, t6, t10 table alone from a single script, is their any possibilities to do this

Thanks in advance...

Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

i did the same what u mention

first created the one script vi c1.sh

#!/bin/sh

MYSQL ="--connect jdbc:mysql://xxxxxx/sama --driver com.mysql.jdbc.Driver --username root"

export MYSQL

./c2.sh

then created the vi c2.sh

#!/bin/sh

sqoop import "$MYSQL" --table st1 --target-dir /tmp/sample222 -m 1

and running the ./c1.sh but it shows the folowing error

9061-123.png

please tell how to solve this pblm

thanks in advance

Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

Guru

@swathi thukkaraju I have made a minor correction to my code example above. Each input param in the sqoop_job.sh must be ${1}, ${2} etc instead of $1, $2 etc.

So, use

sqoop job --create incjob12 --import ${1} --table ${2} --incremental lastmodified -check-column ${3} --target-dir ${4} --m 1--merge-key id

and be sure to pass in "$MYSQL" in quotes because the value has spaces in it.

(I just tested this and it works)

Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

how to remove password while executing sqoop job ????


password.png
Highlighted

Re: how to implement and deploying our own sqoop framework to connect databases dynamically(reusing same framework for importing and exporting data from /to different databases like oracle,casandra,mysql etc)

@swathi thukkaraju

Try using the option --password-file to remove the possibility of entering / exposing the password. Below is the link for creating the password file (link)

Don't have an account?
Coming from Hortonworks? Activate your account here