Vertica—操作汇总

官网地址：https://www.vertica.com/
官方文档：https://www.vertica.com/documentation/vertica/12-0-x-documentation/
驱动下载：https://www.vertica.com/download/vertica/client-drivers/

1、概念简介

Vertica是由关系数据库大师Michael Stonebraker(2014 年图灵奖获得者)所创建，于2011年被HP（惠普）收购并成为其核心大数据平台软件。

在国内 vertica和部分厂商进行合作，进行云解决方案的实施；

1.1、新华三 oneStor5.0

产品介绍：https://www.h3c.com/cn/Service/Document_Software/Document_Center/H3Cloud/Catalog/H3C_ONEStor/ONEStor5.0/

ONEStor分布式存储采用全分布式的融合架构，由硬件平台、存储操作系统、分布式管理集群、基础服务、增值服务等模块组成，为存储系统的可靠性、可用性、自动运维、高性能等方面提供了有力保证。2021年底，新华三集团全新发布了ONEStor 5.0，在上一代基础上重构底层软件架构，引入对持久化内存的支持，打造全栈优化产品。全新发布的ONEStor 5.0关键优化技术包括：

• DCache 加速：高性能的DCache分布式缓存存储加速引擎，通过智能数据预取技术提高缓存命中率，大幅提升存储系统性能，极速响应关键业务。

• 在线实时重删压缩：全面支持软硬件加速技术，并提供在线和离线重删压缩服务。指纹计算和压缩并行处理，显著简化处理逻辑，满足实时在线处理性能需求。

• 多站点容灾：提供对象存储多站点技术，可轻松实现两中心、三中心多套分布式集群，互为容灾对象，实现多站点的业务容灾。

• 提升固态盘耐用性：通过IO聚合，将随机小块IO聚合成顺序大块IO，再刷入SCM或NVME等固态存储介质，在高并发的情况下提供了稳定的 IO 性能，并显著提升大容量固态盘的耐用性。

• 大比例纠删码提高磁盘使用率：在支持传统的副本、纠删码的基础之上，提供最大比例可支持22+2的大比例纠删码，磁盘使用率高达91.6%。在保证数据可靠性的前提下，为客户带来更高得盘率。

1.2、核心设计

Vertica采用无共享的MPP架构，基于工业标准的x86服务器，拥有高可扩展性。Vertica集群中的所有节点100%对等，集群中没有主节点或其他共享资源。通过增加节点，就可以线性地扩展集群的计算能力和数据处理容量。而且，Vertica集群的节点数量几乎可以无限制地扩展。

• Vertica是真正的列式数据库

1）列式存储：Vertica不仅仅按列式存储数据，还主动地根据列数据的特点和查询的要求选用最佳的算法对数据进行排序和压缩，这就极大地降低磁盘I/O消耗。
2）列式引擎：Vertica的执行引擎和优化器也是基于列式数据库设计的，压缩过的列数据在Vertica的执行引擎中进行过滤、关联、分组等操作时不需要解压缩，从而大大降低了CPU和内存消耗。

• 高可用

Vertica 通过维护数据的多个冗余备份来实现高可用性。Vertica保证冗余数据被散列存储在不同的结点上，从而内置了智能数据镜像功能。Vertica 将其称之为K级系数安全性（K-safety），K指的是Vertica能够容忍的可能发生故障的任意结点的个数。

• 支持延迟物化技术

1）对于大多数的分析查询而言，往往只需要获取所有列数据的一个子集。Vertica列式优化器和执行引擎可以在列式存储中跳过无关的列，从而节省了大量的I/O资源消耗。
2）与其他宣称支持列存储的数据库不同，Vertica不仅仅是简单地将数据按列存储，它还会自动根据查询的要求和数据的特点主动选择合理的排序方式和压缩算法，降低数据所占的存储空间，从而降低查询的I/O消耗，进一步提升查询性能。

• 支持延迟解压缩技术。

Vertica的优化器和执行引擎充分利用列式计算技术，支持在查询条件和关联中直接访问数据编码后的值，而不需要先解码。这样Vertica就可以大大节省在数据查询期间的CPU开销，进而提升整体的查询性能。

