Hologres：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;