Kueri GoogleSQL untuk Bigtable contoh

Contoh di halaman ini menunjukkan pola kueri SQL untuk perintah{i> <i}umum dan lanjutan kueri Bigtable. Anda dapat menjalankan kueri GoogleSQL di Editor kueri Bigtable Studio. Anda juga dapat menjalankan kueri menggunakan library klien Bigtable untuk Java.

Sebelum Anda membaca halaman ini, baca GoogleSQL untuk Ringkasan Bigtable.

Catatan: Versi mengacu pada nilai stempel waktu untuk kolom dan baris tertentu. Setiap versi disimpan dalam sel. Untuk 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 yang umum

Berikut adalah contoh kueri umum untuk data Bigtable. Untuk melihat contoh kueri serupa yang memanggil Data Bigtable API, lihat Baca contoh dan Gunakan filter.

Ambil versi terbaru dari semua kolom untuk baris kunci tertentu.

  SELECT * FROM myTable WHERE _key = 'r1'

Ambil semua versi semua kolom untuk row key tertentu.

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

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

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

Ambil {i>row key<i} dan versi terbaru dari beberapa kolom untuk {i>string<i} rentang kunci baris.

  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'

Ambil semua versi dari 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

Ambil 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 dari semua kolom untuk beberapa {i>row key<i} menggunakan pendekatan.

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

Mengambil versi terbaru dari semua kolom dalam keluarga kolom untuk row key .

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

Ambil {i>row key<i} dan tiga versi terbaru dari semua kolom dalam sebuah kolom keluarga untuk semua baris dalam tabel. Kueri ini membutuhkan pemindaian tabel penuh, jadi tidak direkomendasikan untuk pola akses latensi rendah dan throughput tinggi.

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

Ambil versi terbaru dari semua kolom dengan kunci baris yang cocok dengan ekspresi reguler. Kueri ini membutuhkan pemindaian tabel penuh, jadi tidak direkomendasikan untuk pola akses latensi rendah dan throughput tinggi, kecuali jika Anda juga berikan awalan kunci baris atau predikat rentang kunci baris dalam klausa WHERE.

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

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

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

Mengambil versi terbaru dari 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 menggunakan penghitungan kondisional filter di Bigtable Data API Anda.

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

Ambil row key dan penentu kolom dalam keluarga kolom spesifik untuk rentang kunci baris yang ditentukan. Dalam SQL, kelompok kolom diwakili oleh data peta dengan setiap nilai dan penentu kolom dipetakan sebagai pasangan nilai kunci. Ini Kueri SQL mirip dengan menggunakan nilai strip filter di Bigtable Data API Anda.

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

Pola kueri SQL Bigtable lanjutan

Contoh berikut menunjukkan pola yang lebih canggih.

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

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

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

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

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

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

Dengan kueri berikut ini, Anda dapat mengambil versi terbaru dari semua kolom untuk awalan kunci baris tertentu jika kelompok kolom session berisi referrer, origin, dan server sebagai penentu kolom. Atau, kueri ini dapat berupa yang 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 ini, Anda dapat mengambil versi terbaru dari semua kolom untuk awalan kunci baris tertentu jika kelompok 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 dari semua kolom jika pasangan nilai kunci di kelompok kolom cell_plan mencakup keduanya 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 row key dan temperature pembacaan sensor cuaca untuk 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 sementara, latest_n muncul terakhir, sehingga kueri seperti after => X, before => y, latest_n => 3 menampilkan tiga nilai terbaru yang memenuhi kondisi setelah dan sebelum. Jika kasus penggunaan Anda memerlukan latest_n untuk melakukan prioritasnya, Anda dapat menyediakan latest_n sebagai satu-satunya filter sementara, kemudian menerapkan filter temporal lainnya menggunakan operator kueri di Pernyataan SELECT, seperti yang ditunjukkan dalam contoh. Untuk informasi selengkapnya, lihat Temporal filter.

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

Mirip dengan contoh sebelumnya, Anda dapat menerapkan filter temporal yang berbeda untuk setiap grup kolom di kueri Anda. Misalnya, kueri berikut mengembalikan tiga versi terbaru dari kolom street dan dua versi terbaru dari 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 ini, Anda dapat mengambil semua versi dari semua kolom jika pasangan nilai kunci di kelompok kolom alamat mencakup 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, transmisi tidak diperlukan, jadi ini juga dapat ditulis dalam bentuk yang lebih singkat.

  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 selanjutnya

• Jelajahi dokumentasi referensi GoogleSQL untuk Bigtable.
• Gunakan konektor Bigtable Spark.