CREATE EXTERNAL FILE FORMAT (Transact-SQL)CREATE EXTERNAL FILE FORMAT (Transact-SQL)

S’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) et ultérieures OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance ouiAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics ouiParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data WarehouseS’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance ouiAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics ouiParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

Crée un objet format de fichier externe qui définit les données externes stockées dans Hadoop, Stockage Blob Azure ou Azure Data Lake Store, ou pour les flux d’entrée et de sortie associés à des flux externes.Creates an External File Format object defining external data stored in Hadoop, Azure Blob Storage, Azure Data Lake Store or for the input and output streams associated with External Streams. La création d’un format de fichier externe est un prérequis à la création d’une table externe.Creating an external file format is a prerequisite for creating an External Table. En créant un format de fichier externe, vous spécifiez la disposition des données référencées par une table externe.By creating an External File Format, you specify the actual layout of the data referenced by an external table.

Les formats de fichier suivants sont pris en charge :The following file formats are supported:

  • Texte délimitéDelimited Text

  • Hive RCFileHive RCFile

  • Hive ORCHive ORC

  • ParquetParquet

  • JSON - S’applique uniquement à Azure SQL Edge.JSON - Applies to Azure SQL Edge only.

Pour créer une table externe, consultez CREATE EXTERNAL TABLE (Transact-SQL).To create an External Table, see CREATE EXTERNAL TABLE (Transact-SQL).

Icône du lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

-- 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'  
         | 'org.apache.hadoop.io.compress.DefaultCodec'  
        }  
     ]);

<format_options> ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter 
    | First_Row = integer -- ONLY AVAILABLE SQL DW
    | DATE_FORMAT = datetime_format  
    | USE_TYPE_DEFAULT = { TRUE | FALSE } 
    | Encoding = {'UTF8' | 'UTF16'} 
}

ArgumentsArguments

file_format_namefile_format_name
Spécifie un nom pour le format de fichier externe.Specifies a name for the external file format.

FORMAT_TYPEFORMAT_TYPE

FORMAT_TYPE = [ PARQUET | ORC | RCFILE | DELIMITEDTEXT]

Spécifie le format des données externes.Specifies the format of the external data.

  • PARQUET Spécifie un format Parquet.PARQUET Specifies a Parquet format.

  • ORCORC
    Spécifie un format ORC.Specifies an Optimized Row Columnar (ORC) format. Cette option nécessite Hive version 0.11 ou version ultérieure dans le cluster Hadoop externe.This option requires Hive version 0.11 or higher on the external Hadoop cluster. Dans Hadoop, le format de fichier ORC offre une meilleure compression et de meilleures performances que le format de fichier RCFILE.In Hadoop, the ORC file format offers better compression and performance than the RCFILE file format.

  • RCFILE (en association avec SERDE_METHOD = SERDE_method) Spécifie un format de fichier RcFile.RCFILE (in combination with SERDE_METHOD = SERDE_method) Specifies a Record Columnar file format (RcFile). Cette option nécessite que vous spécifiiez un sérialiseur Hive et la méthode de désérialiseur (SerDe).This option requires you to specify a Hive Serializer and Deserializer (SerDe) method. Cette exigence est la même si vous utilisez Hive/HiveQL dans Hadoop pour interroger les fichiers RC.This requirement is the same if you use Hive/HiveQL in Hadoop to query RC files. Notez que la méthode SerDe respecte la casse.Note, the SerDe method is case-sensitive.

    Exemples de spécification RCFile avec les deux méthodes SerDe prises en charge par PolyBase :Examples of specifying RCFile with the two SerDe methods that PolyBase supports.

    • FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'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 = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'

  • DELIMITEDTEXT spécifie un format de texte avec des délimiteurs de colonne, également appelés « marques de fin de champ ».DELIMITEDTEXT Specifies a text format with column delimiters, also called field terminators.

  • JSON spécifie un format JSON.JSON Specifies a JSON format. S’applique uniquement à Azure SQL Edge.Applies to Azure SQL Edge only.

DATA_COMPRESSIONDATA_COMPRESSION

