How to clean up BigQuery views

0
votes

I have a BigQuery project with well over a hundred views (most of our queries are done as views) many of which are obsolete, and I am embarking on a project to clean these up and also reorganize/rename views for ease of use. There are a few questions I have about BigQuery features:

  1. Is it possible to do a mass export of the queries for all the BQ views in my project (just the queries, not the results)? This would be useful to have a backup (and also to more easily search them, which would help answer other questions)

  2. Is there a way to organize views into folders? I know that they can be organized into different datasets (which is what I'm doing now) but is there a way to create a multi-level "directory structure" to organize all the views?

  3. Is there a way to easily search for all the views whose queries use a particular other table (or view)?

  4. Is there a way to "rename a view" (or table), which may including moving it to a new dataset, such that all existing views whose queries include the renamed view (or table) are updated to use the new name?

1
google-bigquery

1 Answers

1
votes

  1. You could quite easily script something e.g. using gcloud. Off the top of my head: list all datasets -> for each dataset list all the tables/views -> check if it's a view -> grab the SQL/query behind the view -> append to file -> add to git repo

  2. No, only one level deep of datasets are supported in BigQuery.

  3. See #1, and then use grep.

  4. No, you'd need to update all the references to that view if you rename it or relocate it.