How to create external tables in Azure synapse

Siddalinga GK 0 Reputation points
2025-02-04T16:10:16.8566667+00:00

Scenario-I have created views in Azure synapse. I have to create external tables in Azure synapse, but i am getting below error.

Error message-External table location path is not valid. Location provided: 'https://project01sadatalake.blob.core.windows.net/silver/extsales/'

Script;

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Sql@12345';

CREATE DATABASE SCOPED CREDENTIAL cred_project
with IDENTITY ='MANAGED IDENTITY';
-----------------
--External Data source
CREATE EXTERNAL DATA SOURCE source_silver1
with(
    LOCATION='https://project01sadatalake.blob.core.windows.net/silver',
    CREDENTIAL = cred_project
)

CREATE EXTERNAL DATA SOURCE source_gold1
with(
    LOCATION='https://project01sadatalake.blob.core.windows.net/gold',
    CREDENTIAL = cred_project
);
-------------------------

CREATE EXTERNAL FILE FORMAT format_parquet
with(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION='org.apache.hadoop.io.compress.SnappyCodec'
)


create EXTERNAL table gold.extsales
with(
    LOCATION= 'extsales',
    DATA_SOURCE=source_gold1,
    FILE_FORMAT=format_parquet
)
AS
SELECT * FROM gold.sales;



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.
5,167 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.