• 主持主动压缩

与其他宣称支持列存储的数据库按照数据插入的向后顺序来存储的方式不同，Vertica会根据每个列的数据类型、基数和查询特点，自动选择适用的排序方式和压缩算法，以尽可能减少数据所占的存储空间，降低查询的I/O消耗以提升查询性能。

Vertica的数据压缩比通常可达10:1。从I/O资源消耗节约的角度来看，对I/O是主要瓶颈的分析系统而言，相较于传统的行式数据库，Vertica的主动压缩技术可以带来约一个数量级的性能提升。

• 无中心式架构

Vertica集群中的所有节点完全对等，不需要主节点，数据加载、数据导出和查询都可以并行地在所有节点同时执行。由于没有资源共享，增加节点就可以线性地扩展Vertica的数据容量和计算能力，可以轻松从几个节点到上千节点、或从几个TB到数10PB规模扩展和收缩，满足业务规模增长的要求。

Vertica支持一键式在线群集扩展。可一键加入和删除节点并自动完成数据重分布，扩展过程不需要中断正在运行的业务。

2、环境部署

Vertica可以部署在主流的x86工业化标准服务器上，它也可以运行在Amazon Elastic云计算平台上（Amazon EC2）。Vertica同样支持虚拟化，能够作为云计算服务的提供者。

• 版本查询

select version();

# Vertica Analytic Database v12.0.1-0

2.1、集群搭建

2.2、集群操作

1.View the status of your cluster. It should return an empty table.

$ admintools -t view_cluster

2.Create a database called “vdb” in the home directory with the password “vertica”.
This command also sets the plaintext password “vertica” for both the database and
dbadmin.

$ admintools -t create_db --data_path=/home/dbadmin --catalog_path=/home/dbadmin --
database=vdb --password=vertica --hosts=localhost

3.Run vsql and enter “vertica” at the password prompt

$ vsql

4.Create a user named “Mike” with the password “inventor.”

=> CREATE USER 'Mike' IDENTIFIED BY 'inventor';

2.3、集群监控

• 对象空间统计

select projection_schema as schema_name,
       anchor_table_name as object_name,
       to_char(sum(used_bytes) / 1024 / 1024 / 1024, '999,999.99') as disk_space_used_gb
  from projection_storage
 group by schema_name, object_name
 order by disk_space_used_gb desc 
 limit 50;

• 磁盘信息

select node_name,
       storage_path,
       storage_usage,
       disk_block_size_bytes,
       round((disk_space_used_blocks + disk_space_free_blocks) * 4 / 1024,
             2) as total_mb,
       disk_space_used_mb,
       disk_space_free_mb,
       storage_status,
       disk_space_free_percent
  from disk_storage;

If possible, always run the Administration Tools using the database administrator account
(dbadmin) on the administration host.

3、使用操作

标识符：长度为1~128个字节，标识符不区分大小写；

• delete禁止加别名

delete from $schema_name.target_object t
where 1=1 and ${condition};

3.1、建数据库

命名规范：
数据库名称必须介于 1-30 个字符之间，以字母开头，后面跟着字母（大写和小写）、数字和/或下划线的任意组合。

说明： 数据库名称区分大小写，所以不建议创建只是名称大小写差异的库；

3.1.1、密码策略

数据库超级用户可以使用ALTER 配置文件更改 Vertica 用户的最大密码长度、重用时间、最小位数或所需位数以及其他详细信息。

• 策略更改

ALTER PROFILE name LIMIT [ 
    PASSWORD_LIFE_TIME setting
    PASSWORD_MIN_LIFE_TIME setting
    PASSWORD_GRACE_TIME setting
    FAILED_LOGIN_ATTEMPTS setting
    PASSWORD_LOCK_TIME setting
    PASSWORD_REUSE_MAX setting
    PASSWORD_REUSE_TIME setting
    PASSWORD_MAX_LENGTH setting
    PASSWORD_MIN_LENGTH setting
    PASSWORD_MIN_LETTERS setting
    PASSWORD_MIN_UPPERCASE_LETTERS setting
    PASSWORD_MIN_LOWERCASE_LETTERS setting
    PASSWORD_MIN_DIGITS setting
    PASSWORD_MIN_SYMBOLS setting
    PASSWORD_MIN_CHAR_CHANGE setting ]