DATA_COMPRESSION = *data_compression_method*
Spécifie la méthode de compression appliquée aux données externes.Specifies the data compression method for the external data. Lorsque DATA_COMPRESSION n’est pas spécifié, par défaut, les données ne sont pas compressées.When DATA_COMPRESSION isn't specified, the default is uncompressed data. Pour fonctionner correctement, les fichiers compressés avec Gzip doivent avoir l’extension de fichier « .gz ».To work properly, Gzip compressed files must have the ".gz" file extension.

Le type de format DELIMITEDTEXT prend en charge les méthodes de compression suivantes :The DELIMITEDTEXT format type supports these compression methods:

  • DATA COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec'DATA COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec'
  • DATA COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'DATA COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'

Options de format de texte délimitéDelimited text format options

Les options de format décrites dans cette section sont facultatives et s’appliquent uniquement aux fichiers texte délimité.The format options described in this section are optional and only apply to delimited text files.

FIELD_TERMINATORFIELD_TERMINATOR

FIELD_TERMINATOR = *field_terminator*
S’applique uniquement aux fichiers texte délimité.Applies only to delimited text files. La marque de fin de champ spécifie un ou plusieurs caractères qui indiquent la fin de chaque champ (colonne) dans le fichier texte délimité.The field terminator specifies one or more characters that mark the end of each field (column) in the text-delimited file. La valeur par défaut est le caractère ꞌ|ꞌ.The default is the pipe character ꞌ|ꞌ. Pour une prise en charge garantie, nous vous recommandons d’utiliser un ou plusieurs caractères ASCII.For guaranteed support, we recommend using one or more ascii characters.

Exemples :Examples:

  • FIELD_TERMINATOR = '|'FIELD_TERMINATOR = '|'

  • FIELD_TERMINATOR = ' 'FIELD_TERMINATOR = ' '

  • FIELD_TERMINATOR = ꞌ\tꞌFIELD_TERMINATOR = ꞌ\tꞌ

  • FIELD_TERMINATOR = '~|~'FIELD_TERMINATOR = '~|~'

STRING_DELIMITERSTRING_DELIMITER

STRING_DELIMITER = *string_delimiter*

Spécifie la marque de fin de champ pour les données de type chaîne du fichier texte délimité.Specifies the field terminator for data of type string in the text-delimited file. Le délimiteur de chaîne est constitué d’un ou plusieurs caractères, et est entouré de guillemets simples.The string delimiter is one or more characters in length and is enclosed with single quotes. La valeur par défaut est la chaîne vide "".The default is the empty string "". Pour une prise en charge garantie, nous vous recommandons d’utiliser un ou plusieurs caractères ASCII.For guaranteed support, we recommend using one or more ascii characters.

Exemples :Examples:

  • STRING_DELIMITER = '"'STRING_DELIMITER = '"'

  • STRING_DELIMITER = '0 x 22'-- valeur hexadécimale de guillemets doublesSTRING_DELIMITER = '0x22' -- Double quote hex

  • STRING_DELIMITER = '*'STRING_DELIMITER = '*'

  • STRING_DELIMITER = ꞌ,ꞌSTRING_DELIMITER = ꞌ,ꞌ

  • STRING_DELIMITER = '0x7E0x7E'--deux tildes (par exemple, ~~)STRING_DELIMITER = '0x7E0x7E' -- Two tildes (for example, ~~)

FIRST_ROWFIRST_ROW

FIRST_ROW = *First_row_int*
Spécifie le numéro de la ligne qui est lue en premier dans tous les fichiers lors d’un chargement PolyBase.Specifies the row number that is read first in all files during a PolyBase load. Ce paramètre peut prendre les valeurs allant de 1 à 15.This parameter can take values 1-15. Si la valeur est définie sur 2, la première ligne de chaque fichier (ligne d’en-tête) est ignorée lorsque les données sont chargées.If the value is set to two, the first row in every file (header row) is skipped when the data is loaded. Les lignes sont ignorées en présence de marques de fin de ligne (/r/n, /r, /n).Rows are skipped based on the existence of row terminators (/r/n, /r, /n). Lorsque cette option est utilisée pour l’exportation, les lignes sont ajoutées aux données pour garantir que le fichier pourra être lu sans perte de données.When this option is used for export, rows are added to the data to make sure the file can be read with no data loss. Si la valeur est définie sur > 2, la première ligne exportée est celle des noms de colonnes de la table externe.If the value is set to >2, the first row exported is the Column names of the external table.

