CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

Artikel
08/11/2023

Gilt für: SQL Server 2022 (16.x) und höher Azure Synapse Analytics Analytics Platform System (PDW)

Erstellt eine externe Tabelle und exportiert dann parallel die Ergebnisse einer Transact-SQL SELECT-Anweisung.

Azure Synapse Analytics und Analytics Platform System unterstützen Hadoop oder Azure Blob Storage.
Ab SQL Server 2022 (16.x) wird CREATE EXTERNAL TABLE AS SELECT (CETAS) unterstützt, um eine externe Tabelle zu erstellen und dann das Ergebnis einer Transact-SQL-SELECT-Anweisung parallel in Azure Data Lake Storage (ADLS) Gen2, in ein Azure Storage-Konto V2 und in S3-kompatiblen Objektspeicher zu exportieren.

Hinweis

Die Funktionen und die Sicherheit von CETAS für Azure SQL Managed Instance unterscheiden sich von SQL Server und Azure Synapse Analytics. Weitere Informationen finden Sie in der Version von CREATE EXTERNAL TABLE AS SELECT für Azure SQL Managed Instance.

Hinweis

Die Funktionen und die Sicherheit von CETAS für serverlose Pools in Azure Synapse Analytics unterscheiden sich von SQL Server. Weitere Informationen finden Sie unter CETAS mit Synapse SQL.

Transact-SQL-Syntaxkonventionen

Syntax

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

Argumente

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

Der ein- bis dreiteilige Name der Tabelle, die in der Datenbank erstellt werden soll. Bei einer externen Tabelle werden nur die Metadaten der Tabelle in der relationalen Datenbank gespeichert.

[ ( column_name [ ,...n ] ) ]

Der Name einer Tabellenspalte.

LOCATION

Gilt für: Azure Synapse Analytics und Analytics Platform System

'hdfs_folder'**
Gibt an, wohin die Ergebnisse der SELECT-Anweisung auf der externen Datenquelle geschrieben werden sollen. Der Speicherort ist ein Ordnername und kann wahlweise einen Pfad enthalten, der relativ zum Stammordner des Hadoop-Clusters oder der Azure Blob Storage-Instanz ist. PolyBase erstellt Pfad und Ordner, falls nicht bereits vorhanden.

Die externen Dateien mit dem Namen QueryID_date_time_ID.format werden in hdfs_folder geschrieben, wobei ID ein inkrementeller Bezeichner ist und format das Format der exportierten Daten. z. B. QID776_20160130_182739_0.orc.

LOCATION muss auf einen Ordner zeigen und einen nachgestellten / aufweisen, z. B. aggregated_data/.

Gilt für: SQL Server 2022 (16.x) und höher

prefix://path[:port] gibt das Konnektivitätsprotokoll (Präfix), den Pfad und optional den Port der externen Datenquelle an, in die das Ergebnis der SELECT-Anweisung geschrieben werden soll.

Wenn das Ziel ein S3-kompatibler Objektspeicher ist, muss zunächst ein Bucket vorhanden sein, aber PolyBase kann bei Bedarf Unterordner erstellen. SQL Server 2022 (16.x) unterstützt Azure Data Lake Storage Gen2, Azure Storage-Konto v2 und S3-kompatiblen Objektspeicher. ORC-Dateien werden derzeit nicht unterstützt.

DATA_SOURCE = external_data_source_name

Gibt den Namen des externen Datenquellenobjekts an, das den Speicherort enthält, an dem die externen Daten gespeichert sind oder noch gespeichert werden. Der Speicherort ist entweder ein Hadoop-Cluster oder eine Azure Blob Storage-Instanz. Verwenden Sie zum Erstellen einer externen Datenquelle CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

REJECT-Optionen

Zu dem Zeitpunkt der Ausführung der CREATE EXTERNAL TABLE AS SELECT-Anweisung werden keine REJECT-Optionen angewendet. Stattdessen werden sie hier angegeben, damit die Datenbank sie später beim Importieren von Daten aus der externen Tabelle verwenden kann. Wenn die CREATE TABLE AS SELECT-Anweisung später Daten aus der externen Tabelle auswählt, verwendet die Datenbank die REJECT-Optionen, um die Anzahl oder den Prozentsatz an Zeilen zu bestimmen, für die ein Importfehler auftreten kann, bevor der Importvorgang beendet wird.

REJECT_VALUE = reject_value

Gibt die Anzahl oder den Prozentsatz der Zeilen an, für die ein Importfehler auftreten darf, bevor die Datenbank den Importvorgang anhält.
REJECT_TYPE = value | percentage

Bestimmt, ob die Option REJECT_VALUE als Literalwert oder Prozentsatz angegeben ist.
- value
  
  Wird verwendet, wenn REJECT_VALUE ein Literalwert und kein Prozentsatz ist. Die Datenbank beendet das Importieren von Zeilen aus der externen Datendatei, wenn die Anzahl der fehlerhaften Zeilen den Wert von reject_value überschreitet.
  
  Wenn beispielsweise REJECT_VALUE = 5 und REJECT_TYPE = value, beendet die Datenbank den Zeilenimport, nachdem fünf Zeilen nicht importiert werden konnten.
- Prozentwert
  
  Wird verwendet, wenn REJECT_VALUE ein Prozentsatz und kein Literalwert ist. Die Datenbank beendet das Importieren von Zeilen aus der externen Datendatei, wenn der Prozentsatz fehlerhafter Zeilen den Wert von reject_value überschreitet. Der Prozentsatz der fehlerhaften Zeilen wird in Intervallen berechnet. Nur gültig in dedizierten SQL-Pools, wenn TYPE=HADOOP.
REJECT_SAMPLE_VALUE = reject_sample_value

Erforderlich, wenn REJECT_TYPE = percentage. Gibt die Anzahl von Zeilen für den Importversuch an, bevor die Datenbank den Prozentsatz fehlerhafter Zeilen neu berechnet.

Gilt beispielsweise REJECT_SAMPLE_VALUE = 1000, dann berechnet die Datenbank den Prozentsatz fehlerhafter Zeilen nach dem Importversuch von 1000 Zeilen aus der externen Datendatei. Wenn der Prozentsatz fehlerhafter Zeilen kleiner als reject_value ist, führt die Datenbank einen erneuten Ladeversuch von 1.000 Zeilen aus. Nach jedem weiteren Importversuch von 1000 Zeilen berechnet die Datenbank den Prozentsatz fehlerhafter Zeilen neu.

Hinweis

Da die Datenbank den Prozentsatz fehlerhafter Zeilen in Intervallen berechnet, kann der tatsächliche Prozentsatz fehlerhafter Zeilen den Ablehnungswert überschreiten.

