sqoop的安装与使用

Sqoop是一个用于在hadoop与mysql之间传输数据的工具

Sqoop 环境搭建
(1)上传安装包:sqoop-1.4.6-cdh5.14.2.tar.gz到/opt/software
(2)解压安装包:tar -zxf sqoop-1.4.6-cdh5.14.2.tar.gz -C /opt/install/
(3)创建软连接:ln -s /opt/install/sqoop-1.4.6-cdh5.14.2/ /opt/install/sqoop
(4)配置环境变量:vi /etc/profile
   export SQOOP_HOME=/opt/install/sqoop
   export PATH=$SQOOP_HOME/bin:$PATH
(5)让配置文件生效:source /etc/profile
(6)切换到sqoop根目录下的conf目录，复制并改名配置文件:cp sqoop-env-template.sh sqoop-env.sh
(7)修改配置文件sqoop-env.sh，在文件末尾追加以下内容：
export HADOOP_COMMON_HOME=/opt/install/hadoop
export HADOOP_MAPRED_HOME=/opt/install/hadoop
export HIVE_HOME=/opt/install/hive
export ZOOCFGDIR=/opt/install/zookeeper
export HBASE_HOME=/opt/install/hbase
(8)复制以下文件到 sqoop 的 lib 目录下
   mysql-connector-java-5.1.27-bin.jar
   java-json.jar
   hive-common-1.1.0-cdh5.14.2.jar
   hive-exec-1.1.0-cdh5.14.2.jar
(9)验证 sqoop 配置是否正确：sqoop help
(10)测试 Sqoop 是否能够成功连接数据库：
    sqoop list-databases --connect jdbc:mysql://hadoop101:3306/ --username root --password 123
(11)做快照

