Vertica常用操作总结

转载：https://blog.csdn.net/jiangshouzhuang/article/details/34434089 Vertica常用操作总结

1. 修改列名称

alter table app.DIM_TNES_CELL_RAT rename rat_name to CELL_RAT_NAME;

2. Vertica导出自己测试

NOAS252:/tmp/vertica6.1.2# /opt/vertica/sbin/install_vertica -s NOAS252 -r vertica-6.1.2-0.x86_64.SUSE.rpm -u dbadmin -L CE

VerticaAnalytic Database 6.1.2-0 Installation Tool

Upgradingadmintools meta data format…

scanning/opt/vertica/config/users

Startinginstallation tasks…

Gettingsystem information for cluster (this may take a while)…

backingup admintools.conf on 10.41.20.252

Defaultshell on nodes:

10.41.20.252/bin/bash

Info: thepackage ‘pstack’ is useful during troubleshooting. Vertica recommends this package is installed.

Checking/fixingOS parameters…

Settingvm.min_free_kbytes to 28120 …

Detectedcpufreq module loaded on 10.41.20.252

CPUfrequency scaling is enabled. This mayadversely affect the performance of your database.

Verticarecommends that cpu frequency scaling be turned off or set to ‘performance’

Creating/Checking Vertica DBA group

Creating/Checking Vertica DBA user

Error:Can’t change ownership of home directory ~dbadmin for the user dbadmin on host[‘10.41.20.252’]

Installing/RepairingSSH keys for dbadmin

Creating Vertica Data Directory…

Updatingspread configuration…

Verifyingspread configuration on whole cluster.

Creatingnode node0001 definition for host 10.41.20.252

… Done

ErrorMonitor 0 errors 4 warnings

Installationcompleted with warnings.

Installationcomplete.

To createa database:

1. Logoutand login as dbadmin.**

2. Run/opt/vertica/bin/adminTools as dbadmin

3. SelectCreate Database from the Configuration Menu

** Theinstallation modified the group privileges for dbadmin.

If you used sudo to install vertica asdbadmin, you will

need to logout andlogin again before the privileges are applied.

3. Vertica导入

导入时若用户不存在，会自动创建用户；

用户存在则导入数据；

若表数据冲突，如主键冲突，不会导入冲突的，会导入不冲突的。

4. Vertica数据导出步骤

l 生成配置文件

/opt/vertica/bin/vbr.py --setupconfig

注意：当集群上存在多个数据库时，配置文件生成后，可能需要手工修改，请检查配置文件参数。

导出目录权限设置问777。（设置为666时出现很多权限错误）。

l 导出数据

/opt/vertica/bin/vbr.py --task backup --config-filenoasbak1.ini

l 导入数据

/opt/vertica/bin/vbr.py --task restore --config-filenoasbak1.ini

5. 不同数据库之间导数据

导出数据：

l CONNECT TO VERTICA noas USER dbadmin PASSWORD ‘noas’ ON’10.41.20.252’,5433;

l EXPORT TO VERTICA noas.test.fct_cont_station_result FROM noas_app.fct_cont_station_result;

l EXPORT TO VERTICA testdb.ma_customers AS SELECT customer_key,customer_name, annual_income FROM customer_dimension WHERE customer_state =‘MA’;

l EXPORT TO VERTICA testdb.people (name, gender, age) FROMcustomer_dimension (customer_name, customer_gender, customer_age);

l DISCONNECT noas;

导入数据：

l CONNECT TO VERTICA noas USER dbadmin PASSWORD ‘noas’ ON’10.41.20.252’,5433;

l COPY customer_dimension FROM VERTICA vmart.customer_dimensionDIRECT;

l COPY people (name, gender, age) FROM VERTICAvmart.customer_dimension (customer_name, customer_gender,customer_age);

l DISCONNECT vmart;

6. Vertica导出数据到cluster之外的机器，需要满足：

