Vertica 數據庫運維備忘錄：DDL、權限、分區、跨庫遷移實戰

約束與主鍵管理

-- 檢查約束違規
SELECT ANALYZE_CONSTRAINTS('fb_s.c_log');

-- 刪除主鍵（Vertica 主鍵不強制唯一，僅供優化器參考）
ALTER TABLE fb_s.c_log DROP CONSTRAINT C_PRIMARY;
ALTER TABLE fb_s.user_info DROP CONSTRAINT C_PRIMARY;

注意：Vertica 主鍵預設 ENABLED 但 NOT ENFORCED，僅用於查詢優化，不阻擋重複值寫入。

用戶與權限體系

-- 建立用戶與 Schema
CREATE USER fb_s_sql IDENTIFIED BY 'password';
CREATE SCHEMA fb_s_sql AUTHORIZATION fb_s_sql;

-- 權限授予
GRANT ALL ON SCHEMA fb_s_sql TO fb_s_sql;
GRANT ALL ON SCHEMA fb_s TO fb_s_sql;      -- 跨 Schema 訪問
GRANT ALL ON TABLE fb_s_sql.sqllog TO fb_s_sql;

建表與分區策略

普通表

CREATE TABLE fb_s.c_log (
    uid INT NOT NULL,
    cash INT, gold INT, level INT,
    rtime TIMESTAMP, tid VARCHAR(20),
    act VARCHAR(50), item VARCHAR(500),
    value INT, value2 INT, time TIMESTAMP
);

分區表（按時間月份）

CREATE TABLE fb_s.new_c_log (
    uid INT PRIMARY KEY NOT NULL,
    cash INT, gold INT, level INT,
    rtime TIMESTAMP, tid VARCHAR(20),
    act VARCHAR(50), item VARCHAR(500),
    value INT, value2 INT, time TIMESTAMP NOT NULL
)
PARTITION BY EXTRACT(YEAR FROM time)*100 + EXTRACT(MONTH FROM time);

• 分區鍵運算式返回整數，每個月一個分區（如 202401、202402）。
• 查詢含分區鍵篩選時自動分區剪枝，大幅提升掃描效能。
• 支援 DROP_PARTITIONS 秒級歸檔舊數據。

欄位異動操作

-- 新增欄位帶預設值
ALTER TABLE fb_s.c_log ADD COLUMN value2 INT DEFAULT 0;

-- 修改預設值
ALTER TABLE fb_s.c_log ALTER COLUMN duration SET DEFAULT 0;
ALTER TABLE fb_s.c_log ALTER COLUMN mesg SET DEFAULT '';

表間/跨庫數據遷移

同庫表間複製

INSERT INTO fb_s.c_log (uid,cash,gold,level,rtime,tid,act,item,value,value2,time)
SELECT * FROM fb_s.c_logbak;

跨庫遷移：導出 → 傳輸 → 導入

REM 來源庫導出 CSV（vsql 命令列工具）
vsql -d topcity -U dbadmin -w password -F ',' -At -o fs_user_info.csv -c "SELECT * FROM fb_s.user_info;"
vsql -d topcity -U dbadmin -w password -F ',' -At -o fs_c_log.csv -c "SELECT * FROM fb_s.c_log;"

REM 目的庫導入（COPY 命令，高效並行）
COPY fb_s.user_info FROM '/opt/fs_user_info.csv' EXCEPTIONS '/tmp/exp.log' DELIMITER ',';
COPY fb_s.c_log FROM '/opt/fs_c_log.csv' EXCEPTIONS '/tmp/exp.log' DELIMITER ',';

關鍵參數：

• -F ','：欄位分隔符
• -At：僅輸出資料行（無標題、無對齊）
• EXCEPTIONS：錯誤行導向日誌檔，不中斷整體導入
• DELIMITER：指定分隔符，支援多字元

效能調優關鍵點

1. 投影設計：Vertica 核心在於投影，建立針對查詢模式優化的投影（排序、編碼、分段）。
2. 統計資訊：SELECT ANALYZE_STATISTICS('schema.table'); 定期執行。
3. Tuple Mover：監控 MOVEOUT / MERGEOUT 狀態，避免 ROS 容器過多導致查詢降級。
4. EXPLAIN 分析：關注 SEGMENT、BROADCAST、RESEGMENT 網路傳輸開銷。

常用管理命令速查

-- 查看節點狀態
SELECT * FROM v_monitor.hosts;

-- 查看投影儲存佔用
SELECT anchor_table_name, SUM(used_bytes)/1024^3 AS GB 
FROM v_monitor.projection_storage GROUP BY 1 ORDER BY 2 DESC;

-- 手動觸發 Tuple Mover
SELECT DO_TM_TASK('moveout');
SELECT DO_TM_TASK('mergeout');