CREATE EXTERNAL FILE FORMAT (Transact-SQL)

Si applica a: SQL Server 2016 (13.x) e versioni successive Istanza gestita di SQL di Azure azure Synapse Analytics Analytics Platform System (PDW)

Crea un oggetto formato di file esterno che definisce i dati esterni archiviati in Hadoop, in Archiviazione BLOB di Azure o in Azure Data Lake Store o per i flussi di input e output associati a flussi esterni. La creazione di un formato di file esterno è un prerequisito per la creazione di una tabella esterna. Creando un formato di file esterno, si specifica il layout effettivo dei dati a cui fa riferimento una tabella esterna. Per creare una tabella esterna, vedere CREATE EXTERNAL TABLE (Transact-SQL).

Sono supportati i seguenti formati di file:

• Testo delimitato

• Hive RCFile

  Non si applica ad Azure Synapse Analytics, Istanza gestita di SQL di Azure o SQL Server 2022 (16.x).

• Hive ORC

  Non si applica ad Azure Synapse Analytics, Istanza gestita di SQL di Azure o SQL Server 2022 (16.x).

• Parquet

• JSON

  Si applica solo a SQL Edge di Azure. Per informazioni sull'uso di OPENROWSET per importare dati JSON in altre piattaforme, vedere Importare documenti JSON in SQL Server o Eseguire query su file JSON usando il pool SQL serverless in Azure Synapse Analytics.

• Delta

  Si applica solo ai pool SQL serverless in Azure Synapse Analytics e SQL Server 2022 (16.x).

Sintassi

Convenzioni relative alla sintassi Transact-SQL

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

Argomenti

file_format_name

Specifica un nome per il formato di file esterno.

FORMAT_TYPE

Specifica il formato dei dati esterni.

• FORMAT_TYPE = PARQUET

  specifica un formato Parquet.

• FORMAT_TYPE = ORC

  Specifica un formato ORC (Optimized Row Columnar). Questa opzione richiede Hive 0.11 o versione successiva nel cluster Hadoop esterno. In Hadoop il formato file ORC offre una migliore qualità di compressione e prestazioni rispetto al formato di file RCFILE.

• FORMAT_TYPE = RCFILE, SERDE_METHOD = SERDE_method

  Specifica un formato di file columnar di record (RcFile). Questa opzione richiede di specificare un metodo SerDe (Serializer and Deserializer) di Hive. Il requisito è lo stesso se si usa Hive/HiveQL in Hadoop per eseguire query sui file RC. Si noti che il metodo SerDe fa distinzione tra maiuscole e minuscole.

  Esempi di definizione di file RC con i due metodi SerDe supportati da PolyBase.

  • 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

  Specifica un formato di testo con delimitatori di colonna, detti anche caratteri di terminazione dei campi.

• FORMAT_TYPE = JSON

  Specifica un formato JSON. Si applica solo a SQL Edge di Azure.

• FORMAT_TYPE = DELTA

  Specifica un formato Delta Lake. Si applica ai pool SQL serverless in Azure Synapse Analytics e SQL Server 2022 (16.x).

FORMAT_OPTIONS

Facoltativo. Solo per i tipi di dati di testo delimitati.

Solo i pool SQL serverless in Azure Synapse Analytics supportano PARSER_VERSION.

I pool SQL serverless non supportano l'opzione DATE_FORMAT .

DATA_COMPRESSION = data_compression_method

Specifica il metodo di compressione dei dati per il file esterno. Quando DATA_COMPRESSION non è specificato, per impostazione predefinita vengono usati i dati non compressi.

Per funzionare correttamente, i file compressi Gzip devono avere estensione ".gz".

Opzioni per il formato di testo delimitato

Le opzioni di formato descritte in questa sezione sono facoltative e si applicano solo ai file di testo delimitato.

FIELD_TERMINATOR = field_terminator

Si applica solo ai file di testo delimitato. Il carattere di terminazione del campo specifica uno o più caratteri che contrassegnano la fine di ogni campo (colonna) nel file di testo delimitato. Il valore predefinito è il carattere pipe |. Per garantire il supporto, è consigliabile usare uno o più caratteri ASCII.

Esempi:

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

STRING_DELIMITER

STRING_DELIMITER = *string_delimiter*

Specifica il carattere di terminazione del campo per i dati di tipo stringa nel file di testo delimitato. Il delimitatore di stringa è costituito da uno o più caratteri ed è racchiuso tra virgolette singole. Il valore predefinito è la stringa vuota "". Per garantire il supporto, è consigliabile usare uno o più caratteri ASCII.

Esempi:

• STRING_DELIMITER = '"'

• STRING_DELIMITER = '0x22' (esadecimale virgoletta doppia)

• STRING_DELIMITER = '*'

• STRING_DELIMITER = ꞌ,ꞌ

• STRING_DELIMITER = '0x7E0x7E' (Due tilde, ad esempio , ~~)

FIRST_ROW = first_row_int

