bigquery backup all view definitions

Question

I am working with bigquery, and there have been a few hundred views created. Most of these are not used and should be deleted. However, there is a chance that some are used and I cannot just blindly delete all. Therefore, I need to backup all view definitions somehow before deleting them.

Does anyone know of a good way? I am not trying to save the data, just the view definition queries and their names.

Thanks for reading!

Answer 1

Part 1.

Issue the bq ls command. The --format flag can be used to control the output. If you are listing views in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET].

bq ls --format=pretty [PROJECT_ID]:[DATASET]

Where:

[PROJECT_ID] is your project ID.
[DATASET] is the name of the dataset.

When you run the command, the Type field displays either TABLE or VIEW. For example:

+-------------------------+-------+----------------------+-------------------+
|         tableId         | Type  |        Labels        | Time Partitioning |
+-------------------------+-------+----------------------+-------------------+
| mytable                 | TABLE | department:shipping  |                   |
| myview                  | VIEW  |                      |                   |
+-------------------------+-------+----------------------+-------------------+

Part 2.

Issue the bq show command. The --format flag can be used to control the output. If you are getting information about a view in a project other than your default project, add the project ID to the dataset in the following format: [PROJECT_ID]:[DATASET]. To write the view properties to a file, add > [PATH_TO_FILE] to the command.

bq show --format=prettyjson [PROJECT_ID]:[DATASET].[VIEW] > [PATH_TO_FILE]

Where:

[PROJECT_ID] is your project ID.
[DATASET] is the name of the dataset.
[VIEW] is the name of the view.
[PATH_TO_FILE] is the path to the output file on your local machine.

Examples:

Enter the following command to display information about myview in mydataset. mydataset is in your default project.

bq show --format=prettyjson mydataset.myview

Enter the following command to display information about myview in mydataset. mydataset is in myotherproject, not your default project. The view properties are written to a local file — /tmp/myview.json.

bq show --format=prettyjson myotherproject:mydataset.myview > /tmp/myview.json

Answer 2

Building off the existing answer, you can automate the backing up of all views by parsing the output of bq with jq:

#!/bin/bash

DATASETS=$(bq  ls --format=sparse | tail -n+3)
for d in $DATASETS; do
  TABLES=$(bq ls --format=prettyjson "$d" | jq '.[] | "\(.id), \(.type)"')
  IFS=$'\n'
  for table in $TABLES; do
    [[ ! "$table" == *VIEW* ]] && continue
    view=$(echo "$table" | sed -e 's/"//g' | cut -d , -f 1)
    query=$(bq show --format=prettyjson "$view" | jq -r '.view.query')
    echo -e "$query" > "$view.sql"
  done
done