使用SQOOP抽數(shù)到Hive遇到問題

前置條件

1.源端數(shù)據(jù)庫類型為Mysql

2.目標端是Hive庫，beeline為1.1.0

3.Hive建表時使用了分桶，并且加入了stored as orc參數(shù)，之前這么創(chuàng)建是為了能夠?qū)崿F(xiàn)delete等操作

處理過程

最初想要在Sqoop中使用hcatalog直接建表+抽數(shù)據(jù)，語句是這樣寫的：

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參數(shù)會在建表的時候自動加入到create語句之后，所以這里這樣寫

滿心期待說能直接把工作做完了，結(jié)果就報錯了，報錯結(jié)果如下：

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，這顯然超了，這咋辦啊，只有求助谷歌大法了，查來查去也只發(fā)現(xiàn)這一個帖子：

帖子鏈接：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”參數(shù)，可人家用的源庫是oracle，我這是mysql，給的方法也不頂用，就很頭大，沒辦法，只有花點時間自己建表然后把數(shù)據(jù)導進來了。

捯飭了一段時間，表建好了，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 ; 

結(jié)果又報錯了，好在不是和上次同樣的錯誤，看看是什么問題：

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暫時還是不支持分桶的表，但是可以通過臨時表來進行一下數(shù)據(jù)的中轉(zhuǎn)，那就先建個臨時表，不過這個臨時表不加“clustered by (obj_id) into 16 buckets”參數(shù)，只在create語句后加上stored as orc TBLPROPERTIES(‘transactional’=‘true’) 就行了，隨后就是先把數(shù)據(jù)抽進臨時表，在從臨時表insert到目標表去就可以了。

一通操作做完以后驗證一下表內(nèi)的數(shù)據(jù)也是ok的。

雖然目的是達到了，但是發(fā)現(xiàn)了一個問題，在目標表中進行查詢時，select后面加了limit的話，Hive服務會直接宕機掉，日志也沒有ERROR級別的，很奇怪，只能后面再找找原因

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論