CREATE TABLE

准备工作

• 已购买Hologres实例。

• 已创建数据库。

• 已连接Hologres目标数据库，推荐连接HoloWeb并执行查询。

建表

推荐使用 V2.1 版本起支持的 CREATE TABLE WITH 语法。此示例演示一张遵循分层命名规范、字段丰富且包含完善注释的交易明细表。

BEGIN;

-- 创建交易明细事实表（基础普通表）
-- 显式指定 public schema，并在表名上体现分层规范 (dwd_xxx)
CREATE TABLE IF NOT EXISTS public.dwd_trade_orders (
    order_id BIGINT NOT NULL,           -- 订单唯一标识
    shop_id INT NOT NULL,              -- 店铺ID
    user_id TEXT NOT NULL,             -- 用户ID
    order_amount NUMERIC(12, 2) DEFAULT 0.00,       -- 订单金额
    payment NUMERIC(12, 2) DEFAULT 0.00,         -- 实付金额
    payment_type INT DEFAULT 0,                     -- 支付类型（0:未支付, 1:支付宝, 2:微信, 3:信用卡）
    is_delivered BOOLEAN DEFAULT false,             -- 是否发货
    dt TEXT NOT NULL,                               -- 业务日期
    order_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 下单时间
    PRIMARY KEY (order_id)                          -- 设置主键，保证数据唯一性
)
WITH (
    orientation = 'column',                         -- 设置为列存模式：适用于海量数据的实时分析（OLAP）场景
    distribution_key = 'order_id',                  -- 设置分布键：数据按 order_id 分片存储
    clustering_key = 'order_time:asc',              -- 设置聚簇索引：数据在文件内按时间升序排列
    event_time_column = 'order_time',               -- 设置分段键：利用文件级裁剪快速过滤时间区间
    bitmap_columns = 'shop_id,payment_type,is_delivered', -- 设置位图索引：针对低基数列加速等值过滤
    dictionary_encoding_columns = 'user_id:auto'    -- 设置字典编码：加速字符串列的聚合与过滤
);

-- 添加元数据注释
COMMENT ON TABLE public.dwd_trade_orders IS '交易订单明细基础事实表';
COMMENT ON COLUMN public.dwd_trade_orders.order_id IS '订单唯一标识';
COMMENT ON COLUMN public.dwd_trade_orders.shop_id IS '店铺唯一ID';
COMMENT ON COLUMN public.dwd_trade_orders.user_id IS '购买者用户ID';
COMMENT ON COLUMN public.dwd_trade_orders.dt IS '业务日期, 格式 YYYYMMDD';
COMMENT ON COLUMN public.dwd_trade_orders.order_time IS '订单创建精确时间戳';

COMMIT;

插入数据

Hologres 兼容标准 DML 语法。以下插入 10 条模拟生产环境的明细数据。

INSERT INTO public.dwd_trade_orders (order_id, shop_id, user_id, order_amount, payment, payment_type, is_delivered, dt, order_time) VALUES 
(50001, 101, 'U678', 299.00, 280.00, 1, true,  '20231101', '2023-11-01 10:00:01+08'),
(50002, 102, 'U992', 59.00,  59.00,  2, false, '20231101', '2023-11-01 10:05:12+08'),
(50003, 101, 'U441', 150.00, 145.00, 1, true,  '20231101', '2023-11-01 10:10:45+08'),
(50004, 105, 'U219', 888.00, 888.00, 3, true,  '20231101', '2023-11-01 10:20:11+08'),
(50005, 102, 'U883', 35.00,  30.00,  1, false, '20231101', '2023-11-01 10:32:00+08'),
(50006, 110, 'U007', 120.50, 120.50, 2, true,  '20231101', '2023-11-01 10:45:33+08'),
(50007, 101, 'U321', 210.00, 210.00, 1, true,  '20231101', '2023-11-01 11:02:19+08'),
(50008, 108, 'U556', 45.00,  45.00,  2, false, '20231101', '2023-11-01 11:15:04+08'),
(50009, 101, 'U112', 300.00, 290.00, 3, true,  '20231101', '2023-11-01 11:25:55+08'),
(50010, 105, 'U449', 99.90,  99.90,  1, true,  '20231101', '2023-11-01 11:40:22+08');

查询数据

-- 统计各店铺成交额并按金额降序排列
SELECT 
    shop_id, 
    COUNT(1) as total_orders, 
    SUM(payment) as total_payment 
FROM public.dwd_trade_orders 
GROUP BY shop_id 
ORDER BY total_payment DESC;

查询结果：

shop_id	total_orders	total_payment
105	2	987.90
101	4	925.00
110	1	120.50
102	1	59.00
108	1	45.00

建表语法说明

Hologres 提供两种设置表属性及添加注释的语法：

