Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 108 of 212

96 C H A P T E R 5 | Broader data access EXTERNALPUSHDOWN) to the end of your statement as a query hint to respectively force or disable pushdown computations. Example 5-36: Joining a PolyBase external table with a SQL Server table SELECT CustomerLastName, CustomerFirstName, CustomerEmail, cs.TirePressure FROM Customer c INNER JOIN CarSensorData cs ON c.VIN = cs.VIN WHERE cs.TirePressure < 20 You can also export data from SQL Server to your external data source, but first you must configure SQL Server to allow this capability by using sp_configure, as shown in Example 5-37. Example 5-37: Configure PolyBase export from SQL Server sp_configure 'allow polybase export',1; reconfigure; Then use the INSERT INTO statement, as shown in Example 5-38, to export data. If the destination file or directory that you specified in the external table definition does not exist, your use of the INSERT INTO statement creates it. Example 5-38: Exporting data from SQL Server to a PolyBase external table INSERT INTO [dbo].[CarSensorDataArchive] SELECT * FROM CarSensorData_local; Troubleshooting with PolyBase system views and DMVs You can use new DMVs in SQL Server 2016 to monitor and troubleshoot PolyBase. Use the following query to identify external tables in a database: SELECT name, type from sys.tables WHERE is_external <> 0 To review the current set of PolyBase objects defined within a database, as shown in Figure 5-11, use the following views: sys.external_data_sources Displays information about external data sources, including data_source_id, name, location, type_desc, type, resource_manager_location, credential_id. Not shown in Figure 5-11 are the following columns related to elastic databases only: database_name and shard_map_name. sys.external_file_formats Displays information about external file formats in the following columns: file_format_id, name, format_type, field_terminator, string_delimiter, use_type_default, row_terminator, and encoding. Not shown in Figure 5-11 are the following columns: date_format, serde_method, and data_compression. sys.external_tables Displays information about external tables in the following columns: name, object_id, schema_id, parent_object_id, type, type_desc, data_source_id, file_format_id, location, reject_type, and reject_value. Not shown in Figure 5-11 are the following columns: principal_id, create_date, modify_date, is_ms_shipped, is_published, is_schema_published,

Articles in this issue

Archives of this issue

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