Sqoop数据导入导出
实验目的
掌握Sqoop的各种导入,导出操作
实验原理
在导入开始之前,Sqoop使用JDBC来检查将要导入的表。他检索出表中所有的列以及列的SQL数据类型。这些SQL类型(varchar、integer)被映射到Java数据类型(String、Integer等),在MapReduce应用中将使用这些对应的Java类型来保存字段的值。Sqoop的代码生成器使用这些信息来创建对应表的类,用于保存从表中抽取的记录。Sqoop启动的MapReduce作业用到一个InputFormat,他可以通过JDBC从一个数据库表中读取部分内容。
Hadoop提供的DataDriverDB InputFormat能为查询结果进行划分传给指定个数的map任务。为了获取更好的导入性能,查询会根据一个“划分列”来进行划分。Sqoop会选择一个合适的列作为划分列(通常是表的主键)。在生成反序列化代码和配置InputFormat之后,Sqoop将作业发送到MapReduce集群。Map任务将执行查询并将ResultSet中的数据反序列化到生成类的实例,这些数据要么直接保存在SequenceFile文件中,要么在写到HDFS之前被转换成分割的文本。Sqoop不需要每次都导入整张表,用户也可以在查询中加入到where子句,以此来限定需要导入的记录。
(Sqoop数据导入原理图)
Sqoop导出功能的架构与其导入功能非常相似,在执行导出操作之前,Sqoop会根据数据库连接字符串来选择一个导出方法。一般为JDBC。然后,Sqoop会根据目标表的定义生成一个Java类。这个生成的类能够从文本文件中解析记录,并能够向表中插入合适类型的值。接着会启动一个MapReduce作业,从HDFS中读取源数据文件,使用生成的类解析记录,并且执行选定的导出方法。
(Sqoop 数据导出原理图)
实验内容
-
练习使用Sqoop将数据在Mysql与HDFS之间的导入导出。
-
练习使用Sqoop将数据从Mysql中导入到HBase中。
-
练习使用Sqoop将数据在Mysql与Hive之间的导入导出。
实验步骤
现有buyer_log表,存储了买家行为日志,包含四个字段(ID,用户ID,时间,IP,操作类型)
ID 用户ID 时间 IP 操作类型
461 10181 2010-03-26 19:45:07 123.127.164.252 1
462 10262 2010-03-26 19:55:10 123.127.164.252 1
463 20001 2010-03-29 14:28:02 221.208.129.117 2
464 20001 2010-03-29 14:28:02 221.208.129.117 1
465 20002 2010-03-30 10:56:35 222.44.94.235 2
466 20002 2010-03-30 10:56:35 222.44.94.235 1
481 10181 2010-03-31 16:48:43 123.127.164.252 1
482 10181 2010-04-01 17:35:05 123.127.164.252 1
483 10181 2010-04-02 10:34:20 123.127.164.252 1
484 20001 2010-04-04 16:38:22 221.208.129.38 1
本地 --> MySQL
1.在本地新建/data/sqoop2目录用来存放数据文件
2.开启mysql服务
sudo service mysql start
mysql -u root -p
4.在Mysql中创建数据库mydb 并使用
mysql> create database mydb;
mysql> use mydb;
在mydb数据库中创建表record
mysql> create table record (
id varchar(100),
buyer_id varchar(100),
dt varchar(100),
ip varchar(100),
opt_type varchar(100)
);
5.将Linux本地/data/sqoop2/buyer_log里的内容,导入的mydb数据库record表中。
mysql> load data infile '/data/sqoop2/buyer_log' into table record fields terminated by '\t';
注意:执行上面的语句后可能会报以下错误
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决方法:
- 在mysql下输入:
show variables like ‘%secure%’;
- 查看secure_file_priv的路径,将buyer_log文件上传到该路径下,执行:
load data local infile '/var/lib/mysql-files/buyer_log' into table record fields terminated by '\t';
- 查看record表中内容。
mysql> select * from record;
6.使用Sqoop查看Mysql中的数据库(测试Sqoop与Mysql是否连通)
sqoop list-databases --connect jdbc:mysql://localhost:3306/ -username root -P
使用Sqoop查看Mysql中的表 (在jdbc连接字符串中添加了数据库的名称。用于直接访问数据库实例)
sqoop list-tables --connect jdbc:mysql://localhost:3306/mydb -username root -P
Mysql -> HDFS
-
使用Sqoop将Mysql中mydb数据库record表里的数据导入到HDFS/sqoop2目录里(HDFS上的/mysqoop2目录,不需要提前创建)
sqoop import --connect jdbc:mysql://localhost:3306/mydb --username root --password Password123$ --table record -m 1 --target-dir /sqoop2
-
查看HDFS上/sqoop2目录下的文件内容
hadoop fs -cat /sqoop2/part-m-00000
HDFS -> Mysql
-
在Mysql中新建表recordfromhdfs,表结构与record表相同。将HDFS上/sqoop2/part-m-00000里的数据导入到recordfromhdfs表中
-
启动Hadoop进程
-
连接Mysql,并创建表recordfromhdfs
mysql> use mydb mysql> create table recordfromhdfs like record;
-
用Sqoop将HDFS中数据导入到Mysql中
sqoop export --connect jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8 --username root --password Password123$ --table recordfromhdfs --export-dir hdfs://localhost:9000/mysqoop2/part-m-00000
注意:connect后面连接字符串要带上编码方式
characterEncoding=UTF-8
,否则中文数据可能会产生乱码 -
查看Mysql中recordfromhdfs表中内容
mysql> select * from recordfromhdfs;
Mysql -> HBase
-
启动HBase
cd /apps/hbase/bin ./start-hbase.sh
-
输入
hbase shell
,进入HBase命令行模式。 -
用Sqoop将Mysql中数据导入到HBase中,在hbase中新建表user_info,列族baseinfo,使用Sqoop将Mysql中test数据库user表中的数据,导入到HBase中user表中,同时以id为rowkey
sqoop import --connect jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8 --username root --password Password123$ --table record --hbase-table hbaserecord # 指定要导入的HBase中的表名 --column-family mycf # 指定要导入的HBase表的列族 --hbase-row-key dt # 指定mysql中的某一列作为HBase表中的rowkey -m 1
-
在hbase shell下输入
list
查看HBase中的表
-
查看HBase表hbaserecord里的内容
scan '表名'
HBase -> Mysql
暂时无法直接接口实现,需要借助其他途径去处理,比如:HBase=>HDFS=>Mysql或 HDFS=>Hive=>Mysql
Mysql -> Hive
-
启动Hive,在Hive中创建hiverecord表,包含(id,buyer_id,dt,ip,opt_type)五个字段,字符类型均为varchar(100),分隔符为 ‘ , ’
create table hiverecord ( id varchar(100), buyer_id varchar(100), dt varchar(100), ip varchar(100), opt_type varchar(100) ) row format delimited fields terminated by ',' stored as textfile;
-
使用Sqoop将Mysql中record表导入Hive中
sqoop import --connect jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8 --username root --password Password123$ --table record --hive-import --hive-table hiverecord --fields-terminated-by ',' -m 1
-
查看Hive中hiverecord表
select * from hiverecord;
Hive -> Mysql
-
首先在Mysql中创建表recordfromhive
create table recordfromhive like record;
-
使用sqoop将Hive表中数据导出到Mysql表中
sqoop export --connect jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8 --username root --password Password123$ --table recordfromhive --export-dir /user/hive/warehouse/hiverecord/part-m-00000 --input-fields-terminated-by ','
-
导入完成,查看Mysql中recordfromhive表
mysql> select * from recordfromhive;