体验 Serverless StarRocks × Paimon (DLF) 查询 TPC-DS 标准库性能

CREATE EXTERNAL CATALOG `dlf_samples`
PROPERTIES (
'type' = 'paimon',
'uri' = 'http://cn-hangzhou-vpc.dlf.aliyuncs.com',
'paimon.catalog.type' = 'rest',
'paimon.catalog.warehouse' = 'dlf_samples',
'token.provider' = 'dlf'
);

show property;

-- TPC-DS Query 3: 客户地域分布分析（性能压测）
SELECT 
    dt.d_year,
    ca.ca_state,
    ca.ca_city,
    COUNT(*) AS customer_cnt
FROM 
    dlf_samples.tpcds_paimon_sf100.customer c
JOIN 
    dlf_samples.tpcds_paimon_sf100.customer_address ca 
    ON c.c_current_addr_sk = ca.ca_address_sk
JOIN 
    dlf_samples.tpcds_paimon_sf100.store_sales ss 
    ON c.c_customer_sk = ss.ss_customer_sk
JOIN 
    dlf_samples.tpcds_paimon_sf100.date_dim dt 
    ON ss.ss_sold_date_sk = dt.d_date_sk
WHERE 
    ca.ca_state IN ('IL', 'IN', 'KS')  -- 选择高数据量的州
    AND dt.d_year BETWEEN 2000 AND 2002  -- 覆盖多个分区
GROUP BY 
    dt.d_year,
    ca.ca_state,
    ca.ca_city
ORDER BY 
    dt.d_year,
    ca.ca_state,
    ca.ca_city
LIMIT 100;

CREATE DATABASE IF NOT EXISTS sr_demo_db;
CREATE TABLE IF NOT EXISTS sr_demo_db.customer_geo_summary (
    ca_state STRING,
    ca_city STRING,
    d_year INT,
    customer_cnt BIGINT,
    update_time DATETIME
)
PRIMARY KEY (ca_state, ca_city, d_year)
PARTITION BY (d_year)
DISTRIBUTED BY HASH(ca_city) BUCKETS 8;

INSERT INTO sr_demo_db.customer_geo_summary
SELECT 
    ca.ca_state,
    ca.ca_city,
    COUNT(*) AS customer_cnt,
    NOW() AS update_time,
    dt.d_year
FROM 
    dlf_samples.tpcds_paimon_sf100.customer c
JOIN 
    dlf_samples.tpcds_paimon_sf100.customer_address ca 
    ON c.c_current_addr_sk = ca.ca_address_sk
JOIN 
    dlf_samples.tpcds_paimon_sf100.store_sales ss 
    ON c.c_customer_sk = ss.ss_customer_sk
JOIN 
    dlf_samples.tpcds_paimon_sf100.date_dim dt 
    ON ss.ss_sold_date_sk = dt.d_date_sk
WHERE 
    ca.ca_state IN ('IL', 'IN', 'KS')
    AND dt.d_year BETWEEN 2000 AND 2002
    AND ca.ca_state IS NOT NULL
    AND ca.ca_city IS NOT NULL
    AND dt.d_year IS NOT NULL
GROUP BY 
    ca.ca_state,
    ca.ca_city,
    dt.d_year;

SELECT * FROM sr_demo_db.customer_geo_summary LIMIT 10;
SELECT COUNT(*) FROM sr_demo_db.customer_geo_summary;

--- 使用StarRocks物化视图，自动化构建数据仓库DWD层
--- 说明：此处为了简化逻辑，仅直接将ODS层数据（即customer_geo_summary）转换为DWD层。
-- 删除已存在的物化视图（如果存在）
DROP MATERIALIZED VIEW IF EXISTS sr_demo_db.dwd_mv_customer_geo_summary;
-- 创建新的物化视图
CREATE MATERIALIZED VIEW IF NOT EXISTS sr_demo_db.dwd_mv_customer_geo_summary
DISTRIBUTED BY HASH(ca_city) BUCKETS 8
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT 
    ca_state,
    ca_city,
    customer_cnt,
    update_time,
    d_year
