Sqoop 导入数据的基本案例

最新推荐文章于 2024-10-24 22:35:07 发布

star5610

最新推荐文章于 2024-10-24 22:35:07 发布

阅读量303

点赞数

CC 4.0 BY-SA版权

分类专栏： sqoop 文章标签： sqoop 大数据

本文链接：https://blog.csdn.net/star5610/article/details/106804530

sqoop 专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍了一种从SQL数据库迁移到Hadoop的数据处理流程，包括创建SQL表、插入数据、使用Sqoop工具进行数据导入及筛选操作。通过具体实例展示了如何在不同场景下运用Sqoop的关键字和查询条件来高效地进行数据迁移。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建表数据脚本（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;'