Si applica a: Azure Synapse Analytics, SQL Server 2022 e versioni successive

Specifica il numero di righe che verranno lette prima in tutti i file durante un caricamento di PolyBase. Questo parametro può accettare valori da 1 a 15. Se il valore è impostato su due, la prima riga di ogni file (riga di intestazione) viene ignorata quando i dati vengono caricati. Le righe vengono ignorate in base all'esistenza di caratteri di terminazione della riga (r/n, /r, /n). Quando si usa questa opzione per l'esportazione, le righe vengono aggiunti ai dati per garantire che il file possa essere letto senza perdere dati. Se il valore è impostato su >2, la prima riga esportata è quella dei nomi delle colonne della tabella esterna.

DATE_FORMAT = datetime_format

Specifica un formato personalizzato per tutti i dati di data e ora che potrebbero essere visualizzati in un file di testo delimitato. Se il file di origine usa formati di data e ora predefiniti, questa opzione non è necessaria. Per ogni file è consentito un solo formato di data e ora personalizzato. Non è possibile specificare più di un formato di data e ora personalizzato per ogni file. Tuttavia, è possibile usare più formati di data e ora se ognuno di essi è il formato predefinito per il rispettivo tipo di dati nella definizione della tabella esterna.

Se DATE_FORMAT non è specificata o è la stringa vuota, PolyBase usa i formati predefiniti seguenti:

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

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

• data: 'yyyy-MM-dd'

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

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

• time: 'HH:mm:ss'

Nella tabella seguente sono riportati esempi di formati di data:

Note sulla tabella:

• Anno, mese e giorno possono avere diversi formati e ordini. Nella tabella è riportato solo il formato ymd. Il mese può avere una o due cifre o tre caratteri. Il giorno può avere una o due cifre. L'anno può avere due o quattro cifre.

• I millisecondi (fffffff) non sono necessari.

• am o pm (tt) non è obbligatorio. Il valore predefinito è AM.

Formati di data e ora supportati

Il formato di file esterno può descrivere una quantità elevata di formati di data e ora:

Dettagli:

• Per separare i valori mese, giorno e anno, è necessario usare uno dei separatori seguenti: -, /o .. Per semplicità, nella tabella viene usato solo il separatore -.

• Per specificare il mese come testo, usare tre o più caratteri. I mesi con uno o due caratteri vengono interpretati come un numero.

• Per separare i valori di ora, usare il simbolo :.

• Le lettere tra parentesi quadre sono facoltative.

• Le lettere tt designano [AM|PM|am|pm]. AM è l'impostazione predefinita. Se si specifica tt, il valore dell'ora (hh) deve essere compreso nell'intervallo da 0 a 12.

• Le lettere zzz designano la differenza di fuso orario per il fuso orario corrente del sistema nel formato {+|-}HH:ss].

USE_TYPE_DEFAULT = { TRUE | FALSE }

Specifica come gestire valori mancanti nei file di testo delimitato quando PolyBase recupera i dati dal file di testo. Il valore predefinito è FALSE.

• TRUE

  Quando si recuperano dati dal file di testo, archiviare ogni valore mancante usando il valore predefinito per il tipo di dati della colonna corrispondente nella definizione della tabella esterna. Ad esempio, sostituire un valore mancante con:

  • 0 se la colonna è definita come colonna numerica. Le colonne decimali non sono supportate e avranno un errore.

  • Una stringa vuota "" se la colonna è una colonna stringa.

  • 1900-01-01 se la colonna è una colonna di data.

  • In Azure Synapse Analytics USE_TYPE_DEFAULT=true non è supportato per FORMAT_TYPE = DELIMITEDTEXT, PARSER_VERSION = '2.0'.

• FALSE

  Archiviare tutti i valori mancanti come NULL. Tutti i valori NULL archiviati usando la parola NULL nel file di testo delimitato vengono importati come stringa NULL.

ENCODING = {'UTF8' | 'UTF16'}

In Azure Synapse Analytics e nella piattaforma di strumenti analitici (PDW) (APS CU7.4) PolyBase è in grado di leggere file di testo delimitati con codifica UTF8 e UTF16-LE.

In SQL Server, PolyBase non supporta la lettura di file con codifica UTF16.

Autorizzazioni

Richiede l'autorizzazione ALTER ANY EXTERNAL FILE FORMAT.

Osservazioni:

Il formato di file esterno è con ambito database in SQL Server e Azure Synapse Analytics. È con ambito server nella piattaforma di strumenti analitici (PDW).

Le opzioni di formato sono tutte facoltative e si applicano solo ai file di testo delimitato.

Quando i dati vengono archiviati in uno dei formati compressi, PolyBase decomprime i dati prima di restituire i record di dati.

Limiti

Il delimitatore di riga nei file di testo delimitato deve essere supportato da LineRecordReader di Hadoop. Deve quindi essere \r, \n o \r\n. Questi delimitatori non sono configurabili dall'utente.

