Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link: http://hub-nl.insight.com/i/692679

Contents of this Issue

Navigation

Page 105 of 212

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

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016