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

適用対象: ○SQL Server (2016 以降) XAzure SQL Database ○Azure SQL Data Warehouse ○Parallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2016) noAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Hadoop、Azure Blob Storage、または Azure Data Lake Store に格納される外部データを定義する外部ファイル形式オブジェクトを作成します。Creates an External File Format object defining external data stored in Hadoop, Azure Blob Storage, or Azure Data Lake Store. 外部ファイル形式の作成は、外部テーブルを作成するための前提条件です。Creating an external file format is a prerequisite for creating an External Table. 外部ファイル形式を作成することで、外部テーブルによって参照されるデータの実際のレイアウトを指定します。By creating an External File Format, you specify the actual layout of the data referenced by an external table.

PolyBase では、次のファイル形式がサポートされています。PolyBase supports the following file formats:

  • 区切りテキストDelimited Text

  • Hive RCFileHive RCFile

  • Hive ORCHive ORC

  • ParquetParquet

外部テーブルの作成については、「CREATE EXTERNAL TABLE (Transact-SQL)」をご覧ください。To create an External Table, see CREATE EXTERNAL TABLE (Transact-SQL).

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

-- 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 ORC files.  
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 RCFILE.  
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 DELIMITED TEXT 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'} 
}  

引数Arguments

file_format_namefile_format_name
外部ファイル形式の名前を指定します。Specifies a name for the external file format.

FORMAT_TYPE = [ PARQUET | ORC | RCFILE | DELIMITEDTEXT] 外部データの形式を指定します。FORMAT_TYPE = [ PARQUET | ORC | RCFILE | DELIMITEDTEXT] Specifies the format of the external data.

  • PARQUET は、Parquet 形式を指定します。PARQUET Specifies a Parquet format.

  • ORCORC
    最適化行多桁式 (ORC) 形式を指定します。Specifies an Optimized Row Columnar (ORC) format. このオプションでは、外部の Hadoop クラスター上で 0.11 以降のバージョンの Hive が必要です。This option requires Hive version 0.11 or higher on the external Hadoop cluster. Hadoop では、ORC ファイル形式の方が RCFILE ファイル形式よりも圧縮とパフォーマンスに優れています。In Hadoop, the ORC file format offers better compression and performance than the RCFILE file format.

  • RCFILE (SERDE_METHOD = SERDE_method との組み合わせ): レコード単票形式ファイル形式 (RcFile)。RCFILE (in combination with SERDE_METHOD = SERDE_method) Specifies a Record Columnar file format (RcFile). このオプションでは、Hive のシリアライザーとデシリアライザー (SerDe) メソッドを指定する必要があります。This option requires you to specify a Hive Serializer and Deserializer (SerDe) method. この要件は、Hadoop の Hive/HiveQL を使用して RC ファイルに対してクエリを実行する場合と同じです。This requirement is the same if you use Hive/HiveQL in Hadoop to query RC files. SerDe メソッドは大文字と小文字の区別があることに注意してください。Note, the SerDe method is case-sensitive.

PolyBase をサポートする 2 つの SerDe メソッドを使用して RCFile を指定する例です。Examples of specifying RCFile with the two SerDe methods that PolyBase supports.

-   <span data-ttu-id="11fd5-128">FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'</span><span class="sxs-lookup"><span data-stu-id="11fd5-128">FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'</span></span>

-   <span data-ttu-id="11fd5-129">FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'</span><span class="sxs-lookup"><span data-stu-id="11fd5-129">FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe'</span></span>
  • DELIMITEDTEXT: フィールド ターミネータとも呼ばれる、列の区切り記号付きのテキスト形式を指定します。DELIMITEDTEXT Specifies a text format with column delimiters, also called field terminators.

FIELD_TERMINATOR = field_terminatorFIELD_TERMINATOR = field_terminator
区切りテキスト ファイルにのみ適用されます。Applies only to delimited text files. フィールド ターミネータは、テキスト区切りファイルでの各フィールド (列) の終了を示す 1 つ以上の文字を指定します。The field terminator specifies one or more characters that mark the end of each field (column) in the text-delimited file. 既定値はパイプ文字 ꞌ|ꞌ です。The default is the pipe character ꞌ|ꞌ. サポートが保証されるよう、1 つまたは複数の Ascii 文字を使うことをお勧めします。For guaranteed support, we recommend using one or more ascii characters.

例 :Examples:

  • FIELD_TERMINATOR = '|'FIELD_TERMINATOR = '|'

  • FIELD_TERMINATOR = ' 'FIELD_TERMINATOR = ' '

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

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

