Vertica常用语法

转载请注明出处，谢谢~

一、建表

1.创建普通表

CREATE TABLE INCOME.ORDER_FEE_XMYCX_201910
(
CODE_DESC varchar(50),
CHARGE_PAY_FEE_RELATION numeric(15,0),
CREATE_DATE timestamp
) DIRECT
ORDER BY CODE_DESC
SEGMENTED BY HASH(CHARGE_PAY_FEE_RELATION) ALL NODES KSAFE 1;

2.创建分区表

CREATE TABLE INCOME.OWE_B30_B34_H
(
HANDLE_MONTH char(6),
LATN_ID varchar(10) NOT NULL,
SERV_CODE varchar(30),
ACCT_ITEM_TYPE_CODE varchar(30),
ACCT_PERIOD varchar(30),
ACCT_MONTH varchar(30),
OWE_AMOUNT numeric(20,0),
UNCONFIRM_OWE numeric(20,0),
CONFIRM_OWE numeric(20,0),
PAY_TYPE varchar(10)
) DIRECT
ORDER BY SERV_CODE
SEGMENTED BY HASH(SERV_CODE) ALL NODES KSAFE 1
PARTITION BY (LATN_ID);

注意：分区字段一定要加上NOT NULL

3.修改普通表为分区表
首先需要确保分区字段不允许为空

ALTER TABLE TEST.FCT_FOURNET_WLANAP_EQUP_A ALTER DAY_ID SET NOT NULL;
ALTER TABLE TEST.FCT_FOURNET_WLANAP_EQUP_A PARTITION BY DAY_ID;

4.创建临时表（连接关闭后表会自动删掉）

CREATE LOCAL TEMPORARY TABLE ORG_TREE_TMP
ON COMMIT PRESERVE ROWS AS
SELECT * FROM INCOME.ORG_TREE_INFO_NEW;
 
CREATE LOCAL TEMPORARY TABLE input(id,name)
ON COMMIT PRESERVE ROWS AS
SELECT 42,'Arthur Dent'
UNION ALL SELECT 43,'Ford Prefect'
UNION ALL SELECT 44,'Tricia McMillan'
KSAFE 0;

Vertica临时表一些关键字说明：

1. CREATE TEMPORARY TABLE ：表示建的为SESSION级别的临时表；
2. ON COMMIT PRESERVE ROWS ：表示在SESSION断开前临时表一直保存数据；
3. KSAFE 0 ：该语句表示，数据存储时的副本数（防止节点故障数据丢失的），对于临时表时指定0即可，表示不创建副本。
   注意：如果使用CREATE LOCAL TEMPORARY TABLE tablename + 字段类型语句创建临时表，需要将ON COMMIT PRESERVE ROWS关键词放到字段类型后面。如果使用CREATE LOCAL TEMPORARY TABLE tablename AS 语句创建临时表，则不需要调整。

5.vertica建表的一些规则
在建表时如何选择order by 的列：
1、order by 后表中插入的数据是有序的，所以order by 的列就源自于你在查询语句时使用的where 字句的内容。例如，如果字句查询中有where x=1 and y=2,那么建立projection时order by (x, y)查询的时候就会迅速定位到符合条件的数据
2、group by 后面的字段，出现在order by 中也可以优化查询。
3、order by 不要建立在LONG VARBINARY and LONG VARCHAR的列
Segmentation
1、Segmentation by hash()就是按照某一列，打散数据，把数据均匀的分布在各个节点上，对于大表，要记得使用。所以 hash里的列是主键最好，也就是说该列数据不重复的值越多，越适合做hash.
2、Segmentation by 的列不要用LONG VARBINARY and LONG VARCHAR columns.

二、插入数据

跟Oracle一样，eg：

INSET INTO STORE.LW_TEST VALUES('LW','001',20,20);
INSERT INTO INCOME.SUB_CODE_ARPU
SELECT * FROM APP_Z.SUB_CODE_ARPU_19 WHERE BIL_MONTH = '201910';
INSERT INTO PUBLIC.WEIBOUSER ( 字段1,字段2,字段3,..