使用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级别的,很奇怪,只能后面再找找原因

总结

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

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部