How to Dump the mysql database into hbase using sqoop:

How to Dump the mysql database into hbase using sqoop:

                      Hi Guys yesterday my task is Dump the mysql value into hbase table. so i was dump the mysql table into hbase table. by using the below query is done my job

Step1: create and grant the privillage in mysql server
        CREATE USER 'root'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'root';
        GRANT ALL PRIVILEGES ON *.* TO 'root'@'xxx.xxx.xxx.xxx' WITH GRANT OPTION;
        flush privileges;
step2: Create the table and column family in hbase
       $ hbase shell
         hbase > create  'cities', 'world'
step3: add the mysql connector in the sqoop lib folder
        $ cd /usr/lib/sqoop/lib
        $ wget https://jybookshop.googlecode.com/files/mysql-connector-java-5.1.6-bin.jar
 
step3:run the sqoop command
      $ su hdfds
      $ sqoop import -Dsqoop.hbase.add.row.key=true --connect jdbc:mysql://xx.xx.xxx.xxx:3306/vms --username root --password root --table vms_country --hbase-table cities --column-family world

      When i try to dump another table in hbase at the time i feel very boring. so i was planing to do this action in manually. so i planned to write some code in java. it will like to share the code with you guys.
                                 
                                  The Flow chart of my coding
My codes:
 Mainclass.java
 package com.srini.myexperiment;
 import java.sql.SQLException;
 import java.util.List;
 import net.neoremind.sshxcute.exception.TaskExecFailException;
 import org.apache.commons.mail.EmailException;
 import org.apache.log4j.Logger;

public class MainClass {
final static Logger logger = Logger.getLogger(MainClass.class);

public static void main(String[] args) throws SQLException,
TaskExecFailException, EmailException {
logger.info("Entering application.");

RunTheSqoopCommnad ru = new RunTheSqoopCommnad();
List<String> table = ru.gettable();
ru.runCommand(table);
logger.info("Exiting application.");
}

}

GetTableInfo.java

package com.srini.myexperiment;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;

import org.apache.commons.mail.EmailException;

import org.apache.log4j.Logger;

