列存delta表优秀实践

基本原理

在GaussDB(DWS)中，列存表按列存储数据，每列默认60000行存储在一个CU中，CU是列存表存储数据的最小单元，CU生成后数据固定不可更改。无论是向列存表中插入1条还是60000条数据，都只会生成一个CU，在多次插入少量数据时，不能有效的利用列存压缩能力，从而导致数据膨胀影响查询的性能和磁盘使用率。

由于CU文件数据不能更改只能追加写，对CU中的数据做更新或删除都不会真正更改这个CU，删除是将老数据在字典中标记为作废，更新操作是标记老数据删除后，再写入一条新记录到新CU，CU不会有任何的更改。在对列存表进行多次更新/删除，或每次只插入少量数据后，会导致列存表空间膨胀，大量空间无法有效利用。

因为列存表在设计上就是为了大批量数据导入以及海量数据按列存储/查询。为了解决上述问题，引入了列存delta表就是列存表附带的行存表。在启用delta表后，单条或者小批量数据导入时，数据将进入delta表中，避免小CU的产生，delta表的增删改查与行存表一致。开启delta表后，将显著提升列存表单条导入的性能。

使用场景

列存delta表应用于行列混合存储，适合实时分析统计，解决了实时小批量数据入库引起的性能问题，定期合并到主表保证分析查询的性能。在实际使用时需要根据业务场景来评估是否需要开启delta表，否则不仅无法充分发挥GaussDB(DWS)列存表的优势，反而会造成额外的空间和时间的浪费。

准备工作

• 已注册账号，且在使用GaussDB(DWS) 前检查账号状态，账号不能处于欠费或冻结状态。
• 获取此账号的“AK/SK”。
• 参考交通卡口通行车辆分析，GaussDB(DWS) 已预先将样例数据上传到OBS桶的“traffic-data”文件夹中，并给所有华为云用户赋予了该OBS桶的只读访问权限。

操作步骤

1. 使用DAS连接集群。在集群列表中找到所需要的集群，单击“操作”栏中的“登录”按钮，跳转至数据库管理服务（DAS）页面，填写登录用户名、数据库名称、密码信息后测试连接，测试无误后登录集群。更多详细步骤请参考使用DAS连接集群。

   

2. 执行以下语句，创建traffic数据库。

   1	CREATE DATABASE traffic encoding 'utf8' template template0;

3. 执行以下语句，分别创建用于存储卡口车辆信息的数据库表GCJL和GCJL2。其中GCJL默认不开启delta表，GCJL2为开启delta表。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   CREATE SCHEMA traffic_data; SET current_schema= traffic_data; DROP TABLE if exists GCJL; CREATE TABLE GCJL ( kkbh VARCHAR(20), hphm VARCHAR(20), gcsj DATE , cplx VARCHAR(8), cllx VARCHAR(8), csys VARCHAR(8) ) with (orientation = column, COMPRESSION=MIDDLE) distribute by hash(hphm); DROP TABLE if exists GCJL2; CREATE TABLE GCJL2 ( kkbh VARCHAR(20), hphm VARCHAR(20), gcsj DATE , cplx VARCHAR(8), cllx VARCHAR(8), csys VARCHAR(8) ) with (orientation = column, COMPRESSION=MIDDLE, ENABLE_DELTA = TRUE) distribute by hash(hphm);

   

   • Delta表默认是关闭的，若需要开启delta表，可以在创建列存表时指定enable_delta为true。
   • 如果未开启delta表，也可以执行如下命令开启delta表：

     1	ALTER TABLE table_name SET (enable_delta=TRUE);

   • 如果已开启delta表，需要关闭delta表时可以执行如下命令：

     1	ALTER TABLE table_name SET (enable_delta=FALSE);

4. 创建外表。外表用于识别和关联OBS上的源数据。
   

   • 其中，<obs_bucket_name>代表OBS桶名，仅支持部分区域，当前支持的区域和对应的OBS桶名请参见支持区域。GaussDB(DWS) 集群不支持跨区域访问OBS桶数据。
   • 本实践以““中国-香港”地区为例，可填入dws-demo-ap-southeast-1，<Access_Key_Id>和<Secret_Access_Key>替换为实际值。
   • 创建外表如果提示“ERROR: schema "xxx" does not exist Position”，则说明schema不存在，请先参照上一步创建schema。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   DROP FOREIGN table if exists GCJL_OBS; CREATE FOREIGN TABLE GCJL_OBS ( like traffic_data.GCJL ) SERVER gsmpp_server OPTIONS ( encoding 'utf8', location 'obs://<obs_bucket_name>/traffic-data/gcxx', format 'text', delimiter ',', access_key '<Access_Key_Id>', secret_access_key '<Secret_Access_Key>', chunksize '64', IGNORE_EXTRA_DATA 'on' );

5. 执行以下语句，将数据从外表导入到数据库表中。

   1 2	INSERT INTO traffic_data.GCJL select * from GCJL_OBS; INSERT INTO traffic_data.GCJL2 select * from GCJL_OBS;

   导入数据需要一些时间，请耐心等待。

6. 执行以下语句，查看导入数据库表后的存储空间大小。

   1 2	SELECT pg_size_pretty(pg_total_relation_size('traffic_data.GCJL')); SELECT pg_size_pretty(pg_total_relation_size('traffic_data.GCJL2'));

   通过对比两种场景下的列存表，开启delta表后，存储空间由8953 MB减少为6053 MB，大大提升了存储能力。

   

7. 执行以下语句，查询表中数据，对比之后可以发现开启delta表后查询速度有所提升。

   1 2	SELECT * FROM traffic_data.GCJL where hphm = 'YD38641'; SELECT * FROM traffic_data.GCJL2 where hphm = 'YD38641';

开启Delta表的影响

• 开启列存表的delta表功能，在导入单条或者小规模数据进入表中时，能够防止小CU的产生，所以开启delta表能够带来显著的性能提升，例如在3CN、6DN的集群上操作，每次导入100条数据，导入时间能减少25%，存储空间减少97%，所以在需要多次插入小批量数据前应该先开启delta表，等到确定接下来没有小批量数据导入了再关闭。
• Delta表就是列存表附带的行存表，那么将数据插入delta表后将失去列存表的高压缩比等优势，正常情况下使用列存表的场景都是大批量数据导入，所以默认关闭delta表，如果开启delta表做大批量数据导入，反而会额外消耗更多时间和空间，同样在3CN、6DN的集群上操作，每次导入10000条数据时，开启delta表会比不开启时慢4倍，额外消耗10倍以上的空间。所以开启delta表需谨慎，根据实际业务需要来选择开启和关闭。