单条密码策略更新：

ALTER PROFILE DEFAULT LIMIT PASSWORD_MIN_SYMBOLS 1;

3.2、权限管理

3.2.1、角色权限

配置参数指定是否在登录时为所有数据库用户启用所有角色。默认情况下，此参数设置为 0。如果设置为 1，则 Vertica将启用所有用户登录到数据库时的角色。EnableAllRolesOnLogin

vertica预定义的角色有6类，分别如下：

决策	权限
dbadmin	系统管理员角色
dbduser
public	公共角色，新用户默认被赋予的角色
sysmonitor	系统监控者
UDXDEVELOPER
PSEUDOSUPERUSER

GRANT DBADMIN TO ${user_1}<,${user_2}>WITH ADMIN OPTION;

说明：此处加了 with admin option 可以对将该角色赋予其他用户；

• 角色删除

drop role ${role_name} <cascade>;

说明：当角色被赋予其他用户时，需要加cascade强制删除；

• 权限撤回

revoke <admin option for > ${rolename}<,role_2,role_3,..> from ${grantee}  <cascade>;

说明：
1）加 admin option for 时，撤回被授予者给其他用户或角色赋权该角色的权限；
2）此处被撤回权限的角色可以写多个；

• 启用/禁用角色

---启用该用户单个角色
set role ${rolename};
---启用该用户所有角色
set role all;

---禁用该用户所有角色
set role none;

• 默认角色

alter user ${username} default ${rolename};
---清楚用户默认会话
alter user ${username} default none;

说明：默认角色在会话开始时默认启用；

3.2.2、用户权限

• 创建schema

create schema if not exists ${schemaName} authorization ${userName};

• 修改scehma

alter schema ${schemaName} rename to ${schemaNameNew};

3.2.3、对象赋权

• 赋权语法

grant  { select| update | all [ privileges ] }
 on {schema.object | all tables in schema}
 to {username | role | public } [ with grant option];

3.3、对象操作

3.3.1、表操作

vertica建表可以不指定segment表达式，默认会按照表的所有列来进行数据分布；这样会出现一个问题，当需要对该表进行DDL操作时，会报错不允许对 segment表达式用到的列进行操作；
解决办法：建表时指定 segment用到的列，可以设置主键为segment，这样其他列就可以正常进行DDL 操作；

• ddl语法

create table schema_name.object_name
(
  column_1 data_type,
  column_2 data_type,
  ...
  column_n data_type
)
ORDER BY column_x
SEGMENTED BY HASH(column_1,column_2) <ALL NODES|Offset offset| Nodes node1,node2,...noden> KSAFE 1;

• 修改属主

alter table schemaName.tableName owner to ${newUser};

• 修改表名

alter table schemaName.tableName rename to tableName2;

• 删除表约束

alter table schemaName.tableName drop constraint pkName;

• 新增表约束

alter table schemaName.tableName add constraint pkName primary key(field1,field2);

• 新增表字段

alter table schemaName.tableName add column ${columnName} ${datatype} <default ${defaultVaule}>;

• 删除表字段

alter table schemaName.tableName drop column ${columnName};
说明：这里列只能单个删除；

• 修改表字段名

alter table schemaName.tableName rename column ${columnName} to field2;

• 设置字段不为空约束

alter table schemaName.tableName alter column ${columnName} set not null;

• 删除字段不为空约束

alter table schemaName.tableName alter column ${columnName} drop not null;

• 更改字段数据类型

alter table schemaName.tableName alter column ${columnName} set data type ${dataType};

3.3.2、查询操作

历史查询：

• 子查询需要加别名

select h.col_1,h.col_1,...
from (select a.col_1,a.col_2 from xxxx) h;

• 查询优化
  数据类型优化：float 调整 为 numeric，有效位数为18位以内；

3.4、数据同步

