CREATE EXTERNAL FILE FORMAT (Transact-SQL)

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Hadoop、Azure Blob Storage、Azure Data Lake Store に格納される外部データを定義する、または外部ストリームに関連付けられている入力および出力ストリーム用の外部ファイル形式オブジェクトを作成します。 外部ファイル形式の作成は、外部テーブルを作成するための前提条件です。 外部ファイル形式を作成することで、外部テーブルによって参照されるデータの実際のレイアウトを指定します。 外部テーブルの作成については、「CREATE EXTERNAL TABLE (Transact-SQL)」を参照してください。

次のファイル形式がサポートされています。

• 区切りテキスト

• Hive RCFile

  Azure Synapse Analytics、Azure SQL Managed Instance、または SQL Server 2022 (16.x) には適用されません。

• Hive ORC

  Azure Synapse Analytics、Azure SQL Managed Instance、または SQL Server 2022 (16.x) には適用されません。

• Parquet

• JSON

  Azure SQL Edge にのみ適用されます。 OPENROWSET を使用して他のプラットフォームで JSON データをインポートする方法については、「JSON ドキュメントの SQL Server へのインポート」または「Azure Synapse Analytics でサーバーレス SQL プールを使用して JSON ファイルのクエリを実行する」を参照してください。

• Delta

  Azure Synapse Analytics のサーバーレス SQL プールと SQL Server 2022 (16.x) にのみ適用されます

構文

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

引数

file_format_name

外部ファイル形式の名前を指定します。

FORMAT_TYPE

外部データの形式を指定します。

• FORMAT_TYPE = PARQUET

  Parquet 形式を指定します。

• FORMAT_TYPE = ORC

  最適化行多桁式 (ORC) 形式を指定します。 このオプションでは、外部の Hadoop クラスター上で 0.11 以降のバージョンの Hive が必要です。 Hadoop では、ORC ファイル形式の方が RCFILE ファイル形式よりも圧縮とパフォーマンスに優れています。

• FORMAT_TYPE = RCFILE, SERDE_METHOD = SERDE_method

  レコード単票形式ファイル形式 (RcFile) を指定します。 このオプションでは、Hive のシリアライザーとデシリアライザー (SerDe) メソッドを指定する必要があります。 この要件は、Hadoop の Hive/HiveQL を使用して RC ファイルに対してクエリを実行する場合と同じです。 SerDe メソッドは大文字と小文字の区別があることに注意してください。

  PolyBase をサポートする 2 つの 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" である必要があります。

区切りテキスト形式のオプション

このセクションで説明する形式オプションは省略可能であり、区切りテキスト ファイルにのみ適用されます。

FIELD_TERMINATOR = field_terminator

区切りテキスト ファイルにのみ適用されます。 フィールド ターミネータは、テキスト区切りファイルでの各フィールド (列) の終了を示す 1 つ以上の文字を指定します。 既定値はパイプ文字 | です。 サポートが保証されるよう、1 つまたは複数の ASCII 文字を使うことをお勧めします。

例 :

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

STRING_DELIMITER

STRING_DELIMITER = *string_delimiter*

テキスト区切りのファイル内の文字列型のデータにはフィールド ターミネータを指定します。 文字列の区切り記号の長さは 1 文字または複数文字とし、単一引用符で囲みます。 既定値は空の文字列 "" です。 サポートが保証されるよう、1 つまたは複数の ASCII 文字を使うことをお勧めします。

例 :

• STRING_DELIMITER = '"'

• STRING_DELIMITER = '0x22' (二重引用符 16 進数)

• STRING_DELIMITER = '*'

• STRING_DELIMITER = ꞌ,ꞌ

• STRING_DELIMITER = '0x7E0x7E' (2 つのチルダ。例: ~~)

FIRST_ROW = first_row_int

適用対象: Azure Synapse Analytics、SQL Server 2022 以降のバージョン

PolyBase の読み込みの間にすべてのファイルで最初に読み取る行番号を指定します。 このパラメーターでは、値 1 から 15 を受け取ることができます。 2 に設定すると、データが読み込まれるときに、すべてのファイルの最初の行 (ヘッダー行) がスキップされます。 行は、行ターミネータ (/r/n、/r、/n) の存在に基づいてスキップされます。 このオプションをエクスポートに使うと、行がデータに追加されて、データ損失なしにファイルを読み取ることができます。 2 より大きい値に設定する場合は、エクスポートされる最初の行は外部テーブルの列名です。

DATE_FORMAT = datetime_format

区切りテキスト ファイルに出現する可能性のあるすべての日付データと時刻データのカスタム形式を指定します。 ソース ファイルで既定の datetime 形式が使われている場合は、このオプションは必要ありません。 指定できるカスタム datetime 形式は、ファイルごとに 1 つだけです。 1 つのファイルに複数のカスタム datetime 形式を指定することはできません。 ただし、各形式が外部テーブル定義でそれぞれのデータ型の既定の形式になっている場合は、複数の datetime 形式を使うことができます。

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'

