15. Create External File Format in Azure Synapse Analytics

[@sayakchatpad]

Supported File Formats 2023 May:
Serverless SQL pool: Delimited/CSV, Parquet, and Delta Lake
Dedicated SQL pool: Parquet (preview)

[@himanshutrivedi4956]

Wonderful and please create external file csv format as well for better understanding.. Thanking you in advance Rockstar..

[@WafaStudies]

In our upcoming videos we will do that too.

[@deepak0417]

Very nicely done.

[@WafaStudies]

Thank you 😊

[@dperezc88]

Can I use a Delta file to create an external table on a Synapse SQL Serverless pool? Delta doesn't appear as a supported file format on the Microsoft documentation for CREATE EXTERNAL FILE FORMAT (TSQL), but Delta make use of Parquet under the hood, so i wonder if there is a way to use it.

[@ranjansrivastava9256]

--create delta file format in Serverless SQL Pool
CREATE EXTERNAL FILE FORMAT DeltaFormat
WITH
(
FORMAT_TYPE = DELTA
)
--create external table
CREATE EXTERNAL TABLE WebTelemetry
(
UserID varchar(20),
EventType varchar(100),
ProductID varchar(100),
URL varchar(100),
Device varchar(50),
SessionViewSeconds int,
EventYear int,
EventMonth int,
EventDate date,
FilePathYear varchar(10),
FilePathMonth varchar(10),
FilePathDate varchar(10)
)
WITH (
LOCATION = 'your file location',
DATA_SOURCE = ExternalDataSourceDataLake,
FILE_FORMAT = DeltaFormat
)
GO

[@rk-ej9ep]

Awesome 😎 sir..

[@WafaStudies]

Thank you ☺️

[@tirumalasandeepthota1471]

is it compulsory to use compression, any corns and pros

[@michaels1813]

Does create external file format create the physical file?

[@ArunKumar-bh6gd]

Can you elaborate external file means...?

[@WafaStudies]

Its external file format. That means an object which defines format of your file like parquet or delimited text. Now question is what is the use of it? U will understand that when i explain and create external tables in up coming videos.

[@ArunKumar-bh6gd]

Thank you maheer

[@rajunaik8803]

Just a observation - If we create a External table by taking structure from ADLS file, then the External Data Source and Format will be created automatically.

[@sonamkori8169]

Thank You

[@WafaStudies]

Welcome

[@Weallgoingtodiesomeday]

it's possible now to create a delta file format :
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
FORMAT_TYPE = DELTA
);