Beispiel:

In diesem Beispiel wird verdeutlicht, wie die drei REJECT-Optionen interagieren. Wenn beispielsweise REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100, kann das folgende Szenario eintreten:
- Die Datenbank versucht, die ersten 100 Zeilen zu laden. Davon können 75 Zeilen erfolgreich geladen werden, 25 jedoch nicht.
- Der berechnete Prozentsatz fehlerhafter Zeilen ist mit 25 % kleiner als der Ablehnungswert von 30 %. Somit muss der Ladevorgang nicht angehalten werden.
- Die Datenbank versucht, die nächsten 100 Zeilen zu laden. Dieses Mal wurden 25 Zeilen erfolgreich geladen, 75 dagegen nicht.
- Der Prozentsatz fehlerhafter Zeilen wird mit 50 % neu berechnet. Der Prozentsatz fehlerhafter Zeilen hat den REJECT-Wert von 30 % überschritten.
- Nach dem Ladeversuch von 200 Zeilen schlägt der Ladevorgang mit 50 % fehlerhaften Zeilen fehl. Dieser Prozentsatz liegt über dem angegebenen Grenzwert von 30 %.

WITH common_table_expression

Gibt ein temporäres benanntes Resultset an, das als allgemeiner Tabellenausdruck (CTE, Common Table Expression) bezeichnet wird. Weitere Informationen finden Sie unter WITH common_table_expression (Transact-SQL).

SELECT <select_criteria>

Füllt die neue Tabelle mit den Ergebnissen einer SELECT-Anweisung auf. Select_criteria ist der Hauptteil der SELECT-Anweisung, der bestimmt, welche Daten in die neue Tabelle kopiert werden sollen. Informationen zu SELECT-Anweisungen finden Sie unter SELECT (Transact-SQL).

Hinweis

ORDER BY-Klausel in SELECT hat keine Auswirkungen auf CREATE TABLE AS SELECT.

Spaltenoptionen

column_name [ ,...n ]

Spaltennamen lassen die in CREATE TABLE erwähnten Spaltenoptionen nicht zu. Sie können stattdessen eine optionale Liste mit mindestens einem Spaltennamen für die neue Tabelle bereitstellen. Die Spalten in der neuen Tabelle haben die von Ihnen angegebenen Namen. Wenn Sie Spaltennamen angeben, muss die Anzahl der Spalten in der Spaltenliste mit der Anzahl der Spalten in den SELECT-Ergebnissen übereinstimmen. Wenn Sie keine Spaltennamen angeben, übernimmt die neue Zieltabelle die Spaltennamen aus den Ergebnissen der SELECT-Anweisung.

Sie können keine anderen Spaltenoptionen wie z. B. Datentypen, Sortierung oder NULL-Zulässigkeit angeben. Jedes dieser Attribute wird aus den Ergebnissen der SELECT-Anweisung abgeleitet. Sie können die SELECT-Anwendung allerdings zum Ändern der Attribute verwenden. Ein Beispiel finden Sie unter Verwenden von CETAS zum Ändern von Spaltenattributen.

Berechtigungen

Datenbankbenutzer benötigen sämtliche folgenden Berechtigungen oder Mitgliedschaften, um diesen Befehl ausführen zu können:

ALTER SCHEMA-Berechtigung für das lokale Schema, das die neue Tabelle oder Mitgliedschaft in der festen Datenbankrolle db_ddladmin enthält
CREATE TABLE-Berechtigung oder -Mitgliedschaft in der festen Datenbankrolle db_ddladmin
SELECT-Berechtigung für alle Objekte, auf die in select_criteria verwiesen wird

Eine Anmeldung erfordert alle folgenden Berechtigungen:

ADMINISTER BULK OPERATIONS
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT
Im Allgemeinen benötigen Sie für CREATE TABLE AS SELECT die Berechtigung Auflisten für den Ordnerinhalt und Schreiben für den Ordner LOCATION.
In Azure Synapse Analytics and Analytics Platform System die Berechtigung Schreiben zum Lesen und Schreiben in den externen Ordner im Hadoop-Cluster oder in Azure Blob Storage.
In SQL Server 2022 (16.x) müssen außerdem die richtigen Berechtigungen für den externen Speicherort festgelegt werden. Die Berechtigung Schreiben zum Ausgeben der Daten am Speicherort und die Berechtigung Lesen für den Zugriff darauf.
Für Azure Blob Storage und Azure Data Lake Gen2 müssen dem SHARED ACCESS SIGNATURE-Token die folgenden Berechtigungen für den Container erteilt werden: Lesen, Schreiben, Auflisten, Erstellen.
Für Azure Blob Storage: Bei Allowed Services muss das Kontrollkästchen Blob aktiviert sein, um das SAS-Token zu generieren.
Für Azure Data Lake Gen2: Bei Allowed Services müssen die Kontrollkästchen Container und Object aktiviert sein, um das SAS-Token zu generieren.

Wichtig

Mit der Berechtigung ALTER ANY EXTERNAL DATA SOURCE erhält jeder Prinzipal die Fähigkeit, beliebige externe Datenquellenobjekte zu erstellen und zu ändern. Damit ist auch der Zugriff auf alle datenbankweit gültigen Anmeldeinformationen der Datenbank möglich. Dies ist eine weitreichende Berechtigung und darf nur vertrauenswürdigen Prinzipalen im System erteilt werden.

Fehlerbehandlung

Beim Exportieren von Daten mithilfe der CREATE EXTERNAL TABLE AS SELECT-Anweisung in eine Datei mit Texttrennzeichen gibt es keine Ablehnungsdatei für Zeilen mit Exportfehler.

Wenn Sie eine externe Tabelle erstellen, versucht die Datenbank, eine Verbindung mit dem externen Speicherort herzustellen. Bei einem Verbindungsfehler kann der Befehl nicht ausgeführt und die externe Tabelle nicht erstellt werden. Da mindestens drei Versuche für einen Verbindungsaufbau erfolgen, kann es eine Minute oder länger dauern, bis für den Befehl ein Fehler auftritt.

Wenn CREATE EXTERNAL TABLE AS SELECT abgebrochen wird oder fehlschlägt, unternimmt die Datenbank einen einmaligen Versuch, alle neuen Dateien und Ordner zu entfernen, die bereits für die externe Datenquelle erstellt wurden.

In Azure Synapse Analytics und Analytics Platform System meldet die Datenbank alle Java-Fehler in der externen Datenquelle während des Datenexports.

Bemerkungen