STRING_DELIMITER = string_delimiterSTRING_DELIMITER = string_delimiter
テキスト区切りのファイル内の文字列型のデータにはフィールド ターミネータを指定します。Specifies the field terminator for data of type string in the text-delimited file. 文字列の区切り記号の長さは 1 文字または複数文字とし、単一引用符で囲みます。The string delimiter is one or more characters in length and is enclosed with single quotes. 既定値は空の文字列 "" です。The default is the empty string "". サポートが保証されるよう、1 つまたは複数の Ascii 文字を使うことをお勧めします。For guaranteed support, we recommend using one or more ascii characters.

例 :Examples:

  • STRING_DELIMITER = '"'STRING_DELIMITER = '"'

  • STRING_DELIMITER = '0x22' -- 二重引用符の十六進値STRING_DELIMITER = '0x22' -- Double quote hex

  • STRING_DELIMITER = '*'STRING_DELIMITER = '*'

  • STRING_DELIMITER = ꞌ,ꞌSTRING_DELIMITER = ꞌ,ꞌ

  • STRING_DELIMITER = '0x7E0x7E' -- 2 個のチルダ (~~)STRING_DELIMITER = '0x7E0x7E' -- Two tildes (for example, ~~)

FIRST_ROW = First_row_intFIRST_ROW = First_row_int
PolyBase の読み込みの間にすべてのファイルで最初に読み取る行番号を指定します。Specifies the row number that is read first in all files during a PolyBase load. このパラメーターで指定できる値は 1 ~ 15 です。This parameter can take values 1-15. 2 に設定すると、データが読み込まれるときに、すべてのファイルの最初の行 (ヘッダー行) がスキップされます。If the value is set to two, the first row in every file (header row) is skipped when the data is loaded. 行は、行ターミネータ (/r/n、/r、/n) の存在に基づいてスキップされます。Rows are skipped based on the existence of row terminators (/r/n, /r, /n). このオプションをエクスポートに使うと、行がデータに追加されて、データ損失なしにファイルを読み取ることができます。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. 2 より大きい値に設定する場合は、エクスポートされる最初の行は外部テーブルの列名です。If the value is set to >2, the first row exported is the Column names of the external table.

DATE_FORMAT = datetime_formatDATE_FORMAT = datetime_format
区切りテキスト ファイルに出現する可能性のあるすべての日付データと時刻データのカスタム形式を指定します。Specifies a custom format for all date and time data that might appear in a delimited text file. ソース ファイルで既定の datetime 形式が使われている場合は、このオプションは必要ありません。If the source file uses default datetime formats, this option isn't necessary. 指定できるカスタム datetime 形式は、ファイルごとに 1 つだけです。Only one custom datetime format is allowed per file. 1 つのファイルに複数のカスタム datetime 形式を指定することはできません。You can't specify more than one custom datetime formats per file. ただし、各形式が外部テーブル定義でそれぞれのデータ型の既定の形式になっている場合は、複数の datetime 形式を使うことができます。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.

PolyBase では、カスタム日付形式はデータのインポートに対してのみ使われます。PolyBase only uses the custom date format for importing the data. 外部ファイルへのデータの書き込みには、カスタム書式は使われません。It doesn't use the custom format for writing data to an external file.

DATE_FORMAT が指定されていないか、空の文字列の場合、PolyBase は次の既定の形式を使います。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'

日付形式の例を次の表に示します。Example date formats are in the following table:

テーブルに関する注意事項:Notes about the table:

  • 年、月、日は、さまざまな形式と順序で指定できます。Year, month, and day can have a variety of formats and orders. 表では、ymd 形式のみを示します。The table shows only the ymd format. 月は、1 または 2 桁の数字、または 3 つの文字で指定できます。Month can have one or two digits, or three characters. 日は、1 または 2 桁の数字で指定できます。Day can have one or two digits. 年は、2 または 4 桁の数字で指定できます。Year can have two or four digits.

  • ミリ秒 (fffffff) は、必須ではありません。Milliseconds (fffffff) are not required.

  • AM/PM (tt) は、必須ではありません。Am, pm (tt) isn't required. 既定値は AM です。The default is AM.

