CODEWITHSUNDEEP
google-bigquery
Sobur Ahmed
Sobur Ahmed

Reputation: 19

Big query update or delete issue

I can't do any update or delete operation by big query api via php. Have any techniques to complete this task.

Upvotes: 0

Views: 9092

Answers (4)

Ratnesh Sharma
Ratnesh Sharma

Reputation: 547

I have stopped setting write disposition and then could run dml statements on bigquery using python call

dont set following parameter

query_job_config.write_disposition

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

2016-08: BigQuery support for UPDATE and DELETE :)

Does BigQuery support UPDATE, DELETE, and INSERT (SQL DML) statements?

You can achieve UPDATEs and DELETEs with BigQuery - it's just not a native operation.

To delete rows from a table, re-materialize without the un-desired rows:

SELECT *
FROM [my.table1]
WHERE NOT id IN (19239,192392139,129391)

(set as options "allow large results" and "write results to my.table1")

To update rows, you can do something similar:

SELECT * FROM (
  SELECT * FROM [my.table1]
  WHERE NOT id IN (SELECT id FROM [my.updated_rows]
), (
  SELECT * FROM [my.updated_rows]
)

In other words: BigQuery is an analytical database, where DELETE and UPDATE are not optimized operations - but you pull them off with a single SELECT statement that overwrites the original table.

Upvotes: 3

Cihan Fethi Hızar
Cihan Fethi Hızar

Reputation: 155

As other friends explained, BigQuery has append-only design.

Instead of deleting a row, you should write a sql query which filters all un-wanted rows from your table; and you should create a brand new table from the results of this query. Then you can delete the old table.

For updating, you should append new updated rows to the table, and follow the instructions i wrote above for removing the old rows.

Upvotes: 1

Pentium10
Pentium10

Reputation: 207838

Don't forget that BigQuery is a WORM technology (append-only by design). It looks for me, that you are not aware of this thing, as there is no option like UPDATE or DELETE.

Upvotes: 2

Related Questions