Sqoop on Ubuntu VM

MySQL:

mysql> USE userdb;

mysql> CREATE TABLE emp(id INT NOT NULL, name VARCHAR(100) NOT NULL, deg VARCHAR(40) NOT NULL, salary INT(100), dept VARCHAR(10) NOT NULL);

mysql> INSERT INTO emp VALUES (1201, ‘gopal’, ‘manager’, 50000, ‘TP’);
mysql> INSERT INTO emp VALUES (1202, ‘manisha’, ‘Proof reader’, 50000, ‘TP’);
mysql> INSERT INTO emp VALUES (1203, ‘khalil’, ‘php dev’, 30000, ‘AC’);
mysql> INSERT INTO emp VALUES (1204, ‘prasanth’, ‘php dev’, 30000, ‘AC’);
mysql> INSERT INTO emp VALUES (1205, ‘kranthi’, ‘admin’, 20000, ‘TP’);

mysql> INSERT INTO emp VALUES (1206, ‘satish p’, ‘grp des’, 20000, ‘GR’);

mysql> ALTER TABLE emp MODIFY dept VARCHAR(10);
mysql> UPDATE emp SET dept=’TP’ WHERE id=1201;
mysql> UPDATE emp SET dept=’TP’ WHERE id=1202;
mysql> UPDATE emp SET dept=’AC’ WHERE id=1203;
mysql> UPDATE emp SET dept=’AC’ WHERE id=1204;
mysql> UPDATE emp SET dept=’TP’ WHERE id=1205;

mysql> CREATE TABLE emp_add(id INT NOT NULL, hno VARCHAR(30), street VARCHAR(40), city VARCHAR(100));

mysql> INSERT INTO emp_add VALUES (1201, ‘288A’, ‘vgiri’, ‘jublee’);
mysql> INSERT INTO emp_add VALUES (1202, ‘108I’, ‘aoc’, ‘sec-bad’);
mysql> INSERT INTO emp_add VALUES (1203, ‘144Z’, ‘pgutta’, ‘hyd’);
mysql> INSERT INTO emp_add VALUES (1204, ’78B’, ‘old city’, ‘sec-bad’);
mysql> INSERT INTO emp_add VALUES (1205, ‘720X’, ‘hitec’, ‘sec-bad’);

mysql> CREATE TABLE emp_contact(id INT NOT NULL, phno INT(20), email VARCHAR(100));

mysql> INSERT INTO emp_contact VALUES (1201, 2356742, ‘gopal@tp.com’);
mysql> INSERT INTO emp_contact VALUES (1202, 1661663, ‘manisha@tp.com’);
mysql> INSERT INTO emp_contact VALUES (1203, 8887776, ‘khalil@ac.com’);
mysql> INSERT INTO emp_contact VALUES (1204, 9988774, ‘prasanth@ac.com’);
mysql> INSERT INTO emp_contact VALUES (1205, 1231231, ‘kranthi@tp.com’);

mysql> CREATE TABLE employee (id INT NOT NULL PRIMARY KEY, name VARCHAR(100) NOT NULL, deg VARCHAR(40) NOT NULL, salary INT(100), dept VARCHAR(10) NOT NULL);

SQOOP:

In mapred-site.xml:
<property>
<name>mapreduce.framework.name</name>
<value>yarn</value>
</property>

 

Sqoop Import:

$ sqoop import –connect jdbc:mysql://localhost/userdb –username root –password root –table emp –m 1
(Default directory: /user/gudiseva/emp)

$ sqoop import –connect jdbc:mysql://localhost/userdb –username root –password root –table emp –target-dir /sqoop/import/emp –m 1

$ sqoop import –bindir /tmp/sqoop-gudiseva/compile –connect jdbc:mysql://localhost/userdb –username root –password root –table emp –target-dir /sqoop/import/emp –m 1

$ sqoop import –bindir /tmp/sqoop-gudiseva/compile –connect jdbc:mysql://localhost/userdb –username root –password root –table emp –target-dir /sqoop/import/emp –m 1 –incremental append –check-column id –last-value 1205

 

Sqoop Import with Partitioned Data:

$ sqoop import \
-–connect jdbc:mysql://localhost/userdb \
-–username root \
-–password root \
-–table emp \
-–target-dir /sqoop/import/emp \
-–m 2 \
–boundary-query “select 20000, 50000 from salary” \
–columns name, deg

Explanation:
-–table emp -> Imports emp table
–m 2 -> Number of mappers = number of partitions; Each table file is partitioned into 2 files.
–boundary-query “select 20000, 50000 from emp” -> Boundary Query doesn’t have a WHERE Clause.  Here, 20000 = Min value of the salary; 50000 = Max value of the salary;
–columns name, deg -> Imports only 2 columns from the table

 

Verify the imported records
$ hadoop fs -cat /sqoop/import/emp/part-m-*

View the modified or newly added records
$ hadoop fs -cat /sqoop/import/emp/part-m-*1

 

$ sqoop import-all-tables –connect jdbc:mysql://localhost/userdb –username root –password root
(It is mandatory that every table in the database must have a primary key)

 

Sqoop Export:

Copy Sqoop Jars to HDFS:

$ sudo rm -rf mysql-connector-java-5.1.30

$ hadoop fs -mkdir -p /usr/local/sqoop/lib
$ hadoop fs -copyFromLocal /usr/local/sqoop/lib/. /usr/local/sqoop/.
$ hadoop fs -copyFromLocal /usr/local/sqoop/sqoop-1.4.6.jar /usr/local/sqoop/.

$ hadoop fs -rmr /user/gudiseva/emp

$ sqoop export –connect jdbc:mysql://localhost/userdb –username root –password root –table employee –export-dir /sqoop/import/emp

 

Reference:

http://stackoverflow.com/questions/24163800/sqoop-installation-export-and-import-commands

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s