Sqoop Basics - Session 1

Using Sqoop command

% Sqoop import --connect jdbc:mysql://localhost/databasename --username $username --password $password --table t1 --m 1

% Sqoop import --connect jdbc:mysql://localhost/testDb --username root --password hadoop123 -table t1 --m 1

Creating  a config file import.txt

import

--connect jdbc:mysql://localhost/databasename

--username root

--password hadoop123

Execute the sqoop import

S    % Sqoop –options-file /home/hduser/import.txt --table student –m 1

 % Hadoop dfs –ls –R student

3 files are generated (_Success, part-m-0000, _logs)

% Hadoop fs –cat /home/hduser/student/part-r-0000

Ø       1,Archana

Ø        2,XYZ

   Import all rows of a table in MySQL, but specific columns of the table

% Sqoop import –connect jdbc:mysql://localhost/testDb --username hduser --password hadoop123 --table student --column “name” –m 1

   

   $ hadoop dfs -cat  /user/hduser/student/part-m-0000

       Archana

       Xyz

Importing multiple mysql tables into 1 hive/hbase table

MySql tables

Table A: “users” , columns: user_name, user_id, user_add, etc

Table B: “customers”, columns : customer_name, customer_Id, customer_add etc

Table C: “employees” , columns: employee_name, employee_id, employee_add etc

Importing into HIVE

Sqoop import –connect  jdbc:mysql:///myDb --username hue --password hue

--query “SELECT * FROM users JOIN customers ON users.user_id=customers.customer_id JOIN employees ON users.user_id=employees.employee_id

where $conditions –split-by oozie_job.id

--target-dir “/tmp/hue”

--hive-import --hive-table tableAll

Importing into HBASE

sqoop imort –connect jdbc:mysql///mydb –username hue –password hue

--query “SELECT 8 FROM user JOIN customers ON users.id=customers.id ON users.id = employees.id

WHERE $CONDITIONS

--SPLIT-BY oozie_job.id

--hbase-table hue –column-family c1