DATE_FORMATDATE_FORMAT

DATE_FORMAT = *datetime_format*
Spécifie un format personnalisé pour toutes les données de date et d’heure qui peuvent apparaître dans un fichier texte délimité.Specifies a custom format for all date and time data that might appear in a delimited text file. Si le fichier source utilise des formats DateHeure par défaut, cette option n’est pas nécessaire.If the source file uses default datetime formats, this option isn't necessary. Un seul format DateHeure personnalisé est autorisé par fichier.Only one custom datetime format is allowed per file. Vous ne pouvez pas spécifier plusieurs formats DateHeure personnalisés dans un même fichier.You can't specify more than one custom datetime formats per file. Toutefois, vous pouvez utiliser plusieurs formats DateHeure si chacun d’eux est le format par défaut de son type de données dans la définition de la table externe.However, you can use more than one datetime formats if each one is the default format for its respective data type in the external table definition.

Important

PolyBase utilise uniquement le format de date personnalisé pour l’importation des données.PolyBase only uses the custom date format for importing the data. Il n’utilise pas le format personnalisé pour l’écriture de données dans un fichier externe.It doesn't use the custom format for writing data to an external file.

Lorsque DATE_FORMAT n’est pas spécifié ou est une chaîne vide, PolyBase utilise les formats par défaut suivants :When DATE_FORMAT isn't specified or is the empty string, PolyBase uses the following default formats:

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

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

  • Date : 'yyyy-MM-dd'Date: 'yyyy-MM-dd'

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

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

  • Time : 'HH:mm:ss'Time: 'HH:mm:ss'

Important

Si vous spécifiez un format DATE_FORMAT personnalisé, tous les formats de type par défaut seront remplacés.Specifying custom DATE_FORMAT will override all default type formats. Cela signifie que vous devrez avoir les mêmes formats de date dans toutes les cellules datetime, date et time de vos fichiers.This means that you will need to have the same date formats in all datetime, date, and time cells in your files. Avec le format DATE_FORMAT de remplacement, les valeurs de date et d’heure ne peuvent pas avec un format différent.With the overriden DATE_FORMAT you cannot have date and time values in different format.

Des exemples de formats de date se trouvent dans le tableau suivant :Example date formats are in the following table:

Remarques sur ce tableau :Notes about the table:

  • Les années, les mois et les jours peuvent avoir de nombreux formats et ordres différents.Year, month, and day can have a variety of formats and orders. Le tableau présente uniquement le format ymd.The table shows only the ymd format. Les mois peuvent avoir un ou deux chiffres, ou trois caractères.Month can have one or two digits, or three characters. Les jours peuvent avoir un ou deux chiffres.Day can have one or two digits. Les années peuvent comporter deux ou quatre chiffres.Year can have two or four digits.

  • Les millisecondes (fffffff) ne sont pas obligatoires.Milliseconds (fffffff) are not required.

  • La mention du matin ou de l’après-midi (tt) n’est pas obligatoire.Am, pm (tt) isn't required. Par défaut, l’heure du matin (AM) est utilisée.The default is AM.

