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 left pane, click explore Explorer:

   

   If you don't see the left pane, click last_page Expand left paneto open the pane.

3. In the Explorerpane, expand your project, click Datasets, and then click a dataset. The dataset opens in a tab.

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

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"

  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 
 
 } 
 ." 
 )