日付型Date Type Example [説明]Description
DateTimeDateTime DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fff'DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fff' 年、月、日に加えて、この日付形式には、00-24 の時、00-59 の分、00-59 の秒、3 桁のミリ秒が含まれます。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' 年、月、日に加えて、この日付形式には、00-12 の時、00-59 の分、00-59 の秒、3 桁のミリ秒、および AM/am/PM/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' 年、月、日に加えて、この日付形式には、00-23 の時、00-59 の分が含まれます。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' 年、月、日に加えて、この日付形式には、00-11 の時、00-59 の分、AM/am/PM/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' 年、月、日。Year, month, and day. 時刻要素は含まれていません。No time element is included.
dateDate DATE_FORMAT = 'yyyy-MMM-dd'DATE_FORMAT = 'yyyy-MMM-dd' 年、月、日。Year, month, and day. 3 つの M を使用して月を指定する場合、入力値は 1 つです。つまり、1 月、2 月、3 月、4 月、6 月、7 月、8 月、9 月、10 月、11 月、または 12 月という文字列です。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' 年、月、日に加えて、この日付形式には、00-23 の時、00-59 の分、00-59 の秒、7 桁のミリ秒が含まれます。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' 年、月、日に加えて、この日付形式には、00-11 の時、00-59 の分、00-59 の秒、7 桁のミリ秒、および AM/am/PM/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' 年、月、日に加えて、この日付形式には、00-23 の時、00-59 の分、00-59 の秒、7 桁のミリ秒、および {+&#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. たとえば、ロサンゼルス時間 (夏時間なし) は、UTC よりも 8 時間遅くなるので、入力ファイル内の値 -08:00 によってロサンゼルスのタイムゾーンが指定されます。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' 年、月、日に加えて、この日付形式には、00-11 の時、00-59 の分、00-59 の秒、7 桁のミリ秒、AM/am/PM/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, (AM, am, PM, or pm), and the timezone offset. 前の行の説明を参照してください。See the description in the previous row.
TimeTime DATE_FORMAT = 'HH:mm:ss'DATE_FORMAT = 'HH:mm:ss' 日付の値はありません。00 から 23 時、00 から 59 分、00 から 59 秒のみです。There is no date value, only 00-23 hours, 00-59 minutes, and 00-59 seconds.

サポートされているすべての日付形式:All supported date 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

詳細:Details:

  • 月、日、年の値を区切るには、"-"、"/"、"." を使うことができます。To separate month, day and year values, you can use '-', '/', or '.'. わかりやすくするために、この表では、'-' の区切りのみを使用しています。For simplicity, the table uses only the ' - ' separator.

  • 月をテキストとして指定するには、3 つ以上の文字を使います。To specify the month as text, use three or more characters. 1 つまたは 2 つの文字で表された月は、数値として解釈されます。Months with one or two characters are interpreted as a number.

  • 時刻の値を区切るには、":" 記号を使います。To separate time values, use the ':' symbol.

  • 角かっこで囲まれた文字はオプションです。Letters enclosed in square brackets are optional.

  • 文字 'tt' では [AM|PM|am|pm] が指定されます。The letters 'tt' designate [AM|PM|am|pm]. AM が既定値です。AM is the default. 'tt' を指定すると、時間の値 (hh) を 0 から 12 の範囲にする必要があります。When 'tt' is specified, the hour value (hh) must be in the range of 0 to 12.

  • 文字 'zzz' では、システムの現在のタイム ゾーンに対するオフセットが {+|-}HH:ss] の形式で指定されます。The letters 'zzz' designate the time zone offset for the system's current time zone in the format {+|-}HH:ss].

USE_TYPE_DEFAULT = { TRUE | FALSE }USE_TYPE_DEFAULT = { TRUE | FALSE }
PolyBase によってテキスト ファイルからデータが取得されるときに、区切りテキスト ファイル内の不足値を処理する方法を指定します。Specifies how to handle missing values in delimited text files when PolyBase retrieves data from the text file.

TRUETRUE
テキスト ファイルからデータを取得するときは、外部テーブルの定義内の対応する列のデータ型の既定値を使用して、各不足値を格納します。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. たとえば、不足値を次の値に置き換えます。For example, replace a missing value with:

  • 列が numeric 型の列として定義されている場合は 00 if the column is defined as a numeric column. decimal 型の列はサポートされていないため、エラーになります。Decimal columns are not supported and will error.

  • 列が string 型の列である場合は空の文字列 ""。Empty string "" if the column is a string column.

  • 列が date 列の場合は 1900-01-01 です。1900-01-01 if the column is a date column.

FALSEFALSE
すべての不足値を NULL として格納します。Store all missing values as NULL. 区切りテキスト ファイルで単語 NULL を使って格納されている NULL 値は、文字列 "NULL" としてインポートされます。Any NULL values that are stored by using the word NULL in the delimited text file are imported as the string 'NULL'.

