golfalot commented 2 years ago

synapse error message bug

There is horrible very time wasting bug that occurs when using Built-in SQL, writing to ADLS gen2 with synapse Data Exfiltration Protection enabled, and Managed Service Identity If you follow the current documentation and use

https://<mystoageaccount>.blob.core.windows.net/<mycontainer>/<mybaseoutputfolderpath> Instead of https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath> You get error

Operation failed since the external data source 'https://<mystorageaccount>.blob.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>/<myoutputsubfolder>/' has underlying storage account that is not in the list of Outbound Firewall Rules on the server. Please add this storage account to the list of Outbound Firewall Rules on your server and retry the operation.

a better end to end example

The documentation needs a better end to end example, using ADSL gen2 and Managed Service Identity which should be the the majority use case.

I wish to propose the following replacement example

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'Managed Identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [synpaseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [synpaseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

ID: 3ec02f6c-e0bd-93f8-fc77-4b712aafd4b4
Version Independent ID: a1203428-8d9e-7956-9d12-350b01ce8a0f
Content: CREATE EXTERNAL TABLE AS SELECT (CETAS) in Synapse SQL - Azure Synapse Analytics
Content Source: articles/synapse-analytics/sql/develop-tables-cetas.md
Service: synapse-analytics
Sub-service: sql
GitHub Login: @filippopovic
Microsoft Alias: fipopovi