oushuDB之各类压缩表如何创建

原创于 2022-12-21 16:22:16 发布 · 136 阅读

CC 4.0 BY-SA版权

文章标签：

此博客展示了在不同数据库系统中创建压缩表的实验，包括PostgreSQL的Appendonly表和OushuDB的Parquet及ORC表。实验涉及Snappy、LZ4和Zlib等压缩算法，并探讨了不同压缩级别的影响。尽管Parquet和ORC表不支持所有压缩类型，但实验揭示了每种格式的特性和限制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

oushuDB version
PostgreSQL 8.2.15 (OushuDB 4.1.0.0 build 25055 Enterprise Edition)
(Apache HAWQ 2.4.0.0) (Greenplum Database 4.2.0)
1.
2.
Table type
Append only table
None
CREATE TABLE ao_demo_none (id int)
with (appendonly =true, orientation =row );
1.
2.
Snappy [ - ]
CREATE TABLE ao_demo_snappy (id int)
with (appendonly =true, orientation =row , compresstype = snappy);
1.
2.
Lz4 [ 0-9 ]
CREATE TABLE ao_demo_lz4 (id int)
with (appendonly =true, orientation =row , compresstype = lz4);
CREATE TABLE ao_demo_lz4_01 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=1);
CREATE TABLE ao_demo_lz4_02 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=2);
CREATE TABLE ao_demo_lz4_03 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=3);
CREATE TABLE ao_demo_lz4_04 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=4);
CREATE TABLE ao_demo_lz4_05 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=5);
CREATE TABLE ao_demo_lz4_06 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=6);
CREATE TABLE ao_demo_lz4_07 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=7);
CREATE TABLE ao_demo_lz4_08 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=8);
CREATE TABLE ao_demo_lz4_09 (id int)
with (appendonly =true, orientation =row , compresstype = lz4,compresslevel=9);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Zlib [ 0-9 ]
CREATE TABLE ao_demo_zlib (id int)
with (appendonly =true, orientation =row , compresstype = zlib);
CREATE TABLE ao_demo_zlib_01 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=1);
CREATE TABLE ao_demo_zlib_02 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=2);
CREATE TABLE ao_demo_zlib_03 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=3);
CREATE TABLE ao_demo_zlib_04 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=4);
CREATE TABLE ao_demo_zlib_05 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=5);
CREATE TABLE ao_demo_zlib_06 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=6);
CREATE TABLE ao_demo_zlib_07 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=7);
CREATE TABLE ao_demo_zlib_08 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=8);
CREATE TABLE ao_demo_zlib_09 (id int)
with (appendonly =true, orientation =row , compresstype = zlib,compresslevel=9);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Parquet table
None
CREATE TABLE parquet_demo_none (id int)
with (appendonly =true, orientation =parquet );
1.
2.
Snappy [ - ]
CREATE TABLE parquet_demo_snappy (id int)
with (appendonly =true, orientation =parquet, compresstype = snappy );
1.
2.
Lz4 [ 0-9 ]
ERROR: parquet table doesn't support compress type: 'lz4'
1.
Zlib [ 0-9 ]
ERROR: parquet table doesn't support compress type: 'zlib'
1.
Native Orc table
None
create table orc_demo_none (id int)
with (appendonly=true, orientation=orc);
1.
2.
Snappy [ - ]
create table orc_demo_snappy (id int)
with (appendonly=true, orientation=orc, compresstype = snappy);
1.
2.
Lz4 [ 0-9 ][ 压缩级别无效均等价于level=1 ]
create table orc_demo_lz4 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8');
create table orc_demo_lz4_01 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=1);
create table orc_demo_lz4_02 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=2);
create table orc_demo_lz4_03 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=3);
create table orc_demo_lz4_04 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=4);
create table orc_demo_lz4_05 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=5);
create table orc_demo_lz4_06 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=6);
create table orc_demo_lz4_07 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=7);
create table orc_demo_lz4_08 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=8);
create table orc_demo_lz4_09 (id int)
with (appendonly=true, orientation=orc, compresstype = lz4, dicthreshold='0.8', compresslevel=9);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Zlib [ 0-9 ][ 压缩级别无效均等价于level=1 ]
create table orc_demo_zlib (id int)
with (appendonly=true, orientation=orc, compresstype = zlib);
create table orc_demo_zlib_01 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=1);
create table orc_demo_zlib_02 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=2);
create table orc_demo_zlib_03 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=3);
create table orc_demo_zlib_04 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=4);
create table orc_demo_zlib_05 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=5);
create table orc_demo_zlib_06 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=6);
create table orc_demo_zlib_07 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=7);
create table orc_demo_zlib_08 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=8);
create table orc_demo_zlib_09 (id int)
with (appendonly=true, orientation=orc, compresstype = zlib, compresslevel=9);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
zstd [ 0-9 ][ 压缩级别无效均等价于level=1 ]
create table orc_demo_zstd (id int)
with (appendonly=true, orientation=orc, compresstype = zstd);
create table orc_demo_zstd_01 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=1);
create table orc_demo_zstd_02 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=2);
create table orc_demo_zstd_03 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=3);
create table orc_demo_zstd_04 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=4);
create table orc_demo_zstd_05 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=5);
create table orc_demo_zstd_06 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=6);
create table orc_demo_zstd_07 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=7);
create table orc_demo_zstd_08 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=8);
create table orc_demo_zstd_09 (id int)
with (appendonly=true, orientation=orc, compresstype = zstd, compresslevel=9);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Magmaap table
None
create table magmaap_demo_none (id int) format 'magmaap';