Create External Table with Multiple CSV's in Azure Blob folder

Question

Hi All

I am trying to create a single external tables in Azure Synapse. Files location is Azure blob, format CSV.
If I connect the external table with file location then it works perfectly but I connect it just folder locations then instead of showing me data from multiple files it returns empty table. There is no error but it is not returning data.

CREATE EXTERNAL TABLE [stage].[TableName]
(
[Columns] Datatype
)
WITH (LOCATION = '/Legacy/WEBXL',
DATA_SOURCE = [BlobStorage],
FILE_FORMAT = [CsvFormatPipeSeperated]
)

Answer 1

Hello @Kumar, Dinesh ,

Welcome to the Microsoft Q&A platform.

(UPDATE): I had tested with the folder contains two csv files.

Here is the sample to create External Table with Multiple CSV's in Azure Blob folder.

-- Create a database master key if one does not already exist, using your own password. This key is used to encrypt the credential secret in next step.  
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo' ;  
      
-- Create a database scoped credential with Azure storage account key as the secret.  
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredentialKumarDinesh  
WITH  
  IDENTITY = 'SHARED ACCESS SIGNATURE',  
  SECRET = 'gv7nVISeXXXXXXXXXXXXXXXXXXXXXXdlOiA==' ;  
  
-- Create an external data source with CREDENTIAL option.  
CREATE EXTERNAL DATA SOURCE MyAzureStorageKumarDinesh  
WITH  
  ( LOCATION = 'wasbs://******@chepra.blob.core.windows.net/' ,  
    CREDENTIAL = AzureStorageCredentialKumarDinesh,  
    TYPE = HADOOP  
  ) ;  
  
--FILE FORMAT  
  CREATE EXTERNAL FILE FORMAT KumarDinesh  
  WITH  
  (  
      FORMAT_TYPE = DELIMITEDTEXT,  
      FORMAT_OPTIONS (FIELD_TERMINATOR=',',  
      FIRST_ROW = 2)  
  );  
  
  -- EXXTERNAL TABLE  
  
  CREATE EXTERNAL TABLE KumarDinesh  
  (  
      ID INT,  
      Name VARCHAR(12),  
      Age INT  
  )  
  WITH (  
      LOCATION = '/csvfiles/',  
      DATA_SOURCE = MyAzureStorageKumarDinesh,  
      FILE_FORMAT = KumarDinesh   
  )  
  
  SELECT * from KumarDinesh  

Proof of completion:

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

You can specify the pattern that the files must satisfy in order to be referenced by the external table. The pattern is required only for Parquet and CSV tables. If you are using Delta Lake format, you need to specify just a root folder, and the external table will automatically find the pattern.

You can create external tables that read data from a set of files placed on Azure storage:

CREATE EXTERNAL TABLE Taxi (  
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2,   
     pickup_datetime DATETIME2,   
     dropoff_datetime DATETIME2,  
     passenger_count INT,  
     trip_distance FLOAT,  
     fare_amount FLOAT,  
     tip_amount FLOAT,  
     tolls_amount FLOAT,  
     total_amount FLOAT  
) WITH (  
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',  
         DATA_SOURCE = nyctlc,  
         FILE_FORMAT = ParquetFormat  
);  

For more details, refer to Azure Synapse Analytics - External table on a set of files.

Hope this helps. Do let us know if you any further queries.

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

• Please accept an answer if correct. 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.

Answer 2

@PRADEEPCHEEKATLA : Hi Pradeep I am not getting any error just the table is returning 0 row. Below is the script I am using, both specifying the csv and just the folder location. Screenshot of output for both the external tables

CREATE EXTERNAL FILE FORMAT Emp
WITH
(
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS (FIELD_TERMINATOR=',',
FIRST_ROW = 2)
);

   -- EXXTERNAL TABLE  
   --Connecting to folder  
   CREATE EXTERNAL TABLE azure  
   (  
       ID INT,  
       Name VARCHAR(12),  
       Age INT  
   )  
   WITH (  
       LOCATION = '/TestDK/' ,  
       DATA_SOURCE = [IrrWreeBlobStorage],  
       FILE_FORMAT = Emp      
   )  
      
   --------------------------------------------  
   --Connecting to file  
   CREATE EXTERNAL TABLE csvazure  
   (  
       ID INT,  
       Name VARCHAR(12),  
       Age INT  
   )  
   WITH (  
       LOCATION = '/TestDK/Emp.csv' ,  
       DATA_SOURCE = [IrrWreeBlobStorage],  
       FILE_FORMAT = Emp      
   )  
      
   SELECT * from azure  
   SELECT * from csvazure![108101-output.png][1]