Sqoop Java Client

Sqoop Java Client

Apache Sqoop

Apache Sqoop(TM) is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases.

Sqoop successfully graduated from the Incubator in March of 2012 and is now a Top-Level Apache project: More information

Latest stable release is 1.4.4 (download, documentation). Latest cut of Sqoop2 is 1.99.2 (download, documentation).

Here is the Java Client for Apache Sqoop import data from MySql to Hadoop hdfs :)

//Here I am using a table Persons, with columns PersonID and LastName
import org.apache.sqoop.client.SqoopClient;
import org.apache.sqoop.model.MConnection;
import org.apache.sqoop.model.MConnectionForms;
import org.apache.sqoop.model.MJob;
import org.apache.sqoop.model.MJobForms;
import org.apache.sqoop.model.MSubmission;
import org.apache.sqoop.validation.Status;

/**
 * @author  devan
 * @date 19-Sep-2013
 * @mail msdevanms@gmail.com
 */

public class SqoopImport {
 public static void main(String[] args) {
  
  
  String connectionString = "jdbc:mysql://YourMysqlIP:3306/test";
  String username = "YourMysqUserName";
  String password = "YourMysqlPassword";
  String schemaName = "YourMysqlDB";
  String tableName = "Persons";
  String columns = "PersonID,LastName"; //comma seperated column names
  String partitionColumn = "PersonID";
  String outputDirectory = "/output/Persons";
  String url = "http://YourSqoopIP:12000/sqoop/";

  
  SqoopClient client = new SqoopClient(url);
  //client.setServerUrl(newUrl);
  //Dummy connection object
  MConnection newCon = client.newConnection(1);

  //Get connection and framework forms. Set name for connection
  MConnectionForms conForms = newCon.getConnectorPart();
  MConnectionForms frameworkForms = newCon.getFrameworkPart();
  newCon.setName("MyConnection");

  //Set connection forms values
  conForms.getStringInput("connection.connectionString").setValue(connectionString);
  conForms.getStringInput("connection.jdbcDriver").setValue("com.mysql.jdbc.Driver");
  conForms.getStringInput("connection.username").setValue(username);
  conForms.getStringInput("connection.password").setValue(password);

  frameworkForms.getIntegerInput("security.maxConnections").setValue(0);

  Status status  = client.createConnection(newCon);
  if(status.canProceed()) {
   System.out.println("Created. New Connection ID : " +newCon.getPersistenceId());
  } else {
   System.out.println("Check for status and forms error ");
  }

  //Creating dummy job object
  MJob newjob = client.newJob(newCon.getPersistenceId(), org.apache.sqoop.model.MJob.Type.IMPORT);
  MJobForms connectorForm = newjob.getConnectorPart();
  MJobForms frameworkForm = newjob.getFrameworkPart();

  newjob.setName("ImportJob");
  //Database configuration
  connectorForm.getStringInput("table.schemaName").setValue(schemaName);
  //Input either table name or sql
  connectorForm.getStringInput("table.tableName").setValue(tableName);
  //connectorForm.getStringInput("table.sql").setValue("select id,name from table where ${CONDITIONS}");
  
  
  connectorForm.getStringInput("table.columns").setValue(columns);
  connectorForm.getStringInput("table.partitionColumn").setValue(partitionColumn);
  
  //Set boundary value only if required
  //connectorForm.getStringInput("table.boundaryQuery").setValue("");

  //Output configurations
  frameworkForm.getEnumInput("output.storageType").setValue("HDFS");
  frameworkForm.getEnumInput("output.outputFormat").setValue("TEXT_FILE");//Other option: SEQUENCE_FILE / TEXT_FILE
  frameworkForm.getStringInput("output.outputDirectory").setValue(outputDirectory);
  //Job resources
  frameworkForm.getIntegerInput("throttling.extractors").setValue(1);
  frameworkForm.getIntegerInput("throttling.loaders").setValue(1);

  status = client.createJob(newjob);
  if(status.canProceed()) {
   System.out.println("New Job ID: "+ newjob.getPersistenceId());
  } else {
   System.out.println("Check for status and forms error ");
  }
  //Now Submit the Job
  MSubmission submission = client.startSubmission(newjob.getPersistenceId());
  System.out.println("Status : " + submission.getStatus());
 
 }

 
}

Handle IP and IP number in MySQL

Handle IP and IP number in MySQL

You can use int unsigned for storing IP address (like 127.0.0.1)  in MySQL.

But HOW ???

INET_ATON() and INET_NTOA() Functions in MySQL will help you....

SELECT INET_ATON('127.0.0.1');

+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 | 
+------------------------+
1 row in set (0.00 sec)


SELECT INET_NTOA('2130706433');

+-------------------------+
| INET_NTOA('2130706433') |
+-------------------------+
| 127.0.0.1               | 
+-------------------------+
1 row in set (0.02 sec)

Store the converted IP number as int unsigned in MySQL using INET_ATON() and get the exact IP addess by using INET_NTOA()

MySQL persistence connection settings (Change connection Time out values)

MySQL persistence connection settings

1. Start the DB server as 'root' user with  the comandline option and check the values of 'net_read_timeout' and 'wait_timeout'

The result may be like this :

mysql> SHOW SESSION VARIABLES LIKE 'net_read_timeout';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| net_read_timeout | 30    |

+------------------+-------+

1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'wait_timeout';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| wait_timeout | 30    |

+------------------+-------+

1 row in set (0.00 sec)

2. Change the values as you need

Here i am setting 28800 instead of 30

mysql> SET SESSION net_read_timeout=28800;

Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION wait_timeout=28800;

Query OK, 0 rows affected (0.00 sec)

3. Now you can check the variable values updated or not..

mysql> SHOW SESSION VARIABLES LIKE 'net_read_timeout';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| net_read_timeout | 28800 |

+------------------+-------+

1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'wait_timeout';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| wait_timeout | 28800 |

+------------------+-------+

1 row in set (0.00 sec)

4. Now restart your mysql server

If you are using mysql on RedHat Linux (Fedora Core/Cent OS) then use following command:

/etc/init.d/mysqld restart

If you are using mysql on Debian / Ubuntu Linux then use following command:

/etc/init.d/mysql restart

Thats it  !