Vertica Database Operations

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.