Issue link: http://hub-nl.insight.com/i/692679
93 C H A P T E R 5 | Broader data access FIELD_TERMINATOR You use this argument as one of the format options for a delimited text file only. The default is a pipe character (|), but you can specify one or more characters if applicable. Enclose the field terminator in single quotation marks. STRING_DELIMITER This argument is another format option available only to a delimited text file. The default is an empty string, but you can specify one or more characters if applicable. Enclose the string delimiter in single quotation marks. DATE_FORMAT This argument specifies the date format to use for a delimited text file as a format option. If you do not supply one, PolyBase uses one of the following default formats: 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' Note You can learn more about working with date formats in delimited text files at "CREATE EXTERNAL FILE FORMAT (Transact-SQL)," https://msdn.microsoft.com/en-us/library/dn935026.aspx. USE_TYPE_DEFAULT By default, PolyBase stores missing values in a delimited text file as NULL. You can set this argument as a format option to TRUE if you prefer that PolyBase replace missing values with one of the following default values: 0 for a numeric column, empty string for a string column, and 1900-01-01 for a date column. DATA_COMPRESSION By default, data is uncompressed. You can optionally add this argument to specify the compression method for the external data. You can use org.apache.hadoop.io.compress.DefaultCodec with the delimited text, RCFILE, and ORC file types; org.apache.hadoop.io.compress.GzipCodec with the delimited text and PARQUET file types; or org.apache.hadoop.io.compress.SnappyCodec with the ORC and PARQUET file types. Example 5-33: Creating an external file format -- PARQUET file CREATE EXTERNAL FILE FORMAT MyPARQUETFileFormat WITH ( FORMAT_TYPE = PARQUET, DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec' ); -- ORC file CREATE EXTERNAL FILE FORMAT MyORCFileFormat WITH ( FORMAT_TYPE = ORC, DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec' ); -- RCFILE CREATE EXTERNAL FILE FORMAT MyRCFILEFileFormat WITH ( FORMAT_TYPE = RCFILE, SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe', DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec');