Membuat kueri log perubahan Bigtable di BigQuery

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

Halaman ini ditujukan untuk pengguna yang telah menyelesaikan hal berikut:

Panduan ini mengasumsikan bahwa Anda memiliki pengetahuan tentang BigQuery. Untuk mempelajari lebih lanjut, Anda dapat mengikuti panduan memulai yang menunjukkan cara memuat dan membuat kueri 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 nilainya, serta menguraikan nilai ke dalam format yang dapat dianalisis.

Kueri dasar

Contoh di bagian ini menggunakan tabel Bigtable untuk melacak penjualan kartu kredit. Tabel 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 hasil hanya ke satu keluarga 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 dimaksudkan 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 menjalankan kueri yang melibatkan beberapa kolom Bigtable, Anda harus memutar tabel. Setiap baris BigQuery baru menyertakan satu data perubahan yang ditampilkan oleh aliran perubahan dari baris yang sesuai di tabel Bigtable Anda. Bergantung pada skema Anda, Anda dapat menggunakan kombinasi kunci baris 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")
)

Memutar dengan set kolom dinamis

Jika memiliki kumpulan kolom 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 menetapkan semua nilai dengan JSON, Anda perlu mengurainya dan mengekstrak nilai 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 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 melakukan 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