AnalyticDB:Buat tampilan yang di-materialisasi

Aktifkan binary logging

Refresh inkremental bergantung pada binary logging untuk melacak perubahan di tabel dasar. Aktifkan di tingkat kluster dan untuk setiap tabel dasar:

SET ADB_CONFIG BINLOG_ENABLE=true;
ALTER TABLE customer binlog=true;
ALTER TABLE sales binlog=true;

Jika aktivasi binary logging gagal, lihat Can not create FAST materialized view.

Buat tampilan yang di-materialisasi fast tabel tunggal

Contoh berikut membuat tampilan yang di-materialisasi fast bernama sales_mv_incre pada tabel sales dengan interval auto-refresh 3 menit:

CREATE MATERIALIZED VIEW sales_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
  sale_id,
  SUM(price * quantity) AS price
FROM sales
GROUP BY sale_id;

Buat tampilan yang di-materialisasi fast multi-tabel

Pada kluster yang menjalankan V3.2.1.0 atau lebih baru, tampilan yang di-materialisasi fast dapat menggabungkan beberapa tabel. Contoh berikut menggabungkan customer dan sales:

CREATE MATERIALIZED VIEW join_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
  customer.customer_id,
  SUM(sales.price) AS price
FROM customer
INNER JOIN (SELECT customer_id, price FROM sales) sales
  ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;

Untuk sintaks lengkap CREATE MATERIALIZED VIEW, lihat CREATE MATERIALIZED VIEW.

Aturan kolom SELECT

Kolom dalam daftar SELECT harus mengikuti aturan berikut:

• Dengan GROUP BY dan fungsi agregat: Sertakan semua kolom GROUP BY dalam daftar SELECT.

  Lihat contoh

  Contoh benar (semua kolom GROUP BY disertakan):

  CREATE MATERIALIZED VIEW demo_mv1
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,    -- kolom GROUP BY
    sale_date,  -- kolom GROUP BY
    max(quantity) AS max,  -- kolom ekspresi harus memiliki alias
    sum(price) AS sum
  FROM sales
  GROUP BY sale_id, sale_date;

  Salah (kolom GROUP BY sale_date tidak disertakan):

  CREATE MATERIALIZED VIEW false_mv1
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales
  GROUP BY sale_id, sale_date;  -- sale_date ada di GROUP BY tetapi tidak di SELECT

• Dengan fungsi agregat tanpa GROUP BY: Daftar SELECT hanya boleh berisi kolom agregat, atau konstanta dan kolom agregat.

  Lihat contoh

  -- Hanya kolom agregat
  CREATE MATERIALIZED VIEW demo_mv2
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales;
  
  -- Konstanta ditambah kolom agregat (konstanta menjadi kunci primer)
  CREATE MATERIALIZED VIEW demo_mv3
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    1 AS pk,
    max(quantity) AS max,
    sum(price) AS sum
  FROM sales;

• Tanpa agregasi: Sertakan semua kolom kunci primer dari tabel dasar.

  Lihat contoh

  -- Kunci primer tunggal
  CREATE MATERIALIZED VIEW demo_mv4
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,   -- kolom kunci primer dari tabel dasar sales
    quantity
  FROM sales;
  
  -- Kunci primer gabungan: PRIMARY KEY(sale_id, sale_date)
  CREATE MATERIALIZED VIEW demo_mv5
  REFRESH FAST ON DEMAND
   NEXT now() + INTERVAL 3 minute
  AS
  SELECT
    sale_id,    -- kolom kunci primer pertama
    sale_date,  -- kolom kunci primer kedua
    quantity
  FROM sales1;

• Kueri UNION ALL: Setiap cabang harus menghasilkan kolom bernama union_all_marker dengan nilai konstan berbeda per cabang. Sertakan semua kolom kunci primer tabel dasar. Kunci primer tampilan yang di-materialisasi harus mencakup kolom kunci primer tabel dasar dan union_all_marker.

  CREATE MATERIALIZED VIEW demo_union_all_mv (PRIMARY KEY(id, union_all_marker))
  REFRESH FAST NEXT now() + INTERVAL 5 minute
  AS
  SELECT customer_id AS id, "customer" AS union_all_marker
  FROM customer
  UNION ALL
  SELECT sale_id AS id, "sales" AS union_all_marker
  FROM sales;

• Kolom ekspresi: Semua kolom ekspresi dalam daftar SELECT harus memiliki alias, misalnya SUM(price) AS total_price.

Batasan umum

Batasan berikut berlaku untuk semua tampilan yang di-materialisasi.

• Anda tidak dapat menjalankan INSERT, DELETE, atau UPDATE pada tampilan yang di-materialisasi.

• Anda tidak dapat menghapus atau mengganti nama tabel dasar atau kolomnya selama tampilan yang di-materialisasi masih mereferensikannya. Hapus tampilan yang di-materialisasi terlebih dahulu, lalu ubah tabel dasar.

• Maksimum tampilan yang di-materialisasi per kluster:

  • V3.1.4.7 atau lebih baru: 64

  • Lebih awal dari V3.1.4.7: 8

Untuk menaikkan kuota, hubungi dukungan teknis.

Batasan pada tampilan yang di-materialisasi dengan complete refresh

Ketika Anda menambah atau menghapus node cadangan, job asinkron dinonaktifkan. Karena complete refresh merupakan job asinkron, refresh ini tidak dapat dijalankan selama penskalaan node. Fast refresh tidak terpengaruh.

