vertica基本操作

1
2
`CREATE USER laiyingyi IDENTIFIED BY 'WCbQngi2';`

1
2
GRANT CREATE ON SCHEMA DATA to xiaoming;

1
2
GRANT SELECT ON SCHEMA PUBLIC to xiaoming;

1
2
GRANT usage ON SCHEMA DATA to xiaoming;

1
2
GRANT ALL PRIVILEGES ON ship TO xiaoming

1
2
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to xiaoming;

1
2
GRANT all ON ALL TABLES IN SCHEMA DATA to xiaoming ;

1
2
GRANT SELECT ON TABLE PUBLIC.v_3 PUBLIC.v_2, PUBLIC.v_1 to xiaoming;

1
2
GRANT USAGE ON RESOURCE POOL qa_pool TO xiaoming;

1
2
ALTER USER laiyingyi RESOURCE POOL qa_pool;

1
2
ALTER TABLE data.shuyun_rmid SET SCHEMA public;

1
2
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public

1
2
 ALTER USER xiaoming IDENTIFIED BY 'sadasdas';

1
2
3
4
5
select E'select
MOVE_STATEMENT_TO_RESOURCE_POOL (''||session_id||E'','|| transaction_id||','||statement_id||','||E''kettle_pool');'
from sessions
where user_name = 'xiaoming' and statement_id is not null

1
2
3
4
5
6
7
8
9
10
11
12
13
database
table
------------>database1 table1
CONNECT TO VERTICA database1 USER
xxx
PASSWORD 'xxx' ON
'192.168.0.1', 5433;
EXPORT TO VERTICA database1.table1 AS
SELECT *
from
table;
DISCONNECT database1;

1
2
select analyze_statistics('asdsa');

1
2
3
select 'drop table ' ||table_schema ||'.'|| table_name || ';' from
tables where owner_name='xiaoming' and table_schema='DATA';

1
2
3
ALTER TABLE asdas RENAME COLUMN
user_typee TO stage_type

1
2
3
4
alter table rm_report_reach_by_channel_daily
add
user_type varchar(32) DEFAULT CASE WHEN (table1.stage_type = 'RM优选频次分布') THEN 'all' WHEN (table1.stage_type = 'TA 优选频次') THEN 'ta' WHEN (table1.stage_type = 'TA 推送频次') THEN 'ta' WHEN (table1.stage_type = 'TA 曝光频次') THEN 'ta' WHEN (rm_report_reach_by_channel_daily.stage_type = '媒体推送频次分布') THEN 'all' WHEN (table1.stage_type = '媒体曝光频次分布') THEN 'all' WHEN (table1.stage_type = '稳定人群 优选频次') THEN 'st' WHEN (table1.stage_type = '稳定人群 推送频次') THEN 'st' WHEN (table1.stage_type = '稳定人群 曝光频次') THEN 'st' ELSE rm_report_reach_by_channel_daily.stage_type END

1
2
3
4
5
6
7
8

alter table rm_report_reach_by_channel_daily
alter
column campaign_platform_name
set DEFAULT ( SELECT rm_campaign.campaign_platform_name
FROM rm_campaign
WHERE (rm_campaign.campaign_id= rm_report_reach_by_channel_daily.campaign_id));