1、表
OushuDB表由行（rows）和（columns）组成。每一个列有一个列名和一个数据类型，一个表的列数和列的顺序是固定的。一个表的行数是可变的。SQL并不假设表中行的顺序。当读一个表时，除非显示要求排序，返回的行会以任意顺序出现。另外，SQL并不给每一行一个唯一标志符，所以，一个表中具有同样几个同样的行是可能的。
创建一个表可以使用create table命令。在命令里面，需要指定表名，列名以及列的类型。例如：
create table my_first_table ( first_column text, second_column integer );
上面的命令创建了一个两列的表，一列为文本类型，一列为整数类型。删除刚刚创建的表可以使用drop table命令。
drop table my_first_table;
2、表的存储格式
OushuDB现在支持多种存储格式：AO，Parquet，ORC，MagmaAP。AO是按行存储的格式，而Parquet，ORC，MagmaAP是按列存储的格式。 其中MagmaAP 是在4.0.0.0发布的全新的存储格式。MagmaAP，ORC都支持update／delete，支持事务， 且MagmaAP还支持index。
注：和GPDB类似，之前OushuDB版本支持CO格式，但CO格式不适合集群大和分区多的情况，后续新版本去除了CO支持。
对于各种格式的表的建表语法，下面给出了几个例子。
# 默认创建的是AO表 CREATE TABLE rank1 (id int, rank int, year smallint,gender char(1), count int ); # 和上面的创建的表一样，显式指定存储格式类型 CREATE TABLE rank2 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =row); # 创建一个snappy压缩的AO表 CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =row, compresstype = snappy); # 创建一个snappy压缩的Parquet表，如果不指定压缩类型的话，默认不压缩。 CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =parquet, compresstype = snappy); # 创建一个不压缩的ORC表，如果不指定压缩类型的话，默认不压缩。 CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =orc); # 创建一个带压缩的ORC表，需指定压缩类型。 CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =orc, compresstype = lz4); # 创建一个压缩的magma表， magma 内部自动实现了压缩。 CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) format 'magmaap'; # 创建一个有primary key的magma表， magma 内部自动实现了压缩。 CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int，primary key(id) ) format 'magmaap';
3、表的分布
在OushuDB中，表可以两种方式分布方式：基于Hash的分布和Random分布。基于Hash的分布方法基于分布列的Hash值进行分布，Random分布采取随机分布模式。
创建表时用户不指定分布方式的时候非magmaap表默认使用Random分布，magmaap表目前不支持Random 分布。下面这个两个例子等价。
CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int ); CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int ); DISTRIBUTED RANDOMLY;
下面这个例子创建一个Hash分布的表，分布的Key使用三个列（rank, gender, year）的组合，数据分布到32个bucket里面。
如果不指定bucketnum的话，系统默认使用default_hash_table_bucket_number系统参数的值来做为bucketnum。
CREATE TABLE rank (id int, rank int, year smallint,gender char(1), count int ) WITH (bucketnum = 32) DISTRIBUTED BY (rank, gender,year);
4、Hash分布和Random分布的选取
非Magma表： Random分布的表较灵活，在系统扩容添加节点后无需重新分布数据。而Hash分布的表在系统扩容后，为了利用新增加节点的计算能力，需要重新分布数据。另外，针对Hash分布的表资源管理器在分配资源的时候采取分配固定virtual segment数的方式，不如Random分布灵活。
Hash分布的表在某些查询上会有性能上的好处，因为有时可以避免重新分布某些表。
例如下面例子的查询，如果lineitem和orders两张表分别按照l_orderkey和o_orderkey分布，则这个查询在执行时不再需要重新分布任何一张表就可以并行在各个节点并行执行连接操作。
SELECT l_orderkey, count(l_quantity) FROM lineitem, orders WHERE l_orderkey = o_orderkey
针对绝大多数查询，实验表明都不是网络瓶颈，基于Hash分布和基于Random分布性能差别不大。所以我们建议用户默认采取Random分布， 只针对特定需要优化的场合使用Hash分布的表。
Magma表具备的Hash和random 表的优势，通过 default_magma_hash_table_nvseg_per_node 来控制每个节点能启动的virtual segment数。 在系统扩容后不需要重新分布数据。
5、Hash分布的表bucketnum的选取
针对Hash分布的表，bucketnum决定了一个查询的并行度。在一些常见的硬件配置中（128G内存和12块SAS盘），我们建议选取6 节点数或者8 节点数。 硬件更好的话可以增加bucketnum。在系统初始化的时候，default_hash_table_bucket_number的初始化默认值为8 * 节点数。Magma table 使用default_magma_hash_table_nvseg_per_node, 表示每个节点是virtual segment 的个数。：
6、表分区
针对大的数据仓库事实表，往往我们可以通过对表进行分区的方式来把一个很大的表拆分成多个子表。这样的话，有两个好处:
● 查询优化器可以针对分区表进行优化，如果查询只设计到某些分区，则查询计划只需要扫描这些分区，从而加速查询
● 如果我们按照日期进行分区的话，我们可以简单的加入分区和删除过期的分区。
OushuDB支持基于Range和List的两种分区方式。
● Range分区：依据数值范围进行分区，比如日期，价格等
● List分区：依据一个值的列表进行分区，比如地区等
下面我们通过例子说明这两种分区的使用方式。
Range分区
# 创建一个sales表，按照date列Range分区，从2008年到2009年每月创建一个分区 postgres=＃ CREATE TABLE sales (id int, date date, amt decimal(10,2)) PARTITION BY RANGE (date) ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
查看创建的表信息，d+给出该表的所有信息
postgres=# \d+ sales Append-Only Table "public.sales" Column | Type | Modifiers | Storage | Description --------+---------------+-----------+---------+------------- id | integer | | plain | date | date | | plain | amt | numeric(10,2) | | main | Compression Type: None Compression Level: 0 Block Size: 32768 Checksum: f Child tables: sales_1_prt_1, sales_1_prt_10, sales_1_prt_11, sales_1_prt_12, sales_1_prt_2, sales_1_prt_3, sales_1_prt_4, sales_1_prt_5, sales_1_prt_6, sales_1_prt_7, sales_1_prt_8, sales_1_prt_9 Has OIDs: no Options: appendonly=true Distributed randomly Partition by: (date)
你也可以显式得声明子分区并指定子表名字。
CREATE TABLE sales_exp (id int, date date, amt decimal(10,2)) PARTITION BY RANGE (date) ( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE , PARTITION Feb08 START (date '2008-02-01') INCLUSIVE , PARTITION Mar08 START (date '2008-03-01') INCLUSIVE , PARTITION Apr08 START (date '2008-04-01') INCLUSIVE , PARTITION May08 START (date '2008-05-01') INCLUSIVE , PARTITION Jun08 START (date '2008-06-01') INCLUSIVE , PARTITION Jul08 START (date '2008-07-01') INCLUSIVE , PARTITION Aug08 START (date '2008-08-01') INCLUSIVE , PARTITION Sep08 START (date '2008-09-01') INCLUSIVE , PARTITION Oct08 START (date '2008-10-01') INCLUSIVE , PARTITION Nov08 START (date '2008-11-01') INCLUSIVE , PARTITION Dec08 START (date '2008-12-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE );
查看创建的表信息
postgres=＃ \d+ sales_exp Append-Only Table "public.sales_exp" Column | Type | Modifiers | Storage | Description --------+---------------+-----------+---------+------------- id | integer | | plain | date | date | | plain | amt | numeric(10,2) | | main | Compression Type: None Compression Level: 0 Block Size: 32768 Checksum: f Child tables: sales_exp_1_prt_apr08, sales_exp_1_prt_aug08, sales_exp_1_prt_dec08, sales_exp_1_prt_feb08, sales_exp_1_prt_jan08, sales_exp_1_prt_jul08, sales_exp_1_prt_jun08, sales_exp_1_prt_mar08, sales_exp_1_prt_may08, sales_exp_1_prt_nov08, sales_exp_1_prt_oct08, sales_exp_1_prt_sep08 Has OIDs: no Options: appendonly=true Distributed randomly Partition by: (date)
下面是另外一个根据Range分区的例子，这次使用的是整型列进行分区。这里面我们添加了一个DEFAULT PARTITION， 在不满足其他分区的条件下，数据会被插入DEFAULT PARTITION。
CREATE TABLE rank (id int, rank int, year int, gender char(1), count int) PARTITION BY RANGE (year) ( START (2001) END (2008) EVERY (1), DEFAULT PARTITION extra );
7、List分区
下面的例子创建了一个基于List的分区表。List分区表可以基于任意支持等值比较的数据类型。对与List分区，你需要 显式的指定所有子分区。
postgres=＃ CREATE TABLE rank (id int, rank int, year int, gender char(1), count int ) PARTITION BY LIST (gender) ( PARTITION girls VALUES ('F'), PARTITION boys VALUES ('M'), DEFAULT PARTITION other );
postgres=＃ \d+ rank Append-Only Table "public.rank" Column | Type | Modifiers | Storage | Description --------+--------------+-----------+----------+------------- id | integer | | plain | rank | integer | | plain | year | integer | | plain | gender | character(1) | | extended | count | integer | | plain | Compression Type: None Compression Level: 0 Block Size: 32768 Checksum: f Child tables: rank_1_prt_boys, rank_1_prt_girls, rank_1_prt_other Has OIDs: no Options: appendonly=true Distributed randomly Partition by: (gender)
8、多级分区
你可以使用SUBPARTITION模版定义多级分区。下面的例子定义了一个两级分区表，第一级安装date列进行Range分区，第二级按照region列进行List分区。
CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text) PARTITION BY RANGE (date) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe'), DEFAULT SUBPARTITION other_regions) (START (date '2011-01-01') INCLUSIVE END (date '2012-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'), DEFAULT PARTITION outlying_dates);
注：当你在使用多级分区的时候，系统会产生大量的小表，有些表可能没有数据或包含很少数据，这样会对系统元数据管理产生过多压力。 建议不要创建具有过多分区的表。一般限制分区数在100或以内比较合理。
9、查看你的分区设计
你可以通过pg_partitions视图来查看你的分区表设计。例如通过下面的语句可以查看出sales表的分区设计。
postgres=# SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank postgres-# FROM pg_partitions postgres-# WHERE tablename='sales'; partitionboundary | partitiontablename | partitionname | partitionlevel | partitionrank ------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+--------------- START ('2008-01-01'::date) END ('2008-02-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_1 | | 0 | 1 START ('2008-02-01'::date) END ('2008-03-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_2 | | 0 | 2 START ('2008-03-01'::date) END ('2008-04-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_3 | | 0 | 3 START ('2008-04-01'::date) END ('2008-05-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_4 | | 0 | 4 START ('2008-05-01'::date) END ('2008-06-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_5 | | 0 | 5 START ('2008-06-01'::date) END ('2008-07-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_6 | | 0 | 6 START ('2008-07-01'::date) END ('2008-08-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_7 | | 0 | 7 START ('2008-08-01'::date) END ('2008-09-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_8 | | 0 | 8 START ('2008-09-01'::date) END ('2008-10-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_9 | | 0 | 9 START ('2008-10-01'::date) END ('2008-11-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_10 | | 0 | 10 START ('2008-11-01'::date) END ('2008-12-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_11 | | 0 | 11 START ('2008-12-01'::date) END ('2009-01-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_12 | | 0 | 12 (12 rows)
