Write a program using Sqoop to transfer the Digital Library Book Data and related linked to multime- dia/PDF les stored using MySQL to HDFS and from HDFS to MySQL

Download packages click here

Download database click here

 
SqoopImpExp.java

/**
 * STEPS FOR EXECUTION : (for this assign, we are using hadoop 1.0.0 & Sqoop 1.4.6)
 ----------------------------------------------------------------------------------------
 1. open mysql by typing
  # mysql -u root -p
  enter mysql password
 ----------------------------------------------------------------------------------------
 2. create database
  mysql> create database eBooks;
 ----------------------------------------------------------------------------------------
 3. exit mysql by
  mysql> exit;
 ----------------------------------------------------------------------------------------
 4. now copy eBooks.sql (database file given along with program) to desktop
  # cd Desktop
 ----------------------------------------------------------------------------------------
 5. type in terminal
  # mysql -u root -p eBooks < eBooks.sql
 ----------------------------------------------------------------------------------------
 6. now check database in mysql
  # mysql -u root -p
  mysql> use eBooks;
  mysql select * from eBooks;
  
  u will get entries of books.
  exit from mysql.
  
  mysql> exit;
 ----------------------------------------------------------------------------------------
 7. format namenode of hadoop by firing command
   # hadoop namenode -format
  ----------------------------------------------------------------------------------------
  8. start hadoop
   # start-all.sh
  ----------------------------------------------------------------------------------------
 9. Now create java project in eclipse & paste code given below.
 ----------------------------------------------------------------------------------------
 10. add external jars required from 'Build Path > Configure Build Path > Libraries > Add External Jars'
  add jars from directories given below
  > /usr/lib/hadoop-1.0.0/hadoop-core-1.0.0.jar
  > /usr/lib/hadoop-1.0.0/lib/*.jar
  > /usr/lib/sqoop-1.4.6.bin__hadoop-1.0.0/sqoop-1.4.6.jar
  > /usr/lib/sqoop-1.4.6.bin__hadoop-1.0.0/lib/*.jar
 ----------------------------------------------------------------------------------------
 11. Run the project... :)
 ----------------------------------------------------------------------------------------
 12. the program takes database name & table name as input. which must be created already in MySQL in order to execute program.
 ----------------------------------------------------------------------------------------
 13. it also takes import path & export datafile path as input.
  Import path is path where output will be stored in case of import operation. (output directory path u r mentioning must not be exit. it will be automatically created by program)
  Export datafile path is path to datafile from which data to be exported in mysql.
  
  In both cases, path must be provided as full path from root
  for example, if ur datafile is inside 'in' folder on desktop, u must specify path as
  
   /home/abhi/Desktop/in/datafile     (in this case abhi is my username, just replace it with ur username)
 ----------------------------------------------------------------------------------------
 NOTE : In case if u are not get provided with eBooks.sql file (dataset) at exam time, u have to create ur own dummy database & work on it.
 ----------------------------------------------------------------------------------------
 14. Ohh, dont forget to stop ur hadoop daemons at end by typing
  # stop-all.sh
 ----------------------------------------------------------------------------------------
 HAPPY CODING.... :)
*/
import java.util.Scanner;
import com.cloudera.sqoop.*;
import com.cloudera.sqoop.tool.*;

public class SqoopImpExp {
 
 public static Scanner s = new Scanner(System.in);
 private static SqoopOptions options;
 
 private static String dataBase = null;
 private static String tableName = null;
 private static String DBURL = null;
 private static final String USERNAME = "root";
 private static final String PASSWORD = "123";
 private static String IMPORT_DIR = null;  // will be used in case of import
 private static String EXPORT_FILE = null;  // will be used in case of export
 
 public static void initialize(String db, String table){
  
  dataBase = db;
  tableName = table;
  
  DBURL = "jdbc:mysql://localhost:3306/"+dataBase;
  options = new SqoopOptions(DBURL,table);
  options.setUsername(USERNAME);
  options.setPassword(PASSWORD);
  options.setFieldsTerminatedBy(',');
  options.setLinesTerminatedBy('\n');
 }
 
 // IMPORTING.. i.e mysql table to hdfs file
 public static void mysqlToHdsf(){
  
  System.out.println("--------------------- IMPORT OPERATION ---------------------");
  
  System.out.println("Note that output directory must not exist.");
  System.out.print("\nEnter direcory where output will be stored (eg, /home/abhi/Desktop/out) : ");
  IMPORT_DIR = s.next();
  
  options.setTargetDir(IMPORT_DIR);
  
  @SuppressWarnings("deprecation")
  ImportTool it = new ImportTool();
  int retVal = it.run(options);
  
  if(retVal == 0){
   System.out.println("SUCCESSFULLY Imported...");
  }
  else{
   System.out.println("ERROR");
  }
 }
 
 // EXPORTING... i.e hdfs file to mysql table
 public static void hdfsToMysql(){
  
  System.out.println("--------------------- IMPORT OPERATION ---------------------");
  
  System.out.println("delete all entries from mysql table '"+tableName+"' before exporting. else Exception may cause.");
  System.out.print("\ndone ? (y/n) : ");
  String ch = s.next();
  if(ch.equalsIgnoreCase("y"));
  else{
   System.out.println("Export operation aborted by user.."); return;
  }
  
  System.out.print("\nEnter path to the data to be exported to mysql (eg, /home/abhi/Desktop/in/data) : ");
  EXPORT_FILE = s.next();
  
  String []columns = {"book_id", "book_title", "book_author"};
  
  options.setExportDir(EXPORT_FILE);
  options.setDbOutputColumns(columns);
  options.setUpdateMode(SqoopOptions.UpdateMode.AllowInsert);  
  
  ExportTool it = new ExportTool();
  int retVal = it.run(options);
  
  if(retVal == 0){
   System.out.println("SUCCESSFULLY Exported...");
  }
  else{
   System.out.println("ERROR");
  }
 }
 
 public static void main(String []args)
 {
  String db, table;
  int choice;
  
  System.out.println("Note : Database & Table must be already present in MySQL.");
  System.out.print("\nEnter name of the database : ");
  db = s.next();
  System.out.print("\nEnter name of the table : ");
  table = s.next();
   
  initialize(db, table);
  
  do{ 
   System.out.println("================================ SQOOP ================================");
   System.out.println("1. MySQL to HDFS [IMPORT]");
   System.out.println("2. HDFS to MySQL [EXPORT]");
   System.out.println("3. Exit");
   System.out.print("\nEnter choice : ");
   choice = s.nextInt();
   
   switch(choice){
    case 1 : mysqlToHdsf(); break;
    case 2 : hdfsToMysql(); break;
    case 3 : System.exit(0);
    
    default: System.out.println("Invalid choice... Please try again...");
   }
  }while(choice != 3);
 }
 
}