文档首页/ 数据仓库服务 GaussDB(DWS)/ 开发指南/ 标准数仓开发指南(9.1.0.x)/ 定义数据库对象/ 创建和管理表
更新时间：2024-09-02 GMT+08:00
查看PDF
分享

创建和管理表

创建表

CREATE TABLE命令创建一个表，创建表时可以定义以下内容：

示例：CREATE TABLE创建了一个表web_returns_p1，使用wr_item_sk作为分布键，并基于wr_returned_date_sk设置了range分布功能。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE TABLE web_returns_p1
(
    wr_returned_date_sk       integer,
    wr_returned_time_sk       integer,
    wr_item_sk                integer NOT NULL,
    wr_refunded_customer_sk   integer
)
WITH (orientation = column)
DISTRIBUTE BY HASH (wr_item_sk)
PARTITION BY RANGE(wr_returned_date_sk)
(
    PARTITION p2019 START(20191231) END(20221231) EVERY(10000),
    PARTITION p0 END(maxvalue)
);

表约束的定义

可以在列和表上定义约束来限制表中的数据，但是有以下一些限制：

  • 表中的主键约束和唯一约束必须包含分布列。
  • 列存表支持PARTIAL CLUSTER KEY、主键和唯一表级约束，不支持外键表级约束。
  • 列存表的字段约束只支持NULL、NOT NULL和DEFAULT常量值。
表1 表约束

约束项

说明

示例

检查约束

检查约束允许用户指定一个特定列中的值必须满足一个布尔（真值）表达式。

创建表products，要求产品价格为正：

1
2
3
4
5
6
 
CREATE TABLE products
(
   product_no integer, 
   name text, 
   price numeric CHECK (price > 0)
);

非空约束

非空约束指定一个列不能有空值。非空约束总是被写作为列约束。

创建表products，要求product_no和name不能为空：

1
2
3
4
5
6
 
CREATE TABLE products 
( 
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric 
);

唯一约束

唯一约束确保一列或者一组列中包含的数据对于表中所有的行都是唯一的。 如果没有声明DISTRIBUTE BY REPLICATION，则唯一约束的列集合中必须包含分布列。

创建表products，product_no值不能重复：

1
2
3
4
5
6
 
CREATE TABLE products 
(
    product_no integer UNIQUE, 
    name text, 
    price numeric
)DISTRIBUTE BY HASH(product_no);

主键约束

主键约束是一个UNIQUE约束和一个NOT NULL约束的组合。 如果没有声明DISTRIBUTE BY REPLICATION，则主键约束的列集合中必须包含分布列。如果一个表具有主键，这个列（或者这一组列）会被默认选中为该表的分布键。

创建表products，主键约束为product_no：

1
2
3
4
5
6
 
CREATE TABLE products 
( 
    product_no integer PRIMARY KEY, 
    name text, 
    price numeric
)DISTRIBUTE BY HASH(product_no);

局部聚簇

局部聚簇通过min/max稀疏索引较快的实现基表扫描的filter过滤。Partial Cluster Key可以指定多列，但是一般不建议超过2列。

创建表products，PCK为product_no：

1
2
3
4
5
6
7
 
CREATE TABLE products 
(
    product_no integer, 
    name text, 
    price numeric,
    PARTIAL CLUSTER KEY(product_no)
) WITH (ORIENTATION = COLUMN);

表分布的定义

GaussDB(DWS)支持的分布方式：复制表（Replication）、哈希表（Hash）和轮询表（Roundrobin）。

轮询表（Roundrobin）分布方式仅8.1.2及以上集群版支持。

策略

描述

适用场景

优势与劣势

复制表（Replication）

集群中每一个DN实例上都有一份全量表数据。

小表、维度表。
  • Replication优点是每个DN上都有此表的全量数据，在join操作中可以避免数据重分布操作，从而减小网络开销，同时减少了plan segment(每个plan segment都会起对应的线程)
  • Replication缺点是每个DN都保留了表的完整数据，造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。

哈希表（Hash）

表数据通过hash方式散列到集群中的所有DN实例上。

数据量较大的事实表。
  • 在读/写数据时可以利用各个节点的IO资源，大幅度提升表的读/写速度。
  • 一般情况下大表（1000000条记录以上）定义为Hash表。

轮询表（Roundrobin）

表的每一行被轮番地发送给各个DN，数据会被均匀地分布在各个DN中。

数据量较大的事实表，且使用Hash分布时找不到合适的分布列。
  • Roundrobin优点是保证了数据不会发生倾斜，从而提高了集群的空间利用率。
  • Roundrobin缺点是无法像Hash表一样进行DN本地化优化，查询性能通常不如Hash表。
  • 一般在大表无法找到合适的分布列时，定义为Roundrobin表，若大表能够找到合适的分布列，优先选择性能更好的Hash分布。

选择分布列

