Hive中Sqoop的基本用法和常见问题

本文源自：https://blog.csdn.net/onlyoncelove/article/details/81191432

一、通过Sqoop将Hive表数据导入到Mysql

1、第一种是将hive上某张表的全部数据导入到mysql对应的表中。

2、第二种是将hive上某张表中的部分数据导入到mysql对应的表中。

两种方式的区别在于第二种情况需要指定要导入数据的列名称。两种情况的导入方式分别如下：

1.全部导入

Sqoop export --connect jdbc:mysql://127.0.0.1:3306/dbname?useUnicode=true&characterEncoding=utf-8 --username mysql(mysql用户名) --password 123456(密码) --table  student(mysql上的表) --export-dir /user/hive/warehouse/tablename(hive上的表) --input-null-string "\\N" --input-null-non-string "\\N" --fields-terminated-by "," --lines-terminated-by '\n'

说明：

dbname?useUnicode=true&characterEncoding=utf-8：设置数据库的编码格式

--export-dir：指定hive表的存储路径，数据存储在HDFS中

--input-null-string "\\N" --input-null-non-string "\\N"：为了避免空值时，没有数据，需要该命令

--fields-terminated-by ","  ：指定行内数据的分隔符

 --lines-terminated-by '\n'：指定行间数据的分隔符

2.部分导入

Sqoop export --connect  jdbc:mysql://127.0.0.1:3306/dbname  --username mysql(mysql用户名) --password 123456(密码) --table  student(mysql上的表) --columns "id,name,age"  --hcatalog-database sopdm(hive上的schema) --hcatalog-table student(hive上的表)

说明：

--columns ：导入表中的部分字段，后面跟指定的列名；

--where ：可以按条件导入数据；

sqoop import \
--connect jdbc:mysql://localhost:3306/sqoop \
--username root --password 123456 \
--table stu \
--mapreduce-job-name FromMySQL2HDFS \
--delete-target-dir \
--fields-terminated-by '\t' \
-m 1 \
--null-string 0 \
--columns "name" \
--target-dir STU_COLUMN_WHERE \
--where 'id<3'

--query ：按照查询条件导入数据，使用--query关键字，就不能使用--table和--columns 

自定义sql语句的where条件中必须包含字符串 $CONDITIONS，$CONDITIONS是一个变量，用于给多个map任务划分任务范 围；

sqoop import \
--connect jdbc:mysql://localhost:3306/sqoop \
--username root --password 123456 \
--mapreduce-job-name FromMySQL2HDFS \
--delete-target-dir \
--fields-terminated-by '\t' \
-m 1 \
--null-string 0 \
--target-dir STU_COLUMN_QUERY \
--query "select * from stu where id>1 and \$CONDITIONS"
                                                         

3.导入分区表到MySQL

从HIVE分区表导入到MySQL，需要依次导入每个分区的数据

sqoop export   \
--connect jdbc:mysql://server74:3306/Server74   \
--username root   \
--password 123456   \
--table dw_pvs_hour \
--hive-partition-key datestr \
--hive-partition-value ‘2017-11-05’ \
--export-dir /user/hive/warehouse/dw_pvs_hour/datestr=2017-11-15/  \
--input-fields-terminated-by '\001'   \
--input-lines-terminated-by '\n'

根据官方文档的说法，---export-dir这个参数是必须的，指定hive表源文件路径后，sqoop回到路径下路径下的文件，文件不是路径否则报错。所以分区表需要单独指定每个分区的目录，分别导入。

 

二、通过Sqoop将MySQL表数据导入到Hive中

1、将mysql当中的数据直接导入到hive当中

sqoop import --connect jdbc:mysql://192.168.1.10:3306/itcast --username root --password 123 --table trade_detail --hive-import --hive-overwrite --hive-table trade_detail --input-null-string "\\N" --input-null-non-string "\\N"  --fields-terminated-by ","   --lines-terminated-by '\n'

