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
Post a Comment