l Have sufficient backup disk space.

l Be accessible from your database cluster.

l Have passwordless SSH access for the database administrator account.

l Have a copy of the same versions of Python and rsync that wereinstalled by the Vertica installer.

7. Vertica集群导出数据日志

[dbadmin@etl10 ~]$/opt/vertica/bin/vbr.py --task backup --config-filefct_fournet_3gcell_equp_ana_d_snapshot.ini

Preparing…

Found Database port: 5433

Enter password for dbadmin@10.41.28.83(2 attempts left):

Warning: following node(s) is in thecluster but not specified in the config. Backup will proceed but restoring froma partial backup can result in loss of data or corruption if an inappropriateset of nodes is chosen.

[‘v_noas_node0003’]

Warning: Node state ofv_noas_node0001 is VALIDATING LICENSE; ideally node should be UP for backup;empty backup will be created on this node.

Warning: Node state ofv_noas_node0002 is VALIDATING LICENSE; ideally node should be UP for backup;empty backup will be created on this node.

The authenticity of host’10.41.28.84 (10.41.28.84)’ can’t be established.

RSA key fingerprint isda:d1:ed:23:99:f7:8f:ef:96:af:91:49:67:f7:1c:ef.

Are you sure you want to continueconnecting (yes/no)? yes

Warning: Permanently added’10.41.28.84’ (RSA) to the list of known hosts.

Permission denied(publickey,gssapi-keyex,gssapi-with-mic,password).

Unable to ssh to host: 10.41.28.84

backupfailed!

8. Vsql

可安装在集群外的机器上

9. 临时表

GlobalTemporary Tables

在public schema中创建，多个用户可同时访问，但只能看到自己session的数据。

LocalTemporary Tables

仅对创建它的用户可见，仅在创建此表的session中生效。Session结束，系统会自动删除该表。

创建速度比一般表快。

创建完Temporary Tables后，系统会立即创建default superprojection，除非你指定NO PROJECTION。

10. LICENSE

查看license的信息：

SELECTDISPLAY_LICENSE();

查看license状态：

SELECT GET_COMPLIANCE_STATUS();

11. USER

创建用户：

CREATE USER Fred IDENTIFIED BY ‘password’;–用户名不区分大小写

CREATE USER “FrEd1”;–区分大小写

改变用户：

ALTER USER Fred ACCOUNT LOCK;

ALTER USER Fred ACCOUNT UNLOCK;

ALTER USER Fred IDENTIFIED BY ‘newpassword’;

12. adminTools

命令行启动数据库：/opt/vertica/bin/admintools-t start_db -d VMart

13. 外部表

外部表可做查询语句的操作，不可做更新（update）、删除（delete）、插入（insert）语句的操作。

支持：

SELECT * FROM external_table;

SELECT * FROM external_table where col1=4;

DELETE FROM ext WHERE id IN (SELECT x FROM external_table);

INSERT INTO ext SELECT * FROM external_table;

不支持：

DELETE FROM external_table WHERE x = 5;

SELECT * FROM external_table for update;

INSERT INTO external_table SELECT * FROM ext;

外部文件可有多个，可在多个目录，多个节点上。

CREATE EXTERNAL TABLE tbl(i INT) AS COPY (i) FROM’path1’ ON node1, ‘path2’ ON node2;

通过参数ExternalTablesExceptionsLimit可以设置外部表数据的异常，默认值是100，可设置为-1，代表无限制。

14. MERGE语句

MERGE INTO locations l USINGnew_locations n

ON (l.customer_id = n.customer_id

AND l.location_x = n.location_x AND

l.location_y = n.location_y)

WHEN MATCHED THEN UPDATE SETlocation_count = l.location_count + n.location_count

WHEN NOT MATCHED THEN INSERT(customer_id, location_x, location_y, location_count, location_name)

VALUES(n.customer_id, n.location_x, n.location_y, n.location_count, n.location_name);

