Azure SQL support for Delta tables

Question

I am using Azure SQL with files in Delta-Parquet format stored in a Azure Data Lake Storage and also in a regular Storage account.

Does Azure SQL support Delta external tables? I looked at Fabric, Azure Data Explorer documentation and they are both supporting it.

For SQL Server, the documentation is not clear, some features only apply to certain versions, also depending on the nature of the managed service you are using.

I tried both these syntax and they don't seem to work.

CREATE EXTERNAL FILE FORMAT file_format_name WITH (FORMAT_TYPE = DELTA);

SELECT * FROM OPENROWSET(BULK '/Contoso', FORMAT='DELTA', DATA_SOURCE='Delta_ED');

• https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql
• https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql

If I stick to Azure SQL, should I just ingest the Delta files as tables with a scheduled script reading Delta and inserting in SQL Server?

Answer 1

Azure SQL Database does not support Parquet Delta tables. However, Azure Synapse serverless pool can help you read those parquet delta files and insert them on Azure SQL database.

INSERT INTO YourAzureSQLTable (PickupYear, passenger_count, cnt)
SELECT
        YEAR(pickup_datetime) AS year,
        passenger_count,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'yellow',
        DATA_SOURCE = 'DeltaLakeStorage',
        FORMAT='DELTA'
    ) nyc
WHERE
    nyc.year = 2017
    AND nyc.month IN (1, 2, 3)
    AND pickup_datetime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passenger_count,
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime),
    passenger_count;