| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | dev_langs | monikerRange | |
|---|---|---|---|---|---|---|---|---|---|---|
sys.external_file_formats (Transact-SQL) |
sys.external_file_formats (Transact-SQL) |
rwestMSFT |
randolphwest |
03/14/2017 |
sql |
system-objects |
reference |
|
>=aps-pdw-2016||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE sqlserver2016-asdbmi-asa-pdw]
Contains a row for each external file format in the current database for [!INCLUDEssNoVersion], [!INCLUDEssSDS], and [!INCLUDEssazuresynapse-md].
Contains a row for each external file format on the server for [!INCLUDEssPDW].
| Column Name | Data Type | Description | Range |
|---|---|---|---|
| file_format_id | int | Object ID for the external file format. | |
| name | sysname | Name of the file format. in [!INCLUDEssNoVersion] and [!INCLUDEssazuresynapse-md], this is unique for the database. In [!INCLUDEssPDW], this is unique for the server. | |
| format_type | tinyint | The file format type. | DELIMITEDTEXT, RCFILE, ORC, PARQUET |
| field_terminator | nvarchar(10) | For format_type = DELIMITEDTEXT, this is the field terminator. | |
| string_delimiter | nvarchar(10) | For format_type = DELIMITEDTEXT, this is the string delimiter. | |
| date_format | nvarchar(50) | For format_type = DELIMITEDTEXT, this is the user-defined date and time format. | |
| use_type_default | bit | For format_type = DELIMITED TEXT, specifies how to handle missing values when PolyBase is importing data from HDFS text files into [!INCLUDEssazuresynapse-md]. | 0 - store missing values as the string 'NULL'. 1 - store missing values as the column default value. |
| serde_method | nvarchar(255) | For format_type = RCFILE, this is the serialization/deserialization method. | |
| row_terminator | nvarchar(10) | For format_type = DELIMITEDTEXT, this is the character string that terminates each row in the external Hadoop file. | Always '\n'. |
| encoding | nvarchar(10) | For format_type = DELIMITEDTEXT, this is the encoding method for the external Hadoop file. | Always 'UTF8'. |
| data_compression | nvarchar(255) | The data compression method for the external data. | For format_type = DELIMITEDTEXT: - 'org.apache.hadoop.io.compress.DefaultCodec' - 'org.apache.hadoop.io.compress.GzipCodec' For format_type = RCFILE: - 'org.apache.hadoop.io.compress.DefaultCodec' For format_type = ORC: - 'org.apache.hadoop.io.compress.DefaultCodec' - 'org.apache.hadoop.io.compress.SnappyCodec' For format_type = PARQUET: - 'org.apache.hadoop.io.compress.GzipCodec' - 'org.apache.hadoop.io.compress.SnappyCodec' |
The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.
sys.external_data_sources (Transact-SQL)
sys.external_tables (Transact-SQL)
CREATE EXTERNAL FILE FORMAT (Transact-SQL)