关于使用SQOOP抽数到Hive遇到的问题

使用SQOOP抽数到Hive遇到问题

前置条件

1.源端数据库类型为Mysql

2.目标端是Hive库，beeline为1.1.0

3.Hive建表时使用了分桶，并且加入了stored as orc参数，之前这么创建是为了能够实现delete等操作

处理过程

最初想要在Sqoop中使用hcatalog直接建表+抽数据，语句是这样写的：

sqoop import --connect jdbc:mysql://XX.XX.XX.XX:19100/pms_scyw --username root --password ********\
--table t_sb_zwyc_xl --null-string '\\N' --null-non-string '\\N' \
--create-hcatalog-table \
--hcatalog-database test1 \
--hcatalog-table t_sb_zwyc_xl_521 \
--hcatalog-storage-stanza "clustered by (obj_id) into 16 buckets stored as orc TBLPROPERTIES('transactional'='true')"
#查到的资料说hcatalog-storage-stanza参数会在建表的时候自动加入到create语句之后，所以这里这样写

满心期待说能直接把工作做完了，结果就报错了，报错结果如下：

19/05/21 10:03:57 INFO hcat.SqoopHCatUtilities: Executing external HCatalog CLI process with args :-f,/tmp/hcat-script-1558404237184
19/05/21 10:04:00 INFO hcat.SqoopHCatUtilities: FAILED: IllegalArgumentException Decimal precision out of allowed range [1,38]
19/05/21 10:04:00 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: HCat exited with status 64
at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.executeExternalHCatProgram(SqoopHCatUtilities.java:1148)
at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.launchHCatCli(SqoopHCatUtilities.java:1097)
at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.createHCatTable(SqoopHCatUtilities.java:644)
at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.configureHCat(SqoopHCatUtilities.java:340)
at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.configureImportOutputFormat(SqoopHCatUtilities.java:802)
at org.apache.sqoop.mapreduce.ImportJobBase.configureOutputFormat(ImportJobBase.java:98)
at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:259)
at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:692)
at org.apache.sqoop.manager.MySQLManager.importTable(MySQLManager.java:118)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:497)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605)
at org.apache.sqoop.Sqoop.run(Sqoop.java:143)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:218)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:227)
at org.apache.sqoop.Sqoop.main(Sqoop.java:236)

看信息似乎是精度超过了限制，怎么会呢，随即看了看Sqoop生成的建表语句，找到端倪了：

    `dqtz` varchar(6),
    `dszll` decimal(12,4),
    `dxmpyxkid` varchar(42),
    `dycs` decimal(65),

可以看到有个字段的类型是decimal(65)，允许范围只有1-38，这显然超了，这咋办啊，只有求助谷歌大法了，查来查去也只发现这一个帖子：

帖子链接：https://kb.informatica.com/solution/23/pages/64/524288.aspx

原帖给了一种解决方法

Solution
To resolve this issue, we need to ensure that the Precision and the Scale of the Number datatype changes from 0 to a valid value.
To achieve this, add the following arguments in the JDBC parameters of the Oracle connection.
CatalogOptions=0;NumberPrecisionScale=1
Add above to the Connection string of the JDBC connection used for metadata access.
After modifying the JDBC connection string, re-import the metadata and verify if the right precision is getting imported.

意思就是说在JDBC串后面加上“CatalogOptions=0;NumberPrecisionScale=1”参数，可人家用的源库是oracle，我这是mysql，给的方法也不顶用，就很头大，没办法，只有花点时间自己建表然后把数据导进来了。

捯饬了一段时间，表建好了，ok，再抽一下试试看，这次语句变了下：

sqoop import  --connect jdbc:mysql://XX.XX.XX.XX:19100/pms_scyw --username root --password ********\
 --table t_sb_zwyc_xl --null-string '\\N' --null-non-string '\\N'  \
 --fields-terminated-by '~@!'   -m 2 \
 --hive-overwrite \
 --hcatalog-database test1 \
 --hcatalog-table t_sb_zwyc_xl_ljd \
 --verbose ; 

结果又报错了，好在不是和上次同样的错误，看看是什么问题：

ERROR tool.ImportTool: Encountered IOException running import job: org.apache.hive.hcatalog.common.HCatException : 2016 : Error operation not supported : Store into a partition with bucket definition from Pig/Mapreduce is not supported
at org.apache.hive.hcatalog.mapreduce.HCatOutputFormat.setOutput(HCatOutputFormat.java:109)
at org.apache.hive.hcatalog.mapreduce.HCatOutputFormat.setOutput(HCatOutputFormat.java:70)
at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.configureHCat(SqoopHCatUtilities.java:346)
at org.apache.sqoop.mapreduce.hcat.SqoopHCatUtilities.configureImportOutputFormat(SqoopHCatUtilities.java:768)
at org.apache.sqoop.mapreduce.ImportJobBase.configureOutputFormat(ImportJobBase.java:98)
at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:249)
at org.apache.sqoop.manager.SqlManager.importTable(SqlManager.java:665)

舒服了，这次直接是说不支持分桶格式了，查了下，貌似Sqoop暂时还是不支持分桶的表，但是可以通过临时表来进行一下数据的中转，那就先建个临时表，不过这个临时表不加“clustered by (obj_id) into 16 buckets”参数，只在create语句后加上stored as orc TBLPROPERTIES(‘transactional’=‘true’)就行了，随后就是先把数据抽进临时表，在从临时表insert到目标表去就可以了。

一通操作做完以后验证一下表内的数据也是ok的。

虽然目的是达到了，但是发现了一个问题，在目标表中进行查询时，select后面加了limit的话，Hive服务会直接宕机掉，日志也没有ERROR级别的，很奇怪，只能后面再找找原因

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。