Console

You cannot update the partition expiration in the Google Cloud console.

SQL

Use theALTER TABLE SET OPTIONS statement.The following exampleupdates the expiration to 5 days. To remove the partition expiration for atable, setpartition_expiration_days toNULL.

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

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

   ALTERTABLEmydataset.mytableSETOPTIONS(-- Sets partition expiration to 5 dayspartition_expiration_days=5);

3. Clickplay_circleRun.

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

bq

Issue thebq update command with the--time_partitioning_expirationflag. If you are updating a partitioned table in a project other than yourdefault project, add the project ID to the dataset name in the followingformat:project_id:dataset.

bqupdate\--time_partitioning_expirationinteger_in_seconds\--time_partitioning_typeunit_time\project_id:dataset.table

Where:

• integer is the default lifetime (in seconds) for the table'spartitions. There is no minimum value. The expiration time evaluates tothe partition's date plus the integer value. If you specify0, thepartition expiration is removed, and the partitionnever expires. Partitions with no expiration must be manually deleted.
• unit_time is eitherDAY,HOUR,MONTH, orYEAR, basedon the table's partitioning granularity. This value must match thegranularity that you set when you created the table.
• project_id is your project ID.
• dataset is the name of the dataset that contains the tableyou're updating.
• table is the name of the table you're updating.

Examples:

Enter the following command to update the expiration time of partitions inmydataset.mytable to 5 days (432000 seconds).mydataset is in yourdefault project.

bq update --time_partitioning_expiration 432000 mydataset.mytable

Enter the following command to update the expiration time of partitions inmydataset.mytable to 5 days (432000 seconds).mydataset is inmyotherproject, not your default project.

bq update \--time_partitioning_expiration 432000 \myotherproject:mydataset.mytable

API

Call thetables.patchmethod and use thetimePartitioning.expirationMs property to update thepartition expiration in milliseconds. Because thetables.update methodreplaces the entire table resource, thetables.patch method is preferred.

Console

You cannot use the Google Cloud console to require partition filters aftera partitioned table is created.

SQL

Use theALTER TABLE SET OPTIONS statementto update the partition filter requirement. The followingexample updates the requirement totrue:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

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

   ALTERTABLEmydataset.mypartitionedtableSETOPTIONS(require_partition_filter=true);

3. Clickplay_circleRun.

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

bq

To update a partitioned table to require partition filters by using the bq command-line tool,enter thebq update command and supply the--require_partition_filterflag.

To update a partitioned table in a project other than your default project,add the project ID to the dataset in the following format:project_id:dataset.

For example:

To updatemypartitionedtable inmydataset in your default project,enter:

bqupdate--require_partition_filtermydataset.mytable

To updatemypartitionedtable inmydataset inmyotherproject,enter:

bqupdate--require_partition_filtermyotherproject:mydataset.mytable

API

Call thetables.patchmethod and set therequirePartitionFilter property totrue to requirepartition filters. Because thetables.update method replaces the entiretable resource, thetables.patch method is preferred.

Java

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.Table;// Sample to update require partition filter on a table.publicclassUpdateTableRequirePartitionFilter{publicstaticvoidrunUpdateTableRequirePartitionFilter(){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringtableName="MY_TABLE_NAME";updateTableRequirePartitionFilter(datasetName,tableName);}publicstaticvoidupdateTableRequirePartitionFilter(StringdatasetName,StringtableName){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.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();Tabletable=bigquery.getTable(datasetName,tableName);table.toBuilder().setRequirePartitionFilter(true).build().update();System.out.println("Table require partition filter updated successfully");}catch(BigQueryExceptione){System.out.println("Table require partition filter was not updated \n"+e.toString());}}}

Console

Copying partitions is not supported by the Google Cloud console.

bq

To copy a partition, use the bq command-line tool'sbq cp (copy)command with a partition decorator ($date) such as$20160201.

Optional flags can be used to control the write disposition of thedestination partition:

• -a or--append_table appends the data from the source partition to anexisting table or partition in the destination dataset.
• -f or--force overwrites an existing table or partition in thedestination dataset and doesn't prompt you for confirmation.
• -n or--no_clobber returns the following error message if the table orpartition exists in the destination dataset:Table '<var>project_id:dataset.table</var> or <var>table$date</var>'already exists, skipping. If-n is not specified, the default behavioris to prompt you to choose whether to replace the destination table orpartition.
• --destination_kms_key is the customer-managed Cloud KMS keyused to encrypt the destination table or partition.

