Unleashing the Power of Azure Synapse with External Table

In this article, we will learn how to create an external table in Dedicated SQL Pool and Serverless SQL Pool.

Azure Synapse Architecture

How do create an External Table in Serverless SQL Pool?

# create database

DROP DATABASE appdb;

CREATE DATABASE appdb;

# Change to connect to: built-in and db name: appdb

CREATE master key ENCRYPTION by PASSWORD=’password@123';

# create a database for scope CREDENTIAL

CREATE DATABASE SCOPED CREDENTIAL SasToken with IDENTITY =’SHARED ACCESS SIGNATURE’, SECRET = ‘SECRETKEYNAME’

#create external data SOURCE

CREATE EXTERNAL DATA SOURCE log_data with (LOCATION =’https://{container_name}.dfs.core.windows.net/nidhifs', CREDENTIAL=SasToken)

SELECT * from log_data;

#read file FORMAT

CREATE EXTERNAL file FORMAT textfileformat with(FORMAT_TYPE=DELIMITEDTEXT, FORMAT_OPTIONS( FIELD_TERMINATOR=’,’, FIRST_ROW=2))

# create an external table

create EXTERNAL table ext_tab1 ( id int null,correlationid VARCHAR(200) null,operationname VARCHAR(200) null,status varchar(100) null,eventcategory varchar(100) null,level VARCHAR(100) null,time DATETIME null,subscription VARCHAR(200) null,resourcetype varchar(1000) null,eventiniatiatedby varchar(1000) null,resourcegroup varchar(1000) null) with (location=’Log.csv’,DATA_SOURCE = log_data,FILE_FORMAT=textfileformat)

select * from ext_tab1;

How to create an External Table in a Dedicated SQL Pool?

# Change to connect to: built-in and db name: appdb

CREATE master key ENCRYPTION by PASSWORD=’password@123';

# create a database for scope CREDENTIAL

CREATE DATABASE SCOPED CREDENTIAL azurestoragecred with IDENTITY =’nidhistg’,SECRET = ‘SECRET KEY NAME’;

# create external data SOURCE

create EXTERNAL DATA SOURCE log_data with (LOCATION =’abfss://nidhifs@{container_name}.dfs.core.windows.net’,CREDENTIAL=azurestoragecred,Type =HADOOP)

select * from log_data;

# read file FORMAT

create EXTERNAL file FORMAT textfileformat with(FORMAT_TYPE=DELIMITEDTEXT,FORMAT_OPTIONS(FIELD_TERMINATOR=’,’,FIRST_ROW=2))

drop EXTERNAL data source log_data;

# create an external table

create EXTERNAL table ext_tab1( id int null,correlationid VARCHAR(200) null,operationname VARCHAR(200) null,status varchar(100) null,eventcategory varchar(100) null,level VARCHAR(100) null,time DATETIME null,subscription VARCHAR(200) null,resourcetype varchar(1000) null,eventiniatiatedby varchar(1000) null,resourcegroup varchar(1000) null) with (location=’Log.csv’,DATA_SOURCE = log_data,FILE_FORMAT=textfileformat)

select * from ext_tab1;

Thanks for the read🙂.Do clap👏👏 if find it useful.

“Keep learning and keep sharing knowledge”