Java - MySQL to Hive Import where MySQL Running on

Question

Before any Answer and Comments. I tried several option I found in Stackoverflow but end with a failure. Following are those links -

• How can I execute Sqoop in Java?
• How to use Sqoop in Java Program?
• How to import table from MySQL to Hive using Java?
• How to load SQL data into the Hortonworks?

I tried it in Horton Sandbox through command line and succeded.

sqoop import --connect jdbc:mysql://192.168.56.101:3316/database_name --username=user --password=pwd --table table_name --hive-import -m 1 -- --schema default

Where 192.168.56.101 is for Windows and 192.168.56.102 for Horton Sandbox 2.6.

Now I want to do the same thing from Java where that java code run somewhere else but not in horton sandbox.

• How to loacate HIVE_HOME and other Sqoop parameters because that are running in Sandbox.
• parameters which I have to pass. It should be passes as SqoopOptions or Sqoop.runTools String Array Arguments. Both failing.
• I also get confused While import library (com.cloudera.sqoop and org.apache.sqoop) and get this

The method run(com.cloudera.sqoop.SqoopOptions) in the type ImportTool is not applicable for the arguments (org.apache.sqoop.SqoopOptions) with this two line (option parameter are added between this two lines)

 SqoopOptions options = new SqoopOptions();
 int ret = new ImportTool().run(options);

if I choose Cloudera method get deprecated but if I choose apace run method doesn't accept the options argument

I am strucked in this from weeks. Please Help.

Answer 1

Yes you can do it via ssh. Horton Sandbox comes with ssh support pre installed. You can execute the sqoop command via ssh client on windows. Or if you want to do it programaticaly (thats what I have done in java) you have to follow this step.

1. Download sshxcute java library : https://code.google.com/p/sshxcute/
2. Add to the build path of your java project which contains the following java code

---

import net.neoremind.sshxcute.core.SSHExec;
import net.neoremind.sshxcute.core.ConnBean;
import net.neoremind.sshxcute.task.CustomTask;
import net.neoremind.sshxcute.task.impl.ExecCommand;

public class TestSSH {

public static void main(String args[]) throws Exception{

    // Initialize a ConnBean object, parameter list is ip, username, password

    ConnBean cb = new ConnBean("192.168.56.102", "root","hadoop");

    // Put the ConnBean instance as parameter for SSHExec static method getInstance(ConnBean) to retrieve a singleton SSHExec instance
    SSHExec ssh = SSHExec.getInstance(cb);          
    // Connect to server
    ssh.connect();
    CustomTask sampleTask1 = new ExecCommand("echo $SSH_CLIENT"); // Print Your Client IP By which you connected to ssh server on Horton Sandbox
    System.out.println(ssh.exec(sampleTask1));
    CustomTask sampleTask2 = new ExecCommand("sqoop import --connect jdbc:mysql://192.168.56.101:3316/mysql_db_name --username=mysql_user --password=mysql_pwd --table mysql_table_name --hive-import -m 1 -- --schema default");
    ssh.exec(sampleTask2);
    ssh.disconnect();   
}
}

---