Nach Abschluss der CREATE EXTERNAL TABLE AS SELECT-Anweisung können Sie Transact-SQL-Abfragen in der externen Tabelle ausführen. Mit diesen Vorgängen werden Daten für die Dauer der Abfrage in die Datenbank importiert, es sei denn, der Import erfolgt mit der CREATE TABLE AS SELECT-Anweisung.

Der Name und die Definition der externen Tabelle werden in den Metadaten der Datenbank gespeichert. Die Daten werden in der externen Datenquelle gespeichert.

Die CREATE EXTERNAL TABLE AS SELECT-Anweisung erstellt immer eine nicht partitionierte Tabelle, selbst wenn die Quelltabelle partitioniert ist.

Für SQL Server 2022 (16.x) muss die Option allow polybase export durch die Verwendung von sp_configure aktiviert sein. Weitere Informationen finden Sie unter Festlegen von Konfigurationsoptionen für allow polybase export.

Für Abfragepläne in Azure Synapse Analytics und Analytics Platform System, die mit EXPLAIN erstellt wurden, verwendet die Datenbank diese Abfrageplanvorgänge für externe Tabellen: ExternalShuffleMove, ExternalBroadcastMove, ExternalPartitionMove.

Als Voraussetzung für das Erstellen einer externen Tabelle muss der Administrator der Appliance in Analytics Platform System Hadoop-Konnektivität konfigurieren. Weitere Informationen finden Sie in der Dokumentation zu Analytics Platform System unter „Configure Connectivity to External Data“ (Konfigurieren der Konnektivität mit externen Daten), die Sie aus dem Microsoft Download Center herunterladen können.

Einschränkungen

Daten aus externen Tabellen befinden sich außerhalb der Datenbank. Sicherungs- und Wiederherstellungsvorgänge werden nur für Daten ausgeführt, die in der Datenbank gespeichert sind. Das bedeutet, dass nur die Metadaten gesichert und wiederhergestellt werden.

Beim Wiederherstellen einer Datenbanksicherung, die eine externe Tabelle enthält, überprüft die Datenbank die Verbindung mit der externen Datenquelle nicht. Wenn auf die ursprüngliche Quelle nicht zugegriffen werden kann, werden die Metadaten der externen Tabelle trotzdem erfolgreich wiederhergestellt. Bei SELECT-Vorgängen für die externe Tabelle treten jedoch Fehler auf.

Die Datenbank kann keine Datenkonsistenz zwischen Datenbank und externen Daten garantieren. Sie als Benutzer tragen die alleinige Verantwortung für die Konsistenz zwischen externen Daten und Datenbank.

DML-Vorgänge (Data Manipulation Language, Datenbearbeitungssprache) werden in externen Tabellen nicht unterstützt. So können Sie beispielsweise externe Daten nicht mit den Transact-SQL-Anweisungen UPDATE, INSERT oder DELETE ändern.

Die Anweisungen CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW und DROP VIEW sind die einzigen DDL-Vorgänge (Data Definition Language, Datenbeschreibungssprache), die für externe Tabelle zulässig sind.

Einschränkungen für Azure Synapse Analytics

In dedizierten SQL-Pools von Azure Synapse Analytics und Analytics Platform System sind für PolyBase maximal 33.000 Dateien pro Ordner möglich, wenn 32 gleichzeitige PolyBase-Abfragen ausgeführt werden. Diese maximale Anzahl schließt sowohl Dateien als auch Unterordner im jeweiligen HDFS-Ordner ein. Bei weniger als 32 gleichzeitigen Abfragen können Benutzer auch PolyBase-Abfragen für Ordner in HDFS ausführen, die mehr als 33.000 Dateien enthalten. Benutzern von Hadoop und PolyBase wird empfohlen, kurze Dateipfade und nicht mehr als 30.000 Dateien pro HDFS-Ordner zu verwenden. Verweise auf eine zu große Anzahl von Dateien können zu einer JVM-Ausnahme aufgrund unzureichenden Arbeitsspeichers führen.
In serverlosen SQL-Pools können externe Tabellen nicht an einem Speicherort erstellt werden, an dem derzeit Daten vorhanden sind. Um einen Speicherort wiederzuverwenden, der bereits zum Speichern von Daten verwendet wurde, muss der Speicherort auf ADLS manuell gelöscht werden. Weitere Einschränkungen und bewährte Methoden finden Sie unter Bewährte Methoden für die Filteroptimierung.

Wenn in dedizierten SQL-Pools von Azure Synapse Analytics und Analytics Platform System CREATE EXTERNAL TABLE AS SELECT auf den Formattyp RCFILE angewendet wird, dürfen die Spaltenwerte in RCFILE keinen senkrechten Strich (|) enthalten.

SET ROWCOUNT (Transact-SQL) hat keine Auswirkungen auf CREATE EXTERNAL TABLE AS SELECT. Verwenden Sie TOP (Transact-SQL), um ein ähnliches Verhalten zu erzielen.

Weitere Informationen zu Einschränkungen bei Dateinamen finden Sie unter Benennen von und Verweisen auf Container, Blobs und Metadaten.

Zeichenfehler

Die folgenden Zeichen in den Daten können zu Fehlern führen, einschließlich Ablehnung von Datensätzen mit CREATE EXTERNAL TABLE AS SELECT in Parquet-Dateien.

In Azure Synapse Analytics and Analytics Platform System gilt dies auch für ORC-Dateien.

|
" (Anführungszeichen)
\r\n
\r
\n

Wenn Sie CREATE EXTERNAL TABLE AS SELECT mit diesen Zeichen verwenden möchten, müssen Sie zunächst die CREATE EXTERNAL TABLE AS SELECT-Anweisung ausführen, um die Daten in durch Trennzeichen getrennte Textdateien zu exportieren. Diese können Sie dann mit einem externen Tool in Parquetdateien oder ORC-Dateien konvertieren.

Arbeiten mit Parkett

Bei der Arbeit mit Parkettdateien CREATE EXTERNAL TABLE AS SELECT wird eine Parkettdatei pro verfügbare CPU bis zum konfigurierten maximalen Parallelitätsgrad (MAXDOP) generiert. Jede Datei kann bis zu 190 GB groß werden, nachdem SQL Server bei Bedarf weitere Parkettdateien generiert.

Der Abfragehinweis OPTION (MAXDOP n) wirkt sich nur auf den SELECT-Teil aus CREATE EXTERNAL TABLE AS SELECT, hat keinen Einfluss auf die Menge von Parkettdateien. Nur MAXDOP auf Datenbankebene und MAXDOP auf Instanzebene werden berücksichtigt.

Sperren

Akzeptiert eine gemeinsame Sperre für das SCHEMARESOLUTION-Objekt.

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

Beispiele

A. Erstellen einer Hadoop-Tabelle mithilfe von CREATE EXTERNAL TABLE AS SELECT

