CREATE EXTERNAL FILE FORMAT (Transact-SQL)

  • 發行項

適用於： SQL Server 2016 (13.x)及更新版本 Azure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

建立外部檔案格式物件，以定義儲存在 Hadoop、Azure Blob 儲存體、Azure Data Lake Store 的外部資料，或供給外部串流相關聨的輸入和輸出串流使用。 建立外部檔案格式是建立外部資料表的先決條件。 透過建立外部檔案格式，您可以指定外部資料表所參考資料的實際配置。 若要建立外部資料表，請參閱 CREATE EXTERNAL TABLE (Transact-SQL)。

支援下列檔案格式：

Syntax

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

}

引數

file_format_name

指定外部檔案格式的名稱。

FORMAT_TYPE

指定外部資料的格式。

  • FORMAT_TYPE = PARQUET

    指定 Parquet 格式。

  • FORMAT_TYPE = ORC

    指定 Optimized Row Columnar (ORC) 格式。 此選項要求外部 Hadoop 叢集上必須有 Hive 0.11 版或更高版本。 在 Hadoop 中，ORC 檔案格式會提供比 RCFILE 檔案格式更好的壓縮率和效能。

  • FORMAT_TYPE = RCFILE, SERDE_METHOD = SERDE_method

    指定 Record Columnar 檔案格式 (RcFile)。 此選項要求您必須指定 Hive 序列化程式和還原序列化程式 (SerDe) 方法。 如果您在 Hadoop 中使用 Hive/HiveQL 來查詢 RC 檔案，則此需求是一樣的。 請注意，SerDe 方法會區分大小寫。

    使用 PolyBase 所支援的這兩種 SerDe 方法來指定 RCFile 的範例。

    • 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

    指定使用資料行分隔符號 (亦稱為欄位結束字元) 的文字格式。

  • FORMAT_TYPE = JSON

    指定 JSON 格式。 僅適用於 Azure SQL Edge。

  • FORMAT_TYPE = DELTA

    指定 Delta Lake 格式。 適用於 Azure Synapse Analytics 中的無伺服器 SQL 集區及 SQL Server 2022 (16.x)。

FORMAT_OPTIONS

選擇性。 僅適用於分隔的文字資料類型。

只有 Azure Synapse Analytics 中的無伺服器 SQL 集區支援 PARSER_VERSION。

無伺服器 SQL 集區不支援 DATE_FORMAT 選項。

DATA_COMPRESSION = data_compression_method

指定外部資料的資料壓縮方法。 未指定 DATA_COMPRESSION 時，預設值為未經壓縮的資料。

若要正常運作，Gzip 壓縮檔案必須具有 ".gz" 副檔名。

DELIMITEDTEXT 格式類型支援此壓縮方式：

  • DATA_COMPRESSION = org.apache.hadoop.io.compress.GzipCodec

[分隔的文字格式] 選項

此節中所述的格式選項都是選用的，而且僅適用於分隔的文字檔。

FIELD_TERMINATOR = field_terminator

僅適用於分隔符號文字檔。 欄位結束字元會指定一或多個字元，在文字分隔檔案中標記每個欄位 (欄) 的結尾。 預設值為管道字元 |。 為確保成功支援，建議使用一或多個 ASCII 字元。

範例：

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

STRING_DELIMITER

STRING_DELIMITER = *string_delimiter*

針對文字分隔檔案中類型字串的資料指定欄位結束字元。 字串分隔符號的長度為一或多個字元，且要以單引號括起來。 預設值為空字串 ""。 為確保成功支援，建議使用一或多個 ASCII 字元。

範例：

  • STRING_DELIMITER = '"'

  • STRING_DELIMITER = '0x22' (雙引號十六進位)

  • STRING_DELIMITER = '*'

  • STRING_DELIMITER = ꞌ,ꞌ

  • STRING_DELIMITER = '0x7E0x7E' (兩個波狀符號，例如 ~~)

FIRST_ROW = first_row_int

