-- 檢查約束違規
SELECT ANALYZE_CONSTRAINTS('fb_s.c_log');
-- 刪除主鍵約束(需先知名稱)
ALTER TABLE fb_s.c_log DROP CONSTRAINT C_PRIMARY;
CREATE USER fb_s_sql IDENTIFIED BY 'password';
CREATE SCHEMA fb_s_sql;
GRANT ALL ON SCHEMA fb_s_sql TO fb_s_sql;
GRANT ALL ON SCHEMA fb_s TO fb_s_sql;
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 DATETIME, tid VARCHAR(20), act VARCHAR(50),
item VARCHAR(500), value INT, value2 INT, time DATETIME
);
-- 分區表:依 time 欄位按 年月 分區
CREATE TABLE fb_s.new_c_log (
uid INTEGER PRIMARY KEY NOT NULL, cash INTEGER, gold INTEGER,
level INTEGER, rtime DATETIME, tid VARCHAR(20), act VARCHAR(50),
item VARCHAR(500), value INTEGER, value2 INTEGER, time DATETIME NOT NULL
)
PARTITION BY EXTRACT(YEAR FROM time)*100 + EXTRACT(MONTH FROM time);
ALTER TABLE fb_s.c_log ADD COLUMN value2 INTEGER 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;
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;" &
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:匯入時對應分隔符作者:zhe-jiang.he@hp.com | 歡迎交流指正