External table location path is not valid. Location provided:

Question

External table location path is not valid. Location provided:

Bala 26

I am not able to create an external table and the message is not a valid path

However, I confirm that the path exists. Could there be any other issue but the error message is not clear?

https://storagename.blob.core.windows.net/storage-container/path/to/folder

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2022-04-26T09:34:18.767+00:00

Hello @Bala ,

Thanks for the question and using MS Q&A platform.

Could you please share the code snippet which you are trying to create an external table and also share the stack trace or screenshot of the error message which you are experiencing?
Bala 26 Reputation points

2022-04-26T13:38:47.513+00:00

Error message

9:29:43 AM
Started executing query at Line 7
External table location path is not valid. Location provided: 'https://{storageaccount}.blob.core.windows.net/{container}/conformed/dimensions/dimcustomer/01/'
Total execution time: 00:00:01.816

Code to create the external datasource

CREATE EXTERNAL DATA SOURCE ext_source_worldwideimporter WITH (
LOCATION = 'https://{storageaccount}.blob.core.windows.net/{container}/',
CREDENTIAL = WorkspaceIdentity
);

WorkspaceIdentity is 'Managed Identity' which was created along with Synapseworkspace and has 'Storage Blob Contributor' in {storageaccount}
Bala 26 Reputation points

2022-04-26T13:39:20.387+00:00

The query that fails is below.

--Customer
CREATE EXTERNAL TABLE STG.DimCustomer
WITH
(
LOCATION = 'conformed/dimensions/dimcustomer/01',
DATA_SOURCE = ext_source_worldwideimporter,
FILE_FORMAT = ParquetFormat
)
AS
SELECT CAST(ROW_NUMBER() OVER(ORDER BY C.CustomerID) AS INT) AS CustomerKey,
CAST(C.CustomerID AS INT) AS CustomerID,
C.CustomerName,
CC.CustomerCategoryName,
BG.BuyingGroupName,
DM.DeliveryMethodName,
DC.CityName AS DeliveryCityName,
DSP.StateProvinceName AS DeliveryStateProvinceName,
DSP.SalesTerritory AS DeliverySalesTerritory,
DCO.Country AS DeliveryCountry,
DCO.Continent AS DeliveryContinent,
DCO.Region AS DeliveryRegion,
DCO.Subregion AS DeliverySubregion,
CAST('2013-01-01' AS DATE) AS ValidFromDate
FROM LDW.vwCustomers C
LEFT JOIN LDW.vwCustomerCategories CC On CC.CustomerCategoryID = C.CustomerCategoryID
LEFT JOIN LDW.vwCities DC ON DC.CityID = C.DeliveryCityID
LEFT JOIN LDW.vwStateProvinces DSP ON DSP.StateProvinceID = DC.StateProvinceID
LEFT JOIN LDW.vwCountries DCO ON DCO.CountryID = DSP.CountryID
LEFT JOIN LDW.vwBuyingGroups BG ON BG.BuyingGroupID = C.BuyingGroupID
LEFT JOIN LDW.vwDeliveryMethods DM ON DM.DeliveryMethodID = C.DeliveryMethodID
ORDER BY C.CustomerID;

Select query alone works fine which means that views are fine and they are also read from the same external datasource but from different directories under the {container}

2 answers

Your answer

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2022-04-26T09:34:18.767+00:00

Hello @Bala ,

Thanks for the question and using MS Q&A platform.

Could you please share the code snippet which you are trying to create an external table and also share the stack trace or screenshot of the error message which you are experiencing?
Bala 26 Reputation points

2022-04-26T13:38:47.513+00:00

Error message

9:29:43 AM
Started executing query at Line 7
External table location path is not valid. Location provided: 'https://{storageaccount}.blob.core.windows.net/{container}/conformed/dimensions/dimcustomer/01/'
Total execution time: 00:00:01.816

Code to create the external datasource

CREATE EXTERNAL DATA SOURCE ext_source_worldwideimporter WITH (
LOCATION = 'https://{storageaccount}.blob.core.windows.net/{container}/',
CREDENTIAL = WorkspaceIdentity
);