Type de dateDate Type ExempleExample DescriptionDescription
DateTimeDateTime DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fff'DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fff' Outre l’année, le mois et le jour, ce format de date inclut de 00 à 24 heures, de 00 à 59 minutes, de 00 à 59 secondes, ainsi que 3 chiffres pour les millisecondes.In addition to year, month and day, this date format includes 00-24 hours, 00-59 minutes, 00-59 seconds, and 3 digits for milliseconds.
DateTimeDateTime DATE_FORMAT = 'yyyy-MM-dd hh:mm:ss.ffftt'DATE_FORMAT = 'yyyy-MM-dd hh:mm:ss.ffftt' Outre l’année, le mois et le jour, ce format de date inclut de 00 à 12 heures, de 00 à 59 minutes, de 00 à 59 secondes, 3 chiffres pour les millisecondes, ainsi que la mention AM, am, PM ou pm.In addition to year, month and day, this date format includes 00-12 hours, 00-59 minutes, 00-59 seconds, 3 digits for milliseconds, and AM, am, PM, or pm.
SmallDateTimeSmallDateTime DATE_FORMAT = 'yyyy-MM-dd HH:mm'DATE_FORMAT = 'yyyy-MM-dd HH:mm' Outre l’année, le mois et le jour, ce format de date inclut de 00 à 23 heures et de 00 à 59 minutes.In addition to year, month, and day, this date format includes 00-23 hours, 00-59 minutes.
SmallDateTimeSmallDateTime DATE_FORMAT = 'yyyy-MM-dd hh:mmtt'DATE_FORMAT = 'yyyy-MM-dd hh:mmtt' Outre l’année, le mois et le jour, ce format de date inclut de 00 à 11 heures, de 00 à 59 minutes, pas de secondes, et la mention AM, am, PM ou pm.In addition to year, month, and day, this date format includes 00-11 hours, 00-59 minutes, no seconds, and AM, am, PM, or pm.
DateDate DATE_FORMAT = 'yyyy-MM-dd'DATE_FORMAT = 'yyyy-MM-dd' L’année, le mois et le jour.Year, month, and day. Aucun élément d’heure n’est inclus.No time element is included.
DateDate DATE_FORMAT = 'yyyy-MMM-dd'DATE_FORMAT = 'yyyy-MMM-dd' L’année, le mois et le jour.Year, month, and day. Quand le mois est spécifié avec 3 M, la valeur d’entrée est une des chaînes suivantes : Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov ou Dec.When month is specified with 3 M's, the input value is one or the strings Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, or Dec.
DateTime2DateTime2 DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fffffff'DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fffffff' Outre l’année, le mois et le jour, ce format de date inclut de 00 à 23 heures, de 00 à 59 minutes, de 00 à 59 secondes, ainsi que 7 chiffres pour les millisecondes.In addition to year, month, and day, this date format includes 00-23 hours, 00-59 minutes, 00-59 seconds, and 7 digits for milliseconds.
DateTime2DateTime2 DATE_FORMAT = 'yyyy-MM-dd hh:mm:ss.ffffffftt'DATE_FORMAT = 'yyyy-MM-dd hh:mm:ss.ffffffftt' Outre l’année, le mois et le jour, ce format de date inclut de 00 à 11 heures, de 00 à 59 minutes, de 00 à 59 secondes, 7 chiffres pour les millisecondes, ainsi que la mention AM, am, PM ou pm.In addition to year, month, and day, this date format includes 00-11 hours, 00-59 minutes, 00-59 seconds, 7 digits for milliseconds, and AM, am, PM, or pm.
DateTimeOffsetDateTimeOffset DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fffffff zzz'DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fffffff zzz' Outre l’année, le mois et le jour, ce format de date inclut de 00 à 23 heures, de 00 à 59 minutes, de 00 à 59 secondes, 7 chiffres pour les millisecondes, ainsi que le décalage des fuseaux horaires que vous placez dans le fichier ainsi : {+&#124;-}HH:ss.In addition to year, month, and day, this date format includes 00-23 hours, 00-59 minutes, 00-59 seconds, and 7 digits for milliseconds, and the timezone offset which you put in the input file as {+&#124;-}HH:ss. Par exemple, étant donné que l’heure de Los Angeles, sans l’heure d’été, est en retard de 8 heures par rapport à l’heure UTC, la valeur -08:00, dans le fichier d’entrée va spécifier le fuseau horaire de Los Angeles.For example, since Los Angeles time without daylight savings is 8 hours behind UTC, a value of -08:00 in the input file specifies the timezone for Los Angeles.
DateTimeOffsetDateTimeOffset DATE_FORMAT = 'yyyy-MM-dd hh:mm:ss.ffffffftt zzz'DATE_FORMAT = 'yyyy-MM-dd hh:mm:ss.ffffffftt zzz' Outre l’année, le mois et le jour, ce format de date inclut de 00 à 11 heures, de 00 à 59 minutes, de 00 à 59 secondes, 7 chiffres pour les millisecondes, la mention AM, am, PM ou pm, et le décalage des fuseaux horaires.In addition to year, month, and day, this date format includes 00-11 hours, 00-59 minutes, 00-59 seconds, 7 digits for milliseconds, (AM, am, PM, or pm), and the timezone offset. Voir la description à la ligne précédente.See the description in the previous row.
TempsTime DATE_FORMAT = 'HH:mm:ss'DATE_FORMAT = 'HH:mm:ss' Il n’existe aucune valeur de date, seulement de 00 à 23 heures, de 00 à 59 minutes et de 00 à 59 secondes.There is no date value, only 00-23 hours, 00-59 minutes, and 00-59 seconds.
Formats de date et d’heure pris en chargeSupported date and time formats

Le format de fichier externe peut décrire un grand nombre de formats de date et d’heure :External file format can describe a large number of date and time formats:

DATETIMEdatetime smalldatetimesmalldatetime Datedate datetime2datetime2 datetimeoffsetdatetimeoffset
[M[M]]M-[d]d-[yy]yy HH:mm:ss[.fff][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 HH:mm[:00] [M[M]]M-[d]d-[yy]yy[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] [M[M]]M-[d]d-[yy]yy HH:mm:ss[.fffffff] zzz[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:ss[.fff][tt] [M[M]]M-[d]d-[yy]yy hh:mm[:00][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] [M[M]]M-[d]d-[yy]yy hh:mm:ss[.fffffff][tt] zzz[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:ss[.fff] [M[M]]M-[yy]yy-[d]d HH:mm[:00][M[M]]M-[yy]yy-[d]d HH:mm[:00] [M[M]]M-[yy]yy-[d]d[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] [M[M]]M-[yy]yy-[d]d HH:mm:ss[.fffffff] zzz[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:ss[.fff][tt] [M[M]]M-[yy]yy-[d]d hh:mm[:00][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] [M[M]]M-[yy]yy-[d]d hh:mm:ss[.fffffff][tt] zzz[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:ss[.fff] [yy]yy-[M[M]]M-[d]d HH:mm[:00][yy]yy-[M[M]]M-[d]d HH:mm[:00] [yy]yy-[M[M]]M-[d]d[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] [yy]yy-[M[M]]M-[d]d HH:mm:ss[.fffffff] zzz[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:ss[.fff][tt] [yy]yy-[M[M]]M-[d]d hh:mm[:00][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] [yy]yy-[M[M]]M-[d]d hh:mm:ss[.fffffff][tt] zzz[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:ss[.fff] [yy]yy-[d]d-[M[M]]M HH:mm[:00][yy]yy-[d]d-[M[M]]M HH:mm[:00] [yy]yy-[d]d-[M[M]]M[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] [yy]yy-[d]d-[M[M]]M HH:mm:ss[.fffffff] zzz[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:ss[.fff][tt] [yy]yy-[d]d-[M[M]]M hh:mm[:00][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] [yy]yy-[d]d-[M[M]]M hh:mm:ss[.fffffff][tt] zzz[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:ss[.fff] [d]d-[M[M]]M-[yy]yy HH:mm[:00][d]d-[M[M]]M-[yy]yy HH:mm[:00] [d]d-[M[M]]M-[yy]yy[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] [d]d-[M[M]]M-[yy]yy HH:mm:ss[.fffffff] zzz[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:ss[.fff][tt] [d]d-[M[M]]M-[yy]yy hh:mm[:00][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] [d]d-[M[M]]M-[yy]yy hh:mm:ss[.fffffff][tt] zzz[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:ss[.fff] [d]d-[yy]yy-[M[M]]M HH:mm[:00][d]d-[yy]yy-[M[M]]M HH:mm[:00] [d]d-[yy]yy-[M[M]]M[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] [d]d-[yy]yy-[M[M]]M HH:mm:ss[.fffffff] zzz[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:ss[.fff][tt] [d]d-[yy]yy-[M[M]]M hh:mm[:00][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] [d]d-[yy]yy-[M[M]]M hh:mm:ss[.fffffff][tt] zzz[d]d-[yy]yy-[M[M]]M hh:mm:ss[.fffffff][tt] zzz

Détails :Details:

  • Pour séparer les valeurs du mois, du jour et de l’année, vous pouvez utiliser les caractères « - », « / » ou « . ».To separate month, day and year values, you can use '-', '/', or '.'. Par souci de simplicité, le tableau utilise uniquement le séparateur « - ».For simplicity, the table uses only the ' - ' separator.

  • Pour spécifier le mois sous forme de texte, utilisez les trois caractères (ou plus).To specify the month as text, use three or more characters. Les mois constitués d’un ou deux caractères sont interprétés comme des nombres.Months with one or two characters are interpreted as a number.

  • Pour séparer les valeurs d’heure, utilisez le symbole « : ».To separate time values, use the ':' symbol.

  • Les lettres entre crochets sont facultatives.Letters enclosed in square brackets are optional.

  • Les lettres « tt » correspondent aux mentions [AM|PM|am|pm].The letters 'tt' designate [AM|PM|am|pm]. AM est la valeur par défaut.AM is the default. Lorsque « tt » est spécifié, la valeur d’heure (hh) doit être comprise entre 0 et 12.When 'tt' is specified, the hour value (hh) must be in the range of 0 to 12.

  • Les lettres « zzz » désignent le décalage par rapport au fuseau horaire du système, au format {+|-}HH:ss].The letters 'zzz' designate the time zone offset for the system's current time zone in the format {+|-}HH:ss].

USE_TYPE_DEFAULTUSE_TYPE_DEFAULT

USE_TYPE_DEFAULT = { TRUE | FALSE }

Spécifie comment gérer les valeurs manquantes dans les fichiers texte délimité lorsque PolyBase récupère les données à partir du fichier texte.Specifies how to handle missing values in delimited text files when PolyBase retrieves data from the text file.

TRUETRUE
Lors de la récupération des données à partir du fichier texte, stockez chaque valeur manquante à l’aide de la valeur par défaut du type de données de la colonne correspondante dans la définition de la table externe.When retrieving data from the text file, store each missing value by using the default value for the data type of the corresponding column in the external table definition. Par exemple, remplacez une valeur manquante par :For example, replace a missing value with:

  • 0 si la colonne est définie comme une colonne numérique.0 if the column is defined as a numeric column. Les colonnes décimales ne sont pas prises en charge et génèrent une erreur.Decimal columns are not supported and will error.

  • Une chaîne vide «» si la colonne est une colonne de chaîne.Empty string "" if the column is a string column.

  • 1900-01-01 si la colonne est une colonne de date.1900-01-01 if the column is a date column.

FALSEFALSE
Stockez toutes les valeurs manquantes en tant que valeurs NULL.Store all missing values as NULL. Toutes les valeurs NULL qui sont stockées à l’aide du mot NULL dans le fichier texte délimité sont importées en tant que chaînes NULL.Any NULL values that are stored by using the word NULL in the delimited text file are imported as the string 'NULL'.

CODAGEENCODING

Encoding = {'UTF8' | 'UTF16'}

Dans Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data Warehouse (APS CU 7.4), PolyBase peut lire les fichiers texte délimités encodés en UTF8 et UTF16-LE.In Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse (APS CU7.4), PolyBase can read UTF8 and UTF16-LE encoded delimited text files. Dans SQL Server, PolyBase ne prend pas en charge la lecture des fichiers UTF16.In SQL Server, PolyBase doesn't support reading UTF16 encoded files.

AutorisationsPermissions

Nécessite l’autorisation ALTER ANY EXTERNAL FILE FORMAT.Requires ALTER ANY EXTERNAL FILE FORMAT permission.

Remarques d'ordre généralGeneral Remarks

Le format de fichier externe est limité à la base de données dans SQL ServerSQL Server et Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse).The external file format is database-scoped in SQL ServerSQL Server and Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse). Il est limité au serveur dans Parallel Data WarehouseParallel Data Warehouse.It is server-scoped in Parallel Data WarehouseParallel Data Warehouse.

Les options de format sont toutes facultatives et s’appliquent uniquement aux fichiers texte délimité.The format options are all optional and only apply to delimited text files.

Lorsque les données sont stockées dans l’un des formats compressés, PolyBase décompresse d’abord les données avant de retourner les enregistrements de données.When the data is stored in one of the compressed formats, PolyBase first decompresses the data before returning the data records.

Limitations et restrictionsLimitations and Restrictions

Dans les fichiers texte délimité, le séparateur de lignes doit être pris en charge par LineRecordReader de Hadoop.The row delimiter in delimited-text files must be supported by Hadoop's LineRecordReader. Il doit donc être « \r », « \n » ou « \r\n ».That is, it must be either '\r', '\n', or '\r\n'. Ces délimiteurs ne sont pas configurables par l’utilisateur.These delimiters are not user-configurable.

Les combinaisons de méthodes SerDe prises en charge par RCFile et les méthodes de compression de données prises en charge sont répertoriées plus haut dans cet article.The combinations of supported SerDe methods with RCFiles, and the supported data compression methods are listed previously in this article. Toutes les combinaisons ne sont pas prises en charge.Not all combinations are supported.

Le nombre maximal de requêtes PolyBase simultanées est de 32.The maximum number of concurrent PolyBase queries is 32. Lorsque 32 requêtes sont exécutées simultanément, chaque requête peut lire un maximum de 33 000 fichiers à partir de l’emplacement de fichier externe.When 32 concurrent queries are running, each query can read a maximum of 33,000 files from the external file location. Le dossier racine et chaque sous-dossier comptent également comme un fichier.The root folder and each subfolder also count as a file. Si le degré de concurrence est inférieur à 32, l’emplacement de fichier externe peut contenir plus de 33 000 fichiers.If the degree of concurrency is less than 32, the external file location can contain more than 33,000 files.

En raison de la limitation concernant le nombre de fichiers de la table externe, nous vous recommandons de stocker moins de 30 000 fichiers à la racine et dans les sous-dossiers de l’emplacement de fichier externe.Because of the limitation on number of files in the external table, we recommend storing less than 30,000 files in the root and subfolders of the external file location. En outre, nous vous recommandons de limiter au maximum le nombre de sous-dossiers dans le répertoire racine.Also, we recommend keeping the number of subfolders under the root directory to a small number. Lorsque trop de fichiers sont référencés, une exception d’insuffisance de mémoire Java Virtual Machine peut être levée.When too many files are referenced, a Java Virtual Machine out-of-memory exception might occur.

En cas d’exportation de données vers Hadoop ou vers le Stockage Blob Azure via PolyBase, seules les données sont exportées, et non les noms de colonnes (métadonnées), comme le définit la commande CREATE EXTERNAL TABLE.When exporting data to Hadoop or Azure Blob Storage via PolyBase, only the data is exported, not the column names(metadata) as defined in the CREATE EXTERNAL TABLE command.

VerrouillageLocking

Prend un verrou partagé sur l’objet EXTERNAL FILE FORMAT.Takes a shared lock on the EXTERNAL FILE FORMAT object.

PerformancesPerformance

L’utilisation de fichiers compressés présente l’inconvénient de devoir transférer moins de données entre la source de données externe et SQL Server, tout en augmentant l’utilisation du processeur pour compresser et décompresser les données.Using compressed files always comes with the tradeoff between transferring less data between the external data source and SQL Server while increasing the CPU usage to compress and decompress the data.

Les fichiers texte compressés avec GZip ne sont pas fractionnables.Gzip compressed text files are not splittable. Pour améliorer les performances des fichiers texte compressés avec Gzip, nous vous recommandons de générer plusieurs fichiers et de les stocker dans le même répertoire de la source de données externe.To improve performance for Gzip compressed text files, we recommend generating multiple files that are all stored in the same directory within the external data source. Cette structure de fichiers permet à PolyBase de lire et de décompresser les données plus rapidement à l’aide de plusieurs processus de lecture et de décompression.This file structure allows PolyBase to read and decompress the data faster by using multiple reader and decompression processes. Le nombre idéal de fichiers compressés correspond au nombre maximal de processus de lecteur de données par nœud de calcul.The ideal number of compressed files is the maximum number of data reader processes per compute node. Dans SQL ServerSQL Server et Parallel Data WarehouseParallel Data Warehouse, le nombre maximal de processus de lecteur de données est de 8 par nœud, à l’exception de Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) Gen2, soit 20 lecteurs par nœud.In SQL ServerSQL Server and Parallel Data WarehouseParallel Data Warehouse, the maximum number of data reader processes is 8 per node except Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) Gen2 which is 20 readers per node. Dans Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse), le nombre maximal de processus de lecteur de données par nœud varie selon les objectifs du contrat.In Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse), the maximum number of data reader processes per node varies by SLO. Pour plus d’informations, consultez Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) chargement des modèles et des stratégies.See Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) loading patterns and strategies for details.

ExemplesExamples

R.A. Créer un format de fichier externe DELIMITEDTEXTCreate a DELIMITEDTEXT external file format

Cet exemple crée un format de fichier externe nommé textdelimited1 pour un fichier texte délimité.This example creates an external file format named textdelimited1 for a text-delimited file. Les options répertoriées pour FORMAT_OPTIONS spécifient que les champs du fichier doivent être séparés par le caractère « | ».The options listed for FORMAT_OPTIONS specify that the fields in the file should be separated using a pipe character '|'. Le fichier texte est également compressé avec le codec Gzip.The text file is also compressed with the Gzip codec. Si DATA_COMPRESSION n’est pas spécifié, le fichier texte est décompressé.If DATA_COMPRESSION isn't specified, the text file is uncompressed.

Pour un fichier texte délimité, la méthode de compression des données peut être le Codec par défaut, « org.apache.hadoop.io.compress.DefaultCodec », ou le Codec Gzip « org.apache.hadoop.io.compress.GzipCodec ».For a delimited text file, the data compression method can either be the default Codec, 'org.apache.hadoop.io.compress.DefaultCodec', or the 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.B. Créer un format de fichier externe RCFileCreate an RCFile external file format

Cet exemple crée un format de fichier externe pour un RCFile qui utilise la méthode de sérialisation/désérialisation org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe.This example creates an external file format for a RCFile that uses the serialization/deserialization method org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe. Il spécifie également l’utilisation du Codec par défaut pour la méthode de compression des données.It also specifies to use the Default Codec for the data compression method. Si DATA_COMPRESSION n’est pas spécifié, par défaut, les données ne sont pas compressées.If DATA_COMPRESSION isn't specified, the default is no compression.

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.C. Créer un format de fichier externe ORCCreate an ORC external file format

Cet exemple crée un format de fichier externe pour un fichier ORC qui compresse les données avec la méthode de compression des données org.apache.io.compress.SnappyCodec.This example creates an external file format for an ORC file that compresses the data with the org.apache.io.compress.SnappyCodec data compression method. Si DATA_COMPRESSION n’est pas spécifié, par défaut, les données ne sont pas compressées.If DATA_COMPRESSION isn't specified, the default is no compression.

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

D.D. Créer un format de fichier externe PARQUETCreate a PARQUET external file format

Cet exemple crée un format de fichier externe pour un fichier PARQUET qui compresse les données avec la méthode de compression des données org.apache.io.compress.SnappyCodec.This example creates an external file format for a Parquet file that compresses the data with the org.apache.io.compress.SnappyCodec data compression method. Si DATA_COMPRESSION n’est pas spécifié, par défaut, les données ne sont pas compressées.If DATA_COMPRESSION isn't specified, the default is no compression.

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

E.E. Créer un fichier texte délimité qui ignore la ligne d’en-tête (Azure SQL DW uniquement)Create a Delimited Text File Skipping Header Row (Azure SQL DW Only)

Cet exemple crée un format de fichier externe pour un fichier CSV avec une seule ligne d’en-tête.This example creates an external file format for CSV file with a single header row.

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

F.F. Créer un format de fichier externe JSONCreate a JSON external file format

Cet exemple crée un format de fichier externe pour un fichier JSON qui compresse les données avec la méthode de compression des données org.apache.io.compress.SnappyCodec.This example creates an external file format for a JSON file that compresses the data with the org.apache.io.compress.SnappyCodec data compression method. Si DATA_COMPRESSION n’est pas spécifié, par défaut, les données ne sont pas compressées.If DATA_COMPRESSION isn't specified, the default is no compression. Cet exemple s’applique à Azure SQL Edge et n’est actuellement pas pris en charge pour les autres produits SQL.This example applies to Azure SQL Edge and is currently not supported for other SQL products.

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

Voir aussiSee Also

CREATE EXTERNAL DATA SOURCE (Transact-SQL) CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL TABLE (Transact-SQL) CREATE EXTERNAL TABLE (Transact-SQL)
CREATE EXTERNAL TABLE AS SELECT (Transact-SQL) CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
CREATE TABLE AS SELECT (Azure Synapse Analytics) CREATE TABLE AS SELECT (Azure Synapse Analytics)
sys.external_file_formats (Transact-SQL)sys.external_file_formats (Transact-SQL)