CREATE EXTERNAL FILE FORMAT (Transact-SQL)

van toepassing op: SQL Server 2016 (13.x) en hoger Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Hiermee maakt u een extern bestandsindelingsobject dat externe gegevens definieert die zijn opgeslagen in Hadoop, Azure Blob Storage, Azure Data Lake Store of voor de invoer- en uitvoerstromen die zijn gekoppeld aan externe streams. Het maken van een externe bestandsindeling is een vereiste voor het maken van een externe tabel. Door een externe bestandsindeling te maken, geeft u de werkelijke indeling op van de gegevens waarnaar wordt verwezen door een externe tabel. Zie CREATE EXTERNAL TABLE (Transact-SQL) om een externe tabel te maken.

De volgende bestandsindelingen worden ondersteund:

• Gescheiden tekst

• Hive RCFile

  Is niet van toepassing op Azure Synapse Analytics, Azure SQL Managed Instance, Azure SQL Database of SQL Server 2022 (16.x).

• Hive ORC

  Is niet van toepassing op Azure Synapse Analytics, Azure SQL Managed Instance, Azure SQL Database of SQL Server 2022 (16.x).

• Parket

• JSON

  Alleen van toepassing op Azure SQL Edge. Zie OPENROWSET of JSON-bestanden opvragen met behulp van een serverloze SQL-pool in Azure Synapse Analytics voor informatie over het gebruik van JSON-gegevens in andere platforms.

• Delta

  Is alleen van toepassing op serverloze SQL-pools in Azure Synapse Analytics, Azure SQL Database, SQL Server 2022 (16.x) en latere versies. U kunt een query uitvoeren op Delta Lake versie 1.0. Wijzigingen die zijn geïntroduceerd omdat in Delta Lake 1.2, zoals het wijzigen van de naam van kolommen, niet worden ondersteund. Als u de hogere versies van Delta gebruikt met verwijdervectoren, v2-controlepunten en andere functies, kunt u overwegen andere query-engines te gebruiken, zoals het Eindpunt van Microsoft Fabric SQL Analytics voor Lakehouses.

Transact-SQL syntaxis-conventies

Syntaxis

-- 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
CREATE EXTERNAL FILE FORMAT file_format_name
WITH (
         FORMAT_TYPE = DELTA
      );

Argumenten

file_format_name

Hiermee geeft u een naam voor de externe bestandsindeling.

FORMAT_TYPE

Hiermee geeft u de indeling van de externe gegevens.

• FORMAT_TYPE = PARQUET

  Hiermee geeft u een Parquet-indeling op.

• FORMAT_TYPE = ORC

  Hiermee geeft u een ORC-indeling (Optimized Row Columnar) op. Voor deze optie is Hive versie 0.11 of hoger vereist op het externe Hadoop-cluster. In Hadoop biedt de ORC-bestandsindeling betere compressie en prestaties dan de RCFILE-bestandsindeling.

• FORMAT_TYPE = RCFILE, SERDE_METHOD = SERDE_method

  Hiermee geeft u een Record Columnar-bestandsindeling (RcFile). Voor deze optie moet u een Hive Serializer- en Deserializer-methode (SerDe) opgeven. Deze vereiste is hetzelfde als u Hive/HiveQL in Hadoop gebruikt om query's uit te voeren op RC-bestanden. Opmerking: de SerDe-methode is hoofdlettergevoelig.

  Voorbeelden van het opgeven van RCFile met de twee SerDe-methoden die PolyBase ondersteunt.

  • 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

  Hiermee geeft u een tekstopmaak op met kolomscheidingstekens, ook wel veldeindtekens genoemd.

• FORMAT_TYPE = JSON

  Hiermee geeft u een JSON-indeling op. Alleen van toepassing op Azure SQL Edge.

• FORMAT_TYPE = DELTA

  Hiermee geeft u een Delta Lake-indeling op. Van toepassing op serverloze SQL-pools in Azure Synapse Analytics en SQL Server 2022 (16.x).

FORMAT_OPTIONS

Facultatief. Alleen voor gegevenstypen met scheidingstekens.

Alleen serverloze SQL-pools in Azure Synapse Analytics-ondersteuning PARSER_VERSION.

Serverloze SQL-pools bieden geen ondersteuning voor de DATE_FORMAT optie.

DATA_COMPRESSION = data_compression_method

Hiermee geeft u de methode voor gegevenscompressie voor de externe gegevens op. Wanneer DATA_COMPRESSION dit niet is opgegeven, worden de standaardgegevens niet gecomprimeerd.

