Sqoop

SQOOP OVERVIEW

IPv4 Address. . . . . . . . . . . : 192.168.56.1

grant all privileges on *.* to root@192.168.56.1 IDENTIFIED BY 'root' WITH GRANT OPTION;

grant all privileges on *.* to root@192.168.0.115 IDENTIFIED BY 'root' WITH GRANT OPTION;

Working With Virtual Machine 

sqoop import --connect jdbc:mysql://192.168.56.1/sakila --username root --password root --table actor --target-dir /user/cloudera/SQP901 –m1

mysql -u root -p

cloudera       (password)

Working with Internal Mysql...

sqoop import --connect jdbc:mysql://quickstart.cloudera:3306/retail_db --username root --password cloudera --table categories --target-dir /user/cloudera/SQPMYSQL901

--hive-import --create-hive-table --hive-table gustdb.categories;

sqoop import --connect jdbc:mysql://quickstart.cloudera:3306/retail_db--username root --password cloudera--table categories--target-dir /user/cloudera/cu99

--hive-import --create-hive-table --hive-table nitdb.city;

Import Command:

sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username root --password cloudera --table categories --target-dir /user/cloudera/Sqoop-categories -m 1;

localhost:3306: As we are using internal MySQL, 3306 is the portnumber and localhost is the hostname

In place of localhost, we can even use quickstart.cloudera which is the hostname.

retail_db:database present in internal MySQL.

Categories: Table present in retail_db

Sqoop-categories: Target directory where we need to store the data from iinternsl MySQL

·      Syntax for retrieving and storing records based on condition:

sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username root --password cloudera --table customers--where "customer_id='12429'"--target-dir /user/cloudera/sqoop-customers--split-by customer_id -m 1;

sqoop import --connect jdbc:mysql://localhost:3306/retail_db --username root --password cloudera --table customers--where "customer_id='12429'"--target-dir /user/cloudera/CUSTSQP345 -m1;

--split-by

retail_db: database present in internal MySQL.

Customers: Table present in retail_db

--where: For retrieving particular column according to the condition  from the database, --where is used

·      Importing data from external mysql:

sqoop import --connect jdbc:mysql://192.168.56.1/sakila --username root --password root --table actor_info --target-dir /user/cloudera/Shravz-actor -m 1;

sakila: database present in external MySQL

actor_info: table present in database sakila.

Shravz_actor: target directory where we are  saving the imported data.

Export Command:

·      Exporting data into mysql present in environment

gotomysql using "mysql -u root -p"

Create a database:

create database gusto;

Create a table :

create table visit123(name varchar(30), url varchar(20), time varchar(20)) ;

Export Command:

sqoop  export --connect jdbc:mysql://localhost:3306/gusto  --username  root  --password cloudera  --table visit123  --export-dir/user/cloudera/Visits.csv -m  1;

gusto:database which we have created in internal MySQL.

Visit123: table created under the database gusto.

/user/cloudera/Visits.csv: Path from where the data is being exported into mysql.

·      Exporting Data Into External Database:

Create a database:

create database gusto;

Create a table :

create table visit001(name varchar(30), url varchar(20), time varchar(20)) ;

Export command:

sqoop  export  --connect jdbc:mysql://192.168.56.1/gusto --username root --password root --table visit001 --export-dir /user/cloudera/Visits.csv -m 1;

·     Sqoop export syntax for files other than CSV Files(INTERNAL MYSQL):

1.   create table in mysql

create table results(rollno int, grade varchar(30));

2.   Export command using ‘\t’ as delimiter

sqoop export --connect jdbc:mysql://localhost:3306/gusto --username root --password cloudera --table results--input-fields-terminated-by '\t'--export-dir /user/cloudera/RESULTS1 -m 1;

results:table name present in Mysql

gusto : database used by the table

--input-fields-terminated-by '\t':  tab is present as delimeter in the file so, we are using the command.

3.   Verify in Mysql:

select * from results;

·      Exporting Data Into External Database

Command for granting privileges on external mysql:

grant all privileges on *.* to root@192.168.56.1 IDENTIFIED BY 'root' WITH GRANT OPTION;

1.   create table in mysql

create table data1;

2.   Export command using ‘\t’ as delimiter

sqoop export --connect jdbc:mysql://192.168.56.1/vanshu --username root --password root --table data1 --input-fields-terminated-by '\t' --export-dir /user/cloudera/data1 -m 1;

sqoop export --connect jdbc:mysql://192.168.56.1/gustdb --username root --password root --table customers --input-fields-terminated-by '\t' --export-dir /user/cloudera/CUST2299 -m 1;

vanshu: database name

data1: table created in database

/user/cloudera/data1: path where the file is present

3.   Verify in Mysql:

Select * from data1;