Ringkasan GoogleSQL untuk Bigtable

Anda dapat menggunakan pernyataan GoogleSQL untuk membuat kueri data Bigtable. GoogleSQL adalah bahasa kueri terstruktur (SQL) yang sesuai dengan ANSI dan juga diterapkan untuk layanan Google Cloud lainnya seperti BigQuery dan Spanner.

Dokumen ini memberikan ringkasan GoogleSQL untuk Bigtable. Dokumen ini memberikan contoh kueri SQL yang dapat Anda gunakan dengan Bigtable dan menjelaskan hubungannya dengan skema tabel Bigtable. Sebelum membaca dokumen ini, Anda harus memahami model penyimpanan Bigtable dan konsep desain skema.

Anda dapat membuat dan menjalankan kueri di Bigtable Studio di konsol Google Cloud , atau Anda dapat menjalankannya secara terprogram menggunakan library klien Bigtable untuk Java, Python, atau Go. Untuk mengetahui informasi selengkapnya, lihat Menggunakan SQL dengan library klien Bigtable.

Kueri SQL ditangani oleh node cluster dengan cara yang sama seperti permintaan data NoSQL. Oleh karena itu, praktik terbaik yang sama berlaku saat membuat kueri SQL untuk dijalankan terhadap data Bigtable Anda, seperti menghindari pemindaian tabel penuh atau filter yang kompleks. Untuk mengetahui informasi selengkapnya, lihat Pembacaan dan performa.

Anda tidak dapat menggunakan Data Boost dengan GoogleSQL untuk Bigtable.

Kasus penggunaan

GoogleSQL untuk Bigtable sangat optimal untuk pengembangan aplikasi berlatensi rendah. Selain itu, menjalankan kueri SQL di konsolGoogle Cloud dapat berguna untuk mendapatkan representasi visual skema tabel dengan cepat, memverifikasi bahwa data tertentu telah ditulis, atau men-debug kemungkinan masalah data.

Rilis GoogleSQL untuk Bigtable saat ini tidak mendukung beberapa konstruksi SQL umum, termasuk, tetapi tidak terbatas pada, berikut ini:

• Pernyataan Bahasa Pengolahan Data (DML) di luar SELECT, seperti INSERT, UPDATE, atau DELETE
• Pernyataan Bahasa Definisi Data (DDL) seperti CREATE, ALTER, atau DROP
• Pernyataan Kontrol Akses Data
• Sintaksis kueri untuk subkueri, JOIN, UNION, UNNEST, dan CTEs

Untuk mengetahui informasi selengkapnya, termasuk fungsi, operator, jenis data, dan sintaksis kueri yang didukung, lihat dokumentasi referensi GoogleSQL untuk Bigtable.

Dilihat

Anda dapat menggunakan GoogleSQL untuk Bigtable guna membuat resource berikut:

• Tampilan terwujud - hasil yang telah dihitung sebelumnya dari kueri SQL yang berjalan terus-menerus, termasuk data gabungan, yang disinkronkan dengan tabel sumbernya dengan update inkremental.
• Tampilan logis - kueri tersimpan dan diberi nama yang dapat dikueri seperti tabel.

Untuk membandingkan jenis tampilan ini serta tampilan yang diotorisasi, lihat Tabel dan tampilan.

Konsep utama

Bagian ini membahas konsep utama yang perlu Anda ketahui saat menggunakan GoogleSQL untuk membuat kueri data Bigtable.

Grup kolom dalam respons SQL

Di Bigtable, tabel berisi satu atau beberapa grup kolom, yang digunakan untuk mengelompokkan kolom. Saat Anda membuat kueri tabel Bigtable dengan GoogleSQL, skema untuk tabel tersebut terdiri dari hal berikut:

• Kolom khusus bernama _key yang sesuai dengan kunci baris dalam tabel yang dikueri
• Satu kolom untuk setiap grup kolom Bigtable dalam tabel, yang berisi data grup kolom dalam baris tersebut

Jenis data peta

GoogleSQL untuk Bigtable mencakup jenis data MAP<key, value>, yang dirancang khusus untuk mengakomodasi column family.

Secara default, setiap baris dalam kolom peta berisi pasangan nilai kunci, dengan kunci adalah penentu kolom Bigtable dalam tabel yang dikueri, dan nilai adalah nilai terbaru untuk kolom tersebut.

Berikut adalah contoh kueri SQL yang menampilkan tabel dengan nilai kunci baris dan nilai terbaru dari penentu dari peta bernama columnFamily.

  SELECT _key, columnFamily['qualifier'] FROM myTable

Jika skema Bigtable Anda melibatkan penyimpanan beberapa sel – atau versi data – dalam kolom, Anda dapat menambahkan filter temporal, seperti with_history, ke pernyataan SQL Anda.

Dalam hal ini, peta yang merepresentasikan family kolom disusun bertingkat dan ditampilkan sebagai array. Dalam array, setiap nilai itu sendiri adalah peta yang terdiri dari stempel waktu sebagai kunci dan data sel sebagai nilai. Formatnya adalah MAP<key, ARRAY<STRUCT<timestamp, value>>>.