当前 vertica 的数据同步主要是按照 jdbc 的 copy 接口实现；

3.5、函数

常用函数

• hash_role($username,$rolename)
  说明：返回 布尔值，判断用户是否具备某个角色；

• greatest(null,value_1,value_2)

• least(null,value_1,value_2)
  说明：以上2个函数，当有空值时，结果为空；需要进行空值判断和转换；

• row_number() over(partition by column_p order by column_o)
  说明：分析函数的排序列有空值时，空值会被排在第一位，需要进行空值判断和处理；

• months_between(date_1,date_2) 和 AGE_IN_MONTHS(date_1,date_2)
  说明：AGE_IN_MONTHS函数返回的是整形结果；

• analyze_statistics(schema_name.table_name, ‘column‑list’ ,col_percent)
  说明：统计信息收集，此处参数2和3可以默认；

• uuid_generate()
  说明：该函数返回的是带 “-” 的36位随机UUID值，去掉"-"为32位字符；

• 导出函数
  export_objects
  export_tables

• projection函数

SELECT get_projections('sc_ty.hxzg_dj_nsrxx')

说明：该函数可以查询表的投影信息；

（一）、系统信息函数

• current_database()
• current_schema()
• current_user()
• dbname()
  说明：获取数据库名称；
• user()
• username()
• session_user()
  说明：获取会话或登录用户名；
• version()
  说明：获取vertica集群版本

自定义函数

在 vertica 中自定义的 pgsql函数只支持简单的单个表达式类型的函数，复杂函数需要使用java/python写 udf 函数；

3.6、系统表/视图

• databases

select t.* from databases t
order by t.database_id;

• database_backups

select t.* FROM database_backups t;

• database_snapshots
  说明：数据库快照

• keywords
  说明：使用场景比较少，查询vertica的关键字；

• nodes
  说明：查询 vertica集群的节点信息；

• network_interfaces
  说明：查询网卡接口信息

/-----------------会话相关----------------------------------------------/

• user_sessions

select node_name,
       user_name,
       session_id,
       transaction_id,
       statement_id,
       session_start_timestamp,
       session_end_timestamp,
       is_active,
       client_hostname,
       client_pid,
       client_label,
       client_type,
       client_version,
       client_os,
       client_os_user_name
  from user_sessions s;

• sessions
  说明：查询集群内各个节点每个用户下会话信息；

/---------------对象相关-------------------------------------------------/

• tables

select owner_id,
       owner_name,
       table_schema_id      as schema_id,
       table_schema         as schema_name,
       table_id,
       table_name,
       is_temp_table,
       is_system_table,
       create_time,
       table_definition,
       partition_expression,
       storage_mode,
       is_shared
  from tables t
 where 1 = 1
   and schema_name = ''
   and table_name = '';

• views
  说明：系统视图里的 table_name实际是 view_name；

• view_tables
  说明：系统视图里的 table_name实际是 view_name；可以查询视图引用的物理表；

• view_columns
  说明：系统视图里的 table_name实际是 view_name；可以查询视图的列信息；

• all_tables
  说明：该系统表里包含视图的信息，table_type可以区分是表还是视图；

• columns

• comments

• ODBC_COLUMNS
  说明：查询对象列信息；

• projection_columns
  说明：查询对象列统计附加信息

• sequences
  说明：查询库中的序列信息；

• projections

select owner_id,
       owner_name,
       projection_schema_id         as schema_id,
       projection_schema            as schema_name,
       projection_id,
       projection_name,
       projection_basename,
       anchor_table_id,
       anchor_table_name,
       node_id,
       node_name,
       create_type,
       has_statistics,
       is_segmented,
       segment_expression,
       segment_range,
       is_super_projection,
       is_key_constraint_projection,
       has_expressions,
       is_aggregate_projection,
       partition_range_min,
       partition_range_max
  from v_catalog.projections o;

说明：该系统表或视图可以看到每个对象的 segment 表达式列；

• projection_storage