15. 主键重复分析：

ANALYZE_CONSTRAINTS

16. Operating the Database

命令行停止数据库：/opt/vertica/bin/admintools -t stop_db -d VMart

17. Working with Tables

创建基本表

使用/* +direct */可将表之间创建在disk（ROS），示例：

create table StateBird as/+direct/ select State, Bird from states;

create temp table StateTax ON COMMITPRESERVE ROWS as /+direct/ select State, Tax from states;

projection creation：

使用CREATE TABLE时，projection的创建情况如下：

1.当创建表，而不提供projection相关的语句时，当使用insertinto或者copy命令插入数据时，系统自动创建superprojection

2.当使用CREATE TABLE AS SELECT创建表时，创建后projection立即创建

3.当使用下面的参数创建表时，默认的projection会立即创建

 column-definition (ENCODINGencoding-type and ACCESSRANK integer)

 ORDER BY table-column

 hash-segmentation-clause

 range-segmentation-clause

 UNSEGMENTED { NODE node | ALLNODES }

 KSAFE

创建临时表

分为GlobalTemporary Tables和Local Temporary Tables。

系统创建完临时表后，会立即创建默认的superprojection，除非指定了NOPROJECTION。

K-SAFE=0。

用户也可设置如下参数：

 column-definition (temp table)(ENCODING encoding-type and ACCESSRANK integer)

 ORDER BY table-column

 hash-segmentation-clause

 range-segmentation-clause

 UNSEGMENTED { NODE node | ALLNODES }

 NOPROJECTION

1查看K-Safety

SELECT current_fault_tolerance FROM system;

2.将vertica内部通信移到私有地址

步骤一.

修改hosts文件，添加私有地址列表

步骤二.

根据修改ip地址的文档进行修改

步骤三.

私有地址之间Ssh要实现无密码登陆，若以前配置的公有地址，需要删除密匙进行重建。

步骤四.

Spread需要重启。

3.projection

Vertica推荐在projection中进行编码，因为这个可以减少IO。

4.vertica数据大小估算

不会估算的数据包括：

l 多个projection的重复数据只会计算一次；

l 临时表数据不计算

l 外部表数据不计算

l 已删除但是还留在数据库中的数据

l 存储在wos的数据

l 系统表的数据

查看方法：

SELECT AUDIT(’’);—参数可以为空、schemas, tables, or partitions

SELECT GET_COMPLIANCE_STATUS();

5.约束

ANALYZE_CONSTRAINTS检查约束异常信息。

三种用法：

l 无参数：分析当前schema的所有表

l 表名：分析表

l 表名和列名：分析列

To remove the violation:

1 Save the originaldim rows that match the duplicated primary key:

CREATE TEMP TABLE dim_temp(pkinteger, x integer);

INSERT INTO dim_temp SELECT *FROM dim WHERE pk=1 AND x=1; – original dim row

2 Temporarily disable error messaging on duplicateconstraint values:

SELECTDISABLE_DUPLICATE_KEY_ERROR();

Caution: Remember that running theDISABLE_DUPLICATE_KEY_ERROR functionsuppresses the enforcement of data integrity checking.

3 Remove the original row that contains duplicate values:

DELETE FROM dim WHERE pk=1;

4 Allow the database to resume data integrity checking:

SELECTREENABLE_DUPLICATE_KEY_ERROR();

5 Reinsert the original values back into the dimensiontable:

INSERT INTO dim SELECT * from dim_temp;

COMMIT;

6Validateyour dimension and fact tables.

资源池：

select * from RESOURCE_POOLS;

PLANNEDCONCURRENCY：分配的内存/PLANNEDCONCURRENCY=每个进程能使用的最大能存

文档中

IO块设置为8k：53页

新文档第六页。

建立projection后需要REFRESH以下才会刷入数据。select REFRESH(‘projection_name’);

调整哪些系统参数？

