Dela via

SKAPA EXTERNT FILFORMAT (Transact-SQL)

gäller för: SQL Server 2016 (13.x) och senare Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Skapar ett externt filformatobjekt som definierar externa data som lagras i Hadoop, Azure Blob Storage, Azure Data Lake Store eller för in- och utdataströmmar som är associerade med externa strömmar. Att skapa ett externt filformat är en förutsättning för att skapa en extern tabell. Genom att skapa ett externt filformat anger du den faktiska layouten för de data som refereras av en extern tabell. Information om hur du skapar en extern tabell finns i SKAPA EXTERN TABELL (Transact-SQL).

Följande filformat stöds:

Transact-SQL syntaxkonventioner

Syntax

-- 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'}

}

Argumentpunkter

file_format_name

Anger ett namn för det externa filformatet.

FORMAT_TYP

Anger formatet för externa data.

  • FORMAT_TYPE = PARQUET

    Anger ett Parquet-format.

  • FORMAT_TYPE = ORC

    Anger ett ORC-format (Optimized Row Columnar). Det här alternativet kräver Hive version 0.11 eller senare i det externa Hadoop-klustret. I Hadoop erbjuder ORC-filformatet bättre komprimering och prestanda än RCFILE-filformatet.

  • FORMAT_TYPE = RCFILE, SERDE_METHOD = SERDE_method

    Anger filformatet Record Columnar (RcFile). Det här alternativet kräver att du anger en Hive Serializer- och Deserializer-metod (SerDe). Det här kravet är detsamma om du använder Hive/HiveQL i Hadoop för att fråga RC-filer. Observera att SerDe-metoden är skiftlägeskänslig.

    Exempel på att ange RCFile med de två SerDe-metoder som PolyBase stöder.

    • 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

    Anger ett textformat med kolumnavgränsare, även kallat fältavgränsare.

  • FORMAT_TYPE = JSON

    Anger ett JSON-format. Gäller endast för Azure SQL Edge.

  • FORMAT_TYPE = DELTA

    Anger ett Delta Lake-format. Gäller för serverlösa SQL-pooler i Azure Synapse Analytics och SQL Server 2022 (16.x).

FORMAT_OPTIONS

Valfritt. Endast för avgränsade textdatatyper.

Endast serverlösa SQL-pooler i Azure Synapse Analytics stöder PARSER_VERSION.

Serverlösa SQL-pooler stöder inte alternativet DATE_FORMAT .

DATA_COMPRESSION = data_compression_method

Anger datakomprimeringsmetod för externa data. När DATA_COMPRESSION inte har angetts är standardvärdet okomprimerade data.

För att fungera korrekt måste Gzip-komprimerade filer ha filnamnstillägget .gz .

Formattypen DELIMITEDTEXT stöder den här komprimeringsmetoden: DATA_COMPRESSION = org.apache.hadoop.io.compress.GzipCodec

Alternativ för avgränsat textformat

Formatalternativen som beskrivs i det här avsnittet är valfria och gäller endast för avgränsade textfiler.

FIELD_TERMINATOR = field_terminator

Gäller endast för avgränsade textfiler. Fältavgränsaren anger ett eller flera tecken som markerar slutet på varje fält (kolumn) i den textavgränsade filen. Standardvärdet är pipe-tecknet |. För garanterad support rekommenderar vi att du använder ett eller flera ASCII-tecken.

Exempel:

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

STRÄNGAVGRÄNSARE

STRING_DELIMITER = *string_delimiter*

Anger ett tecken som omger textvärdena i den textavgränsade filen. Standardvärdet är den tomma strängen "". Värdet för STRING_DELIMITER är funktionellt likvärdigt med alternativet FIELDQUOTE i OPENROWSET BULK funktionen.

Anmärkning

Med Hadoop-tabeller i Synapse-dedikerade SQL-pooler kan du ange ett eller flera tecken i STRING_DELIMITER. I en serverlös SQL-pool kan du bara använda ett tecken.

Exempel:

  • STRING_DELIMITER = '"'

  • STRING_DELIMITER = '0x22' (Dubbel citat hex)

  • STRING_DELIMITER = '*'

  • STRING_DELIMITER = ꞌ,ꞌ

  • STRING_DELIMITER = '0x7E0x7E' (Två tildes, till exempel ~~) – stöds i en dedikerad SQL-pool.

