ERSTELLEN EINES EXTERNEN DATEIFORMATS (Transact-SQL)

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL verwaltete Instanz Azure Synapse Analytics Analytics Platform System (PDW)

Erstellt ein Objekt im externen Dateiformat und definiert externe Daten, die in Hadoop, Azure Blob Storage, Azure Data Lake Store oder für die Eingabe- und Ausgabestreams gespeichert sind, die externen Streams zugeordnet sind. Das Erstellen eines externen Dateiformats ist eine Voraussetzung für die Erstellung einer externen Tabelle. Durch das Erstellen eines externen Dateiformats geben Sie das tatsächliche Layout der Daten an, auf die von einer externen Tabelle verwiesen wird. Informationen zur Erstellung einer externen Tabelle finden Sie unter ERSTELLEN EINER EXTERNEN TABELLE (Transact-SQL).

Die folgenden Dateiformate werden unterstützt:

• Text mit Trennzeichen

• Hive RCFile

  Gilt nicht für Azure Synapse Analytics, Azure SQL Managed Instance oder SQL Server 2022 (16.x).

• Hive ORC

  Gilt nicht für Azure Synapse Analytics, Azure SQL Managed Instance oder SQL Server 2022 (16.x).

• Parquet

• JSON

  Es gilt nur für Azure SQL Edge. Weitere Informationen zur Verwendung von OPENROWSET zum Importieren von JSON-Daten auf anderen Plattformen finden Sie unter Importieren von JSON-Dokumenten in SQL Server und Abfragen von JSON-Dateien mit einem serverlosen SQL-Pool in Azure Synapse Analytics.

• Delta

  Dies gilt nur für die serverlosen SQL-Pools in Azure Synapse Analytics und SQL Server 2022 (16.x).

Syntax

Transact-SQL-Syntaxkonventionen

-- Create an external file format for DELIMITED (CSV/TSV) files.
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
        FORMAT_TYPE = DELIMITEDTEXT
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]
    [ , DATA_COMPRESSION = {
           'org.apache.hadoop.io.compress.GzipCodec'
        }
     ]);

<format_options> ::=
{
    FIELD_TERMINATOR = field_terminator
    | STRING_DELIMITER = string_delimiter
    | FIRST_ROW = integer -- Applies to: Azure Synapse Analytics and SQL Server 2022 and later versions
    | DATE_FORMAT = datetime_format
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | ENCODING = {'UTF8' | 'UTF16'}
    | PARSER_VERSION = {'parser_version'}

}

--Create an external file format for RC files.
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = {
        'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
      | 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'
    }
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec' ]);

--Create an external file format for ORC file.
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
         FORMAT_TYPE = ORC
     [ , DATA_COMPRESSION = {
        'org.apache.hadoop.io.compress.SnappyCodec'
      | 'org.apache.hadoop.io.compress.DefaultCodec' }
    ]);

--Create an external file format for PARQUET files.
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
         FORMAT_TYPE = PARQUET
     [ , DATA_COMPRESSION = {
        'org.apache.hadoop.io.compress.SnappyCodec'
      | 'org.apache.hadoop.io.compress.GzipCodec' }
    ]);

-- Create an external file format for JSON files.
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
    FORMAT_TYPE = JSON
     [ , DATA_COMPRESSION = {
        'org.apache.hadoop.io.compress.SnappyCodec'
      | 'org.apache.hadoop.io.compress.GzipCodec'
      | 'org.apache.hadoop.io.compress.DefaultCodec' }
    ]);

-- Create an external file format for delta table files (serverless SQL pools in Synapse analytics and SQL Server 2022).
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
         FORMAT_TYPE = DELTA
      );

Argumente

file_format_name

Gibt einen Namen für das externe Dateiformat an.

FORMAT_TYPE

Gibt das Format der externen Daten an.

• FORMAT_TYPE = PARQUET

  Gibt ein Parquet-Format an.

• FORMAT_TYPE = ORC

  Gibt ein ORC-Format (ORC = Optimized Row Columnar) an. Für diese Option ist auf dem externen Hadoop-Cluster Hive Version 0.11 oder höher erforderlich. In Hadoop bietet das ORC-Dateiformat eine bessere Komprimierung und Leistung als das RCFILE-Dateiformat.