public class GetTableInfo {

final  Logger logger = Logger.getLogger(this.getClass());

public List<String> gettable() throws EmailException {

logger .info("Mysql Datbase Connectivity is start");

BasicDataSource n = new BasicDataSource();

n.setDriverClassName("com.mysql.jdbc.Driver");

n.setUrl("jdbc:mysql://xxx.xxx.xxx.xxx:3306/vms");

n.setUsername("root");

n.setPassword("root");

Connection conn = null;

try {

conn = n.getConnection();

} catch (SQLException e) {

          logger .error("Database Connection error",e);

          logger .warn("this warning message");

// TODO Auto-generated catch block

          GetMail maill=new GetMail();

          maill.getmessage(e.getMessage());

}

DatabaseMetaData dbm = null;

try {

dbm = conn.getMetaData();

} catch (SQLException e) {

// TODO Auto-generated catch block

logger .error("it can't get the metadat information",e);

logger .warn("this warning message");

GetMail maill=new GetMail();

         maill.getmessage(e.getMessage());

}

String[] types = { "TABLE" };

ResultSet re = null;

try {

re = dbm.getTables(null, null, "%", types);

} catch (SQLException e) {

// TODO Auto-generated catch block

logger .error("The tabel not found error",e);

logger .warn("this warning message");

GetMail maill=new GetMail();

         maill.getmessage(e.getMessage());

}

List<String> result = new ArrayList<String>();

try {

while (re.next()) {

String ta = re.getString("TABLE_NAME");

result.add(ta);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

logger .error("it can't retrive the table in the database",e);

logger .warn("this warning message");

GetMail maill=new GetMail();

         maill.getmessage(e.getMessage());

}

return result;

}

}

RunTheSqoopCommnad.java

package com.srini.myexperiment;

import java.util.List;

import org.apache.commons.mail.EmailException;

import org.apache.log4j.Logger;

import net.neoremind.sshxcute.core.ConnBean;

import net.neoremind.sshxcute.core.SSHExec;

import net.neoremind.sshxcute.exception.TaskExecFailException;

import net.neoremind.sshxcute.task.CustomTask;

import net.neoremind.sshxcute.task.impl.ExecCommand;

public class RunTheSqoopCommnad extends GetTableInfo {

final  Logger logger = Logger.getLogger(this.getClass());

public void runCommand(List<String> tableinfo) throws EmailException {

logger .info("Mysql Datbase Connectivity is start");

String command = "create 'vms',";

for (int j = 0; j < tableinfo.size(); j++) {

command = command.concat(" " + "\'" + tableinfo.get(j) + "\'" + ","

+ " ");

}

command = command.substring(0, command.length() - 2);

ConnBean conn = new ConnBean("192.168.119.131", "cloudera", "cloudera");

SSHExec ssh = SSHExec.getInstance(conn);

ssh.connect();

logger .error("ssh connect connect");

CustomTask hbase = new ExecCommand("echo" + " " + "\"" + command + "\""

+ " | hbase shell");

try {

ssh.exec(hbase);

} catch (TaskExecFailException e) {

// TODO Auto-generated catch block

logger .error("it can't able to create the hbase table ",e);

logger .warn("This Is the warning");

GetMail maill=new GetMail();

         maill.getmessage(e.getMessage());

         e.printStackTrace();

}

CustomTask hdfs = new ExecCommand("su hdfs");

try {

ssh.exec(hdfs);

} catch (TaskExecFailException e) {

// TODO Auto-generated catch block

logger .error("sudo user error ",e);

logger .warn("This Is the warning");

GetMail maill=new GetMail();

         maill.getmessage(e.getMessage());

}

for (int i = 0; i < tableinfo.size(); i++) {

CustomTask cu = new ExecCommand(

"sqoop import -Dsqoop.hbase.add.row.key=true --connect jdbc:mysql://xxx.xxx.xxx.xxx:3306/vms --username root --password root --table"

+ " "

+ tableinfo.get(i)

+ " "

+ "--hbase-table vms --column-family"

+ " "

+ tableinfo.get(i));

try {

ssh.exec(cu);

} catch (TaskExecFailException e) {

// TODO Auto-generated catch block

logger .error("sqoop command err ",e);

logger .warn("This Is the warning");

GetMail maill=new GetMail();

         maill.getmessage(e.getMessage());

         e.printStackTrace();

}

}

ssh.disconnect();

GetMail ma=new GetMail();

ma.getmessage("The Query has been executed successfully in you server");

}

}

GetMail.java

package com.srini.myexperiment;

import org.apache.commons.mail.EmailAttachment;

import org.apache.commons.mail.EmailException;

import org.apache.commons.mail.MultiPartEmail;

public class GetMail {

public void getmessage(String message) throws EmailException {

EmailAttachment emaii = new EmailAttachment();

emaii.setPath("D:\\loging.log");

emaii.setDisposition(EmailAttachment.ATTACHMENT);

emaii.setDescription("logging information");

emaii.setName("Srinivasan");

MultiPartEmail mail = new MultiPartEmail();

mail.setHostName("smtp.googlemail.com");

mail.setSmtpPort(465);

mail.setAuthentication("eceseenu1989", "password");

mail.setSSLOnConnect(true);

mail.setFrom("eceseenu1989@gmail.com");

mail.setSubject("test mail");

mail.setMsg(message);

mail.addTo("eceseenu19898.2@gmail.com");

mail.attach(emaii);

mail.send();

}

}

log4j.properties

# Log levels

log4j.rootLogger=INFO,CONSOLE,R

# Appender Configuration

log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender

# Pattern to output the caller's file name and line number

log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout

log4j.appender.CONSOLE.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n

# Rolling File Appender

log4j.appender.R=org.apache.log4j.RollingFileAppender

# Path and file name to store the log file

log4j.appender.R.File=D:\\loging.log

log4j.appender.R.MaxFileSize=200KB

# Number of backup files

log4j.appender.R.MaxBackupIndex=2

# Layout for Rolling File Appender

log4j.appender.R.layout=org.apache.log4j.PatternLayout

log4j.appender.R.layout.ConversionPattern=%d - %c - %p - %m%n