Creating external table using delta format in Azure Synapse ADLS Gen 2

Question

Hello,

I want to create a external table using the delta format in Azure Synapse. I'm using a ADLS Gen 2 Data Lake. Here is the relevante part of the script I'm using to create the table:

WITH (  
LOCATION = 'test/staging/CaseComment/**',  
DATA_SOURCE = default_datasynapselakedev_dfs_core_windows_net,  
FILE_FORMAT = DeltaLakeFormat  
)  

I get the following error:

External table references 'FILE_FORMAT' that does not exist.

I'm very confused, since due to the docu (https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables) it should be possible to use this file format:

CREATE EXTERNAL TABLE Covid (
date_rep date,
cases int,
geo_id varchar(6)
) WITH (
LOCATION = 'covid', --> the root folder containing the Delta Lake files
data_source = DeltaLakeStorage,
FILE_FORMAT = DeltaLakeFormat
);

But even when I use this script (with the necesarry adjustments) I get the same error.

I would be very gratefull, if you can solve my problem.

Cheers

Lukas

Answer 1

Hi,

Please follow below steps..

CREATE EXTERNAL DATA SOURCE -->

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver16
CREATE EXTERNAL FILE FORMAT -->

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver16
CREATE EXTERNAL TABLE -->

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16

For more details, Kindly check out the following video: https://www.youtube.com/watch?v=L0ShHDtIxLQ

Please let us know for any question.

Thanks

Answer 2

Although the documentation says creating a Delta file format isn't supported in Serverless SQL Pools, I have just run the following SQL successfully on a native Serverless SQL Pools database (not a lake database) and successfully created an external table using the file format

--create delta file format
CREATE EXTERNAL FILE FORMAT DeltaFormat
WITH
(
FORMAT_TYPE = DELTA
)

--create external table
CREATE EXTERNAL TABLE WebTelemetry
(
UserID varchar(20),
EventType varchar(100),
ProductID varchar(100),
URL varchar(100),
Device varchar(50),
SessionViewSeconds int,
EventYear int,
EventMonth int,
EventDate date,
FilePathYear varchar(10),
FilePathMonth varchar(10),
FilePathDate varchar(10)
)
WITH (
LOCATION = 'cleansed/WebTelemetry/WebEvents',
DATA_SOURCE = ExternalDataSourceDataLakeUKMI,
FILE_FORMAT = DeltaFormat
)
GO