建立邏輯檢視表

邏輯檢視表可讓您將 SQL 查詢儲存為虛擬資料表，以便重複使用查詢邏輯，並簡化資料存取作業。本文說明如何建立邏輯檢視表，包括檢視表命名規則和限制。

資料檢視限制

BigQuery 資料檢視有下列幾項限制：

  • 檢視表為唯讀狀態。例如，您無法執行插入、更新或刪除資料的查詢。
  • 如果檢視表參照遠端位置的資料表，您必須先啟用全域查詢，才能建立檢視表。
  • 檢視表內的參照必須符合資料集。預設資料集不會影響檢視表主體。
  • 您無法使用 TableDataList JSON API 方法從檢視表擷取資料。詳情請參閱 Tabledata：list 的相關說明。
  • 使用檢視表時，您無法混合使用 GoogleSQL 和舊版 SQL 查詢。GoogleSQL 查詢無法參照使用舊版 SQL 語法定義的檢視表。
  • 您無法在檢視表中參照查詢參數。
  • 建立檢視表時，系統會將基礎資料表的結構定義和檢視表一併儲存。如果在建立檢視表後新增、刪除或修改資料欄，檢視表不會自動更新，且回報的結構定義會維持不準確狀態，直到檢視表 SQL 定義變更或重新建立檢視表為止。雖然回報的結構定義可能不準確，但所有提交的查詢都會產生準確的結果。
  • 您無法自動將舊版 SQL 檢視表更新為 GoogleSQL 語法。如要修改用於定義檢視表的查詢，可以使用下列指令：
  • 在定義檢視表的 SQL 查詢中，不得包含 temporary user-defined function 或臨時資料表。
  • 您無法在萬用字元資料表查詢中參照檢視表。
  • 邏輯檢視表無法繼承或明確定義參數化資料類型 (例如 STRING(n))，因為參數化資料類型僅適用於基礎資料表資料欄和指令碼變數。

如要瞭解檢視表的配額和限制，請參閱「檢視表限制」。

事前準備

授予 Identity and Access Management (IAM) 角色，讓使用者具備必要權限，可執行本文件中的各項工作。

所需權限

在 BigQuery 中，檢視表會視為資料表資源，因此建立檢視表所需的權限與建立資料表相同。您也必須具備權限，才能查詢檢視表 SQL 查詢參照的任何資料表。

  • 如要建立資料集，您必須具備專案的 bigquery.datasets.create IAM 權限。
  • 如要建立檢視表，您必須具備資料集的 bigquery.tables.create IAM 權限。roles/bigquery.dataEditor 預先定義的 IAM 角色包含建立檢視表所需的權限。
  • 如要建立檢視表來查詢您無權存取的資料表，您必須具備檢視表查詢資料表的 bigquery.tables.getData 權限。

如要進一步瞭解 BigQuery 的 IAM 角色和權限，請參閱「預先定義的角色和權限」。

檢視表命名

在 BigQuery 中建立檢視表時，檢視表名稱在同一資料集中不得重複。以下是檢視表的命名規則：

  • 包含總計最多 1,024 個 UTF-8 位元組的字元。
  • 包含 L (字母)、M (標記)、N (數字)、Pc (連接符號，包括底線)、Pd (破折號)、Zs (空格) 類別的 Unicode 字元。詳情請參閱「一般類別」。

以下是有效的檢視表名稱範例：view 01、ग्राहक、00_お客様、étudiant-01。

注意事項：

  • 根據預設，資料表名稱會區分大小寫。mytable 和 MyTable 可以共存於同一個資料集，除非它們是不區分大小寫的資料集的一部分。
  • 部分檢視表名稱和檢視表名稱前置字元為保留字，如果收到錯誤訊息，指出檢視表名稱或前置字串已保留，請選取其他名稱，然後再試一次。

  • 如果您在序列中加入多個點運算子 (.)，系統會隱含移除重複的運算子。

    例如： project_name....dataset_name..table_name

    變成： project_name.dataset_name.table_name

建立檢視表

您可以編寫 SQL 查詢，定義檢視表可存取的資料，藉此建立檢視表。SQL 查詢必須包含 SELECT 陳述式。檢視表查詢不允許其他陳述式類型 (例如 DML 陳述式) 和多陳述式查詢，但 @@session_id 系統變數除外。

如何建立資料檢視：

控制台

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往「BigQuery」

  2. 按一下「 SQL 查詢」。

  3. 在查詢編輯器中，輸入有效的 SQL 查詢。

    或者，您也可以開啟已儲存的查詢。

  4. 依序點選 「儲存」 >「儲存 view」。

    儲存檢視畫面。

  5. 在「Save view」(儲存檢視表) 對話方塊中：

    • 在「Project」(專案) 選單中，選取要儲存檢視表的專案。
    • 在「Dataset」(資料集) 選單中，選取資料集或建立新的資料集，用來儲存檢視表。已儲存檢視表的目的地資料集必須與來源位於相同區域。
    • 在「Table」(資料表) 欄位中，輸入檢視表名稱。
    • 按一下 [儲存]。

