DuckDB Delta + Unity Catalog 完全指南：写入、时间旅行与数据治理一体化

-- 附加 Delta 表
ATTACH './path/to/my_delta_table' AS my_table (TYPE delta);

-- 单行插入
INSERT INTO my_table
VALUES ('Question 2', 2), ('The Answer', 42);

-- 批量插入（从查询结果）
INSERT INTO my_table
FROM (SELECT text || ' (copy)', code + 100 FROM my_table);

BEGIN;

INSERT INTO my_table VALUES ('Alice', 30);
INSERT INTO my_table VALUES ('Bob', 25);
INSERT INTO my_table VALUES ('Charlie', 35);

COMMIT;
-- 以上三条 INSERT 被合并为一次 Delta 提交

-- 正常附加表
ATTACH './path/to/my_table' AS my_table (TYPE delta);

-- 查询版本 0（初始状态）
SELECT count() FROM my_table AT (VERSION => 0);
-- 返回 1 行（只有 Question 1）

-- 查询版本 1（第一次插入后）
SELECT count() FROM my_table AT (VERSION => 1);
-- 返回 3 行

-- 查询最新版本
SELECT count() FROM my_table;
-- 返回 6 行

-- 始终指向版本 1，不受后续写入影响
ATTACH './path/to/my_table' AS my_table_v1
    (TYPE delta, VERSION 1);

SELECT count() FROM my_table_v1;
-- 始终返回 3

-- 附加时锁定快照（PIN_SNAPSHOT）
ATTACH './path/to/my_table' AS my_table_pinned
    (TYPE delta, PIN_SNAPSHOT);

SELECT count() FROM my_table_pinned;
-- 锁定附加时的最新版本

-- 假设你的交易表每天接收增量数据
-- 月末审计时，可以精确回溯到任意日期的数据状态

ATTACH './transactions' AS txn (TYPE delta);

-- 查看月初 vs 月末的差异
SELECT 
    (SELECT SUM(amount) FROM txn AT (VERSION => 100)) AS month_start_total,
    (SELECT SUM(amount) FROM txn) AS current_total;

# 克隆示例仓库
git clone https://github.com/benfleis/duckdb-unitycatalog-playground.git
cd duckdb-unitycatalog-playground

# 构建并启动（会自动创建 schema 和测试表）
docker build -t duckdb-uc-playground .
docker run -p 8080:8080 duckdb-uc-playground

-- 加载 Unity Catalog 扩展
LOAD unity_catalog;

-- 创建连接到 Unity Catalog 服务器的 Secret
CREATE SECRET (
    TYPE     unity_catalog,
    TOKEN    'demo-ignored-token',
    ENDPOINT 'http://localhost:8080'
);

-- 附加为 DuckDB 中的目录
ATTACH 'unity' AS my_catalog
    (TYPE unity_catalog, DEFAULT_SCHEMA 'my_schema');

-- 直接查询 Unity Catalog 管理的表
SELECT name, age, adopted 
FROM my_catalog.pets 
ORDER BY name;

CREATE SECRET (
    TYPE     unity_catalog,
    TOKEN    'databricks-pat-token',  -- 个人访问令牌
    ENDPOINT 'https://your-workspace.cloud.databricks.com/'
);

ATTACH 'unity' AS dbx_catalog
    (TYPE unity_catalog, DEFAULT_SCHEMA 'analytics');

SELECT * FROM dbx_catalog.default.sales_data;

-- ============================================
-- 第一步：通过 Delta 扩展写入数据
-- ============================================
ATTACH './customer_data' AS customers (TYPE delta);

INSERT INTO customers VALUES ('C001', '张三', 25, TRUE);
INSERT INTO customers VALUES ('C002', '李四', 30, FALSE);
INSERT INTO customers VALUES ('C003', '王五', 28, TRUE);

-- ============================================
-- 第二步：通过 Unity Catalog 注册治理
-- ============================================
LOAD unity_catalog;

CREATE SECRET (
    TYPE     unity_catalog,
    TOKEN    'demo-token',
    ENDPOINT 'http://localhost:8080'
);

ATTACH 'unity' AS uc (TYPE unity_catalog, DEFAULT_SCHEMA 'analytics');

-- ============================================
-- 第三步：跨 Delta + UC 进行联合分析
-- ============================================
-- 查询 Delta 表的历史版本
SELECT count() FROM customers AT (VERSION => 0);

-- 通过 UC 执行权限感知的查询
SELECT * FROM uc.analytics.customer_profiles;