CETAS mit Synapse SQL

Artikel
03/31/2023

Sie können „CREATE EXTERNAL TABLE AS SELECT (CETAS)“ in einem dedizierten SQL-Pool oder in einem serverlosen SQL-Pool verwenden, um die folgenden Aufgaben auszuführen:

Erstellen einer externen Tabelle
paralleles Exportieren der Ergebnisse einer Transact-SQL-SELECT-Anweisung in folgende Dienste:
- Hadoop
- Azure Storage-Blob
- Azure Data Lake Storage Gen2

CETAS im dedizierten SQL-Pool

Informationen zum dedizierten SQL-Pool sowie zur Verwendung und Syntax von CETAS finden Sie im Artikel CREATE EXTERNAL TABLE AS SELECT. Zusätzlich finden Sie Anleitungen zur Verwendung von CTAS mit dem dedizierten SQL-Pool im Artikel CREATE TABLE AS SELECT.

CETAS im serverlosen SQL-Pool

Bei Verwendung eines serverlosen SQL-Pools dient CETAS zum Erstellen einer externen Tabelle und Exportieren von Abfrageergebnissen in Azure Storage Blob oder Azure Data Lake Storage Gen2.

Die vollständige Syntax finden Sie unter CREATE EXTERNAL TABLE AS SELECT (Transact-SQL).

Beispiele

Diese Beispiele verwenden CETAS, um eine nach Jahr und Staat aggregierte Gesamtbevölkerungszahl in den Ordner „aggregated_data“ zu speichern, der sich in der Datenquelle „population_ds“ befindet.

Dieses Beispiel verwendet die zuvor erstellten Parameter für Anmeldeinformationen, Datenquelle und externes Dateiformat. Informationen dazu finden Sie im Dokument Externe Tabellen. Um Abfrageergebnisse in einem anderen Ordner in derselben Datenquelle zu speichern, ändern Sie das LOCATION-Argument.

Um Ergebnisse in einem anderen Speicherkonto zu speichern, erstellen und verwenden Sie eine andere Datenquelle für das DATA_SOURCE-Argument.

Hinweis

In den folgenden Beispielen wird ein öffentliches Azure Open Data-Speicherkonto verwendet. Dieses ist schreibgeschützt. Um diese Abfragen auszuführen, müssen Sie die Datenquelle angeben, für die Sie Schreibberechtigungen besitzen.

-- use CETAS to export select statement with OPENROWSET result to  storage
CREATE EXTERNAL TABLE population_by_year_state
WITH (
    LOCATION = 'aggregated_data/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)  
AS
SELECT decennialTime, stateName, SUM(population) AS population
FROM
    OPENROWSET(BULK 'https://azureopendatastorage.dfs.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet',
    FORMAT='PARQUET') AS [r]
GROUP BY decennialTime, stateName
GO

-- you can query the newly created external table
SELECT * FROM population_by_year_state

Im folgenden Beispiel wird eine externe Tabelle als Quelle für CETAS verwendet. Es nutzt die zuvor erstellten Parameter für Anmeldeinformationen, Datenquelle, externes Dateiformat und externe Tabelle. Informationen dazu finden Sie im Dokument Externe Tabellen.

-- use CETAS with select from external table
CREATE EXTERNAL TABLE population_by_year_state
WITH (
    LOCATION = 'aggregated_data/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)  
AS
SELECT decennialTime, stateName, SUM(population) AS population
FROM census_external_table
GROUP BY decennialTime, stateName
GO

-- you can query the newly created external table
SELECT * FROM population_by_year_state

Allgemeines Beispiel

Hier sehen Sie ein Beispiel für einen Vorlagencode zum Schreiben von CETAS mit einer Ansicht als Quelle und mit Verwendung einer verwalteten Identität als Authentifizierung.

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 [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

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

Unterstützte Datentypen

CETAS kann zum Speichern von Resultsets mit den folgenden SQL-Datentypen verwendet werden:

BINARY
varbinary
char
varchar
NCHAR
NVARCHAR
smalldate
date
datetime
datetime2
datetimeoffset
time
Decimal
NUMERIC
float
real
BIGINT
TINYINT
SMALLINT
INT
BIGINT
bit
money
SMALLMONEY
UNIQUEIDENTIFIER

Hinweis

LOBs, die größer als 1 MB sind, können nicht mit CETAS verwendet werden.

Nächste Schritte

Versuchen Sie, Apache Spark für externe Azure Synapse-Tabellen abzufragen.

Teilen über