Percepat metrik UV dengan tabel intermediate di MaxCompute - MaxCompute

Saat membangun gudang data, perusahaan e-commerce sering perlu menghitung metrik rolling-window—seperti unique visitors (UV), jumlah pembeli, dan jumlah pembeli reguler selama 30 hari terakhir. Metrik ini dihitung berdasarkan data yang terakumulasi dalam periode panjang, dan kueri rolling-window yang tidak dioptimalkan menjadi sangat lambat atau bahkan gagal total ketika skalanya besar.

Akar permasalahannya selalu sama: terlalu banyak data dihitung ulang dari awal pada setiap eksekusi. Dua pendekatan optimasi dalam topik ini menerapkan prinsip yang sama—menghindari perhitungan ulang penuh melalui pre-aggregasi atau akumulasi inkremental—dan hanya berbeda dalam tingkat penerapannya.

Semua contoh kode menggunakan variabel penjadwalan dari DataWorks (misalnya, ${bdp.system.bizdate}). Contoh-contoh ini hanya berlaku untuk scheduling node di DataWorks.

Permasalahan pada kueri rolling-window

Kueri UV 30 hari yang khas terlihat seperti ini:

SELECT item_id,
       COUNT(DISTINCT visitor_id) AS ipv_uv_1d_001
FROM vistor_item_detail_log
WHERE ds <= ${bdp.system.bizdate}
  AND ds >= TO_CHAR(DATEADD(TO_DATE(${bdp.system.bizdate},'yyyymmdd'),-29,'dd'),'yyyymmdd')
GROUP BY item_id;

Kueri ini memindai 30 partisi data log mentah pada setiap eksekusi. Jika volume log Anda besar, MaxCompute mungkin perlu membuat lebih dari 99.999 tugas map—yang menyebabkan pekerjaan gagal. Bahkan di bawah batas tersebut, memindai log mentah selama 30 hari setiap hari merupakan pemborosan karena sebagian besar data tidak berubah sejak eksekusi sebelumnya.

Pilih pendekatan

Dua pola optimasi mengatasi permasalahan ini. Pilih berdasarkan struktur data dan kueri Anda:

Pendekatan	Cara kerja	Gunakan saat
Intermediate table	Hapus duplikat dan agregasi log mentah harian ke dalam tabel ringkasan; kueri tabel ringkasan alih-alih log mentah	Volume log harian besar; data per partisi berubah signifikan antar eksekusi
Incremental accumulation	Gabungkan semua partisi historis ke dalam satu partisi tunggal dan tambahkan data baru setiap hari	Anda menjalankan kueri rolling window yang sama berulang kali; mengurangi pemindaian multi-partisi lebih penting daripada deduplikasi harian

Untuk sebagian besar beban kerja e-commerce, mulailah dengan pendekatan intermediate table. Beralihlah ke incremental accumulation jika pemindaian multi-partisi tetap menjadi bottleneck setelah intermediate table diterapkan.

Pendekatan 1: Intermediate table

Cara kerja

Alih-alih mengkueri 30 partisi log mentah, lakukan pre-aggregasi log harian ke dalam tabel ringkasan. Setiap baris dalam tabel ringkasan merepresentasikan pasangan unik (item_id, visitor_id) untuk hari tersebut. Mengkueri 30 partisi data yang telah di-agregasi jauh lebih hemat dibandingkan mengkueri 30 partisi log mentah, karena langkah pre-aggregasi telah menghilangkan baris duplikat berdimensi tinggi sebelum kueri rolling-window dijalankan.

Langkah 1: Buat ringkasan harian

Jalankan ini sebagai scheduling node harian di DataWorks, sebelum node metrik Anda:

INSERT OVERWRITE TABLE mds_itm_vsr_xx (ds='${bdp.system.bizdate} ')
SELECT item_id,
       visitor_id,
       COUNT(1) AS pv
