寫入查詢結果

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

暫時性與永久性資料表

BigQuery 會將所有查詢結果寫入資料表,資料表可以是永久性或暫時性的:

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

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

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

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

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

所需權限

將查詢結果寫入永久性資料表的所需權限取決於資料的寫入配置。

將查詢結果寫入新資料表的權限

如要將查詢結果寫入新資料表,您必須擁有資料集層級的 WRITER 權限,或者取得具備 bigquery.tables.create 權限的專案層級身分與存取權管理角色。以下是擁有 bigquery.tables.create 權限的預先定義專案層級身分與存取權管理角色:

此外,因為 bigquery.user 角色具備 bigquery.datasets.create 權限,所以取得 bigquery.user 角色指派的使用者可以在那位使用者建立的任何資料集中建立資料表。當取得 bigquery.user 角色指派的使用者建立資料集時,該使用者即被授予該資料集的 OWNER 存取權。 資料集的 OWNER 存取權可讓使用者完全控管那個資料集以及其中的所有資料表。

如要進一步瞭解 BigQuery 中的身分與存取權管理角色和權限,請參閱存取權控管。如要進一步瞭解資料集層級角色,請參閱資料集的原始角色

覆寫或附加資料的權限

如要使用查詢結果覆寫現有資料表或將資料附加到現有資料表,您必須擁有資料集層級的 WRITER 權限,或是取得 bigquery.tables.updateData 權限的專案層級身分與存取權管理角色。以下是擁有 bigquery.tables.updateData 權限的預先定義專案層級身分與存取權管理角色:

此外,因為 bigquery.user 角色擁有 bigquery.datasets.create 權限,所以接受 bigquery.user 角色指派的使用者可以在那位使用者在資料集中建立的任何資料表中覆寫或附加資料。當接受 bigquery.user 角色指派的使用者建立資料集時,那位使用者即被授予資料集的 OWNER 存取權。資料集的 OWNER 存取權可讓使用者完全控管該資料集,以及其中的所有資料表。

如要深入瞭解 BigQuery 中的 IAM 角色和權限,請參閱存取權控管一文。如要進一步瞭解資料集層級角色,請參閱資料集的原始角色

寫入查詢結果

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

主控台

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

  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 View] (儲存視圖) 按鈕,將臨時資料表複製到永久資料表。

傳統版 UI

選項 1:使用 DDL 陳述式

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

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

選項 2:使用傳統網頁版 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 --location=US 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 --location=US 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 的目標資料表。資料集位於 myotherproject 而非預設專案中。指令使用 --append 標記將查詢結果附加至目標資料表。

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

API

如要將查詢結果儲存到永久資料表,請呼叫 jobs.insert 方法、設定 query 工作,然後加入 configuration.query.destinationTable 屬性的值。如要控管現有目標資料表的寫入配置,請設定 configuration.query.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 寫入大型結果集:

主控台

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

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

  3. 在「Query editor」(查詢編輯器) 文字區域中輸入有效的 BigQuery 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」欄位,輸入資料表名稱。

    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] (執行查詢),這會建立一個查詢工作,將大型結果集寫入您指定的資料表。

指令列

搭配 --destination_table 標記使用 --allow_large_results 標記,建立目標資料表來保存大型結果集。因為 --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_NAME] --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 --location=US 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 --location=US 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 --location=US 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 工作,然後將 configuration.query.allowLargeResults 屬性設為 true。使用 configuration.query.destinationTable 屬性指定目標資料表。如要控管現有目標資料表的寫入配置,請設定 configuration.query.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

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

如要啟用大型結果,請在 QueryJobConfiguration 中將「allow large results」(允許大型結果) 設為 true,並將「destination table」(目標資料表) 設為需要的 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 檔案:

主控台

  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] (儲存結果) 並選取您要用來儲存結果的格式/位置。

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

傳統版 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 雲端硬碟:

主控台

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

    前往 BigQuery 網頁版 UI

  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] (我的雲端硬碟)

將查詢結果儲存到資料表

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

主控台

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

  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 View] (儲存視圖) 按鈕,將臨時資料表複製到永久資料表。

傳統版 UI

選項 1:使用 DDL 陳述式

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

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

選項 2:使用傳統網頁版 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 --location=US 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 --location=US 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 的目標資料表。資料集位於 myotherproject 而非預設專案中。指令使用 --append 標記將查詢結果附加至目標資料表。

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

API

如要將查詢結果儲存到永久資料表,請呼叫 jobs.insert 方法、設定 query 工作,然後加入 configuration.query.destinationTable 屬性的值。如要控管現有目標資料表的寫入配置,請設定 configuration.query.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 試算表:

主控台

  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 格式的今天日期。

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

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

這個網頁