Sqoop_1


MYSQL
-------------
database:gnr
tables:tab1:emp
mysql> select * from emp;
+------+--------+-------+------+------+
| id   | name   | sal   | sex  | dno  |
+------+--------+-------+------+------+
|  101 | gnr | 56000 | m    |   11 |
|  102 | mahi   | 65000 | f    |   12 |
|  103 | mohan  | 25000 | m    |   13 |
|  104 | varsha | 35000 | f    |   14 |
|  105 | venu   | 23000 | m    |   13 |
|  106 | vani   | 65000 | f    |  113 |
|  107 | vaji   | 95000 | m    |   12 |
+------+--------+-------+------+------+
7 rows in set (0.00 sec)


SQOOP
==========
1.sqoop help    =>list out the sqoop commands
2.sqoop version =>Display version information
3.list-databases:
-----------------
sqoop list-databases --connect jdbc:mysql://localhost --username root --password cloudera

4.list-tables:
----------------
sqoop list-tables --connect jdbc:mysql://localhost/gnr --username root --password cloudera

5.import-all-tables
-------------------
sqoop import-all-tables --connect jdbc:mysql://localhost/gnr --username root --password cloudera -m 1

6.import
--------
sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  -
-table emp -m 1  --append

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1 --target-dir emp1

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1 --target-dir emp1  --delete-target-dir

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp --split-by id -m 2 --target-dir emp2
sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1  --fields-terminated-by '--' --target-dir emp2 --append

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1 -warehouse-dir emp3

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table tab1 -m 1 -warehouse-dir emp3

Columns filter
-------------
sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1  --columns id,name --target-dir emp4

Rows filter
----------------
sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1  --where "id<=104"  --target-dir emp4 --append


Columns and rows filter
--------------------------------
sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1 --columns name,sal --where "id<=104"  --target-dir emp4 --append

Generating new columns
-------------------------
sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1 --columns id,name,sal,sal*0.5  --target-dir emp4 --append

//error
//solution --- query option


sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera   --query 'select id,name,sal,sal*0.5 from emp where $CONDITIONS' -m 1   --target-dir emp4 --append

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera   --query 'select id,name,sal,sal*0.5 from emp where $CONDITIONS and id<=104' -m 1   --target-dir emp4 --append

sqoop import --connect jdbc:mysql://localhost/gnr --username root --
password cloudera   --query 'select id,sum(sal),max(sal) from emp where $CONDITIONS group by id' -m 1   --target-dir emp4 --append


file_formats
----------------
--as-textfile  (default)
--as-sequencefile
--as-parquetfile
--as-avrodatafile


sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1 --target-dir emp2 --append --as-sequencefile

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1 --target-dir emp2 --append --as-parquetfile

sqoop import --connect jdbc:mysql://localhost/gnr --username root --password cloudera  --table emp -m 1 --target-dir emp2 --append --as-avrodatafile

INCREMENTAL IMPORTS
--------------------------------
incremental   append(new insert auto increment id)/lastmodified(date/timestamp)
check-column    colname
last-value    30

mysql> create database gnr;
mysql> use gnr;
mysql> create table samp(id int,name varchar(10),date timestamp default current_timestamp on update current_timestamp);

mysql> insert into samp(id,name) values(10,'aaa'),(20,'bbb'),(30,'cccc');

mysql> select * from samp;

+------+------+---------------------+
| id   | name | date                |
+------+------+---------------------+
|   10 | aaa  | 2017-07-10 04:15:39 |
|   20 | bbb  | 2017-07-10 04:15:39 |
|   30 | cccc | 2017-07-10 04:15:39 |
+------+------+---------------------+




CREATE SQOOP JOB
-------------------
sqoop job --create job1 -- import --connect jdbc:mysql://localhost/gnr  --username root -P --table samp -m 1  --incremental lastmodified --check-column date

LIST OUT JOBS
------------
sqoop job --list

DISPLAY JOB
------------
sqoop job --show job1

EXCUTE A JOB
----------------
sqoop job --excc job1