適用於：Azure Synapse Analytics、SQL Server 2022 和更新版本

指定在 PolyBase 載入期間，要在所有檔案中先讀取的資料列編號。 此參數接受 1 到 15 的值。 如果將值設定為二，則在載入資料時，會略過每個檔案中的第一個資料列 (標題列)。 如果資料列具有資料列結束字元 (/r/n、/r、/n)，就會略過。 使用此選項進行匯出時，會將資料列加入至資料，以確定可讀取檔案且不會遺失任何資料。 若將此值設定為 >2，匯出的第一列將是外部資料表的資料行名稱。

DATE_FORMAT = datetime_format

針對所有可能出現在分隔符號文字檔中的日期和時間資料，指定自訂格式。 如果來源檔案使用預設的日期時間格式，就不需要這個選項。 每個檔案只允許一個自訂的日期時間格式。 您無法針對每個檔案指定多個自訂的日期時間格式。 不過，如果每個日期時間格式在外部資料表定義中都是其各自資料類型的預設格式，您就可以使用多個日期時間格式。

重要

PolyBase 只會使用自訂日期格式來匯入資料。 它不會使用自訂格式來將資料寫入至外部檔案。

未指定 DATE_FORMAT 或其為空字串時，PolyBase 會使用下列預設格式：

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

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

  • date: 'yyyy-MM-dd'

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

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

  • time: 'HH:mm:ss'

重要

指定自訂 DATE_FORMAT 將會覆寫所有的預設類型格式。 這表示檔案的所有日期時間、日期與時間資料格，都必須有相同的日期格式。 使用覆寫的 DATE_FORMAT 時，日期與時間值的格式必須相同。

下表提供範例日期格式：

關於該表格的注意事項：

  • 年、月、日均可有幾種格式和順序。 下表僅顯示 ymd 格式。 月份可以有一或兩個位數，或是三個字元。 日期可以有一或兩個位數。 年份可以有兩或四個位數。

  • 不需要毫秒 (fffffff)。

  • 不需要 am 或 pm (tt)。 預設值為 AM。

日期類型 範例 描述
datetime DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fff 除了年、月及日之外，此日期格式包含 00-24 時、00-59 分、00-59 秒，以及 3 位數的毫秒。
datetime DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffftt 除了年、月及日之外，此日期格式包含 00-12 時、00-59 分、00-59 秒、3 位數的毫秒，以及 AM、am、PM 或 pm。
smalldatetime DATE_FORMAT = yyyy-MM-dd HH:mm 除了年、月及日之外，此日期格式包含 00-23 時、00-59 分。
smalldatetime DATE_FORMAT = yyyy-MM-dd hh:mmtt 除了年、月及日之外，此日期格式包含 00-11 時、00-59 分、沒有秒，以及 AM、am、PM 或 pm。
date DATE_FORMAT = yyyy-MM-dd 年、月及日。 未包含任何時間元素。
date DATE_FORMAT = yyyy-MMM-dd 年、月及日。 當指定月份和 MMM 時，輸入值為一或字串、Jan、Feb、Mar、Apr、May、Jun、Jul、Aug、Sep、Oct、Nov 或 Dec。
datetime2 DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fffffff 除了年、月及日之外，此日期格式包含 00-23 時、00-59 分、00-59 秒，以及 7 位數的毫秒。
datetime2 DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffffffftt 除了年、月及日之外，此日期格式包含 00-11 時、00-59 分、00-59 秒、7 位數的毫秒，以及 AM、am、PM 或 pm。
datetimeoffset DATE_FORMAT = yyyy-MM-dd HH:mm:ss.fffffff zzz 除了年、月及日之外，此日期格式包含 00-23 時、00-59 分、00-59 秒、7 位數的毫秒，以及您放入輸入檔以作為 {+&#124;-}HH:ss 的時區差距。 例如，由於不含日光節約時間的洛杉磯時間比 UTC 晚 8 小時，因此，輸入檔中的 -08:00 值會指定洛杉磯的時區。
datetimeoffset DATE_FORMAT = yyyy-MM-dd hh:mm:ss.ffffffftt zzz 除了年、月及日之外，此日期格式包含 00-11 時、00-59 分、00-59 秒、7 位數的毫秒、(AM、am、PM 或 pm)，以及時區差距。 請參閱上一列的描述。
time DATE_FORMAT = HH:mm:ss 沒有日期值，只有 00-23 時、00-59 分和 00-59 秒。

支援的日期與時間格式

外部檔案格式可以描述大量的日期與時間格式：

Datetime smalldatetime date 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

詳細資料：

  • 若要分隔月、日及年值，只能使用下列其中一種分隔符號：-、/, 或 .。 為了簡單起見，此資料表只使用 - 分隔符號。

  • 若要以文字格式指定月份，請使用三個以上的字元。 含有一或兩個字元的月份會被視為數字。

  • 若要分隔時間值，請使用 : 符號。

  • 括在方括號中的字母是選擇性的。

  • 字母 tt 會指定 [AM|PM|am|pm]。 預設值是 AM。 指定 tt 時，小時值 (hh) 必須介於 0 到 12 之間。

  • 字母 zzz 會以 {+|-}HH:ss] 格式，指定系統目前時區的時區差距。

