Sqoop使用，mysql,hbase,hive等相互转换

Sqoop 是一款用来在不同数据存储软件之间进行数据传输的开源软件，它支持多种类型的数据储存软件。

安装 Sqoop

1.下载sqoop并加mysql驱动包

http://mirror.bit.edu.cn/apache/sqoop/,下载，如下载sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz，

要从 MySQL 导数据需要安装 MySQL driver。如 mysql-connector-java-5.1.38.tar.gz，解压以后把 jar 包放到 Sqoop 目录下的 lib 目录下。

2.配置环境变量

要把HADOOP_MAPRED_HOME添加到系统环境，否则会报错/usr/software/sqoop/bin/../../hadoop/mapreduce does not exist!

需要 vi /etc/profiles加export HADOOP_MAPRED_HOME=/usr/software/hadoop

 

从 MySQL 到 HDFS

mysql测试表 bbb 为例

sqoop import --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --check-column <col> --incremental <mode> --last-value <value> --target-dir <hdfs-dir> --fields-terminated-by '\t'
例子：
sqoop import --connect jdbc:mysql://localhost:3306/test --username root --password admin --table bbb --check-column id --incremental append --last-value '1' --target-dir output_hb --fields-terminated-by '\t'

参数说明：

参数说明：

dburi：数据库的访问连接，例如： jdbc:mysql://192.168.1.124:3306/ 如果您的访问连接中含有参数,那么请用单引号将整个连接包裹住，例如’jdbc:mysql://192.168.1.124:3306/mydatabase?useUnicode=true’
dbname：数据库的名字，例如：user。
username：数据库登录用户名。
password：用户对应的密码。
tablename：MySQL 表的名字。
col：要检查的列的名称。
mode：该模式决定Sqoop如何定义哪些行为新的行。取值为append或lastmodified。
value：前一个导入中检查列的最大值。
hdfs-dir：HDFS 的写入目录，例如：/user/hive/result。
–check-column，用来指定一些列，这些列在导入时用来检查做决定数据是否要被作为增量数据，在一般关系型数据库中，都存在类似Last_Mod_Date的字段或主键。注意：这些被检查的列的类型不能是任意字符类型，例如Char，VARCHAR…（即字符类型不能作为增量标识字段） 
–incremental，用来指定增量导入的模式（Mode），append和lastmodified 
–last-value，指定上一次导入中检查列指定字段最大值
--fields-terminated-by 行分隔符 ，如 '\t'
--lines-terminated-by 列分隔符，hive只支持'\n'作为行分隔符，默认也是'\n'
更加详细的参数使用请参考 Sqoop Import。

View Code

sqoop --split-by详解 

从 HDFS 到 MySQL

需要先创建好对应 HDFS 中的数据结构的 MySQL 表，然后在集群的 Master 节点上执行如下命令，指定要导的数据文件的路径。

1. sqoop export --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --export-dir <hdfs-dir>

从 MySQL 到 Hive

在集群的 Master 节点上执行如下命令后，从MySQL数据库导入数据的同时，也会新建一个 Hive 表。 

1. sqoop import --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --check-column <col> --incremental <mode> --last-value <value> --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --target-dir <hdfs-dir> --create-hive-table --hive-table <hive-tablename>  

报错处理：若报错“Hive does not support the SQL type for column GoodsPic” ,解决:对那列名指定类型，如 --map-column-hive GoodsPic=string

例子：

sqoop import --connect jdbc:mysql://localhost:33065/gznt --username gznt_r --password '123' --table gznt_bmda  --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --target-dir hive_gznt --create-hive-table --hive-table gznt.gznt_bmda

 参数说明：

--hive-table：对应的 Hive 中的表名，可以是 xxx.yyy。

--hdfs-dir：将源数据导入到HDFS上的那个文件夹下

--create-hive-table：没表就自动创建

--hive-overwrite 覆盖同步

从 Hive 到 MySQL

请参考上面的从 HDFS 到 MySQL的命令，只需要指定 Hive 表对应的 HDFS 路径就可以了。

例如：

sqoop export --connect jdbc:mysql://127.0.0.1:3306/parkdb --username xiaoming --password '123' --table t_vip_user --export-dir 'hive_g2park/vip/*' --fields-terminated-by "\t"

--fields-terminated-by指明hdfs数据到mysql的分割凭据

具体如下：hdfs数据到hive中，以及hdfs数据隐身理解

从 MySQL 到 OSS

类似从 MySQL 到 HDFS，只是 --target-dir 不同。在集群的 Master 节点上执行如下命令：

注意1： OSS 地址中的 host 有内网地址、外网地址和 VPC 网络地址之分。如果用经典网络，需要指定内网地址，杭州是 oss-cn-hangzhou-internal.aliyuncs.com，VPC 要指定 VPC 内网，杭州是 vpc100-oss-cn-hangzhou.aliyuncs.com。

