bigquery备份所有的视图定义 [英] bigquery backup all view definitions

问题描述

我正在与bigquery合作，并且创建了几百个视图。大多数这些都没有使用，应该删除。但是，有一些可能会被使用，我不能一味地删除所有内容。因此，我需要在删除它们之前以某种方式备份所有视图定义。

有谁知道一个好方法？我不是试图保存数据，只是查看定义查询和他们的名字。

感谢您的阅读！

解决方案

第一部分

发行 bq ls 命令。 - 格式标志可用于控制输出。如果要在默认项目以外的项目中列出视图，请按以下格式将项目ID添加到数据集： [PROJECT_ID]：[DATASET] 。

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

其中：

[PROJECT_ID] 是您的项目ID。

[DATASET] 是数据集的名称。

运行该命令时，Type字段会显示 TABLE 或 VIEW 。例如：

  + ---------------------- --- + ------- + ---------------------- + --------------- ---- + 
 | tableId |类型|标签|时间分区| 
 + ------------------------- + ------- + ----------- ----------- + ------------------- + 
 | mytable | TABLE |部门：航运| | 
 | myview |查看| | | 
 + ------------------------- + ------- + ----------- ----------- + ------------------- + 
  

第2部分

发行 bq show 命令。 - 格式标志可用于控制输出。如果您正在获取项目中与默认项目不同的视图信息，请按以下格式将项目ID添加到数据集： [PROJECT_ID]：[DATASET] 。要将视图属性写入文件，请添加> [PATH_TO_FILE] 给命令。

  bq show --format = prettyjson [PROJECT_ID]：[ DATASET]。[VIEW]> [PATH_TO_FILE] 
  

其中：

[PROJECT_ID] 是您的项目标识符。

[DATASET] 是数据集的名称。

[VIEW] 是视图名称。
[PATH_TO_FILE]

示例：

输入以下内容命令在mydataset中显示关于myview的信息。 mydataset在您的默认项目中。

  bq show --format = prettyjson mydataset.myview 
  

输入以下命令以在 mydataset中显示有关 myview 的信息。 mydataset在myotherproject中，而不是你的默认项目。视图属性写入本地文件 - /tmp/myview.json 。

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

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!

解决方案

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

这篇关于bigquery备份所有的视图定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！