USE_TYPE_DEFAULT = { TRUE | FALSE }

指定當 PolyBase 從文字檔擷取資料時，如何處理分隔符號文字檔中遺漏的值。 預設值為 FALSE。

  • TRUE

    從文字檔擷取資料時，使用外部資料表定義中對應資料行之資料類型的預設值來儲存每個遺漏值。 例如，使用下列項目來取代遺漏值：

    • 若將資料行定義為數值資料行時為 0。 不支援十進位資料行，並會出現錯誤。

    • 如果資料行為字串資料行，使用空字串 ""。

    • 如果資料行為日期資料行，使用 1900-01-01。

    • 在 Azure Synapse Analytics 中，FORMAT_TYPE = DELIMITEDTEXT, PARSER_VERSION = '2.0' 不支援 USE_TYPE_DEFAULT=true。

  • FALSE

    將所有遺漏值儲存為 NULL。 在使用了分隔符號的文字檔案中，使用 NULL 一字儲存的任何 NULL 值，都會匯入成字串 NULL。

ENCODING = {'UTF8' | 'UTF16'}

在 Azure Synapse Analytics 與 Analytics Platform System (PDW) (APS CU7.4) 中，PolyBase 可以讀取使用了分隔符號和 UTF8 和 UTF16-LE 編碼的文字檔案。

在 SQL Server 中，PolyBase 不支援讀取 UTF16 編碼的檔案。

權限

需要 ALTER ANY EXTERNAL FILE FORMAT 權限。

備註

在 SQL Server 和 Azure Synapse Analytics 中，此外部檔案格式屬於資料庫範圍， 而在 Analytics Platform System (PDW) 中則是伺服器範圍。

格式選項都是選用的，僅適用於分隔符號文字檔。

以其中一種壓縮格式儲存資料時，PolyBase 會先將資料解壓縮，然後再傳回資料記錄。

限制

Hadoop 的 LineRecordReader 必須支援分隔符號文字檔中的資料列分隔符號。 也就是說，必須是 \r、\n 或 \r\n。 使用者無法設定這些分隔符號。

本文先前已列出支援的 SerDe 方法與 RCFile 的組合以及支援的資料壓縮方法。 並非所有組合都受到支援。

並行 PolyBase 查詢的數目上限為 32 個。 當 32 個並行查詢正在執行時，每個查詢都能從外部檔案位置讀取最多 33,000 個檔案。 根資料夾與每個子資料夾也算是一個檔案。 如果並行程度小於 32，外部檔案位置所包含的檔案就可超過 33,000 個。

