Membuat kueri log perubahan Bigtable di BigQuery

Halaman ini menyediakan panduan dan contoh kueri untuk membantu Anda memproses log perubahan Bigtable di BigQuery.

Halaman ini ditujukan bagi pengguna yang telah menyelesaikan hal berikut:

Panduan ini mengasumsikan Anda memiliki pengetahuan tentang BigQuery. Untuk mempelajari lebih lanjut, Anda dapat mengikuti panduan memulai yang menunjukkan cara memuat dan mengkueri data.

Membuka tabel log perubahan

  1. Di konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di panel Explorer, luaskan project Anda.

  3. Perluas set data Anda.

  4. Klik tabel dengan akhiran: _changelog.

Format tabel

Seluruh skema output berisi beberapa kolom. Panduan ini berfokus pada menghubungkan baris ke kolom dan nilai, serta mengurai nilai ke dalam format yang dapat dianalisis.

Kueri dasar

Contoh di bagian ini menggunakan tabel Bigtable untuk melacak penjualan kartu kredit. Tabel ini memiliki satu grup kolom (cf) dan kolom berikut:

  • Kunci baris dengan format credit card number#transaction timestamp
  • Penjual
  • Jumlah
  • Kategori
  • Tanggal transaksi

Membuat kueri satu kolom

Filter hasilnya hanya ke satu grup kolom dan satu kolom menggunakan klausa WHERE.

SELECT row_key, column_family, column, value, timestamp,
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="merchant"
LIMIT 1000

Mengurai nilai

Semua nilai disimpan sebagai string atau string byte. Anda dapat mentransmisikan nilai ke jenis yang diinginkan dengan fungsi konversi.

SELECT row_key, column_family, column, value, CAST(value AS NUMERIC) AS amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"
LIMIT 1000

Melakukan agregasi

Anda dapat melakukan lebih banyak operasi, seperti agregasi pada nilai numerik.

SELECT SUM(CAST(value AS NUMERIC)) as total_amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"

Memutar data

Untuk melakukan kueri yang melibatkan beberapa kolom Bigtable, Anda perlu melakukan pivot pada tabel. Setiap baris BigQuery baru menyertakan satu catatan perubahan data yang ditampilkan oleh aliran perubahan dari baris yang sesuai dalam tabel BigQuery. Bergantung pada skema, Anda dapat menggunakan kombinasi row key dan stempel waktu untuk mengelompokkan data.

SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in ("merchant", "amount", "category", "transaction_date")
)

Beradaptasi dengan kumpulan kolom dinamis

Jika memiliki kumpulan kolom yang dinamis, Anda dapat melakukan beberapa pemrosesan tambahan untuk mendapatkan semua kolom dan memasukkannya ke dalam kueri secara terprogram.

DECLARE cols STRING;
SET cols = (
  SELECT CONCAT('("', STRING_AGG(DISTINCT column, '", "'), '")'),
  FROM your_dataset.your_table
);

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in %s
)""", cols);

Data JSON

Jika semua nilai ditetapkan dengan JSON, Anda harus mengurainya dan mengekstrak nilai tersebut berdasarkan kunci. Anda dapat menggunakan fungsi penguraian setelah mendapatkan nilai dari objek JSON. Contoh ini menggunakan data penjualan kartu kredit yang diperkenalkan sebelumnya, tetapi data tersebut ditulis sebagai satu kolom sebagai objek JSON, bukan menulis data ke beberapa kolom.

SELECT
  row_key,
  JSON_VALUE(value, "$.category") as category,
  CAST(JSON_VALUE(value, "$.amount") AS NUMERIC) as amount
FROM your_dataset.your_table
LIMIT 1000

Kueri agregasi dengan JSON

Anda dapat menjalankan kueri agregasi dengan nilai JSON.

SELECT
  JSON_VALUE(value, "$.category") as category,
  SUM(CAST(JSON_VALUE(value, "$.amount") AS NUMERIC)) as total_amount
FROM your_dataset.your_table
GROUP BY category

Langkah selanjutnya