Customer-managed Cloud KMS keys

Console

1. Go to the Dashboardpage in the Google Cloud console.

   Go to the Dashboard page

2. Click the Select fromdrop-down list at the top of the page. In the Select Fromwindow that appears, select your project.

3. Both the project ID and project number are displayed on the project Dashboard Project infocard:

   

4. In the following string, replace PROJECT_NUMBER with your project number. The new string identifies your BigQuery service account ID.

   bq- PROJECT_NUMBER 
   @bigquery-encryption.iam.gserviceaccount.com

bq

Use the bq show command with the --encryption_service_account flag to determine the service account ID:

bq show --encryption_service_account

The command displays the service account ID:

ServiceAccountID
-------------------------------------------------------------
bq- PROJECT_NUMBER 
@bigquery-encryption.iam.gserviceaccount.com

Console

1. Open the Cryptographic Keyspage in the Google Cloud console.

   Open the Cryptographic Keys page

2. Click the name of the key ring that contains the key.

3. Click the checkbox for the encryption key to which you want to add the role. The Permissionstab opens.

4. Click Add member.

5. Enter the email address of the service account, bq- PROJECT_NUMBER @bigquery-encryption.iam.gserviceaccount.com .

   • If the service account is already on the members list, it has existing roles. Click the current role drop-down list for the bq- PROJECT_NUMBER @bigquery-encryption.iam.gserviceaccount.com service account.

6. Click the drop-down list for Select a role, click Cloud KMS, and then click the Cloud KMS CryptoKey Encrypter/Decrypterrole.

7. Click Saveto apply the role to the bq- PROJECT_NUMBER @bigquery-encryption.iam.gserviceaccount.com service account.

gcloud

You can use the Google Cloud CLI to assign the role:

gcloud kms keys add-iam-policy-binding \
--project= KMS_PROJECT_ID 
\
--member serviceAccount:bq- PROJECT_NUMBER 
@bigquery-encryption.iam.gserviceaccount.com \
--role roles/cloudkms.cryptoKeyEncrypterDecrypter \
--location= KMS_KEY_LOCATION 
\
--keyring= KMS_KEY_RING 
\ KMS_KEY 

Replace the following:

• KMS_PROJECT_ID : the ID of your Google Cloud project that is running Cloud KMS
• PROJECT_NUMBER : the project number (not project ID) of your Google Cloud project that is running BigQuery
• KMS_KEY_LOCATION : the location name of your Cloud KMS key
• KMS_KEY_RING : the key ring name of your Cloud KMS key
• KMS_KEY : the key name of your Cloud KMS key

Console

1. Open the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. In the Explorerpanel, expand your project and select a dataset.

3. Expand the more_vert Actionsoption and click Open.

4. In the details panel, click Create table add_box .

5. On the Create tablepage, fill in the information needed to create an empty table with a schema definition . Before you click Create Table, set the encryption type and specify the Cloud KMS key to use with the table:

   1. Click Advanced options.
   2. Click Customer-managed key.
   3. Select the key. If the key you want to use is not listed, enter the resource ID for the key.

6. Click Create table.

SQL

Use the CREATE TABLE statement with the kms_key_name option:

1. In the Google Cloud console, go to the BigQuerypage.

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATE TABLE DATASET_ID 
   . TABLE_ID 
   (
     name STRING, value INT64
   ) OPTIONS 
   (
       kms_key_name
         = 'projects/ KMS_PROJECT_ID 
   /locations/ LOCATION 
   /keyRings/ KEY_RING 
   /cryptoKeys/ KEY 
   ');

3. Click play_circle Run.

For more information about how to run queries, see Run an interactive query .

bq

You can use the bq command-line tool with the --destination_kms_key flag to create the table. The --destination_kms_key flag specifies the resource ID of the key to use with the table.

To create an empty table with a schema:

bq mk --schema name:string,value:integer -t \
--destination_kms_key projects/ KMS_PROJECT_ID 
/locations/ LOCATION 
/keyRings/ KEY_RING 
/cryptoKeys/ KEY 
\ DATASET_ID 
. TABLE_ID 

To create a table from a query:

bq query --destination_table= DATASET_ID 
. TABLE_ID 
\
--destination_kms_key projects/ KMS_PROJECT_ID 
/locations/ LOCATION 
/keyRings/ KEY_RING 
/cryptoKeys/ KEY 
\
"SELECT name,count FROM DATASET_ID 
. TABLE_ID 
WHERE gender = 'M' ORDER BY count DESC LIMIT 6"

For more information about the bq command-line tool, see Using the bq command-line tool .

Terraform

Use the google_bigquery_table resource.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

The following example creates a table named mytable , and also uses the google_kms_crypto_key and google_kms_key_ring resources to specify a Cloud Key Management Service key for the table.

To run this example, you must enable the Cloud Resource Manager API and the Cloud Key Management Service API .

 resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id          = google_bigquery_dataset.default.dataset_id
  table_id            = "mytable"
  deletion_protection = false # set to "true" in production

  schema = <<EOF
[
  {
    "name": "ID",
    "type": "INT64",
    "mode": "NULLABLE",
    "description": "Item ID"
  },
  {
    "name": "Item",
    "type": "STRING",
    "mode": "NULLABLE"
  }
]
EOF

  encryption_configuration {
    kms_key_name = google_kms_crypto_key.crypto_key.id
  }

  depends_on = [google_project_iam_member.service_account_access]
}