Om goed te kunnen werken, moeten gecomprimeerde Gzip-bestanden de .gz bestandsextensie hebben.

Opties voor tekstopmaak met scheidingstekens

De indelingsopties die in deze sectie worden beschreven, zijn optioneel en zijn alleen van toepassing op tekstbestanden met scheidingstekens.

FIELD_TERMINATOR = field_terminator

Alleen van toepassing op tekstbestanden met scheidingstekens. Het veldeindteken geeft een of meer tekens op waarmee het einde van elk veld (kolom) in het bestand met tekstscheidingstekens wordt gemarkeerd. De standaardwaarde is het pipe-teken |. Voor gegarandeerde ondersteuning raden we u aan een of meer ASCII-tekens te gebruiken.

Voorbeelden:

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

STRING_DELIMITER

STRING_DELIMITER = *string_delimiter*

Hiermee geeft u een teken op dat de tekstwaarden in het bestand met tekstscheidingstekens plaatst. De standaardwaarde is de lege tekenreeks ''. De waarde van STRING_DELIMITER is functioneel gelijk aan de optie FIELDQUOTE in de OPENROWSET BULK functie.

Voorbeelden:

• STRING_DELIMITER = '"'

• STRING_DELIMITER = '0x22' (Dubbele aanhalingsteken hex)

• STRING_DELIMITER = '*'

• STRING_DELIMITER = ꞌ,ꞌ

• STRING_DELIMITER = '0x7E0x7E' (Twee tildes, bijvoorbeeld ~~) - ondersteund in een toegewezen SQL-pool.

FIRST_ROW = first_row_int

Van toepassing op: Azure Synapse Analytics, SQL Server 2022 en latere versies

Hiermee geeft u het rijnummer op dat als eerste wordt gelezen in alle bestanden tijdens het laden van PolyBase. Deze parameter kan waarden 1 - 15 aannemen. Als de waarde is ingesteld op twee, wordt de eerste rij in elk bestand (veldnamenrij) overgeslagen wanneer de gegevens worden geladen. Rijen worden overgeslagen op basis van het bestaan van rijeindtekens (/r/n, /r, /n). Wanneer deze optie wordt gebruikt voor het exporteren, worden rijen toegevoegd aan de gegevens om ervoor te zorgen dat het bestand zonder gegevensverlies kan worden gelezen. Als de waarde is ingesteld op >2, is de eerste rij die wordt geëxporteerd de kolomnamen van de externe tabel.

DATE_FORMAT = datetime_format

Hiermee geeft u een aangepaste indeling op voor alle datum- en tijdgegevens die kunnen worden weergegeven in een tekstbestand met scheidingstekens. Als het bronbestand standaard datum/tijd-indelingen gebruikt, is deze optie niet nodig. Er is slechts één aangepaste datum/tijd-indeling toegestaan per bestand. U kunt niet meer dan één aangepaste datum/tijd-indeling per bestand opgeven. U kunt echter meer dan één datum/tijd-notatie gebruiken als elk de standaardindeling is voor het respectieve gegevenstype in de definitie van de externe tabel.

Wanneer DATE_FORMAT niet is opgegeven of de lege tekenreeks is, gebruikt PolyBase de volgende standaardindelingen:

• datum/tijd-: 'yyyy-MM-dd HH:mm:ss'

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

• datum: 'yyyy-MM-dd'

• datum/tijd2-: 'yyyy-MM-dd HH:mm:ss'

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

• tijd: 'HH:mm:ss'

Voorbeelddatumnotaties staan in de volgende tabel:

Opmerkingen over de tabel:

• Jaar, maand en dag kunnen verschillende indelingen en orders hebben. In de tabel wordt alleen de indeling ymd weergegeven. Maand kan één of twee cijfers of drie tekens bevatten. Dag kan één of twee cijfers hebben. Het jaar kan twee of vier cijfers hebben.

• Milliseconden (fffffff) zijn niet vereist.

• am of pm (tt) is niet vereist. De standaardwaarde is AM.

Ondersteunde datum- en tijdnotaties

Externe bestandsindeling kan een groot aantal datum- en tijdnotaties beschrijven:

Bijzonderheden:

• Als u waarden voor maand, dag en jaar wilt scheiden, moet u een van de volgende scheidingstekens gebruiken: -, /of .. Ter vereenvoudiging gebruikt de tabel alleen het - scheidingsteken.

• Als u de maand als tekst wilt opgeven, gebruikt u drie of meer tekens. Maanden met een of twee tekens worden geïnterpreteerd als een getal.

