CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NashTech@123';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureCREDENTIAL
WITH IDENTITY = 'khoa',  SECRET = 'ReE0TsZRZ2F32oDWmbZAzZ7QdLjyUJMwp7di3KXlYVFefng9KvCm+bKtMIR+AgmXHKAr+1YpRRSKr7Nz8fp4Cw==';
 CREATE EXTERNAL DATA SOURCE CSV_Sample_data
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://blobcontainer@khoa.blob.core.windows.net/',
     -- <container_name>@<account_name>
    CREDENTIAL = AccessAzureCREDENTIAL
);

/*Create external table*/

-- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (
     FORMAT_TYPE = DELIMITEDTEXT,
     FORMAT_OPTIONS (FIELD_TERMINATOR =',',
           USE_TYPE_DEFAULT = TRUE));

-- LOCATION: path to file or directory that contains the data (relative to HDFS root).
CREATE EXTERNAL TABLE [dbo].[CSV_Sample_data] (
Fullname nvarchar(200)
,Gender nvarchar(7)
,Age nvarchar(7)
,HiredYear nvarchar(7)
,Position nvarchar(100)
,BranchCity nvarchar(50)
)
WITH (LOCATION='/Sample_data_small.csv',
     DATA_SOURCE = CSV_Sample_data,
     FILE_FORMAT = TextFileFormat
);
/* ​Check data*/

select * from [dbo].[CSV_Sample_data]