Vertica Database Operations

Delete primary KEY (the primary key value of the Vertica database is not unique):
SELECT analyze_constraints (' Fb_s.c_log ');

Locate the key name, and then:

ALTER TABLE fb_s.c_log DROP CONSTRAINT c_primary;

SELECT analyze_constraints (' Fb_s.user_info ');

ALTER TABLE fb_s.user_info DROP CONSTRAINT c_primary;

Build Users and schemas:

CREATE user fb_s_sql identified by ' password ';
CREATE SCHEMA Fb_s_sql;

Give permission:
GRANT all on the SCHEMA fb_s_sql to Fb_s_sql;
GRANT all on the SCHEMA fb_s to Fb_s_sql;

GRANT all on TABLE fb_s_sql.sqllog to Fb_s_sql;

Build table:

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
);

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 of time) *100 + EXTRACT (month from time);

The latter one is partitioned by the time field

Add and Change fields:

ALTER TABLE fb_s.c_logadd 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 ';


Guide data between two tables:
Insert into Fb_s.c_log (uid,cash,gold,level,rtime,tid,act,item,value,value2,time)
(SELECT * from Fb_s.c_logbak);

Guide data between two libraries:

Export in Source Library:

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; "&

Destination Library Import:
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 ', ';

Vertica Database Operations