hadoop - sqoop 导出到 sql server 失败

Question

我正在尝试将 250mb 的数据(75 个字符数组列)从 hdfs 导出到 sqlserver。它因以下错误而失败，

Caused by: java.io.IOException: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

然后我通过 "-D sqoop.export.records.per.statement=10" 这个语句连同 sqoop export 一起工作，但速度很慢。加载 250mb 数据需要 15 分钟。

无论如何我们可以提高性能。

下面是实际的 sqoop 命令:

sqoop export -D sqoop.export.records.per.statement=10 --connect 'jdbc:sqlserver://199.198.165.191:1433;username=;password=;database=database' --table Facttable --columns DimDateID,DimQHourID,ETLMergedFileQHourlyNortelID,DimSWVersionID,DimFreqCellRelationID,OSSC_RC,SubNetwork1,SubNetwork2,MeContext,ENodeBFunction,EUtranCellFDD,EUtranFreqRelation,EUtranCellRelation,Time,GmtOffset,ffv,sn,st,vn,cbt,ts,neun,nedn,nesw,mts,gp,sf,pmHoExeAttLteInterF,pmHoExeAttLteIntraF,pmHoExeSuccLteInterF,pmHoExeSuccLteIntraF,pmHoPrepAttLteInterF,pmHoPrepAttLteIntraF,pmHoPrepSuccLteInterF,pmHoPrepSuccLteIntraF,Count_Null,Count_Negative,Count_Threshold,pmHoExeAttLteInterFLb,pmHoExeSuccLteInterFLb,pmHoOscInterF,pmHoOscIntraF,pmHoPrepAttLteInterFLb,pmHoPrepSuccLteInterFLb,pmHoPrepTNotAllowedLteInterF,pmHoPrepTNotAllowedLteIntraF,pmHoTooEarlyHoInterF,pmHoTooEarlyHoIntraF,pmHoTooLateHoInterF,pmHoTooLateHoIntraF,pmHoWrongCellInterF,pmHoWrongCellIntraF,pmHoWrongCellReestInterF,pmHoWrongCellReestIntraF,pmLbQualifiedUe,pmZtemporary36,pmHoExeAttLteIntraFTuneOut,pmHoExeSuccLteIntraFTuneOut --export-dir /Fact_Peg --direct -m 8 --input-fields-terminated-by "," --input-lines-terminated-by "\n";

”

Answer 1

批量插入是最快的方法。目前 SQOOP 和 SQL Server 附带的默认驱动程序不支持批量插入。您可能想尝试来自 DataDirect 的第三方 JDBC5 驱动程序。

https://www.progress.co.uk/sitecore/content/Progress%20Root/Home/support-and-services/evaluation-support/support-matrices/jdbc-xe