Azure Synapse serverless pool External table and openrowset for Delta lake table

Question

when creating External table with delta lake , is FORMAT_TYPE = DELTA correct ?

basically, when I create an external table and also with the above external format_type, which is able to view the results. But not sure why these is a syntax error check .

Answer 1

Hi @sakuraime

The FORMAT_TYPE is DELTA and the LOCATION within the WITH statement is referring to the folder of the Delta Lake with parquet files in it. See the docs for further information.

Here is an example:

CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );  
  
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'DeltaLakeSource')   
 CREATE EXTERNAL DATA SOURCE [DeltaLakeSource]   
 WITH (  
 LOCATION   = 'https://{storage-account-name}.dfs.core.windows.net/{container}',   
 )  
Go  
  
CREATE EXTERNAL TABLE Person(  
 [Id] int,  
 [Firstname] varchar(128),  
 [Lastname] varchar(128),  
 )  
 WITH (  
 LOCATION = '{path-to-delta-lake-folder}',  
 DATA_SOURCE = [DeltaLakeSource],  
 FILE_FORMAT = [DeltaLakeFormat]  
 )  
GO  

If you use the serverless pool of Synapse I suggest that you use views instead of external tables, because Delta Lake partition is currently not working with external tables (see info here).