Gilt für: Azure Synapse Analytics und Analytics Platform System

Das folgende Beispiel erstellt eine neue externe Tabelle mit dem Namen hdfsCustomer, die die Spaltendefinitionen und Daten aus der Quelltabelle dimCustomer verwendet.

Die Tabellendefinition ist in der Datenbank gespeichert. Die Ergebnisse der SELECT-Anweisung werden in die Datei /pdwdata/customer.tbl in der externen Hadoop-Datenquelle customer_ds exportiert. Die Datei wird entsprechend dem externen Dateiformat customer_ff formatiert.

Der Dateiname wird von der Datenbank generiert und enthält die Abfrage-ID. So lässt sich die Abfrage einfacher mit der Datei abgleichen, die von der Abfrage erzeugt wird.

Der Pfad hdfs://xxx.xxx.xxx.xxx:5000/files/, der dem Customer-Verzeichnis vorangeht, muss bereits vorhanden sein. Wenn das Verzeichnis „Customer“ nicht vorhanden sein, wird es von der Datenbank erstellt.

Hinweis

In diesem Beispiel wird 5000 angegeben. Wird kein Port angegeben, verwendet die Datenbank 8020 als Standardport.

Dies ergibt hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt. als Hadoop-Speicherort und -Dateiname.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. Verwenden eines Abfragehinweises mit CREATE EXTERNAL TABLE AS SELECT

Gilt für: Azure Synapse Analytics und Analytics Platform System

Diese Abfrage zeigt die grundlegende Syntax für die Verwendung eines JOIN-Abfragehinweises mit der CREATE EXTERNAL TABLE AS SELECT-Anweisung. Nach dem Senden der Abfrage verwendet die Datenbank die Hashjoinstrategie, um den Abfrageplan zu generieren. Weitere Informationen zu Join-Abfragehinweisen und zur Verwendung der OPTION-Klausel finden Sie unter OPTION-Klausel (Transact-SQL).

Hinweis

In diesem Beispiel wird 5000 angegeben. Wird kein Port angegeben, verwendet die Datenbank 8020 als Standardport.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. Verwenden von CETAS zum Ändern von Spaltenattributen

Gilt für: Azure Synapse Analytics und Analytics Platform System

Dieses Beispiel verwendet CETAS, um die Datentypen, NULL-Zulässigkeit und Sortierung für mehrere Spalten in der Tabelle FactInternetSales zu ändern.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. Verwenden von CREATE EXTERNAL TABLE AS SELECT zum Exportieren von Daten im Parquet-Format

Gilt für: SQL Server 2022 (16.x)

Das folgende Beispiel erstellt eine neue externe Tabelle mit dem Namen ext_sales, die die Daten aus der Tabelle SalesOrderDetail der Datenbank AdventureWorks2022 verwendet. Die Konfigurationsoption „allow polybase export“ muss aktiviert sein.

Das Ergebnis der SELECT-Anweisung wird in einem zuvor konfigurierten S3-kompatiblen Objektspeicher mit dem Namen s3_eds gespeichert, und die entsprechenden Anmeldeinformationen werden als s3_dsc erstellt. Der Speicherort der Parquet-Datei ist <ip>:<port>/cetas/sales.parquet, wobei cetas der zuvor erstellte Speicherbucket ist.

Hinweis

Das Delta-Format wird derzeit nur schreibgeschützt unterstützt.

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. Verwenden von CREATE EXTERNAL TABLE AS SELECT (Delta-Tabelle in Parquet)

Gilt für: SQL Server 2022 (16.x)

Im folgenden Beispiel wird eine neue externe Tabelle namens Delta_to_Parquet erstellt, die Daten des Typs „Delta-Table“ verwendet, die sich in einem S3-kompatiblen Objektspeicher namens s3_delta befinden, und das Ergebnis in eine andere Datenquelle namens s3_parquet als Parquet-Datei geschrieben. Dafür wird im Beispiel der Befehl OPENROWSET verwendet. Die Konfigurationsoption „allow polybase export“ muss aktiviert sein.

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. Verwenden von CREATE EXTERNAL TABLE AS SELECT mit einer Sicht als Quelle

Gilt für: serverlose und dedizierte SQL-Pools in Azure Synapse Analytics.

In diesem Beispiel sehen wir ein Beispiel für einen Vorlagencode zum Schreiben von CETAS mit einer benutzerdefinierten Ansicht als Quelle, verwenden verwaltete Identität als Authentifizierung und wasbs:.

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 = 'wasbs://<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

G. Verwenden von CREATE EXTERNAL TABLE AS SELECT mit einer Sicht als Quelle

Gilt für: serverlose und dedizierte SQL-Pools in Azure Synapse Analytics.

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

Nächste Schritte

Gilt für: Azure SQL verwaltete Instanz

Erstellt eine externe Tabelle und exportiert dann parallel die Ergebnisse einer Transact-SQL SELECT-Anweisung.

Sie können CREATE EXTERNAL TABLE AS SELECT (CETAS) im verwenden, um die folgenden Aufgaben auszuführen:

Erstellen einer externen Tabelle auf der Grundlage von Parquet- oder CSV-Dateien in Azure Blob Storage oder Azure Data Lake Storage (ADLS) Gen2
Paralleles Exportieren der Ergebnisse einer T-SQL-SELECT-Anweisung in die erstellte externe Tabelle
Weitere Datenvirtualisierungsfunktionen von Azure SQL Managed Instance finden Sie unter Datenvirtualisierung mit Azure SQL Managed Instance.

Hinweis

Diese Inhalte gelten nur für Azure SQL Managed Instance. Wählen Sie für andere Plattformen im Dropdrownmenü die entsprechende Version von CREATE EXTERNAL TABLE AS SELECT aus.

Transact-SQL-Syntaxkonventionen

Syntax

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>

Argumente

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

Ein- bis dreiteiliger Name der Tabelle, die erstellt werden soll. Bei einer externen Tabelle werden nur die Tabellenmetadaten gespeichert. Es werden keine tatsächlichen Daten verschoben oder gespeichert.

LOCATION = 'Pfad_zum_Ordner'

Gibt an, wohin die Ergebnisse der SELECT-Anweisung auf der externen Datenquelle geschrieben werden sollen. Der Stammordner ist der in der externen Datenquelle angegebene Datenspeicherort. LOCATION muss auf einen Ordner zeigen und einen nachgestellten Schrägstrich (/) aufweisen. Beispiel: aggregated_data/.

Der Zielordner für CETAS muss leer sein. Wenn der Pfad und der Ordner noch nicht vorhanden sind, werden sie automatisch erstellt.

