Bigtable 適用的 GoogleSQL 總覽

您可以使用 GoogleSQL 陳述式查詢 Bigtable 資料。GoogleSQL 是符合 ANSI 標準的結構化查詢語言 (SQL)，也適用於其他服務，例如 BigQuery 和 Spanner。 Google Cloud

本文概要介紹 Bigtable 適用的 GoogleSQL。並提供可搭配 Bigtable 使用的 SQL 查詢範例，以及說明這些查詢與 Bigtable 資料表結構定義的關係。閱讀本文之前，請先熟悉 Bigtable 儲存空間模型和結構定義設計概念。

您可以在 Google Cloud 控制台的 Bigtable Studio 中建立及執行查詢，也可以使用 Bigtable Java、Python 或 Go 用戶端程式庫，以程式輔助方式執行查詢。詳情請參閱「使用 Bigtable 用戶端程式庫搭配 SQL」。

叢集節點處理 SQL 查詢的方式與 NoSQL 資料要求相同。因此，建立要對 Bigtable 資料執行的 SQL 查詢時，請遵循相同的最佳做法，例如避免完整資料表掃描或複雜的篩選條件。詳情請參閱「讀取和效能」。

您無法將 Data Boost 與 Bigtable 的 GoogleSQL 搭配使用。

用途

GoogleSQL for Bigtable 非常適合開發低延遲應用程式。此外，在Google Cloud 控制台中執行 SQL 查詢，有助於快速取得資料表結構定義的視覺化表示法、驗證特定資料是否已寫入，或偵錯可能的資料問題。

目前 Bigtable 適用的 GoogleSQL 版本不支援部分常見的 SQL 建構函式，包括但不限於下列項目：

  • SELECT 以外的資料操縱語言 (DML) 陳述式，例如 INSERT、UPDATE 或 DELETE
  • 資料定義語言 (DDL) 陳述式，例如 CREATE、ALTER 或 DROP
  • 資料存取權控管陳述式
  • 子查詢、JOIN、UNION、UNNEST 和 CTEs 的查詢語法

如要進一步瞭解支援的函式、運算子、資料型別和查詢語法，請參閱 GoogleSQL for Bigtable 參考文件。

瀏覽次數

您可以使用 Bigtable 適用的 GoogleSQL 建立下列資源：

  • 具體化檢視區塊：持續執行的 SQL 查詢預先計算結果，包括匯總資料，並透過增量更新與來源資料表同步。
  • 邏輯檢視畫面：已儲存且已命名的查詢，可像資料表一樣查詢。

如要比較這些類型的檢視畫面和授權檢視畫面，請參閱「表格和檢視畫面」。

基本概念

本節將討論使用 GoogleSQL 查詢 Bigtable 資料時，需要注意的重要概念。

SQL 回應中的資料欄系列

在 Bigtable 中，資料表包含一或多個資料欄系列，用於將資料欄分組。使用 GoogleSQL 查詢 Bigtable 資料表時，資料表的結構定義包含下列項目：

  • 名為 _key 的特殊資料欄，對應於所查詢資料表中的資料列鍵
  • 資料表中每個 Bigtable 資料欄系列各有一個資料欄，其中包含該資料列的資料欄系列資料

地圖資料類型

Bigtable 適用的 GoogleSQL 包含 MAP<key, value> 資料型別，專門用於容納資料欄系列。

根據預設，對應資料欄中的每個資料列都包含鍵/值配對，其中鍵是所查詢資料表中的 Bigtable 資料欄限定符，值則是該資料欄的最新值。

以下是 SQL 查詢範例，會傳回資料表，其中包含資料列鍵值和來自名為 columnFamily 的對應項的限定符最新值。

  SELECT _key, columnFamily['qualifier'] FROM myTable

如果 Bigtable 結構定義涉及在資料欄中儲存多個儲存格或資料版本，您可以在 SQL 陳述式中加入時間篩選器，例如 with_history。

