如何在Databricks中为流表添加ID并编写DLT管道的SQL声明？

针对你的需求，以下是Databricks Delta Live Tables（DLT）的流式SQL实现方案：

1. 地址维度表 silver_address #

这个表需要对bronze表中的City+Country组合去重，并生成唯一整数ID，提供两种常用实现方式：

方式1：基础流式去重+生成ID #

适合不需要后续动态更新地址的场景，直接从bronze表流式读取并去重，用窗口函数生成ID：

CREATE OR REFRESH STREAMING LIVE TABLE silver_address
COMMENT '流式地址维度表，存储唯一地址信息'
AS
WITH unique_addresses AS (
  -- 从bronze流中提取唯一的地址组合
  SELECT DISTINCT City, Country
  FROM STREAM(live.bronze_table)
)
SELECT
  -- 按City+Country排序生成自增整数ID
  ROW_NUMBER() OVER (ORDER BY City, Country) AS ID,
  City,
  Country
FROM unique_addresses;

方式2：支持动态更新的维度表（推荐） #

如果后续bronze表会新增新地址，用APPLY CHANGES确保地址不重复插入，同时用IDENTITY列自动生成自增ID：

-- 创建临时表存储去重后的地址流数据
CREATE OR REFRESH STREAMING LIVE TABLE silver_address_raw
COMMENT '临时存储去重后的地址流数据'
AS
SELECT DISTINCT City, Country
FROM STREAM(live.bronze_table);

-- 创建最终维度表，自动生成自增ID
CREATE OR REFRESH LIVE TABLE silver_address
COMMENT '流式地址维度表，自动维护唯一地址与自增ID'
(
  City STRING,
  Country STRING,
  ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
)
TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true');

-- 合并数据，确保同一地址仅插入一次
APPLY CHANGES INTO live.silver_address
FROM STREAM(live.silver_address_raw)
KEYS (City, Country) -- 用地址组合作为唯一键去重
SEQUENCE BY __timestamp; -- 按数据时间戳处理顺序

2. 人员主表 silver_people #

关联silver_address获取地址ID，同时生成人员唯一ID：

基础实现（窗口函数生成ID） #

CREATE OR REFRESH STREAMING LIVE TABLE silver_people
COMMENT '流式人员主表，关联地址维度'
AS
SELECT
  -- 按Name排序生成人员自增ID
  ROW_NUMBER() OVER (ORDER BY b.Name) AS ID,
  b.Name,
  sa.ID AS Address_ID
FROM STREAM(live.bronze_table) b
-- 关联地址维度表获取对应的Address_ID
JOIN live.silver_address sa
ON b.City = sa.City AND b.Country = sa.Country;

支持动态更新的实现（IDENTITY列） #

如果需要人员ID自动自增且不重复，用IDENTITY列更可靠：

CREATE OR REFRESH STREAMING LIVE TABLE silver_people
COMMENT '流式人员主表，带自增ID并关联地址维度'
(
  Name STRING,
  Address_ID INT,
  ID INT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
)
AS
SELECT
  b.Name,
  sa.ID AS Address_ID
FROM STREAM(live.bronze_table) b
JOIN live.silver_address sa
ON b.City = sa.City AND b.Country = sa.Country;

关键注意点 #

• STREAM(live.bronze_table)：表示读取bronze表的流式数据，确保处理实时流入的记录
• IDENTITY列：DLT支持自动生成自增整数ID，比窗口函数更适合动态流式场景，避免重复ID
• APPLY CHANGES INTO：用于维度表的缓慢变化处理（SCD Type 1），确保同一地址只被存储一次
• 关联逻辑：通过City和Country字段将人员数据与地址维度关联，获取对应的Address_ID

内容的提问来源于stack exchange，提问作者redford