Enable and disable PolyBase

MySQL   |  PostgreSQL   |  SQL Server

This page shows you how to enable and disable PolyBase for your Cloud SQL for SQL Server instances.

For overview information about PolyBase, see Cloud SQL for SQL Server support for PolyBase.

For information about how to set up configuration, see Configure external data sources for use with PolyBase.

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL, Cloud SQL Admin, and Compute Engine APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

Enable the APIs

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

• Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
• Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

If you're using an existing project for this guide, verify that you have the permissions required to complete this guide. If you created a new project, then you already have the required permissions.

Verify that billing is enabled for your Google Cloud project.

Enable the Cloud SQL, Cloud SQL Admin, and Compute Engine APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

Enable the APIs

1. Configure your network to allow traffic between Cloud SQL for SQL Server and your external data source.
   • If SQL Server can't establish a successful connection, then the T-SQL command to set up or query the external data source fails.

   • Whether using a public or private IP address, make sure your Cloud SQL instance, and any supporting Google Cloud resources, have access to your external data source within the firewall of the data source.

     If you are using the public IP address of the Cloud SQL instance, then you also need to obtain access to the underlying Compute Engine VM and must obtain its IP address. Since this underlying VM is not externalized to users, you must contact Google Cloud Support to retrieve its IP address. This IP address might change if the instance is recreated.

2. To enable or disable PolyBase, you need the following permissions:
   • cloudsql.instances.get
   • cloudsql.instances.update
   Either of the following roles provide the previous permissions:
   • roles/cloudsql.editor
   • roles/cloudsql.admin

   We recommend using the principle of least privilege when granting access.

   Alternatively, you can also assign these permissions to a custom role.

3. Review your instance's existing warehouse database names.

   When PolyBase is enabled, three new PolyBase data warehouse databases are created:

   • DWConfiguration
   • DWQueue
   • DWDiagnostics
   If you enable PolyBase on an instance that already contains user databases with these restricted DW names, then SQL Server deletes and recreates them.

   Before enablement, use a tool like SQL Server Management Studio (SSMS) to verify whether any databases with these names already exist. If so, contact Google Cloud support for help before you continue.

   Certain restrictions apply to these system databases:

   • You can't use the Google Cloud console, Cloud SQL Admin API, and gcloud CLI to perform certain operations on DW databases:
     • Create
     • Delete
     • List
     • Import
     You can, however, use the export command to take a backup of these databases.
   • You can't access DW databases using SQL Server Management Studio (SSMS).
   • If the instance is replicated, DW databases are not copied to the new resource.
   • If the instance is restored using PITR, DW databases are not copied to the new resource.
   • DW databases are retained even after PolyBase is disabled.

   For more information, see the following resources:

   • Install PolyBase on Linux
   • PolyBase feature summary

PolyBase enablement using database flags

You can enable PolyBase with your Cloud SQL for SQL Server instance using database flags. The required flags vary based on the type of external data source you want to use:

• The polybase database flag is required for all external data sources.
• If using Cloud Storage, you also need to apply the Microsoft SQL Server trace flag, 13702. Enabling this flag also enables other trace flags required for PolyBase support.

The following sections show you how to enable PolyBase on new and existing instances.

Create a new instance with PolyBase enabled

To create a new instance with PolyBase enabled, run the following gcloud command:

Oracle

gcloud sql instances create INSTANCE_NAME \
  --database-version=VERSION --region=LOCATION \
  --root-password=PASSWORD --edition=EDITION \
  --cpu=NUMBER_OF_CPUs --memory=MEMORY_SIZE \
  --database-flags="DATABASE_FLAG"

Replace the following:

• INSTANCE_NAME: the name of the Cloud SQL instance.
• VERSION: the database version you want to use.
• LOCATION: the location of the Cloud SQL instance.
• PASSWORD: the password of the Cloud SQL instance.
• EDITION: the database edition you want to use.
• NUMBER_OF_CPUs: the number of CPUs you want to use.
• MEMORY_SIZE: the amount of memory you want to use.

• DATABASE_FLAG: the database flag value required to enable PolyBase, regardless of the type of external data source.

  You must enter, cloud sql enable polybase=on.

  For more information, see Database flags.

Cloud Storage

gcloud sql instances create INSTANCE_NAME \
  --database-version=VERSION --region=LOCATION \
  --root-password=PASSWORD --edition=EDITION \
  --cpu=NUMBER_OF_CPUs --memory=MEMORY_SIZE \
  --database-flags="DATABASE_FLAG_CLOUD_STORAGE"

Replace the following:

• INSTANCE_NAME: the name of the Cloud SQL instance.
• VERSION: the database version you want to use.
• LOCATION: the location of the Cloud SQL instance.
• PASSWORD: the password of the Cloud SQL instance.
• EDITION: the database edition you want to use.
• NUMBER_OF_CPUs: the number of CPUs you want to use.
• MEMORY_SIZE: the amount of memory you want to use.

• DATABASE_FLAG_CLOUD_STORAGE: the database flag values required to enable PolyBase. If your external data source is Cloud Storage, then you must enter, cloud sql enable polybase=on,13702=on.

  For more information, see Database flags.

Enable PolyBase on an existing instance

To enable PolyBase on an existing instance, run the following gcloud command:

Oracle

gcloud sql instances patch INSTANCE_NAME \
--database-flags="DATABASE_FLAG"

Replace the following:

• INSTANCE_NAME: the name of the Cloud SQL instance.
• DATABASE_FLAG: the database flag value required to enable PolyBase, regardless of the type of external data source.

You must enter, cloud sql enable polybase=on.

This example is for illustrative purposes only. Running a patch operation with a specific database flag setting replaces any existing database flags. Make sure to include any existing database flags you want to keep. If you omit a previously set database flag, then the omitted flag is reverted to its default value.

For more information, see Database flags.

Cloud Storage

gcloud sql instances patch INSTANCE_NAME \
--database-flags="DATABASE_FLAG_CLOUD_STORAGE"

Replace the following:

• INSTANCE_NAME: the name of the Cloud SQL instance.
• DATABASE_FLAG_CLOUD_STORAGE: the database flag value required to enable PolyBase, regardless of the type of external data source.

You must enter, cloud sql enable polybase=on,13702=on.

This example is for illustrative purposes only. Running a patch operation with a specific database flag setting replaces any existing database flags. Make sure to include any existing database flags you want to keep. If you omit a previously set database flag, then the omitted flag is reverted to its default value.

For more information, see Database flags.

Check instance configuration

To check whether PolyBase is enabled on an existing instance, run the following gcloud command:

gcloud

gcloud sql instances describe INSTANCE_NAME \
  --format="value(settings.databaseFlags)"

Replace the following:

• INSTANCE_NAME: the name of the Cloud SQL instance.

Disable PolyBase

To disable PolyBase on an instance, run the following gcloud command:

gcloud

gcloud sql instances patch INSTANCE_NAME \
--database-flags="cloud sql enable polybase=off"

Replace the following:

• INSTANCE_NAME: the name of the Cloud SQL instance.

When PolyBase is disabled, the following PolyBase resources aren't deleted:

• DW system databases
• Database master key (DMK)
• Data sources
• Data scoped credentials
• External tables

These resources are kept in the event you want to re-enable the feature in the future.

What's next

• Cloud SQL for SQL Server support for PolyBase
• Configure external data sources for use with PolyBase