Le combinazioni dei metodi SerDe supportati con i file RC e i metodi di compressione dei dati supportati sono elencati in precedenza in questo articolo. Non tutte le combinazioni sono supportate.

Il numero massimo di query PolyBase simultanee è 32. Quando si eseguono contemporaneamente 32 query, ogni query è in grado di leggere al massimo 33.000 file dal percorso del file esterno. Anche la cartella radice e ogni sottocartella vengono considerate file. Se il livello di concorrenza è inferiore a 32, il percorso del file esterno può contenere più di 33.000 file.

A causa della limitazione del numero di file nella tabella esterna, si consiglia di archiviare meno di 30.000 file nella radice e nelle sottocartelle del percorso del file esterno. Si consiglia inoltre di usare un numero ridotto di sottocartelle nella cartella radice. Quando si fa riferimento a troppi file, può verificarsi un'eccezione di memoria insufficiente in Java Virtual Machine.

Quando si esportano dati in Hadoop o nell'archiviazione BLOB di Azure usando PolyBase, vengono esportati solo i dati e non i nomi di colonne (metadati) definiti nel comando CREATE EXTERNAL TABLE.

Blocco

Acquisisce un blocco condiviso per l'oggetto EXTERNAL FILE FORMAT.

Prestazioni

L'uso dei file compressi implica sempre un compromesso tra il trasferimento di una minore quantità di dati tra l'origine dati esterna e SQL Server e l'incremento dell'utilizzo della CPU per comprimere e decomprimere i dati.

I file di testo compressi Gzip non sono suddivisibili. Per migliorare le prestazioni dei file di testo compressi Gzip, si consiglia di generare più file da archiviare nella stessa directory all'interno dell'origine dati esterna. Questa struttura di file consente a PolyBase di leggere e decomprimere i dati più rapidamente usando più processi di lettura e decompressione. Il numero ideale di file compressi è il numero massimo di processi del lettore dati per ogni nodo di calcolo. In SQL Server e nella piattaforma di strumenti analitici (PDW) il numero massimo di processi del lettore dati è 8 per nodo, ad eccezione di Azure Synapse Analytics Gen2, per cui è 20 lettori per nodo. In Azure Synapse Analytics il numero massimo di processi del lettore dati per nodo varia in base a SLO. Per informazioni dettagliate, vedere il blog sui modelli e le strategie di caricamento di Azure Synapse Analytics.

Esempi

R. Creare un formato di file esterno DELIMITEDTEXT

In questo esempio viene creato un formato di file esterno denominato textdelimited1 per un file delimitato da testo. Le opzioni indicate per FORMAT_OPTIONS specificano che i campi nel file devono essere separati usando un carattere pipe |. Il file di testo viene anche compresso con il codec Gzip. Se non si specifica DATA_COMPRESSION, il file di testo non è compresso.

Per un file di testo delimitato, il metodo di compressione dei dati può essere il codec predefinito org.apache.hadoop.io.compress.DefaultCodec o il codec Gzip 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. Creare un formato di file esterno RCFILE

In questo esempio viene creato un formato di file esterno per RCFile che usa il metodo serialization/deserialization org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe. Specifica anche di usare il codec predefinito per il metodo di compressione dei dati. Se non si specifica DATA_COMPRESSION, per impostazione predefinita la compressione non viene usata.

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. Creare un formato di file esterno ORC

In questo esempio viene creato un formato di file esterno per un file ORC che comprime i dati con il metodo di compressione org.apache.io.compress.SnappyCodec. Se non si specifica DATA_COMPRESSION, per impostazione predefinita la compressione non viene usata.

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

D. Creare un formato di file esterno PARQUET

In questo esempio viene creato un formato di file esterno per un file Parquet che comprime i dati con il metodo di compressione org.apache.io.compress.SnappyCodec. Se non si specifica DATA_COMPRESSION, per impostazione predefinita la compressione non viene usata.

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

E. Creare un file di testo delimitato ignorando la riga di intestazione

Si applica a: Azure Synapse Analytics e SQL Server 2022 (16.x) e versioni successive.

In questo esempio viene creato un formato di file esterno per un file CSV con un'unica riga di intestazione. Per altre informazioni, vedere Virtualizzare un file CSV con 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. Creare un formato di file esterno JSON

Si applica a: SQL Edge di Azure.

Questo esempio crea un formato di file esterno per un file JSON che comprime i dati con il metodo di compressione org.apache.io.compress.SnappyCodec. Se non si specifica DATA_COMPRESSION, per impostazione predefinita la compressione non viene usata. Questo esempio si applica a SQL Edge di Azure e non è attualmente valido per altri prodotti SQL.

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

G. Creare un formato di file esterno di tipo tabella Delta

In questo esempio viene creato un formato di file esterno di tipo tabella Delta. Questo esempio si applica a SQL Server 2022 (16.x). Per altre informazioni, vedere Virtualizzare un file di tabella Delta con PolyBase.

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

Contenuto correlato

• CREATE EXTERNAL DATA SOURCE (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)