Encoding = {'UTF8' | 'UTF16'}Encoding = {'UTF8' | 'UTF16'}
Azure SQL Data Warehouse と PDW (APS CU7.4) では、UTF8 および UTF16-LE でエンコードされた区切りテキスト ファイルを PolyBase で読み取ることができます。In Azure SQL Data Warehouse and PDW (APS CU7.4), PolyBase can read UTF8 and UTF16-LE encoded delimited text files. SQL Server の PolyBase では、UTF16 でエンコードされたファイルの読み取りはサポートされていません。In SQL Server, PolyBase doesn't support reading UTF16 encoded files.

DATA_COMPRESSION = data_compression_methodDATA_COMPRESSION = data_compression_method
外部データのデータ圧縮方法を指定します。Specifies the data compression method for the external data. DATA_COMPRESSION を指定しない場合の既定値は、圧縮されていないデータです。When DATA_COMPRESSION isn't specified, the default is uncompressed data. 正しく動作するためには、Gzip で圧縮されたファイルの拡張子が ".gz" である必要があります。To work properly, Gzip compressed files must have the ".gz" file extension.

DELIMITEDTEXT 形式の種類では、次の圧縮メソッドがサポートされています。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'

RCFILE 形式の種類では、次の圧縮メソッドがサポートされています。The RCFILE format type supports this compression method:

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

ORC ファイル形式の種類では、次の圧縮メソッドがサポートされています。The ORC file 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.SnappyCodec'DATA COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

PARQUET ファイル形式の種類では、次の圧縮方法がサポートされています。The PARQUET file format type supports the following compression methods:

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

  • DATA COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'DATA COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

アクセス許可Permissions

ALTER ANY EXTERNAL FILE FORMAT アクセス許可が必須です。Requires ALTER ANY EXTERNAL FILE FORMAT permission.

全般的な解説General Remarks

外部ファイルの形式は、SQL ServerSQL ServerSQL データ ウェアハウスSQL Data Warehouse ではデータベース スコープです。The external file format is database-scoped in SQL ServerSQL Server and SQL データ ウェアハウスSQL Data Warehouse. Parallel Data WarehouseParallel Data Warehouse ではサーバー スコープです。It is server-scoped in Parallel Data WarehouseParallel Data Warehouse.

形式オプションはすべてオプションであり、区切りテキスト ファイルにのみ適用されます。The format options are all optional and only apply to delimited text files.

いずれかの圧縮形式でデータが格納されていると、PolyBase は最初にデータの圧縮を解除してから、データ レコードを返します。When the data is stored in one of the compressed formats, PolyBase first decompresses the data before returning the data records.

制限事項と制約事項Limitations and Restrictions

区切りテキスト ファイルの行区切り記号は、Hadoop の LineRecordReader でサポートされている必要があります。The row delimiter in delimited-text files must be supported by Hadoop's LineRecordReader. つまり、"\r"、"'\n"、"\r\n" のいずれかでなければなりません。That is, it must be either '\r', '\n', or '\r\n'. これらの区切り記号はユーザー構成可能ではありません。These delimiters are not user-configurable.

サポートされる SerDe メソッドと RCFile の組み合わせ、およびサポートされるデータ圧縮方法は、この記事で前に示しました。The combinations of supported SerDe methods with RCFiles, and the supported data compression methods are listed previously in this article. すべての組み合わせがサポートされているわけではありません。Not all combinations are supported.

PolyBase の同時実行クエリの最大数は、32 です。The maximum number of concurrent PolyBase queries is 32. 32 の同時実行クエリを実行した場合、各クエリでは、外部ファイルの場所から最大で 33,000 個のファイルを読み取ることができます。When 32 concurrent queries are running, each query can read a maximum of 33,000 files from the external file location. ルート フォルダーと各サブフォルダーもファイルとしてカウントされます。The root folder and each subfolder also count as a file. 同時実行クエリの数が 32 未満である場合、外部ファイルの場所には 33,000 個を超えるファイルを含めることができます。If the degree of concurrency is less than 32, the external file location can contain more than 33,000 files.

外部テーブル内のファイル数には制限があるため、外部ファイルの場所のルートおよびサブフォルダーに格納するファイルの数は 30,000 個未満とすることをお勧めします。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. また、ルート ディレクトリの下に保持するサブフォルダーの数は少なくすることをお勧めします。Also, we recommend keeping the number of subfolders under the root directory to a small number. 参照されているファイルが多すぎる場合、Java 仮想マシンのメモリ不足例外が発生する可能性があります。When too many files are referenced, a Java Virtual Machine out-of-memory exception might occur.

