表#

OushuDB表由行（rows）和（columns）组成。每一个列有一个列名和一个数据类型，一个表的列数和列的顺序是固定的。一个表的行数是可变的。SQL并不假设表中行的顺序。当读一个表时，除非显示要求排序，返回的行会以任意顺序出现。另外，SQL并不给每一行一个唯一标志符，所以，一个表中具有同样几个同样的行是可能的。

创建一个表可以使用create table命令。在命令里面，需要指定表名，列名以及列的类型。例如：

1create table my_first_table (
2    first_column text,
3    second_column integer
4);

上面的命令创建了一个两列的表，一列为文本类型，一列为整数类型。删除刚刚创建的表可以使用drop table命令。

1drop table my_first_table;

表的存储格式#

OushuDB现在支持多种存储格式：ROW，ORC，Hudi，MagmaAP。ROW是按行存储的格式，而ORC，Hudi，MagmaAP是按列存储的格式。 其中MagmaAP 是在4.0.0.0发布的全新的存储格式。ROW，MagmaAP，ORC，Hudi都支持update／delete，支持事务， 且MagmaAP还支持index。

注：和GPDB类似，之前OushuDB版本支持CO格式，但CO格式不适合集群大和分区多的情况，后续新版本去除了CO支持。

对于各种格式的表的建表语法，下面给出了几个例子。

 1# 默认创建的是ROW表
 2CREATE TABLE rank1 (id int, rank int, year smallint,gender char(1), count int );
 3
 4# 和上面的创建的表一样，显式指定存储格式类型
 5CREATE TABLE rank2 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =row);
 6
 7# 创建一个snappy压缩的ROW表
 8CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =row, compresstype = snappy);
 9
10# 创建一个不压缩的ORC表，如果不指定压缩类型的话，默认不压缩。
11CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =orc);
12
13# 创建一个带压缩的ORC表，需指定压缩类型。
14CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =orc, compresstype = lz4);
15
16# 创建一个普通的Hudi表，相比ROW/ORC表，需要指定type，默认不压缩。
17CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =horc, type = mor);
18
19# 创建一个带压缩的Hudi表，需指定压缩类型。
20CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =horc, type = mor, compresstype = lz4);
21
22# 创建一个压缩的magma表， magma 内部自动实现了压缩。
23CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) format 'magmaap';
24
25# 创建一个有primary key的magma表， magma 内部自动实现了压缩。
26CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int，primary key(id) ) format 'magmaap';

表的分布#

在OushuDB中，表可以两种方式分布方式：基于Hash的分布和Random分布。基于Hash的分布方法基于分布列的Hash值进行分布，Random分布采取随机分布模式。

创建表时用户不指定分布方式的时候非magmaap表默认使用Random分布，magmaap表目前不支持Random 分布。下面这个两个例子等价。

1CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int );
2
3
4CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int );
5DISTRIBUTED RANDOMLY;

下面这个例子创建一个Hash分布的表，分布的Key使用三个列（rank, gender, year）的组合，数据分布到32个bucket里面。

如果不指定bucketnum的话，系统默认使用default_hash_table_bucket_number系统参数的值来做为bucketnum。

1CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int )
2WITH (bucketnum = 32)
3DISTRIBUTED BY (rank, gender,year);

Hash分布和Random分布的选取#

非Magma表： Random分布的表较灵活，在系统扩容添加节点后无需重新分布数据。而Hash分布的表在系统扩容后，为了利用新增加节点的计算能力，需要重新分布数据。另外，针对Hash分布的表资源管理器在分配资源的时候采取分配固定virtual segment数的方式，不如Random分布灵活。

Hash分布的表在某些查询上会有性能上的好处，因为有时可以避免重新分布某些表。

例如下面例子的查询，如果lineitem和orders两张表分别按照l_orderkey和o_orderkey分布，则这个查询在执行时不再需要重新分布任何一张表就可以并行在各个节点并行执行连接操作。

1SELECT l_orderkey, count(l_quantity)
2FROM lineitem, orders
3WHERE l_orderkey = o_orderkey

针对绝大多数查询，实验表明都不是网络瓶颈，基于Hash分布和基于Random分布性能差别不大。所以我们建议用户默认采取Random分布， 只针对特定需要优化的场合使用Hash分布的表。

Magma表具备的Hash和random 表的优势，通过 default_magma_hash_table_nvseg_per_node 来控制每个节点能启动的virtual segment数。 在系统扩容后不需要重新分布数据。

