Contoh kueri GoogleSQL untuk Bigtable

Catatan: Halaman ini mencakup fitur yang hanya tersedia di edisi Enterprise Plus, seperti Data Boost untuk SQL. Untuk mengetahui informasi selengkapnya, lihat Ringkasan edisi.

Contoh di halaman ini menunjukkan pola kueri SQL untuk kueri Bigtable umum dan lanjutan. Anda dapat menjalankan kueri GoogleSQL di editor kueri Bigtable Studio. Anda juga dapat menjalankan kueri menggunakan library klien Bigtable untuk Java.

Sebelum membaca halaman ini, baca Ringkasan GoogleSQL untuk Bigtable.

Catatan: Versi mengacu pada nilai yang diberi stempel waktu untuk kolom dan baris tertentu. Setiap versi disimpan dalam sel. Untuk mengetahui informasi selengkapnya, lihat Model penyimpanan Bigtable.

Contoh di halaman ini menggunakan ID dan nilai yang serupa dengan yang ada di Data untuk contoh.

Pola kueri SQL Bigtable umum

Berikut adalah contoh kueri umum untuk data Bigtable. Untuk melihat contoh kueri serupa yang memanggil Bigtable Data API, lihat Contoh baca dan Menggunakan filter. Untuk contoh kueri pada kunci baris terstruktur, lihat Kueri kunci baris terstruktur.

Mengambil versi terbaru semua kolom untuk kunci baris tertentu.

  SELECT * FROM myTable WHERE _key = 'r1'

Mengambil semua versi semua kolom untuk kunci baris tertentu.

  SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'

Mengambil versi terbaru kolom tertentu dari family kolom tertentu untuk kunci baris tertentu.

  SELECT stats_summary['os_build'] AS os
  FROM analytics
  WHERE _key = 'phone#4c410523#20190501'

Mengambil kunci baris dan versi terbaru dari beberapa kolom untuk rentang kunci baris tertentu.

  SELECT
    _key,
    stats_summary['os_build'] AS os,
    stats_summary['user_agent'] AS agent
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

Mengambil semua versi semua kolom untuk beberapa rentang kunci baris, hingga 10 baris.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE
    (_key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201')
    OR (_key >= 'phone#5c10102#20190501' AND _key < 'phone#5c10102#20190601')
  LIMIT 10

Mengambil semua versi semua kolom untuk beberapa kunci baris.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'

Ambil semua versi semua kolom untuk beberapa kunci baris menggunakan pendekatan yang berbeda.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')

Mengambil versi terbaru semua kolom dalam grup kolom untuk awalan kunci baris.

  SELECT stats_summary
  FROM analytics
  WHERE _key LIKE 'phone#%'

Mengambil kunci baris dan tiga versi terbaru dari semua kolom dalam kolom family untuk semua baris dalam tabel. Kueri ini memerlukan pemindaian tabel penuh, sehingga tidak direkomendasikan untuk pola akses latensi rendah dan throughput tinggi.

  SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)

Mengambil versi terbaru semua kolom dengan kunci baris yang cocok dengan ekspresi reguler yang ditentukan. Kueri ini memerlukan pemindaian tabel lengkap, jadi tidak direkomendasikan untuk pola akses latensi rendah dan throughput tinggi, kecuali jika Anda juga memberikan awalan row key atau predikat rentang row key dalam klausa WHERE.

  SELECT *
  FROM myTable(with_history => TRUE)
  WHERE REGEXP_CONTAINS(_key, '.*#20190501$')

Ambil versi terbaru semua kolom dengan awalan kunci baris yang cocok dan nilai counter lebih dari 123. Anda tidak perlu melakukan transmisi untuk perbandingan ini, karena agregasi Bigtable adalah numerik.

  SELECT *
  FROM myTable
  WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123

Mengambil versi terbaru semua kolom untuk awalan kunci baris jika perujuk cocok dengan nilai tertentu.

  SELECT *
  FROM analytics
  WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'