WorkspaceIdentity is 'Managed Identity' which was created along with Synapseworkspace and has 'Storage Blob Contributor' in {storageaccount}
Bala 26 Reputation points

2022-04-26T13:39:20.387+00:00

The query that fails is below.

--Customer
CREATE EXTERNAL TABLE STG.DimCustomer
WITH
(
LOCATION = 'conformed/dimensions/dimcustomer/01',
DATA_SOURCE = ext_source_worldwideimporter,
FILE_FORMAT = ParquetFormat
)
AS
SELECT CAST(ROW_NUMBER() OVER(ORDER BY C.CustomerID) AS INT) AS CustomerKey,
CAST(C.CustomerID AS INT) AS CustomerID,
C.CustomerName,
CC.CustomerCategoryName,
BG.BuyingGroupName,
DM.DeliveryMethodName,
DC.CityName AS DeliveryCityName,
DSP.StateProvinceName AS DeliveryStateProvinceName,
DSP.SalesTerritory AS DeliverySalesTerritory,
DCO.Country AS DeliveryCountry,
DCO.Continent AS DeliveryContinent,
DCO.Region AS DeliveryRegion,
DCO.Subregion AS DeliverySubregion,
CAST('2013-01-01' AS DATE) AS ValidFromDate
FROM LDW.vwCustomers C
LEFT JOIN LDW.vwCustomerCategories CC On CC.CustomerCategoryID = C.CustomerCategoryID
LEFT JOIN LDW.vwCities DC ON DC.CityID = C.DeliveryCityID
LEFT JOIN LDW.vwStateProvinces DSP ON DSP.StateProvinceID = DC.StateProvinceID
LEFT JOIN LDW.vwCountries DCO ON DCO.CountryID = DSP.CountryID
LEFT JOIN LDW.vwBuyingGroups BG ON BG.BuyingGroupID = C.BuyingGroupID
LEFT JOIN LDW.vwDeliveryMethods DM ON DM.DeliveryMethodID = C.DeliveryMethodID
ORDER BY C.CustomerID;

Select query alone works fine which means that views are fine and they are also read from the same external datasource but from different directories under the {container}

Answer 1

Bala 26

It is resolved after calling the Microsoft support team.

Issue was the file path. However, it is not the prefix but the suffix after the storage account name. Looks like Gen 2 storage account should be referred with dfs instead of blob

CREATE EXTERNAL DATA SOURCE ext_source_worldwideimporter WITH (
LOCATION = 'https://{storageaccount}**.dfs.**core.windows.net/{container}/',
CREDENTIAL = WorkspaceIdentity
);

Hope this is updated in the documentation for future users, be consistent to allow one method ( blob works in read but only dfs work for write ) and also change the error message to be more clear.

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2022-05-02T06:42:36.513+00:00

Hello @Bala ,

Glad to know that your issue has been resolved. And thanks for sharing the solution, which might be beneficial to other community members reading this thread.

Answer 2

PRADEEPCHEEKATLA 90,641 Moderator

Hello @Bala ,

Thanks for the question and using MS Q&A platform.

Make sure you are using the correct location path for Azure Storage account - LOCATION = 'https://{storageaccount}.blob.core.windows.net/{container}/'

You may check the supported location path for Azure Storage account:

For more details, refer to CREATE EXTERNAL DATA SOURCE (Transact-SQL).

And also, checkout the MS Q&A thread which addressing similar issue.

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Bala 26 Reputation points

2022-04-27T22:53:10.837+00:00

Though I change the external source to the format that you have highlighted, it changes back to https// only for the CETAS query.

Regardless of HTTPS or WASPS, the views ( read ) is working fine from the same container.
Bala 26 Reputation points

2022-04-28T16:57:42.857+00:00

@PRADEEPCHEEKATLA - Any idea why it is converting to or interpreting as 'https' when the external source is created as 'wasbs' ?

Share via

External table location path is not valid. Location provided:

2 answers

Your answer