How to delete cloudsql mysql records through bigquery. Federated query gives error “Failed to get query schema from MySQL server”

0
votes

Hello google cloud and bigquery experts :-)

I have a cloudsql mysql database and a BigQuery database in GCP I need to delete certain records in the cloudsql mysql database using mechanism akin to bigquery federated queries. for example see below (does not work offcourse :-( )

SELECT * FROM EXTERNAL_QUERY("uca-proddev-devops.us-east1.uca-useast-cloudsql-conn", " DELETE from inventory. cloud_object_events where cloud_object_id in (SELECT cloud_object_id from inventory.cloud_objects where date_created <  CURDATE() - INTERVAL 60 DAY);”);

This gives me an error

Invalid table-valued function EXTERNAL_QUERY
Failed to get query schema from MySQL server. Error: MysqlErrorCode(0):  at [3:15]

I checked the Cloud SQL federated queries documentation at (https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries). It clearly mentions that "external_database_query (string): is a read-only query in the external database's SQL dialect (MySQL or PostgreSQL).
So it seems like I cannot use federated queries mechanism directly to delete records from cloudsql using a criteria based on data in bigquery.

Exporting data from BigQuery and importing in cloudsql for forming a delete query is too rigorous. Is there a more convenient mechanism that would allow me to delete from cloudsql based on data in BigQuery.

Thanks for your help

1
google-cloud-platformgoogle-bigqueryfederated
Check this stackoverflow.com/a/36464054/11866104 "If you need more granular control, you can use the BigQuery API to perform the query, fetch the results, connect to the Cloud SQL instance and insert the data." – Mahboob
If you use "Select * from inventory" instead "DELETE from inventory" does this works for you? – Mahboob

1 Answers

2
votes

You can't delete/update data with Cloud SQL federated table. You can only read in Cloud SQL.

You need to implement your own process elsewhere (Cloud Functions, Cloud Run,...)