-- 第一步：通过 SQL PUT 将本地 CSV 文件上传到 User Volume
PUT '/path/to/your/data.csv' TO USER VOLUME FILE 'data.csv';
-- 第二步：从 User Volume COPY INTO 表
COPY INTO best_practice_insurance_dw.doc_ods_insurance_policy
FROM USER VOLUME
USING csv
OPTIONS('header'='true', 'sep'=',', 'nullValue'='')
FILES ('data.csv');
也可直接内联插入小批量测试数据（不需要 CSV 文件）：
写入后验证行数：
SELECT COUNT(*) AS ods_row_count
FROM best_practice_insurance_dw.doc_ods_insurance_policy;
-- 创建脱敏函数
CREATE OR REPLACE FUNCTION best_practice_insurance_dw.mask_customer_age(age INT)
RETURNS INT
AS CASE
WHEN current_user() IN ('privileged_user') THEN age -- 替换为实际获授权的用户名
ELSE CAST(FLOOR(CAST(age AS DOUBLE) / 10.0) * 10 AS INT)
END;
-- 绑定到 customer_age 列
ALTER TABLE best_practice_insurance_dw.doc_ods_insurance_policy
CHANGE COLUMN customer_age
SET MASK best_practice_insurance_dw.mask_customer_age;
💡 提示：将
'privileged_user'
'privileged_user'
替换为实际需要查看明文数据的用户名。Column Masking 通过
current_user()
current_user()
函数匹配当前连接的用户名，需将所有授权用户名显式列在
IN()
IN()
列表中。
验证绑定效果（管理员账号看原始精确年龄）：
SELECT policy_id, customer_age
FROM best_practice_insurance_dw.doc_ods_insurance_policy
WHERE policy_id IN ('POL007194', 'POL016745', 'POL045360')
ORDER BY policy_id;
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_insurance_dw.doc_dwd_policy_lifecycle
AS
SELECT
policy_id,
subscription_length,
vehicle_age,
customer_age,
region_code,
region_density,
segment,
model,
fuel_type,
engine_type,
airbags,
displacement,
cylinder,
transmission_type,
ncap_rating,
claim_status,
-- 客户年龄分段
CASE
WHEN customer_age < 30 THEN 'Young (<30)'
WHEN customer_age < 45 THEN 'Middle (30-44)'
WHEN customer_age < 60 THEN 'Senior (45-59)'
ELSE 'Elderly (60+)'
END AS age_group,
-- 车龄分段
CASE
WHEN vehicle_age < 1 THEN 'New (<1yr)'
WHEN vehicle_age < 3 THEN 'Recent (1-3yr)'
WHEN vehicle_age < 5 THEN 'Mid (3-5yr)'
ELSE 'Old (5+yr)'
END AS vehicle_age_group,
-- 保单年限分段
CASE
WHEN subscription_length < 3 THEN 'Short (<3yr)'
WHEN subscription_length < 7 THEN 'Medium (3-7yr)'
ELSE 'Long (7+yr)'
END AS policy_tenure_group,
-- 高风险标记：车龄超5年 或 NCAP评分为0
CASE WHEN vehicle_age >= 5 OR ncap_rating = 0 THEN 1 ELSE 0 END AS is_high_risk
FROM best_practice_insurance_dw.doc_ods_insurance_policy;
⚠️ 注意：DDL 中不写
REFRESH INTERVAL
REFRESH INTERVAL
，刷新调度通过 Studio Task 管理（见下文"调度刷新任务"节）。
手动触发首次刷新：
REFRESH DYNAMIC TABLE best_practice_insurance_dw.doc_dwd_policy_lifecycle;
SELECT COUNT(*) AS dwd_count
FROM best_practice_insurance_dw.doc_dwd_policy_lifecycle;
dwd_count
---------
100
分析示例：年龄段与车龄段交叉理赔率
SELECT
age_group,
vehicle_age_group,
COUNT(*) AS policy_count,
SUM(claim_status) AS claim_count
FROM best_practice_insurance_dw.doc_dwd_policy_lifecycle
GROUP BY age_group, vehicle_age_group
ORDER BY claim_count DESC;
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_insurance_dw.doc_dws_product_region_daily
AS
SELECT
segment,
fuel_type,
region_code,
age_group,
COUNT(*) AS policy_count,
SUM(claim_status) AS claim_count,
ROUND(SUM(claim_status) * 100.0 / COUNT(*), 4) AS claim_rate_pct,
ROUND(AVG(vehicle_age), 2) AS avg_vehicle_age,
ROUND(AVG(customer_age), 2) AS avg_customer_age,
ROUND(AVG(subscription_length), 2) AS avg_subscription_length,
SUM(is_high_risk) AS high_risk_count,
ROUND(SUM(is_high_risk) * 100.0 / COUNT(*), 4) AS high_risk_rate_pct
FROM best_practice_insurance_dw.doc_dwd_policy_lifecycle
GROUP BY segment, fuel_type, region_code, age_group;
手动触发首次刷新并验证：
REFRESH DYNAMIC TABLE best_practice_insurance_dw.doc_dws_product_region_daily;
SELECT COUNT(*) AS dws_count
FROM best_practice_insurance_dw.doc_dws_product_region_daily;
dws_count
---------
59
查看有理赔记录的险种-地区组合：
SELECT segment, fuel_type, region_code,
policy_count, claim_count, claim_rate_pct, high_risk_count
FROM best_practice_insurance_dw.doc_dws_product_region_daily
WHERE claim_count > 0
ORDER BY claim_rate_pct DESC;
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_insurance_dw.doc_ads_regulatory_report
AS
SELECT
segment AS product_type,
fuel_type,
COUNT(*) AS total_policies,
SUM(claim_status) AS total_claims,
ROUND(SUM(claim_status) * 100.0 / COUNT(*), 4) AS claim_rate_pct,
COUNT(DISTINCT region_code) AS covered_regions,
SUM(is_high_risk) AS high_risk_policies,
ROUND(SUM(is_high_risk) * 100.0 / COUNT(*), 4) AS high_risk_rate_pct,
ROUND(AVG(customer_age), 2) AS avg_customer_age,
ROUND(AVG(vehicle_age), 2) AS avg_vehicle_age,
ROUND(AVG(subscription_length), 2) AS avg_policy_tenure,
ROUND(AVG(ncap_rating), 2) AS avg_safety_rating
FROM best_practice_insurance_dw.doc_dwd_policy_lifecycle
GROUP BY segment, fuel_type;
REFRESH DYNAMIC TABLE best_practice_insurance_dw.doc_ads_regulatory_report;
SELECT product_type, fuel_type, total_policies, total_claims,
claim_rate_pct, covered_regions, high_risk_policies, avg_safety_rating
FROM best_practice_insurance_dw.doc_ads_regulatory_report
ORDER BY total_policies DESC;
CREATE DYNAMIC TABLE IF NOT EXISTS best_practice_insurance_dw.doc_ads_region_claim_analysis
AS
SELECT
region_code,
COUNT(*) AS total_policies,
SUM(claim_status) AS total_claims,
ROUND(SUM(claim_status) * 100.0 / COUNT(*), 4) AS claim_rate_pct,
SUM(is_high_risk) AS high_risk_count,
ROUND(AVG(subscription_length), 2) AS avg_policy_tenure,
ROUND(AVG(vehicle_age), 2) AS avg_vehicle_age,
SUM(CASE WHEN ncap_rating >= 3 THEN 1 ELSE 0 END) AS high_safety_policies,
ROUND(AVG(ncap_rating), 2) AS avg_ncap_rating
FROM best_practice_insurance_dw.doc_dwd_policy_lifecycle
GROUP BY region_code;
REFRESH DYNAMIC TABLE best_practice_insurance_dw.doc_ads_region_claim_analysis;
SELECT region_code, total_policies, total_claims, claim_rate_pct,
high_risk_count, avg_ncap_rating
FROM best_practice_insurance_dw.doc_ads_region_claim_analysis
ORDER BY total_policies DESC
LIMIT 10;
SELECT
age_group,
policy_tenure_group,
COUNT(*) AS policy_count,
SUM(claim_status) AS claim_count,
ROUND(SUM(claim_status) * 100.0 / COUNT(*), 4) AS claim_rate_pct
FROM best_practice_insurance_dw.doc_dwd_policy_lifecycle
GROUP BY age_group, policy_tenure_group
ORDER BY claim_rate_pct DESC;
-- 查询数据写入40条时的历史快照
SELECT COUNT(*) AS historical_count
FROM best_practice_insurance_dw.doc_ods_insurance_policy
TIMESTAMP AS OF '2026-06-06T22:48:26.136';
historical_count
----------------
40
月末对账典型用法：
-- 比较本月末与上月末的理赔率差异
WITH current_month AS (
SELECT
segment,
SUM(claim_status) AS claims,
COUNT(*) AS policies
FROM best_practice_insurance_dw.doc_ods_insurance_policy
GROUP BY segment
),
last_month AS (
SELECT
segment,
SUM(claim_status) AS claims,
COUNT(*) AS policies
FROM best_practice_insurance_dw.doc_ods_insurance_policy
TIMESTAMP AS OF '2026-06-06T22:48:26.136' -- 替换为上月末时间点
GROUP BY segment
)
SELECT
c.segment,
c.policies AS curr_policies,
c.claims AS curr_claims,
l.policies AS prev_policies,
l.claims AS prev_claims,
ROUND((c.claims * 1.0 / c.policies - l.claims * 1.0 / l.policies) * 100, 4) AS claim_rate_delta_pct
FROM current_month c
LEFT JOIN last_month l ON c.segment = l.segment
ORDER BY claim_rate_delta_pct DESC;
💡 提示：
TIMESTAMP AS OF
TIMESTAMP AS OF
仅接受字面量常量，不支持
NOW() - INTERVAL 1 MONTH
NOW() - INTERVAL 1 MONTH
这类动态表达式。生产环境中可将时间点参数化，通过 Studio Task 传入
${bizdate}
${bizdate}
等调度变量。
RBAC：细粒度权限分级
保险核心业务各部门对数据的访问权限差异显著：
角色
访问范围
典型需求
精算部（
role_actuarial
role_actuarial
）
ODS + DWD + DWS，可见
customer_age
customer_age
（原始值）
费率精算、风险建模
理赔部（
role_claims
role_claims
）
ODS + DWD，可见保单全字段，不可修改
理赔处理、欺诈核查
合规部（
role_compliance
role_compliance
）
ADS 只读，不可见原始
customer_age
customer_age
监管报表生成、合规审查
IT 运维（
role_ops
role_ops
）
Schema 级别 DDL 权限，不可查询数据
表结构管理、索引维护
创建角色并分配权限的示例：
-- 创建角色
CREATE ROLE IF NOT EXISTS role_compliance;
CREATE ROLE IF NOT EXISTS role_actuarial;
CREATE ROLE IF NOT EXISTS role_claims;
-- 合规部：仅授权 ADS 层两张动态报表表的 SELECT 权限
GRANT SELECT ON DYNAMIC TABLE best_practice_insurance_dw.doc_ads_regulatory_report TO ROLE role_compliance;
GRANT SELECT ON DYNAMIC TABLE best_practice_insurance_dw.doc_ads_region_claim_analysis TO ROLE role_compliance;
-- 精算部：授权 ODS + DWD + DWS 的 SELECT，同时加入可见原始 customer_age 的白名单
GRANT SELECT ON TABLE best_practice_insurance_dw.doc_ods_insurance_policy TO ROLE role_actuarial;
GRANT SELECT ON TABLE best_practice_insurance_dw.doc_dwd_policy_lifecycle TO ROLE role_actuarial;
GRANT SELECT ON TABLE best_practice_insurance_dw.doc_dws_product_region_daily TO ROLE role_actuarial;
-- 理赔部：授权 ODS + DWD 的 SELECT
GRANT SELECT ON TABLE best_practice_insurance_dw.doc_ods_insurance_policy TO ROLE role_claims;
GRANT SELECT ON TABLE best_practice_insurance_dw.doc_dwd_policy_lifecycle TO ROLE role_claims;