sqoop import --connect jdbc:mysql://localhost:3306/test --username root --password 123456 --table person --hive-import --target-dir temp -m 1 --hive-overwrite  --input-null-string "\\N" --input-null-non-string "\\N"  --fields-terminated-by ","   --lines-terminated-by '\n'

sqoop import --connect jdbc:mysql://192.168.7.7:3306/asto_ec_web --username root --password password --table shop_tb  --target-dir /ysf/input/shop_tb --fields-terminated-by ','

使用--hive-import就可以将数据导入到hive中；

hive-overwrite参数：在导入表的时候对表的数据进行覆盖；

fields-terminated-by参数：改变默认的分隔符；

-m 1：表示启动一个map程序，如果数据量比较大可以启动多个，默认启动4个；

--hive-partition-key dt  --hive-partition-value "2018-08-08"：导入数据到指定的分区，即dt='2018-08-08'。

 

三、通过Sqoop将SQLServer表数据导入到Hive中

首先需要在集群机器上安装sqlserver的jdbc包，sqoop的写法如下：

import--driver com.microsoft.sqlserver.jdbc.SQLServerDriver --connect jdbc:sqlserver://10.177.0.164:49376;database=数据库名 --username 用户名--password  密码 --table testtable --null-non-string \\N --null-string \\N --lines-terminated-by \n  --hive-import --hive-overwrite --hive-table database.testtable  --delete-target-dir -m 1 --fields-terminated-by ','

–create-hive-table ：创建目标表，如果有会报错； 

–hive-database：指定hive数据库； 

–hive-import ：指定导入hive（没有这个条件导入到hdfs中）； 

–hive-overwrite ：覆盖源数据； 

–hive-table stu_import :指定hive中表的名字，如果不指定使用导入的表的表名。

--delete-target-dir ：表示删除目标目录，再导入数据；

-m 1：表示启动一个map程序。

 

四、Sqoop同步需要注意的几个常见问题：

1、Sqoop将hive表同步MySQL时，需要先在mysql构建对应的表；将MySQL表同步到Hive时，可以不建表直接同步，但是没有字段说明，一般情况下写好建表语句建表再进行同步。

2、Hive中建表的时候也需要定义fields-terminated-by、lines-terminated-by，Hive表的数据是以文件的形式存储在HDFS上，定义fields-terminated-by非常关键，实际情况中数据之间的分隔有空格、逗号、tab、\001等，如果数据某一列包含了如逗号，那么表的分隔符就不能使用逗号分隔，默认使用‘\001’。如果表的fields分隔使用了逗号，sqoop就会报错。

3、Sqoop的过程中也需要注意字段类型的不同，Hive的常用类型是int、bigint、double、string，而且Hive中不需要定义字段长度；MySQL中一般都会默认定义字段类型及长度，所以在Sqoop数据从hive到mysql的时候就要控制好字段长度、数据类型。特别注意时间格式，在Hive中时间一般用string类型，在时间计算插入hive表的时候最好不要用timestamp，如current_timestamp，考虑用格式化后的年月日时分秒，如当前时间作为create_time则用from_unixtime(unix_timestamp())，这样在mysql中可能用datetime就不会报错。

4、useUnicode=true&characterEncoding=utf-8：设置数据库的编码格式，避免出现中文乱码的问题； --input-null-string "\\N" --input-null-non-string "\\N"：为了避免空值时，没有数据，需要该命令；一般这两句话在写sqoop语句时尽量带上，确保不会出现问题。

(1) –null-string含义是 string类型的字段，当Value是NULL，替换成指定的字符"\\N"； 
(2) –null-non-string 含义是非string类型的字段，当Value是NULL，替换成指定字符"\\N"。

5、在进行数据同步的时候，一定要注意表名是否一致，特别是大小写要留意。Hive建表后表名是不区分大小写的，但是HDFS中的文件名是有大小写的，所以MySQL中一定要对应上大小写，hive建表用的大写MySQL建表则也大写，否则也会报错，导致数据无法同步。