日付形式の例を次の表に示します。

テーブルに関する注意事項:

• 年、月、日は、いくつかの形式と順序で指定できます。 表では、ymd 形式のみを示します。 月は、1 または 2 桁の数字、または 3 つの文字で指定できます。 日は、1 または 2 桁の数字で指定できます。 年は、2 または 4 桁の数字で指定できます。

• ミリ秒 (fffffff) は必須ではありません。

• am や pm (tt) は必須ではありません。 既定では、 AMです。

サポートされていない日付と時刻の形式

外部ファイル形式では、多数の日付と時刻の形式を記述できます。

詳細:

• 月、日、年の値を区切るには、-、/、. の区切り記号のいずれかを使用する必要があります。 わかりやすくするために、この表では、'-' の区切りのみを使用しています。

• 月をテキストとして指定するには、3 つ以上の文字を使います。 1 つまたは 2 つの文字で表された月は、数値として解釈されます。

• 時刻の値を区切るには、":" 記号を使います。

• 角かっこで囲まれた文字はオプションです。

• 文字 tt を使って [AM|PM|am|pm] を指定します。 AM が既定値です。 tt を指定すると、時間の値 (hh) を 0 から 12 の範囲にする必要があります。

• 文字 zzz を使って、システムの現在のタイム ゾーンに対するオフセットを {+|-}HH:ss] の形式で指定します。

USE_TYPE_DEFAULT = { TRUE | FALSE }

PolyBase によってテキスト ファイルからデータが取得されるときに、区切りテキスト ファイル内の不足値を処理する方法を指定します。 既定値は FALSE です。

• TRUE

  テキスト ファイルからデータを取得するときは、外部テーブルの定義内の対応する列のデータ型の既定値を使用して、各不足値を格納します。 たとえば、不足値を次の値に置き換えます。

  • 列が数値列として定義されている場合は 0。 decimal 型の列はサポートされておらず、エラーになります。

  • 列が string 型の列である場合は空の文字列 ""。

  • 列が date 列の場合は 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 Storage にデータをエクスポートするときは、データだけがエクスポートされて、CREATE EXTERNAL TABLE コマンドで定義されている列名 (メタデータ) はエクスポートされません。

ロック

EXTERNAL FILE FORMAT オブジェクトに対して共有ロックを取得します。

パフォーマンス

常に圧縮したファイルを使用すると、外部データソースと SQL Server の間で転送するデータ量が少なくなると同時に、データの圧縮および圧縮解除のために CPU の使用率が高くなるというデメリットもあります。

Gzip で圧縮されたテキスト ファイルは分割可能ではありません。 Gzip で圧縮されたテキスト ファイルのパフォーマンスを向上させるには、複数のファイルを生成し、そのすべてを外部データ ソースの同じディレクトリに格納することをお勧めします。 このようなファイル構造にすると、PolyBase は、複数のリーダー プロセスと圧縮解除プロセスを使って、より速くデータを圧縮解除できます。 圧縮されたファイルの理想的な数は、計算ノードあたりのデータ リーダー プロセスの最大数です。 SQL Server と Analytics Platform System (PDW) では、データ リーダー プロセスの最大数は、ノードあたり 20 個の Azure Synapse Analytics Gen2 を除き、ノードあたり 8 個です。 Azure Synapse Analytics では、ノードごとのデータ リーダー プロセスの最大数は SLO によって変化します。 詳細については、「Azure Synapse Analytics の読み込みパターンと戦略」を参照してください。

例

A. DELIMITEDTEXT 外部ファイル形式を作成する

この例では、テキスト区切りファイル用に textdelimited1 という名前の外部ファイル形式を作成します。 FORMAT_OPTIONS のオプション リストでは、ファイルのフィールドをパイプ文字 "|" を使って区切ることが指定されています。 テキスト ファイルはまた、Gzip コーデックを使用して圧縮されます。 DATA_COMPRESSION を指定しないと、テキスト ファイルは圧縮されません。

区切りテキスト ファイルの場合、データ圧縮方法には、既定のコーデックである org.apache.hadoop.io.compress.DefaultCodec か、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. 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) 以降。

この例では、ヘッダー行が 1 つの 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. Delta テーブルの外部ファイル形式を作成する

この例では、Delta テーブル型のファイル形式の外部ファイル形式を作成します。 この例は、SQL Server 2022 (16.x) に適用されます。 詳細については、PolyBase を使用したデルタ テーブル ファイルの仮想化に関する記事を参照してください。

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

関連するコンテンツ

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