Sqoop的实际使用

一、简单的测试，查看有哪些数据库

$SQOOP_HOME/bin/sqoop list-databases  \
--connect jdbc:mysql://mini1:3306 \
--username hive \
--password hive
//可以

二、将数据导入到hive

不指定分隔符的话，在hive中列的分隔符是\001，行的分隔符是\n

验证：

$SQOOP_HOME/bin/sqoop import \
--connect  jdbc:mysql://mini1:3306/test \
--username hive \
--password hive \
--table myorder \
--hive-import \
--hive-table myorder1 \
--hive-overwrite \
--split-by oid \
-m 2

查看表的结构信息

CREATE TABLE `myorder1`(
  `oid` int,
  `onum` bigint,
  `pname` string,
  `odate` string)
COMMENT 'Imported by sqoop on 2020/01/13 23:32:15'
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\u0001'
  LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://mini1:9000/user/hive/warehouse/myorder1'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='true',
  'numFiles'='2',
  'numRows'='0',
  'rawDataSize'='0',
  'totalSize'='2734',
  'transient_lastDdlTime'='1578929567')
Time taken: 2.161 seconds, Fetched: 22 row(s)
hive (default)> select * from myorder2;
OK
 

 

当我指定了分隔符以后：

$SQOOP_HOME/bin/sqoop import \
--connect  jdbc:mysql://mini1:3306/test \
--username hive \
--password hive \
--table myorder \
--fields-terminated-by ',' \
--hive-import \
--hive-table myorder2 \
--hive-overwrite \
--null-string '\\N' \
--null-non-string '\\N' \
--split-by oid 

hive (default)> show create table myorder2;
OK
createtab_stmt
CREATE TABLE `myorder2`(
  `oid` int,
  `onum` bigint,
  `pname` string,
  `odate` string)
COMMENT 'Imported by sqoop on 2020/01/14 00:24:37'
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://mini1:9000/user/hive/warehouse/myorder2'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='true',
  'numFiles'='2',
  'numRows'='0',
  'rawDataSize'='0',
  'totalSize'='2780',
  'transient_lastDdlTime'='1578932713')
Time taken: 0.461 seconds, Fetched: 22 row(s)

 

三、将数据导出到mysql

sqoop export \
--connect 'jdbc:mysql://mini1:3306/test?useUnicode=true&characterEncoding=utf8' \
--username hive \
--password hive \
--export-dir '/user/hive/warehouse/myorder2/*' \
--table myresult2 \
--fields-terminated-by ',' \
--input-null-string '\\N' \
--input-null-non-string '\\N' \

 

注意的几个点：

1.最后的\之前都记得要空空格，不然报错,如果import放在行首，那么要写成- -     import 也就是说两个横杠之后要有空格，俺也不懂为啥；  

2.指定字符集，不然汉字变成问号