Batasan pada tampilan yang di-materialisasi fast (incremental)

Lihat Incremental MV query constraints untuk daftar lengkap pembatasan SQL.

Pemicu refresh: Hanya auto-refresh terjadwal yang didukung. Interval harus antara 5 detik hingga 5 menit.

Bagaimana cara menyimpan hanya data satu tahun terakhir dalam tampilan yang di-materialisasi?

Gunakan kolom tanggal sebagai kunci partisi (PARTITION BY) dan atur nilai LIFECYCLE untuk membatasi jumlah partisi yang disimpan. Untuk tampilan yang dipartisi per hari, atur LIFECYCLE 365 untuk menyimpan 365 partisi terbaru (satu tahun).

Contoh: tabel sales menerima catatan baru setiap hari, dipartisi berdasarkan sale_date:

CREATE MATERIALIZED VIEW sales_mv_lifecycle
PARTITION BY VALUE(DATE_FORMAT(sale_date, '%Y%m%d')) LIFECYCLE 365
REFRESH FAST NEXT now() + INTERVAL 100 second
AS
SELECT
  sale_date,
  SUM(price * quantity) AS price
FROM sales
GROUP BY sale_date;

Kesalahan eksekusi kueri: Tidak dapat membuat tampilan FAST yang di-materialisasi, karena *demotable* tidak mendukung pengambilan data inkremental

Binary logging tidak diaktifkan untuk tabel dasar demotable. Aktifkan dengan:

ALTER TABLE demotable binlog=true;

Jika Anda melihat XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now, tabel dasar menggunakan mesin XUANWU_V2 dan kluster menjalankan versi kernel sebelum V3.2.6.0. XUANWU_V2 mendukung binary logging mulai versi kernel V3.2.6.0. Upgrade kluster ke V3.2.6.0 atau lebih baru, lalu jalankan kembali ALTER TABLE demotable binlog=true; untuk mengaktifkan binary logging pada tabel dasar.

Error eksekusi kueri: PRIMARY KEY *id* must output to MV

Tampilan yang di-materialisasi fast Anda menggunakan kueri non-agregat tanpa GROUP BY. Dalam kasus ini, daftar SELECT harus menyertakan semua kolom kunci primer dari tabel dasar.

Salah (tidak menyertakan sale_id, yang merupakan kunci primer dari sales):

CREATE MATERIALIZED VIEW wrong_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT product_id, price
FROM sales;

Tambahkan kolom kunci primer:

CREATE MATERIALIZED VIEW correct_example1
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Error eksekusi kueri: MV PRIMARY KEY must be equal to base table PRIMARY KEY

Tampilan yang di-materialisasi fast Anda menggunakan kueri non-agregat tanpa GROUP BY, dan definisi kunci primer tampilan mencakup kolom yang bukan bagian dari kunci primer tabel dasar.

Salah (product_id bukan kolom kunci primer dari sales):

CREATE MATERIALIZED VIEW wrong_example2
(PRIMARY KEY(sale_id, product_id))
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Hapus kolom non-kunci-primer dari definisi kunci primer:

CREATE MATERIALIZED VIEW correct_example2
(PRIMARY KEY(sale_id))
REFRESH FAST ON DEMAND
 NEXT now() + interval 200 second
AS
SELECT sale_id, product_id, price
FROM sales;

Error eksekusi kueri: FAST materialized view must define PRIMARY KEY

Error ini memiliki dua kemungkinan penyebab:

• Tidak ada kunci primer yang valid didefinisikan. Perbarui definisi tampilan yang di-materialisasi agar memenuhi aturan berikut:

  • Kueri agregat bergrup (dengan GROUP BY): kunci primer harus berupa kolom GROUP BY (misalnya, jika GROUP BY a, b, atur PRIMARY KEY(a, b)).

  • Kueri agregat tidak bergrup (tanpa GROUP BY): kunci primer harus berupa konstanta.

  • Kueri non-agregat: kunci primer harus persis sama dengan kunci primer tabel dasar (misalnya, jika tabel dasar memiliki PRIMARY KEY(sale_id, sale_date), tampilan juga harus memiliki PRIMARY KEY(sale_id, sale_date)).

• Fungsi diterapkan pada kolom kunci primer. Hapus fungsi dari kolom kunci primer dalam kueri.

Error eksekusi kueri: The join graph is not supported

Kolom join memiliki tipe data yang tidak sesuai. Misalnya, jika customer.id dan sales.id memiliki tipe berbeda, join akan gagal. Samakan tipe datanya dengan:

ALTER TABLE tablename MODIFY COLUMN columnname newtype;

Untuk informasi lebih lanjut, lihat Change the data type of a column.

Kesalahan eksekusi kueri: Tidak dapat menggunakan index join untuk menyegarkan fast MV ini

Kolom join tidak memiliki indeks. Tambahkan indeks pada setiap kolom join:

ALTER TABLE tablename ADD KEY idx_name(columnname);

Untuk informasi lebih lanjut, lihat Create an index.

Error eksekusi kueri: Query exceeded reserved memory limit

Kueri melebihi batas memori per node. Gunakan fitur SQL diagnostics untuk mengidentifikasi tahapan dan operator berkonsumsi memori tinggi (Aggregation, TopN, Window, dan Join adalah penyebab umum), lalu optimalkan operator tersebut. Lihat juga Memory metrics dan Use stage and task details to analyze queries.