Mengategorikan baris tertentu berdasarkan nilai kolom tertentu. Kueri ini mirip dengan penggunaan filter bersyarat penyusunan di Bigtable Data API.

  SELECT
    *,
    CASE cell_plan['data_plan']
      WHEN '10gb' THEN 'passed-filter'
      ELSE 'filtered-out'
      END
      AS label
  FROM analytics

Mengambil kunci baris dan penentu kolom dalam grup kolom tertentu untuk rentang kunci baris yang ditentukan. Di SQL, grup kolom diwakili oleh jenis data peta, dengan setiap penentu dan nilai kolom dipetakan sebagai pasangan nilai kunci. Kueri SQL ini mirip dengan penggunaan filter nilai strip di Bigtable Data API.

  SELECT _key, MAP_KEYS(cell_plan) AS keys
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

Fungsi UNPACK memungkinkan Anda mengubah data Bigtable menjadi format deret waktu tabular, yang berguna saat melakukan analisis deret waktu. Pertimbangkan contoh saat Anda memiliki kolom clicks di kolom engagement. Kueri berikut menggunakan UNPACK untuk melihat performa kampanye tertentu dengan menggabungkan klik selama satu menit dari satu jam terakhir.

  SELECT
    FORMAT_TIMESTAMP('%M', _timestamp) AS minute,
    COUNT(clicks) AS total_clicks
  FROM
    UNPACK((
      SELECT engagement['clicks'] as clicks
      FROM metrics(with_history => true, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
      WHERE _key = @campaign_id
    ))
  GROUP BY
    minute;

Pola kueri SQL Bigtable lanjutan

Contoh berikut menunjukkan pola yang lebih canggih.

Dengan kueri berikut, Anda dapat mengambil kunci baris dan nilai terbaru dari atribut JSON abc dalam kelompok kolom session. Untuk mengetahui informasi selengkapnya, lihat fungsi JSON.

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

Dengan kueri berikut, Anda dapat mengambil kunci baris dan menghitung durasi sesi rata-rata menggunakan nilai terbaru dari dua sel agregat Bigtable , yang bersifat numerik, untuk setiap baris dalam tabel.

  SELECT
    _key AS userid,
    session['total_minutes'] / session['count'] AS avg_session_length
  FROM analytics

Dengan kueri berikut, Anda dapat mengambil versi terbaru semua kolom untuk awalan kunci baris tertentu jika grup kolom session berisi referrer, origin, atau server sebagai penentu kolom. Atau, kueri ini juga dapat ditulis sebagai serangkaian perbandingan individual seperti session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL. Namun, untuk kueri yang melibatkan sejumlah besar perbandingan, sebaiknya gunakan pendekatan berikut.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])

Dengan kueri berikut, Anda dapat mengambil versi terbaru semua kolom untuk awalan kunci baris tertentu jika grup kolom session berisireferrer, origin, dan server sebagai kualifikasi kolom. Atau, kueri ini dapat ditulis sebagai serangkaian perbandingan individual seperti session['referrer'] IS NOT NULL AND session ['origin'] IS NOT NULL.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ALL(MAP_KEYS(session), ['referrer', 'origin', 'server'])

Dengan kueri berikut, Anda dapat mengambil versi terbaru semua kolom untuk awalan kunci baris tertentu jika grup kolom session berisi com.google.search, com.google.maps, atau com.google.shopping sebagai nilai.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(
      MAP_VALUES(session),
      ['com.google.search', 'com.google.maps', 'com.google.shopping'])

Dengan kueri berikut, Anda dapat mengambil versi terbaru semua kolom jika key-value pair dalam kelompok kolom cell_plan mencakup data_plan:unlimited dan roaming:North America.

  SELECT *
  FROM analytics
  WHERE
    ARRAY_INCLUDES_ALL(
      CAST(
        MAP_ENTRIES(cell_plan)
        AS ARRAY<STRUCT<key STRING, value STRING>>),
      [('data_plan', 'unlimited'), ('roaming', 'North America')])

