Synapse external table see all records from delta lake table

Victor Yang 21 Reputation points
2021-06-13T16:38:59.547+00:00

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.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,253 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Victor Yang 21 Reputation points
    2021-06-14T21:56:22.403+00:00

    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

    0 comments No comments

  2. Victor Yang 21 Reputation points
    2021-06-15T11:11:19.273+00:00

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


  3. Victor Yang 21 Reputation points
    2021-06-18T12:28:04.427+00:00

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