How to create an External Table when the location matches the external data source

Westerfield, Jared T 0 Reputation points
2023-04-26T17:52:26.6066667+00:00

Hey all,

I'm trying to make an external data source pointing to a container in a data lake. Below is my data source create statement.

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (TYPE = HADOOP,
      LOCATION = 'abfss://data@pbasetr.azuredatalakestore.net'
);

The container the data source is pointing at is fill of files, and has no folders. Therefore, I want the root of this data source as the location in my external table. What should I put down for location? I'm getting errors no matter what I put. Here's a sample external table with this test data in mind, with Location left blank.

CREATE EXTERNAL TABLE [dbo].[DimProduct_external]

( [Key] [int] NOT NULL,
  [Name] nvarchar NULL,
  [Description] nvarchar NULL )
WITH
(
    LOCATION='' ,
    DATA_SOURCE = AzureDataLakeStore ,
    FILE_FORMAT = CSV,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
);

Any and all help is greatly appreciated.

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,553 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,301 Reputation points Microsoft Employee
    2023-04-29T14:26:44.97+00:00

    Hi Westerfield, Jared T,

    Thank you for posting query in Micrsoft Q&A Platform.

    For LOCATION use values as '/'. This will take files of format specified from root folder and sub folders. In your case container is your folder and inside which you have only files. So it automatically takes data from all files.

    Please note, schema of data in every file should be same to see expected results.

    Hope this helps. Please let me know if any further queries.


    Please consider hitting Accept Answer button. Accepted answers help community as well.