Dengan kueri berikut, Anda dapat mengambil pembacaan row key dan temperature untuk sensor cuaca dalam kasus ketika suhu melebihi 70 derajat selama tujuh pengukuran terakhir.

  SELECT
    _key AS sensorid,
    ARRAY_FILTER(
      CAST(
        sensor['temperature']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> CAST(e.value AS FLOAT32) > 70) AS high_temperature
  FROM weather(with_history => TRUE, latest_n => 7)

Dalam urutan pemfilteran temporal, latest_n berada di urutan terakhir, sehingga kueri seperti after => X, before => y, latest_n => 3 menampilkan tiga nilai terbaru yang memenuhi kondisi after dan before. Jika kasus penggunaan Anda memerlukan latest_n untuk diprioritaskan, Anda dapat memberikan latest_n sebagai satu-satunya filter temporal, lalu menerapkan filter temporal lainnya menggunakan operator kueri dalam pernyataan SELECT, seperti yang ditunjukkan dalam contoh. Untuk mengetahui informasi selengkapnya, lihat Filter temporal.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> e.timestamp > TIMESTAMP('2021-01-04T23:51:00.000Z'))
      AS street_address
  FROM locations(with_history => TRUE, latest_n => 3)

Mirip dengan contoh sebelumnya, Anda dapat menerapkan filter temporal yang berbeda ke setiap family kolom dalam kueri Anda. Misalnya, kueri berikut menampilkan tiga versi terbaru kolom street dan dua versi terlama kolom state.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      (e, i) -> i <= 2)
      AS street_address,
    ARRAY_FILTER(
      ARRAY_REVERSE(
        CAST(
          address['state']
          AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>)),
      (e, i) -> i <= 1)
      AS state
  FROM locations(with_history => TRUE)

Dengan kueri berikut, Anda dapat mengambil semua versi semua kolom jika pasangan nilai kunci dalam grup kolom alamat menyertakan city:Savannah atau city:Nashville kapan saja.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        CAST(
          MAP_ENTRIES(address)
          AS ARRAY<
            STRUCT<
              key STRING,
              value ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>>>),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

Dalam contoh khusus ini, casting tidak diperlukan, sehingga dapat juga ditulis dalam bentuk yang lebih singkat berikut.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        MAP_ENTRIES(address),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

Analisis SQL throughput tinggi dengan Data Boost

Pada edisi Enterprise Plus, untuk workload analitik dengan throughput tinggi, Anda dapat menggunakan Data Boost untuk menjalankan kueri yang memindai data dalam jumlah besar tanpa memengaruhi performa cluster produksi Anda. Kueri ini optimal untuk analisis real-time dan menghasilkan insight dari data historis atau deret waktu. Untuk menjalankan kueri ini, Anda harus menggunakan profil aplikasi yang dikonfigurasi untuk Data Boost.

Contoh berikut menunjukkan pola SQL untuk analisis throughput tinggi menggunakan komputasi serverless.

Menganalisis data deret waktu historis

Dengan Peningkatan Data, Anda dapat mengambil kunci baris dan semua versi metrik untuk semua baris dalam tabel dari 24 jam terakhir. Kueri berikut melakukan pemindaian tabel penuh, yang ditangani oleh komputasi serverless terisolasi.

SELECT _key, metrics
FROM myTable(with_history => TRUE, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR))

Menggabungkan set data besar

Kueri berikut menunjukkan cara menghitung jumlah total peristiwa untuk setiap perangkat selama rentang waktu tertentu di seluruh tabel.

SELECT
  _key AS device_id,
  COUNT(events['event_type']) AS total_events
FROM
  UNPACK((
    SELECT events['event_type']
    FROM sensor_data(with_history => TRUE, after => TIMESTAMP('2026-01-01T00:00:00Z'))
  ))
GROUP BY
  Device_id;

Pemindaian rentang besar

Kueri berikut menunjukkan cara mengambil semua kolom untuk rentang besar kunci baris. Dengan menggunakan Data Boost, permintaan throughput tinggi ini tidak akan menggunakan resource CPU di node cluster Anda.

SELECT *
FROM analytics
WHERE _key >= 'user#000000' AND _key < 'user#999999'

Langkah berikutnya

• Jelajahi dokumentasi referensi GoogleSQL untuk Bigtable.
• Gunakan konektor Bigtable Spark.
• Mengelola skema kunci baris.