Azure Synapse Delta Table Creation and Import Data From ADLS delta lake

Question

We have requirement to load the data from ADLS delta data into synapse table. actually, we are writing the delta format data into ADLS gen2 from databricks. now we want to load the data from ADLS gen2(with delta table) to synapse table delta table. below steps we followed to create table but we are getting issues.

CREATE EXTERNAL FILE FORMAT DeltaFileFormat
WITH (  
     FORMAT_TYPE = DELTA  
);   
CREATE EXTERNAL DATA SOURCE test_data_source
WITH
(     LOCATION = 'abfss://container@storage.dfs.core.windows.net/table_metadata/testtable'
        --,CREDENTIAL = <database scoped credential>
); 
CREATE EXTERNAL TABLE testtable (
     job_id int,
     source_type varchar(10),
     server_name varchar(10),
     database_name varchar(15),
     table_name varchar(20),
     custom_query varchar(100),
     source_location varchar(500),
     job_timestamp datetime2,
     job_user varchar(50)
) WITH (
        LOCATION = 'abfss://targetcontainer@targetstorage.dfs.core.windows.net/table_metadata/testtable',
        data_source = test_data_source,
        FILE_FORMAT = DeltaFileFormat
);
select * from testtable;

while query select statement, below issues throwing exception.

Content of directory on path 'https://container@storage.dfs.core.windows.net_delta_log/.' cannot be listed.

could you please let me know, where exactly needs to put *.json, we are working delta file with parquet? format. — Developer KE, Feb 15 '23 at 07:35
it was only example, I suggest to try: LOCATION = 'abfss://targetcontainer@targetstorage.dfs.core.windows.net/table_metadata/testtable/*.parquet', — Lev Gelman, Feb 15 '23 at 07:47

score 0 · Answer 1 · answered Feb 15 '23 at 10:25

I also tried and getting similar error.

enter image description here

Content of directory on path 'https://container@storage.dfs.core.windows.net_delta_log/.' cannot be listed.

This error message typically occurs when there is no data or delta files present in the _delta_log directory of the specified ADLS Gen2 storage account. The _delta_log directory is created automatically when you create a Delta table, and it contains the transaction log files for the Delta table.

In below code, the delta table files are located at /demodata/ and the external data source for this example is test_data_source18 which contain file path information and credentials. LOCATION the delta table folder or the absolute file itself, which would be located at /demodata/_delta_log/00000000000000000000.json.

CREATE  EXTERNAL  FILE  FORMAT DeltaFileFormat
WITH (
FORMAT_TYPE = DELTA
);

CREATE  EXTERNAL  DATA  SOURCE test_data_source18
WITH
( LOCATION = 'abfss://demo@dlsg2p.dfs.core.windows.net');

CREATE  EXTERNAL  TABLE testtable24(
Id varchar(20),
Name  varchar(20)
) WITH (
LOCATION = '/demodata/',
data_source = test_data_source18,
FILE_FORMAT = DeltaFileFormat
);

select * from testtable24;

Output:

enter image description here

Reference: delta table with PolyBase

Azure Synapse Delta Table Creation and Import Data From ADLS delta lake

1 Answers1