vertica基本操作

创建用户

`CREATE USER laiyingyi IDENTIFIED BY 'WCbQngi2';`

赋给 xiaoming用户 在 DATA schema下 创建权限

GRANT CREATE ON SCHEMA DATA to xiaoming;

赋给 xiaoming用户 在 PUBLIC schema下 查询权限

GRANT SELECT ON SCHEMA PUBLIC to xiaoming;

赋给 xiaoming用户 在 DATA schema下 使用权限

GRANT usage ON SCHEMA DATA to xiaoming;

赋给 xiaoming用户 在 ship 视图下 查看权限

GRANT ALL PRIVILEGES ON ship TO xiaoming

赋给 xiaoming用户 在 public SCHEMA 的所有表 查看权限

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to xiaoming;

赋给 xiaoming用户 在 DATA SCHEMA 的所有表 查看删除修改权限

GRANT all ON ALL TABLES IN SCHEMA DATA to xiaoming ;

赋给 xiaoming用户 在 v1,v2,v3 查看权限

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

赋给 xiaoming用户 qa 资源池 使用 权限

GRANT USAGE ON RESOURCE POOL qa_pool TO xiaoming;

赋给 xiaoming用户 默认资源池 为 qa

ALTER USER laiyingyi RESOURCE POOL qa_pool;

修改表 data.shuyun_rmid 的schema 为public

ALTER TABLE data.shuyun_rmid SET SCHEMA public;

将所有函数所有权限给所有人

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public

修改小明密码为 sadasdas

 ALTER USER xiaoming IDENTIFIED BY 'sadasdas';

修改xiaoming用户当前资源池为kettle

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

vertica 俩个数据库倒数据

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;

扫描表

select analyze_statistics('asdsa');

删除临时表

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

修改列名称：

ALTER TABLE asdas RENAME COLUMN
user_typee TO stage_type

添加字段default：

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

修改字段default：

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

最后

以上就是伶俐皮皮虾为你收集整理的vertica基本操作的全部内容，希望文章能够帮你解决vertica基本操作所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。