Contoh berikut menampilkan semua sel dalam grup kolom 'info' untuk satu baris.

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

Peta yang ditampilkan akan terlihat seperti berikut. Dalam tabel yang dikueri, info adalah grup kolom, user_123 adalah kunci baris, dan city serta state adalah penentu kolom. Setiap pasangan nilai stempel waktu (STRUCT) dalam array mewakili sel di kolom tersebut dalam baris itu, dan diurutkan berdasarkan stempel waktu secara menurun.

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

Tabel jarang

Fitur utama Bigtable adalah model datanya yang fleksibel. Dalam tabel Bigtable, jika kolom tidak digunakan dalam baris, tidak ada data yang disimpan untuk kolom tersebut. Satu baris mungkin memiliki satu kolom dan baris berikutnya mungkin memiliki 100 kolom. Sebaliknya, dalam tabel database relasional, semua baris berisi semua kolom, dan nilai NULL biasanya disimpan di kolom baris yang tidak memiliki data untuk kolom tersebut.

Namun, saat Anda membuat kueri tabel Bigtable dengan GoogleSQL, kolom yang tidak digunakan akan ditampilkan dengan peta kosong dan ditampilkan sebagai nilai NULL. Nilai NULL ini dapat digunakan sebagai predikat kueri. Misalnya, predikat seperti WHERE family['column1'] IS NOT NULL dapat digunakan untuk menampilkan baris hanya jika column1 digunakan dalam baris.

Byte

Saat Anda memberikan string, GoogleSQL secara default akan melakukan transmisi implisit dari nilai STRING ke nilai BYTES. Misalnya, Anda dapat memberikan string 'qualifier', bukan urutan byte b'qualifier'.

Karena Bigtable secara default memperlakukan semua data sebagai byte, sebagian besar kolom Bigtable tidak berisi informasi jenis. Namun, dengan GoogleSQL, Anda dapat menentukan skema pada waktu baca dengan fungsi CAST. Untuk mengetahui informasi selengkapnya tentang casting, lihat Fungsi konversi.

Filter temporal

Tabel berikut mencantumkan argumen yang dapat Anda gunakan saat mengakses elemen temporal tabel. Argumen dicantumkan sesuai urutan pemfilterannya. Misalnya, with_history diterapkan sebelum latest_n. Anda harus memberikan stempel waktu yang valid.

Untuk contoh lainnya, lihat Pola kueri lanjutan.

Kueri dasar

Bagian ini menjelaskan dan menunjukkan contoh kueri SQL Bigtable dasar dan cara kerjanya. Untuk contoh kueri tambahan, lihat Contoh pola kueri GoogleSQL untuk Bigtable.

Mengambil versi terbaru

Meskipun Bigtable memungkinkan Anda menyimpan beberapa versi data di setiap kolom, GoogleSQL untuk Bigtable secara default menampilkan versi terbaru – sel terbaru – dari data untuk setiap baris.

Pertimbangkan contoh set data berikut, yang menunjukkan bahwa user1 dipindahkan dua kali di negara bagian NY dan sekali di dalam kota Brooklyn. Dalam contoh ini, address adalah grup kolom, dan penentu kolomnya adalah street, city, dan state. Sel dalam kolom dipisahkan oleh baris kosong.

Untuk mengambil versi terbaru setiap kolom untuk user1, Anda dapat menggunakan pernyataan SELECT seperti berikut.

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

Respons berisi alamat saat ini, yang merupakan kombinasi dari nilai jalan, kota, dan negara bagian terbaru (ditulis pada waktu yang berbeda) yang dicetak sebagai JSON. Stempel waktu tidak disertakan dalam respons.

Mengambil semua versi

Untuk mengambil versi (sel) data yang lebih lama, gunakan tanda with_history. Anda juga dapat membuat alias kolom dan ekspresi, seperti yang diilustrasikan dalam contoh berikut.

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

Untuk lebih memahami peristiwa yang menyebabkan status baris saat ini, Anda dapat mengambil stempel waktu untuk setiap nilai dengan mengambil histori lengkap. Misalnya, untuk memahami kapan user1 pindah ke alamatnya saat ini dan dari mana dia pindah, Anda dapat menjalankan kueri berikut:

  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'

Saat Anda menggunakan tanda with_history dalam kueri SQL, respons akan ditampilkan sebagai MAP<key, ARRAY<STRUCT<timestamp, value>>>. Setiap item dalam array adalah nilai yang diberi stempel waktu untuk baris, grup kolom, dan kolom yang ditentukan. Stempel waktu diurutkan dalam urutan kronologis terbalik, sehingga data terbaru selalu menjadi item pertama yang ditampilkan.

Respons kuerinya adalah sebagai berikut.

Anda juga dapat mengambil jumlah versi di setiap baris menggunakan fungsi array seperti yang ditunjukkan dalam kueri berikut:

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

Mengambil data dari waktu yang ditentukan

Dengan menggunakan filter as_of, Anda dapat mengambil status baris sebagaimana adanya pada titik waktu tertentu. Misalnya, jika Anda ingin mengetahui alamat user pada 10 Januari 2022 pukul 13.14, Anda dapat menjalankan kueri berikut.

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

