Synapse external table see all records from delta lake table

Question

Synapse external table see all records from delta lake table

Anonymous

Hi,

I have an delta lake table in ADLS, if I create view with delta format, I only see the last version which has 4 records; however if I create the external table (even with delta format), I see all the records from all the versions.

Samara Soucy - MSFT 5,141 Reputation points

2021-06-14T21:38:40.35+00:00
Could you share a little bit more about how you are creating the tables? If I created the CETAS table with the delta format I got the expected result- only the most recent data. If I created it from with parquet format then I did get all versions of the data.

This is my test script:

CREATE EXTERNAL TABLE dbo.deltaCetasTest WITH ( LOCATION = 'deltaCetasTest/', DATA_SOURCE = <source>, FILE_FORMAT = snappy ) AS SELECT * FROM OPENROWSET(BULK 'abfss://<container>@<account>.dfs.core.windows.net/<path>', FORMAT = 'delta') AS f; SELECT * FROM dbo.deltaCetasTest;

3 answers

Your answer

Samara Soucy - MSFT 5,141 Reputation points

2021-06-14T21:38:40.35+00:00

Could you share a little bit more about how you are creating the tables? If I created the CETAS table with the delta format I got the expected result- only the most recent data. If I created it from with parquet format then I did get all versions of the data.

This is my test script:

CREATE EXTERNAL TABLE dbo.deltaCetasTest WITH ( LOCATION = 'deltaCetasTest/', DATA_SOURCE = <source>, FILE_FORMAT = snappy ) AS SELECT * FROM OPENROWSET(BULK 'abfss://<container>@<account>.dfs.core.windows.net/<path>', FORMAT = 'delta') AS f; SELECT * FROM dbo.deltaCetasTest;

Answer 1

I use the wizard from azure as below,

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseParquetFormat')
CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat]
WITH ( FORMAT_TYPE = DELTA)
GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'lake_vyadls_dfs_core_windows_net')
CREATE EXTERNAL DATA SOURCE [lake_vyadls_dfs_core_windows_net]
WITH (
LOCATION = 'https://vyadls.dfs.core.windows.net/lake',
)
Go

CREATE EXTERNAL TABLE lob.customer (
[id] smallint,
[name] varchar(8000),
[etltime] datetime2(7),
[city] varchar(8000)
)
WITH (
LOCATION = 'customer',
DATA_SOURCE = [lake_vyadls_dfs_core_windows_net],
FILE_FORMAT = [SynapseParquetFormat]
)
GO

Answer 2

Anonymous

can you please let me know what is the proper way to create external table for delta lake? Thanks!

Samara Soucy - MSFT 5,141 Reputation points

2021-06-18T02:46:48.07+00:00

II'm not seeing anything wrong with your code, and using it I'm still having trouble replicating your issue. I've sent you a private message to gather some information so I can set you up with a one-time free support request. I appreciate your patience while working through this problem.

Answer 3

Anonymous

Thanks for checking on this, I have sent you email about my subscription id to you by email.

Samara Soucy - MSFT 5,141 Reputation points

2021-06-18T15:10:46.367+00:00

Thank you. I've responded with instructions for you.

Share via

Synapse external table see all records from delta lake table

3 answers

Your answer