Hash分布的表bucketnum的选取#

针对Hash分布的表，bucketnum决定了一个查询的并行度。在一些常见的硬件配置中（128G内存和12块SAS盘），我们建议选取6 * 节点数或者8 * 节点数。 硬件更好的话可以增加bucketnum。Magma table 使用default_magma_hash_table_nvseg_per_node, 表示每个节点是virtual segment 的个数。：

表分区#

针对大的数据仓库事实表，往往我们可以通过对表进行分区的方式来把一个很大的表拆分成多个子表。这样的话，有两个好处:

  • 查询优化器可以针对分区表进行优化，如果查询只设计到某些分区，则查询计划只需要扫描这些分区，从而加速查询

  • 如果我们按照日期进行分区的话，我们可以简单的加入分区和删除过期的分区。

OushuDB支持基于Range和List的两种分区方式。

  • Range分区：依据数值范围进行分区，比如日期，价格等

  • List分区：依据一个值的列表进行分区，比如地区等

下面我们通过例子说明这两种分区的使用方式。

Range分区

1# 创建一个sales表，按照date列Range分区，从2008年到2009年每月创建一个分区
2
3postgres=＃ CREATE TABLE sales (id int, date date, amt decimal(10,2))
4PARTITION BY RANGE (date)
5( START (date '2008-01-01') INCLUSIVE
6   END (date '2009-01-01') EXCLUSIVE
7   EVERY (INTERVAL '1 month') );

查看创建的表信息，d+给出该表的所有信息

 1postgres=# \d+ sales
 2              Append-Only Table "public.sales"
 3 Column |     Type      | Modifiers | Storage | Description
 4--------+---------------+-----------+---------+-------------
 5 id     | integer       |           | plain   |
 6 date   | date          |           | plain   |
 7 amt    | numeric(10,2) |           | main    |
 8Compression Type: None
 9Compression Level: 0
10Block Size: 32768
11Checksum: f
12Child tables: sales_1_prt_1,
13              sales_1_prt_10,
14              sales_1_prt_11,
15              sales_1_prt_12,
16              sales_1_prt_2,
17              sales_1_prt_3,
18              sales_1_prt_4,
19              sales_1_prt_5,
20              sales_1_prt_6,
21              sales_1_prt_7,
22              sales_1_prt_8,
23              sales_1_prt_9
24Has OIDs: no
25Options: appendonly=true
26Distributed randomly
27Partition by: (date)

你也可以显式得声明子分区并指定子表名字。

 1CREATE TABLE sales_exp (id int, date date, amt decimal(10,2))
 2PARTITION BY RANGE (date)
 3( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE ,
 4  PARTITION Feb08 START (date '2008-02-01') INCLUSIVE ,
 5  PARTITION Mar08 START (date '2008-03-01') INCLUSIVE ,
 6  PARTITION Apr08 START (date '2008-04-01') INCLUSIVE ,
 7  PARTITION May08 START (date '2008-05-01') INCLUSIVE ,
 8  PARTITION Jun08 START (date '2008-06-01') INCLUSIVE ,
 9  PARTITION Jul08 START (date '2008-07-01') INCLUSIVE ,
10  PARTITION Aug08 START (date '2008-08-01') INCLUSIVE ,
11  PARTITION Sep08 START (date '2008-09-01') INCLUSIVE ,
12  PARTITION Oct08 START (date '2008-10-01') INCLUSIVE ,
13  PARTITION Nov08 START (date '2008-11-01') INCLUSIVE ,
14  PARTITION Dec08 START (date '2008-12-01') INCLUSIVE
15                   END (date '2009-01-01') EXCLUSIVE );

查看创建的表信息

 1postgres=＃ \d+ sales_exp
 2            Append-Only Table "public.sales_exp"
 3 Column |     Type      | Modifiers | Storage | Description
 4--------+---------------+-----------+---------+-------------
 5 id     | integer       |           | plain   |
 6 date   | date          |           | plain   |
 7 amt    | numeric(10,2) |           | main    |
 8Compression Type: None
 9Compression Level: 0
10Block Size: 32768
11Checksum: f
12Child tables: sales_exp_1_prt_apr08,
13              sales_exp_1_prt_aug08,
14              sales_exp_1_prt_dec08,
15              sales_exp_1_prt_feb08,
16              sales_exp_1_prt_jan08,
17              sales_exp_1_prt_jul08,
18              sales_exp_1_prt_jun08,
19              sales_exp_1_prt_mar08,
20              sales_exp_1_prt_may08,
21              sales_exp_1_prt_nov08,
22              sales_exp_1_prt_oct08,
23              sales_exp_1_prt_sep08
24Has OIDs: no
25Options: appendonly=true
26Distributed randomly
27Partition by: (date)

下面是另外一个根据Range分区的例子，这次使用的是整型列进行分区。这里面我们添加了一个DEFAULT PARTITION， 在不满足其他分区的条件下，数据会被插入DEFAULT PARTITION。

1CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
2PARTITION BY RANGE (year)
3( START (2001) END (2008) EVERY (1),
4  DEFAULT PARTITION extra );

List分区#

下面的例子创建了一个基于List的分区表。List分区表可以基于任意支持等值比较的数据类型。对与List分区，你需要 显式的指定所有子分区。

1postgres=＃ CREATE TABLE rank (id int, rank int, year int, gender char(1), count int )
2PARTITION BY LIST (gender)
3( PARTITION girls VALUES ('F'),
4  PARTITION boys VALUES ('M'),
5  DEFAULT PARTITION other );

查看表信息

 1postgres=＃ \d+ rank
 2              Append-Only Table "public.rank"
 3 Column |     Type     | Modifiers | Storage  | Description
 4--------+--------------+-----------+----------+-------------
 5 id     | integer      |           | plain    |
 6 rank   | integer      |           | plain    |
 7 year   | integer      |           | plain    |
 8 gender | character(1) |           | extended |
 9 count  | integer      |           | plain    |
10Compression Type: None
11Compression Level: 0
12Block Size: 32768
13Checksum: f
14Child tables: rank_1_prt_boys,
15              rank_1_prt_girls,
16              rank_1_prt_other
17Has OIDs: no
18Options: appendonly=true
19Distributed randomly
20Partition by: (gender)

多级分区#

你可以使用SUBPARTITION模版定义多级分区。下面的例子定义了一个两级分区表，第一级安装date列进行Range分区，第二级按照region列进行List分区。

 1CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text)
 2PARTITION BY RANGE (date)
 3SUBPARTITION BY LIST (region)
 4SUBPARTITION TEMPLATE
 5( SUBPARTITION usa VALUES ('usa'),
 6  SUBPARTITION asia VALUES ('asia'),
 7  SUBPARTITION europe VALUES ('europe'),
 8  DEFAULT SUBPARTITION other_regions)
 9(START (date '2011-01-01') INCLUSIVE
10 END (date '2012-01-01') EXCLUSIVE
11 EVERY (INTERVAL '1 month'),
12 DEFAULT PARTITION outlying_dates);

注：当你在使用多级分区的时候，系统会产生大量的小表，有些表可能没有数据或包含很少数据，这样会对系统元数据管理产生过多压力。 建议不要创建具有过多分区的表。一般限制分区数在100或以内比较合理。

查看你的分区设计#

