列存文件格式使用
列存文件格式使用
1.1 列存文件存储简介
目前列存表存储格式支持两种，分别为colversion 1.0和2.0，当指定为1.0格式时，每一列数据存储在一个单独文件之中，文件名以relfilenode.C1、relfilenode.C2、relfilenode.C3等命名，bcm文件也是类似，如果业务中列存表/列存宽表较多时，文件数据会比较多，影响扩容、build、备份等运维操作性能，当指定2.0格式时，所有列的数据存储在一个文件中，命令为relfilenode_C1.0，relfilenode_C1.1，可以有效减少文件数量，提升扩容、build、备份等运维操作性能。
1.2 SQL示例
可以通过CREATE TABLE指定colversion格式，或者通过ALTER TABLE修改colversion格式，其他操作均不改变文件存储格式。
（1）创建表指定文件格式colversion=2.0
DROP TABLE IF EXISTS cv_comtb;
CREATE TABLE cv_comtb
(
l_orderkey bigint not null
, l_partkey bigint not null
, l_suppkey bigint not null
, l_linenumber bigint not null
, l_quantity decimal(15,2) not null
, l_extendedprice decimal(15,2) not null
, l_discount decimal(15,2) not null
, l_tax decimal(15,2) not null
, l_returnflag varchar not null
, l_linestatus varchar not null
, l_shipdate date not null
, l_commitdate date not null
, l_receiptdate date not null
, l_shipinstruct varchar not null
, l_shipmode varchar not null
, l_comment varchar not null
)
with (orientation = column, colversion=2.0)
distribute by hash(l_partkey);
（2）插入有效数据
insert into cv_comtb values (1,237,7706,1,17,21168.23,0.04,0.02,'N','O','1996-03-13','1996-02-12','1996-03-22','DELIVER IN PERSON','TRUCK','egular courts above the');
insert into cv_comtb values (7,94780,9799,3,46,81639.88,0.10,0.07,'N','O','1996-01-15','1996-03-27','1996-02-03','COLLECT COD','MAIL', 'unusual reques');
insert into cv_comtb values (146,163073,3074,4,28,31809.96,0.03,0.04,'N','O','1996-03-21','1996-04-08','1996-04-20','NONE','FOB','. slyly special requests haggl');
insert into cv_comtb values (117,151894,9440,5,38,73943.82,0.08,0.01,'N','O','1996-02-11','1996-02-24','1996-02-18','DELIVER IN PERSON','TRUCK','Ns haggle carefully ironic deposits. bl');
insert into cv_comtb values (121,79251,1759,6,35,43058.75,0.06,0.03,'N','O','1996-01-16','1996-02-23','1996-01-22','TAKE BACK RETURN','FOB','jole. excuses wake carefully alongside of');
insert into cv_comtb values (33,generate_series(100,500),2269,7,5,6476.15,0.04,0.02,'N','O','1996-02-10','1996-03-26','1996-02-13','NONE','FOB','ithely regula');
（3）查询表中数据
analyze cv_comtb;
select count(*) from cv_comtb;
count
-------
406
(1 row)
（4）查看实例上文件存储（云上无法直接访问实例路径，此示例为了说明文件存储和定义格式相匹配）
-rw------- 1 pgxc wheel 0 Apr 8 20:16 base/14852/16384
-rw------- 1 pgxc wheel 655360 Apr 8 20:16 base/14852/16384_C1.0
-rw------- 1 pgxc wheel 24576 Apr 8 20:16 base/14852/16384_C1_bcm
（5）通过alter table修改文件格式1.0，并插入新的数据正常
alter table cv_comtb set (colversion=1.0);
insert into cv_comtb values (8,151899,9440,5,38,73943.82,0.08,0.01,'N','O','1996-02-11','1996-02-24','1996-02-18','DELIVER IN PERSON','TRUCK','Ns haggle carefully ironic deposits. bl');
insert into cv_comtb values (9,79256,1759,6,35,43058.75,0.06,0.03,'N','O','1996-01-16','1996-02-23','1996-01-22','TAKE BACK RETURN','FOB','jole. excuses wake carefully alongside of');
select count(*) from cv_comtb;
count
-------
408
(1 row)
（6）查看colversion=1.0的文件存储格式
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C1.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C10.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C11.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C12.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C13.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C14.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C15.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C16.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C2.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C3.0
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C4.0
-rw------- 1 pgxc wheel 163840 Apr 8 20:24 base/14852/16400_C5.0
-rw------- 1 pgxc wheel 24576 Apr 8 20:24 base/14852/16400_C5_bcm
-rw------- 1 pgxc wheel 163840 Apr 8 20:24 base/14852/16400_C6.0
-rw------- 1 pgxc wheel 24576 Apr 8 20:24 base/14852/16400_C6_bcm
-rw------- 1 pgxc wheel 163840 Apr 8 20:24 base/14852/16400_C7.0
-rw------- 1 pgxc wheel 24576 Apr 8 20:24 base/14852/16400_C7_bcm
-rw------- 1 pgxc wheel 163840 Apr 8 20:24 base/14852/16400_C8.0
-rw------- 1 pgxc wheel 24576 Apr 8 20:24 base/14852/16400_C8_bcm
-rw------- 1 pgxc wheel 0 Apr 8 20:24 base/14852/16400_C9.0
备注：0文件由于数据一样，存储在pg_cudesc表中
（6）再次修改文件格式为2.0，查看文件存储
alter table cv_comtb set (colversion=2.0);
-rw------- 1 pgxc wheel 0 Apr 9 09:10 base/14852/16416
-rw------- 1 pgxc wheel 655360 Apr 9 09:10 base/14852/16416_C1.0
-rw------- 1 pgxc wheel 24576 Apr 9 09:10 base/14852/16416_C1_bcm
（7）对表进行UDI功能均正常
update cv_comtb set l_suppkey=l_suppkey+1,l_shipmode=l_shipmode||'city'||l_suppkey where l_partkey<=3000;
--UPDATE 402
delete cv_comtb where l_partkey%6=1;
--DELETE 67
insert into cv_comtb values (133,generate_series(700,800),12269,17,25,64763.15,0.04,0.02,'F','T','2001-02-10','2021-03-26','2021-02-13','yONE','FOB','ithely regula');
--INSERT 0 101
select count(*) from cv_comtb;
count
-------
442
(1 row)
select count(*) from cv_comtb where l_orderkey=133;
count
-------
101
(1 row)
- 点赞
- 收藏
- 关注作者
评论（0）