FIRST_ROW = first_row_int

Gäller för: Azure Synapse Analytics, SQL Server 2022 och senare versioner

Anger radnumret som läses först i alla filer under en PolyBase-inläsning. Den här parametern kan ta värdena 1–15. Om värdet är inställt på två hoppas den första raden i varje fil (rubrikrad) över när data läses in. Rader hoppas över baserat på förekomsten av radavgränsare (/r/n, /r, /n). När det här alternativet används för export läggs rader till i data för att se till att filen kan läsas utan dataförlust. Om värdet är inställt på >2 är den första raden som exporteras kolumnnamnen för den externa tabellen.

DATE_FORMAT = datetime_format

Anger ett anpassat format för alla datum- och tidsdata som kan visas i en avgränsad textfil. Om källfilen använder standardformat för datetime är det här alternativet inte nödvändigt. Endast ett anpassat datetime-format tillåts per fil. Du kan inte ange fler än ett anpassat datetime-format per fil. Du kan dock använda mer än ett datetime-format om var och en är standardformatet för sin respektive datatyp i den externa tabelldefinitionen.

Viktigt!

PolyBase använder endast det anpassade datumformatet för att importera data. Det använder inte det anpassade formatet för att skriva data till en extern fil.

När DATE_FORMAT inte har angetts eller är den tomma strängen använder PolyBase följande standardformat:

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

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

  • datum: 'yyyy-MM-dd'

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

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

  • tid: 'HH:mm:ss'

Viktigt!

Om du anger anpassade DATE_FORMAT åsidosättningar åsidosätts alla standardtypformat. Det innebär att du måste ha samma datumformat i alla datum-, datum- och tidsceller i dina filer. Med det åsidosatta DATE_FORMATkan du inte ha datum- och tidsvärden i olika format.

Exempel på datumformat finns i följande tabell:

Anteckningar om tabellen:

  • År, månad och dag kan ha flera format och beställningar. Tabellen visar endast formatet ymd . Månad kan innehålla en eller två siffror eller tre tecken. Day kan ha en eller två siffror. År kan ha två eller fyra siffror.

  • Millisekunder (fffffff) krävs inte.

  • am eller pm (tt) krävs inte. Standardvärdet är AM.