Hasilnya menunjukkan alamat terakhir yang diketahui pada 10 Januari 2022 pukul 13.14, yang merupakan kombinasi jalan dan kota dari pembaruan 2021/12/20-09:44:31.010 dan negara bagian dari 2005/03/01-11:12:15.112.

Hasil yang sama juga dapat dicapai menggunakan stempel waktu Unix.

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

Pertimbangkan set data berikut, yang menunjukkan status aktif atau nonaktif alarm asap dan karbon monoksida. Grup kolom adalah alarmType dan penentu kolomnya adalah smoke dan carbonMonoxide. Sel di setiap kolom dipisahkan oleh baris kosong.

Anda dapat menemukan bagian building1 saat alarm asap aktif pada pukul 09.00 pada 1 April 2023 dan status alarm karbon monoksida pada saat itu menggunakan kueri berikut.

  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'

Hasilnya adalah sebagai berikut:

Membuat kueri data deret waktu

Kasus penggunaan umum untuk Bigtable adalah penyimpanan data deret waktu. Pertimbangkan contoh set data berikut, yang menunjukkan pembacaan suhu dan kelembapan untuk sensor cuaca. ID grup kolom adalah metrics dan penentu kolomnya adalah temperature dan humidity. Sel dalam kolom dipisahkan oleh baris kosong, dan setiap sel mewakili pembacaan sensor yang diberi stempel waktu.

Anda dapat mengambil rentang nilai stempel waktu tertentu menggunakan filter temporal after, before, atau after_or_equal. Contoh berikut menggunakan 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%'

Kueri menampilkan data dalam format ini:

Data deret waktu UNPACK

Saat menganalisis data deret waktu, sebaiknya Anda menggunakan data dalam format tabel. Fungsi UNPACK Bigtable dapat membantu.

UNPACK adalah fungsi bernilai tabel (TVF) Bigtable yang menampilkan seluruh tabel output, bukan satu nilai skalar, dan muncul dalam klausa FROM seperti subkueri tabel. TVF UNPACK memperluas setiap nilai yang diberi stempel waktu menjadi beberapa baris – satu per stempel waktu – dan memindahkan stempel waktu ke kolom _timestamp.

Input ke UNPACK adalah subkueri dengan with_history => true.

Outputnya adalah tabel yang diperluas dengan kolom _timestamp di setiap baris.

Grup kolom input MAP<key, ARRAY<STRUCT<timestamp, value>>> diuraikan menjadi MAP<key, value>, dan penentu kolom ARRAY<STRUCT<timestamp, value>>> diuraikan menjadi value. Jenis kolom input lainnya tidak berubah. Kolom harus dipilih dalam subkueri agar dapat diuraikan dan dipilih. Kolom _timestamp baru tidak perlu dipilih agar stempel waktu dapat diuraikan.

Mengembangkan contoh deret waktu di Kueri data deret waktu, dan menggunakan kueri di bagian tersebut sebagai input, kueri UNPACK Anda diformat seperti ini:

  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%'
  ));

Kueri menampilkan data dalam format ini:

Kueri JSON

Fungsi JSON memungkinkan Anda memanipulasi JSON yang disimpan sebagai nilai Bigtable untuk beban kerja operasional.

Misalnya, Anda dapat mengambil nilai untuk elemen JSON abc dari sel terbaru dalam family kolom session bersama dengan kunci baris menggunakan kueri berikut.

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

Meng-escape karakter khusus dan kata yang dicadangkan

Bigtable menawarkan fleksibilitas tinggi dalam penamaan tabel dan kolom. Akibatnya, dalam kueri SQL, nama tabel Anda mungkin perlu di-escape karena karakter khusus atau kata yang dicadangkan.

Misalnya, kueri berikut bukan SQL yang valid karena titik di nama tabel.

  -- ERROR: Table name format not supported

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

Namun, Anda dapat menyelesaikan masalah ini dengan menyertakan item dalam karakter kutip terbalik (`).

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

Jika kata kunci SQL yang dicadangkan digunakan sebagai ID, kata kunci tersebut juga dapat dilepaskan.

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

Menggunakan SQL dengan library klien Bigtable

Library klien Bigtable untuk Java, Python, dan Go mendukung kueri data dengan SQL menggunakan executeQuery API. Contoh berikut menunjukkan cara mengeluarkan kueri dan mengakses data:

  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
    })
  }

  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.
      }
    }
  }

  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 * penggunaan

Kueri SELECT * dapat mengalami error sementara saat kolom family ditambahkan atau dihapus dari tabel yang dikueri. Oleh karena itu, untuk workload produksi, sebaiknya tentukan semua ID grup kolom dalam kueri Anda, daripada menggunakan SELECT *. Misalnya, gunakan SELECT cf1, cf2, cf3, bukan SELECT *.

Langkah berikutnya

• Pelajari dokumentasi referensi GoogleSQL untuk Bigtable.
• Pelajari Bigtable Studio lebih lanjut.