查詢分區資料表

查詢分區資料表

您可以藉由下列方式查詢分區資料表:

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

如需執行查詢的詳細資訊,請參閱執行互動式與批次查詢一文。

所需權限

您至少必須擁有 bigquery.tables.getData 權限,才能查詢資料表。

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

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

此外,具備 bigquery.datasets.create 權限的使用者在建立資料集時,會獲得 bigquery.dataOwner 存取權。 bigquery.dataOwner 存取權可讓使用者查詢資料集中的資料表和檢視表。

您還必須取得 bigquery.jobs.create 權限才可執行查詢工作。以下是具有 bigquery.jobs.create 權限的預先定義 Cloud IAM 角色:

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

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

擷取時間分區資料表虛擬資料欄

建立擷取時間分區資料表時,系統會在資料表中新增兩個虛擬資料欄:_PARTITIONTIME 虛擬資料欄和 _PARTITIONDATE 虛擬資料欄。_PARTITIONTIME 虛擬資料欄包含要載入資料表中的資料的日期時間戳記。_PARTITIONDATE 虛擬資料欄則包含日期。這兩個虛擬資料欄名稱均已獲得保留,也就是說,您無法在任何資料表中建立該名稱的資料欄。

_PARTITIONTIME_PARTITIONDATE 僅提供於擷取時間分區資料表。分區資料表並沒有虛擬資料欄。如需查詢分區資料表的相關資訊,請參閱查詢分區資料表

_PARTITIONTIME 虛擬資料欄

_PARTITIONTIME 虛擬資料欄包含依照 UTC 時間的時間戳記並顯示自 Unix 紀元開始算起的微秒數。例如,如果資料是在 2016 年 4 月 15 日附加至資料表,在該日期附加的所有資料列會在 _PARTITIONTIME 資料欄中出現 TIMESTAMP("2016-04-15") 的值。

如要查詢 _PARTITIONTIME 虛擬資料欄,您必須使用別名。例如,以下查詢會將別名 pt 指派至虛擬資料欄以選擇 _PARTITIONTIME

SELECT
  _PARTITIONTIME AS pt,
  column
FROM
  dataset.table

其中:

  • 「column」是要查詢的資料欄名稱。您可以透過逗號分隔清單的形式指定多個資料欄。
  • 「dataset」是包含分區資料表的資料集。
  • 「table」是分區資料表。

串流緩衝區中的資料在 _PARTITIONTIME 資料欄的值為 NULL

_PARTITIONDATE 虛擬資料欄

_PARTITIONDATE 虛擬資料欄包含的 UTC 日期與 _PARTITIONTIME 虛擬資料欄中的值相對應。

若要查詢 _PARTITIONDATE 虛擬資料欄,您必須使用別名。舉例來說,以下查詢會將別名 pd 指派至虛擬資料欄以選取 _PARTITIONDATE

SELECT
  _PARTITIONDATE AS pd,
  column
FROM
  dataset.table

其中:

  • 「column」是要查詢的資料欄名稱。您可以透過逗號分隔清單的形式指定多個資料欄。
  • 「dataset」是包含分區資料表的資料集。
  • 「table」是分區資料表。

串流緩衝區中的資料在 _PARTITIONDATE 資料欄的值為 NULL

使用虛擬資料欄查詢擷取時間分區資料表

查詢擷取時間分區資料表中的資料時,您可以指定 _PARTITIONTIME_PARTITIONDATE 虛擬資料欄中的值以參照特定分區。例如:

  • _PARTITIONTIME >= "2018-01-29 00:00:00" AND _PARTITIONTIME < "2018-01-30 00:00:00"
  • _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

  • _PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"
  • _PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'

使用虛擬資料欄限制查詢的分區

使用 _PARTITIONTIME_PARTITIONDATE 虛擬資料欄限制查詢時掃描的分區數。這也稱為縮減分區。分區縮減是 BigQuery 用來從輸入掃描中消除不必要分區的機制。計算查詢掃描的位元組時就不會納入縮減的分區,進而降低隨選分析成本。一般而言,如果能在查詢開始時評估篩選而無需進行任何子查詢評估或資料掃描,分區縮減將可降低查詢成本。

例如,以下查詢僅會掃描分區資料表中日期介於 2016 年 1 月 1 日至 2016 年 1 月 2 日的分區:

_PARTITIONTIME

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
  AND TIMESTAMP('2016-01-02')

_PARTITIONDATE

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONDATE BETWEEN '2016-01-01'
  AND '2016-01-02'