resource "google_kms_crypto_key" "crypto_key" {
  name     = "example-key"
  key_ring = google_kms_key_ring.key_ring.id
}

resource "random_id" "default" {
  byte_length = 8
}

resource "google_kms_key_ring" "key_ring" {
  name     = "${random_id.default.hex}-example-keyring"
  location = "us"
}

# Enable the BigQuery service account to encrypt/decrypt Cloud KMS keys
data "google_project" "project" {
}

resource "google_project_iam_member" "service_account_access" {
  project = data.google_project.project.project_id
  role    = "roles/cloudkms.cryptoKeyEncrypterDecrypter"
  member  = "serviceAccount:bq-${data.google_project.project.number}@bigquery-encryption.iam.gserviceaccount.com"
} 

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

1. Launch Cloud Shell .

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT= PROJECT_ID 
   

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module ).

1. In Cloud Shell , create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf . In this tutorial, the file is referred to as main.tf .

   mkdir DIRECTORY 
   && cd DIRECTORY 
   && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf .

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

Apply the changes

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Go

 import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// createTableWithCMEK demonstrates creating a table protected with a customer managed encryption key.
func createTableWithCMEK(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta := &bigquery.TableMetadata{
		EncryptionConfig: &bigquery.EncryptionConfig{
			// TODO: Replace this key with a key you have created in Cloud KMS.
			KMSKeyName: "projects/cloud-samples-tests/locations/us/keyRings/test/cryptoKeys/test",
		},
	}
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	return nil
} 

Java

 import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.EncryptionConfiguration;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create a cmek table
public class CreateTableCMEK {

  public static void runCreateTableCMEK() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String kmsKeyName = "MY_KEY_NAME";
    Schema schema =
        Schema.of(
            Field.of("stringField", StandardSQLTypeName.STRING),
            Field.of("booleanField", StandardSQLTypeName.BOOL));
    // i.e. projects/{project}/locations/{location}/keyRings/{key_ring}/cryptoKeys/{cryptoKey}
    EncryptionConfiguration encryption =
        EncryptionConfiguration.newBuilder().setKmsKeyName(kmsKeyName).build();
    createTableCMEK(datasetName, tableName, schema, encryption);
  }

  public static void createTableCMEK(
      String datasetName, String tableName, Schema schema, EncryptionConfiguration configuration) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);
      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo =
          TableInfo.newBuilder(tableId, tableDefinition)
              .setEncryptionConfiguration(configuration)
              .build();

      bigquery.create(tableInfo);
      System.out.println("Table cmek created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table cmek was not created. \n" + e.toString());
    }
  }
} 

Python

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries . For more information, see the BigQuery Python API reference documentation .

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries .

 from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to the full name of the table you want to create.
table_id = "your-project.your_dataset.your_table_name"

# Set the encryption key to use for the table.
# TODO: Replace this key with a key you have created in Cloud KMS.
kms_key_name = "projects/your-project/locations/us/keyRings/test/cryptoKeys/test"

table = bigquery.Table(table_id)
table.encryption_configuration = bigquery.EncryptionConfiguration(
    kms_key_name=kms_key_name
)
table = client.create_table(table)  # API request

print(f"Created {table_id}.")
print(f"Key: {table.encryption_configuration.kms_key_name}.")