由於外部資料表中檔案數目的限制，我們建議您在外部檔案位置的根和子資料夾中儲存少於 30,000 個檔案。 此外，建議您讓根目錄底下的子資料夾數目保持在一個很小的數目。 參考太多檔案時，可能會發生 Java 虛擬機器記憶體不足的例外狀況。

透過 PolyBase 將檔案匯出到 Hadoop 或 Azure Blob 儲存體時，就會如 CREATE EXTERNAL TABLE 命令中所定義，只匯出資料，而不含資料行名稱 (中繼資料)。

鎖定

在 EXTERNAL FILE FORMAT 物件上取得共用鎖定。

效能

使用壓縮檔案一律要在下列兩者間進行取捨：在外部資料來源和 SQL Server 之間傳送較少資料，同時要提高 CPU 使用量來壓縮和解壓縮資料。

Gzip 壓縮的文字檔案無法分割。 若要提升 Gzip 壓縮文字檔的效能，我們建議產生多個檔案，並將它們全都儲存於外部資料來源內的相同目錄中。 這個檔案結構允許 PolyBase 使用多個讀取器和解壓縮程序，更快速地讀取及解壓縮資料。 壓縮檔案的理想數目是每個計算節點的資料讀取器處理序數目上限。 在 SQL Server 及 Analytics Platform System (PDW) 中，除了 Azure Synapse Analytics Gen2 每個節點的資料讀取器處理緒數上限為 20 之外，其餘皆為 8。 在 Azure Synapse Analytics 中，每個節點的資料讀取器處理緒數上限隨 SLO 而異。 如需詳細資訊，請參閱 Azure Synapse Analytics 的載入模式與策略 (英文)。

範例

A. 建立 DELIMITEDTEXT 外部檔案格式

這個範例會建立名為 textdelimited1 的外部檔案格式，以供文字分隔檔案使用。 針對 FORMAT_OPTIONS 所列出的選項，可指定檔案中的欄位，應使用管道字元 | 加以分隔。 文字檔也會使用 Gzip 轉碼器來壓縮。 若未指定 DATA_COMPRESSION，會將文字檔案予以解壓縮。

對於使用分隔符號的文字檔案，壓縮資料的方式可以是預設的 Codec、org.apache.hadoop.io.compress.DefaultCodec 或 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. 建立 RCFile 外部檔案格式

此範例會為使用 serialization/deserialization 方法 org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe 的 RCFile 建立外部檔案格式。 它也會指定要針對資料壓縮方法使用預設轉碼器。 如果未指定 DATA_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. 建立 ORC 外部檔案格式

此範例會為使用 org.apache.io.compress.SnappyCodec 資料壓縮方法壓縮資料的 ORC 檔案建立外部檔案格式。 如果未指定 DATA_COMPRESSION，預設為不壓縮。

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

D. 建立 PARQUET 外部檔案格式

此範例會為使用 org.apache.io.compress.SnappyCodec 資料壓縮方法壓縮資料的 Parquet 檔案建立外部檔案格式。 如果未指定 DATA_COMPRESSION，預設為不壓縮。

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

E. 建立使用分隔符號的文字檔案，以跳過標題資料列

適用於：Azure Synapse Analytics 及 SQL Server 2022 (16.x) 與更新版本。

這個範例會針對具有單一標題列的 CSV 檔案建立外部檔案格式。 如需詳細資訊，請參閱使用 PolyBase 將 CSV 檔案虛擬化。

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

F. 建立 JSON 外部檔案格式

適用於：Azure SQL Edge

此範例會為使用 org.apache.io.compress.SnappyCodec 資料壓縮方法壓縮資料的 JSON 檔案建立外部檔案格式。 如果未指定 DATA_COMPRESSION，預設為不壓縮。 此範例適用於 Azure SQL Edge，但其他 SQL 產品目前不支援此範例。

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

G. 建立差異資料表的外部檔案格式

此範例會為差異資料表類型的檔案格式建立外部檔案格式。 此範例適用於 SQL Server 2022 (16.x)。 如需詳細資訊，請參閱使用 PolyBase 將差異檔案虛擬化。

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

相關內容