Netherlands: Software

Introductie van Micorosoft SQL Server 2016

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

Contents of this Issue

Navigation

Page 106 of 212

94 C H A P T E R 5 | Broader data access -- Delimited text file CREATE EXTERNAL FILE FORMAT MyDelimitedTextFileFormat WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', USE_TYPE_DEFAULT = TRUE ), DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ); External table You use the CREATE EXTERNAL TABLE statement to describe the structure of your data in more detail by specifying column names and data types. The number of columns and data types must match your data or a mismatched row is rejected when you query the data. When you create an external table, SQL Server stores only the metadata and basic statistics about external data. When your data is stored in blob storage, you use the WITH clause to provide the directory relative to the blob storage container that holds your data, reference the external data source, and specify the file format, as shown in Example 5-34. Example 5-34: Creating an external table for file stored in blob storage CREATE EXTERNAL TABLE [dbo].[CarSensorData] ( [VIN] VARCHAR(50) NOT NULL, [Model] VARCHAR(50) NOT NULL, [TimeStamp] DATETIME NOT NULL, [EngineTemperature] int NOT NULL, [TirePressure] int NOT NULL ) WITH (LOCATION='/rawcarevents/file_1.csv', DATA_SOURCE = AzureBlobStorage, FILE_FORMAT = MyDelimitedTextFileFormat, REJECT_TYPE = VALUE, REJECT_VALUE = 0 ); You can optionally include reject options. For REJECT_TYPE, you can specify VALUE to fail a PolyBase query when the number of rows rejected exceeds the number specified by the REJECT_VALUE argument. As an alternative, you can set REJECT_TYPE to PERCENTAGE to fail when the percentage of rows rejected exceeds the percentage specified by REJECT_VALUE. The percentage of rows depends on the current interval, which is set by the REJECT_SAMPLE_VALUE argument. PolyBase attempts to retrieve the number of rows that you set in this argument and calculates the percentage of failures based on this number. The percentage is recalculated when each set of rows is retrieved. When your data is stored in Hadoop, the structure of the CREATE EXTERNAL TABLE statement is similar to the one you use for blob storage. You specify the data directory path from the root directory, reference the external data source, and specify the file format, as shown in Example 5-35. Example 5-35: Creating an external table for a file stored in Hadoop CREATE EXTERNAL TABLE [dbo].[HadoopCarSensorData] ( [VIN] VARCHAR(50) NOT NULL, [Model] VARCHAR(50) NOT NULL,

Articles in this issue

Archives of this issue

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