bq mk -f --view ( force replacing existing view does not work )

user_4031584 0
Viewed 1843

Seems the -f or --force=true flag does not work for views. As it still output the following error.

could not be created; a table with this name already exists.

Below is part of the command I use

bq mk --use_legacy_sql=false -f --description "View on reporting table ..." --view
google-bigquery
3 Answers

You can use a CREATE OR REPLACE VIEW statement, e.g.

bq query --use_legacy_sql=false "
  CREATE OR REPLACE VIEW dataset.view
  OPTIONS (description='View on reporting table ...') AS
  SELECT ...
"

See the DDL documentation for more reading.

user_62533470

Actually, as per some tests I have been running, this option does not do what the documentation suggests ([...] and overwrite the table without prompting) even for tables:

$ bq mk test_dataset.test
  Table 'PROJECT:test_dataset.test' successfully created.
$ bq mk test_dataset.test
  BigQuery error in mk operation: Table 'PROJECT:test_dataset.test' could not be created; a table with this name already exists.
$ bq mk -f test_dataset.test
  Table 'PROJECT:test_dataset.test' could not be created; a table with this name already exists.

Also, when looking at the description of the CLI tool, the explanation is not the same as in the documentation:

$ bq mk --help
  [...]
  -f,--[no]force: Ignore errors reporting that the object already exists.
    (default: 'false')

And in fact, if we look at the exit status of the command when adding or not the -f flag, we see a significant difference:

$ bq mk test_dataset.test
  BigQuery error in mk operation: Table 'PROJECT:test_dataset.test' could not be created; a table with this name already exists.
$ echo $?
  1
$ bq mk -f test_dataset.test
  Table 'PROJECT:test_dataset.test' could not be created; a table with this name already exists.
$ echo $?
  0

So I believe that in this case the functionality is correct (also, as you can see, when not adding the flag, the output includes an additional message BigQuery error in mk operation that is not present with the flag), and the documentation does not reflect the real behavior of the flag.

Therefore, I have already reported this internally in order to make the necessary change to the documentation.

Regarding the way to achieve the objective you attempted to have with this flag, you can use either of the workarounds that have been proposed in other answers and comments, which all seem good options.

Just to provide some final context to this post, the documentation has already been changed in order to reflect the real functionality of the -f flag:

--force or -f

When specified, if a resource already exists, the exit code is 0. The default value is false.

user_44824910

Based on the documentation it only forces table creations if already exist, do not talk about views 

--force or -f
When specified, ignore already exists errors and overwrite the table without prompting. The default value is false.
user_38294260
Related