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

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

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).

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.

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 https:.

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

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics, Parallel Data Warehouse)
• CREATE TABLE AS SELECT (Azure Synapse Analytics)
• DROP TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)