• FORMAT_TYPE = RCFILE, SERDE_METHOD = SERDE_method

  Gibt ein RcFile-Dateiformat (Record Columnar File) an. Für diese Option müssen Sie eine Hive Serializer- und Hive Deserializer-Methode (SerDe) angeben. Diese Anforderung ist auch gegeben, wenn Sie in Hadoop Hive/HiveQL zum Abfragen von RC-Dateien verwenden. Beachten Sie, dass bei der SerDe-Methode die Groß-/Kleinschreibung beachtet werden muss.

  Beispiele für die Angabe von RCFile mit den zwei von PolyBase unterstützten SerDe-Methoden.

  • FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
  • FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'

• FORMAT_TYPE = DELIMITEDTEXT

  Dient zum Angeben eines Textformats mit Spaltentrennzeichen (auch Feldabschlusszeichen genannt).

• FORMAT_TYPE = JSON

  Gibt ein JSON-Format an. Es gilt nur für Azure SQL Edge.

• FORMAT_TYPE = DELTA

  Gibt ein Delta Lake-Format an. Gilt für serverlose SQL-Pools in Azure Synapse Analytics und SQL Server 2022 (16.x).

FORMAT_OPTIONS

Optional. Nur für Textdatentypen mit Trennzeichen.

Nur serverlose SQL-Pools in Azure Synapse Analytics unterstützen PARSER_VERSION.

Serverlose SQL-Pools unterstützen die Option DATE_FORMAT nicht.

DATA_COMPRESSION = data_compression_method

Gibt die Datenkomprimierungsmethode für die externen Daten an. Wenn DATA_COMPRESSION nicht angegeben ist, stellen dekomprimierte Daten den Standard dar.

GZip-komprimierte Dateien müssen die Dateierweiterung „.gz“ aufweisen, damit sie ordnungsgemäß funktionieren.

Formatierungsoptionen für Text mit Trennzeichen

Sämtliche in diesem Abschnitt beschriebenen Formatierungsoptionen sind optional und gelten nur für durch Trennzeichen getrennte Textdateien.

FIELD_TERMINATOR = field_terminator

Gilt nur für durch Trennzeichen getrennte Textdateien. Das Feldabschlusszeichen gibt mindestens ein Zeichen an, welches das Ende der einzelnen Felder (Spalten) in der durch Trennzeichen getrennten Textdatei markiert. Als Standardzeichen wird der senkrechte Strich (|) verwendet. Für garantierte Unterstützung wird empfohlen, mehrere ASCII-Zeichen zu verwenden.

Beispiele:

• FIELD_TERMINATOR = '|'
• FIELD_TERMINATOR = ' '
• FIELD_TERMINATOR = ꞌ\tꞌ
• FIELD_TERMINATOR = '~|~'

STRING_DELIMITER

STRING_DELIMITER = *string_delimiter*

Gibt das Feldabschlusszeichen für Daten der Typzeichenfolge in der durch Trennzeichen getrennten Textdatei an. Das Zeichenfolgen-Trennzeichen umfasst mindestens ein Zeichen und ist in einfache Anführungszeichen gesetzt. Der Standardwert ist die leere Zeichenfolge „“. Für garantierte Unterstützung wird empfohlen, mehrere ASCII-Zeichen zu verwenden.

Beispiele:

• STRING_DELIMITER = '"'

• STRING_DELIMITER = '0x22': Hexadezimalzahl in doppelten Anführungszeichen

• STRING_DELIMITER = '*'

• STRING_DELIMITER = ꞌ,ꞌ

• STRING_DELIMITER = '0x7E0x7E': Zwei Tilden (Beispiel: ~~)

FIRST_ROW = first_row_int

Gilt für: Azure Synapse Analytics SQL Server ab Version 2022.

Gibt die Zeilenzahl an, die während eines PolyBase-Ladevorgangs in allen Dateien zuerst gelesen wird. Dieser Parameter kann Werte von 1 bis 15 umfassen. Wenn der Wert auf zwei festgelegt ist, wird die erste Zeile in jeder Datei (Kopfzeile) beim Laden der Daten übersprungen. Zeilen werden basierend auf dem Vorhandensein von Zeilenabschlusszeichen (/ r/n, r, /n) übersprungen. Wenn diese Option für den Export verwendet wird, werden Zeilen zu den Daten hinzugefügt, um sicherzustellen, dass die Datei ohne Datenverlust gelesen werden kann. Wenn der Wert auf >2 festgelegt wird, enthält die erste exportierte Zeile die Spaltennamen der externen Tabelle.

