CODEWITHSUNDEEP
hadoophiveapache-pigsqoop
user3350280
user3350280

Reputation: 95

sqoop export to sql server fails

Am trying to export 250mb of data(75 chararray columns) from hdfs to sqlserver. It failed with the below error,

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.

Then i passed "-D sqoop.export.records.per.statement=10" this statement along with sqoop export it worked but it is very slow. It took 15 minutes to load 250mb of data.

Is there anyway we can improve the performence.

Below is the actual sqoop cmd:

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";

"

Upvotes: 1

Views: 1433

Answers (2)

Chris Marotta
Chris Marotta

Reputation: 600

Looking at your sqoop command, you are specifying 8 mappers. First, 8 is probably too many for your DB to handle concurrently. Second, there is no split-by specification for those 8 mappers to divide the export work equally. I would remove the -m 8 parameter and run again. It is only 250mb, depending on your cluster it shouldn't take very long at all.

Upvotes: 0

user3559239
user3559239

Reputation: 11

The bulk inserts is the fastest way. Currently SQOOP and the default drivers that come with it for SQL server do not support bulk inserts. You may want to try a third party JDBC5 drivers from DataDirect.

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

Upvotes: 1

Related Questions