• 标准语法（推荐 V2.1+ 使用）

  使用 WITH 关键字定义属性，结构紧凑且性能更佳，注释语句通常紧随建表语句，完整示例请参见建表。

  BEGIN;
  
  CREATE TABLE [ IF NOT EXISTS] [schema_name.]table_name ([
      { 
      column_name column_type [column_constraints, [...]]
      | table_constraints
      [,...]
      }
  ])
  [WITH (
      property = 'value',
      [, ...]
  )]
  ;
  [COMMENT ON COLUMN <[schema_name.]tablename.column> IS '<value>';]
  [COMMENT ON TABLE <[schema_name.]tablename> IS '<value>';]
  
  COMMIT;

• 兼容语法（全版本支持）

  通过 CALL 方式set_table_property设置属性和COMMENT注释，必须与 CREATE TABLE 处于同一个事务块（BEGIN...COMMIT）中。

  BEGIN;
  
  CREATE TABLE [ IF NOT EXISTS] [schema_name.]table_name ([
      { 
      column_name column_type [column_constraints, [...]]
      | table_constraints
      [,...]
      }
  ]);
  
  CALL set_table_property('[schema_name.]<table_name>', '<property>', '<value>');
  COMMENT ON COLUMN <[schema_name.]tablename.column> IS '<value>';
  COMMENT ON TABLE <[schema_name.]tablename> IS '<value>';
  
  COMMIT;

表属性

示例：海量数据实时分析场景（事实表）

场景描述：用于实时大盘汇总。此类表通常数据量极大，核心需求是统计聚合性能（如计算 GMV、订单量）。

BEGIN;

-- 显式指定 public schema
CREATE TABLE IF NOT EXISTS public.dwd_order_summary (
    order_id BIGINT PRIMARY KEY,
    category_id INT NOT NULL,
    gmv NUMERIC(15, 2),
    order_time TIMESTAMPTZ NOT NULL
) WITH (
    orientation = 'column',             -- 存储模式：列存。海量聚合分析的首选，提供高压缩比和高效的列扫描
    distribution_key = 'order_id',      -- 分布键：按 order_id 分片，确保数据均匀分布；关联其他订单表时可实现 Local Join
    event_time_column = 'order_time',   -- 分段键：通常设为时间字段。系统会按此字段划分文件段，加速按时间范围的过滤
    clustering_key = 'order_time:asc'   -- 聚簇索引：物理上按时间升序排列。在查询“最近1小时”或“某天”的数据时，减少磁盘 I/O
);

-- 添加元数据注释，便于后续数据治理
COMMENT ON TABLE public.dwd_order_summary IS '订单汇总明细事实表';
COMMENT ON COLUMN public.dwd_order_summary.order_id IS '唯一订单ID';
COMMENT ON COLUMN public.dwd_order_summary.category_id IS '品类ID';
COMMENT ON COLUMN public.dwd_order_summary.gmv IS '成交额';
COMMENT ON COLUMN public.dwd_order_summary.order_time IS '下单时间';

COMMIT;

示例：高并发主键点查场景（维表）

场景描述：根据 user_id 毫秒级回显用户画像。此类表侧重于高 QPS 下的极低延迟响应。

BEGIN;

-- 显式指定 public schema
CREATE TABLE IF NOT EXISTS public.dim_user_persona (
    user_id TEXT PRIMARY KEY,           -- 主键：点查场景的核心，必须设置
    user_level INT,
    persona_jsonb JSONB 
) WITH (
    orientation = 'row'                 -- 存储模式：行存。专门为基于主键的点查（Point Lookup）优化，响应速度可达毫秒级
    -- 说明：行存表会自动将主键设为分布键和聚簇索引，无需额外设置
);

-- 添加元数据注释
COMMENT ON TABLE public.dim_user_persona IS '用户画像维度表';
COMMENT ON COLUMN public.dim_user_persona.user_id IS '唯一用户ID';
COMMENT ON COLUMN public.dim_user_persona.user_level IS '用户等级';
COMMENT ON COLUMN public.dim_user_persona.persona_jsonb IS '用户特征画像JSON';

COMMIT;

示例：混合负载场景（行列共存）

场景描述：物流售后系统。既要对物流状态进行分析汇总（分析类），也要根据订单 ID 瞬间调取物流详情（点查类）。

BEGIN;

-- 显式指定 public schema
CREATE TABLE IF NOT EXISTS public.ads_shipping_info (
    order_id BIGINT PRIMARY KEY,
    shipping_status INT,
    receiver_address TEXT,
    update_time TIMESTAMPTZ
) WITH (
    orientation = 'row,column',         -- 存储模式：行列共存。同时拥有行存的毫秒级点查和列存的高效聚合分析能力
    distribution_key = 'order_id',      -- 分布键：决定数据在 Shard 间的分布逻辑
    bitmap_columns = 'shipping_status'  -- 位图索引：针对状态字段等低基数列设置，极大加速“状态过滤”类的查询
);

-- 添加元数据注释
COMMENT ON TABLE public.ads_shipping_info IS '物流状态查询应用表';
COMMENT ON COLUMN public.ads_shipping_info.order_id IS '订单ID';
COMMENT ON COLUMN public.ads_shipping_info.shipping_status IS '物流状态（1:待发货, 2:运输中, 3:已签收）';
COMMENT ON COLUMN public.ads_shipping_info.receiver_address IS '收货地址';

COMMIT;