DATE_FORMAT = datetime_format

Gibt ein benutzerdefiniertes Format für alle Datums- und Zeitdaten an, die in einer durch Trennzeichen getrennten Textdatei angezeigt werden könnten. Wenn die Quelldatei Standardformate für datetime verwendet, ist diese Option nicht erforderlich. Pro Datei ist nur ein benutzerdefiniertes datetime-Format zulässig. Sie können pro Datei nicht mehrere benutzerdefinierte datetime-Formate angeben. Sie können jedoch mehrere datetime-Formate verwenden, wenn es sich bei den einzelnen Formaten um die Standardformate der entsprechenden zugehörigen Datentypen in der externen Tabellendefinition handelt.

Wenn DATE_FORMAT nicht angegeben ist oder eine leere Zeichenfolge ist, verwendet PolyBase folgende Standardformate:

• datetime: 'yyyy-MM-dd HH:mm:ss'

• smalldatetime: 'yyyy-MM-dd HH:mm'

• date: 'yyyy-MM-dd'

• datetime2: 'yyyy-MM-dd HH:mm:ss'

• datetimeoffset: 'yyyy-MM-dd HH:mm:ss'

• time: 'HH:mm:ss'

In der folgenden Tabelle finden Sie Beispiele für Datumsformate:

Hinweise zur Tabelle:

• Jahr, Monat und Tag können verschiedene Formate und Reihenfolgen aufweisen. Die Tabelle enthält nur das Format JMT. Ein Monat kann eine oder zwei Ziffern oder drei Zeichen umfassen. Ein Tag kann eine oder zwei Ziffern umfassen. Ein Jahr kann zwei oder vier Ziffern umfassen.

• Millisekunden (fffffff) sind nicht erforderlich.

• am oder pm (tt) ist nicht erforderlich. Der Standardwert ist AM.

Unterstützte Datums- und Uhrzeitformate

Ein externes Dateiformat kann eine Vielzahl von Datums- und Uhrzeitformaten beschreiben:

Details:

• Um Monats-, Tages- und Jahreswerte zu trennen, müssen Sie eine der folgenden Trennlinien verwenden: -,/ oder .. Der Einfachheit halber wird in der Tabelle nur das Trennzeichen - verwendet.

• Geben Sie mindestens drei Zeichen an, wenn der Monat in Textform angegeben werden soll. Monate mit einem oder zwei Zeichen werden als Zahlen interpretiert.

• Trennen Sie Zeitwerte mit dem Symbol :.

• Buchstaben in eckigen Klammern sind optional.

• Die Buchstaben tt kennzeichnen [AM|PM|am|pm]. AM ist die Standardeinstellung. Wenn tt angegeben ist, muss der Wert für die Stunde (hh) im Bereich von 0 bis 12 liegen.

• Die Buchstaben zzz kennzeichnen den Offsetwert der Zeitzone für die aktuelle Zeitzone des Systems im Format {+|-}HH:ss].

USE_TYPE_DEFAULT = { TRUE | FALSE }

Gibt an, wie fehlende Werte in durch Trennzeichen getrennten Textdateien behandelt werden sollen, wenn PolyBase Daten aus der Textdatei abruft. Der Standardwert ist FALSCH.

• TRUE

  Speichern Sie beim Abrufen von Daten aus der Textdatei jeden fehlenden Wert, indem Sie den Standardwert für den Datentyp der entsprechenden Spalte in der externen Tabellendefinition verwenden. Ersetzen Sie einen fehlenden Wert beispielsweise durch:

  • 0, wenn die Spalte als numerische Spalte definiert ist. Dezimalspalten werden nicht unterstützt und führen zu einem Fehler.

  • Leere Zeichenfolge „“, wenn die Spalte eine Zeichenfolgenspalte ist.

  • 1900-01-01, wenn die Spalte eine Datumsspalte ist.

  • In Azure Synapse Analytics wird USE_TYPE_DEFAULT=true nicht für FORMAT_TYPE = DELIMITEDTEXT, PARSER_VERSION = '2.0' unterstützt.

• FALSCH

  Speichern Sie alle fehlenden Werte als NULL-Werte. Alle NULL-Werte, die durch Angabe des Worts NULL in der durch Trennzeichen getrennten Textdatei gespeichert werden, werden als die Zeichenfolge NULL importiert.

