Vertica系列:从一些细节看Vertica为什么是一个优秀的数据仓库平台

===========================================
对象名称可以长到128字符
===========================================
1. Vertica 中字段名/表名/视图名/projection名/schema名, 名称最长达128个字节, 所以我们不用费尽心思缩写表名和字段名.
2. Vertica中的名称在catalog系统表能区分出大小写, 但使用的时候是大小写不铭感的.

在Oracle中, 因为表名不能超过30个字符, 所以单词必须要用缩写, 缩写单词之间用下划线分割, 以便提升辨识度. 但放到数据仓库场景中, 因为数据仓库不像单一业务系统, 它一定会包含很多种业务的数据, 用30个字符起表名其实是很难的, 经常碰到用了下划线名字就超过30个字符, 不用下划线可读性又不好. Vertica没这个问题.

推荐的表名的命名规范为:
产品线_表类型_业务名_[特殊后缀]
产品线: 可以理解为产品线或BU
表类型有: Dim/Fact/Agg/Cfg/Stg/Med
特殊后缀, 一般是Err/Tmp/Bak或Bak_20171231这样, Err是某个表错误数据表, Tmp是临时表, Bak表是备份表(往往需要加上备份日期)
比如: CRM_Fact_UserCall_Tmp

===========================================
字段可设置default值, Default值支持 Sequence和函数
===========================================
1. 每个表都可增加一个 DWH_ID 代理主键字段, 可以使用sequence作为每行的unique字段, 而且是递增的, 这将对于后续的数据清洗非常有帮助. 另外该字段也非常适合作为分段字段.
2. 每个表都可增加一个 DWH_ITime/DWH_UTime 时间字段, 可以使用 sysdate() 作为其缺省值
3. 因为 null 取值的SQL查询写法比较特殊, 不能用等于或不等于比较符, 为了简化后期的分析的难度, 字段如果能设置not null就尽量设置, 配合default约束.

create sequence CRM.CRM_Fact_UserCall_Seq;
create table CRM.CRM_Fact_UserCall(
UserId varchar(50)
,CallType varchar(30) NOT NULL DEFAULT 'OnSite'
,DWH_ID numeric(38,0) DEFAULT nextval('CRM.CRM_Fact_UserCall_Seq')  
,DWH_ITime timestamp DEFAULT  sysdate() 
,DWH_UTime timestamp DEFAULT  sysdate() 
) 
order by UserId
SEGMENTED BY HASH(DWH_ID) ALL NODES  
;

===========================================
Vertica 强大的数据清洗能力
===========================================
实际项目中, 会遇到各类数据问题, 简单的问题可以用 like /ilike /case when 处理, 但复杂一些的问题最好是用正则表达式. 下面是常用的几个正则函数:
regexp_substr()
regexp_not_like()
regexp_like()
regexp_replace()

===========================================
简单够用的事务
===========================================
在ETL实现过程中, 经常会使用Delete+Insert的方式来更新数据, 如果维度表用了这种更新方式, 一旦事实表跑批正好是在维度表Delete和Insert动作之间, 就会出现数据质量问题. Vertica 支持事务操作.

不同的客户端工具, 有着不同的缺省的提交模式:
1. vsql 默认是关闭自动事务提交的.
2. 采用vertica jdbc/odbc连接的客户端工具, 默认开启自动事务提交的.

我们可以也可以手工修改事务提交模式:

    SET SESSION AUTOCOMMIT TO OFF;
    SET SESSION AUTOCOMMIT TO ON;
    begin transaction ;   --*甚至begin transaction都可以省略
    .... 
    end; -- 这里的 end 也可以用 commit 代替;

    -- 在关闭 AUTOCOMMIT 下, 回滚事务的方法
    begin transaction --*甚至begin transaction都可以省略
    .... 
    rollback; 

 

===========================================
表和字段支持comment
===========================================
SQL代码加上必要的comment, 对于系统维护帮助很大. 非常推荐为所有的视图级/表级/字段级加上comment, 这对于大型数仓运维意义重大.

COMMENT ON COLUMN customer_dimension_vmart_node01.customer_name IS 'Last name only';
COMMENT ON TABLE promotion_dimension IS '2011 Promotions';
COMMENT ON VIEW curr_month_ship IS 'Shipping data for the current month';

===========================================
临时表
===========================================
在ETL过程中经常会用到临时表, 尤其是Vertica的local 临时表更是经常使用, 可以加上Order by 子句和 segment 子句.

drop table if exists Tmp_Table;
create local temp table Tmp_Table
on commit preserve rows
as /*+direct*/
select * from table_a 
order by uid  
segmented by hash(uid) all nodes ksafe 1;

===========================================
字段级的 mask 控制机制.
===========================================
数据安全是数仓中重要的一环, 敏感数据在使用的时候往往需要脱敏, Vertica 提供能字段级的mask机制.

CREATE ACCESS POLICY ON customers_table
 FOR COLUMN SSN
    CASE
        WHEN ENABLED_ROLE('manager') THEN SSN
        WHEN ENABLED_ROLE('operator') THEN SUBSTR(SSN, 8, 4)
        ELSE NULL
    END
ENABLE;

===========================================
时间序列语法
===========================================
在很多时候, 我们需要处理时序数据, 比如初始化日期维度表, 比如检查每分钟的数据量, 比如补跑堆积的批次, 可以用时序语句构建一个批次的循环记录集.

SELECT slice_time1 FROM 
(select '20171110'::timestamp date_value from dual  
 union all 
 select '20171120'::timestamp date_value from dual 
) t 
TIMESERIES slice_time1 AS '1 days' OVER(ORDER BY date_value)
order by slice_time1 asc

===========================================
和Oracle的兼容性
===========================================
Vertica 在很多时候都尽量和Oracle保持一致, 比如普通的一个Oracle建表语句, 直接可以在Vertica上执行, 再比如dual伪表在Vertica中也可以使用, 还有很多函数都差不多.