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 want to run the queries over the last X days of data, however after the last X days of data we no longer need to store the data in BigQuery.
The only way currently we can think of to delete the data would be to delete the entire table of data, then recreate it and load it with X number of days of data. This would though require us to store our data in daily CSV files too, which isn't optimal.
Any recommendations on how to handle this issue or if there is a delete rows query coming in the near future?
Answer
2016 update: BigQuery can delete and update rows now -- Fh
https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax
Thanks for describing your use case. BigQuery is append-only by design. We currently don't support deleting single rows or a batch of rows from an existing dataset.
Currently, to implement a "rotating" log system you must either: 1. Create a new table each day (and delete older tables if that is necessary) 2. Append your data to a table and query by time/date
I would actually recommend creating a new table for each day. Since BigQuery charges by amount of data queried over, this would be most economical for you, rather than having to query over entire massive datasets every time.
By the way - how are you currently collecting your data?