Vertica database operations, vertica Database

Vertica database operations, vertica Database

Delete a primary key (the primary key value of the Vertica database is not unique ):
SELECT ANALYZE_CONSTRAINTS ('fb _ s. c_log ');

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

Create user and SCHEMA:

CREATE user fb_s_ SQL IDENTIFIED BY 'Password ';
Create schema fb_s_ SQL;

Grant permissions:
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;

Table creation:

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

The last one is partitioned by the time field.

Add and modify 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 '';


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

Import data between two databases:

Export in source database:

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 Database 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 'exception'/tmp/exp. log 'delimiter ',';

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.