3.指定分割字段：-split-by   (通常m的值大于1时，就需要指定split-by

4.关于Null，在MySQL中varchar类型的字段如果值为Null，并且不处理的话，在hive中会把它存成字符串null，（mysql中的int字段为Null如果不处理，hive中仍然是Null）HIVE表中默认将NULL存为\N

如果在导入hive的时候不指定--null-string '\\N' \   --null-non-string '\\N' \  ，

那么你select * from t where pname is null；查不出来

写成"select * from t where panme="null"才查的出来，因为hive中会把它存成字符串null，

这个时候，如果在导出的时候指定null转换，那些转换成字符串的那几行根本没有被导出。还没有仔细研究为啥。

如果你在导入的时候，正确的指定了null的转换，但是在导出到MySQL的时候不指定--input-null-string '\\N' \     --input-null-non-string '\\N' \  那么直接报错。

5.如果导入的是分区表：

--hive-partition-key 'dt' \

--hive-partition-value '20200201' \

 

指定列：--columns
sqoop import --connect jdbc:mysql://hadoop01:3306/ywp --driver com.mysql.jdbc.Driver \
--username root --password root --table userinfo -m 1 \
--columns 'name,age' \
--fields-terminated-by '\t' --lines-terminated-by '\n' \
--null-string '\\N' --null-non-string '\\N' \
--target-dir /sqoop/out/05;

指定过滤条件：--where
sqoop import --connect jdbc:mysql://hadoop01:3306/ywp --driver com.mysql.jdbc.Driver \
--username root --password root --table userinfo -m 1 \
--columns 'name,age' --where 'id%2 = 0' \
--fields-terminated-by '\t' --lines-terminated-by '\n' \
--null-string '\\N' --null-non-string '\\N' \
--target-dir /sqoop/out/08;

四、增量导入hive

有两种方式

4.1 query中，使用where进行筛选

当我们用sqoop，并且采用query的时候，我们最好使用双引号，而且如果有where语句，必须加上“\CONDITIONS”，注意有“\”

注意query和--table 不能并存

query 后面如果是单引号，那么要加上$CONDITIONS，如果是双引号，要加上\$CONDITIONS

（在实际运行中\$CONDITIONS 是和map数量有关，比如有200条数据，从0到200，2个map,会将0-100分给一个map，100-200分给另一个map ，这里就变成

................where  ...... and  id <0 and id >=100     

................where  ...... and  id <100 and id >=200     

  ）  这个左右等于符号写的对不对还没有深究,大体是这个意思

sqoop import --connect jdbc:postgresql://192.168.xxx.xxx:5432/xxxxxxdb
–username xxxxxx --password xxxxxxxx
–query "select id,check_code,lastupdatetime,check_num,report_name,json_object_keys(reportinfo) from check_report where reportinfo::Text<>‘null’ and \$CONDITIONS "
–delete-target-dir
–target-dir /user/hive/warehouse/lmj_test.db/mid_yyts_report_analyze
–fields-terminated-by ',' \
--input-null-string '\\N' \
--input-null-non-string '\\N' 
-m 2

4.2 --incremental分为append模式、lastmodified模式

这种方法有坏处：比如我定时在2月2日00:00:00进行数据的导入，想把2月1日的数据导进来，因为程序运行时间耽搁，导致2月2日00:00:00至程序执行起来的这段时间的数据，也就是2月2日产生的数据也被导入到2月1日的分区

 

官网说append方式下，append 用于自增的 id 列（lastmodified 用于更新的日期列），但是我自己动手发现append方式下，也可以通过时间类型增量导入last-value也可以是时间类型（不推荐），ps:last-value对应的那个值是不被导入的，大于的才导入

4.2.1 append模式每次导入数据都会把last-value之后的数据全部导过来，不管是否超过当前时间。

--incremental append \

--checkk-column id \

--last-value 0 \

4.2.2

--incremental lastmodified \

--checkk-column oder-date \

--last-value 0\

这种方式lastmodified模式只会导入last-value 和当前时间之间的数据，大于当前时间不会导入，其他的与append模式无差异。

https://www.cnblogs.com/Alcesttt/p/11432547.html

这种模式下还有个参数

--append \

的作用：把大于last-value时间的数据都导入进来，之前就存在但是后期修改过的数据并不会进行合并，只会当做新增的数据加进来，所以使用--incremental lastmodified  --append有可能导致数据重复的问题

$SQOOP_HOME/bin/sqoop job --create incr1 \
-- import --connect jdbc:mysql://mini1:3306/test \
--username hive \
--password hive \
--table myorder \
--hive-import \
--hive-table myorder2 \
--check-column odate \
--incremental lastmodified \
--last-value '2020-02-12 00:00:00' \
--fields-terminated-by ',' \
--null-string '\\N' \
--null-non-string '\\N' \
--split-by oid \
-m 2

在增量导入的时候，可以借助sqoop job 

--last-value的值会自动更新为上一次的上界的值，注意看一下上次的上界即可。

关于sqoop job

创建：如以上代码所示

查看有哪些job：sqoop job --list

运行sqoop job :sqoop job --exec job名字

删除：sqoop job --delete job名字

 

sqoop导入时删除string类型字段的特殊字符

如果你指定了\n为sqoop导入的换行符，mysql的某个string字段的值如果包含了\n, 则会导致sqoop导入多出一行记录。
有一个选项
-hive-drop-import-delims

 

 Sqoop数据导出一致性问题

使用—staging-table选项，将hdfs中的数据先导入到辅助表中，当hdfs中的数据导出成功后，辅助表中的数据在一个事务中导出到目标表中（也就是说这个过程要不完全成功，要不完全失败）。

为了能够使用staging这个选项，staging表在运行任务前或者是空的，要不就使用—clear-staging-table配置，如果staging表中有数据，并且使用了—clear-staging-table选项,sqoop执行导出任务前会删除staging表中所有的数据。

注意：–direct导入时staging方式是不可用的，使用了—update-key选项时staging方式也不能用。

sqoop export \
--connect url \
--username root \
--password 123456 \
--table app_cource_study_report \
--columns watch_video_cnt,complete_video_cnt,dt \
--fields-terminated-by "\t" \
--export-dir "/user/hive/warehouse/tmp.db/app_cource_study_analysi_${day}" \
--staging-table app_cource_study_report_tmp \
--clear-staging-table \
--input-null-string '\\N' \
--null-non-string "\\N"

2）场景2：多个Map任务时，采用–staging-table方式，仍然可以解决数据一致性问题。
 

Sqoop底层运行的任务是什么

只有Map阶段，没有Reduce阶段的任务。

 

--spalit-by

--split-by <column-name>
默认是主键，假设有100行数据，它会执行那个SELECT * FROM sometable WHERE id >= lo AND id < hi, with (lo, hi)  会分为4次导入（0,250），（250,500），(500,750),(750,1001)

1.split-by 根据不同的参数类型有不同的切分方法，如int型，Sqoop会取最大和最小split-by字段值，然后根据传入的num-mappers来 确定划分几个区域。比如select max(split_by),min(split-by) from得到的max(split-by)和min(split-by)分别为1000和1，而num-mappers（-m）为2的话，则会分成两个区域 (1,500)和(501-1000),同时也会分成2个sql给2个map去进行导入操作，分别为select XXX from table where split-by>=1 and split-by<500和select XXX from table where split-by>=501 and split-by<=1000.最后每个map各自获取各自SQL中的数据进行导入工作。 

2.当split-by不是int型时出现如上场景中的问题。目前想到的解决办法是：将-m 设置称1，split-by不设置，即只有一个map运行，缺点是不能并行map录入数据。（注意，当-m 设置的值大于1时，split-by必须设置字段） 默认情况下，map个数是4，通过-m参数可以改变

3.split-by即便是int型，若不是连续有规律递增的话，各个map分配的数据是不均衡的，可能会有些map很忙，有些map几乎没有数据处理的情况