Iceberg와 Materialized View를 활용한 저지연 쿼리 구현 (실시간 거래 리포트 사례)

CREATE MATERIALIZED VIEW kr_summary_bi_order_stat_all_view
PARTITION BY (p_ymd)
DISTRIBUTED BY RANDOM
REFRESH ASYNC START('2025-07-01 00:00:00') EVERY(INTERVAL 2 MINUTE)
PROPERTIES (
  'replicated_storage' = 'true',
  'replication_num' = '3',
  'partition_retention_condition' = 'p_ymd >= CURRENT_DATE() - INTERVAL 2 MONTH',
  'storage_medium' = 'HDD'
)
AS
SELECT
  t1.p_ymd,
  t1.catg_id,
  SUM(t1.order_amt) AS total_amt,
  COUNT(t1.order_no) AS order_cnt
FROM iceberg_catalog.hms_kr.summary_bi_order_statistics AS t1
LEFT JOIN iceberg_catalog.hms_kr.dw__zz.sdm__meta_shp_category_mtr_mst AS t2
  ON t1.catg_id = t2.catg_id
GROUP BY t1.p_ymd, t1.catg_id;