• Als u tijdwaarden wilt scheiden, gebruikt u het : symbool.

• Letters tussen vierkante haken zijn optioneel.

• De brieven tt wijzen [AM|PM|am|pm] aan. AM is de standaardwaarde. Wanneer tt dit is opgegeven, moet de uurwaarde (hh) binnen het bereik van 0 tot 12 liggen.

• De letters zzz wijzen de tijdzoneverschil aan voor de huidige tijdzone van het systeem in de notatie {+|-}HH:ss].

USE_TYPE_DEFAULT = { TRUE | ONWAAR }

Hiermee geeft u op hoe ontbrekende waarden in tekstbestanden met scheidingstekens moeten worden verwerkt wanneer PolyBase gegevens ophaalt uit het tekstbestand. De standaardwaarde is FALSE.

• WAAR

  Bij het ophalen van gegevens uit het tekstbestand slaat u elke ontbrekende waarde op met behulp van de standaardwaarde voor het gegevenstype van de bijbehorende kolom in de definitie van de externe tabel. Vervang bijvoorbeeld een ontbrekende waarde door:

  • 0 als de kolom is gedefinieerd als een numerieke kolom. Decimale kolommen worden niet ondersteund en er wordt een fout weergegeven.

  • Lege tekenreeks '' als de kolom een tekenreekskolom is.

  • 1900-01-01 als de kolom een datumkolom is.

  • In Azure Synapse Analytics USE_TYPE_DEFAULT=true wordt dit niet ondersteund voor FORMAT_TYPE = DELIMITEDTEXT, PARSER_VERSION = '2.0'.

• ONWAAR

  Sla alle ontbrekende waarden op als NULL. Waarden NULL die zijn opgeslagen met behulp van het woord NULL in het tekstbestand met scheidingstekens, worden als tekenreeks NULLgeïmporteerd.

ENCODING = {'UTF8' | 'UTF16'}

In Azure Synapse Analytics and Analytics Platform System (PDW) (APS CU7.4) kan PolyBase UTF8 en UTF16-LE gecodeerde tekstbestanden lezen.

In SQL Server biedt PolyBase geen ondersteuning voor het lezen van met UTF16 gecodeerde bestanden.

Machtigingen

Hiervoor is DE MACHTIGING ALTER ANY EXTERNAL FILE FORMAT vereist.

Opmerkingen

De externe bestandsindeling is databasebereik in SQL Server en Azure Synapse Analytics. Het is serverbereikd in Analytics Platform System (PDW).

De indelingsopties zijn allemaal optioneel en zijn alleen van toepassing op tekstbestanden met scheidingstekens.

Wanneer de gegevens worden opgeslagen in een van de gecomprimeerde indelingen, worden de gegevens eerst gedecomprimeerd voordat de gegevensrecords worden geretourneerd.

Beperkingen

Het rijscheidingsteken in tekstbestanden met scheidingstekens moet worden ondersteund door LineRecordReader van Hadoop. Dat wil gezegd, het moet \rofwel , \nof \r\n. Deze scheidingstekens kunnen niet door de gebruiker worden geconfigureerd.

De combinaties van ondersteunde SerDe-methoden met RCFiles en de ondersteunde methoden voor gegevenscompressie worden eerder in dit artikel vermeld. Niet alle combinaties worden ondersteund.

Het maximum aantal gelijktijdige PolyBase-query's is 32. Wanneer 32 gelijktijdige query's worden uitgevoerd, kan elke query maximaal 33.000 bestanden lezen vanaf de locatie van het externe bestand. De hoofdmap en elke submap tellen ook als een bestand. Als de mate van gelijktijdigheid kleiner is dan 32, kan de locatie van het externe bestand meer dan 33.000 bestanden bevatten.

Vanwege de beperking van het aantal bestanden in de externe tabel, raden we u aan minder dan 30.000 bestanden op te slaan in de hoofdmap en submappen van de externe bestandslocatie. Het is ook raadzaam om het aantal submappen onder de hoofdmap op een klein aantal te houden. Wanneer er te veel bestanden naar worden verwezen, kan er een uitzondering voor onvoldoende geheugen optreden voor een Virtuele Java-machine.

Wanneer u gegevens exporteert naar Hadoop of Azure Blob Storage via PolyBase, worden alleen de gegevens geëxporteerd, niet de kolomnamen (metagegevens) zoals gedefinieerd in de opdracht CREATE EXTERNAL TABLE.

Vergrendeling

Neemt een gedeelde vergrendeling op het object EXTERNAL FILE FORMAT.

Prestatie