FROM (
    SELECT item_id,
           visitor_id
    FROM vistor_item_detail_log
    WHERE ds = ${bdp.system.bizdate}
    GROUP BY item_id, visitor_id
) a;

Kode ini menulis satu baris tanpa duplikat per pasangan (item_id, visitor_id) ke dalam mds_itm_vsr_xx untuk partisi tanggal hari ini.

Langkah 2: Kueri dari tabel ringkasan

Dengan adanya tabel ringkasan, kueri rolling-window Anda memindai data yang jauh lebih sedikit:

SELECT item_id,
       COUNT(DISTINCT visitor_id) AS uv,
       SUM(pv) AS pv
FROM mds_itm_vsr_xx
WHERE ds <= '${bdp.system.bizdate} '
  AND ds >= TO_CHAR(DATEADD(TO_DATE('${bdp.system.bizdate} ','yyyymmdd'),-29,'dd'),'yyyymmdd')
GROUP BY item_id;

Penting

Pendekatan ini memerlukan satu scheduling node harian khusus untuk mengisi mds_itm_vsr_xx. Konfigurasikan node tersebut sebagai dependensi hulu dari node metrik Anda di DataWorks agar tabel ringkasan selalu mutakhir sebelum kueri hilir dijalankan.

Pendekatan 2: Incremental accumulation

Cara kerja

Pendekatan intermediate table masih memindai 30 partisi pada setiap eksekusi. Incremental accumulation melangkah lebih jauh: gabungkan seluruh data historis ke dalam satu partisi akumulasi tunggal dan tambahkan hanya data baru setiap hari. Kueri rolling-window kemudian membaca dari hanya satu partisi.

Pendekatan ini paling efektif ketika persentase data yang berubah setiap hari relatif kecil dibandingkan total dataset historis—artinya, sebagian besar data dalam partisi akumulasi stabil antar eksekusi.

Implementasi: tabel dimensi pembeli reguler

Kasus penggunaan pembeli reguler—pembeli yang melakukan pembelian dalam 30 hari terakhir—merupakan contoh yang baik. Pendekatan naif akan memindai 30 partisi log penagihan pada setiap eksekusi:

SELECT item_id,
       buyer_id AS old_buyer_id
FROM buyer_item_detail_log
WHERE ds < ${bdp.system.bizdate}
  AND ds >= TO_CHAR(DATEADD(TO_DATE(${bdp.system.bizdate},'yyyymmdd'),-29,'dd'),'yyyymmdd')
GROUP BY item_id,
         buyer_id;

Sebagai gantinya, kelola tabel dimensi di mana setiap baris merepresentasikan hubungan antara pembeli dan item, mencatat bidang seperti waktu pembelian pertama, waktu pembelian terakhir, total item yang dibeli, dan total pengeluaran. Perbarui tabel ini setiap hari dengan log penagihan dari hari sebelumnya.

Untuk menentukan apakah seorang pembeli termasuk pembeli reguler, periksa apakah last_purchase_time-nya berada dalam 30 hari terakhir. Ini menggantikan pemindaian 30 partisi log penagihan dengan pencarian satu partisi pada tabel dimensi, sehingga menghilangkan proses deduplikasi full-scan pada setiap eksekusi.

Pertimbangan performa

Faktor	Intermediate table	Incremental accumulation
Jumlah partisi yang dipindai per eksekusi	30 (tetapi dengan baris yang lebih kecil)	1
Kompleksitas implementasi	Rendah — hanya perlu satu scheduling node tambahan per hari	Lebih tinggi — memerlukan logika penggabungan dan manajemen dependensi
Kesegaran Data	Harian	Harian
Kesesuaian terbaik	Volume log harian besar dengan kunci berdimensi tinggi	Kueri rolling-window berulang pada data historis yang stabil

Kedua pendekatan memindahkan komputasi mahal dari waktu kueri ke waktu ingestion. Intermediate table melakukannya per hari; incremental accumulation melakukannya sepanjang seluruh data historis.