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.
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.
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.
Supported File Formats 2023 May:
Serverless SQL pool: Delimited/CSV, Parquet, and Delta Lake
Dedicated SQL pool: Parquet (preview)
Wonderful and please create external file csv format as well for better understanding.. Thanking you in advance Rockstar..
In our upcoming videos we will do that too.
Very nicely done.
Thank you 😊
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.
--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
Awesome 😎 sir..
Thank you ☺️
is it compulsory to use compression, any corns and pros
Does create external file format create the physical file?
Can you elaborate external file means...?
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.
Thank you maheer
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.
Thank You
Welcome
it's possible now to create a delta file format :
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
FORMAT_TYPE = DELTA
);