Ringkasan GoogleSQL untuk Bigtable

Pratinjau

Produk atau fitur ini tunduk pada "Persyaratan Penawaran Pra-GA" di bagian Persyaratan Layanan Umum di Persyaratan Khusus Layanan. Produk dan fitur pra-GA tersedia "sebagaimana adanya" dan mungkin memiliki dukungan terbatas. Untuk mengetahui informasi selengkapnya, lihat deskripsi tahap peluncuran.

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

Dokumen ini memberikan ringkasan tentang GoogleSQL untuk Bigtable. Panduan 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 menjalankannya secara terprogram menggunakan library klien Bigtable untuk Java. Untuk 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 lengkap atau filter yang kompleks. Untuk informasi selengkapnya, lihat Pembacaan dan performa.

Anda tidak dapat menggunakan Data Boost dengan GoogleSQL untuk Bigtable.

Kasus penggunaan

GoogleSQL untuk Bigtable optimal untuk pengembangan aplikasi berlatensi rendah. Selain itu, menjalankan kueri SQL di konsol Google 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 saat ini untuk Bigtable tidak mendukung beberapa konstruksi SQL umum, termasuk, tetapi tidak terbatas pada, hal berikut:

• Pernyataan Bahasa Manipulasi 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, GROUP BY, UNNEST, dan CTEs

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

Konsep utama

Bagian ini membahas konsep utama yang perlu diperhatikan saat Anda 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 di baris tersebut

Jenis data peta

GoogleSQL untuk Bigtable menyertakan jenis data MAP<key, value>, yang dirancang khusus untuk mengakomodasi keluarga kolom.

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

Berikut adalah contoh kueri SQL yang menampilkan tabel dengan nilai kunci baris dan nilai terbaru 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.

Dalam hal ini, peta yang mewakili keluarga kolom disusun bertingkat dan ditampilkan sebagai array. Dalam array, setiap kunci 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 dalam kolom tersebut di baris tersebut, dan diurutkan berdasarkan stempel waktu 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. 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 direpresentasikan dengan peta kosong dan ditampilkan sebagai nilai NULL. Nilai NULL ini dapat digunakan sebagai predikat kueri. Misalnya, prediket 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 secara implisit melakukan transmisi dari nilai STRING ke nilai BYTES. Artinya, 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 pembacaan dengan fungsi CAST. Untuk mengetahui informasi selengkapnya tentang transmisi, 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.

Argumen	Deskripsi
as_of	Timestamp. Menampilkan nilai terbaru dengan stempel waktu kurang dari atau sama dengan stempel waktu yang diberikan.
with_history	Boolean. Mengontrol apakah akan menampilkan nilai terbaru sebagai nilai skalar atau berstempel waktu sebagai STRUCT.
after_or_equal	Timestamp. Nilai dengan stempel waktu setelah input, inklusif. Memerlukan with_history => TRUE
before	Timestamp. Nilai dengan stempel waktu sebelum input, eksklusif. Memerlukan with_history => TRUE
latest_n	Bilangan bulat. Jumlah nilai stempel waktu yang akan ditampilkan per penentu kolom (kunci peta). Harus lebih besar dari atau sama dengan 1. Memerlukan with_history => TRUE.

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 – data untuk setiap baris.

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

		address
_key	street	city	dengan status tersembunyi akhir
pengguna1	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'

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

   SELECT * FROM myTable WHERE _key = 'user1'

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

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

Mengambil semua versi

Untuk mengambil versi (sel) data yang lebih lama, gunakan flag 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 tempat asalnya, 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 flag with_history dalam kueri SQL, respons akan ditampilkan sebagai MAP<key, ARRAY<STRUCT<timestamp, value>>>. Setiap item dalam array adalah nilai 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.

Catatan: Kebijakan pembersihan sampah mengontrol waktu habis masa berlaku atau jumlah versi yang dipertahankan untuk keluarga kolom. Untuk informasi selengkapnya, lihat Ringkasan pembersihan sampah memori.

Respons kueri adalah sebagai berikut.

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

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 seperti pada titik waktu tertentu. Misalnya, jika ingin mengetahui alamat user pada pukul 13.14 tanggal 10 Januari 2022, Anda dapat menjalankan kueri berikut.

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022/01/10-13:14:00'))
  WHERE _key = 'user1'

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

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

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 kolom adalah smoke dan carbonMonoxide. Sel di setiap kolom dipisahkan oleh baris kosong.

	alarmType
_key	asap	carbonMonoxide
building1#section1	2023/04/01-09:10:15.000: 'nonaktif' 2023/04/01-08:41:40.000: 'aktif' 2020/07/03-06:25:31.000: 'nonaktif' 2020/07/03-06:02:04.000: 'aktif'	2023/04/01-09:22:08.000: 'nonaktif' 2023/04/01-08:53:12.000: 'aktif'
building1#section2	2021/03/11-07:15:04.000: 'nonaktif' 2021/03/11-07:00:25.000: 'aktif'

Anda dapat menemukan bagian building1 tempat 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, sensorType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023/04/01-09:00:00.000'))
  WHERE _key LIKE 'building1%' and sensorType['smoke'] = 'on'

Hasilnya adalah sebagai berikut:

location	CO_sensor
building1#section1	'on'

Membuat kueri data deret waktu

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

	metrik
_key	temperature	kelembapan
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

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(after => TIMESTAMP('2023/01/04-23:00:00'),
         before => TIMESTAMP('2023/01/05-01:00:00'))
   WHERE _key LIKE 'sensorA%'

Kueri menampilkan data dalam format ini:

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}

Membuat 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 keluarga kolom session beserta kunci baris menggunakan kueri berikut.

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

Meng-escape karakter khusus dan kata yang direservasi

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 adanya titik dalam nama tabel.

  -- ERROR: Table name format not supported

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

Namun, Anda dapat mengatasi masalah ini dengan mengapit item dengan karakter backtick (`).

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

Jika kata kunci yang dicadangkan SQL digunakan sebagai ID, kata kunci tersebut juga dapat di-escape.

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

Menggunakan SQL dengan library klien Bigtable

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

Java

Untuk menggunakan fitur ini, Anda harus menggunakan java-bigtable versi 2.41.0 atau yang lebih baru. Untuk mengetahui informasi selengkapnya tentang penggunaan, lihat executeQuery, Statement, dan ResultSet di Javadoc.

  static void query(BigtableDataClient client) {
    try (ResultSet resultSet =
        client.executeQuery(
            Statement.of(
                "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key='mykey'"))) {
      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
      }
    }
  }

Asyncio Python

Untuk menggunakan fitur ini, Anda harus menggunakan python-bigtable versi 2.26.0 atau yang lebih baru.

  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"])

Langkah selanjutnya

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