question

sakuraime avatar image
0 Votes"
sakuraime asked stanleycruvinel edited

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

when creating External table with delta lake , is FORMAT_TYPE = DELTA correct ?
129473-image.png






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 .

azure-synapse-analytics
image.png (14.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @sakuraime,

Just checking in to see if the below answer provided by @ThomasBoersma helped. And, if you have any further query do let us know.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.

0 Votes 0 ·

1 Answer

ThomasBoersma avatar image
0 Votes"
ThomasBoersma answered stanleycruvinel edited

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).

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks for your information

iopenrowset support partitioned parquet wildcard like blobpath/year=/month= ???

0 Votes 0 ·

Yes for parquet it is possible to make use of wildcards inside the BULK command, like: blobpath/year=*/month=*/.
With Delta Lake the partition are made in root folder of the data. See also the docs for use of wildcards, Delta Lake partitions and partition in query-specific-files.


Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

0 Votes 0 ·

Hi @ThomasBoersma,
thanks for answer.

How can I use "time travel" from delta format in one external table (Version or Timestamp)?

0 Votes 0 ·