Contoh kueri GoogleSQL untuk Bigtable

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.

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'

Mengambil 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 family kolom 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 penghitung lebih dari 123. Anda tidak perlu melakukan transmisi untuk perbandingan ini, karena Bigtable menggabungkan data 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, kelompok kolom diwakili oleh jenis data peta, dengan setiap kualifikasi 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 mentransformasi data Bigtable ke dalam format deret waktu tabular, yang berguna saat melakukan analisis deret waktu. Pertimbangkan contoh saat Anda memiliki kolom clicks di family 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 berisi referrer, origin, dan server sebagai penentu 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 pasangan nilai kunci 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 mengharuskan latest_n 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)

Serupa dengan contoh sebelumnya, Anda dapat menerapkan filter temporal yang berbeda ke setiap family kolom dalam kueri. 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

Langkah berikutnya