PolyBase で Hadoop または Azure Blob Storage にデータをエクスポートするときは、データだけがエクスポートされて、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.

ロックLocking

EXTERNAL FILE FORMAT オブジェクトに対して共有ロックを取得します。Takes a shared lock on the EXTERNAL FILE FORMAT object.

パフォーマンスPerformance

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

Gzip で圧縮されたテキスト ファイルは分割可能ではありません。Gzip compressed text files are not splittable. Gzip で圧縮されたテキスト ファイルのパフォーマンスを向上させるには、複数のファイルを生成し、そのすべてを外部データ ソースの同じディレクトリに格納することをお勧めします。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. このようなファイル構造にすると、PolyBase は、複数のリーダー プロセスと圧縮解除プロセスを使って、より速くデータを圧縮解除できます。This file structure allows PolyBase to read and decompress the data faster by using multiple reader and decompression processes. 圧縮されたファイルの理想的な数は、計算ノードあたりのデータ リーダー プロセスの最大数です。The ideal number of compressed files is the maximum number of data reader processes per compute node. SQL ServerSQL Server および Parallel Data WarehouseParallel Data Warehouse では、データ リーダー プロセスの最大数はノードあたり 8 個です。ただし、Azure SQL Data Warehouse Gen2 のリーダー数はノードあたり 20 個です。In SQL ServerSQL Server and Parallel Data WarehouseParallel Data Warehouse, the maximum number of data reader processes is 8 per node except Azure SQL Data Warehouse Gen2 which is 20 readers per node. SQL データ ウェアハウスSQL Data Warehouse では、ノードごとのデータ リーダー プロセスの最大数は SLO によって変化します。In SQL データ ウェアハウスSQL Data Warehouse, the maximum number of data reader processes per node varies by SLO. 詳しくは、「Azure SQL Data Warehouse loading patterns and strategies」(Azure SQL Data Warehouse の読み込みパターンと戦略) をご覧ください。See Azure SQL Data Warehouse loading patterns and strategies for details.

使用例Examples

A.A. DELIMITEDTEXT 外部ファイル形式を作成するCreate a DELIMITEDTEXT external file format

この例では、テキスト区切りファイル用に textdelimited1 という名前の外部ファイル形式を作成します。This example creates an external file format named textdelimited1 for a text-delimited file. FORMAT_OPTIONS のオプション リストでは、ファイルのフィールドをパイプ文字 "|" を使って区切ることが指定されています。The options listed for FORMAT_OPTIONS specify that the fields in the file should be separated using a pipe character '|'. テキスト ファイルはまた、Gzip コーデックを使用して圧縮されます。The text file is also compressed with the Gzip codec. DATA_COMPRESSION を指定しないと、テキスト ファイルは圧縮されません。If DATA_COMPRESSION isn't specified, the text file is uncompressed.

区切りテキスト ファイルの場合、データ圧縮方法は、既定のコーデック "org.apache.hadoop.io.compress.DefaultCodec" または 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. RCFile 外部ファイル形式を作成するCreate an RCFile external file format

この例では、シリアル化/逆シリアル化方法として org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe を使う RCFile の外部ファイル形式を作成します。This example creates an external file format for a RCFile that uses the serialization/deserialization method org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe. また、データ圧縮方法として既定のコーデックを使用するように指定しています。It also specifies to use the Default Codec for the data compression method. DATA_COMPRESSION を指定しない場合の既定の設定は非圧縮です。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. ORC 外部ファイル形式を作成するCreate an ORC external file format

この例では、org.apache.io.compress.SnappyCodec データ圧縮方法でデータを圧縮する ORC ファイルの外部ファイル形式を作成します。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. DATA_COMPRESSION を指定しない場合の既定の設定は非圧縮です。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. PARQUET 外部ファイル形式を作成するCreate a PARQUET external file format

この例では、org.apache.io.compress.SnappyCodec データ圧縮方法でデータを圧縮する Parquet ファイルの外部ファイル形式を作成します。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. DATA_COMPRESSION を指定しない場合の既定の設定は非圧縮です。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. 区切りテキスト ファイルのスキップ ヘッダー行を作成する (Azure SQL DW のみ)Create a Delimited Text File Skipping Header Row (Azure SQL DW Only)

この例では、ヘッダー行が 1 つの CSV ファイルの外部ファイル形式を作成します。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)
)

参照See 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 SQL Data Warehouse) CREATE TABLE AS SELECT (Azure SQL Data Warehouse)
sys.external_file_formats (Transact-SQL)sys.external_file_formats (Transact-SQL)