This page shows you how to configure and manage external data sources for use
with PolyBase and your Cloud SQL for SQL Server instances.
## Manage access to external objects
Cloud SQL supports operations to enable and disable PolyBase only. Users need
to manage SQL Server entities manually, using T-SQL statements.
Before managing your SQL server entities manually, review the following Microsoft
resources:
- [Create a MASTER KEY using T-SQL](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-ver16)
- [Configure PolyBase to access external data in Oracle](https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-oracle?view=sql-server-ver16)
- [Configure PolyBase to access external data in S3-compatible object storage](https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-s3-compatible?view=sql-server-ver16)
## Create an external data source
The following steps show you how to create an external data source to your
Cloud SQL for SQL Server instance.
SQL Server defines an [external data source](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver16&tabs=dedicated)
as a resource with connection information such as the server name, database
name, and connection string.
SQL Server defines [external tables](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16&tabs=dedicated)
as metadata objects within SQL Server that point to the data in the external
data sources. They define the schema of the external data as it will be
presented within SQL Server.
This procedure requires creating a database-scoped credential (DSC) for your
external data source.
[DSCs](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-database-scoped-credential-transact-sql?view=sql-server-ver16')
store the authentication information needed to connect to the external
data sources. These credentials are encrypted by the database master key (DMK).
The [DMK](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine?view=sql-server-ver16#database-master-key)
serves as the root of the encryption hierarchy within a database, protecting
secrets in database-scoped credentials. This symmetric key is encrypted by
the service master key (SMK) and a user-provided password. The DMK isn't
automatically created and requires explicit management by the user using T-SQL.
The [SMK](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine?view=sql-server-ver16#service-master-key)
serves as the root of the encryption hierarchy for a specific instance of
SQL Server. This symmetric key is automatically generated by the SQL Server
itself, the first time it starts, and is used to protect other security
objects like DMKs and login passwords.
### Oracle
Complete the following steps in SQL Server.
1. Create a database-scoped credential (DSC) for Oracle.
1. If one doesn't already exist, then switch to a user database to create a master key:
```sql
USE USER_DB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MK_PASSWORD';
```
Replace the following:
- USER_DB: the user database you want to use to create the master key. For example, `polybasedb`.
- MK_PASSWORD: the password for the master key you want to use. Make sure to save the password for the master key for later use.
2. Create a database-scoped credential for the Oracle data source.
```sql
CREATE DATABASE SCOPED CREDENTIAL DB_CREDENTIAL_NAME WITH IDENTITY = 'EXTERNAL_DATABASE_USERNAME', SECRET = 'EXTERNAL_DATABASE_PASSWORD';
```
Replace the following:
- DB_CREDENTIAL_NAME: the credential name you want to use for the external database.
- EXTERNAL_DATABASE_USERNAME: the username you want to use to access the external database.
- EXTERNAL_DATABASE_PASSWORD: the password for the external database. External data source credentials need to be updated when rotated and must be managed manually by you.
2. Create an external data source for Oracle.
```sql
CREATE EXTERNAL DATA SOURCE DATA_SOURCE_NAME
WITH (
LOCATION = 'oracle://SERVER_IP:PORT',
PUSHDOWN = PUSHDOWN,
CREDENTIAL = DB_CREDENTIAL_NAME
);
```
Replace the following:
- DATA_SOURCE_NAME: the name you want to use for the external database.
- SERVER_IP: the IP of the server or the hostname.
- PORT: the port you want to use.
- PUSHDOWN: Required. [Pushdown](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver16&tabs=dedicated#pushdown--on--off-1) is a capability offered in SQL Server and must be configured. Set the value to `ON` or `OFF`, depending on your use case.
- DB_CREDENTIAL_NAME: the credential you just created.
3. Create an external table.
Define the schema and structure of the external data:
- Create the external table definition. The schema must match the remote table.
- Define columns with appropriate SQL Server data types and collations.
- The location is the remote table path.
```sql
CREATE EXTERNAL TABLE EXTERNAL_TABLE_NAME(
COLUMN_NAME_1 DATA_TYPE_1 COLLATE COLLATION_1,
COLUMN_NAME_2 DATA_TYPE_2 COLLATE COLLATION_2,
...
)
WITH(
LOCATION = 'REMOTE_DATABASE.REMOTE_USERNAME.REMOTE_TABLE_NAME',
DATA_SOURCE = DB_DATA_SOURCE_NAME
);
```
Replace the following:
- EXTERNAL_TABLE_NAME: the name of the external table you want to create.
- COLUMN_NAME_1: the name of the first column of the table.
- DATA_TYPE_1: the data type of the first column.
- COLLATION_1: the collation you want to use for the first column.
- COLUMN_NAME_2: the name of the second column of the table.
- DATA_TYPE_2: the data type of the second column.
- COLLATION_2: the collation you want to use for the second column.
- REMOTE_DATABASE: the name of the remote database.
- REMOTE_USERNAME: the username of the remote user.
- REMOTE_TABLE_NAME: the name of the remote table.
- DB_DATA_SOURCE_NAME: the name of the external data source.
4. Query the external Oracle table you created.
```sql
SELECT TOP 10 * FROM EXTERNAL_TABLE_NAME;
```
Replace the following:
- EXTERNAL_TABLE_NAME: the name of the external table you want to query.
### Cloud Storage
Complete the following steps in SQL Server.
1. Create a database-scoped credential for Cloud Storage.
1. If one doesn't already exist, then switch to a user database to create a master key:
```sql
USE USER_DB;
CREATE MASTER KEY ENCRYPTION BY PASSWORD='MK_PASSWORD';
```
Replace the following:
- USER_DB: the user database you want to use to create the master key. For example, `polybasedb`.
- MK_PASSWORD: the password you want to use for the master key. Make sure to save the password for the master key for later use.
2. Cloud Storage requires an access key ID and a secret key ID:
```sql
CREATE DATABASE SCOPED CREDENTIAL STORAGE_CREDENTIAL_NAME WITH IDENTITY = 'S3 Access Key', SECRET = 'ACCESS_KEY_ID:SECRET_KEY_ID';
```
Replace the following:
- STORAGE_CREDENTIAL_NAME: the name of the Cloud Storage credential you want to use.
- ACCESS_KEY_ID: the access key ID.
- SECRET_KEY_ID: the secret key ID.
Only [basic authentication](https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-configure-s3-compatible?view=sql-server-ver17#basic-authentication) is supported.
2. Create an external data source.
```sql
CREATE EXTERNAL DATA SOURCE STORAGE_DATA_SOURCE_NAME
WITH (
LOCATION = 's3://storage.googleapis.com/',
CREDENTIAL = STORAGE_CREDENTIAL_NAME
);
```
Replace the following:
- STORAGE_DATA_SOURCE_NAME: the name of the external data source in Cloud Storage.
- STORAGE_CREDENTIAL_NAME: the name of the credential for the external storage resource.
3. Create an external table for Cloud Storage.
Define the format and structure of the external data:
- Create the external file. The schema must match the file structure.
- Define columns with appropriate SQL Server data types and collations.
- The location is the path within the data source.
```sql
CREATE EXTERNAL FILE FORMAT FILE_FORMAT_NAME
WITH (
FORMAT_TYPE = FORMAT_TYPE,
FORMAT_OPTIONS (
FIELD_TERMINATOR = 'FIELD_TERMINATOR',
STRING_DELIMITER = 'DELIMITER',
FIRST_ROW = FIRST_ROW
)
);
CREATE EXTERNAL TABLE FILE_EXTERNAL_TABLE_NAME (
COLUMN_NAME_1 DATA_TYPE_1,
COLUMN_NAME_2 DATA_TYPE_2,
...
)
WITH (
LOCATION = 'PATH_TO_BUCKET/FILENAME',
DATA_SOURCE = STORAGE_DATA_SOURCE_NAME,
FILE_FORMAT = FILE_FORMAT_NAME
);
```
Replace the following:
- FILE_FORMAT_NAME: the name of the external file format you want to create.
- FORMAT_TYPE: the format type you want to use, such as `DELIMITEDTEXT`. For a list of supported values, see [Supported file formats](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver16&tabs=delimited).
- FIELD_TERMINATOR: the field delimiter you want to use.
- DELIMITER: the string delimiter you want to use.
- FIRST_ROW: the row from which you want to start reading.
- FILE_EXTERNAL_TABLE_NAME: the name of the external table you want to create.
- COLUMN_NAME_1: the name of the first column of the table.
- DATA_TYPE_1: the data type of the first column.
- COLUMN_NAME_2: the name of the second column of the table.
- DATA_TYPE_2: the data type of the second column.
- PATH_TO_BUCKET: the full path to the Cloud Storage bucket.
- FILENAME: the filename located within the Cloud Storage bucket.
- STORAGE_DATA_SOURCE_NAME: the name of the external data source in Cloud Storage.
- FILE_FORMAT_NAME: the name of the external file format.
4. Query the external Cloud Storage table you created.
```sql
SELECT * FROM FILE_EXTERNAL_TABLE_NAME;
```
Replace the following:
- FILE_EXTERNAL_TABLE_NAME: the name of the external table you want to query.
## Manage the database master key after a database restore operation
The database-scoped credentials used to access external data sources are encrypted
using a database master key (DMK).
The DMK is encrypted with the service master key (SMK) of the source instance
and user-provided DMK password. On the source instance, SQL Server silently
opens the DMK using an SMK.
If you restore a Cloud SQL for SQL Server instance, the DMK won't open automatically
on the new instance due to changes in the SMK.
As a result, you need to first decrypt the DMK using the user password established
during its creation and re-encrypt it with the target instance SMK.
For more information, see the following Microsoft resources:
- [Open a database master key using a password](https://learn.microsoft.com/en-us/sql/t-sql/statements/open-master-key-transact-sql?view=sql-server-ver16)
- [Rotate a database master key](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-master-key-transact-sql?view=sql-server-ver16)
## Logs
The following PolyBase logs are available in [Logs Explorer](https://docs.cloud.google.com/logging/docs/view/logs-explorer-interface.md.txt):
- `Polybase_Dms_errors.log`
- `Polybase_Dms_movement.log`
- `Polybase_DWEngine_errors.log`
- `Polybase_DWEngine_movement.log`
- `Polybase_DWEngine_server.log`
For more information, see [Microsoft documentation on PolyBase troubleshooting](https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-troubleshooting?view=sql-server-ver17#log-file-locations).
## What's next
- [Cloud SQL for SQL Server support for PolyBase](https://docs.cloud.google.com/sql/docs/sqlserver/about-polybase.md.txt)
- [Enable and disable PolyBase](https://docs.cloud.google.com/sql/docs/sqlserver/enable-and-disable-polybase.md.txt)