Azure Data Lake External Data Source: Row size is too big

Question

I tried reading from an external Azure Sql Db both with CREATE EXTERNAL TABLE xyz ....FROM LOCATION "dbo.xyz" and SELECT * FROM ExternalDataSource EXECUTE @"SELECT a,b,c FROM dbo.xyz WHERE DATALENGTH(a)<128000" approaches and getting a Row size too big (A row from remote data source is larger than 4194304 bytes.) error. When I try to locate the rows that are larger than this value, the query inspired by Jaime's answer here does not produce any results.

I tried limiting the rowset by executing a remote query to fetch rows with rowsize of less than 4MB and also columns with large strings to have a datalength of less than 128KB but I am still getting the same error.

I was under the impression that if I executed the remote query on Sql Db side to limit the rowset to what Azure Data Lake can accommodate, I'd be ok but it doesn't seem so. Is there a reasonable workaround I can apply?

Cheers!

Answer 1

First, do you observe the error even with the remote EXECUTE example?

Secondly, what are the REMOTABLE_TYPES that you set on your ExternalDataSource?

Third, it would probably be best to send us a link to the job for further investigations. Feel free to contact us via usql (at) Microsoft.