Is it still possible to remove the Partition filter=Required setting from a BigQuery table?

Question

I am trying to remove the requirement to filter over the partition column in a BigQuery partitioned table. The answer presented here does not seem to work: the query runs, shows the result

"This statement altered the table named project:dataset.table"

There is even a button to go to the table, but it still requires a partition filter.

EDIT: Including table configurations and the query used

Data location: US 
Table type: Partitioned 
Partitioned by: Day 
Partitioned on field: date 
Partition filter :Required
Clustered by: column_name1, column_name2, column_name3

I tried both the query exactly as provided in the answer:

ALTER TABLE IF EXISTS dataset.table
SET OPTIONS(
    require_partition_filter = false
)

and also

ALTER TABLE dataset.table
SET OPTIONS(
    require_partition_filter = false
)

For the second one, for example, i have the following information in my query history:

Query succeeded

Query completed in 0.159 sec
Job ID: job_id 
User: user
Location: United States (US)
Creation time: creation_time
Start time: start_time
End time: end_time
Duration: 0.2 sec 
Bytes processed: 0 B 
Bytes billed :0 B 
Job priority: INTERACTIVE 
Destination table: project:dataset.table
Write preference:
Use legacy SQL: false

"project", "dataset", "table", "job_id","user", "creation_time", "start_time", "end_time" have been replaced

Any help?

Can you share the table's configuration, the exact statement you used and the logs for it? — Francesco Galletta
Although, the ALTER command should work if you set the require_partition_filter to false. You can use the following command, as an alternative, ` bq update --norequire_partition_filter --time_partitioning_field=your_partition_field project:dataset.table`. — Alexandre Moraes

Alexandre Moraes Alexandre Moraes · Accepted Answer · 2020-04-20T08:39:58

In order to further contribute to the community, this answer is based in my last comment.

While the ALTER command should work, according to the documentation. There is another option to update a dataset, table, view or model, which is the bq update command.

There are various of flags within this command, including require_partition_filter and norequire_partition_filter, in order to set the partition filter to True or False, respectively. In your case you can use this command as follows:

bq update --norequire_partition_filter --time_partitioning_field=your_partition_field project:dataset.table

After submitting the command, you should receive the following message:

Table 'project_id_dataset_table_name' successfully updated.

Therefore, your table won't require a filter on the partitioning column any more.

Is it still possible to remove the Partition filter=Required setting from a BigQuery table?

1 Answers