Skip to content

polybase-configure-sql-server.md

Latest commit

 

History

History
82 lines (58 loc) · 3.75 KB

polybase-configure-sql-server.md

File metadata and controls

82 lines (58 loc) · 3.75 KB
title Configure PolyBase to access external data in SQL Server | Microsoft Docs
ms.custom
ms.date 04/23/2019
ms.prod sql
ms.technology polybase
ms.topic conceptual
author Abiola
ms.author aboke
ms.reviewer jroth
manager craigg
monikerRange >= sql-server-linux-ver15 || >= sql-server-ver15 || =sqlallproducts-allversions

Configure PolyBase to access external data in SQL Server

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]

This article explains how to use PolyBase on a SQL Server instance to query external data in another SQL Server instance.

Prerequisites

If you haven't installed PolyBase, see PolyBase installation. The installation article explains the prerequisites.

Before creating a database scoped credential a Master Key must be created.

Configure a SQL Server external data source

To query the data from a SQL Server data source, you must create external tables to reference the external data. This section provides sample code to create these external tables.

For optimal query performance, create statistics on external table columns, especially for the ones used for joins, filters, and aggregates.

The following Transact-SQL commands are used in this section:

  1. Create a database scoped credential for accessing the MongoDB source.

    /*  specify credentials to external data source
*  IDENTITY: user name for external source.  
*  SECRET: password for external source.
*/
CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials   
WITH IDENTITY = 'username', Secret = 'password';

  2. Create an external data source with CREATE EXTERNAL DATA SOURCE.

    /*  LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
*  PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
*  CREDENTIAL: the database scoped credential, created above.
*/
CREATE EXTERNAL DATA SOURCE SQLServerInstance
WITH ( LOCATION = 'sqlserver://SqlServer',
-- PUSHDOWN = ON | OFF,
CREDENTIAL = SQLServerCredentials);

  3. Optional: Create statistics on an external table.

[!INCLUDEfreshInclude]

We recommend creating statistics on external table columns, especially the ones used for joins, filters and aggregates, for optimal query performance.

```sql
CREATE STATISTICS statistics_name ON customer (C_CUSTKEY) WITH FULLSCAN;
```

Important

Once you have created an external data source, you can use the CREATE EXTERNAL TABLE command to create a queryable table over that source.

SQL Server connector compatible types

You can make a connection to other data sources that recognizes a SQL Server connection. Use the SQL Server PolyBase connector to create an external table of both Azure SQL Data Warehouse and Azure SQL Database. To accomplish this task, follow the same steps listed previously. Make sure the database scoped credential, server address, port, and location string correlate to that of the compatible data source you want to connect to.

Next steps

To learn more about PolyBase, see Overview of SQL Server PolyBase.