你可以通过pg_partitions视图来查看你的分区表设计。例如通过下面的语句可以查看出sales表的分区设计。

 1postgres=# SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank
 2postgres-# FROM pg_partitions
 3postgres-# WHERE tablename='sales';
 4                                           partitionboundary                                          | partitiontablename | partitionname | partitionlevel | partitionrank
 5------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+---------------
 6 START ('2008-01-01'::date) END ('2008-02-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_1      |               |              0 |             1
 7 START ('2008-02-01'::date) END ('2008-03-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_2      |               |              0 |             2
 8 START ('2008-03-01'::date) END ('2008-04-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_3      |               |              0 |             3
 9 START ('2008-04-01'::date) END ('2008-05-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_4      |               |              0 |             4
10 START ('2008-05-01'::date) END ('2008-06-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_5      |               |              0 |             5
11 START ('2008-06-01'::date) END ('2008-07-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_6      |               |              0 |             6
12 START ('2008-07-01'::date) END ('2008-08-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_7      |               |              0 |             7
13 START ('2008-08-01'::date) END ('2008-09-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_8      |               |              0 |             8
14 START ('2008-09-01'::date) END ('2008-10-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_9      |               |              0 |             9
15 START ('2008-10-01'::date) END ('2008-11-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_10     |               |              0 |            10
16 START ('2008-11-01'::date) END ('2008-12-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_11     |               |              0 |            11
17 START ('2008-12-01'::date) END ('2009-01-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_12     |               |              0 |            12
18(12 rows)

添加一个分区#

你可以通过下面的语句添加一个分区。

1ALTER TABLE sales ADD PARTITION
2            START (date '2009-02-01') INCLUSIVE
3            END (date '2009-03-01') EXCLUSIVE;

如果你在创建表的时候没有使用subpartition template，你需要在添加分区的时候给出子分区定义，例如：

1ALTER TABLE sales ADD PARTITION
2    START (date '2009-02-01') INCLUSIVE
3    END (date '2009-03-01') EXCLUSIVE
4      ( SUBPARTITION usa VALUES ('usa'),
5        SUBPARTITION asia VALUES ('asia'),
6        SUBPARTITION europe VALUES ('europe') );

你也可以单独修改一个二级分区：

 1CREATE TABLE sales_two_level (trans_id int, date date, amount decimal(9,2), region text)
 2PARTITION BY RANGE (date)
 3SUBPARTITION BY LIST (region)
 4SUBPARTITION TEMPLATE
 5( SUBPARTITION usa VALUES ('usa'),
 6  SUBPARTITION asia VALUES ('asia'),
 7  SUBPARTITION europe VALUES ('europe'))
 8(START (date '2011-01-01') INCLUSIVE
 9 END (date '2012-01-01') EXCLUSIVE
10 EVERY (INTERVAL '1 month'),
11 DEFAULT PARTITION outlying_dates);
12
13ALTER TABLE sales_two_level ALTER PARTITION FOR (RANK(12))
14   ADD PARTITION africa VALUES ('africa');

其中RANK(12)表示第12个分区。

注：指定一个分区可以使用

1PARTITION FOR (value) or PARTITION FOR(RANK(number))语法。

如果你的分区表有一个Default分区的话，你不可以向该分区表添加分区，你只可以通过分裂Default分区的方法来添加分区。

重命名分区#

Partitioned tables use the following naming convention. Partitioned subtable names are subject to uniqueness requirements and length limitations.

分区表使用以下的命名规则。

<parentname>_<level>_prt_<partition_name>

例如：sales_1_prt_jan08指的是父表名字为sales，第一级分区名字为jan08的分区。在创建Range分区表时， 如果没有指定分区名字，分区的名字会自动生成为数字。

改变父表的名字同时会改变分区表的名字。例如：

 1postgres=＃ ALTER TABLE sales_two_level RENAME TO globalsales;
 2
 3postgres=＃ \d+ globalsales
 4            Append-Only Table "public.globalsales"
 5  Column  |     Type     | Modifiers | Storage  | Description
 6----------+--------------+-----------+----------+-------------
 7 trans_id | integer      |           | plain    |
 8 date     | date         |           | plain    |
 9 amount   | numeric(9,2) |           | main     |
10 region   | text         |           | extended |
11Compression Type: None
12Compression Level: 0
13Block Size: 32768
14Checksum: f
15Child tables: globalsales_1_prt_10,
16              globalsales_1_prt_11,
17              globalsales_1_prt_12,
18              globalsales_1_prt_13,
19              globalsales_1_prt_2,
20              globalsales_1_prt_3,
21              globalsales_1_prt_4,
22              globalsales_1_prt_5,
23              globalsales_1_prt_6,
24              globalsales_1_prt_7,
25              globalsales_1_prt_8,
26              globalsales_1_prt_9,
27              globalsales_1_prt_outlying_dates
28Has OIDs: no
29Options: appendonly=true
30Distributed randomly
31Partition by: (date)

你可以改变一个分区的名字，例如：

1ALTER TABLE sales RENAME PARTITION FOR ('2008-01-01') TO jan08;

添加一个默认分区 (Default Partition)#

你可以使用Alter命令添加一个默认分区。不满足任何分区条件的分区会进入默认分区。

1ALTER TABLE sales ADD DEFAULT PARTITION other;
2
3ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other;

删除一个分区#

你可以通过Alter命令删除一个分区。如果一个分区有子分区，在删除该分区的时候，它的子分区也会被删除。

对于一个分区的事实表，删除分区常用来删除保留时间窗口外的分区数据。

1ALTER TABLE sales DROP PARTITION FOR (RANK(1));

Truncate分区#

你可以通过Alter命令Truncate一个分区。在Truncate一个分区时，其子分区也会被Truncate。

1ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1));

交换分区#

你可以使用Alter Table命令来交换一个分区。交换分区操作把一个表和一个已存在分区进行交换（Swap）。你只可以交换叶子节点分区。

分区交换通常对数据加载很有用。例如，你可以首先加载数据到一个中间表，然后把该中间表交换到分区表内部。

你也可以利用分区交换改变分区表的类型。例如：

 1CREATE TABLE sales (id int, date date, amt decimal(10,2))
 2PARTITION BY RANGE (date)
 3( START (date '2008-01-01') INCLUSIVE
 4END (date '2009-01-01') EXCLUSIVE
 5EVERY (INTERVAL '1 month') );
 6
 7CREATE TABLE jan (LIKE sales) WITH (appendonly=true, orientation=row, compresstype = snappy);
 8
 9INSERT INTO jan SELECT * FROM sales_1_prt_1 ;
10
11ALTER TABLE sales EXCHANGE PARTITION FOR (RANK(1)) WITH TABLE jan;

分区分裂#

你可以使用Alter分裂一个已经存在的分区，例如下面的例子把sales_split分区表分裂成两个子分区：jan081to15和 jan0816to31。

1CREATE TABLE sales_split (id int, date date, amt decimal(10,2))
2PARTITION BY RANGE (date)
3( START (date '2008-01-01') INCLUSIVE
4END (date '2009-01-01') EXCLUSIVE
5EVERY (INTERVAL '1 month') );
6
7ALTER TABLE sales_split SPLIT PARTITION FOR ('2008-01-01')
8AT ('2008-01-16')
9INTO (PARTITION jan081to15, PARTITION jan0816to31);

如果你的分区表有Default分区的话，你只可以通过分裂Default分区的方法来添加子分区。例如，下面的例子通过分裂 Default分区的方式添加一个jan2009分区。

 1CREATE TABLE sales_split_default (id int, date date, amt decimal(10,2))
 2PARTITION BY RANGE (date)
 3( START (date '2008-01-01') INCLUSIVE
 4END (date '2009-01-01') EXCLUSIVE
 5EVERY (INTERVAL '1 month'), DEFAULT PARTITION extra);
 6
 7ALTER TABLE sales_split_default SPLIT DEFAULT PARTITION
 8START ('2009-01-01') INCLUSIVE
 9END ('2009-02-01') EXCLUSIVE
10INTO (PARTITION jan2009, default partition);

修改子分区模版#

你可以通过Alter命令修改子分区模版。先创建一个两级分区表。

 1CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text)
 2  DISTRIBUTED BY (trans_id)
 3  PARTITION BY RANGE (date)
 4  SUBPARTITION BY LIST (region)
 5  SUBPARTITION TEMPLATE
 6    ( SUBPARTITION usa VALUES ('usa'),
 7      SUBPARTITION asia VALUES ('asia'),
 8      SUBPARTITION europe VALUES ('europe'),
 9      DEFAULT SUBPARTITION other_regions )
10  ( START (date '2014-01-01') INCLUSIVE
11    END (date '2014-04-01') EXCLUSIVE
12    EVERY (INTERVAL '1 month') );

下面这条命令修改子分区模版。

1ALTER TABLE sales SET SUBPARTITION TEMPLATE
2( SUBPARTITION usa VALUES ('usa'),
3  SUBPARTITION asia VALUES ('asia'),
4  SUBPARTITION europe VALUES ('europe'),
5  SUBPARTITION africa VALUES ('africa'),
6  DEFAULT SUBPARTITION regions );

下面这条命令可以删除子分区模版。

1ALTER TABLE sales SET SUBPARTITION TEMPLATE ();

对已存在非分区表进行分区

对已存在表进行分区，你需要创建一个新的分区表，并把需要分区的表的数据导入新的表。并把相关权限分配好。

 1CREATE TABLE sales2 (LIKE sales)
 2PARTITION BY RANGE (date)
 3( START (date '2008-01-01') INCLUSIVE
 4   END (date '2009-01-01') EXCLUSIVE
 5   EVERY (INTERVAL '1 month') );
 6
 7INSERT INTO sales2 SELECT * FROM sales;
 8DROP TABLE sales;
 9
10ALTER TABLE sales2 RENAME TO sales;
11GRANT ALL PRIVILEGES ON sales TO admin;
12GRANT SELECT ON sales TO guest;