Het gebruik van gecomprimeerde bestanden wordt altijd geleverd met de afweging tussen het overdragen van minder gegevens tussen de externe gegevensbron en SQL Server, terwijl het CPU-gebruik wordt verhoogd om de gegevens te comprimeren en decomprimeren.

Gecomprimeerde Gzip-tekstbestanden zijn niet gesplitst. Om de prestaties voor gecomprimeerde Gzip-tekstbestanden te verbeteren, raden we u aan om meerdere bestanden te genereren die allemaal zijn opgeslagen in dezelfde map in de externe gegevensbron. Met deze bestandsstructuur kan PolyBase de gegevens sneller lezen en decomprimeren met behulp van meerdere lezer- en decompressieprocessen. Het ideale aantal gecomprimeerde bestanden is het maximum aantal processen van gegevenslezers per rekenknooppunt. In SQL Server

In Analytics Platform System (PDW) is het maximum aantal processen voor gegevenslezers 8 per knooppunt. In Azure Synapse Analytics is het maximum aantal processen voor gegevenslezers 20 lezers per knooppunt.

Voorbeelden

Eén. Een externe bestandsindeling VOOR DELIMITEDTEXT maken

In dit voorbeeld wordt een externe bestandsindeling met de naam textdelimited1 gemaakt voor een bestand met tekstscheidingstekens. De opties voor FORMAT_OPTIONS geven aan dat de velden in het bestand moeten worden gescheiden met behulp van een sluisteken |. Het tekstbestand wordt ook gecomprimeerd met de Gzip-codec. Als DATA_COMPRESSION dit niet is opgegeven, wordt het tekstbestand niet gecomprimeerd.

Voor een tekstbestand met scheidingstekens kan de methode voor gegevenscompressie de standaardcodec of org.apache.hadoop.io.compress.DefaultCodecde Gzip-codec zijn. org.apache.hadoop.io.compress.GzipCodec

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. Een externe RCFile-bestandsindeling maken

In dit voorbeeld wordt een externe bestandsindeling gemaakt voor een RCFile die gebruikmaakt van de serialization/deserialization methode org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe. Er wordt ook aangegeven dat de standaardcodec moet worden gebruikt voor de methode voor gegevenscompressie. Als DATA_COMPRESSION dit niet is opgegeven, is de standaardwaarde geen compressie.

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. Een externe ORC-bestandsindeling maken

In dit voorbeeld wordt een externe bestandsindeling gemaakt voor een ORC-bestand waarmee de gegevens worden gecomprimeerd met de org.apache.io.compress.SnappyCodec methode voor gegevenscompressie. Als DATA_COMPRESSION dit niet is opgegeven, is de standaardwaarde geen compressie.

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

D. Een externe PARQUET-bestandsindeling maken

In dit voorbeeld wordt een externe bestandsindeling gemaakt voor een Parquet-bestand waarmee de gegevens worden gecomprimeerd met de org.apache.io.compress.SnappyCodec methode voor gegevenscompressie. Als DATA_COMPRESSION dit niet is opgegeven, is de standaardwaarde geen compressie.

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

E. Een tekstbestand met scheidingstekens maken om de veldnamenrij te overslaan

Van toepassing op: Azure Synapse Analytics en SQL Server 2022 (16.x) en hoger.

In dit voorbeeld wordt een externe bestandsindeling voor een CSV-bestand gemaakt met één veldnamenrij. Zie Csv-bestand virtualiseren met PolyBase voor meer informatie.

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

F. Een externe JSON-bestandsindeling maken

Van toepassing op: Azure SQL Edge.

In dit voorbeeld wordt een externe bestandsindeling gemaakt voor een JSON-bestand waarmee de gegevens worden gecomprimeerd met de org.apache.io.compress.SnappyCodec methode voor gegevenscompressie. Als DATA_COMPRESSION dit niet is opgegeven, is de standaardwaarde geen compressie. Dit voorbeeld is van toepassing op Azure SQL Edge en wordt momenteel niet ondersteund voor andere SQL-producten.

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

G. Een externe bestandsindeling voor een Delta-tabel maken

In dit voorbeeld wordt een externe bestandsindeling gemaakt voor de bestandsindeling van deltatabeltypen. Dit voorbeeld is van toepassing op SQL Server 2022 (16.x). Zie De deltatabel virtualiseren met PolyBase voor meer informatie.

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

Verwante inhoud

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREËER EXTERNE TABEL (Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)
• TABEL AANMAKEN ALS SELECTEREN
• sys.external_file_formats (Transact-SQL)