DATA_SOURCE = external_data_source_name

Gibt den Namen des externen Datenquellenobjekts an, das den Speicherort enthält, an dem die externen Daten gespeichert werden. Verwenden Sie zum Erstellen einer externen Datenquelle CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Gibt den Namen des externen Dateiformatobjekts an, das das Format für die externe Datendatei enthält. Verwenden Sie zum Erstellen eines externen Dateiformats CREATE EXTERNAL FILE FORMAT (Transact-SQL). Derzeit werden nur externe Dateiformate mit FORMAT_TYPE=PARQUET und FORMAT_TYPE=DELIMITEDTEXT unterstützt. Die GZip-Komprimierung für das DELIMITEDTEXT-Format wird nicht unterstützt.

[, PARTITION ( Spaltenname [ , ...n ] ) ]

Partitioniert die Ausgabedaten in mehrere Parquet-Dateipfade. Die Partitionierung erfolgt pro Spalte (column_name) gemäß den Platzhaltern (*) in LOCATION der jeweiligen Partitionierungsspalte. Die Anzahl der Spalten im PARTITION-Teil muss mit der Anzahl der Platzhalter in LOCATION übereinstimmen. Es muss mindestens eine Spalte vorhanden sein, die nicht für die Partitionierung verwendet wird.

WITH <common_table_expression>

SELECT <select_criteria>

Hinweis

Die ORDER BY-Klausel in SELECT wird für CETAS nicht unterstützt.

Berechtigungen

Berechtigungen im Speicher

Damit CETAS funktioniert, benötigen Sie Berechtigungen zum Auflisten des Ordnerinhalts und zum Schreiben in den mit LOCATION angegebenen Pfad.

Unterstützte Authentifizierungsmethoden sind verwaltete Identitäten oder ein SAS-Token (Shared Access Signature).

Wenn Sie die verwaltete Identität für die Authentifizierung verwenden, stellen Sie sicher, dass der Dienstprinzipal Ihrer verwalteten SQL-Instanz eine Rolle des Mitwirkenden von Storage Blob Data im Zielcontainer hat.
Wenn Sie ein SAS-Token verwenden, sind die Berechtigungen Lesen, Schreiben und Auflisten erforderlich.
Für Azure Blob Storage: Bei Allowed Services muss das Kontrollkästchen Blob aktiviert sein, um das SAS-Token zu generieren.
Für Azure Data Lake Gen2: Bei Allowed Services müssen die Kontrollkästchen Container und Object aktiviert sein, um das SAS-Token zu generieren.

Eine vom Benutzer zugewiesene verwaltete Identität wird nicht unterstützt. Die Microsoft Entra-Passthrough-Authentifizierung wird nicht unterstützt. Microsoft Entra-ID ist (früher Azure Active Directory).

Berechtigungen in SQL Managed Instance

Datenbankbenutzer benötigen sämtliche folgenden Berechtigungen oder Mitgliedschaften, um diesen Befehl ausführen zu können:

ALTER SCHEMA-Berechtigung für das lokale Schema, das die neue Tabelle oder Mitgliedschaft in der festen Datenbankrolle db_ddladmin enthält
CREATE TABLE-Berechtigung oder -Mitgliedschaft in der festen Datenbankrolle db_ddladmin
SELECT-Berechtigung für alle Objekte, auf die in select_criteria verwiesen wird

Eine Anmeldung erfordert alle folgenden Berechtigungen:

ADMINISTER BULK OPERATIONS
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT

Wichtig

Unterstützte Datentypen

CETAS speichert Resultsets mit den folgenden SQL-Datentypen:

BINARY
varbinary
char
varchar
nchar
NVARCHAR
smalldatetime
date
datetime
datetime2
datetimeoffset
time
Decimal
NUMERIC
float
real
BIGINT
TINYINT
SMALLINT
INT
BIGINT
bit
money
smallmoney

Hinweis

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

Einschränkungen

CREATE EXTERNAL TABLE AS SELECT (CETAS) ist für Azure SQL Managed Instance standardmäßig deaktiviert. Weitere Informationen finden Sie im nächsten Abschnitt Standardmäßig deaktiviert.
Weitere Informationen zu Einschränkungen oder bekannten Problemen bei der Datenvirtualisierung in Azure SQL Managed Instance finden Sie unter Einschränkungen und bekannte Probleme.

Beim Wiederherstellen einer Datenbanksicherung, die eine externe Tabelle enthält, überprüft die Datenbank die Verbindung mit der externen Datenquelle nicht. Wenn auf die ursprüngliche Quelle nicht zugegriffen werden kann, werden die Metadaten der externen Tabelle trotzdem erfolgreich wiederhergestellt. SELECT-Vorgänge für die externe Tabelle schlagen jedoch fehl.

Externe Tabellen können nicht an einem Speicherort erstellt werden, an dem derzeit Daten vorhanden sind. Um einen Speicherort wiederzuverwenden, der bereits zum Speichern von Daten verwendet wurde, muss der Speicherort auf ADLS manuell gelöscht werden.

SET ROWCOUNT (Transact-SQL) hat keine Auswirkungen auf CREATE EXTERNAL TABLE AS SELECT. Verwenden Sie TOP (Transact-SQL), um ein ähnliches Verhalten zu erzielen.

Weitere Informationen zu Einschränkungen bei Dateinamen finden Sie unter Benennen von und Verweisen auf Container, Blobs und Metadaten.

Speichertypen

Dateien können in Azure Data Lake Storage Gen2 oder Azure Blob Storage gespeichert werden. Um Dateien abzufragen, müssen Sie den Speicherort in einem bestimmten Format bereitstellen und das Speicherorttyppräfix verwenden, das dem Typ der externen Quelle und des Endpunkts/Protokolls entspricht, wie z. B. in den folgenden Beispielen:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

Wichtig

Das angegebene Standorttyppräfix wird verwendet, um das optimale Protokoll für die Kommunikation auszuwählen und alle erweiterten Funktionen zu nutzen, die von diesem bestimmten Speichertyp angeboten werden. Das Verwenden des generischen Präfix https:// ist deaktiviert. Verwenden Sie stets endpunktspezifische Präfixe.

Standardmäßig deaktiviert

CREATE EXTERNAL TABLE AS SELECT (CETAS) ermöglicht Ihnen, Daten aus SQL Managed Instance in ein externes Speicherkonto zu exportieren. Damit geht allerdings auch ein Risiko für die Datenexfiltration einher. Daher ist CETAS bei Azure SQL Managed Instance standardmäßig deaktiviert.

Aktivieren von CETAS

