Sqoop 导入数据的基本案例

创建表数据脚本（company.sql）

create database company;
use company;
create table company.staff(id int(4) primary key not null auto_increment, name varchar(255), sex varchar(255));
insert into company.staff(name, sex) values(‘Thomas’, ‘Male’);
insert into company.staff(name, sex) values(‘Catalina’, ‘FeMale’);
insert into company.staff(name, sex) values(‘zhangsan’, ‘Male’);
insert into company.staff(name, sex) values(‘lisi’, ‘FeMale’);
insert into company.staff(name, sex) values(‘Thomas’, ‘Male’);
insert into company.staff(name, sex) values(‘Catalina’, ‘FeMale’);
insert into company.staff(name, sex) values(‘zhangsan’, ‘Male’);
insert into company.staff(name, sex) values(‘lisi’, ‘FeMale’);
insert into company.staff(name, sex) values(‘Thomas’, ‘Male’);
insert into company.staff(name, sex) values(‘Catalina’, ‘FeMale’);
insert into company.staff(name, sex) values(‘zhangsan’, ‘Male’);
insert into company.staff(name, sex) values(‘lisi’, ‘FeMale’);
insert into company.staff(name, sex) values(‘Thomas’, ‘Male’);
insert into company.staff(name, sex) values(‘Catalina’, ‘FeMale’);
insert into company.staff(name, sex) values(‘zhangsan’, ‘Male’);
insert into company.staff(name, sex) values(‘lisi’, ‘FeMale’);

mysql中执行 source /opt/module/datas/company.sql

sqoop导入数据脚本

全部导入

bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 123456 \
--table staff \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 2 \
--split-by id \
--fields-terminated-by "\t"

查询导入

bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 123456 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--query 'select name,sex from staff where id <=3 and $CONDITIONS;'

(多个切片需要加上--split-by id)
bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 123456 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 2 \
--fields-terminated-by "\t" \
--split-by id \
--query 'select id,name,sex from staff where $CONDITIONS and  id <=3'

导入指定列

bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 123456 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--columns id,sex \
--table staff

使用sqoop关键字筛选查询导入数据

1)
bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 123456 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--table staff \
--where "name='lisi'"


2)
bin/sqoop import \
--connect jdbc:mysql://hadoop102:3306/company \
--username root \
--password 123456 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--query 'select name,sex from staff where name ="lisi" and $CONDITIONS;'