[GCP] DBA - Operation: Find stale/test/fake table and remove in production link with DataStream

operation-mysql-datastream-bigquery-get-table-from-information-schema.sql

/* 
  Context
  -------
  
  In some case, it's required to build `test_*` or `fake_*` table in production branch of MySQL for testing purpose.
  And our production branch link with streaming into BigQuery by using Datastream
  
  Overvview
  ---------
  
  +++++++++       ++++++++++++++         ++++++++++++
  + MySQL + ----> + Datastream + ------> + BigQuery +
  +++++++++       ++++++++++++++         ++++++++++++
  
  Process
  -------
  [1] Paused the streaming linked to source database schema.
  [2] Find the tables in source that match condition rules.
  [3] Delete tables both in source (MySQL) and destination (BigQuery).
  [4] Resume the streaming back.
  
  Required
  --------
  Role `ROLE_ADMIN` with in MySQL
  
  References
  ----------
  [1] Information Schema: https://dev.mysql.com/doc/mysql-infoschema-excerpt/8.2/en/information-schema-introduction.html
  [2] Role by MySQL: https://dev.mysql.com/doc/refman/8.2/en/privileges-provided.html
 */
 
 /* Find all table satisfied */
 SELECT * FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'production'
 AND (
  TABLE_NAME LIKE 'test_%'
  OR TABLE_NAME LIKE 'fake_%'
 );
 
 /* Drop table has been meet condition in source (MySQL) */
 DROP TABLE "production"."<table_name>";
 
 /* Drop table linked in destination (BigQuery) */
 DROP TABLE `linked_schema`.`<linked_table_name>`;