FROM sr_demo_db.customer_geo_summary;

-- 校验上面步骤的数据加工结果
SELECT * FROM (
    SELECT
        COUNT(1) AS cnt,
        'dwd_mv_customer_geo_summary' AS tb
    FROM
        sr_demo_db.dwd_mv_customer_geo_summary
) t;

--- 使用StarRocks物化视图，自动化构建数据仓库ADS层
-- 1. 创建 ADS_MV_CUSTOMER_RETENTION (客户年度汇总)
DROP MATERIALIZED VIEW IF EXISTS sr_demo_db.ADS_MV_CUSTOMER_RETENTION;
CREATE MATERIALIZED VIEW IF NOT EXISTS sr_demo_db.ADS_MV_CUSTOMER_RETENTION
DISTRIBUTED BY HASH(d_year) BUCKETS 4
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT
    d_year,
    SUM(customer_cnt) AS total_customers
FROM sr_demo_db.dwd_mv_customer_geo_summary
GROUP BY d_year;
-- 2. ADS_MV_CUSTOMER_GEOGRAPHIC_DISTRIBUTION (客户地理分布 - 按州)
DROP MATERIALIZED VIEW IF EXISTS sr_demo_db.ADS_MV_CUSTOMER_GEOGRAPHIC_DISTRIBUTION;
CREATE MATERIALIZED VIEW IF NOT EXISTS sr_demo_db.ADS_MV_CUSTOMER_GEOGRAPHIC_DISTRIBUTION
DISTRIBUTED BY HASH(ca_state) BUCKETS 8
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT
    ca_state,
    SUM(customer_cnt) AS total_customers
FROM sr_demo_db.dwd_mv_customer_geo_summary
GROUP BY ca_state;
-- 3. ADS_MV_CUSTOMER_CITY_DISTRIBUTION (城市分布详情)
DROP MATERIALIZED VIEW IF EXISTS sr_demo_db.ADS_MV_CUSTOMER_CITY_DISTRIBUTION;
CREATE MATERIALIZED VIEW IF NOT EXISTS sr_demo_db.ADS_MV_CUSTOMER_CITY_DISTRIBUTION
DISTRIBUTED BY HASH(ca_city) BUCKETS 8
REFRESH ASYNC EVERY(INTERVAL 1 HOUR)
AS
SELECT
    ca_city,
    SUM(customer_cnt) AS total_customers
FROM sr_demo_db.dwd_mv_customer_geo_summary
GROUP BY ca_city;

-- 校验上面步骤的数据加工结果
SELECT * FROM (
    SELECT COUNT(1) AS row_count, 'ADS_MV_CUSTOMER_RETENTION' AS tb
    FROM sr_demo_db.ADS_MV_CUSTOMER_RETENTION
    UNION ALL
    SELECT COUNT(1) AS row_count, 'ADS_MV_CUSTOMER_GEOGRAPHIC_DISTRIBUTION' AS tb
    FROM sr_demo_db.ADS_MV_CUSTOMER_GEOGRAPHIC_DISTRIBUTION
    UNION ALL
    SELECT COUNT(1) AS row_count, 'ADS_MV_CUSTOMER_CITY_DISTRIBUTION' AS tb
    FROM sr_demo_db.ADS_MV_CUSTOMER_CITY_DISTRIBUTION
) t;

SELECT 
    ca_state AS 州,
    total_customers AS 客户数
FROM sr_demo_db.ADS_MV_CUSTOMER_GEOGRAPHIC_DISTRIBUTION
WHERE ca_state IS NOT NULL
ORDER BY total_customers DESC;

SELECT 
    ca_city AS 城市,
    total_customers AS 客户数
FROM sr_demo_db.ADS_MV_CUSTOMER_CITY_DISTRIBUTION
WHERE ca_city IS NOT NULL
ORDER BY total_customers DESC