Synapse polybase data ingestion is not working

Question

I have a task to convert the jobs from synapse bulk insert to synapse polybase pattern. As part of that I see that it doesn't work straight away. It is complaining about some datatypes etc as below.... where as there is no double datatypes sometimes in the source query. Please help to understand if there a basic pattern or casting we need to do before we use polybase.

Here the source SQL I used Source table datatypes

SELECT TOP (1000) cast([SiteCode_SourceId] as varchar(1000)) 
[SiteCode_SourceId]
  ,cast([EquipmentCode_SourceId] as varchar(1000)) 
[EquipmentCode_SourceId]
  ,FORMAT([RecordedAt],'yyyy-MM-dd HH:mm:ss.fffffff') AS 
[RecordedAt] 
  ,cast([DataLineage_SK] as varchar(1000)) [DataLineage_SK]
  ,cast([DataQuality_SK] AS varchar(1000)) [DataQuality_SK]
  ,cast([FixedPlantAsset_SK] as varchar(1000)) 
[FixedPlantAsset_SK]
  ,cast([ProductionTimeOfDay_SK] as varchar(1000)) 
[ProductionTimeOfDay_SK]
  ,cast([ProductionType_SK] as varchar(1000)) [ProductionType_SK]
  ,cast([Shift_SK] as varchar(1000)) [Shift_SK]
  ,cast([Site_SK] as varchar(1000)) [Site_SK]
  ,cast([tBelt] as varchar(1000)) [tBelt]
 ,FORMAT([ModifiedAt],'yyyy-MM-dd HH:mm:ss.fffffff') [ModifiedAt]
 ,FORMAT([SourceUpdatedAt],'yyyy-MM-dd HH:mm:ss.fffffff') 
[SourceUpdatedAt]
FROM [ORXX].[public_XX].[fact_FixedXXXX]

Operation on target cp_data_movement failed: parquet.io.api.Binary$ByteArraySliceBackedBinary cannot be cast to class java.lang.Double (parquet.io.api.Binary$ByteArraySliceBackedBinary is in unnamed module of loader 'app'; java.lang.Double is in module java.base of loader 'bootstrap'),},],'

Here is the destination columns and order is same. Destination table columns

Could you share the source data columns along with their data type — Aswin, Feb 15 '23 at 11:38
Okay. Could you edit the question and give the query instead of posting this as an answer? — Aswin, Feb 20 '23 at 06:32
Are you using this query in source dataset and copy the data to datawarehouse table using synapse pipelines? Could you clarify this? — Aswin, Feb 20 '23 at 07:49
Yes I am using this as a source query and adding the data to landing tables in synapse — user237624, Feb 20 '23 at 22:59
@aswwin, I have attached the image from destination and it has same order as the source query. Please help what else can be wrong. — user237624, Feb 20 '23 at 23:35
Are you mapping the source and sink columns manually in mapping settings of copy activity — Aswin, Feb 21 '23 at 10:47

score 0 · Answer 1 · answered Feb 20 '23 at 10:03

enter image description here

Reasons for this error can be,

Order of the columns in the target table is not matching with the source table. So, there will be data type mismatch
Data types in parquet file is Incompatible to target tables' data type.

Solution:

Make sure the order of the columns are same as in parquet staging file.
Keep the same data types in source columns and target columns.

Synapse polybase data ingestion is not working

1 Answers1