CODEWITHSUNDEEP
azure-data-factoryazure-synapsepolybase
Sauron
Sauron

Reputation: 6657

Cannot ADF polybase file from Azure Storage to Synapse Pool (SQL datawarehouse)

How can I get ADF copy polybase to to simply copy one file from Azure storage to Synapse pool (SQL datawarehouse)

I consistently get this error:

Operation on target Storage to Synapse failed: ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type NVARCHAR to BIGINT.,Source=.Net SqlClient Data Provider,SqlErrorNumber=107090,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=107090,State=1,Message=HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type NVARCHAR to BIGINT.,},],'

summary: HadoopSqlException: Error converting data type NVARCHAR to BIGINT.,},],

This is a sample of what my file looks like, gzip compression:

6872602179|359154818|84154822|1|37.00|68629.82|0.08|0.06|A|F|1992-09-01|1992-08-18|1992-09-05|TAKE BACK RETURN|REG AIR|efully. quickly bold deposits wake alo
6872602179|315023025|90023035|2|22.00|20509.94|0.02|0.05|A|F|1992-10-01|1992-07-06|1992-10-08|DELIVER IN PERSON|FOB| furious, pen
6872602179|591976355|41976366|3|41.00|57472.16|0.07|0.00|A|F|1992-07-23|1992-08-06|1992-08-18|TAKE BACK RETURN|AIR|have to are about the final instructions. 
6872602179|1346584608|46584609|4|37.00|60135.36|0.00|0.05|R|F|1992-09-06|1992-07-23|1992-10-01|TAKE BACK RETURN|FOB|ackages according to the r
6872602179|47498972|97498973|5|31.00|61026.60|0.02|0.03|R|F|1992-06-05|1992-07-21|1992-06-07|NONE|REG AIR|ests cajole slyly quickly special attai
6872602179|315209731|90209741|6|31.00|56574.38|0.06|0.05|R|F|1992-06-15|1992-07-27|1992-07-15|COLLECT COD|SHIP|y bold instructions. even p

source dataset settings in ADF: enter image description here

ADF copy settings:

source:

enter image description here

sink:

enter image description here

mapping:

enter image description here

destination table schema:

CREATE  table dbo.[lineitem](
    [L_OrderKey] [bigint] NULL,
    [L_PartKey] [bigint] NULL,
    [L_SuppKey] [bigint] NULL,
    [L_LineNumber] [bigint] NULL,
    [L_Quantity] [bigint] NULL,
    [L_dboendedPrice] [decimal](13, 2) NULL,
    [L_Discount] [decimal](13, 2) NULL,
    [L_Tax] [decimal](13, 2) NULL,
    [L_ReturnFlag] [varchar](64) NULL,
    [L_LineStatus] [varchar](64) NULL,
    [L_ShipDate] [datetime] NULL,
    [L_CommitDate] [datetime] NULL,
    [L_ReceiptDate] [datetime] NULL,
    [L_ShipInstruct] [varchar](64) NULL,
    [L_ShipMode] [varchar](64) NULL,
    [L_Comment] [varchar](64) NULL    
) ;

Upvotes: 1

Views: 1184

Answers (1)

Pratik Somaiya
Pratik Somaiya

Reputation: 733

I would recommend to debug this issue by keeping only 1 BigInt column at the beginning in your polybase table, if that succeeds that means there's no data in column 1 which is having this issue, for second debug run keep 2 BigInt columns only and then check.

This way you will be able to know which actual column has the issue and then you can check your file for any data issues.

Let me know if that helps !

Thanks!

Upvotes: 1

Related Questions