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

Question

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.

Answer

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.

Share via

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

1 answer