ENCODING = {'UTF8' | 'UTF16'}

In Azure Synapse Analytics und Analytics-Plattformsystem (PDW) (APS CU 7.4) kann PolyBase UTF8- und UFT16-LE-codierte durch Trennzeichen getrennte Textdateien lesen.

In SQL Server unterstützt PolyBase nicht das Lesen UTF16-codierter Dateien.

Berechtigungen

Macht eine ALTER ANY EXTERNAL FILE FORMAT-Berechtigung erforderlich.

Bemerkungen

Das externe Dateiformat ist in SQL Server und Azure Synapse Analytics datenbankweit gültig. Es ist in Analytics-Plattformsystem (PDW) serverweit gültig.

Sämtliche Formatoptionen sind optional und gelten nur für durch Trennzeichen getrennte Textdateien.

Wenn die Daten in einem der komprimierten Formate gespeichert werden, dekomprimiert PolyBase die Daten zunächst, bevor es die Datensätze zurückgibt.

Einschränkungen

Das Zeilentrennzeichen in durch Trennzeichen getrennten Textdateien muss von dem LineRecordReader von Hadoop unterstützt werden. Möglichkeiten sind \r, \n oder \r\n. Diese Trennzeichen sind nicht durch Benutzer*innen konfigurierbar.

Die Kombinationen der unterstützten SerDe-Methoden mit RCFiles und die unterstützten Datenkomprimierungsmethoden werden oben in diesem Artikel aufgeführt. Nicht alle Kombinationen werden unterstützt.

Die maximale Anzahl der gleichzeitigen PolyBase-Abfragen beläuft sich auf 32. Wenn 32 gleichzeitige Abfragen ausgeführt werden, kann jede Abfrage maximal 33.000 Dateien vom Speicherort für externe Dateien lesen. Der Stammordner und die einzelnen Unterordner zählen auch als Datei. Wenn der Grad der Parallelität unter 32 liegt, kann der Speicherort für externe Dateien mehr als 33.000 Dateien enthalten.

Aufgrund der Begrenzung in Bezug auf die Anzahl der Dateien in der externen Tabelle wird empfohlen, weniger als 30.000 Dateien in den Stamm- und Unterordnern des Speicherorts für externe Dateien zu speichern. Darüber hinaus wird empfohlen, die Anzahl der Unterordner unter dem Stammverzeichnis gering zu halten. Wenn auf zu viele Dateien verwiesen wird, kann eine Out-of-Memory-Ausnahme von Java Virtual Machine auftreten.

Beim Exportieren von Daten nach Hadoop oder Azure Blob Storage über PolyBase werden nur die Daten exportiert, nicht die Spaltennamen (Metadaten), wie im CREATE EXTERNAL TABLE-Befehl definiert.

Sperren

Akzeptiert eine gemeinsame Sperre für das EXTERNAL FILE FORMAT-Objekt.

Leistung

Die Verwendung komprimierter Dateien ist immer mit dem Konflikt verbunden, ob weniger Daten zwischen der externen Datenquelle und SQL Server übertragen werden sollen oder ob die CPU-Auslastung zum Komprimieren und Dekomprimieren der Daten erhöht werden soll.

GZip-komprimierte Textdateien können nicht aufgeteilt werden. Es wird empfohlen, zur Verbesserung der Leistung von GZip-komprimierten Textdateien mehrere Dateien zu generieren, die alle in demselben Verzeichnis innerhalb der externen Datenquelle gespeichert sind. Die Dateistruktur ermöglicht PolyBase das schnellere Lesen und Dekomprimieren der Daten, indem mehrere Reader- und Dekomprimierungsprozesse verwendet werden. Die ideale Anzahl der komprimierten Dateien ist die maximale Anzahl der Datenleseprozesse pro Serverknoten. In SQL Server und Analytics Platform System (PDW) ist die maximale Anzahl von Datenleseprozessen auf 8 pro Knoten beschränkt, mit Ausnahme von Azure Synapse Analytics Gen2 mit 20 Leseprozessen pro Knoten. In Azure Synapse Analytics variiert die maximale Anzahl der Datenreaderprozesse pro Knoten je nach SLO. Weitere Informationen finden Sie unter Azure Synapse Analytics-Auslastungsmuster und -Strategien.

