I didn't find a RENAME option to alter table name. I have a case that I must rename a table, and the only way is to select with result to new table. this query cost money, and taking long time for no reason.
It is especially painful when I need to rename a nested table, so I must export, need to even work on the result set in order to import it back.
Any way that I am missing? anything coming soon?
There is no rename option, but there is a copy operation, which uses a fast snapshot process. This doesn't incur any additional charges other than the additional cost of storage (of course you can delete the original table so you only get charged for the storage once).
You can do this in the BigQuery by clicking on the table name and the dropdown arrow next to the table name, then selecting 'copy table'. Alternately you can use the bq cp command in the bq command-line tool.
In May 2021 Google updated its documentation to add a command to rename BigQuery tables. Here is a link to the documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_table_rename_to_statement
Example command:
ALTER TABLE mydataset.mytable RENAME TO mynewtable
Use except function to rename your dataset
bq query --destination_table practiceDataset.test_sales_orc --replace --use_legacy_sql=false 'SELECT
* EXCEPT(
_col16 ),
_col16 as ingestion_tsp
FROM (
SELECT
* EXCEPT(
_col0,
_col1,
_col2,
_col3,
_col4,
_col5,
_col6,
_col7,
_col8,
_col9,
_col10,
_col11,
_col12,
_col13,
_col14,
_col15),
_col0 as order_number,
_col1 as order_line_number,
_col2 as version,
_col3 as order_date,
_col4 as extamt,
_col5 as quantity,
_col6 as vlucost,
_col7 as model_number,
_col8 as credit_amount,
_col9 as written_sales,
_col10 as product_category,
_col11 as product_category_code,
_col12 as product_class,
_col13 as description_model,
_col14 as location_code,
_col15 as location_description
FROM practiceDataset.sales_data
)'