注意2： 目前同步到OSS不支持—delete-target-dir，用这个参数会报错Wrong FS。如果要覆盖以前目录的数据 ，可以在调用sqoop前，用hadoop fs -rm -r osspath先把原来的oss目录删了。

1. sqoop import --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --check-column <col> --incremental <mode> --last-value <value> --target-dir <oss-dir> --temporary-rootdir <oss-tmpdir>

参数说明：

oss-dir：OSS 的写入目录，例如：oss://<accessid>:<accesskey>@<bucketname>.oss-cn-hangzhou-internal.aliyuncs.com/result。

oss-tmpdir：临时写入目录。指定append模式的同时，需要指定该参数。如果目标目录已经存在于HDFS中，则Sqoop将拒绝导入并覆盖该目录的内容。采用append模式后，Sqoop会将数据导入临时目录，然后将文件重命名为正常目标目录。

从OSS到MySQL

类似MySQL到HDFS，只是—export-dir不同。需要创建好对应OSS中的数据结构的MySQL表

然后在集群的Master节点上执行如下：指定要导的数据文件的路径

1. sqoop export --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --table <tablename> --export-dir <oss-dir>

参数：

oss-dir:oss的写入目录，例如：oss://<accessid>:<accesskey>@<bucketname>.oss-cn-hangzhou-internal.aliyuncs.com/result

注意： oss地址host有内网地址，外网地址，VPC网络地址之分。如果 用经典网络，需要指定内网地址，杭州是oss-cn-hangzhou-internal.aliyuncs.com,vpc要指定vpc内网，杭州是vpc100-oss-cn-hangzhou.aliyuncs.com

 

使用 SQL 作为导入条件

除了指定 MySQL 的全表导入，还可以写 SQL 来指定导入的数据，如下所示：

1. sqoop import --connect jdbc:mysql://<dburi>/<dbname> --username <username> --password <password> --query <query-sql> --split-by <sp-column> --hive-import --hive-table <hive-tablename> --target-dir <hdfs-dir>
2. 例子：

sqoop import --connect jdbc:mysql://localhost:3306/gznt --username gznt_r --password '123' --query "SELECT NodeCode,NodeName from tb_bmda where 1=1 AND \$CONDITIONS " --split-by NodeCode  --target-dir output2

 参数说明：

query-sql：使用的查询语句，例如：”SELECT * FROM profile WHERE id>1 AND \$CONDITIONS”。记得要用引号包围，最后一定要带上 AND \$CONDITIONS。

sp-column：进行切分的条件,一般跟 MySQL 表的主键。

hdfs-dir：要导到 MySQL 去的 HDFS 的数据目录，例如：/user/hive/result。

hive-tablename：对应的 Hive 中的表名，可以是 xxx.yyy。

 

从MYSQL到HBASE

类似MySQL到HDFS，只是—export-dir不同。需要创建好对应OSS中的数据结构的MySQL表

然后在集群的Master节点上执行如下：指定要导的数据文件的路径

ps:红色可以不填

1. sqoop import -D sqoop.hbase.add.row.key=true --connect jdbc:mysql://localhost:3306/gznt --username gznt_r --password '123' --table t_bmda --columns NodeCode,NodeType,NodeName,IsWarehouse,IsAssetUser --hbase-table hbase_bmda --column-family info --hbase-row-key NodeCode --where "ID >= 5"

参数：

-D sqoop.hbase.add.row.key=true 是否将rowkey相关字段写入列族中，默认false，默认你在列族中看不到任何row key中的字段。

--hbase-create-table  如果hbase中该表不存在则创建,ps：我加了这个，就报错了，据说了版本兼容问题，可以先手动创建好hbase的表和列族

 --hbase-row-key < col > 指定字段作为row key ，如果输入表包含复合主键，用逗号分隔

 

从HIVE到HBASE

hive下建表（hbase下会自动创建对应表）：

CREATE TABLE IF NOT EXISTS all_detail ( 
key string comment "rowkey",
SaleDate varchar(60), 
NodeCode varchar(60),
NodeName varchar(60)
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'    
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:SaleDate,info:NodeCode,info:NodeName")    
TBLPROPERTIES("hbase.table.name" = "hbase_tb");
;

注意点：

1.建表的时候，必须建一个 key column,如上的 key string comment "rowkey"

2.hbase.columns.mapping中是hive与hbase的对应，即 把SaleData,NodeCode,NodeName数据同步到hbase_tb下的info列族下。

附录：

Sqoop使用 
Sqoop 简介及安装部署 
使用Sqoop从MySQL导入数据到Hive和HBase 及近期感悟