1

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    
) ;
Sauron
  • 6,399
  • 14
  • 71
  • 136
  • Can you please try set the source column type String in Mapping settings? – Leon Yue Feb 02 '21 at 02:02
  • 2
    Can you try changing the 5th column to a decimal everywhere since it has decimals in the file? If that’s not it try removing one bigint column at a time from the mapping until it works so you can identify the culprit. My wager is on the 5th column. – GregGalloway Feb 02 '21 at 11:34
  • @GregGalloway that was it, been so long since I had to do this, very frustrating since the external table worked immediately...thank you – Sauron Feb 02 '21 at 17:32

1 Answers1

1

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!

Pratik Somaiya
  • 695
  • 5
  • 18