Redshift to Azure Data Warehouse CopyActivity Issue - HybridDeliveryException

0
votes

Facts:

-I am running an Azure Data Factory Pipeline between AWS Redshift -> Azure Data Warehouse (since Power BI Online Service doesn't support Redshift as of this posts date)

-I am using Polybase for the copy since I need to skip a few problematic rows. I use the "rejectValue" key and give it an integer.

-I made two Activity runs and got different errors on each run

Issue:

Run no:1 Error

Database operation failed. Error message from database execution : 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=org.apache.hadoop.io.Text cannot be cast to org.apache.hadoop.io.BooleanWritable,Source=.Net SqlClient Data Provider,SqlErrorNumber=106000,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=106000,State=1,Message=org.apache.hadoop.io.Text cannot be cast to org.apache.hadoop.io.BooleanWritable,},],'.

Run No:2 Error

Database operation failed. Error message from database execution : 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= ,Source=.Net SqlClient Data Provider,SqlErrorNumber=106000,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=106000,State=1,Message= ,},],'.

2
azureamazon-redshiftazure-data-factory
The column datatypes were not an issue. That would be quite silly to post on StackOverflow. The issue was with Polybase. So I just removed polybase from all my datafactory json definitions. Easy. - pmod

2 Answers

1
votes

Below is the reply from Azure Data Factory product team:

Like Alexandre mentioned, the error #1 means you have a text valued column on the source Redshift where the corresponding column in SQL DW has type bit. You should be able to resolve the error by making the two column types compatible to each other.

Error #2 is another error from Polybase deserialization. Unfortunately the error message is not clear enough to find out the root cause. However, recently the product team has done some change on the staging format for Polybase load so you should no longer see such error. Do you have the Azure Data Factory runID for the failed job? The product team could take a look.

0
votes

Power BI Online Service does support Redshift, through ODBC and an On-Premises Data Gateway (https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-august-update/). You can install the latter on a Windows VM in Azure or AWS.

Redshift ODBC Drivers are here: http://docs.aws.amazon.com/redshift/latest/mgmt/install-odbc-driver-windows.html

Otherwise, your error indicates that one column of your SQL DW table does not have the expected data type (you probably have a BIT where a CHAR or VARCHAR should be.