OUR PROCEDURE
--------------
sqoop job --exec job1 -- --target-dir old

find jar file:/tmp/sqoop-cloudera/compile/c28f9557d9e69fb988ed6fe61717b571/samp.jar

CHECK HDFS
----------
[cloudera@quickstart ~]$ hadoop fs -cat old/part-m-00000

10,aaa,2017-07-10 04:15:39.0
20,bbb,2017-07-10 04:15:39.0
30,cccc,2017-07-10 04:15:39.0

UPDATE OLD RECORDS AND INSERT NEW RECORDS
----------------------------------------------

mysql> insert into samp(id,name) values(40,'DDD'),(50,'EEE');
mysql> update samp set name='NAAA' where id=10;
mysql> update samp set name='NCCCC' where id=30;

mysql> select * from samp;

+------+-------+---------------------+
| id   | name  | date                |
+------+-------+---------------------+
|   10 | NAAA  | 2017-07-10 04:28:37 |
|   20 | bbb   | 2017-07-10 04:15:39 |
|   30 | NCCCC | 2017-07-10 04:28:46 |
|   40 | DDD   | 2017-07-10 04:28:00 |
|   50 | EEE   | 2017-07-10 04:28:00 |
+------+-------+---------------------+
5 rows in set (0.08 sec)


EXCUTING JOB SECOND TIME
-----------------------
sqoop job --exec job1 -- --target-dir new

find jar file:/tmp/sqoop-cloudera/compile/c28e4db18f1c6c50a91e1022ddcada6c/samp.jar

CHECK HDFS
----------
[cloudera@quickstart ~]$ hadoop fs -cat new/part-m-00000

10,NAAA,2017-07-10 04:28:37.0
30,NCCCC,2017-07-10 04:28:46.0
40,DDD,2017-07-10 04:28:00.0
50,EEE,2017-07-10 04:28:00.0




MERGING TWO DATA SETS
------------------------
sqoop merge --class-name samp  --jar-file /tmp/sqoop-cloudera/compile/c28f9557d9e69fb988ed6fe61717b571/samp.jar --new-data new --onto old  --target-dir latest   --merge-key id

FINAL OUTPUT
--------------
[cloudera@quickstart ~]$ hadoop fs -cat  latest/part-r-00000

10,NAAA,2017-07-10 04:28:37.0
20,bbb,2017-07-10 04:15:39.0
30,NCCCC,2017-07-10 04:28:46.0
40,DDD,2017-07-10 04:28:00.0
50,EEE,2017-07-10 04:28:00.0

SECOND WAY
-------------
sqoop import --connect jdbc:mysql://localhost/gnr  --username root -P --table samp -m 1  --incremental lastmodified --check-column date  --merge-key id --target-dir latest1

THIRD WAY
---------
mysql> create table samp1 like samp;
mysql> select * from samp1;

SQOOP EXPORT
------------
[cloudera@quickstart ~]$ hadoop fs -cat old/part-m-00000
10,aaa,2017-07-10 04:15:39.0
20,bbb,2017-07-10 04:15:39.0
30,cccc,2017-07-10 04:15:39.0

[cloudera@quickstart ~]$ hadoop fs -cat new/part-m-00000
10,NAAA,2017-07-10 04:28:37.0
30,NCCCC,2017-07-10 04:28:46.0
40,DDD,2017-07-10 04:28:00.0
50,EEE,2017-07-10 04:28:00.0


sqoop export --connect jdbc:mysql://localhost/gnr  --username root  --password cloudera --table samp1 -m 1 --export-dir old/part-m-00000

NOTE:update-mode allowinsert then update-column must be primary key

sqoop export --connect jdbc:mysql://localhost/gnr  --username root  --password cloudera --table samp1 -m 1 --update-mode allowinsert --update-key id --export-dir new/part-m-00000

FOURTH  WAY
-------------------
export -data1
export-data2

select * from (select * from data1 union all select * from data2)l
      join

(select id,max(date) date from (select * from data1 union all select * from data2)r  group by id)r1 where l.date=r.date;

Comments