三、Sqoop export讲解
首先在mysql上准备数据表
CREATE TABLE user_info_export ( id int(11) NOT NULL AUTO_INCREMENT, username varchar(20) DEFAULT NULL, password varchar(20) DEFAULT NULL, PRIMARY KEY (id) );
提前在hdfs上准备好数据，我的数据放在/user/kfk/sqoop/export/user_info，默认是逗号隔开，如果hdfs上文件使用’\t’分割，则可以设置参数–fields-terminated-by ‘\t’
将hdfs的数据导入到mysql中
bin/sqoop export \ --connect jdbc:mysql://bigdata-pro-m01:3306/db_sqoop \ --username root \ --password 199911 \ --num-mappers 1 \ --table user_info_export \ --export-dir /user/kfk/sqoop/export/user_info
mysql> select * from user_info_export; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | admin | admin | | 2 | wang | 111111 | | 3 | zhang | 000000 | | 4 | lili | 000000 | | 5 | henry | 000000 | | 6 | cherry | 000000 | | 7 | ben | 111111 | | 8 | leo | 000000 | | 9 | test | test | | 10 | system | 000000 | | 11 | xiao | 111111 | +----+----------+----------+
指定特定的列名
bin/sqoop export \ --connect jdbc:mysql://bigdata-pro-m01:3306/db_sqoop \ --username root \ --password 199911 \ --num-mappers 1 \ --table user_info_export \ --export-dir /user/kfk/sqoop/export/user_info \ --columns 'username,password'
mysql> select * from user_info_export; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | admin | admin | | 2 | wang | 111111 | | 3 | zhang | 000000 | | 4 | lili | 000000 | | 5 | henry | 000000 | | 6 | cherry | 000000 | | 7 | ben | 111111 | | 8 | leo | 000000 | | 9 | test | test | | 10 | system | 000000 | | 11 | xiao | 111111 | | 12 | 1 | admin | | 13 | 2 | wang | | 14 | 3 | zhang | | 15 | 4 | lili | | 16 | 5 | henry | | 17 | 6 | cherry | | 18 | 7 | ben | | 19 | 8 | leo | | 20 | 9 | test | | 21 | 10 | system | | 22 | 11 | xiao | +----+----------+----------+ 22 rows in set (0.00 sec)
四、Sqoop import Hive和Sqoop export Hive
首先在hive上创建数据表
CREATE TABLE user_info ( id int, username string, password string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
将mysql的数据导入到hive表
bin/sqoop import \ --connect jdbc:mysql://bigdata-pro-m01:3306/db_sqoop \ --username root \ --password 199911 \ --num-mappers 1 \ --table user_info \ --fields-terminated-by ',' \ --delete-target-dir \ --target-dir /user/hive/warehouse/db_hive.db/user_info
hive (db_hive)> select * from user_info; OK user_info.id user_info.username user_info.password 1 admin admin 2 wang 111111 3 zhang 000000 4 lili 000000 5 henry 000000 6 cherry 000000 7 ben 111111 8 leo 000000 9 test test 10 system 000000 11 xiao 111111 Time taken: 0.059 seconds, Fetched: 11 row(s)
mysql-table -> /user/hive/warehouse/db_hive.db/user_info
将hive中的数据导出到mysql中
bin/sqoop export \ --connect jdbc:mysql://bigdata-pro-m01:3306/db_sqoop \ --username root \ --password 199911 \ --num-mappers 1 \ --table user_info_export \ --input-fields-terminated-by ',' \ --export-dir /user/hive/warehouse/db_hive.db/user_info
mysql> mysql> select * from user_info_export; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | admin | admin | | 2 | wang | 111111 | | 3 | zhang | 000000 | | 4 | lili | 000000 | | 5 | henry | 000000 | | 6 | cherry | 000000 | | 7 | ben | 111111 | | 8 | leo | 000000 | | 9 | test | test | | 10 | system | 000000 | | 11 | xiao | 111111 | +----+----------+----------+ 11 rows in set (0.00 sec)
五、options-file讲解
出了命令行的方式，我们也可以通过options-file来导入数据：
vim import.txt
import --connect jdbc:mysql://bigdata-pro-m01:3306/db_sqoop --username root --password 199911 --table user_info --delete-target-dir --num-mappers 1 --target-dir /user/kfk/sqoop/import/user_info --fields-terminated-by '\t'
sqoop --options-file import.txt