分區縮減範例

以下範例展示如何在子查詢中使用虛擬資料欄篩選限制掃描分區數。

_PARTITIONTIME

SELECT
  column1,
  column2
FROM (
  SELECT
    column1,
    column2
  FROM
    dataset.table
  WHERE
    _PARTITIONTIME = TIMESTAMP('2016-03-28')) t1
CROSS JOIN
  dataset.table t2
WHERE
  t1.column2 = "one"

_PARTITIONDATE

SELECT
  column1,
  column2
FROM (
  SELECT
    column1,
    column2
  FROM
    dataset.table
  WHERE
    _PARTITIONDATE = '2016-03-28') t1
CROSS JOIN
  dataset.table t2
WHERE
  t1.column2 = "one"

以下查詢會限制部分基於篩選器條件 _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01') 的分區。這不會基於涉及子查詢的條件來限制分區:

_PARTITIONTIME

SELECT
  column
FROM
  dataset.table2
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

_PARTITIONDATE

SELECT
  column
FROM
  dataset.table2
WHERE
  _PARTITIONDATE BETWEEN '2017-01-01' AND '2017-03-01'
  AND _PARTITIONDATE = (SELECT MAX(date) from dataset.table1)

掃描所有分區的虛擬資料欄查詢

以下範例雖使用虛擬資料欄但會掃描時間分區資料表中的所有分區。

在舊版 SQL 中,指定的 _PARTITIONTIME 篩選器僅在相當接近資料表名稱時才會運作。例如,即便存在 _PARTITIONTIME 篩選器,以下查詢仍會掃描 table1 中的所有分區:

#legacySQL
# Scans all partitions on t1
SELECT
  t1.field1,
  t2.field1
FROM
  mydataset.table2 t1
CROSS JOIN
  mydataset.table2 t2
WHERE
  t1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field2 = "one"

請勿在 _PARTITIONTIME 篩選器中包含任何其他資料欄。例如,以下查詢不會限制掃描的分區,因為 field2 是資料表中的資料欄,且 BigQuery 無法預先判斷要選取的分區。

# Scans all partitions of table2
SELECT
  field1
FROM
  mydataset.table2
WHERE
  _PARTITIONTIME + field2 = TIMESTAMP('2016-03-28');

包含子查詢的 _PARTITIONTIME 篩選器無法用於限制對分區資料表掃描的分區數。例如,以下查詢無法限制資料表 mydataset.table2 中的掃描分區數:

# Scans all partitions of table2
SELECT
  field1
FROM
  mydataset.table2
WHERE
  _PARTITIONTIME = (SELECT MAX(timestamp) FROM mydataset.table1)

透過虛擬資料欄提升效能

若要提升查詢成效,請在比較的左側單獨使用_PARTITIONTIME虛擬資料欄。舉例來說,雖然下列查詢處理的資料量相同,但第二個範例成效較佳。

範例 1:下列查詢由於結合了虛擬資料欄值和 WHERE 篩選器中的其他作業,因此速度較慢。

標準 SQL

#standardSQL
/* Can be slower */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")

舊版 SQL

#legacySQL
/* Can be slower */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  DATE_ADD(_PARTITIONTIME, 5, "DAY") > TIMESTAMP("2016-04-15")

範例 2:下列查詢因為在篩選器比較的左側單獨放置虛擬資料欄,所以成效較佳。

標準 SQL

#standardSQL
/* Often performs better */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY)

舊版 SQL

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  mydataset.table1
WHERE
  _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")

視資料表大小而定,第二個查詢在 > 比較運算子的左側單獨放置 _PARTITIONTIME,因此能提供比第一個查詢更好的成效。因為查詢處理的資料量相同,所以兩種情況下收取的位元組數量費用也相同。

使用萬用字元資料表查詢擷取時間分區資料表

除了使用虛擬資料欄限制查詢時掃描的分區數外,您也可以透過虛擬資料欄使用萬用字元資料表查詢特定範圍的分區資料表。如需有關使用萬用字元資料表搭配分區資料表的相關資訊,請參閱使用 _PARTITIONTIME 掃描特定範圍的分區資料表

使用時區查詢擷取時間分區資料表

_PARTITIONTIME 值以填入欄位時的 UTC 日期為準,這表示說,資料表是以 12:00 AM UTC 切割分區。如果您要根據 UTC 以外的時區查詢資料,您應選擇下列其中一個選項,然後才將資料載入至您的資料表。

