关于oushudb-hawq:OushuDB-产品基本介绍表

49次阅读

共计 13508 个字符，预计需要花费 34 分钟才能阅读完成。

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)

10、增加一个分区

你能够通过上面的语句增加一个分区。

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

如果你在创立表的时候没有应用 subpartition template，你须要在增加分区的时候给出子分区定义，例如：

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') );

你也能够独自批改一个二级分区：

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 个分区。
注：指定一个分区能够应用

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 分区表时，如果没有指定分区名字，分区的名字会主动生成为数字。
扭转父表的名字同时会扭转分区表的名字。例如：


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)

你能够扭转一个分区的名字，例如：

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

12、增加一个默认分区 (Default Partition)

你能够应用 Alter 命令增加一个默认分区。不满足任何分区条件的分区会进入默认分区。

ALTER TABLE sales ADD DEFAULT PARTITION other;

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

13、删除一个分区

你能够通过 Alter 命令删除一个分区。如果一个分区有子分区，在删除该分区的时候，它的子分区也会被删除。
对于一个分区的事实表，删除分区罕用来删除保留工夫窗口外的分区数据。

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

14、Truncate 分区

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

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

15、替换分区

你能够应用 Alter Table 命令来替换一个分区。替换分区操作把一个表和一个已存在分区进行替换（Swap）。你只能够替换叶子节点分区。
分区替换通常对数据加载很有用。例如，你能够首先加载数据到一个两头表，而后把该两头表替换到分区表外部。
你也能够利用分区替换扭转分区表的类型。例如：

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。

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 分区。

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 命令批改子分区模版。先创立一个两级分区表。

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') );

上面这条命令批改子分区模版。

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 );

上面这条命令能够删除子分区模版。

ALTER TABLE sales SET SUBPARTITION TEMPLATE ();

对已存在非分区表进行分区
对已存在表进行分区，你须要创立一个新的分区表，并把须要分区的表的数据导入新的表。并把相干权限调配好。

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;

正文完
oushudb-hawq
发表至： oushuDB-HAWQ
2021-11-24
 0
关于oushudb-hawq:OushuDB-小课堂丨如何更好的进行数据管理10-条建议给到你
关于oushudb-hawq:oushudb丨偶数科技为辽宁农信数字化进程添加新动能
关于oushudb-hawq:OushuDB-用户指南图算法之GQL脚本命令
关于oushudb-hawq:偶数科技入选2021爱分析数据智能平台厂商全景报告