Thecp command does not support the--time_partitioning_field or--time_partitioning_type flags. You cannot use a copy job to convert aningestion-time partitioned table into a partitioned table.

--destination_kms_key is not demonstrated here. SeeProtecting data with Cloud KMS keysfor more information.

If the source or destination dataset is in a project other than your defaultproject, add the project ID to the dataset names in the following format:project_id:dataset.

(Optional) Supply the--location flag and set the value to yourlocation.

bq--location=locationcp\-a-f-n\project_id:dataset.source_table$source_partition\project_id:dataset.destination_table$destination_partition

Where:

• location is the name of your location. The--location flag isoptional. For example, if you are using BigQuery in theTokyo region, you can set the flag's value toasia-northeast1. You canset a default value for the location using the.bigqueryrc file.
• project_id is your project ID.
• dataset is the name of the source or destination dataset.
• source_table is the table you're copying.
• source_partition is the partition decorator of the sourcepartition.
• destination_table is the name of the table in the destinationdataset.
• destination_partition is the partition decorator of thedestination partition.

Examples:

bq cp -a 'mydataset.mytable$20180130' mydataset.mytable2

bq cp -a 'mydataset.mytable$20180130' mydataset2.mytable2

bq --location=US cp -f 'mydataset.mytable$20180130' mydataset2.mytable2

bq --location=US cp \-a \'mydataset.mytable$20180130' \myotherproject:mydataset2.mytable2

bq cp \-f \'mydataset.mytable$20180130' \'myotherproject:mydataset2.mytable2$20180130'

bq cp \'mydataset.mytable$20180130' \myotherproject:mydataset2.mytable2

To copy multiple partitions, specify them as a comma-separated list:

bqcp\'mydataset.mytable$20180130,mydataset.mytable$20180131'\myotherproject:mydataset.mytable2

API

Call thejobs.insertmethod, and configure acopy job. (Optional) Specify your region in thelocation property in thejobReference section of thejob resource.

Specify the following properties in your job configuration:

• Enter the source dataset, table, and partition in thesourceTablesproperty.
• Enter the destination dataset and table in thedestinationTableproperty.
• Use thewriteDisposition property to specify whether to append oroverwrite the destination table or partition.

To copy multiple partitions, enter the source partitions (including thedataset and table names) in thesourceTables property.

Console

Deleting partitions is not supported by the Google Cloud console.

SQL

If aqualifyingDELETE statementcovers all rows in a partition,BigQuery removes the entire partition. This removal is donewithout scanning bytes or consuming slots. The following example of aDELETE statement covers the entire partition of a filter on the_PARTITIONDATE pseudocolumn:

1. In the Google Cloud console, go to theBigQuery page.

   Go to BigQuery

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

   DELETEmydataset.mytableWHERE_PARTITIONDATEIN('2076-10-07','2076-03-06');

3. Clickplay_circleRun.

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

bq

Use thebq rm command with the--table flag (or-t shortcut) andspecify the partition decorator to delete a specific partition.

bqrm--tableproject_id:dataset.table$partition

Where:

• project_id is your project ID. If omitted, your default projectis used.
• dataset is the name of the dataset that contains the table.
• table is the name of the table.
• partition is the partition decorator of the partition you'redeleting.

Partition decorators have the following format, depending on the type ofpartitioning:

• Hourly partition:yyyymmddhh. Example:$2016030100.
• Daily partition:yyyymmdd. Example:$20160301.
• Monthly partition:yyyymm. Example:$201603.
• Yearly partition:yyyy. Example:$2016.
• Integer range partition: Start of the partition range. Example:$20.

The bq command-line tool prompts you to confirm the action. To skip the confirmation,use the--force flag (or-f shortcut).

bq rm --table 'mydataset.mytable$20160301'

bq rm --table 'mydataset.mytable$201603'

bq rm --table 'mydataset.mytable$20'

API

Call thetables.deletemethod and specify the table and partition decorator using thetableIdparameter.