title: "sys.external_file_formats (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-data-warehouse, pdw" ms.service: "" ms.component: "system-catalog-views" ms.reviewer: "" ms.suite: "sql" ms.technology:
- "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" dev_langs:
- "TSQL" ms.assetid: a89efb2c-0a3a-4b64-9284-6e93263e29ac caps.latest.revision: 7 author: "stevestein" ms.author: "sstein" manager: "craigg" ms.workload: "Inactive" monikerRange: ">= aps-pdw-2016 || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"
[!INCLUDEtsql-appliesto-ss2016-xxxx-asdw-pdw-md]
Contains a row for each external file format in the current database for [!INCLUDEssNoVersion], [!INCLUDEssSDS], and [!INCLUDEssSDW].
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 [!INCLUDEssSDW], 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 [!INCLUDEssSDW]. | 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)