SET cz.sql.start_date = '2024-01-01';
SET cz.sql.end_date = '2024-01-31';
SELECT *
FROM orders
WHERE order_date BETWEEN SESSION_CONFIGS()['cz.sql.start_date']
AND SESSION_CONFIGS()['cz.sql.end_date'];
需要非字符串类型时显式转换：
SET cz.sql.top_n = '10';
SELECT *
FROM products
ORDER BY sales DESC
LIMIT CAST(SESSION_CONFIGS()['cz.sql.top_n'] AS INT);
-- 在 WHERE 中使用
SELECT * FROM t WHERE CURRENT_SETTING('cz.sql.timezone') IS NOT NULL;
-- 在 CONCAT 中拼接
SELECT CONCAT('tz=', CURRENT_SETTING('cz.sql.timezone'));
-- 返回: 'tz=Asia/Shanghai'
-- 确认返回类型
SELECT typeof(CURRENT_SETTING('cz.sql.timezone')), typeof(SESSION_CONFIGS());
-- 返回: 'string', 'map<string,string>'
动态表参数化（dt.args）
SESSION_CONFIGS()['dt.args.<key>']
SESSION_CONFIGS()['dt.args.<key>']
是动态表的核心参数化机制。在动态表的
AS
AS
查询中引用
dt.args.*
dt.args.*
参数，每次 REFRESH 时通过
SET dt.args.<key>=<value>
SET dt.args.<key>=<value>
传入不同的值，实现按分区按需刷新，无需为每个参数值创建独立的动态表。
基本用法：
-- 第一步：定义动态表，用 SESSION_CONFIGS()['dt.args.<key>'] 作为参数占位符
CREATE DYNAMIC TABLE dt_daily
REFRESH ON DEMAND
AS
SELECT region, order_date, SUM(amount) AS revenue
FROM orders
WHERE order_date = SESSION_CONFIGS()['dt.args.date'];
-- 第二步：刷新时在同一 SQL 中先 SET 再 REFRESH（分号分隔）
SET dt.args.date = '2024-01-01';
REFRESH DYNAMIC TABLE dt_daily;
-- 换一个日期刷新
SET dt.args.date = '2024-01-02';
REFRESH DYNAMIC TABLE dt_daily;
分区动态表按分区刷新：
dt.args.*
dt.args.*
与
PARTITIONED BY
PARTITIONED BY
搭配使用，每次只刷新目标分区：
CREATE DYNAMIC TABLE dt_orders_by_date
REFRESH ON DEMAND
PARTITIONED BY (order_date)
AS
SELECT region,
order_date,
SUM(amount) AS revenue,
COUNT(*) AS order_cnt
FROM orders
WHERE order_date = SESSION_CONFIGS()['dt.args.date'];
-- SET 和 PARTITION 子句的值必须一致，否则报错
SET dt.args.date = '2024-01-01';
REFRESH DYNAMIC TABLE dt_orders_by_date PARTITION (order_date = '2024-01-01');
SET dt.args.date = '2024-01-02';
REFRESH DYNAMIC TABLE dt_orders_by_date PARTITION (order_date = '2024-01-02');
多参数：
CREATE DYNAMIC TABLE dt_hourly
REFRESH ON DEMAND
PARTITIONED BY (event_date, event_hour)
AS
SELECT event_date,
event_hour,
COUNT(*) AS event_cnt,
SESSION_CONFIGS()['dt.args.date'] AS loaded_date,
CAST(SESSION_CONFIGS()['dt.args.hour'] AS INT) AS loaded_hour
FROM events
WHERE event_date = SESSION_CONFIGS()['dt.args.date']
AND event_hour = CAST(SESSION_CONFIGS()['dt.args.hour'] AS INT);
-- 同时绑定多个参数
SET dt.args.date = '2024-01-01';
SET dt.args.hour = '9';
REFRESH DYNAMIC TABLE dt_hourly PARTITION (event_date = '2024-01-01', event_hour = 9);
在 JOIN 条件中使用：
CREATE DYNAMIC TABLE dt_joined
REFRESH ON DEMAND
AS
SELECT a.id, a.value, b.label
FROM fact_table a
JOIN dim_table b
ON a.id = b.id
AND a.dt = SESSION_CONFIGS()['dt.args.date']
AND b.dt = SESSION_CONFIGS()['dt.args.date'];
类型转换：
dt.args.*
dt.args.*
的值始终是
STRING
STRING
类型，需要其他类型时显式转换：
CAST(SESSION_CONFIGS()['dt.args.hour'] AS INT)
CAST(SESSION_CONFIGS()['dt.args.date'] AS DATE)
CAST(SESSION_CONFIGS()['dt.args.amount'] AS DECIMAL(12,2))
