-- 连接到 Delta 表
ATTACH 's3://my-bucket/delta-table' AS delta_db (TYPE delta);

-- 向 Delta 表插入数据
INSERT INTO delta_db.my_table
SELECT
    gen_random_uuid()::VARCHAR AS id,
    ['北京', '上海', '广州', '深圳', '杭州'][1 + (random() * 4)::INT] AS city,
    (1000 + random() * 9000)::INT AS amount,
    CURRENT_DATE AS sale_date
FROM range(1000);

-- 验证写入结果
SELECT count(*) AS total_rows FROM delta_db.my_table;

-- 创建 Unity Catalog 安全凭据
CREATE SECRET (
    TYPE unity_catalog,
    KEY_ID 'your-access-key',
    SECRET 'your-secret-key',
    TOKEN 'your-session-token'
);

-- 附加 Unity Catalog 数据源
ATTACH 'unity://catalog' AS uc (TYPE unity_catalog);

-- 列出可用 schema
SHOW SCHEMAS FROM uc;

-- 查询 Unity Catalog 管理的表
SELECT * FROM uc.my_catalog.my_schema.sales LIMIT 10;

-- 跨 schema 聚合分析
SELECT
    product_category,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM uc.analytics.ecommerce.orders
WHERE sale_date >= DATE '2024-01-01'
GROUP BY product_category
ORDER BY total_revenue DESC;

-- 通过 Spark 或 UC CLI 启用 CMT 属性
-- CREATE TABLE my_schema.concurrent_tbl (...)
-- TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

-- 启用后，DuckDB 写入自动经过 UC 的提交编排
INSERT INTO uc.my_catalog.my_schema.concurrent_tbl
VALUES (gen_random_uuid()::VARCHAR, '产品A', 999, TRUE);

-- 附加特定版本的 Delta 表
ATTACH 's3://my-bucket/delta-table' AS t (TYPE delta, VERSION 16);

-- 查询版本 16 的数据
SELECT count(*) FROM t;  -- 返回该版本的数据量

-- 附加另一个版本
ATTACH 's3://my-bucket/delta-table' AS t2 (TYPE delta, VERSION 20);
SELECT count(*) FROM t2;  -- 返回版本 20 的数据量

-- 附加特定时间点的 Delta 表
ATTACH 's3://my-bucket/delta-table' AS t (TYPE delta, TIMESTAMP '2024-03-15 10:30:00');

-- 查询该时间点的数据状态
SELECT * FROM t WHERE sale_date >= DATE '2024-03-15';

-- 首先查询版本 16
ATTACH 's3://my-bucket/delta-table' AS t16 (TYPE delta, VERSION 16);
SELECT count(*) FROM t16;

-- 然后查询版本 20 - 增量加载不会重新读取旧日志
ATTACH 's3://my-bucket/delta-table' AS t20 (TYPE delta, VERSION 20);
SELECT count(*) FROM t20;

-- 验证增量加载效果
SET enable_logging = true;
SET delta_kernel_logging = true;
CALL enable_logging('DeltaKernel', level = 'trace');

ATTACH 's3://my-bucket/delta-table' AS t (TYPE delta, VERSION 20);
SELECT count(*) FROM t;

-- 检查是否重新读取了日志文件
SELECT count() FROM duckdb_logs
WHERE type = 'DeltaKernel'
  AND message LIKE '%00000000000000000%.json%';
-- 返回 0 表示使用了缓存的快照，而非重新加载

-- 模拟数据误删除后的恢复
-- 1. 查看当前数据
ATTACH 's3://my-bucket/sales-data' AS current (TYPE delta);
SELECT count(*) FROM current.sales;  -- 假设 10000 行

-- 2. 误删除发生...
DELETE FROM current.sales WHERE region = '华东';

-- 3. 恢复到删除前的版本
ATTACH 's3://my-bucket/sales-data' AS backup (TYPE delta, VERSION 42);
SELECT count(*) FROM backup.sales;  -- 仍然是 10000 行

-- 4. 从备份恢复数据
INSERT INTO current.sales
SELECT * FROM backup.sales
WHERE region = '华东'
  AND id NOT IN (SELECT id FROM current.sales);