Azure Synapse Serverless. HashBytes: The query references an object that is not supported in distributed processing mode

Question

I am receiving the error "The query references an object that is not supported in distributed processing mode" when using the HASHBYTES() function to hash rows in Synapse Serverless SQL Pool.

The end goal is to parse the json and store it as parquet along with a hash of the json document. The hash will be used in future imports of new snapshots to identify differentials.

Here is a sample query that produces the error:

SELECT HASHBYTES('sha2_256', csvdata.rec)
FROM OPENROWSET(
        BULK 'json/*/*/*/*/*.json.gz',
        DATA_SOURCE = 'landingzone',
        FORMAT = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
) with (rec nvarchar(max)) as csvdata;

This example also produces the error:

SELECT HASHBYTES('sha2_256', '{"mydoc": {"key":"value"}}')
FROM OPENROWSET(
        BULK 'json/*/*/*/*/*.json.gz',
        DATA_SOURCE = 'landingzone',
        FORMAT = 'csv',
        fieldterminator ='0x0b',
        fieldquote = '0x0b'
) with (rec nvarchar(max)) as csvdata;

This example produces a hash as expected:

SELECT HASHBYTES('sha2_256', '{"mydoc": {"key":"value"}}');

I also receive the error if I first create an external table and use the hashbytes() function when querying from the external table.

Thank you in advance for your advice.

Answer 1

Jason, I'm sorry, hashbytes() is not supported against external tables.