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!
BigQuery provides users the option of exporting single tables to flat files in Google Cloud Storage, effectively creating a point-in-time backup of your data that can be used to restore BigQuery tables to their prior state. You can choose to export to either CSV, newline-delimited JSON, or Avro files.
In BigQuery, materialized views are precomputed views that periodically cache the results of a query for increased performance and efficiency. BigQuery leverages precomputed results from materialized views and whenever possible reads only delta changes from the base tables to compute up-to-date results.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With