Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save joanteixi/9ae18d474c64793cad80384a21c14b28 to your computer and use it in GitHub Desktop.
Save joanteixi/9ae18d474c64793cad80384a21c14b28 to your computer and use it in GitHub Desktop.
Create external table from Delta for Synapse Serverless to a Storage Account
CREATE MASTER KEY ENCRYPTION BY PASSWORD='dwdweoifjidw$_fwefjwoifj%%123';
CREATE DATABASE SCOPED CREDENTIAL ServiceIdentity WITH IDENTITY = 'Managed Identity';
CREATE schema stg
GO
CREATE SCHEMA silver
GO
IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDeltaFormat')
CREATE EXTERNAL FILE FORMAT [SynapseDeltaFormat]
WITH ( FORMAT_TYPE = DELTA)
GO
DROP EXTERNAL DATA SOURCE [dataStorage]
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'dataStorage')
CREATE EXTERNAL DATA SOURCE [dataStorage]
WITH (
LOCATION = 'abfss://data@strbatdataanalyticsd1.dfs.core.windows.net' ,
CREDENTIAL = ServiceIdentity
)
GO
DROP EXTERNAL DATA SOURCE [SynapseDelimitedTextFormat]
IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDelimitedTextFormat')
CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat]
WITH ( FORMAT_TYPE = DELIMITEDTEXT ,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ',',
USE_TYPE_DEFAULT = FALSE,
FIRST_ROW = 2,
))
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment