寫入查詢結果

這份文件說明如何寫入或儲存查詢結果。

暫時性與永久性資料表

BigQuery 會將所有查詢結果儲存至永久性或暫時性資料表:

  • 暫時性資料表是儲存於特殊資料集裡的隨機命名資料表。暫時性資料表可用來快取查詢結果。暫時性資料表的效期約為 24 小時。暫時性資料表無法共用,也無法透過任何一種標準清單或其他資料表操縱方法來顯示。您不需為儲存暫時性資料表付費。

  • 永久性資料表可以是您有權存取的任何資料集中之新資料表或現有資料表。如果您將查詢結果寫入新資料表,則必須支付資料的儲存費用。當您將查詢結果寫入永久性資料表時,所查詢的資料表必須與包含目標資料表的資料集位於相同位置。

必要權限

如要將查詢結果寫入資料表,您至少必須具備下列權限:

  • 建立新資料表的 bigquery.tables.create 權限
  • 將資料寫入新資料表、覆寫資料表或將資料附加至資料表的 bigquery.tables.updateData 權限
  • 執行查詢工作的 bigquery.jobs.create 權限

您可能還需要其他權限 (例如 bigquery.tables.getData),才能存取您要查詢的資料。

以下是具有 bigquery.tables.createbigquery.tables.updateData 權限的預先定義 Cloud IAM 角色:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

以下是具有 bigquery.jobs.create 權限的預先定義 Cloud IAM 角色:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

此外,具備 bigquery.datasets.create 權限的使用者在建立資料集時,會獲得 bigquery.dataOwner 存取權。 bigquery.dataOwner 存取權可讓使用者在資料集裡建立及更新資料表。

如要進一步瞭解 BigQuery 中的 Cloud IAM 角色和權限,請參閱存取權控管一文。

將查詢結果寫入永久性資料表

當您將查詢結果寫入永久性資料表時,可以建立新資料表、將結果附加到現有資料表,或覆寫現有資料表。您可以將查詢結果寫入永久性資料表:

  • 使用 GCP 主控台或傳統 BigQuery 網頁版 UI
  • 使用指令列工具的 bq query 指令
  • 呼叫 jobs.insert API 方法並設定 query 工作
  • 使用用戶端程式庫

寫入查詢結果

將查詢結果寫入永久性資料表:

Console

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

  2. 在導覽面板的「Resources」(資源) 區段,展開您的專案並選取資料集。

  3. 如果查詢編輯器處於隱藏狀態,請按一下視窗右上方的 [Show editor] (顯示編輯器)

  4. 在「Query editor」(查詢編輯器) 文字區域中輸入有效的 SQL 查詢。

  5. 按一下編輯器下方的 [More] (更多),然後選取 [Query settings] (查詢設定)

    查詢設定

  6. 勾選 [Set a destination table for query results] (為查詢結果設定目標資料表) 方塊。

    設定目的地

  7. 在「Destination」(目的地) 區段,根據您要建立資料表的位置選取適當的 [Project name] (專案名稱) 與 [Dataset name] (資料集名稱),並選擇 [Table name] (資料表名稱)

  8. 在「Destination table write preference」(目標資料表寫入偏好設定) 區段,選擇下列其中一項:

    • Write if empty (空白時寫入) — 僅在資料表空白時將查詢結果寫入資料表。
    • Append to table (附加到資料表中) — 將查詢結果附加至現有資料表。
    • Overwrite table (覆寫資料表) — 使用查詢結果覆寫名稱相同的現有資料表。
  9. (選用) 在「Processing location」(處理位置),按一下 [Auto-select] (自動選取),然後選擇您的位置

  10. 按一下 [Run query] (執行查詢),這會建立一個查詢工作,並將查詢結果寫入您指定的資料表。

或者,如果您在執行查詢前忘記指定目標資料表,可以按一下編輯器下方的 [Save Results] (儲存結果) 按鈕,將快取結果資料表複製至永久性資料表。

DDL

資料定義語言 (DDL) 陳述式可讓您使用標準 SQL 查詢語法建立及修改資料表。

詳情請參閱 CREATE TABLE 陳述式頁面與 CREATE TABLE 範例:從現有資料表建立新資料表

傳統版 UI

  1. 使用傳統 BigQuery 網頁版 UI
    前往傳統 BigQuery 網頁版 UI

  2. 按一下 [Compose query] (撰寫查詢) 按鈕。

  3. 在「New Query」(新查詢) 文字區域中輸入有效的 SQL 查詢。

  4. 按一下 [Show Options] (顯示選項)

  5. 在「Destination Table」(目標資料表) 區段,按一下 [Select Table] (選取資料表)

  6. 在「Select Destination Table」(選取目標資料表) 對話方塊中:

    1. 在「Project」(專案) 部分,選擇要儲存資料集和資料表的專案。

    2. 在「Dataset」(資料集) 部分,選擇要儲存資料表的資料集。

    3. 在「Table ID」(資料表 ID) 欄位中,輸入資料表名稱。目的地資料集裡的資料表名稱不得重複。資料表名稱不得超過 1,024 個字元,並且只能包含 a-z、A-Z、0-9 或 _ (底線字元)。

    4. 按一下 [OK] (確定)

  7. 在「Destination Table」(目標資料表) 區段中的「Write Preference」(寫入偏好設定) 部分,選擇下列其中一項:

    • Write if empty (空白時寫入) — 僅在資料表空白時將查詢結果寫入資料表。
    • Append to table (附加到資料表中) — 將查詢結果附加至現有資料表。
    • Overwrite table (覆寫資料表) — 使用查詢結果覆寫名稱相同的現有資料表。
  8. (選用) 在「Processing Location」(處理位置),按一下 [Unspecified] (未指定),然後選擇資料的位置

  9. 按一下 [Run query] (執行查詢),這會建立一個查詢工作,並將查詢結果寫入您指定的資料表。

或者,如果您在執行查詢前忘記指定目標資料表,也可以按一下結果視窗中的 [Save as Table] (另存為資料表) 按鈕,將暫時性資料表複製至永久性資料表。

CLI

輸入 bq query 指令並指定 --destination_table 標記,根據查詢結果建立永久性資料表。指定 use_legacy_sql=false 標記以使用標準 SQL 語法。如要將查詢結果寫入不在預設專案內的資料表,請使用下列格式將專案 ID 新增至資料集名稱:project_id:dataset

(選用) 提供 --location 標記,並將值設為您的位置

如要控管現有目標資料表的寫入配置,請指定下列其中一種選用標記:

  • --append_table — 如果目標資料表已存在,查詢結果會附加至該資料表。
  • --replace — 如果目標資料表已存在,查詢結果會覆寫該資料表。
bq --location=location query \
--destination_table project_id:dataset.table \
--use_legacy_sql=false 'query'

其中:

  • 「location」是用於處理查詢的位置名稱,--location 是選用標記。舉例來說,假設您是在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案來設定位置的預設值。
  • 「project_id」是您的專案 ID。
  • 「dataset」是您要寫入查詢結果之資料表的資料集名稱。
  • 「table」是您要寫入查詢結果的資料表名稱。
  • 「query」是採用標準 SQL 語法的查詢。

如未指定任何寫入配置標記,預設動作是僅在資料表為空白時將結果寫入資料表。如已存在非空白的資料表,系統會傳回下列錯誤:BigQuery error in query operation: Error processing job project_id:bqjob_123abc456789_00000e1234f_1': Already Exists: Table project_id:dataset.table

範例:

輸入下列指令,將查詢結果寫入 mydataset 中名為 mytable 的目標資料表。該資料集位於預設專案中。由於您未在指令中指定任何寫入配置標記,因此資料表必須為新建或空白。否則,系統會傳回 Already exists 錯誤。查詢會從美國人名資料公開資料集裡擷取資料。

bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=false \
'SELECT
  name,
  number
FROM
  `bigquery-public-data`.usa_names.usa_1910_current
WHERE
  gender = "M"
ORDER BY
  number DESC'

輸入下列指令,使用查詢結果覆寫 mydataset 中名為 mytable 的目標資料表。該資料集位於預設專案中。指令使用 --replace 標記覆寫目標資料表。

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

輸入下列指令,將查詢結果附加至 mydataset 中名為 mytable 的目標資料表。資料集位於 my-other-project 而非預設專案中。指令使用 --append 標記將查詢結果附加至目標資料表。

bq query \
--append \
--use_legacy_sql=false \
--destination_table my-other-project:mydataset.mytable \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

各範例的輸出內容如下:為了方便閱讀,部分輸出內容會被截斷。

Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE
+---------+--------+
|  name   | number |
+---------+--------+
| Robert  |  10021 |
| John    |   9636 |
| Robert  |   9297 |
| ...              |
+---------+--------+

API

如要將查詢結果儲存至永久性資料表,請呼叫 jobs.insert 方法,設定 query 工作,然後加入 destinationTable 屬性的值。如要控管現有目標資料表的寫入配置,請設定 writeDisposition 屬性。

如要控管查詢工作的處理位置,請在工作資源jobReference 區段中指定 location 屬性。

Go

在嘗試此範例之前,請至 BigQuery 快速入門導覽課程:使用用戶端程式庫,按照 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")

q := client.Query("SELECT 17 as my_col")
q.Location = "US" // Location must match the dataset(s) referenced in query.
q.QueryConfig.Dst = client.Dataset(destDatasetID).Table(destTableID)
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

在嘗試此範例之前,請前往 BigQuery 快速入門導覽課程:使用用戶端程式庫頁面,按照 Java 設定說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件

如要將查詢結果儲存至永久性資料表,請在 QueryJobConfiguration 中將目標資料表設為需要的 TableId

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// String destinationDataset = 'my_destination_dataset';
// String destinationTable = 'my_destination_table';
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // Note that setUseLegacySql is set to false by default
    QueryJobConfiguration.newBuilder(query)
        // Save the results of the query to a permanent table.
        .setDestinationTable(TableId.of(destinationDataset, destinationTable))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

在嘗試此範例之前,請前往 BigQuery 快速入門導覽課程:使用用戶端程式庫頁面,按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件

如要將查詢結果儲存至永久性資料表,請建立 QueryJobConfig,並將目的地設為需要的 TableReference。接著,將工作設定傳送至查詢方法

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'your_dataset_id'

job_config = bigquery.QueryJobConfig()
# Set the destination table
table_ref = client.dataset(dataset_id).table('your_table_id')
job_config.destination = table_ref
sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""

# Start the query, passing in the extra configuration.
query_job = client.query(
    sql,
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location='US',
    job_config=job_config)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print('Query results loaded to table {}'.format(table_ref.path))

寫入大型查詢結果

通常,查詢都有回應大小上限。 如果您打算執行可能會傳回較大結果的查詢,您可以執行下列動作:

  • 在標準 SQL 中,指定查詢結果的目標資料表。
  • 在舊版 SQL 中,指定目標資料表並設定 allowLargeResults 選項。

如果您指定的目標資料表會寫入大型查詢結果,則必須支付資料的儲存費用。

限制

在舊版 SQL 中,寫入大型結果受到下列限制:

  • 您必須指定目標資料表。
  • 您可以指定頂層 ORDER BYTOPLIMIT 子句。這麼做會抵銷使用 allowLargeResults 的好處,因為再也無法平行計算查詢輸出。
  • 窗型函式只有在合併使用 PARTITION BY 子句時才能傳回大型查詢結果。

使用舊版 SQL 寫入大型結果

如要使用舊版 SQL 寫入大型結果集:

Console

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。
    前往 GCP Console

  2. 按一下 [Compose new query] (撰寫新查詢)

  3. 在「Query editor」(查詢編輯器) 文字區域中輸入有效的 SQL 查詢。請使用 #legacySQL 前置字串,或確認您已在查詢設定中勾選 [Use Legacy SQL] (使用舊版 SQL)

  4. 按一下 [More] (更多),然後選取 [Query settings] (查詢設定)

    查詢設定

  5. 在「Destination」(目的地) 部分,勾選 [Set a destination table for query results] (為查詢結果設定目標資料表)

    設定目的地

  6. 在「Project name」(專案名稱) 部分,選擇要在其中建立目標資料表的專案。

  7. 在「Dataset name」(資料集名稱) 部分,選擇要儲存資料表的資料集。

  8. 在「Table name」(資料表名稱) 欄位,輸入資料表名稱。

  9. 如果您要將大型結果集寫入現有資料表,可以使用「Destination table write preference」(目標資料表寫入偏好設定) 選項來控管目標資料表的寫入配置:

    • Write if empty (空白時寫入):僅在資料表空白時將查詢結果寫入資料表。
    • Append to table (附加到資料表中):將查詢結果附加至現有資料表。
    • Overwrite table (覆寫資料表):使用查詢結果覆寫名稱相同的現有資料表。

    覆寫資料表圓形按鈕

  10. 在「Results Size」(結果大小) 部分,勾選 [Allow Large Results (no size limit)] (允許大型結果 (無大小上限))

    查詢結果大小

  11. (選用) 在「Processing location」(處理位置),按一下 [Auto-select] (自動選取),然後選擇資料的位置

    查詢處理位置

  12. 按一下 [Save] (儲存) 以更新查詢設定。

  13. 按一下 [Run] (執行)。這會建立一個查詢工作,並將大型結果集寫入您指定的資料表。

傳統版 UI

  1. 前往 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose query] (撰寫查詢) 按鈕。

  3. 在「New Query」(新查詢) 文字區域中輸入有效的 BigQuery SQL 查詢。請使用 #legacySQL 前置字串,或確認您已在查詢選項中勾選 [Use Legacy SQL] (使用舊版 SQL)

  4. 按一下 [Show Options] (顯示選項)

  5. 在「Destination Table」(目標資料表) 部分,按一下 [Select Table] (選取資料表)

  6. 在「Select Destination Table」(選取目標資料表) 對話方塊中:

    1. 在「Project」(專案) 部分,選擇要在其中建立目標資料表的專案。

    2. 在「Dataset」(資料集) 部分,選擇要儲存資料表的資料集。

    3. 在「Table ID」(資料表 ID) 欄位中,輸入資料表名稱。

    4. 按一下 [OK] (確定)

  7. 如果您要將大型結果集寫入現有資料表,可以使用「Write Preference」(寫入偏好設定) 選項來控管目標資料表的寫入配置:

    • Write if empty (空白時寫入) — 僅在資料表空白時將查詢結果寫入資料表。
    • Append to table (附加到資料表中) — 將查詢結果附加至現有資料表。
    • Overwrite table (覆寫資料表) — 使用查詢結果覆寫名稱相同的現有資料表。
  8. 在「Results Size」(結果大小) 部分,勾選 [Allow Large Results] (允許大型結果)

    [Allow large results] 選項

  9. (選用) 在「Processing Location」(處理位置),按一下 [Unspecified] (未指定),然後選擇資料的位置

  10. 按一下 [Run query] (執行查詢),這會建立一個查詢工作,並將大型結果集寫入您指定的資料表。

CLI

使用 --allow_large_results 標記搭配 --destination_table 標記,建立目標資料表來保存大型結果集。因為 -- allow_large_results 選項只適用於舊版 SQL,您也必須指定 --use_legacy_sql=true 標記。如要將查詢結果寫入不在預設專案內的資料表,請使用下列格式將專案 ID 新增至資料集名稱:[PROJECT_ID]:[DATASET]。提供 -- location 標記,並將值設為您的位置

如要控管現有目標資料表的寫入配置,請指定下列其中一種選用標記:

  • --append_table — 如果目標資料表已存在,查詢結果會附加至該資料表。
  • --replace — 如果目標資料表已存在,查詢結果會覆寫該資料表。
bq --location=location query \
--destination_table project_id:dataset.table \
--use_legacy_sql=true \
--allow_large_results "query"

其中:

  • 「location」是用於處理查詢的位置名稱,--location 是選用標記。舉例來說,假設您是在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案來設定位置的預設值。
  • 「project_id」是您的專案 ID。
  • 「dataset」是您要寫入查詢結果之資料表的資料集名稱。
  • 「table」是您要寫入查詢結果的資料表名稱。
  • 「query」是採用舊版 SQL 語法中的查詢。

範例:

輸入下列指令,將大型查詢結果寫入 mydataset 中名為 mytable 的目標資料表。該資料集位於預設專案中。由於您未在指令中指定任何寫入配置標記,因此資料表必須為新建或空白。否則,系統會傳回 Already exists 錯誤。查詢會從美國人名資料公開資料集裡擷取資料。此查詢僅做示範之用。傳回的結果集不會超出回應大小上限。

bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
  name,
  number
FROM
  [bigquery-public-data:usa_names.usa_1910_current]
WHERE
  gender = 'M'
ORDER BY
  number DESC"

輸入下列指令,使用大型查詢結果覆寫 mydataset 中名為 mytable 的目標資料表。資料集位於 myotherproject 而非預設專案中。指令使用 --replace 標記覆寫目標資料表。

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
  name,
  number
FROM
  [bigquery-public-data:usa_names.usa_1910_current]
WHERE
  gender = 'M'
ORDER BY
  number DESC"

輸入下列指令,將大型查詢結果附加至 mydataset 中名為 mytable 的目標資料表。資料集位於 myotherproject 而非預設專案中。指令使用 --append 標記將查詢結果附加至目標資料表。

bq query \
--destination_table myotherproject:mydataset.mytable \
--append \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
  name,
  number
FROM
  [bigquery-public-data:usa_names.usa_1910_current]
WHERE
  gender = 'M'
ORDER BY
  number DESC"

API

如要將大型結果寫入目標資料表,請呼叫 jobs.insert 方法,設定 query 工作,並將 allowLargeResults 屬性設為 true。使用 destinationTable 屬性指定目標資料表。如要控管現有目標資料表的寫入配置,請設定 writeDisposition 屬性。

工作資源jobReference 區段中,於 location 屬性指定您的位置。

Go

在嘗試此範例之前,請至 BigQuery 快速入門導覽課程:使用用戶端程式庫,按照 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件

q := client.Query(
	"SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;")
q.UseLegacySQL = true
q.AllowLargeResults = true
q.QueryConfig.Dst = client.Dataset(dstDatasetID).Table(dstTableID)
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

如要啟用大型結果,請在 QueryJobConfiguration 中將允許大型結果設為 true,並將目標資料表設為需要的 TableId

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// String destinationDataset = 'my_destination_dataset';
// String destinationTable = 'my_destination_table';
String query = "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // To use legacy SQL syntax, set useLegacySql to true.
    QueryJobConfiguration.newBuilder(query)
        .setUseLegacySql(true)
        // Save the results of the query to a permanent table.
        .setDestinationTable(TableId.of(destinationDataset, destinationTable))
        // Allow results larger than the maximum response size.
        // If true, a destination table must be set.
        .setAllowLargeResults(true)
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

在嘗試此範例之前,請至 BigQuery 快速入門導覽課程:使用用戶端程式庫,按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'your_dataset_id'

job_config = bigquery.QueryJobConfig()
# Set use_legacy_sql to True to use legacy SQL syntax.
job_config.use_legacy_sql = True
# Set the destination table
table_ref = client.dataset(dataset_id).table("your_table_id")
job_config.destination = table_ref
job_config.allow_large_results = True
sql = """
    SELECT corpus
    FROM [bigquery-public-data:samples.shakespeare]
    GROUP BY corpus;
"""
# Start the query, passing in the extra configuration.
query_job = client.query(
    sql,
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location="US",
    job_config=job_config,
)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print("Query results loaded to table {}".format(table_ref.path))

下載並儲存查詢結果

執行 SQL 查詢之後,您可以下載結果到本機電腦的檔案、儲存結果到 Google 雲端硬碟或 Google 試算表,或者將結果儲存到 BigQuery 中的永久性資料表。

限制

下載並儲存查詢結果受到下列限制:

  • 您可以將查詢結果只下載到本機檔案或傳統 BigQuery 網頁版 UI 的 Google 試算表。如要下載結果到 Google 雲端硬碟,請使用 GCP 主控台。
  • 如要使用傳統 BigQuery 網頁版 UI 查詢結果,結果集裡包含的資料列不得超過 16,000 個,大小必須為 10 MB 或以下。如果您的結果大於 10 MB 或超過 16,000 個資料列,您可以將結果儲存到資料表。
  • 您只能以 CSV 或以換行符號分隔的 JSON 格式將查詢結果下載到本機檔案。
  • 您下載的查詢結果不得包含 CSV 格式的巢狀與重複資料。
  • 您無法將包含巢狀與重複資料的查詢結果儲存到 Google 試算表。
  • 在使用傳統 BigQuery 網頁版 UI 將查詢結果儲存到 Google 試算表時,結果集裡包含的資料列不得超過 16,000 個,大小必須為 10 MB 或以下。如果您的結果大於 10 MB 或超過 16,000 個資料列,您可以改為將結果儲存到資料表。
  • 指令列工具或 API 都不支援將結果儲存到本機檔案、Google 試算表或 Google 雲端硬碟。
  • 如要使用 GCP 主控台將查詢結果儲存到 Google 雲端硬碟,結果集必須為 1GB 或以下。如果您的結果大於 1GB,您可以改為將結果儲存到資料表。
  • 您只能以 CSV 或以換行符號分隔的 JSON 格式將查詢結果儲存到 Google 雲端硬碟。

下載查詢結果到本機檔案

指令列工具或 API 都不支援下載查詢結果到本機檔案。

如要使用網頁版 UI 將查詢結果下載為 CSV 或以換行符號分隔的 JSON 檔案:

Console

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。
    前往 GCP Console

  2. 按一下 [Compose new query] (撰寫新查詢)

  3. 在「Query editor」(查詢編輯器) 文字區域中輸入有效的 SQL 查詢。

  4. (選用) 如要變更處理位置,請按一下 [More] (更多) 並選取 [Query settings] (查詢設定)。在「Processing location」(處理位置) 下,按一下 [Auto-select] (自動選取),然後選擇資料的位置

  5. 按一下 [Run] (執行)。

  6. 傳回結果時,按一下 [Save results] (儲存結果) 並選取您要用來儲存結果的格式/位置。

    檔案會下載至瀏覽器的預設下載位置。

傳統版 UI

  1. 前往 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose Query] (撰寫查詢) 按鈕。

  3. 在「New Query」(新查詢) 文字區域中輸入有效的 SQL 查詢。

  4. 按一下 [Show Options] (顯示選項)

  5. (選用) 在「Processing Location」(處理位置),按一下 [Unspecified] (未指定),然後選擇資料的位置

  6. 按一下 [Run Query] (執行查詢)。

  7. 傳回結果時,按一下查詢結果上方的 [Download as CSV] (以 CSV 格式下載) 或 [Download as JSON] (以 JSON 格式下載) 按鈕。

    [download] (下載) 與 [save] (儲存) 按鈕的螢幕截圖

    檔案會下載至瀏覽器的預設下載位置。

將查詢結果儲存到 Google 雲端硬碟

指令列工具、API 或傳統 BigQuery 網頁版 UI 都不支援將查詢結果儲存到 Google 雲端硬碟。

如何使用 GCP 主控台將查詢結果儲存到 Google 雲端硬碟:

Console

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

  2. 在「Query editor」(查詢編輯器) 文字區域中輸入有效的 SQL 查詢。

  3. 按一下 [Run] (執行)。

  4. 傳回結果時,按一下 [Save Results] (儲存結果)

    [save results] (儲存結果) 按鈕的螢幕擷圖

  5. 選取 [CSV (Google Drive)] (CSV (Google 雲端硬碟) 或 [JSON (Google Drive)] (JSON (Google 雲端硬碟)。當您儲存結果到 Google 雲端硬碟時,無法選擇位置。結果一律會儲存到根目錄「我的雲端硬碟」的位置。

  6. 系統可能需要幾分鐘的時間,才能將結果儲存至 Google 雲端硬碟。儲存結果時,您會收到包含下列檔案名稱的彈出式訊息:bq-results-[TIMESTAMP]-[RANDOM_CHARACTERS].[CSV or JSON]

    [save results] (儲存結果) 按鈕的螢幕擷圖

  7. 在彈出式訊息中,按一下 [Open] (開啟) 來開啟檔案,或者移到 Google 雲端硬碟,並按一下 [My Drive] (我的雲端硬碟)

將查詢結果儲存到資料表

如何將查詢結果儲存為資料表:

Console

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

  2. 在導覽面板的「Resources」(資源) 區段,展開您的專案並選取資料集。

  3. 如果查詢編輯器處於隱藏狀態,請按一下視窗右上方的 [Show editor] (顯示編輯器)

  4. 在「Query editor」(查詢編輯器) 文字區域中輸入有效的 SQL 查詢。

  5. 按一下編輯器下方的 [More] (更多),然後選取 [Query settings] (查詢設定)

    查詢設定

  6. 勾選 [Set a destination table for query results] (為查詢結果設定目標資料表) 方塊。

    設定目的地

  7. 在「Destination」(目的地) 區段,根據您要建立資料表的位置選取適當的 [Project name] (專案名稱) 與 [Dataset name] (資料集名稱),並選擇 [Table name] (資料表名稱)

  8. 在「Destination table write preference」(目標資料表寫入偏好設定) 區段,選擇下列其中一項:

    • 「Write if empty」(空白時寫入) — 僅在資料表空白時將查詢結果寫入資料表。
    • 「Append to table」(附加到資料表中) — 將查詢結果附加至現有資料表。
    • 「Overwrite table」(覆寫資料表) — 使用查詢結果覆寫名稱相同的現有資料表。
  9. (選用) 在「Processing location」(處理位置),按一下 [Auto-select] (自動選取),然後選擇您的位置

  10. 按一下 [Run query] (執行查詢),這會建立一個查詢工作,並將查詢結果寫入您指定的資料表。

或者,如果您在執行查詢前忘記指定目標資料表,可以按一下編輯器下方的 [Save Results] (儲存結果) 按鈕,將快取結果資料表複製至永久性資料表。

DDL

資料定義語言 (DDL) 陳述式可讓您使用標準 SQL 查詢語法建立及修改資料表。

詳情請參閱 CREATE TABLE 陳述式頁面與 CREATE TABLE 範例:從現有資料表建立新資料表

傳統版 UI

  1. 使用傳統 BigQuery 網頁版 UI
    前往傳統 BigQuery 網頁版 UI

  2. 按一下 [Compose query] (撰寫查詢) 按鈕。

  3. 在「New Query」(新查詢) 文字區域中輸入有效的 SQL 查詢。

  4. 按一下 [Show Options] (顯示選項)

  5. 在「Destination Table」(目標資料表) 區段,按一下 [Select Table] (選取資料表)

  6. 在「Select Destination Table」(選取目標資料表) 對話方塊中:

    1. 在「Project」(專案) 部分,選擇要儲存資料集和資料表的專案。

    2. 在「Dataset」(資料集) 部分,選擇要儲存資料表的資料集。

    3. 在「Table ID」(資料表 ID) 欄位中,輸入資料表名稱。目的地資料集裡的資料表名稱不得重複。資料表名稱不得超過 1,024 個字元,並且只能包含 a-z、A-Z、0-9 或 _ (底線字元)。

    4. 按一下 [OK] (確定)

  7. 在「Destination Table」(目標資料表) 區段中的「Write Preference」(寫入偏好設定) 部分,選擇下列其中一項:

    • 「Write if empty」(空白時寫入) — 僅在資料表空白時將查詢結果寫入資料表。
    • 「Append to table」(附加到資料表中) — 將查詢結果附加至現有資料表。
    • 「Overwrite table」(覆寫資料表) — 使用查詢結果覆寫名稱相同的現有資料表。
  8. (選用) 在「Processing Location」(處理位置),按一下 [Unspecified] (未指定),然後選擇資料的位置

  9. 按一下 [Run query] (執行查詢),這會建立一個查詢工作,並將查詢結果寫入您指定的資料表。

或者,如果您在執行查詢前忘記指定目標資料表,也可以按一下結果視窗中的 [Save as Table] (另存為資料表) 按鈕,將暫時性資料表複製至永久性資料表。

CLI

輸入 bq query 指令並指定 --destination_table 標記,根據查詢結果建立永久性資料表。指定 use_legacy_sql=false 標記以使用標準 SQL 語法。如要將查詢結果寫入不在預設專案內的資料表,請使用下列格式將專案 ID 新增至資料集名稱:project_id:dataset

(選用) 提供 --location 標記,並將值設為您的位置

如要控管現有目標資料表的寫入配置,請指定下列其中一種選用標記:

  • --append_table — 如果目標資料表已存在,查詢結果會附加至該資料表。
  • --replace — 如果目標資料表已存在,查詢結果會覆寫該資料表。
bq --location=location query \
--destination_table project_id:dataset.table \
--use_legacy_sql=false 'query'

其中:

  • 「location」是用於處理查詢的位置名稱,--location 是選用標記。舉例來說,假設您是在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案來設定位置的預設值。
  • 「project_id」是您的專案 ID。
  • 「dataset」是您要寫入查詢結果之資料表的資料集名稱。
  • 「table」是您要寫入查詢結果的資料表名稱。
  • 「query」是採用標準 SQL 語法的查詢。

如未指定任何寫入配置標記,預設動作是僅在資料表為空白時將結果寫入資料表。如已存在非空白的資料表,系統會傳回下列錯誤:BigQuery error in query operation: Error processing job project_id:bqjob_123abc456789_00000e1234f_1': Already Exists: Table project_id:dataset.table

範例:

輸入下列指令,將查詢結果寫入 mydataset 中名為 mytable 的目標資料表。該資料集位於預設專案中。由於您未在指令中指定任何寫入配置標記,因此資料表必須為新建或空白。否則,系統會傳回 Already exists 錯誤。查詢會從美國人名資料公開資料集裡擷取資料。

bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=false \
'SELECT
  name,
  number
FROM
  `bigquery-public-data`.usa_names.usa_1910_current
WHERE
  gender = "M"
ORDER BY
  number DESC'

輸入下列指令,使用查詢結果覆寫 mydataset 中名為 mytable 的目標資料表。該資料集位於預設專案中。指令使用 --replace 標記覆寫目標資料表。

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

輸入下列指令,將查詢結果附加至 mydataset 中名為 mytable 的目標資料表。資料集位於 my-other-project 而非預設專案中。指令使用 --append 標記將查詢結果附加至目標資料表。

bq query \
--append \
--use_legacy_sql=false \
--destination_table my-other-project:mydataset.mytable \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

各範例的輸出內容如下:為了方便閱讀,部分輸出內容會被截斷。

Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE
+---------+--------+
|  name   | number |
+---------+--------+
| Robert  |  10021 |
| John    |   9636 |
| Robert  |   9297 |
| ...              |
+---------+--------+

API

如要將查詢結果儲存至永久性資料表,請呼叫 jobs.insert 方法,設定 query 工作,然後加入 destinationTable 屬性的值。如要控管現有目標資料表的寫入配置,請設定 writeDisposition 屬性。

如要控管查詢工作的處理位置,請在工作資源jobReference 區段中指定 location 屬性。

Go

在嘗試此範例之前,請至 BigQuery 快速入門導覽課程:使用用戶端程式庫,按照 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")

q := client.Query("SELECT 17 as my_col")
q.Location = "US" // Location must match the dataset(s) referenced in query.
q.QueryConfig.Dst = client.Dataset(destDatasetID).Table(destTableID)
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

在嘗試此範例之前,請前往 BigQuery 快速入門導覽課程:使用用戶端程式庫頁面,按照 Java 設定說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件

如要將查詢結果儲存至永久性資料表,請在 QueryJobConfiguration 中將目標資料表設為需要的 TableId

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// String destinationDataset = 'my_destination_dataset';
// String destinationTable = 'my_destination_table';
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // Note that setUseLegacySql is set to false by default
    QueryJobConfiguration.newBuilder(query)
        // Save the results of the query to a permanent table.
        .setDestinationTable(TableId.of(destinationDataset, destinationTable))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

在嘗試此範例之前,請前往 BigQuery 快速入門導覽課程:使用用戶端程式庫頁面,按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件

如要將查詢結果儲存至永久性資料表,請建立 QueryJobConfig,並將目的地設為需要的 TableReference。接著,將工作設定傳送至查詢方法

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'your_dataset_id'

job_config = bigquery.QueryJobConfig()
# Set the destination table
table_ref = client.dataset(dataset_id).table('your_table_id')
job_config.destination = table_ref
sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""

# Start the query, passing in the extra configuration.
query_job = client.query(
    sql,
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location='US',
    job_config=job_config)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print('Query results loaded to table {}'.format(table_ref.path))

將查詢結果儲存到 Google 試算表

指令列工具或 API 都不支援將查詢結果儲存到 Google 試算表。

如要使用網頁版 UI 將查詢結果儲存到 Google 試算表:

Console

  1. 在 GCP 主控台中開啟 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose new query] (撰寫新查詢)

  3. 在「Query editor」(查詢編輯器) 文字區域中輸入有效的 SQL 查詢。

  4. (選用) 如要變更處理位置,請按一下 [More] (更多) 並選取 [Query settings] (查詢設定)。在「Processing location」(處理位置) 下,按一下 [Auto-select] (自動選取),然後選擇資料的位置

  5. 按一下 [Run] (執行)。

  6. 傳回結果時,按一下 [Save results] (儲存結果) 並選取 [Google Sheets] (Google 試算表)

  7. 如有必要,請依照提示登入 Google 帳戶,並按一下 [Allow] (允許),授予 BigQuery 將資料寫入 Google 雲端硬碟 MY Drive 資料夾的權限。

    依照提示執行後,您應該會收到主旨為「BigQuery Client Tools connected to your Google Account」的電子郵件。電子郵件中的資訊包含您具有的權限以及移除這些權限的相關步驟。

  8. 儲存結果後,類似下列的訊息會顯示在主控台 BigQuery 網頁版 UI 的查詢結果下方:Saved to Sheets as "results-20190225-103531. Open。按一下訊息中的連結,即可查看 Google 試算表中的結果,您也可以前往 My Drive 資料夾並手動開啟檔案。

    將查詢結果儲存至 Google 試算表時,檔案名稱以 results-[DATE] 開頭,其中 [DATE] 是今天的日期 (以 YYYYMMDD 格式呈現)。

傳統版 UI

  1. 前往 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose Query] (撰寫查詢) 按鈕。

  3. 在「New Query」(新查詢) 文字區域中輸入有效的 SQL 查詢。

  4. 按一下 [Show Options] (顯示選項)

  5. (選用) 在「Processing Location」(處理位置),按一下 [Unspecified] (未指定),然後選擇資料的位置

  6. 按一下 [Run Query] (執行查詢)。

  7. 傳回結果時,按一下查詢結果上方的 [Save to Google Sheets] (儲存至 Google 試算表) 按鈕。

    [download] (下載) 與 [save] (儲存) 按鈕的螢幕截圖

  8. 如有必要,請依照提示登入 Google 帳戶,並按一下 [Allow] (允許),授予 BigQuery 將資料寫入 Google 雲端硬碟 MY Drive 資料夾的權限。

    依照提示執行後,您應該會收到主旨為「BigQuery Client Tools connected to your Google Account」的電子郵件。電子郵件中的資訊包含您具有的權限以及移除這些權限的相關步驟。

  9. 儲存結果後,類似下列的訊息會顯示在 BigQuery 傳統網頁版 UI 的查詢結果上方:Results saved to Google Sheets. Click to view。按一下訊息中的連結,即可查看 Google 試算表中的結果,您也可以前往 MY Drive 資料夾並手動開啟檔案。

    將查詢結果儲存至 Google 試算表時,檔案名稱以 results-[DATE] 開頭,其中 [DATE] 是今天的日期 (以 YYYYMMDD 格式呈現)。

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
需要協助嗎?請前往我們的支援網頁