手工执行dbd:第一个参数是已有的projection名称，第二个是生成的sql文件

selectdbd_design_projection_encodings(‘noas_app.FCT_FOURNET_2GCELL_EQUP_ANA_b0’,’/home/dbadmin/test/dbd_FCT_FOURNET_2GCELL_EQUP_ANA_b0.sql’,false);

WOS:内存结构，行式分布，数据先写入这里，再通过TM到ROS

ROS：select直接从ROS读取

调优：

1.OS系统参数–不多

2.系统负荷：CPU和IO

3.调整资源池

4.调整projection的压缩方式

每个节点上数据量

测一下 网络、磁盘

ifconfig丢包

阵列卡的缓存

由于Vertica数据库与Oracle在架构上有很大差别，请大家在后续开发过程中参考以下的方法做对应的SQL检查工作。

1、查询执行次数做多的10个SQL语句

SELECT request,

COUNT(*)

FROM query_requests

GROUPBY request

ORDERBYCOUNT(*)DESC

LIMIT 10;

在山东现网环境中，最多的SQL中有一条

Ø select count(*) from dual；执行次数高达39524，请大家关注下自己的脚本，有哪些地方使用了；

Ø select region_id as regionId, short_name as regionName from noas_app.dim_region where type = 1 or type = 2 orderby region_id；执行了3757；请应用开发人员确认下是否考虑缓存机制；

Ø select sum(td_tmn_sub_num)as tdTmnSubNum, sum(td_svc_sub_num) as tdSrvSubNum, sum(wlan_tmn_sub_num) aswlanTmnSubNum, sum(wlan_svc_sub_num) as wlanSrvSubNum fromnoas_app.fct_fournet_view_user_ana b where 1=1；未增加时间限制，执行了3576次；请应用开发人员考虑哪些脚本给写错了。

2、查询执行时间最长的10个SQL

SELECT request,
request_duration_ms
FROM query_requests
ORDER BY request_duration_ms DESC
LIMIT 10;

在山东环境中，更多的是Select * from table;判断多是开发人员做数据核查时候没有加LIMIT子句；请各位开发人员后续注意验证数据质量时候加上（避免无谓消耗数据库性能）；

3、查询memory消耗最多的10个SQL

SELECT request,
memory_acquired_mb
FROM query_requests
WHERE memory_acquired_mb IS NOT NULL
ORDER BY memory_acquired_mb DESC
LIMIT 10;

加载数据

1. 转换文件格式

iconv -f ISO88599 -t utf-8data2.txt > data2-utf8.txt

2. 指定文件加载路径

l Create a vsql variable containing your Linux current directory.

\set t_pwd pwd

l Create another vsql variable that uses a path relative to the Linuxcurrent directory variable for a specific data file.

\set input_file’’’:t_pwd’/Date_Dimension.tbl’’

l Use the second variable in the COPY statement:

COPY Date_Dimension FROM :input_file DELIMITER ‘|’;

数据库安装：

设置块大小：/etc/init.d/boot.local，文件添加下面内容：

mount/dev/sda7 /database

deadline> /sys/block/sda/queue/scheduler

0 >/proc/sys/vm/swappiness

/sbin/blockdev–setra 8192 /dev/sda

单节点恢复：

简化一下步骤吧：

1. 保证故障节点的IP地址和主机名与原来完全相同

2. 从集群其他节点复制 /etc/hosts到故障节点

3. 在故障节点上创建spreadd服务。方法是在故障节点上建立一个localhost的单节点集群，然后停止spreadd 服务。

/etc/init.d/spreadd stop

/etc/init.d/spreadd start

4. 从集群其他节点复制 /opt/vertica/config目录到故障节点并覆盖

tar -cvf/opt/vertica/config20140115.tar *

tar -xvf config20140115_196.tar -C/opt/vertica/config

注意，在第4部后还要改一个配置文件/etc/sysconfig/spreadd，修改其中的节点标识（实际上就是集群的私有IP地址）