Datumtyp Exempel Beskrivning
datetime DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fff Förutom år, månad och dag innehåller det här datumformatet 00–24 timmar, 00–59 minuter, 00–59 sekunder och tre siffror för millisekunder.
datetime DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffftt Förutom år, månad och dag innehåller det här datumformatet 00–12 timmar, 00–59 minuter, 00–59 sekunder, tre siffror för millisekunder och AM, am, PMeller pm.
smalldatetime DATE_FORMAT = yyyy-MM-dd HH:mm Förutom år, månad och dag innehåller det här datumformatet 00–23 timmar, 00–59 minuter.
smalldatetime DATE_FORMAT = yyyy-MM-dd hh:mmtt Förutom år, månad och dag innehåller det här datumformatet 00–11 timmar, 00–59 minuter, inga sekunder och AM, am, PMeller pm.
datum DATE_FORMAT = yyyy-MM-dd År, månad och dag. Inget tidselement ingår.
datum DATE_FORMAT = yyyy-MMM-dd År, månad och dag. När månad anges med MMMär indatavärdet en eller strängarna, Jan, , FebMar, Apr, May, Jun, Jul, Aug, Sep, Oct, , Noveller Dec.
datetime2 DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fffffff Förutom år, månad och dag innehåller det här datumformatet 00–23 timmar, 00–59 minuter, 00–59 sekunder och sju siffror för millisekunder.
datetime2 DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffffffftt Förutom år, månad och dag innehåller det här datumformatet 00–11 timmar, 00–59 minuter, 00–59 sekunder, sju siffror för millisekunder och AM, am, PMeller pm.
datetimeoffset DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fffffff zzz Förutom år, månad och dag innehåller det här datumformatet 00–23 timmar, 00–59 minuter, 00–59 sekunder och 7 siffror för millisekunder och tidszonsförskjutningen som du anger i indatafilen som {+&#124;-}HH:ss. Eftersom Tiden i Los Angeles utan sommartid till exempel är 8 timmar efter UTC anger värdet -08:00 i indatafilen tidszonen för Los Angeles.
datetimeoffset DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffffffftt zzz Förutom år, månad och dag innehåller det här datumformatet 00–11 timmar, 00–59 minuter, 00–59 sekunder, sju siffror för millisekunder, (AM, am, PMeller pm) och tidszonsförskjutningen. Se beskrivningen i föregående rad.
tid DATE_FORMAT = HH:mm:ss Det finns inget datumvärde, bara 00–23 timmar, 00–59 minuter och 00–59 sekunder.

Datum- och tidsformat som stöds

Externt filformat kan beskriva ett stort antal datum- och tidsformat:

datetime smalldatetime datum datetime2 datetimeoffset
[M[M]]M-[d]d-[yy]yy HH:mm:ss[.fff] [M[M]]M-[d]d-[yy]yy HH:mm[:00] [M[M]]M-[d]d-[yy]yy [M[M]]M-[d]d-[yy]yy HH:mm:ss[.fffffff] [M[M]]M-[d]d-[yy]yy HH:mm:ss[.fffffff] zzz
[M[M]]M-[d]d-[yy]yy hh:mm:ss[.fff][tt] [M[M]]M-[d]d-[yy]yy hh:mm[:00][tt] [M[M]]M-[d]d-[yy]yy hh:mm:ss[.fffffff][tt] [M[M]]M-[d]d-[yy]yy hh:mm:ss[.fffffff][tt] zzz
[M[M]]M-[yy]yy-[d]d HH:mm:ss[.fff] [M[M]]M-[yy]yy-[d]d HH:mm[:00] [M[M]]M-[yy]yy-[d]d [M[M]]M-[yy]yy-[d]d HH:mm:ss[.fffffff] [M[M]]M-[yy]yy-[d]d HH:mm:ss[.fffffff] zzz
[M[M]]M-[yy]yy-[d]d hh:mm:ss[.fff][tt] [M[M]]M-[yy]yy-[d]d hh:mm[:00][tt] [M[M]]M-[yy]yy-[d]d hh:mm:ss[.fffffff][tt] [M[M]]M-[yy]yy-[d]d hh:mm:ss[.fffffff][tt] zzz
[yy]yy-[M[M]]M-[d]d HH:mm:ss[.fff] [yy]yy-[M[M]]M-[d]d HH:mm[:00] [yy]yy-[M[M]]M-[d]d [yy]yy-[M[M]]M-[d]d HH:mm:ss[.fffffff] [yy]yy-[M[M]]M-[d]d HH:mm:ss[.fffffff] zzz
[yy]yy-[M[M]]M-[d]d hh:mm:ss[.fff][tt] [yy]yy-[M[M]]M-[d]d hh:mm[:00][tt] [yy]yy-[M[M]]M-[d]d hh:mm:ss[.fffffff][tt] [yy]yy-[M[M]]M-[d]d hh:mm:ss[.fffffff][tt] zzz
[yy]yy-[d]d-[M[M]]M HH:mm:ss[.fff] [yy]yy-[d]d-[M[M]]M HH:mm[:00] [yy]yy-[d]d-[M[M]]M [yy]yy-[d]d-[M[M]]M HH:mm:ss[.fffffff] [yy]yy-[d]d-[M[M]]M HH:mm:ss[.fffffff] zzz
[yy]yy-[d]d-[M[M]]M hh:mm:ss[.fff][tt] [yy]yy-[d]d-[M[M]]M hh:mm[:00][tt] [yy]yy-[d]d-[M[M]]M hh:mm:ss[.fffffff][tt] [yy]yy-[d]d-[M[M]]M hh:mm:ss[.fffffff][tt] zzz
[d]d-[M[M]]M-[yy]yy HH:mm:ss[.fff] [d]d-[M[M]]M-[yy]yy HH:mm[:00] [d]d-[M[M]]M-[yy]yy [d]d-[M[M]]M-[yy]yy HH:mm:ss[.fffffff] [d]d-[M[M]]M-[yy]yy HH:mm:ss[.fffffff] zzz
[d]d-[M[M]]M-[yy]yy hh:mm:ss[.fff][tt] [d]d-[M[M]]M-[yy]yy hh:mm[:00][tt] [d]d-[M[M]]M-[yy]yy hh:mm:ss[.fffffff][tt] [d]d-[M[M]]M-[yy]yy hh:mm:ss[.fffffff][tt] zzz
[d]d-[yy]yy-[M[M]]M HH:mm:ss[.fff] [d]d-[yy]yy-[M[M]]M HH:mm[:00] [d]d-[yy]yy-[M[M]]M [d]d-[yy]yy-[M[M]]M HH:mm:ss[.fffffff] [d]d-[yy]yy-[M[M]]M HH:mm:ss[.fffffff] zzz
[d]d-[yy]yy-[M[M]]M hh:mm:ss[.fff][tt] [d]d-[yy]yy-[M[M]]M hh:mm[:00][tt] [d]d-[yy]yy-[M[M]]M hh:mm:ss[.fffffff][tt] [d]d-[yy]yy-[M[M]]M hh:mm:ss[.fffffff][tt] zzz

Information:

  • Om du vill separera värdena för månad, dag och år måste du använda någon av följande avgränsare: -, /eller .. För enkelhetens skull använder tabellen endast avgränsaren - .

  • Om du vill ange månaden som text använder du tre eller fler tecken. Månader med ett eller två tecken tolkas som ett tal.

  • Använd symbolen för att separera tidsvärden : .

  • Bokstäver som omges av hakparenteser är valfria.

  • Bokstäverna tt betecknar [AM|PM|am|pm]. AM används som standard. När tt anges måste timvärdet (hh) ligga i intervallet 0 till 12.

  • Bokstäverna zzz anger tidszonsförskjutningen för systemets aktuella tidszon i formatet {+|-}HH:ss].

