Truncate a table in GBQ

google-bigquery
Teja picture Teja · May 13, 2015 · Viewed 31.9k times · Source

I am trying to truncate an existing table in GBQ but the below command fails when I run it. Is there any specific command or syntax to do that. I looked into GBQ documentation but no luck.

TRUNCATE TABLE [dw_test.test];

Answer

Xiong Chiamiov picture Xiong Chiamiov · May 3, 2018

While BigQuery didn't used to support anything other than SELECTs, it now does as long as you uncheck "Use Legacy SQL" in the query options. There is no truncation, but you can delete:

DELETE from my_table WHERE 1=1

Note that BigQuery requires the use of WHERE in the DELETE, so if you want to delete everything you need to use a statement that will always be true.

Related questions

Setting GOOGLE_APPLICATION_CREDENTIALS for BigQuery Python CLI

I'm trying to connect to Google BigQuery through the BigQuery API, using Python. I'm following this page here: https://cloud.google.com/bigquery/bigquery-api-quickstart My code is as follows: import os import argparse from apiclient.discovery import build from apiclient.…

Read More
Google BigQuery Delete Rows?

Anyone know of any plans to add support for delete parts of data from a table in Google Bigquery? The issue we have right now is we are using it for analytics of data points we collect over time. We …

Read More
Can we cast the type in BigQuery?

Following my query : SELECT SQRT((D_o_latitude - T_s_lat)^2+(D_o_longitude - T_s_long)^2)/0.00001 FROM [datasetName.tableName] I am getting the error as Error: Argument type mismatch in function SUBTRACT:'D_o_latitude' is type …

Read More