SQL

使用 CREATE VIEW 陳述式。下列範例會從美國姓名公開資料集建立名為 usa_male_names 的檢視表：

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往「BigQuery」

  2. 在查詢編輯器中輸入下列陳述式：

    CREATE VIEW mydataset.usa_male_names(name, number) AS (
  SELECT
    name,
    number
  FROM
    `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    gender = 'M'
  ORDER BY
    number DESC
);

  3. 按一下「 執行」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」。

bq

使用 bq mk 指令，並加上 --view 旗標。如果是 GoogleSQL 查詢，請新增 --use_legacy_sql 旗標，並將其設為 false。部分選用參數包括 --add_tags、--expiration、--description 和 --label。如需完整的參數清單，請參閱 bq mk 指令參考資料。

如果查詢參照儲存在 Cloud Storage 或本機檔案中的外部 user-defined function (UDF) 資源，請使用 --view_udf_resource 旗標指定這些資源。本文不示範 --view_udf_resource 旗標。如要進一步瞭解如何使用 UDF，請參閱「UDF」。

如果要在預設專案以外的專案中建立檢視表，請使用 --project_id 旗標來指定專案 ID。

bq mk \
--use_legacy_sql=false \
--view_udf_resource=PATH_TO_FILE \
--expiration=INTEGER \
--description="DESCRIPTION" \
--label=KEY_1:VALUE_1 \
--add_tags=KEY_2:VALUE_2[,...] \
--view='QUERY' \
--project_id=PROJECT_ID \
DATASET.VIEW

更改下列內容：

  • PATH_TO_FILE 是程式碼檔案的 URI 或本機檔案系統路徑，該檔案會立即載入並評估為檢視表使用的 UDF 資源。請重複該標記以指定多個檔案。
  • INTEGER 會設定檢視畫面的生命週期 (以秒為單位)。如果 INTEGER 為 0，則檢視表不會過期。如果不加入 --expiration 旗標，BigQuery 會使用資料集的預設資料表生命週期建立檢視表。
  • DESCRIPTION 是資料檢視的說明，會用引號括住。
  • KEY_1:VALUE_1 是代表標籤的鍵/值組合。重複使用 --label 旗標，即可指定多個標籤。
  • KEY_2:VALUE_2 是代表標記的鍵/值組合。在同一個旗標下新增多個標記，並在鍵/值組合之間加上半形逗號。
  • QUERY 是有效查詢。
  • PROJECT_ID 是您的專案 ID (如果未設定預設專案)。
  • DATASET 是專案中的資料集。
  • VIEW 是要建立的檢視表名稱。

範例：

輸入下列指令，在預設專案的 mydataset 中建立名為 myview 的檢視表。到期時間設為 3600 秒 (1 小時)、說明設為 This is my view，標籤則設為 organization:development。用於建立檢視表的查詢會從美國姓名資料公開資料集查詢資料。

bq mk \
--use_legacy_sql=false \
--expiration 3600 \
--description "This is my view" \
--label organization:development \
--view \
'SELECT
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = "M"
ORDER BY
  number DESC' \
mydataset.myview

輸入下列指令，在 myotherproject 中的 mydataset 建立名為 myview 的檢視表。說明設為 This is my view，標籤設為 organization:development，檢視表的到期時間則設為資料集的預設資料表到期時間。用於建立檢視表的查詢會從美國姓名資料公開資料集查詢資料。

bq mk \
--use_legacy_sql=false \
--description "This is my view" \
--label organization:development \
--project_id myotherproject \
--view \
'SELECT
  name,
  number
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = "M"
ORDER BY
  number DESC' \
mydataset.myview

建立檢視表後，您可以更新檢視表的到期時間、說明和標籤。詳情請參閱更新檢視表的相關說明。

Terraform

使用 google_bigquery_table 資源。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

以下範例說明如何建立名為 myview 的檢視表：

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "myview"

  view {
    query          = "SELECT global_id, faa_identifier, name, latitude, longitude FROM `bigquery-public-data.faa.us_airports`"
    use_legacy_sql = false
  }

}

如要在 Google Cloud 專案中套用 Terraform 設定，請完成下列各節的步驟。

準備 Cloud Shell

  1. 啟動 Cloud Shell。

  2. 設定要套用 Terraform 設定的預設 Google Cloud 專案。

    每個專案只需執行這個指令一次，而且可以在任何目錄中執行。

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    如果您在 Terraform 設定檔中設定明確的值，環境變數就會遭到覆寫。

準備目錄

每個 Terraform 設定檔都必須有自己的目錄 (也稱為根模組)。

  1. 在 Cloud Shell 中建立目錄，並在該目錄中建立新檔案。檔案名稱必須有 .tf 副檔名，例如 main.tf。在本教學課程中，這個檔案稱為 main.tf。
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. 如果您正在進行教學課程，可以複製每個章節或步驟中的程式碼範例。

    將程式碼範例複製到新建立的 main.tf。

    您也可以從 GitHub 複製程式碼。如果 Terraform 程式碼片段是端對端解決方案的一部分，建議您採用這種做法。

  3. 請查看並修改範例參數，以便套用至您的環境。
  4. 儲存變更。
  5. 初始化 Terraform。每個目錄只需執行一次這項操作。 
    terraform init

    如要使用最新版 Google 供應商，請視需要加入 -upgrade 選項：

    terraform init -upgrade

套用變更

  1. 查看設定，確認 Terraform 要建立或更新的資源符合預期：
    terraform plan

    視需要修正設定。

  2. 執行下列指令，並在提示中輸入 yes，套用 Terraform 設定：
    terraform apply

    等待 Terraform 顯示「Apply complete!」訊息。

  3. 開啟 Google Cloud 專案即可查看結果。在 Google Cloud 控制台的使用者介面中，前往資源頁面，確認 Terraform 已建立或更新資源。

API

使用含有 view 屬性的資料表資源呼叫 tables.insert 方法。

Go

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Go 設定操作說明進行操作。詳情請參閱 BigQuery Go API 參考文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// createView demonstrates creation of a BigQuery logical view.
func createView(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	meta := &bigquery.TableMetadata{
		// This example shows how to create a view of the shakespeare sample dataset, which
		// provides word frequency information.  This view restricts the results to only contain
		// results for works that contain the "king" in the title, e.g. King Lear, King Henry V, etc.
		ViewQuery: "SELECT word, word_count, corpus, corpus_date FROM `bigquery-public-data.samples.shakespeare` WHERE corpus LIKE '%king%'",
	}
	if err := client.Dataset(datasetID).Table(tableID).Create(ctx, meta); err != nil {
		return err
	}
	return nil
}

Java

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.ViewDefinition;

// Sample to create a view
public class CreateView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String viewName = "MY_VIEW_NAME";
    String query =
        String.format(
            "SELECT TimestampField, StringField, BooleanField FROM %s.%s", datasetName, tableName);
    createView(datasetName, viewName, query);
  }

  public static void createView(String datasetName, String viewName, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, viewName);

      ViewDefinition viewDefinition =
          ViewDefinition.newBuilder(query).setUseLegacySql(false).build();

      bigquery.create(TableInfo.of(tableId, viewDefinition));
      System.out.println("View created successfully");
    } catch (BigQueryException e) {
      System.out.println("View was not created. \n" + e.toString());
    }
  }
}

Node.js

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Node.js 設定操作說明進行操作。詳情請參閱 BigQuery Node.js API 參考文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function createView() {
  // Creates a new view named "my_shared_view" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const myDatasetId = "my_table"
  // const myTableId = "my_table"
  // const projectId = "bigquery-public-data";
  // const sourceDatasetId = "usa_names"
  // const sourceTableId = "usa_1910_current";
  const myDataset = await bigquery.dataset(myDatasetId);

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    view: `SELECT name 
    FROM \`${projectId}.${sourceDatasetId}.${sourceTableId}\`
    LIMIT 10`,
  };

  // Create a new view in the dataset
  const [view] = await myDataset.createTable(myTableId, options);

  console.log(`View ${view.id} created.`);
}

Python

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Python 設定操作說明進行操作。詳情請參閱 BigQuery Python API 參考文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

from google.cloud import bigquery

client = bigquery.Client()

view_id = "my-project.my_dataset.my_view"
source_id = "my-project.my_dataset.my_table"
view = bigquery.Table(view_id)

# The source table in this example is created from a CSV file in Google
# Cloud Storage located at
# `gs://cloud-samples-data/bigquery/us-states/us-states.csv`. It contains
# 50 US states, while the view returns only those states with names
# starting with the letter 'W'.
view.view_query = f"SELECT name, post_abbr FROM `{source_id}` WHERE name LIKE 'W%'"

# Make an API request to create the view.
view = client.create_table(view)
print(f"Created {view.table_type}: {str(view.reference)}")

建立檢視表後，您可以像查詢資料表一樣查詢檢視表。

查看安全性

如要控管 BigQuery 檢視表的存取權，請參閱「授權檢視表」。

後續步驟