CETAS kann für Azure SQL Managed Instance nur über eine Methode aktiviert werden, die erhöhte Azure-Berechtigungen erfordert. Eine Aktivierung über T-SQL ist nicht möglich. Aufgrund des Risikos einer nicht autorisierten Datenexfiltration kann CETAS nicht über die gespeicherte T-SQL-Prozedur sp_configure aktiviert werden, sondern erfordert stattdessen, dass die Benutzeraktion außerhalb von SQL Managed Instance ausgeführt wird.

Berechtigungen zum Aktivieren von CETAS

Um über Azure PowerShell zu aktivieren, muss der Benutzer, der den Befehl ausführt, über Die Rolle "Mitwirkender " oder SQL Security Manager Azure RBAC-Rollen für Ihre verwaltete SQL-Instanz verfügen.

Hierfür kann auch eine benutzerdefinierte Rolle erstellt werden, die für die Microsoft.Sql/managedInstances/serverConfigurationOptions-Aktion die Berechtigungen Lesen und Schreiben erfordert.

Methoden zum Aktivieren von CETAS

Um die PowerShell-Befehle auf einem Computer aufzurufen, muss die Az-Paketversion 9.7.0 oder höher lokal installiert sein. Alternativ können Sie Azure Cloud Shell verwenden, um Azure PowerShell unter shell.azure.com auszuführen.

Melden Sie sich zunächst bei Azure an, und legen Sie den richtigen Kontext für Ihr Abonnement fest:

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

Um die Serverkonfigurationsoption „allowPolybaseExport“ zu verwalten, passen Sie die folgenden PowerShell-Skripts an Ihr Abonnement und Ihren SQL Managed Instance-Namen an, und führen Sie dann die Befehle aus. Weitere Informationen finden Sie unter Set-AzSqlServerConfigurationOption und Get-AzSqlServerConfigurationOption.

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

So deaktivieren Sie die Serverkonfigurationsoption „allowPolybaseExport“

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

So rufen Sie den Wert der Serverkonfigurationsoption „allowPolybaseExport“ ab

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

Sie können die Azure-Befehlszeilenschnittstelle verwenden, um die Serverkonfigurationsoption „allowPolybaseExport“ zu verwalten.

Öffnen Sie zunächst entweder die Windows-Eingabeaufforderung (CMD) oder PowerShell, und melden Sie sich bei Azure an. Wechseln Sie bei Bedarf zu Ihrem Abonnement.

az login
$subscriptionId = "<your subscription ID>"
az account set --subscription $subscriptionId

Verwenden Sie az sql mi server-configuration-option set, um die Serverkonfigurationsoption „allowPolybaseExport“ zu aktivieren:

# Enable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '1'

So deaktivieren Sie die Serverkonfigurationsoption „allowPolybaseExport“

# Disable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '0'

So rufen Sie den Wert der Serverkonfigurationsoption „allowPolybaseExport“ ab

# Get server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option show --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport'

Azure CLI-Argumentaliase:

Kurzform	Aliase
-g	--resource-group
--mi	--instance-name --managed-instance --managed-instance-name
-n	--name --server-configuration-option-name
--value	--server-configuration-option-value

Sie können einen generischen API-Befehl über Azure Cloud Shell aufrufen, um die Serverkonfigurationsoption allowPolybaseExport in 1 zu ändern.

Um CETAS für SQL Managed Instance zu aktivieren, passen Sie die folgenden Azure PowerShell-Skriptvariablen an Ihr Abonnement und Ihren SQL Managed Instance-Namen an, und führen Sie es in Azure Cloud Shell aus.

Hinweis

Verwenden Sie für die folgenden Schritte in Azure Cloud Shell zum Konfigurieren oder Überprüfen von CETAS dieselbe PowerShell-Konsole. Spätere Skripts zum Überprüfen des Status hängen vom Kontext ab, der vom ursprünglichen Skript festgelegt wurde.

# Enter your Azure subscription ID
$SubscriptionID = "<YourSubscriptionIDHERE>"

# Enter your managed instance name – for example, "sqlmi1"
$ManagedInstanceName = "<YourManagedInstanceNameHere>"

# ===============================================================================
# INVOKING THE API CALL -- REST OF THE SCRIPT IS NOT USER CONFIGURABLE
# ===============================================================================
# Log in and select a subscription if needed.

if ((Get-AzContext ) -eq $null)
{
    echo "Logging to Azure subscription"
    Login-AzAccount
}
Select-AzSubscription -SubscriptionName $SubscriptionID

# Build the URI for the API call.
$resource_group_name = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName
$uriFull = "https://management.azure.com/subscriptions/" + $SubscriptionID + "/resourceGroups/" + $resource_group_name+ "/providers/Microsoft.Sql/managedInstances/" + $ManagedInstanceName + "/serverConfigurationOptions/allowPolybaseExport" + "?api-version=2022-08-01-preview"

# Build the API request body.
$properties = @{serverConfigurationOptionValue = 1}
$bodyFull = @{properties = $properties}
$jsonBody = $bodyFull | ConvertTo-Json

# Get auth token and build the HTTP request header.
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$currentAzureContext = Get-AzContext
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azProfile)
$token = $profileClient.AcquireAccessToken($currentAzureContext.Tenant.TenantId)
$authToken = $token.AccessToken
$headers = @{}
$headers.Add("Authorization", "Bearer "+"$authToken")

# Invoke API call. API call creates an Asynchronous operation that will change the configuration setting. 
# In rare cases that your SQL MI is down when the async operation will run, the async operation may fail.
# You can check the status of the Async operation using the callback URL in the response header.
# Look for response header parameter: Azure-AsyncOperation.
Invoke-WebRequest -Method PUT -Headers $headers -Uri $uriFull -ContentType "application/json" -Body $jsonBody

Hinweis

Das Festlegen der Serverkonfigurationsoption durch Aufrufen der API ist ein asynchroner Vorgang. Es wird erwartet, dass der asynchrone Vorgang schnell ausgeführt wird und innerhalb von Sekunden abgeschlossen ist, aber seine Ausführung kann aus verschiedenen Gründen fehlschlagen, z. B. wenn Ihre Instanz von SQL Managed Instance zum Zeitpunkt der Ausführung des Vorgangs ausgefallen ist. Es ist daher wichtig, das Ergebnis der Änderung Ihrer Konfigurationsoption zu überprüfen.

Um das Ergebnis des asynchronen Vorgangs des API-Aufrufs zu überprüfen, gibt es zwei Methoden:

Wichtig

Verwenden Sie beim Ausführen der folgenden Skripts dieselbe Azure Cloud Shell-Konsole, in der Sie das Skript ausgeführt haben, um die Konfigurationsoption festzulegen. Dieses Skript hängt vom Kontext ab, der vom ursprünglichen Invoke-WebRequest-Skript festgelegt wurde.