USE_TYPE_DEFAULT = { TRUE | FALSE }

Anger hur du hanterar saknade värden i avgränsade textfiler när PolyBase hämtar data från textfilen. Standardvärdet är FALSE.

  • SANN

    När du hämtar data från textfilen lagrar du varje värde som saknas med hjälp av standardvärdet för datatypen för motsvarande kolumn i den externa tabelldefinitionen. Ersätt till exempel ett värde som saknas med:

    • 0 om kolumnen definieras som en numerisk kolumn. Decimalkolumner stöds inte och fel.

    • Tom sträng "" om kolumnen är en strängkolumn.

    • 1900-01-01 om kolumnen är en datumkolumn.

    • I Azure Synapse Analytics USE_TYPE_DEFAULT=true stöds inte för FORMAT_TYPE = DELIMITEDTEXT, PARSER_VERSION = '2.0'.

  • Falskt

    Lagra alla saknade värden som NULL. Alla NULL värden som lagras med hjälp av ordet NULL i den avgränsade textfilen importeras som strängen NULL.

ENCODING = {'UTF8' | 'UTF16'}

I Azure Synapse Analytics and Analytics Platform System (PDW) (APS CU7.4) kan PolyBase läsa UTF8 och UTF16-LE kodade avgränsade textfiler.

I SQL Server stöder PolyBase inte läsning av UTF16-kodade filer.

Behörigheter

Kräver ALTER ANY EXTERNAL FILE FORMAT permission (ÄNDRA BEHÖRIGHET FÖR EXTERNT FILFORMAT).

Anmärkningar

Det externa filformatet är databasomfattande i SQL Server och Azure Synapse Analytics. Det är serveromfattande i Analytics Platform System (PDW).

Formatalternativen är alla valfria och gäller endast för avgränsade textfiler.

När data lagras i något av de komprimerade formaten dekomprimerar PolyBase först data innan dataposterna returneras.

Begränsningar

Radavgränsaren i avgränsade textfiler måste stödjas av Hadoops LineRecordReader. Det vill: det måste vara antingen \r, \neller \r\n. Dessa avgränsare kan inte konfigureras av användaren.

Kombinationerna av SerDe-metoder som stöds med RCFiles och de datakomprimeringsmetoder som stöds visas tidigare i den här artikeln. Alla kombinationer stöds inte.

Det maximala antalet samtidiga PolyBase-frågor är 32. När 32 samtidiga frågor körs kan varje fråga läsa högst 33 000 filer från den externa filplatsen. Rotmappen och varje undermapp räknas också som en fil. Om samtidigheten är mindre än 32 kan den externa filplatsen innehålla mer än 33 000 filer.

