列存文件格式使用

举报
luckly 发表于 2021/04/09 17:05:05 2021/04/09
【摘要】  列存文件格式使用1.1      列存文件存储简介目前列存表存储格式支持两种,分别为colversion 1.0和2.0,当指定为1.0格式时,每一列数据存储在一个单独文件之中,文件名以relfilenode.C1、relfilenode.C2、relfilenode.C3等命名,bcm文件也是类似,如果业务中列存表/列存宽表较多时,文件数据会比较多,影响扩容、build、备份等运维操作性...

 

列存文件格式使用

1.1      列存文件存储简介

目前列存表存储格式支持两种,分别为colversion 1.02.0,当指定为1.0格式时,每一列数据存储在一个单独文件之中,文件名relfilenode.C1relfilenode.C2relfilenode.C3等命名,bcm文件也是类似,如果业务中列存表/列存宽表较多时,文件数据会比较多,影响扩容、build、备份等运维操作性能,当指定2.0格式时,所有列的数据存储在一个文件中,命令为relfilenode_C1.0relfilenode_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)

  

20210414-104922(WeLinkPC).png

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。