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:
- Menyiapkan tabel Bigtable yang mengaktifkan aliran data perubahan.
- Jalankan template Dataflow yang menulis log perubahan ke BigQuery. Anda dapat mengikuti panduan memulai untuk mempelajari cara menyiapkannya.
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
Di Konsol Google Cloud, buka halaman BigQuery.
Di panel Explorer, luaskan project Anda.
Perluas set data Anda.
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
- Pelajari aliran perubahan Bigtable lebih lanjut.
- Lihat halaman referensi template streaming perubahan Bigtable ke BigQuery.
- Pelajari cara menyiapkan aliran perubahan Bigtable ke template BigQuery.