Clever Geek Handbook

Truncating a table in GBQ

I am trying to truncate an existing table in GBQ but the command is below when I run it. Is there any specific command or syntax for this. I have looked at GBQ documentation but no luck.

TRUNCATE TABLE [dw_test.test];
+3


source to share


2 answers


BigQuery does not support TRUNCATE

as part of a query string. The only DDL / DML verb supported by BQ is SELECT

.

One of the options is to execute the job with a WRITE_TRUNCATE

record setting (the link is for the query job parameter, but it is supported on all jobs types with a target table). This will truncate all the data already in the table and replace it with the work results.



If you don't want to replace the content with other data or run a job, your best option is to drop and re-create the table using the same schema.

+4


source


While BigQuery didn't support anything other than SELECT

s, it now does as long as you don't uncheck "Use legacy SQL" in your query parameters. No truncation, but you can remove :

DELETE from my_table WHERE 1=1


Note that BigQuery requires use WHERE

in DELETE

, so if you want to remove everything you need, use the operator that will always be true.

0


source






More articles:


All Articles