I have a delta table in a directory in a storage account and I am creating an external table in azure synapse using this query
IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDeltaFormat')
CREATE EXTERNAL FILE FORMAT [SynapseDeltaFormat]
WITH ( FORMAT_TYPE = DELTA)
GO
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'cla_cladata_dfs_core_windows_net')
CREATE EXTERNAL DATA SOURCE [cla_cladata_dfs_core_windows_net]
WITH (
LOCATION = 'abfss://[email protected]'
)
GO
CREATE EXTERNAL TABLE cla.ExportDataScores (
[GlobalID] int,
[Name] nvarchar(400),
[Metric] nvarchar(400),
[Score] real
)
WITH (
LOCATION = 'ExportDataScores/',
DATA_SOURCE = [cla_cladata_dfs_core_windows_net],
FILE_FORMAT = [SynapseDeltaFormat]
)
GO
SELECT TOP 100 * FROM cla.ExportDataScores
GO
Running the Select on the external table within Synapse works perfectly.
But when I try to import the data in PowerBI, I can list the tables but when I select a table (to preview) I get the below error.
DataSource.Error: Microsoft SQL: Content of directory on path '/*.*' cannot be listed.
Detalles:
DataSourceKind=SQL
DataSourcePath=my-db.sql.azuresynapse;My DB
Message=Content of directory on path '/*.*' cannot be listed.
ErrorCode=-2146232060
I am using sqlserver connection string and user password to connect to the database. My first thought is that maybe its the user I am using in PowerBI but I also ran the query with the user in Synapse and confirmed from the logs that the query was ran with the user.
I have made sure that my storage account and Synapse has public access.
EDIT: I created a VM on azure and tried to connect to the DB with SSMS and connected successfuly. But on running a query on the table, I get this error.
Msg 13807, Level 16, State 1, Line 1
Content of directory on path '/*.*' cannot be listed.
I have a delta table in a directory in a storage account and I am creating an external table in azure synapse using this query
IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDeltaFormat')
CREATE EXTERNAL FILE FORMAT [SynapseDeltaFormat]
WITH ( FORMAT_TYPE = DELTA)
GO
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'cla_cladata_dfs_core_windows_net')
CREATE EXTERNAL DATA SOURCE [cla_cladata_dfs_core_windows_net]
WITH (
LOCATION = 'abfss://[email protected]'
)
GO
CREATE EXTERNAL TABLE cla.ExportDataScores (
[GlobalID] int,
[Name] nvarchar(400),
[Metric] nvarchar(400),
[Score] real
)
WITH (
LOCATION = 'ExportDataScores/',
DATA_SOURCE = [cla_cladata_dfs_core_windows_net],
FILE_FORMAT = [SynapseDeltaFormat]
)
GO
SELECT TOP 100 * FROM cla.ExportDataScores
GO
Running the Select on the external table within Synapse works perfectly.
But when I try to import the data in PowerBI, I can list the tables but when I select a table (to preview) I get the below error.
DataSource.Error: Microsoft SQL: Content of directory on path 'https://cladata.dfs.core.windows/cla/ExportDataScores/_delta_log/*.*' cannot be listed.
Detalles:
DataSourceKind=SQL
DataSourcePath=my-db.sql.azuresynapse;My DB
Message=Content of directory on path 'https://cla.dfs.core.windows/cla/ExportDataScores/_delta_log/*.*' cannot be listed.
ErrorCode=-2146232060
I am using sqlserver connection string and user password to connect to the database. My first thought is that maybe its the user I am using in PowerBI but I also ran the query with the user in Synapse and confirmed from the logs that the query was ran with the user.
I have made sure that my storage account and Synapse has public access.
EDIT: I created a VM on azure and tried to connect to the DB with SSMS and connected successfuly. But on running a query on the table, I get this error.
Msg 13807, Level 16, State 1, Line 1
Content of directory on path 'https://cladata.dfs.core.windows/cla/ExportDataScores/_delta_log/*.*' cannot be listed.
- Create the view based on the external demo data for Power BI to consume by running the following Transact-SQL (T-SQL) script: Create the view usPopulationView inside the database Demo with the following query: SQL Copy DROP VIEW IF EXISTS usPopulationView; GO CREATE VIEW usPopulationView AS SELECT * FROM OPENROWSET( BULK 'censusdatacontainer/release/us_population_county/year=20*/*.parquet', DATA_SOURCE = 'AzureOpenData', FORMAT='PARQUET' ) AS uspv; – Dileep Raj Narayan Thumula Commented Mar 29 at 3:44
1 Answer
Reset to default 0ERROR: DataSource.Error: Microsoft SQL: Content of directory on path 'https://cladata.dfs.core.windows/cla/ExportDataScores/_delta_log/.' cannot be listed.
This error occurs when the serverless SQL pool is unable to read the Delta Lake transaction log folder.
Content of directory on path 'https://.....core.windows/.../_delta_log/*.json' cannot be listed.
Ensure that the _delta_log folder exists, as you may be querying plain Parquet files that have not been converted to the Delta Lake format.
If the _delta_log folder is present, verify that you have both Read and List permissions on the underlying Delta Lake folders.
Additionally, try reading JSON files directly using FORMAT='csv' and specify your URI in the BULK parameter.
select top 10 *
from openrowset(BULK 'https://.....core.windows/.../_delta_log/*.json',FORMAT='csv', FIELDQUOTE = '0x0b', FIELDTERMINATOR ='0x0b',ROWTERMINATOR = '0x0b')
with (line varchar(max)) as logs
Know more about the Content of Delta Lake transaction log can't be listed
Also consider this issue arises when the user lacks the necessary permissions to query the external table or if a firewall is enabled on the storage network
Know more about the
Storage permissions
If you are using Micrsoft Entra ID create a group in Micrsoft Entra ID, grant it Storage Blob Data Contributor role for managed identity on the storage account where your files present. And also add yourself to the group.
Reference: DataSourceError from PowerBI when trying to access Synapse Serverless View on ADLS2