vertica 8.0 新特性

前言:

<<line>> 表明在vertica 8.0文档中的title

 

正文:

1-支持平台

1.1-操作系统

<<Vertica Server and Vertica Management Console>>

When there are multiple minor versions supported for a major operating system release, Hewlett Packard Enterprise recommends that you run Vertica on the latest minor version listed in the supported versions list. For example, if you run Vertica on a Red Hat Enterprise Linux 6.x release, Hewlett Packard Enterprise recommends you upgrade to or be running the latest supported RHEL 6.x release, which is 6.8.

官方推荐rhel6.8或centos6.8版本

 

1.2- 文件系统

Vertica Analytic Database Enterprise Edition has been tested on all supported Linux platforms running the ext4 file system. For the Vertica Analytic Database I/O profile, the ext4 file system is considerably faster than ext3.

官方推荐ext4方式，不要使用LVM

 

1.3-hadoop支持

<<Vertica Integrations for Hadoop>>

支持CDH 5.6-5.8,其中5.6逐渐不再支持

 

1.4-kafka支持

<<Vertica Integration for Apache Kafka>>

支持Kafka 0.8.x-0.9,其中0.8.x逐渐不再支持

 

1.5-java/R支持

jdk 1.6-1.8

R语言3.0

 

2-新特性-

2.1-机器学习的资源池单独设置

<<New Resource Pool for BLOBDATA>>

blobdata资源池的设置

 

2.2-异常值检测

outliner过程

<<DETECT_OUTLIERS>>

基于标准差(待定是不是标准差)来筛选数据

If data point > ( value * STDDEV( data ) ) then data point is an outlier.

dbadmin=> SELECT * FROM baseball_roster;

id | last_name | hr | avg

----+-----------+--------+--------

1 | Polo | 7 | 0.233

2 | Gloss | 45 | 0.17

3 | Gus | 12 | 0.345

4 | Gee | 1 | 0.125

5 | Laus | 3 | 0.095

6 | Hilltop | 16 | 0.222

7 | Wicker | 78 | 0.333

8 | Scooter | 0 | 0.121

9 | Hank | 999999 | 0.8888

10 | Popup | 35 | 0.378

(10 rows)

 

dbadmin=> SELECT * FROM baseball_outliers;

id | last_name | hr | avg

----+-----------+--------+--------

7 | Wicker | 78 | 0.333

9 | Hank | 999999 | 0.8888

(2 rows)

 

2.3-机器学习相关

2.3.1-期初导入

<<Downloading the Machine Learning Example Data>>

yum install git

git clone https://github.com/vertica/Machine-Learning-Examples

vsql -d dbname -f load_ml_data.sql

 

2.3.2-训练模型

<<NAIVE_BAYES>>

#训练基础表,党派人士的投票倾向

select * from public.house84_train;

#训练model//训练基础表//要预测哪一列//基于那些数据来预测//排除非数据列

SELECT NAIVE_BAYES

('naive_house84_model', 'house84_train', 'party', '*','--exclude_columns="party, id"');

 

2.3.3-验证模型

<<Classifying Data Using Naive Bayes>>

SELECT SUMMARIZE_MODEL('naive_house84_model', 'dbadmin');

基于模型来检验是民主党还是共和党

CREATE TABLE predicted_party_naive

AS SELECT party,

PREDICT_NAIVE_BAYES (vote1, vote2, vote3, vote4, vote5,

vote6, vote7, vote8, vote9, vote10, vote11, vote12, vote13, vote14,vote15, vote16

USING PARAMETERS model_name = 'naive_house84_model',owner = 'dbadmin',type = 'response') AS Predicted_Party

FROM house84_test;

 

 

每个预测的可能性百分比

SELECT PREDICT_NAIVE_BAYES_CLASSES (id, vote1, vote2, vote3, vote4, vote5,

vote6, vote7, vote8, vote9, vote10,

vote11, vote12, vote13, vote14,

vote15, vote16

USING PARAMETERS model_name = 'naive_house84_model',

owner = 'dbadmin',

key_columns = 'id', exclude_columns = 'id',

classes = 'democrat, republican')

OVER() FROM house84_test;

 

基于训练集的前三个vote,来预测党派