SPREADARGS="-n N192168001101${SPREADARGS}"

5. 确保dbadmin用户从集群其他节点到故障节点的无口令ssh可用

cd ~

ssh-keygen -t rsa

chmod 755 ~/.ssh

cd ~/.ssh

cp id_rsa.pub authorized_keys2

chmod 600 ~/.ssh/*

ssh-copy-id -i id_rsa.pub dbadmin@10.214.8.198

ssh-copy-id -i id_rsa.pub dbadmin@10.214.8.197

ssh-copy-id -i id_rsa.pub dbadmin@10.214.8.196

ssh-copy-id -i id_rsa.pub dbadmin@10.214.8.199

6. 启动故障节点的spreadd服务

7. 在故障节点创建数据库目录，确保dbadmin用户可以读写

8. 从集群其他节点启动故障节点上的Vertica，等待故障节点完成恢复

上面第3步的安装日志：

ZF-VERTICA-2:/home/dbadmin #/opt/vertica/sbin/install_vertica -s 192.168.1.12 -rvertica-6.1.3-0.x86_64.SUSE.rpm -u dbadmin

Vertica Analytic Database 6.1.3-0Installation Tool

Upgrading admintools meta dataformat…

scanning /opt/vertica/config/users

Starting installation tasks…

Getting system information forcluster (this may take a while)…

backing up admintools.conf on 192.168.1.12

Default shell on nodes:

192.168.1.12 /bin/bash

Info: the package ‘pstack’ is usefulduring troubleshooting. Verticarecommends this package is installed.

Checking/fixing OS parameters…

Info. Readahead parameter is set toohigh for some devices on the host 192.168.1.12

Info. Consider adding the followinglines to /etc/rc.local file for better performance

blockdev --setra 2048 /dev/sda1 #old value 8192

blockdev --setra 2048 /dev/sda6 #old value 8192

blockdev --setra 2048 /dev/sda5 #old value 8192

blockdev --setra 2048 /dev/sda2 #old value 8192

blockdev --setra 2048 /dev/sda4 #old value 8192

blockdev --setra 2048 /dev/sda3 #old value 8192

Setting vm.min_free_kbytes to 135168…

Info! The session setting ofpam_limits.so is not set in /etc/pam.d/su

Setting session of pam_limits.so in/etc/pam.d/su …

Setting virtual memory space tounlimited

Detected cpufreq module loaded on192.168.1.12

CPU frequency scaling isenabled. This may adversely affect theperformance of your database.

Vertica recommends that cpufrequency scaling be turned off or set to ‘performance’

Creating/Checking Vertica DBA group

Creating/Checking Vertica DBA user

Installing/Repairing SSH keys fordbadmin

Creating Vertica Data Directory…

Updating spread configuration…

Verifying spread configuration onwhole cluster.

Creating node node0001 definitionfor host 192.168.1.12

… Done

Error Monitor 0 errors 4 warnings

Installation completed withwarnings.

Installation complete.

To create a database:

1. Logout and login as dbadmin.**

2. Run /opt/vertica/bin/adminToolsas dbadmin

3. Select Create Database from theConfiguration Menu

** The installation modified thegroup privileges for dbadmin.

If you used sudo to install vertica as dbadmin, you will

need to logout and login again before the privileges are applied.

ZF-VERTICA-2:/home/dbadmin # adminTools

常见操作：

导出表数据为文本文件：

vsql -d noas -U dbadmin -Atq -w noas-c “select * from noas_app.fct_fournet_2gcell_equp_ana”>/app/datastage/dim_all/fct_fournet_2gcell_equp_an

导入vertica 导入：

copynoas_app.fct_fournet_2gcell_equp_ana from’/app/datastage/dim_all/fct_fournet_2gcell_equp_ana’ delimiter ‘|’;

copynoas_dw.temp_se_cm_flux_d_0_testx19 from ‘/database/datastage/jyfx/resgsn/TS_BASS_PM_GSNCDR_SGSN_D_20131023*’ onv_noas_node0004 delimiter ‘|’ direct;

– 创建ODM用户资源池，主要做外部表查询

create resource pool pool_noas_odmMAXMEMORYSIZE ‘50%’ EXECUTIONPARALLELISM AUTO PRIORITY 0 QUEUETIMEOUT NONEPLANNEDCONCURRENCY 12;

– 创建DW用户资源池，主要做大表加载、关联、汇总

create resource pool pool_noas_dwMAXMEMORYSIZE ‘90%’ EXECUTIONPARALLELISM AUTO PRIORITY 0 QUEUETIMEOUT NONEPLANNEDCONCURRENCY AUTO;

– 创建APP用户资源池，主要做查询、表关联、指标运算

create resource pool pool_noas_appMAXMEMORYSIZE ‘80%’ EXECUTIONPARALLELISM AUTO PRIORITY 0 QUEUETIMEOUT NONEPLANNEDCONCURRENCY 24;

CREATE USER “user_dw” WITH PASSWORD ‘cmcc’;

– 资源池范例：低并发大查询

CREATE RESOURCE POOL l_poolQUEUETIMEOUT NONE PLANNEDCONCURRENCY 6 MAXCONCURRENCY 4;

– 资源池范例：高发小查询

CREATE RESOURCE POOL s_poolMEMORYSIZE ‘1G’ EXECUTIONPARALLELISM 4 PRIORITY 10 QUEUETIMEOUT NONEPLANNEDCONCURRENCY 36 MAXCONCURRENCY 50;

GRANT ALL ON RESOURCE POOL l_pool TO user_dw;

ALTER USER user_dw RESOURCE POOLl_pool;

GRANT ALL ON SCHEMA DW TO user_dw;

– 创建用户

create user "dev_noas_odm"identified by ‘noas_odm’ resource pool pool_noas_odm;

create user "dev_noas_dw"identified by ‘noas_dw’ resource pool pool_noas_dw;

create user "dev_noas_app"identified by ‘noas_app’ resource pool pool_noas_app;

– 创建schema

create schema if not existsnoas.noas_odm authorization dev_noas_odm;

create schema if not existsnoas.noas_dw authorization dev_noas_dw;

create schema if not existsnoas.noas_app authorization dev_noas_app;

– 跨用户赋予表查询权限

grant all on 表名 to dev_noas_dw

----添加主键

alter table noas_app.DIM_APN_INFOadd CONSTRAINT cons_DIM_APN_INFO_pk primary key(apn_code);

vertica文档：

C)PzN1oxxrVf

selectadd_location(’/database/datastage/sjwg/cxipmanperformance’, ‘v_noas_node0002’,‘USER’);

grant read on location ‘/database/gn_xita/gn_washed’ onv_noas_node0002 to dev_noas_odm;

1. 加载数据

1.1使用pload

需要vertica单独安装，安装步骤：

cd /opt/vertica/packages/pload/ddl

vsql -f install.sql

卸载步骤：

cd /opt/vertica/packages/pload/ddl

vsql -f uninstall.sql

安装完成后，使用方法：

copy table_name with sourcePloadDelimitedSource(file=’/tmp/ploadtest.dat’, chunk_size=integer,nodes=‘node_name’)DELIMITER ‘|’;

参数介绍：

chunk_size:文件分割大小

注意：

不支持压缩文件；

文件须在服务器上；

2. 数据导出

EXPORT_CATALOG：

l 导出Schemas, tables, constraints, views, and projections

selectexport_catalog(’/home/dbadmin/test/sql_cat_design1.sql’,‘DESIGN’);

l 导出All design objects和DBD创建的系统对象

selectexport_catalog(’/home/dbadmin/test/sql_cat_design2.sql’,‘DESIGN_ALL’);

l 导出All tables, constraints, and projections.–实测没有导出projections

selectexport_catalog(’/home/dbadmin/test/sql_cat_design3.sql’,‘TABLES’);

EXPORT_TABLES:

l 导出所有非虚拟对象，包括约束（实测导出SCHEMA、SEQUENCE、TABLE）

selectEXPORT_TABLES(’/home/dbadmin/test/sql_exp_table1.sql’,’’);

l 导出单个表定义

selectEXPORT_TABLES(’/home/dbadmin/test/sql_exp_table1.sql’,‘noas_app.dim_city’);

l 导出多个表定义

select EXPORT_TABLES(’/home/dbadmin/test/sql_exp_table3.sql’,‘schem.t1,schem.t2’);

l 导出单个scheme对象

selectEXPORT_TABLES(’/home/dbadmin/test/sql_exp_table4.sql’,‘schema’);

EXPORT_OBJECTS

l 导出所有对象

select export_objects(’/home/dbadmin/test/sql_objects1.sql’,’’,‘true’);

l 导出单个表定义和projection

select export_objects(’/home/dbadmin/test/sql_objects2.sql’,‘noas_app.dim_city’,‘true’);

l 导出单个schema的所有对象

selectexport_objects(’/home/dbadmin/test/sql_objects3.sql’,‘noas_app’,‘true’);

3. 删除数据

对于已删除的数据，暂时还会保留，真正物理删除的策略VERTICA提供两种：

l 指定删除数据的保存时间

SELECT SET_CONFIG_PARAMETER(‘HistoryRetentionTime’,’{|-1}’);

Seconds表示删除数据的保存时间

-1表示不使用HistoryRetentionTime参数来设置删除数据。

l 指定删除数据的保存数量

VERTICA不建议使用此方式来删除数据，使用此方式的配置步骤如下：

首先设置HistoryRetentionTime为-1：

SELECT SET_CONFIG_PARAMETER(‘HistoryRetentionTime’,’-1’);

第二步设置HistoryRetentionEpochs参数：

SELECT SET_CONFIG_PARAMETER(‘HistoryRetentionEpochs’,’{<num_epochs>|-1}’);

其中-1表示不使用此参数。

如果要保留所有的历史删除数据，需要将两个参数都设置为-1：

SELECTSET_CONFIG_PARAMETER(‘HistoryRetentionTime’, ‘-1’);

SELECTSET_CONFIG_PARAMETER(‘HistoryRetentionEpochs’, ‘-1’);

另外VERTICA还支持手工删除历史数据，可按照下面三步执行：

A. 确定要删除数据的点

B. 使用AHM设置到要删除数据的点

SET_AHM_TIME(): 设置AHM到时间戳指定的的epoch

SET_AHM_EPOCH(): 设置AHM到特定的epoch

GET_AHM_TIME():返回AHM代表的时间戳

GET_AHM_EPOCH():返回AHM代表的epoch的数量

MAKE_AHM_NOW():设置AHM到最大值，这样可以删除所有历史数据

C. 使用函数删除数据

PURGE_PROJECTION():删除指定的projection

PURGE_TABLE():删除指定的table的所有projections

PURGE():删除schema所有projections

4. 节点管理

l 灵活集群

增加、删除节点不需要关闭或重启数据库

数据平衡可多次执行

l 否

5. SESSION管理

查看锁表：

select*fromlocks;–获取transaction_id字段

select * from sessions where transaction_id in();–将上面获取的transaction_id带入,查看transaction_start，判断是否是以前锁的

selectCLOSE_SESSION (‘sessionid’ )–带入上面查出来的session_id

系统表介绍

1. load_streams

可查看数据加载记录，包括活动的和历史的。

本表可查看数据加载时间、时长、加载成功条数、拒绝条数、加载进度、读取字节。

后记：一般情况下用的最多是导数据，查session。