1、表OushuDB表由行（rows）和（columns）组成。每一个列有一个列名和一个数据类型，一个表的列数和列的顺序是固定的。一个表的行数是可变的。SQL并不假设表中行的顺序。当读一个表时，除非显示要求排序，返回的行会以任意顺序出现。另外，SQL并不给每一行一个唯一标志符，所以，一个表中具有同样几个同样的行是可能的。 创建一个表可以使用create table命令。在命令里面，需要指定表名，列名以及列的类型。例如： 1 2 3 4 | create table my_first_table ( first_column text, second_column integer ); |
|
上面的命令创建了一个两列的表，一列为文本类型，一列为整数类型。删除刚刚创建的表可以使用drop table命令。 1 | 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支持。 对于各种格式的表的建表语法，下面给出了几个例子。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | # 默认创建的是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 分布。下面这个两个例子等价。 1 2 3 4 5 | 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。 1 2 3 | 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分布，则这个查询在执行时不再需要重新分布任何一张表就可以并行在各个节点并行执行连接操作。 1 2 3 | 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分区 1 2 3 4 5 6 7 | # 创建一个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+给出该表的所有信息 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 | 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) |
|
你也可以显式得声明子分区并指定子表名字。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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 ); |
|
查看创建的表信息 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 | 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。 1 2 3 4 | 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分区，你需要 显式的指定所有子分区。 1 2 3 4 5 | 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 ); |
|
查看表信息 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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分区。 1 2 3 4 5 6 7 8 9 10 11 12 | 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表的分区设计。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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) |
|
10、添加一个分区你可以通过下面的语句添加一个分区。 1 2 3 | ALTER TABLE sales ADD PARTITION START (date '2009-02-01') INCLUSIVE END (date '2009-03-01') EXCLUSIVE; |
|
如果你在创建表的时候没有使用subpartition template，你需要在添加分区的时候给出子分区定义，例如： 1 2 3 4 5 6 | ALTER TABLE sales ADD PARTITION START (date '2009-02-01') INCLUSIVE END (date '2009-03-01') EXCLUSIVE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe') ); |
|
你也可以单独修改一个二级分区： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE sales_two_level (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')) (START (date '2011-01-01') INCLUSIVE END (date '2012-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'), DEFAULT PARTITION outlying_dates); ALTER TABLE sales_two_level ALTER PARTITION FOR (RANK(12)) ADD PARTITION africa VALUES ('africa'); |
|
其中RANK(12)表示第12个分区。 注：指定一个分区可以使用 1 | PARTITION FOR (value) or PARTITION FOR(RANK(number))语法。 |
|
如果你的分区表有一个Default分区的话，你不可以向该分区表添加分区，你只可以通过分裂Default分区的方法来添加分区。 11、重命名分区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分区表时， 如果没有指定分区名字，分区的名字会自动生成为数字。 改变父表的名字同时会改变分区表的名字。例如： 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 28 29 30 31 | postgres=＃ ALTER TABLE sales_two_level RENAME TO globalsales; postgres=＃ \d+ globalsales Append-Only Table "public.globalsales" Column | Type | Modifiers | Storage | Description ----------+--------------+-----------+----------+------------- trans_id | integer | | plain | date | date | | plain | amount | numeric(9,2) | | main | region | text | | extended | Compression Type: None Compression Level: 0 Block Size: 32768 Checksum: f Child tables: globalsales_1_prt_10, globalsales_1_prt_11, globalsales_1_prt_12, globalsales_1_prt_13, globalsales_1_prt_2, globalsales_1_prt_3, globalsales_1_prt_4, globalsales_1_prt_5, globalsales_1_prt_6, globalsales_1_prt_7, globalsales_1_prt_8, globalsales_1_prt_9, globalsales_1_prt_outlying_dates Has OIDs: no Options: appendonly=true Distributed randomly Partition by: (date) |
|
你可以改变一个分区的名字，例如： 1 | ALTER TABLE sales RENAME PARTITION FOR ('2008-01-01') TO jan08; |
|
12、添加一个默认分区 (Default Partition)你可以使用Alter命令添加一个默认分区。不满足任何分区条件的分区会进入默认分区。 1 2 3 | ALTER TABLE sales ADD DEFAULT PARTITION other; ALTER TABLE sales ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other; |
|
13、删除一个分区你可以通过Alter命令删除一个分区。如果一个分区有子分区，在删除该分区的时候，它的子分区也会被删除。 对于一个分区的事实表，删除分区常用来删除保留时间窗口外的分区数据。 1 | ALTER TABLE sales DROP PARTITION FOR (RANK(1)); |
|
14、Truncate分区你可以通过Alter命令Truncate一个分区。在Truncate一个分区时，其子分区也会被Truncate。 1 | ALTER TABLE sales TRUNCATE PARTITION FOR (RANK(1)); |
|
15、交换分区你可以使用Alter Table命令来交换一个分区。交换分区操作把一个表和一个已存在分区进行交换（Swap）。你只可以交换叶子节点分区。 分区交换通常对数据加载很有用。例如，你可以首先加载数据到一个中间表，然后把该中间表交换到分区表内部。 你也可以利用分区交换改变分区表的类型。例如： 1 2 3 4 5 6 7 8 9 10 11 | 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') ); CREATE TABLE jan (LIKE sales) WITH (appendonly=true, orientation=parquet, compresstype = snappy); INSERT INTO jan SELECT * FROM sales_1_prt_1 ; ALTER TABLE sales EXCHANGE PARTITION FOR (RANK(1)) WITH TABLE jan; |
|
16、分区分裂你可以使用Alter分裂一个已经存在的分区，例如下面的例子把sales_split分区表分裂成两个子分区：jan081to15和 jan0816to31。 1 2 3 4 5 6 7 8 9 | CREATE TABLE sales_split (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') ); ALTER TABLE sales_split SPLIT PARTITION FOR ('2008-01-01') AT ('2008-01-16') INTO (PARTITION jan081to15, PARTITION jan0816to31); |
|
如果你的分区表有Default分区的话，你只可以通过分裂Default分区的方法来添加子分区。例如，下面的例子通过分裂 Default分区的方式添加一个jan2009分区。 1 2 3 4 5 6 7 8 9 10 | CREATE TABLE sales_split_default (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'), DEFAULT PARTITION extra); ALTER TABLE sales_split_default SPLIT DEFAULT PARTITION START ('2009-01-01') INCLUSIVE END ('2009-02-01') EXCLUSIVE INTO (PARTITION jan2009, default partition); |
|
17、修改子分区模版你可以通过Alter命令修改子分区模版。先创建一个两级分区表。 1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text) DISTRIBUTED BY (trans_id) 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 '2014-01-01') INCLUSIVE END (date '2014-04-01') EXCLUSIVE EVERY (INTERVAL '1 month') ); |
|
下面这条命令修改子分区模版。 1 2 3 4 5 6 | ALTER TABLE sales SET SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe'), SUBPARTITION africa VALUES ('africa'), DEFAULT SUBPARTITION regions ); |
|
下面这条命令可以删除子分区模版。 1 | ALTER TABLE sales SET SUBPARTITION TEMPLATE (); |
|
对已存在非分区表进行分区 对已存在表进行分区，你需要创建一个新的分区表，并把需要分区的表的数据导入新的表。并把相关权限分配好。 1 2 3 4 5 6 7 8 9 10 11 12 | CREATE TABLE sales2 (LIKE sales) PARTITION BY RANGE (date) ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') ); INSERT INTO sales2 SELECT * FROM sales; DROP TABLE sales; ALTER TABLE sales2 RENAME TO sales; GRANT ALL PRIVILEGES ON sales TO admin; GRANT SELECT ON sales TO guest; |
|