SELECT party, PREDICT_NAIVE_BAYES (vote1, vote2, vote3

USING PARAMETERS model_name = 'naive_house84_model',

owner = 'dbadmin',

type = 'response') AS Predicted_Party

FROM house84_test;

 

 

 

2.3.4-不均衡数据-rebalance(也许用作多次取数,再取平均值?)

使用下采样(undersampling)剔除一些样本

SELECT BALANCE('balance_fin_data', 'transaction_data', 'fraud', 'weighted_sampling');

 

 

2.3.5-数据标准化-normalizaing

标准化的目的,在于将不同等级的数据归到一个级别

工作年数和现在薪资的范围差距很远,5-30年和3W-30W

Vertica提供了两种标准化方法

大小范围(minmax):将值最大最小设置为1和0,让差距很大的两列,进而成为同一等级的数据[0,1]

标准分数(z-score):低于平均值为负数,反之则正

稳健标准分数(robust z-score):使用中位数(midnumber)而不是平均数(mean nunber)

 

SELECT NORMALIZE('balanced_salary_data', 'salary_data', 'current_salary, years_worked', 'minmax', '--key_columns="employee_id" ');

SELECT * FROM balanced_salary_data;

 

SELECT NORMALIZE('balanced_z_salary_data', 'salary_data', 'current_salary, years_worked', 'zscore', '--key_columns="employee_id" ');

SELECT * FROM balanced_z_salary_data;

 

SELECT NORMALIZE('balanced_robustz_salary_data', 'salary_data', 'current_salary, years_worked', 'robust_zscore', '--key_columns="employee_id" ');

SELECT * FROM balanced_robustz_salary_data;

 

2.3.6-小数据集-sampling

从大量数据中,抽取指定百分比的数据,Vertica并不保证数据的分布一定准确

CREATE TABLE baseball_sample AS SELECT * FROM baseball TABLESAMPLE(25);

这个特性也许可以用作测试环境的数据导入,但也有数据不确定的问题

 

 

 

2.4-节点宕机后的执行计划

<<Node Down Information>>

执行计划会显示,哪些节点的状态异常

Execute on: All Nodes Except [node_list]

 

2.5-vertica 通知信息

<<Monitoring Vertica Using Notifiers>>

可以通过create notifier来向kafka传输vertica的运行信息

 

2.6-metadata资源池

为catalog data和storage data structure服务的资源池,自动增减,最多75%总体池并提示信息

 

2.7-vertica management console监控平台的新功能

全面强化

 

2.8-其他新功能

2.8.1-可以在create table和union中使用label标签(约等于oracle的comment)了

2.8.2-新增bool函数(与/或/异或)判断

 

dbadmin=> select * from mixers;

model | colors

--------------+--------

advanced | blue

advanced | green

beginner | green

beginner | green

intermediate | blue

intermediate | blue

professional | blue

professional | green

(8 rows)

 

dbadmin=> SELECT model,

dbadmin-> BOOL_AND(colors= 'blue')AS two_blue,

dbadmin-> BOOL_OR(colors= 'blue')AS one_or_two_blue,

dbadmin-> BOOL_XOR(colors= 'blue')AS specifically_not_more_than_one_blue

dbadmin-> FROM mixers

dbadmin-> GROUP BY model;

model | two_blue | one_or_two_blue | specifically_not_more_than_one_blue

--------------+----------+-----------------+-------------------------------------

advanced | f | t | t

beginner | f | f | f

intermediate | t | t | f

professional | f | t | t

(4 rows)

 

2.8.3--除数为0改为报错,而不是0

 

 

2.8.4--备份

Backup, Restore, Recovery, and Replication

剩余空间不足以存放时,备份作业会报错

 

2.8.5--kafka优化

配置参数时,vertica会尝试连接并返回正确与否

报错提示的信息更加详细了

 

2.8.6--hdfs使用体验

能够直接读取hdfs文件了

COPY t FROM 'hdfs:///opt/data/file1.dat';

如果hive中的表是分区的,那么vertica也能导入外部表

CREATE EXTERNAL TABLE t (id int, name varchar(50), created date, region varchar(50)) AS COPY FROM 'hdfs:///path/*/*/*' ORC(hive_partition_cols='created,region');

 

2.8.7--服务器限制客户端的参数

MAXCONNECTIONS参数 限制用户连接数

IDLESESSIONTIMEOUT参数 限制空闲连接断开时间