---query_sql
select t.node_name,
       t.projection_schema,
       t.projection_name,
       t.projection_column_count,
       row_count,
       used_bytes,
       anchor_table_schema,
       anchor_table_name
  from v_monitor.projection_storage t
 where 1 = 1
   and t.anchor_table_schema = '${schema_name}'
   and t.anchor_table_name = '${object_name}';

说明：查询对象的投影数据存储占用；

• table_constraints
  说明：查询表的约束信息；

• PRIMARY_KEYS
  说明：查询表的主键列信息；

• constraint_columns
  说明：查询表的约束列信息；

• user_procedures
  说明：vsql存过只能写简单计算的函数；

• user_functions

• system_tables
  说明：列出了库中的系统表；

• system_columns
  说明：列出了库中的系统表的列信息；

/---------------用户相关------------------------------------------------/

• users

select u.user_id,
       u.user_name,
       profile_name,
       u.is_super_user,
       u.is_locked,
       u.lock_time,
       u.memory_cap_kb,
       u.temp_space_cap_kb,
       max_connections,
       idle_session_timeout,
       all_roles,
       default_roles,
       search_path
  from users u
 order by u.user_id;

说明：users里可以查询到用户的默认配置文件名称；

• roles

select * from roles;

• schemata
  说明：查询 vertica 中的 schema信息；

/---------------权限相关-------------------------------------------------/

• grants

select grantor_id,
       grantor,
       privileges_description,
       object_schema,
       object_name,
       object_type,
       grantee_id,
       grantee
  from grants t;

/---------------配置相关-------------------------------------------------/

• profiles
  说明：查询配置文件列表；
• profile_parameters
  说明：查询配置文件参数信息；

3.7、统计信息

Vertica 有三种方式可以手动收集统计信息：
1）ANALYZE ROW COUNT：它是一种轻量级的自动的默认每 60 秒收集一次 projection 行数以及加载数据的最小化统计信息和行数。默认的收集间隔可以有参AnalyzeRowCountInterval
控制，也可以执行 DO_TM_TASK (‘analyze_row_count’)过程进行手动收集。

2）ANALYZE_STATISTICS：它收集数据库、表或者列上完整的统计信息，有以下特点：

• 允许对表进行基于列的统计信息分析，以提升性能。
• 进行快速采样，加速包含大量列的小表的分析
• 包含 WOS 中的数据
• 重组删除数据，而不是忽略删除标记
• 允许你通过 CTRL-C 或者 INTERRUPT_STATEMENT()取消正在执行的收集过程
• 记录最近的统计信息收集时间，以便后续收集过程能够得到优化

3）ANALYZE_HISTOGRAM：事实上 ANALYZE_STATISTICS()是 ANALYZE_HISTOGRAM
别名，它们主要的不同之处是 ANALYZE_HISTOGRAM 允许指定从磁盘读取数据的百分比，由此可以获得更多的采样精度和速度的控制。默认的采样百分比是 10%，可以指定 1~100%的值，另外也可以指定 smaller 或者 larger 百分比，指定了百分比参数之后统计信息的精度和收集速度都会受到影响。

select ANALYZE_STATISTICS('${schema_name.object_name}');

• 删除统计信息

select drop_statistics('${schema_name.object_name}');

3.8、性能优化

执行计划

此处内容待补充；

5、导出导入

5.1、导出相关

Vertica提供了多个EXPORT_函数，允许在目标数据库中重新创建数据库或特定架构和表。

• export_catalog(path, scope)
  说明：EXPORT_CATALOG创建最全面的 SQL 脚本，而EXPORT_TABLES和EXPORT_OBJECTS是该函数的子集，以缩小导出范围。
  scope的可以选择 TABLES、DESIGN、DIRECTED_QUERIES；

• export_objects(path, scope, mark-ksafe)
  说明：导出的sql脚本仅包括用户有权访问的对象。

• export_tables(path, scope)
  说明：export_tables 不会导出视图，即使指定视图也会被忽略；导出的sql脚本仅包括用户有权访问的对象。

5.2、导入相关

6、运维监控

• 会话操作

---关闭指定会话
select close_session('session_id');
---关闭当前用户会话
select close_all_sessions();
---关闭指定用户会话
select close_user_sessions('username');

============================================= over ============================================