如果分區資料表使用非 UTC 的自訂時區,有兩種方法可以查詢其中的資料。您可以另外建立時間戳記資料欄,或使用分區裝飾器將資料載入至特定資料表。

如果您使用時間戳記資料欄,您可以使用預設的 UTC 分區與帳戶在 SQL 查詢中處理時區差異。或者,如果您想要以 UTC 以外的時區分組的分區,請使用分區裝飾器將資料載入至不同時區的分區。

使用時間戳記資料欄查詢時區

若要使用時間戳記調整時區,請另外建立資料欄來儲存能讓您依照小時或分鐘處理列的時間戳記。

若要查詢 UTC 以外時區的資料,請同時使用 _PARTITIONTIME 虛擬資料欄與您自訂的時間戳記資料欄。透過 _PARTITIONTIME 將資料表掃描限制為相關分區,然後以自訂的時間戳記進一步限制符合您時區的結果。比方說,如要查詢分區資料表 (mydataset.partitioned_table) 中的資料,且資料新增到資料表的時間戳記欄位 [MY_TIMESTAMP_FIELD] 介於 2016-05-01 12:00:00 PST2016-05-05 14:00:00 PST 之間,請執行下列操作:

標準 SQL

#standardSQL
SELECT
  field1
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND TIMESTAMP_ADD([MY_TIMESTAMP_FIELD], INTERVAL 8 HOUR) BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

舊版 SQL

#legacySQL
SELECT
  field1
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND DATE_ADD([MY_TIMESTAMP_FIELD], 8, 'HOUR') BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

使用擷取時間分區資料表的虛擬資料欄建立視圖

若要將查詢讀取的資料量限制為特定分區,請建立包含 _PARTITIONTIME_PARTITIONDATE 虛擬資料欄篩選器的資料檢視。例如,下列查詢可用於建立只包含名稱是 mydataset.partitioned_table 的資料表中最近七天資料的視圖:

標準 SQL

#standardSQL
SELECT
  *
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR),DAY)
  AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(),DAY);

舊版 SQL

#legacySQL
SELECT
  *
FROM
  mydataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000))
  AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()));

如要瞭解如何建立視圖,請參閱建立視圖

擷取時間分區資料表的 _UNPARTITIONED_ 分區

__UNPARTITIONED__ 分區會暫時保管串流至分區資料表且位於串流緩衝區的資料。直接串流至分區資料表之特定分區的資料不會使用 __UNPARTITIONED__ 分區。相反地,資料會直接串流至分區中。請參閱串流至分區資料表以取得詳細資訊。

若要查詢 __UNPARTITIONED__ 分區中的資料,請使用 _PARTITIONTIME 虛擬資料欄搭配 NULL 值。例如:

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME IS NULL

其中:

  • 「column」是要查詢的資料欄名稱。您可以透過逗號分隔清單的形式指定多個資料欄。
  • 「dataset」是包含分區資料表的資料集。
  • 「table」是分區資料表。

查詢分區資料表

根據 TIMESTAMPDATE 資料欄分區的資料表並沒有虛擬資料欄。如要限制查詢分區資料表時掃描的分區數,請使用述詞篩選條件 (WHERE 子句)。

建立分區資料表時,可啟用 [Require partition filter] (需要分區篩選器) 選項,要求使用述詞篩選條件。套用此選項時,嘗試在未指定 WHERE 子句的情況下查詢分區資料表會產生下列錯誤: Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination

有關如何在建立分區資料表時新增 [Require partition filter] (需要分區篩選器) 選項的詳情,請參閱建立分區資料表一節。

如果您在建立分區資料表時未啟用 [Require partition filter] (需要分區篩選器) 選項,可以更新該資料表以新增選項。

縮減 (限制) 分區

將述詞篩選器以盡可能接近資料表識別碼的方式表示。 需要對查詢的多個階段進行評估以解析述詞 (例如內部查詢或子查詢) 的複合查詢不會從查詢中縮減分區。

例如,下列查詢會縮減分區:

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON t1.id_field = t2 field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

下列查詢不會縮減分區 (注意子查詢的使用):

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

在篩選器中隔離分區資料欄

在表示篩選器時隔離分區資料欄。需要多個欄位的資料進行運算的篩選器不會縮減分區。例如,如果查詢具有使用分區資料欄與第二個欄位的日期比較運算子,或包含部分欄位串連,將不會縮減分區。

例如,下列篩選器就不會縮減分區,因為它需要分區 ts 欄位與第二個欄位 ts2 進行運算:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

相關資源

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

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

這個網頁