Beispiele

A. Erstellen eines externen DELIMITEDTEXT-Dateiformats

In diesem Beispiel wird für eine durch Trennzeichen getrennte Textdatei ein externes Dateiformat mit dem Namen textdelimited1 erstellt. Die für FORMAT_OPTIONS aufgeführten Optionen geben an, dass die Felder in der Datei durch einen senkrechten Strich | voneinander getrennt werden müssen. Die Textdatei wird auch mit dem GZip-Codec komprimiert. Wenn DATA_COMPRESSION nicht angegeben ist, wird die Textdatei dekomprimiert.

Bei einer durch Trennzeichen getrennten Textdatei kann die Datenkomprimierungsmethode entweder der Standardcodec org.apache.hadoop.io.compress.DefaultCodec oder der GZip-Codec org.apache.hadoop.io.compress.GzipCodec sein.

CREATE EXTERNAL FILE FORMAT textdelimited1
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = '|',
        DATE_FORMAT = 'MM/dd/yyyy' ),
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
);

B. Erstellen eines externen RCFile-Dateiformats

In diesem Beispiel wird ein externes Dateiformat für eine RCFile erstellt, das die serialization/deserialization-Methode org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe verwendet. Zudem wird angegeben, dass der Standard-Codec für die Datenkomprimierungsmethode verwendet werden soll. Wenn DATA_COMPRESSION nicht angegeben ist, ist keine Komprimierung der Standard.

CREATE EXTERNAL FILE FORMAT rcfile1
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe',
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec'
);

C. Erstellen eines externen ORC-Dateiformats

In diesem Beispiel wird für eine ORC-Datei ein externes Dateiformat erstellt, das die Daten mit der Datenkomprimierungsmethode org.apache.io.compress.SnappyCodec komprimiert. Wenn DATA_COMPRESSION nicht angegeben ist, ist keine Komprimierung der Standard.

CREATE EXTERNAL FILE FORMAT orcfile1
WITH (
    FORMAT_TYPE = ORC,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

D: Erstellen eines externen PARQUET-Dateiformats

In diesem Beispiel wird für eine Parquet-Datei ein externes Dateiformat erstellt, das die Daten mit der Datenkomprimierungsmethode org.apache.io.compress.SnappyCodec komprimiert. Wenn DATA_COMPRESSION nicht angegeben ist, ist keine Komprimierung der Standard.

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

E. Erstellen einer durch Trennzeichen getrennten Textdatei unter Auslassen der Kopfzeile

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

In diesem Beispiel wird für eine CSV-Datei mit einer einzelnen Kopfzeile ein externes Dateiformat erstellt. Weitere Informationen finden Sie unter Virtualisieren einer CSV-Datei mit PolyBase.

CREATE EXTERNAL FILE FORMAT skipHeader_CSV
WITH (FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS(
          FIELD_TERMINATOR = ',',
          STRING_DELIMITER = '"',
          FIRST_ROW = 2,
          USE_TYPE_DEFAULT = True)
);

F. Erstellen eines externen JSON-Dateiformats

Gilt für: Azure SQL Edge

In diesem Beispiel wird für eine JSON-Datei ein externes Dateiformat erstellt, das die Daten mit der Datenkomprimierungsmethode org.apache.io.compress.SnappyCodec komprimiert. Wenn DATA_COMPRESSION nicht angegeben ist, ist keine Komprimierung der Standard. Dieses Beispiel gilt für Azure SQL Edge und wird derzeit nicht für andere SQL-Produkte unterstützt.

CREATE EXTERNAL FILE FORMAT jsonFileFormat
WITH (
    FORMAT_TYPE = JSON,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

G. Erstellen eines externen Dateiformats für eine Delta-Tabelle

In diesem Beispiel wird ein externes Dateiformat für Delta-Tabellen erstellt. Dieses Beispiel gilt für SQL Server 2022 (16.x). Weitere Informationen finden Sie unter Virtualisieren einer Delta-Tabelle mit PolyBase.

CREATE EXTERNAL FILE FORMAT DeltaFileFormat
WITH (
    FORMAT_TYPE = DELTA
);

Zugehöriger Inhalt

• ERSTELLEN EINER EXTERNEN DATENQUELLE (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
• CREATE TABLE AS SELECT (Azure Synapse Analytics)
• sys.external_file_formats (Transact-SQL)