连接数据库获取可用的数据库名称
sqoop list-databases \
--connect jdbc:mysql://hadoop101:3306 \
--username root \
--password 123 
连接数据库获取指定数据库中的所有数据表
sqoop list-tables \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--userrname root \
--password 123 
执行导入命令
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--table customers \
--target-dir /data/retail_db/customers \
--num-mappers 1
 从mysql导入指定表中带条件的数据到hdfs  1
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--table orders \
--where 'order_id<500' \
--delete-target-dir \
--target-dir /data/retail_db/orders \
--num-mappers 1
从mysql导入指定表中字段且带条件的数据到hdfs   2
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--table orders \
--where 'order_id<500' \
--columns order_id,order_data,order_customer_id \
--delete-target-dir \
--target-dir /data/retail_db/orders \
--num-mappers 1
从mysql导入指定查询语句的数据到hdfs【注：单双引号的区别，必须有where且以and $CONDITIONS结尾】
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--query 'select * from orders where order_status!="CLOSED" and $CONDITIONS' \
--delete-target-dir \
--target-dir /data/retail_db/orders \
--num-mappers 1
或
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--query "select * from orders where order_status!='CLOSED' and \$CONDITIONS" \
--delete-target-dir \
--target-dir /data/retail_db/orders \
--num-mappers 3 \
--split-by order_id
在sqoop中第二次增量导入【其中last-value是大于的关系】
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/test \
--username root \
--password 123 \
--table student \
--target-dir /data/retail_db/student \
--incremental append \
--check-column id \
--last-value 2 \
--num-mappers 1
# 查看数据
hdfs dfs -cat /data/retail_db/student/*
# 结果
# 增加数据
insert into student values(5,'tim','male'),(6,'jim','male');
# PPT 演示
# 第一次全量导入
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--query "select * from orders where order_date between '2013-07-01' and '2014-04-15' and \$CONDITIONS" \
--delete-target-dir \
--target-dir /data/retail_db/orders \
--num-mappers 3 \
--split-by order_id
# 第二次增量导入
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--table orders \
--incremental append \
--check-column order_date \
--last-value 2014-04-15 \
--target-dir /data/retail_db/orders \
--num-mappers 3 \
--split-by order_id

# 导入mysql数据到hive中
# 启动hive
hiveserver2 &
# 进入客户端
beeline -ujdbc:hive2://hadoop101:10000 -nroot
# 创建数据库
create database retail_db;
# 导入数据
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--table orders \
--where 'order_id<=10' \
--target-dir /data/retail_db/orders \
--delete-target-dir \
--hive-import \
--hive-database retail_db \
--hive-table orders \
--hive-overwrite \
--num-mappers 1
# 在hvie中查看表名称
show tables;
+-----------+--+
| tab_name  |
+-----------+--+
| orders    |
+-----------+--+
# 在hive中查看表结构
desc orders;
+--------------------+------------+----------+--+
|      col_name      | data_type  | comment  |
+--------------------+------------+----------+--+
| order_id           | int        |          |
| order_date         | string     |          |
| order_customer_id  | int        |          |
| order_status       | string     |          |
+--------------------+------------+----------+--+
# 在hive中查看表数据
select * from orders;
+------------------+------------------------+---------------------------+----------------------+--+
| orders.order_id  |   orders.order_date    | orders.order_customer_id  | orders.order_status  |
+------------------+------------------------+---------------------------+----------------------+--+
| 1                | 2013-07-25 00:00:00.0  | 11599                     | CLOSED               |
| 2                | 2013-07-25 00:00:00.0  | 256                       | PENDING_PAYMENT      |
| 3                | 2013-07-25 00:00:00.0  | 12111                     | COMPLETE             |
| 4                | 2013-07-25 00:00:00.0  | 8827                      | CLOSED               |
| 5                | 2013-07-25 00:00:00.0  | 11318                     | COMPLETE             |
| 6                | 2013-07-25 00:00:00.0  | 7130                      | COMPLETE             |
| 7                | 2013-07-25 00:00:00.0  | 4530                      | COMPLETE             |
| 8                | 2013-07-25 00:00:00.0  | 2911                      | PROCESSING           |
| 9                | 2013-07-25 00:00:00.0  | 5657                      | PENDING_PAYMENT      |
| 10               | 2013-07-25 00:00:00.0  | 5648                      | PENDING_PAYMENT      |
+------------------+------------------------+---------------------------+----------------------+--+

# 导入mysql数据到hive分区
# 删除存在的表
drop table if exists orders;
# 执行导入
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--query 'select order_id,order_customer_id,order_status from orders where order_date="2014-07-24" and $CONDITIONS' \
--target-dir /data/retail_db/orders/order_date=2014-07-24 \
--delete-target-dir \
--hive-import \
--create-hive-table \
--hive-table retail_db.orders \
--hive-partition-key order_date \
--hive-partition-value 2014-07-24 \
--num-mappers 1
# 查看是否创建表
show tables;
# 查看表结构
desc orders;
# 查看数据
select * from orders;
# 查看分区
show partitions orders;
+------------------------+--+
|       partition        |
+------------------------+--+
| order_date=2014-07-24  |
+------------------------+--+

# 通过sqoop job实现“自动”增量导入功能
# 创建密码文件
echo -n 123 > mysqlpwd
# 创建新的job
sqoop job \
--create job01 \
-- import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/test \
--username root \
--password-file 'file:///root/mysqlpwd' \
--table student \
--target-dir /data/retail_db/student \
--hive-import \
--hive-database retail_db \
--hive-table student \
--incremental append \
--check-column id \
--last-value 0 \
--num-mappers 1
# 查看job
sqoop job --list
# 执行job，此时为全量导入
sqoop job --exec job01
# 测试在mysql中添加一条新数据
insert into student values(7,'rose','female');
# 执行job，此时为增量导入
sqoop job --exec job01
# 在hive中查看数据
select * from student;

# 导入mysql数据到hbase
# 启到hbase服务
zkServer.sh start
hbase-daemon.sh start master
hbase-daemon.sh start regionserver
# 执行sqoop
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/retail_db \
--username root \
--password 123 \
--table products \
--hbase-create-table \
--hbase-table products \
--hbase-row-key product_id \
--column-family data \
--num-mappers 1
# 打开hbase客户端
hbase shell
# 查看数据
scan 'products'

# 导出hdfs数据到mysql
# 创建mysql表
use test;
create table student2 like student;
# 执行sqoop导入mysql中的student表中数据到hdfs的student表
sqoop import \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/test \
--username root \
--password 123 \
--table student \
--target-dir /data/retail_db/student \
--delete-target-dir \
--num-mappers 1
# 执行sqoop导出hdfs中student表中的数据到mysql的student2表
sqoop export \
--driver com.mysql.jdbc.Driver \
--connect jdbc:mysql://hadoop101:3306/test \
--username root \
--password 123 \
--table student2 \
--export-dir '/data/retail_db/student' \
--num-mappers 1
# 查看数据
select * from student2;