📚 课程目录第4章数据类型与DDL转换实战

4. 数据类型与DDL转换实战

把源平台（Oracle、Teradata、SQL Server、Hive 等）的表结构迁移到 Snowflake 时，最容易出问题的往往不是表名或列名，而是数据类型。Snowflake 的类型体系与多数传统数据库并不一一对应：它把整数、定点数、浮点数统一归入 NUMBER 家族，把变长字符串统一为 VARCHAR，把日期与时间拆成 DATE、TIME、TIMESTAMP 三类，并额外提供 VARIANT、OBJECT、ARRAY 三种半结构化类型。理解这套映射关系，是写出可执行 CREATE TABLE 语句的前提。

一、数值类型：精度与标度的取舍

Snowflake 的 NUMBER(p,s) 中 p 是总有效位数（1~38），s 是小数位数。源平台的 DECIMAL、NUMERIC、INT、BIGINT、SMALLINT 都可以映射到 NUMBER，但要注意两点：一是 Snowflake 的 NUMBER 精度上限为 38，源平台若出现 DECIMAL(50,10) 这类超限定义，必须降精度或改用 VARCHAR 暂存；二是整数类型建议显式写成 NUMBER(38,0)，避免默认精度带来的隐式转换开销。

浮点类型需要特别小心。源平台的 FLOAT、DOUBLE、REAL 在 Snowflake 中对应 FLOAT（即双精度），但 FLOAT 不保证精确相等比较，涉及金额、计数等场景应改用 NUMBER。例如源表 orders 中 amount DECIMAL(18,2)，迁移后应写为 amount NUMBER(18,2)，而不是 FLOAT。

二、字符串类型：长度语义的差异

Snowflake 的 VARCHAR(n) 中 n 是字符数而非字节数，且最大可达 16MB。源平台的 CHAR(n) 是定长补空格，迁移到 Snowflake 后若直接写成 CHAR(n)，查询时可能因尾部空格导致比较失败，因此建议统一转为 VARCHAR(n)。对于源平台中 VARCHAR(MAX)、TEXT、CLOB 这类无明确长度的类型，Snowflake 可直接使用 VARCHAR（不写长度），默认上限即为 16MB。

三、日期时间类型：时区与精度

这是迁移中最容易踩坑的一类。Snowflake 提供 DATE（仅日期）、TIME（仅时间）、TIMESTAMP_NTZ（不带时区）、TIMESTAMP_LTZ（带本地时区）、TIMESTAMP_TZ（带时区偏移）。源平台的 DATETIME、TIMESTAMP 通常不带时区，应映射为 TIMESTAMP_NTZ；若源数据本身带时区信息，则用 TIMESTAMP_TZ。默认情况下 Snowflake 会话参数 TIMESTAMP_TYPE_MAPPING 为 TIMESTAMP_NTZ，因此直接写 TIMESTAMP 等价于 TIMESTAMP_NTZ，但显式写出更利于维护。

精度方面，Snowflake 时间戳默认精度为 9（纳秒），源平台若为毫秒精度，可在 DDL 中写 TIMESTAMP_NTZ(3) 以对齐。日期格式转换则依赖会话参数 DATE_INPUT_FORMAT 与 TIME_INPUT_FORMAT，加载前应确认源数据格式与参数一致。

四、半结构化类型：VARIANT、OBJECT、ARRAY

当源平台使用 JSON、XML 或嵌套结构存储时，Snowflake 推荐使用 VARIANT 承载任意半结构化数据，OBJECT 用于键值对，ARRAY 用于有序列表。VARIANT 最大 16MB，可直接用 src:key 语法或 GET_PATH 函数取值。需要注意的是，VARIANT 中的数值在提取后默认是 VARIANT 类型，参与计算前需显式 CAST，例如 CAST(v:price AS NUMBER(10,2))。

五、编写兼容的 CREATE TABLE 与 CREATE VIEW

下面给出一个综合示例。假设源平台有一张订单表，包含金额、客户名、下单时间、扩展属性 JSON 和是否加急标志，迁移到 Snowflake 的 DDL 如下：

CREATE OR REPLACE TABLE orders (
  order_id      NUMBER(38,0)      NOT NULL,
  customer_name VARCHAR(200),
  amount        NUMBER(18,2),
  order_time    TIMESTAMP_NTZ(3),
  ext_attr      VARIANT,
  is_urgent     BOOLEAN,
  CONSTRAINT pk_orders PRIMARY KEY (order_id)
);

如果源系统习惯用视图屏蔽底层变化，可以在 Snowflake 中建立兼容视图，把 VARIANT 字段展开为普通列，让下游 BI 工具无需感知半结构化类型：

CREATE OR REPLACE VIEW v_orders AS
SELECT
  order_id,
  customer_name,
  amount,
  order_time,
  CAST(ext_attr:channel AS VARCHAR(50))  AS channel,
  CAST(ext_attr:discount AS NUMBER(5,2)) AS discount,
  is_urgent
FROM orders;

这里 ext_attr:channel 是 VARIANT 路径取值语法，返回的仍是 VARIANT，因此必须用 CAST 转成目标类型，否则下游做字符串拼接或数值运算时会报类型错误。这是半结构化类型迁移中最常见的返工点。

迁移前建议先执行一次类型盘点：用源平台的数据字典查询所有列的类型与长度，与 Snowflake 映射表逐列比对，重点标记 DECIMAL 精度超 38、CHAR 定长、带时区时间戳、JSON/XML 列这四类高风险字段。

不要直接把源平台的 DDL 脚本批量替换关键字后执行。Snowflake 不支持某些源平台语法（如 Oracle 的 NUMBER(*)、SQL Server 的 IDENTITY 写法），主键、自增、默认值等约束也需要按 Snowflake 语法重写，否则建表会直接失败。

完成 DDL 转换后，建议用 DESCRIBE TABLE orders; 核对每列的实际类型，再用 SELECT * FROM orders LIMIT 10; 观察数据是否正确落入。若发现时间戳偏移、金额精度丢失或 JSON 取值报错，应回到映射表逐列修正，而不是在加载脚本里做临时补丁。类型映射一次做对，后续的 ETL 与查询才能稳定运行。