Verwenden Sie PowerShell, um HTTP GET für die Serverkonfigurations-API aufzurufen und zu überprüfen, ob die Konfigurationsoption den gewünschten Wert aufweist. Das folgende PowerShell-Skript zeigt, wie Sie eine GET-Anforderung aufrufen, um die Serverkonfigurationsoption zu überprüfen. Suchen Sie im JSON-Antwortcode nach serverConfigurationOptionValue, um sich zu vergewissern, dass der Wert dem entspricht, den Sie im vorherigen Schritt festgelegt haben. Verwenden Sie dieselbe PowerShell-Konsole, in der Sie das Skript ausgeführt haben, um die Konfigurationsoption festzulegen, da das Skript vom Kontext abhängt, der vom ursprünglichen Skript festgelegt wurde.
```
# You can query the configuration option directly to see if it contains the new changed value.
# Get the configuration status using the call below. This will return JSON with 
# serverConfigurationOptionValue variable value. 
# It should match what you provided in your original request:
Invoke-WebRequest -Method GET -Headers $headers -Uri $uriFull -ContentType "application/json" 
```

Verwenden Sie PowerShell, um HTTP GET für die Rückruf-URL des asynchronen Vorgangs aufzurufen, und überprüfen Sie im JSON-Antwortcode die Variablen „status“ und ihren Wert. Wenn Sie die API aufrufen, um eine Änderung an der Serverkonfiguration vorzunehmen, enthält der Antwortheader eine Rückruf-URL, mit der Sie das Ergebnis und den Status des asynchronen Vorgangs überprüfen können. Der Headerwert Azure-AsyncOperation enthält die Rückruf-URL.

Dies ist ein Beispiel für einen Antwortheader:

[Azure-AsyncOperation, https://management.azure.com/subscriptions/abcdefgh-abcd-abcd-abcd-subscription/resourceGroups/<ResourceGroupName>/providers/Microsoft.Sql/locations/eastus/serverConfigurationOptionAzureAsyncOperation/abcdefgh-abcd-abcd-abcd-abcdef123456?api-version=2022-08-01-preview]

Im folgenden PowerShell-Beispiel wird die Rückruf-URL aus dem Antwortheader verwendet, beginnend mit https://:

# If you got the Azure-AsyncOperation URL from response header, 
# use the below to query the operation for status:
$AzAsyncOpURL = "https://management.azure.com/subscriptions/abcdefgh-abcd-abcd-abcd-subscription/resourceGroups/<ResourceGroupName>/providers/Microsoft.Sql/locations/eastus/serverConfigurationOptionAzureAsyncOperation/abcdefgh-abcd-abcd-abcd-abcdef123456?api-version=2022-08-01-preview"
Invoke-WebRequest -Method GET -Headers $headers -Uri $AzAsyncOpURL -ContentType "application/json"

Überprüfen des Status von CETAS

Sie können jederzeit den aktuelle Status der CETAS-Konfigurationsoption überprüfen.

Stellen Sie eine Verbindung mit Ihrer Instanz von SQL Managed Instance her. Führen Sie die folgende T-SQL-Anweisung aus, und überprüfen Sie die Spalte value in der Antwort. Nachdem die Änderung der Serverkonfiguration abgeschlossen ist, sollten die Ergebnisse dieser Abfrage mit Ihrer gewünschten Einstellung übereinstimmen.

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

Problembehandlung

Weitere Schritte zur Problembehandlung bei der Datenvirtualisierung in Azure SQL Managed Instance finden Sie unter Problembehandlung. Im Folgenden finden Sie Informationen zur Fehlerbehandlung und allgemeine Fehlermeldungen für CETAS in Azure SQL Managed Instance.

Fehlerbehandlung

Beim Exportieren von Daten mithilfe der CREATE EXTERNAL TABLE AS SELECT-Anweisung in eine Datei mit Texttrennzeichen gibt es keine Ablehnungsdatei für Zeilen mit Exportfehler.

Häufige Fehlermeldungen

Diese häufig auftretenden Fehlermeldungen enthalten kurze Erklärungen für CETAS für Azure SQL Managed Instance.

Sie haben einen Speicherort angegeben, der bereits im Speicher vorhanden ist.

Lösung: Löschen Sie den Speicherort (einschließlich der Momentaufnahme), oder ändern Sie den LOCATION-Parameter in der Abfrage.

Beispielfehlermeldung: Msg 15842: Cannot create external table. External table location already exists.
Die Spaltenwerte wurden mithilfe von JSON-Objekten formatiert.

Lösung: Konvertieren Sie die Wertspalte in eine einzelne VARCHAR- oder NVARCHAR-Spalte oder in mehrere Spalten mit explizit definierten Typen.

Beispielfehlermeldung: Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.
Der LOCATION-Parameter ist ungültig (z. B. mehrere //).

Lösung: Korrigieren Sie den LOCATION-Parameter.

Beispielfehlermeldung: Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.
Eine der erforderlichen Optionen (DATA_SOURCE, FILE_FORMAT, LOCATION) fehlt.

Lösung: Fügen Sie den fehlenden Parameter in der CETAS-Abfrage hinzu.

Beispielfehlermeldung: Msg 46505: Missing required external DDL option 'FILE_FORMAT'
Zugriffsprobleme (ungültige oder abgelaufene Anmeldeinformationen bzw. Anmeldeinformationen mit unzureichenden Berechtigungen). Eine alternative Möglichkeit ist ein ungültiger Pfad, bei dem SQL Managed Instance einen Fehler 404 vom Speicher erhalten hat.

Lösung: Überprüfen Sie die Gültigkeit und die Berechtigungen der Anmeldeinformationen. Alternativ können Sie überprüfen, ob der Pfad gültig ist und Speicher vorhanden ist. Verwenden Sie den URL-Pfad adls://<container>@<storage_account>.blob.core.windows.net/<path>/.

Beispielfehlermeldung: Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'
Der Abschnitt „location“ von DATA_SOURCE enthält Platzhalterzeichen.

Lösung: Entfernen Sie die Platzhalterzeichen aus „location“.

Beispielfehlermeldung: Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.
Die Anzahl der Platzhalterzeichen im LOCATION-Parameter und die Anzahl der partitionierten Spalten stimmen nicht überein.

Lösung: Stellen Sie sicher, dass in LOCATION dieselbe Anzahl von Platzhaltern wie Partitionsspalten vorhanden ist.

Beispielfehlermeldung: Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.
Der Spaltenname in der PARTITION-Klausel stimmt mit keinen Spalten in der Liste überein.

Lösung: Stellen Sie sicher, dass die Spalten in PARTITION gültig sind.

Beispielfehlermeldung: Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list
Eine Spalte wurde in der PARTITION-Liste mehrmals angegeben.

Lösung: Stellen Sie sicher, dass die Spalten in der PARTITION-Klausel eindeutig sind.

Beispielfehlermeldung: Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.
Eine Spalte wurde in der PARTITION-Liste mehrmals angegeben, oder sie entspricht keinen Spalten in der SELECT-Liste.

Lösung: Stellen Sie sicher, dass die Partitionsliste keine Duplikate enthält und die Partitionsspalten im SELECT-Teil vorhanden sind.

Beispielfehlermeldung: Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter. oder Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.
Es werden alle Spalten in der PARTITION-Liste verwendet.

Lösung: Mindestens eine der Spalten aus dem SELECT-Teil darf nicht im PARTITION-Teil der Abfrage enthalten sein.

Beispielfehlermeldung: Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.
Das Feature ist deaktiviert.

Lösung: Aktivieren Sie das Feature mithilfe der Informationen im Abschnitt Standardmäßig deaktiviert in diesem Artikel.

Beispielfehlermeldung: Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

Sperren

Akzeptiert eine gemeinsame Sperre für das SCHEMARESOLUTION-Objekt.

Beispiele

A. Verwenden von CETAS mit einer Sicht zum Erstellen einer externen Tabelle mithilfe der verwalteten Identität

In diesem Beispiel wird Code zum Schreiben von CETAS mit einer Ansicht als Quelle mithilfe der vom System verwalteten Identität einer Authentifizierung bereitgestellt.

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 [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

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

B. Verwenden von CETAS mit einer Sicht zum Erstellen einer externen Tabelle mithilfe der SAS-Authentifizierung

In diesem Beispiel wird Code zum Schreiben von CETAS mit einer Sicht als Quelle bereitgestellt, wobei ein SAS-Token für die Authentifizierung verwendet wird.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

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

CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = '<azure_shared_access_signature>' ;
GO

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

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

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

C. Erstellen einer externen Tabelle in einer einzelnen Parquet-Datei im Speicher

Die nächsten beiden Beispiele zeigen, wie einige der Daten aus einer lokalen Tabelle in eine externe Tabelle ausgelagert werden, die als Parquet-Dateien im Azure Blob Storage-Container gespeichert werden. Sie sind für die Verwendung mit der AdventureWorks2022-Datenbank konzipiert. Dieses Beispiel zeigt das Erstellen einer externen Tabelle als einzelne Parquet-Datei. Im nächsten Beispiel wird gezeigt, wie Sie eine externe Tabelle erstellen und in mehrere Ordner mit Parquet-Dateien partitionieren.

Im folgenden Beispiel wird die verwaltete Identität für die Authentifizierung verwendet. Stellen Sie daher sicher, dass Ihr Dienstprinzipal für Azure SQL Managed Instance über die Rolle Mitwirkender an Storage-Blobdaten in Ihrem Azure Blob Storage-Container verfügt. Alternativ können Sie das Beispiel ändern und SAS-Token (Shared Access Signature) für die Authentifizierung verwenden.

Im folgenden Beispiel erstellen Sie eine externe Tabelle in einer einzelnen Parquet-Datei in Azure Blob Storage und wählen in der Tabelle SalesOrderHeader Bestellungen von vor dem 1.1.2014 aus:

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

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

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

D: Erstellen einer partitionierten externen Tabelle in mehreren Parquet-Dateien, die in einer Ordnerstruktur gespeichert sind

Dieses Beispiel baut auf dem vorherigen Beispiel auf. Es zeigt, wie Sie eine externe Tabelle erstellen und in mehrere Ordner mit Parquet-Dateien partitionieren. Sie können partitionierte Tabellen verwenden, um Leistungsvorteile zu erzielen, wenn Ihr Dataset groß ist.

Sie erstellen anhand der Schritte aus Beispiel B eine externe Tabelle aus SalesOrderHeader-Daten, partitionieren die externe Tabelle jedoch nach Jahr und Monat von OrderDate. Beim Abfragen partitionierter externer Tabellen können Sie von einem Leistungsvorteil aufgrund der fehlenden Partitionierung profitieren.

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

Freigeben über

CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

Syntax

Argumente

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

[ ( column_name [ ,...n ] ) ]

LOCATION

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

REJECT-Optionen

WITH common_table_expression

SELECT <select_criteria>

Berechtigungen

Fehlerbehandlung

Bemerkungen

Einschränkungen

Einschränkungen für Azure Synapse Analytics

Zeichenfehler

Arbeiten mit Parkett

Sperren

Unterstützte Datentypen

Beispiele

A. Erstellen einer Hadoop-Tabelle mithilfe von CREATE EXTERNAL TABLE AS SELECT

B. Verwenden eines Abfragehinweises mit CREATE EXTERNAL TABLE AS SELECT

C. Verwenden von CETAS zum Ändern von Spaltenattributen

D. Verwenden von CREATE EXTERNAL TABLE AS SELECT zum Exportieren von Daten im Parquet-Format

E. Verwenden von CREATE EXTERNAL TABLE AS SELECT (Delta-Tabelle in Parquet)

F. Verwenden von CREATE EXTERNAL TABLE AS SELECT mit einer Sicht als Quelle

G. Verwenden von CREATE EXTERNAL TABLE AS SELECT mit einer Sicht als Quelle

Nächste Schritte

Syntax

Argumente

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

LOCATION = 'Pfad_zum_Ordner'

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

[, PARTITION ( Spaltenname [ , ...n ] ) ]

WITH <common_table_expression>

SELECT <select_criteria>

Berechtigungen

Berechtigungen im Speicher

Berechtigungen in SQL Managed Instance

Unterstützte Datentypen

Einschränkungen

Speichertypen

Standardmäßig deaktiviert

Aktivieren von CETAS

Berechtigungen zum Aktivieren von CETAS

Methoden zum Aktivieren von CETAS

Überprüfen des Status von CETAS

Problembehandlung

Fehlerbehandlung

Häufige Fehlermeldungen

Sperren

Beispiele

A. Verwenden von CETAS mit einer Sicht zum Erstellen einer externen Tabelle mithilfe der verwalteten Identität

B. Verwenden von CETAS mit einer Sicht zum Erstellen einer externen Tabelle mithilfe der SAS-Authentifizierung

C. Erstellen einer externen Tabelle in einer einzelnen Parquet-Datei im Speicher

D: Erstellen einer partitionierten externen Tabelle in mehreren Parquet-Dateien, die in einer Ordnerstruktur gespeichert sind

Nächste Schritte

Feedback

Zusätzliche Ressourcen