VERTIA常用命令

新建测试表 ：

CREATE TABLE PUBLIC.STUDENTS
(ID     INT NOT NULL ,
NAME    VARCHAR(20) ,
AGE     INT ,
GRADE   INT ,
COURSE  VARCHAR(20) ,
SCORE   NUMERIC(18,1) ,
CREDIT  NUMERIC(18,2)
);

常用命令 ：

查看表结构  \d PUBLIC.STUDENTS

模糊查询表  \dt *STUDENTS*

新增字段     alter table PUBLIC.STUDENTS add column PHONE VARCHAR(20);

修改字段名称   alter table PUBLIC.STUDENTS rename PHONE to TELLPHONE;

修改字段类型  alter table PUBLIC.STUDENTS alter column PHONE set data type VARCHAR(18);

重命名       alter table PUBLIC.STUDENTS rename to STUDENTS_BAK0724;

插入数据     insert into PUBLIC.STUDENTS 
             select 1,'zhangsan',18,9,'语文',81.5,4.24,'13011112222'
             union  select 1,'zhangsan',18,9,'数学',90,4.24,'15500001111'
             union  select 2,'lisi',17,8,'语文',88,3.75,'18733334444'
             
查看建表语句 select export_tables ('','PUBLIC.STUDENTS')

vertica导入数据文件  cpoy PUBLIC.STUDENTS  from /data/v_data/verticadata/a.txt delimiter ',' (如果是admin用户，数据文件可以放在任何目录下，如果不是，数据文件就需要放在/data/v_data/verticadata/这个目录下，否则就会报 ‘Permission denied for storage location [路径]')

vertica导出数据文件  vsql -U username -w password -At -F'|' -c "select * from PUBLIC.STUDENTS" -o /data/v_data/verticadata/b.txt  (-U 账户名 -w密码 -F导出数据切割符 -o导入目标文件不存在会直接创建)

vertica查询表字段    select column_id,column_name  from columns where  upper(table_schema) = 'PUBLIC' and upper(table_name) = 'STUDENTS'

vertica查询表使用次数 select table_schema , table_name,  count(1) as used_count  from  dc_projections_used group by table_schema , table_name

vertica 查询表大小  select  anchor_table_schema , anchor_table_name, sum(used_bytes)/1024/1024/1024 as size_G  from  projection_storage  group by anchor_table_schema , anchor_table_name
      
vertica 查询节点信息  select * from nodes 

vertica 查询磁盘使用情况 SELECT sum(disk_space_used_mb) as disk_space_used_mb ,sum(disk_space_free_mb) as disk_space_free_mb from disk_storage

vertica执行计划：

1. 获取 transaction_id 和 statement_id
   profile select * from PUBLIC.STUDENTS a where id > 2 and id <= 5;
2. 查询sql资源消耗信息
   Select * from v_monitor.execution_engine_profiles where transaction_id=45035996283088106 and statement_id=28;
3. 查询sql实际执行计划
   select * from v_monitor.query_plan_profiles where transaction_id = ‘45035996283088106’ and statement_id = 28 ;