在此情況下，代表資料欄系列的對應會巢狀化，並以陣列形式傳回。在陣列中，每個值本身都是對應項，由時間戳記 (做為鍵) 和儲存格資料 (做為值) 組成。格式為 MAP<key, ARRAY<STRUCT<timestamp, value>>>。

以下範例會傳回單一資料列中「info」資料欄系列的所有儲存格。

  SELECT _key, info FROM users(with_history => TRUE) WHERE _key = 'user_123';

傳回的地圖如下所示。在查詢的資料表中，info 是資料欄系列，user_123 是資料列索引鍵，city 和 state 則是資料欄限定詞。陣列中的每個時間戳記值配對 (STRUCT) 代表該列中這些資料欄的儲存格，並依時間戳記降序排序。

/*----------+------------------------------------------------------------------+
 |   _key   |                              info                                |
 +----------+------------------------------------------------------------------+
 | user_123 | {"city":{<t5>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

稀疏資料表

Bigtable 的主要特色是彈性資料模型。在 Bigtable 資料表中，如果資料列未使用某個資料欄，系統就不會儲存該資料欄的資料。某列可能只有一欄，下一列則可能有 100 欄。相較之下，在關聯式資料庫資料表中，所有資料列都包含所有資料欄，而 NULL 值通常會儲存在沒有該資料欄資料的資料列資料欄中。

不過，使用 GoogleSQL 查詢 Bigtable 資料表時，未使用的資料欄會以空白對應表示，並以 NULL 值傳回。這些 NULL 值可用做查詢述詞。舉例來說，WHERE family['column1'] IS NOT NULL 這類述詞可用於只在資料列中使用 column1 時傳回資料列。

位元組

提供字串時，GoogleSQL 預設會從 STRING 值隱含轉換為 BYTES 值。舉例來說，這表示您可以提供字串 'qualifier'，而不是位元組序列 b'qualifier'。

由於 Bigtable 預設會將所有資料視為位元組，因此大多數 Bigtable 資料欄都不含類型資訊。不過，您可以使用 GoogleSQL 的 CAST 函式，在讀取時定義結構定義。如要進一步瞭解轉換，請參閱轉換函式。

時間篩選器

下表列出存取表格時間元素時可使用的引數。引數會按照篩選順序列出。舉例來說，with_history 會在 latest_n 之前套用。請務必提供有效時間戳記。

引數 說明
as_of 時間戳記。傳回時間戳記小於或等於所提供時間戳記的最新值。
with_history Boolean。控制是否要以純量形式傳回最新值，或以 STRUCT 形式傳回含時間戳記的值。
after_or_equal 時間戳記。時間戳記晚於輸入時間的值 (含輸入時間)。需要「with_history => TRUE」欄
before 時間戳記。時間戳記早於輸入的時間 (不含輸入時間) 的值。需要「with_history => TRUE」欄
latest_n 整數。每個資料欄要傳回的時間戳記值數量 限定符 (對應鍵)。不得小於 1。需要 with_history => TRUE。

如需更多範例，請參閱「進階查詢模式」。

基礎查詢

本節將說明基本的 Bigtable SQL 查詢，並提供範例，如需其他查詢範例，請參閱 GoogleSQL for Bigtable 查詢模式範例。

擷取最新版本

雖然 Bigtable 可讓您在每個資料欄中儲存多個版本的資料，但 GoogleSQL for Bigtable 預設會傳回每個資料列的最新版本資料 (即最近的儲存格)。

請看以下範例資料集，其中顯示 user1 在紐約州搬了兩次家，在布魯克林市內搬了一次家。在本例中，address 是資料欄系列，而資料欄限定詞是 street、city 和 state。資料欄中的儲存格以空白行分隔。

address
_key street city state
user1 2023/01/10-14:10:01.000:
'113 Xyz Street'

2021/12/20-09:44:31.010:
'76 Xyz Street'

2005/03/01-11:12:15.112:
'123 Abc Street'		 2021/12/20-09:44:31.010:
'Brooklyn'

2005/03/01-11:12:15.112:
'Queens'		 2005/03/01-11:12:15.112:
'NY'

如要擷取 user1 中每個資料欄的最新版本，可以使用類似下列的 SELECT 陳述式。

   SELECT street, city FROM myTable WHERE _key = 'user1'

回覆內容包含目前地址，也就是以 JSON 格式列印的最新街道、城市和州值 (在不同時間寫入)。回覆中不會顯示時間戳記。

_key address
user1 {street:'113 Xyz Street', city:'Brooklyn', state: :'NY'}

擷取所有版本

如要擷取舊版資料 (儲存格)，請使用 with_history 旗標。您也可以為資料欄和運算式建立別名，如以下範例所示。

  SELECT _key, columnFamily['qualifier'] AS col1
  FROM myTable(with_history => TRUE)

如要進一步瞭解導致資料列目前狀態的事件，您可以擷取完整記錄，取得每個值的時間戳記。舉例來說，如要瞭解 user1 何時搬到目前地址，以及搬遷前的地址，可以執行下列查詢：

  SELECT
    address['street'][0].value AS moved_to,
    address['street'][1].value AS moved_from,
    FORMAT_TIMESTAMP('%Y-%m-%d', address['street'][0].timestamp) AS moved_on,
  FROM myTable(with_history => TRUE)
  WHERE _key = 'user1'

在 SQL 查詢中使用 with_history 標記時，系統會以 MAP<key, ARRAY<STRUCT<timestamp, value>>> 形式傳回回應。陣列中的每個項目都是指定資料列、資料欄系列和資料欄的時間戳記值。時間戳記會依時間倒序排列，因此傳回的第一個項目一律是最新資料。

查詢回應如下。

moved_to moved_from moved_on
113 Xyz Street 76 Xyz Street 2023/01/10

您也可以使用陣列函式，擷取每個資料列中的版本數量，如下列查詢所示：

  SELECT _key, ARRAY_LENGTH(MAP_ENTRIES(address)) AS version_count
  FROM myTable(with_history => TRUE)

從指定時間擷取資料

使用 as_of 篩選器可擷取特定時間點的資料列狀態。舉例來說，如要查詢 user 在 2022 年 1 月 10 日下午 1:14 的地址，可以執行下列查詢。

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022-01-10T13:14:00.234Z'))
  WHERE _key = 'user1'

結果會顯示 2022 年 1 月 10 日下午 1:14 的最後已知地址，也就是 2021 年 12 月 20 日上午 9:44:31.010 更新中的街道和城市，以及 2005 年 3 月 1 日上午 11:12:15.112 更新中的州別。

address
{street:'76 Xyz Street', city:'Brooklyn', state: :'NY'}

使用 Unix 時間戳記也能達到相同效果。

  SELECT address
  FROM myTable(as_of => TIMESTAMP_FROM_UNIX_MILLIS(1641820440000))
  WHERE _key = 'user1'

請看以下資料集，其中顯示煙霧和一氧化碳警報器的開啟或關閉狀態。資料欄系列為 alarmType，資料欄限定詞則為 smoke 和 carbonMonoxide。每個資料欄中的儲存格都以空白行分隔。


alarmType
_key 煙霧 carbonMonoxide
building1#section1 2023/04/01-09:10:15.000:
'off'

2023/04/01-08:41:40.000:
'on'

2020/07/03-06:25:31.000:
'off'

2020/07/03-06:02:04.000:
'on'		 2023/04/01-09:22:08.000:
'off'

2023/04/01-08:53:12.000:
'on'
building1#section2 2021/03/11-07:15:04.000:
'off'

2021/03/11-07:00:25.000:
'on'

您可以使用下列查詢，找出 2023 年 4 月 1 日上午 9 點煙霧警報器開啟的 building1 區段，以及當時一氧化碳警報器的狀態。

  SELECT _key AS location, alarmType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023-04-01T09:00:00.000Z'))
  WHERE _key LIKE 'building1%' and alarmType['smoke'] = 'on'

結果如下：

location CO_sensor
building1#section1 'on'

查詢時間序列資料

Bigtable 的常見用途是儲存時間序列資料。請看以下範例資料集，其中顯示天氣感應器的溫度和濕度讀數。資料欄系列 ID 為 metrics，資料欄限定詞為 temperature 和 humidity。資料欄中的儲存格會以空白行分隔，每個儲存格代表附有時間戳記的感應器讀數。


指標
_key 溫度 濕度
sensorA#20230105 2023/01/05-02:00:00.000：
54

2023/01/05-01:00:00.000：
56

2023/01/05-00:00:00.000：
55		 2023/01/05-02:00:00.000：
0.89

2023/01/05-01:00:00.000：
0.9

2023/01/05-00:00:00.000：
0.91
sensorA#20230104 2023/01/04-23:00:00.000：
56

2023/01/04-22:00:00.000：
57		 2023/01/04-23:00:00.000：
0.9

2023/01/04-22:00:00.000：
0.91

您可以使用時間篩選條件 after、before 或 after_or_equal 擷取特定範圍的時間戳記值。以下範例使用 after：

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
         before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
   WHERE _key LIKE 'sensorA%'

查詢會傳回下列格式的資料：

temp_versioned
[{timestamp: '2023/01/05-01:00:00.000', value:56}
{timestamp: '2023/01/05-00:00:00.000', value: 55}]
[{timestamp: '2023/01/04-23:00:00.000', value:56}]

UNPACK 時間序列資料

分析時間序列資料時，通常會偏好使用表格格式的資料。Bigtable UNPACK 函式可派上用場。

UNPACK 是 Bigtable 資料表值函式 (TVF)，會傳回整個輸出資料表，而非單一純量值，並出現在 FROM 子句中，就像資料表子查詢一樣。UNPACK TVF 會將每個含有時間戳記的值擴展為多個資料列 (每個時間戳記對應一個資料列)，並將時間戳記移至 _timestamp 欄。

UNPACK 的輸入是子查詢，其中 with_history => true。

輸出內容是展開的表格，每列都有 _timestamp 資料欄。

輸入資料欄系列 MAP<key, ARRAY<STRUCT<timestamp, value>>> 會解壓縮為 MAP<key, value>，而資料欄限定詞 ARRAY<STRUCT<timestamp, value>>> 會解壓縮為 value。其他輸入資料欄類型則維持不變。必須在子查詢中選取資料欄，才能解壓縮及選取。不必選取新資料欄，系統就會解壓縮時間戳記。_timestamp

以「查詢時間序列資料」中的時間序列範例為基礎，並使用該節中的查詢做為輸入內容，您的 UNPACK 查詢格式如下：

  SELECT temp_versioned, _timestamp
  FROM
  UNPACK((
    SELECT metrics['temperature'] AS temperature_versioned
    FROM
    sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
          before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
    WHERE _key LIKE 'sensorA%'
  ));

查詢會傳回下列格式的資料：

temp_versioned

_timestamp

55

1672898400

55

1672894800

56

1672891200

查詢 JSON

您可以使用 JSON 函式，針對作業工作負載，操控以 Bigtable 值形式儲存的 JSON。

舉例來說，您可以使用下列查詢，從 session 資料欄系列的最新儲存格中，擷取 JSON 元素 abc 的值，以及資料列鍵。

  SELECT _key, JSON_VALUE(session['payload'],'$.abc') AS abc FROM analytics

逸出特殊字元和保留字

Bigtable 在命名資料表和資料欄時，提供高度彈性。 因此，在 SQL 查詢中，您的資料表名稱可能需要逸出，因為含有特殊字元或保留字。

舉例來說，由於資料表名稱中有句號，因此下列查詢不是有效的 SQL。

  -- ERROR: Table name format not supported

  SELECT * FROM my.table WHERE _key = 'r1'

不過，只要以倒引號 (`) 字元括住項目，即可解決這個問題。

  SELECT * FROM `my.table` WHERE _key = 'r1'

如果 SQL 保留的關鍵字用做 ID，同樣可以逸出。

  SELECT * FROM `select` WHERE _key = 'r1'

搭配 Bigtable 用戶端程式庫使用 SQL

適用於 Java、Python 和 Go 的 Bigtable 用戶端程式庫支援使用 executeQuery API，透過 SQL 查詢資料。下列範例說明如何發出查詢和存取資料：

Go

如要使用這項功能，您必須使用 cloud.google.com/go/bigtable 1.36.0 以上版本。如要進一步瞭解用法，請參閱 PrepareStatement、 Bind、 Execute 和 ResultRow 說明文件。

  import (
    "cloud.google.com/go/bigtable"
  )

  func query(client *bigtable.Client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    ps, err := client.PrepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      map[string]SQLType{
        "keyParam": BytesSQLType{},
      }
    )
    if err != nil {
      log.Fatalf("Failed to create PreparedStatement: %v", err)
    }

    // For each request, create a BoundStatement with your query parameters set.
    bs, err := ps.Bind(map[string]any{
      "keyParam": []byte("mykey")
    })
    if err != nil {
      log.Fatalf("Failed to bind parameters: %v", err)
    }

    err = bs.Execute(ctx, func(rr ResultRow) bool {
      var byteValue []byte
      err := rr.GetByName("bytesCol", &byteValue)
      if err != nil {
        log.Fatalf("Failed to access bytesCol: %v", err)
      }
      var stringValue string
      err = rr.GetByName("stringCol", &stringValue)
      if err != nil {
        log.Fatalf("Failed to access stringCol: %v", err)
      }
      // Note that column family maps have byte valued keys. Go maps don't support
      // byte[] keys, so the map will have Base64 encoded string keys.
      var cf3 map[string][]byte
      err = rr.GetByName("cf3", &cf3)
      if err != nil {
        log.Fatalf("Failed to access cf3: %v", err)
      }
      // Do something with the data
      // ...
      return true
    })
  }

Java

如要使用這項功能，請務必使用 java-bigtable 2.57.3 以上版本。如要進一步瞭解使用方式，請參閱 Javadoc 中的 prepareStatement、executeQuery、BoundStatement 和 ResultSet。

  static void query(BigtableDataClient client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    PreparedStatement preparedStatement = client.prepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      // For queries with parameters, set the parameter names and types here.
      Map.of("keyParam", SqlType.bytes())
    );

    // For each request, create a BoundStatement with your query parameters set.
    BoundStatement boundStatement = preparedStatement.bind()
      .setBytesParam("keyParam", ByteString.copyFromUtf8("mykey"))
      .build();

    try (ResultSet resultSet = client.executeQuery(boundStatement)) {
      while (resultSet.next()) {
        ByteString byteValue = resultSet.getBytes("bytesCol");
        String stringValue = resultSet.getString("stringCol");
        Map<ByteString, ByteString> cf3Value =
            resultSet.getMap("cf3", SqlType.mapOf(SqlType.bytes(), SqlType.bytes()));
        // Do something with the data.
      }
    }
  }

Python asyncio

如要使用這項功能，必須使用 python-bigtable 2.30.1 以上版本。

  from google.cloud.bigtable.data import BigtableDataClientAsync

  async def execute_query(project_id, instance_id, table_id):
      async with BigtableDataClientAsync(project=project_id) as client:
          query = (
            "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol,"
            " cf3 FROM {table_id} WHERE _key='mykey'"
          )
          async for row in await client.execute_query(query, instance_id):
            print(row["_key"], row["bytesCol"], row["stringCol"], row["cf3"])

SELECT *用量

如果從查詢的資料表中新增或刪除資料欄系列，SELECT * 查詢可能會暫時發生錯誤。因此，對於實際工作環境工作負載，建議您在查詢中指定所有資料欄系列 ID，而不是使用 SELECT *。例如，使用 SELECT cf1, cf2, cf3 而不是 SELECT *。

後續步驟