På grund av begränsningen för antalet filer i den externa tabellen rekommenderar vi att du lagrar mindre än 30 000 filer i rot- och undermapparna på den externa filplatsen. Vi rekommenderar också att du håller antalet undermappar under rotkatalogen till ett litet antal. När du refererar till för många filer kan ett undantag för den virtuella Java-datorns minne uppstå.

När du exporterar data till Hadoop eller Azure Blob Storage via PolyBase exporteras endast data, inte kolumnnamnen (metadata) enligt definitionen i kommandot CREATE EXTERNAL TABLE.

Låsning

Tar ett delat lås på OBJEKTET EXTERNT FILFORMAT.

Prestanda

Att använda komprimerade filer medför alltid kompromissen mellan överföring av mindre data mellan den externa datakällan och SQL Server samtidigt som processoranvändningen ökar för att komprimera och dekomprimera data.

Gzip-komprimerade textfiler delas inte upp. För att förbättra prestandan för Gzip-komprimerade textfiler rekommenderar vi att du genererar flera filer som alla lagras i samma katalog i den externa datakällan. Med den här filstrukturen kan PolyBase läsa och dekomprimera data snabbare med hjälp av flera processer för läsare och dekomprimering. Det idealiska antalet komprimerade filer är det maximala antalet dataläsarprocesser per beräkningsnod. I SQL Server

I Analytics Platform System (PDW) är det maximala antalet dataläsarprocesser 8 per nod. I Azure Synapse Analytics är det maximala antalet dataläsarprocesser 20 läsare per nod.

Exempel

A. Skapa ett externt DELIMITEDTEXT-filformat

Det här exemplet skapar ett externt filformat med namnet textdelimited1 för en textavgränsad fil. Alternativen som anges för FORMAT_OPTIONS anger att fälten i filen ska avgränsas med hjälp av ett pipe-tecken |. Textfilen komprimeras också med Gzip-kodcen. Om DATA_COMPRESSION inte anges är textfilen inte komprimerad.

För en avgränsad textfil kan datakomprimeringsmetoden antingen vara standardmetoden Codec, org.apache.hadoop.io.compress.DefaultCodec, eller Gzip Codec, 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. Skapa ett externt RCFile-filformat

I det här exemplet skapas ett externt filformat för en RCFile som använder serialization/deserialization metoden org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe. Den anger också att du vill använda Standard codec för datakomprimeringsmetoden. Om DATA_COMPRESSION inte anges är standardvärdet ingen komprimering.

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. Skapa ett externt ORC-filformat

Det här exemplet skapar ett externt filformat för en ORC-fil som komprimerar data med org.apache.io.compress.SnappyCodec datakomprimeringsmetoden. Om DATA_COMPRESSION inte anges är standardvärdet ingen komprimering.

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

D. Skapa ett externt PARQUET-filformat

Det här exemplet skapar ett externt filformat för en Parquet-fil som komprimerar data med org.apache.io.compress.SnappyCodec datakomprimeringsmetoden. Om DATA_COMPRESSION inte anges är standardvärdet ingen komprimering.

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

E. Skapa en avgränsad textfil som hoppar över rubrikraden

Gäller för: Azure Synapse Analytics och SQL Server 2022 (16.x) och senare.

Det här exemplet skapar ett externt filformat för CSV-fil med en enda rubrikrad. Mer information finns i Virtualisera CSV-fil med 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. Skapa ett externt JSON-filformat

Gäller för: Azure SQL Edge.

Det här exemplet skapar ett externt filformat för en JSON-fil som komprimerar data med org.apache.io.compress.SnappyCodec datakomprimeringsmetoden. Om DATA_COMPRESSION inte anges är standardvärdet ingen komprimering. Det här exemplet gäller för Azure SQL Edge och stöds för närvarande inte för andra SQL-produkter.

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

G. Skapa ett externt filformat för Delta-tabellen

I det här exemplet skapas ett externt filformat för filformatet Delta-tabelltyp. Det här exemplet gäller för SQL Server 2022 (16.x). Mer information finns i Virtualisera deltatabell med PolyBase.

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

Relaterat innehåll