Foreign Catalog Wrong Mapping - Azure SQL Database Binary Column

[RobsonNLPT]

Hi all.

I've used foreign catalog attached to azure sql databases and never had problems except in 2 situations:

1) Foreign Catalogs don't support sql schemas/objects like [xxxx.yyyy].tablename. The workaround is creating views on sql database

2) This is new issue: When I try to read a table from a foreign catalog, specifically a Binary column serialized as base64(a representation of a zip file) the resultset returns a truncated and wrong value and then I can't decode.

This is the second issue with data mappings. The first one was trying read a json column from google bigquery  table and now this problem reading sql varbinary column(mapped to binary in databricks).

Using data factory I can read the sql database and copy to parquet files. It works but it should be done using only databricks.

Any help?  

[Isi]

Hello @RobsonNLPT 

One thing that might help to narrow this down: could you check whether the problem occurs for the entire column, or if some of the batches you receive actually contain the full (non-truncated) value?

If some batches are complete but others are cut off, it could mean the connector hit a buffer or batch size limit and decided to truncate the value instead of failing with an out-of-memory error.

You might also try a controlled test with smaller binary values (for example, shorter base64-encoded ZIPs) to see if they come through without issues. If smaller payloads work fine but larger ones get truncated, that would confirm the problem is related to size limits in the federation connector...

I understand that these things can sometimes be frustrating 😕

Hope this helps 😕
Isi