采用Hash分布方式，需要为用户表指定一个分布列（distribute key）。当插入一条记录时，系统会根据分布列的值进行hash运算后，将数据存储在对应的DN中。

所以Hash分布列选取至关重要，需要满足以下原则：

  1. 列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。
  2. 在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如，表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’)，那么就应当尽量不用zqdh做分布列。
  3. 在满足前两条原则的情况，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。

    对于Hash分表策略，如果分布列选择不当，可能导致数据倾斜，查询时出现部分DN的I/O短板，从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查，以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性

    1
2
3
4
5
    		 
    select 
xc_node_id, count(1) 
from tablename 
group by xc_node_id 
order by xc_node_id desc;

    其中xc_node_id对应DN，一般来说，不同DN的数据量相差5%以上即可视为倾斜，如果相差10%以上就必须要调整分布列。

  4. 一般不建议用户新增一列专门用作分布列，尤其不建议用户新增一列，然后用SEQUENCE的值来填充作为分布列。因为SEQUENCE可能会带来性能瓶颈和不必要的维护成本。

查看表数据

  • 使用系统表pg_tables查询数据库所有表的信息。 
    1
    		 
    SELECT * FROM pg_tables;
  • 使用gsql的\d+命令查询表的属性。 
    1
    		 
    \d+ customer_t1;
  • 执行如下命令查询表customer_t1的数据量。 
    1
    		 
    SELECT count(*) FROM customer_t1;
  • 执行如下命令查询表customer_t1的所有数据。 
    1
    		 
    SELECT * FROM customer_t1;
  • 执行如下命令只查询字段c_customer_sk的数据。 
    1
    		 
    SELECT c_customer_sk FROM customer_t1;
  • 执行如下命令过滤字段c_customer_sk的重复数据。 
    1
    		 
    SELECT DISTINCT( c_customer_sk ) FROM customer_t1;
  • 执行如下命令查询字段c_customer_sk为3869的所有数据。 
    1
    		 
    SELECT * FROM customer_t1 WHERE c_customer_sk = 3869;
  • 执行如下命令按照字段c_customer_sk进行排序。 
    1
    		 
    SELECT * FROM customer_t1 ORDER BY c_customer_sk;

删除表数据

在使用表的过程中，可能需要删除已过期的数据，删除数据必须从表中整行的删除。

SQL不能直接访问独立的行，只能通过声明被删除行匹配的条件进行。如果表中有一个主键，用户可以指定准确的行。用户可以删除匹配条件的一组行或者一次删除表中的所有行。

  • 使用DELETE命令删除行，如果删除表customer_t1中所有c_customer_sk为3869的记录： 
    1
    		 
    DELETE FROM customer_t1 WHERE c_customer_sk = 3869;
  • 如果执行如下命令，会删除表中所有的行。 
    1
    		 
    DELETE FROM customer_t1;

    1
    		 
    TRUNCATE TABLE customer_t1;

全表删除的场景下，建议使用truncate，不建议使用delete。进行大批量删除操作，truncate的删除速度比delete快得多。

  • 删除创建的表。 
    1
    		 
    DROP TABLE customer_t1;

管理UNLOGGED表

UNLOGGED表即非日志表。在非日志表中写入的数据不会被写入到预写日志中，这样就会比普通表快很多。但是非日志表在冲突、执行操作系统重启、强制重启、切断电源操作或异常关机后会被自动清理，会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。

使用场景：非日志表不能保证数据的安全性，需要在确保数据已经做好备份的前提下使用，例如系统升级时进行数据的备份。在创建非日志表时应将cnretry关闭（即设置GUC参数max_query_retry_times=0）。

故障处理：当异常关机等操作导致非日志表上的索引发生数据丢失时，用户应该对发生错误的索引进行重建。

  • 9.1.0版本后，UNLOGGED表默认存储在pg_unlogged表空间下，且不可迁移或指定到其他表空间。
  • 从低版本升级到9.1.0版本后，旧版本创建的UNLOGGED表还存储在原表空间下。

9.1.0版本提供UNLOGGED表迁移脚本switch_unlogged_tablepace.py，配合GUC参数enable_unlogged_tablespace_compat，可以优化RTO。

  1. 脚本位于/opt/huawei/wisequery/script目录，可以通过-?获取帮助信息。

  2. 迁移所有UNLOGGED表（建议） 
    1
    		 
    python3 switch_unlogged_tablepace.py -t switch

3. 迁移成功后，GUC参数enable_unlogged_tablespace_compat会自动设置为off。

建议在升级到9.1.0版本后通过以下两步操作提升实例重启RTO：

  1. 使用switch_unlogged_tablespace.py脚本将UNLOGGED表全部迁移到pg_unlogged表空间。
  2. 如果旧版本没有使用UNLOGGED表，建议将GUC参数enable_unlogged_tablespace_compat设置为OFF。
父主题： 定义数据库对象