01-正文

本章节下载 (1.80 MB)

01-正文

3.3.3 gpstate（查看集群状态）

3.3.4 ssgather（一键收集集群日志）

4.6 非并行备份与恢复（pg_dump与pg_restore）

10.1.2 普通用户创建内部表无法copy本地数据至该内部表中怎么办？

10.1.3 数据库变慢了，怎么解决？

10.1.4 SQL查询慢，怎么解决？

10.1.5 数据导入慢怎么解决？

1 SeaSQL MPP简介

1.1 概述

SeaSQL MPP是一款基于PostgreSQL的分布式数据库，采用Shared-Nothing架构，其主机、操作系统、内存和存储都是自我控制，不存在共享。该数据库架构具有很强的横向扩展能力，并能够提供高效的并行处理能力，并支持SQL 92、SQL 99以及SQL 2003，同时支持窗口函数、cube、rollup等函数。因其具备PB级数据量的快速分析能力，现在金融、医疗、教育、交通等领域被广泛应用。

1.1.1 特点

· 支持海量数据存储和处理

· 具有良好的扩展性能，支持线性扩展

· 支持主流的SQL语法，支持多语言的自定义函数和自定义类型等

· 支持PB级别数据量的实时查询分析能力

· 支持分布式事务，支持ACID，保证数据的强一致性

· 图形化的Monitor监控界面，集群状态、性能指标、邮件通知的实时监控

· 支持行列混合存储

· 高性能加载，提供PB级别数据量的加载性能

· 支持x86_64、ARM架构平台

· 支持库内机器学习

· 支持PostGIS空间信息处理

· 支持异构数据库的查询

· 支持PL/pgSQL、PL/R、PL/Java存储过程

· 支持B-Tree、Bitmap、GiST、GIN等多种类型的索引

· 支持多种存储压缩算法

· 支持资源隔离、任务优先级设定、多租户系统

· 支持多种标准接口：SQL/JDBC/ODBC等

1.1.2 适用场景

· 适用于面向分析的应用，比如构建企业级的ODS/EDW、数据集市等。

· 适用于需要处理海量数据的应用，比如数据仓库、商业智能等。

· 离线数据的分析和数据挖掘，如客户行为分析、人物画像、行为预测建模等。

1.2 架构

如图1-1所示，SeaSQL MPP架构主要由主节点、数据节点和Interconnect三个部分组成。

1. 主节点（Master）

Master是SeaSQL MPP数据库系统的入口，负责：

· 接受客户端的连接请求，建立与客户端的会话连接和管理

· 处理SQL命令，解析生成分布式的执行计划，并分发执行计划至数据节点上

· 汇总数据节点的执行结果，将结果返回给客户端

· Master不存储业务数据，仅存储数据字典（例如表结构定义、索引、数据分布等信息）

· Master分为主节点（Master）和从主节点（Standby Master），Master与Standby Master之间通过流复制的方式进行数据同步组成Master的高可用特性

2. 数据节点（Segment节点）

Segment节点存储实际的业务数据，负责：

· 执行Master下发的SQL语句

· 对于Master，每个Segment节点都是对等的，负责对应数据的存储和计算

· Segment节点上的任务实际上是运行在Segment Instance（实例）上的。每个Segment节点上可以运行多个实例，实例分为Primary和Mirror，互为备份

¡ 通常同一数据块的Primary和Mirror交错的存放在不同的Segment节点上

¡ 当Primary故障时，Mirror会自动唤醒代替Primary工作，保持集群可用状态

¡ Segment instance可以动态扩展，既可以在原有主机上进行增加Segment instance操作，也可以通过新增主机来增加Segment instance

3. Interconnect

Interconnect是SeaSQL MPP数据库的网络层，主节点和数据节点之间通过万兆网卡组成的Interconnect进行通信。

图1-1 SeaSQL MPP架构

1.3 相关概念

· MPP架构：（Massive Parallel Processing，大规模并行计算），数据库集群架构中的一种，其基本特征是：由多个主机通过节点互联而成，每个节点只访问自己的本地资源（内存、存储等），是一种完全无共享（Share Nothing）的结构。MPP架构扩展能力较高，理论上可以无限扩展，目前的技术可实现数百个节点互联，支持数千个CPU。

· 数据库集群：用来安装部署数据库服务的一组计算机资源。

· 集群模式：分为独立集群和共享集群两种。

¡ 独立集群：每个租户可以使用独立集群，网络和资源互相隔离。

¡ 共享集群：多个租户之间共享一套数据库集群，可以通过创建多个共享资源来共享集群。

· 共享资源：租户及其对应的数据库从共享集群中申请系统资源，包括内存、CPU核数、磁盘以及最大并发数等资源。

2 快速入门

2.1 快速使用指导

2.1.1 连接数据库

连接数据库前须登录SeaSQL MPP数据库的Master节点。可以在SeaSQL MPP Monitor的[实例详情]页面中查找SeaSQL MPP数据库的Master节点。关于SeaSQL MPP Monitor，详情请参见6 SeaSQL MPP Monitor。

SeaSQL MPP支持使用PostgreSQL兼容的客户端来连接数据库，例如psql和pgAdmin。

1. psql

psql提供以交互式命令行方式来访问SeaSQL MPP数据库。

(1) ssadmin用户在本地连接数据库，操作步骤如下：

a. 在root用户下执行su - ssadmin命令切换至ssadmin用户。

b. 在ssadmin用户下执行psql命令，即可成功连接数据库。

(2) 其它用户在本地或远程连接数据库，操作步骤如下：

a. 其它用户本地或远程连接数据库需要指定连接数据库的相关参数，执行psql -h host_address -p port -U username -d database命令。其中：

- -h：指定主机地址

- -p：指定端口号，默认为5434

- -U：指定连接的数据库用户，默认为ssadmin

- -d：指定数据库，默认为postgres

b. 输入指定数据库用户的密码，成功连接到数据库后，psql会出现一个提示符，包含连接的数据库名和一串字符（例如postgres=#）。

2. pgAdmin连接

pgAdmin是PostgreSQL的图形客户端，可以直接连接SeaSQL MPP数据库。

根据需要可以从PostgreSQL官网选择下载中文或英文版本的pgAdmin客户端。

本章节以下载中文版客户端pgAdmin4-1.6为例，操作如下：

(1) 下载并安装pgAdmin4-1.6客户端。

(2) 选择[对象]>[创建服务器]，打开“创建-服务器”配置框。

(3) 配置服务器的属性，如表3-1所示。其中：

¡ 名称：自定义服务器名称

¡ 服务器组：根据需要可以将此服务器添加到一个组里

¡ 主机名称/地址：填写数据库的Master节点IP

¡ 端口号：Master节点的端口号，默认为5434

¡ 维护数据库：要连接的数据库名

¡ 用户名：连接数据库的用户名

¡ 密码：用户密码

其他参数保持默认即可

图2-1 属性配置

(4) 单击<确定>，即可连接到SeaSQL MPP数据库。

2.1.2 导入数据

SeaSQL MPP数据加载有copy、gpfdist和gpload这三种方式。简单的数据导入可使用copy命令，gpfdist以及gpload适用于大数据量并行的数据导入，关于gpfdist和gpload，详情请参见4 ETL工具。

使用copy命令可以将本地文本文件数据导入SeaSQL MPP数据库中，但要求本地文本文件是格式化的，例如使用逗号、分号或特有符号作为分割符号的文件。

使用copy命令将/home/ssadmin/item.dat文件中的数据导入到数据库的item表中，操作步骤如下：

(1) 在SeaSQL MPP数据库中创建对应的item表，执行如下命令：

CREATE TABLE item(id int, name varchar(20));

(2) 将数据copy到数据库，执行如下命令：

postgres=# copy public.item from '/home/ssadmin/item.dat' with delimiter '|' null '';

3 使用指南

· 使用SeaSQL MPP数据库时，不建议操作和使用系统默认数据库postgres、template0和template1。

· 禁止在未关闭SeaSQL MPP服务时，强制进行服务器断电操作。

3.1 用户权限管理

SeaSQL MPP通过角色来管理数据库的访问权限。角色是一系列相关权限的集合，包含用户和组，可以把一系列相关的数据库权限赋给一个角色。通过一个组来统一授予权限和回收权限以实现权限的批量管理。

1. 用户与权限

· 管理员ssadmin用户

¡ 系统用户ssadmin

SeaSQL MPP安装后，默认会创建一个操作系统用户ssadmin作为SeaSQL MPP数据库的管理员用户。在创建SeaSQL MPP集群时，可以配置该用户的密码。该用户是SeaSQL MPP服务运行的基础，如果没有该用户SeaSQL MPP服务将无法运行。

¡ 数据库管理员用户

在新建SeaSQL MPP集群时，根据需要可以配置数据库管理员用户的名称以及密码，数据库管理员可用于较细粒度的用户授权（如表的授权）。

· 新建集群时配置的数据库管理员为“管理员用户”；在[用户管理/新建用户]页面上或在在新建共享资源时新建的用户均为“普通用户”。

· 根据需要，可以通过数据库管理员用户为普通用户授予更细粒度的数据库的操作权限。

· 为每个登录SeaSQL MPP数据库的用户可以分配不同的角色（Role）。对于应用程序(APP)或者Web Service来说，应该考虑为每个应用程序或者Service建立独立的角色。

· 控制具备超级用户属性的用户数量。数据库管理员可以在创建角色时，为其它角色赋予超级用户属性，具有超级属性的角色将可以像数据库管理员那样绕过SeaSQL MPP的所有权限限制，包括资源队列。

2. 用户权限控制

· 在SeaSQL MPP数据库中，创建User时默认该用户会具有LOGIN权限，而创建Role时则需手动指定其LOGIN权限。

· 除LOGIN权限外，其它数据库操作权限都需要手动授予User或Role。

创建Role时，根据需要可以指定其数据库操作权限。例如，创建Role lee时赋予该角色登录数据库的权限，命令如下：

CREATE ROLE lee WITH LOGIN;

Role的属性决定其可以操作数据库的权限。可以在创建Role时指定其属性，也可以在创建Role之后使用ALTER Role来指定其属性。Role的属性说明如表3-1所示。

表3-1 Role的属性说明

属性	描述
SUPERUSER \| NOSUPERUSER	· SUPERUSER可以绕过所有权限限制，SUPERUSER使用有风险，建议谨慎使用 · CREATE ROLE时默认属性为NOSUPERUSER
CREATEDB \| NOCREATEDB	· 是否具备CREATE DATABASE权限 · 默认为NOCREATEDB
CREATEROLE \| NOCREATEROLE	· 是否具备CREATE和管理其他ROLE的权限 · 默认为NOCREATEROLE
INHERIT \| NOINHERIT	· 决定其权限是否被其子成员继承 · 默认属性为INHERIT
LOGIN \| NOLOGIN	决定ROLE是否可以登录数据库。其中： · 具备LOGIN属性的ROLE就是USER · 不具备LOGIN属性的ROLE一般用来做权限管理(GROUP) · 默认值为NOLOGIN
CONNECTION LIMIT connlimit	· 对于具有LOGIN属性的ROLE来说，决定其最多可同时有多少个连接 · 默认值为-1(无限制)
CREATEEXTTABLE \| NOCREATEEXTTABLE	设置一个ROLE是否有创建外部表的权限，默认是NOCREATEEXTTABLE，即不允许创建外部表
PASSWORD ‘password’	· 设置ROLE的PASSWORD · 如果不打算使用密码登录，可忽略该属性，如果选择了该属性却没有设定密码，则PASSWORD会被设置为NULL并且始终无法登录。 · 空密码可以明确定义PASSWORD NULL
ENCRYPTED \| UNENCRYPTED	· 控制密码是否在系统目录中进行加密存储 · 如果提供的密码字符已采用md5加密，则不管是否指定了ENCRYPTED或者UNENCRYPTED，它都按原样进行加密存储（因为无法解密指定加密的字符串）
VALID UNTIL ‘timestamp’	· 设置在指定日期后该Role的密码会失效 · 不设置的情况下为永远有效
RESOURCE QUEUE queue_name	· 将Role分配到指定的资源队列(RQ)，所有的语句都受到该RQ的约束 · 默认的是pg_default 【说明】该属性不会被继承，必须为每个User单独指定该属性
DENY {deny_interval \| deny_point}	定义允许登录的时间段

3. 数据库对象权限管理

当数据库对象（表、视图、序列、数据库、函数、Schema或者表空间等）被创建时，它会被分配一个拥有者。拥有者是执行创建该数据库对象的数据库用户。默认只有拥有者或者数据库管理员用户可以操作该数据库对象。若其他用户需要使用它，必须对其进行授权操作。SeaSQL MPP数据库对数据库对象类型支持的权限如表3-2所示。

· 关于grant、revoke，可以使用\h grant或\h revoke查看其使用语法。

· 每个数据库对象的权限必须被单独的授予给用户。例如，在一个数据库上授予某用户“ALL”的权限，但是该用户并不会被授予该数据库中已存在的所有对象的操作权限。它只会将数据库级别的权限（CONNECT、CREATE、TEMPORARY）授权给该用户。

表3-2 Role的属性说明

对象类型	描述
表、视图、序列	SELECT、INSERT、UPDATE、DELETE、RULE、ALL
外部表	SELECT、RULE、ALL
数据库	CONNECT、CREATE、TEMPORARY或TEMP、ALL
函数	EXECUTE
过程语言	USAGE
Schemas	CREATE、USAGE、ALL
自定义协议	SELECT、INSERT、UPDATE、DELETE、RULE、ALL

可以使用DROP OWNED和REASSIGN OWNED命令来管理用户所有拥有的对象，但是只有对象的拥有者或者数据库管理员用户才能删除对象或者重新分配对象权限。删除用户前，需要先删除该用户的所有数据库对象，删除用户的数据库对象有以下两种方法：

· 将sally用户的所有数据库对象授权给bob用户，命令如下：

REASSIGN OWNED BY sally TO bob;

· 直接删除sally用户的所有数据库对象，命令如下

DROP OWNED BY sally;

4. 行级安全策略

行级安全策略是一种粒度更细的，可以针对表行级所做的权限控制。它可以设置用户对哪些行执行查询、创建、删除、修改等操作。默认情况下，数据库表是没有行级安全性的，若需要设置表的行级安全性，操作如下：

(1) 开启表行级安全策略，命令如下：

ALTER TABLE <tablename> ENABLE ROW LEVEL SECURITY;

其中<tablename>为表名称，需根据实际需要替换该参数。

(2) 创建行级安全策略。创建语法为：

CREATE POLICY name ON table_name

[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]

[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]

[ USING ( using_expression ) ]

[ WITH CHECK ( check_expression ) ]

其中：

¡ name：自定义安全策略名称

¡ table_name：设置适用该策略的表名称

¡ FOR：设置该策略适用的DML命令，ALL表示所有

¡ TO：设置该策略适用的角色

¡ USING：设置表的CHECK表达式

¡ WITH CHECK：设置表的INSERT或UPDATE的SQL表达式

所有表的行级操作（除表的拥有者或数据库管理员之外），都需要设置行级安全性策略之后才可操作表的行。

5. 列级访问控制

SesSQL MPP数据库支持对表中的列进行权限控制，可以使用\h grant和\h revoke查看其使用语法。例如，授予用户user1查询table2的col1权限，命令如下：

GRANT SELECT (col1) on TABLE table2 TO user1;

3.2 基础操作

3.2.1 创建数据库

1. 创建数据库

创建数据库，命令如下：

CREATE DATABASE name

[ [ WITH ] [ OWNER [=] user_name ]

[ TEMPLATE [=] template ]

[ ENCODING [=] encoding ]

[ LC_COLLATE [=] lc_collate ]

[ LC_CTYPE [=] lc_ctype ]

[ TABLESPACE [=] tablespace_name ]

[ IS_TEMPLATE [=] istemplate ]

[ ALLOW_CONNECTIONS [=] allowconn ]

[ CONNECTION LIMIT [=] connlimit ] ]

其中：

· name：创建数据库的名称。

· user_namer：数据库的所属用户，缺省为执行该创建数据库命令的用户。

· template：创建新数据库模板，默认使用template1模板。

· encoding：数据库使用的字符集编码，可以指定一个字符串常量（如'UTF-8'），或者代表字符集编码的数字，或者使用缺省值（即模板数据库使用的字符集编码）。

· lc_collate：配置数据库的排序规则，默认使用模板数据库的排序规则。

· lc_type：配置数据库的字符分类，默认使用模板数据库的字符分类。

· tablespace_name：数据库相关联的表空间名字，默认使用模板数据库的表空间。

· connlimit：数据库的最大并发连接数。缺省值为-1，表示没有限制。

2. 创建数据库示例

(1) 创建testdb数据库，命令如下：

CREATE DATABASE testdb;

(2) 创建sales数据库，数据库所有者用户为salesapp，表空间为salesspace，命令如下：

CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;

(3) 创建数据库music支持UTF-8编码格式，命令如下：

CREATE DATABASE music ENCODING 'UTF-8';

3.2.2 创建表

1. 创建表

创建表，命令如下：

CREATE TABLE [ IF NOT EXISTS ] table_name (

[column_name data_type] , [ ... ] ]

] )

[ WITH (

APPENDONLY={TRUE|FALSE}

BLOCKSIZE={8192-2097152}

ORIENTATION={COLUMN|ROW}

COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE}

COMPRESSLEVEL={0-9}

CHECKSUM={TRUE|FALSE}

OIDS[=TRUE|FALSE]

)]

[ TABLESPACE tablespace_name ]

[ DISTRIBUTED BY (column [opclass], [ ... ] ) ]

其中：

· if not exists：判断表是否存在，如果不存在则创建，如果存在则不会创建。

· column_name：列名。

· data_type：列的数据类型。

· appendonly（AO表）：若设置为true则表示创建AO表，否则表示创建非AO表，默认为false。该参数需与orientation配合使用。

· orientation：设置存储方式，默认为行存储。设置为column表示列存储；设置为row表示行存储。使用列存储方式时必须是AO表。

· compresstype：压缩算法，目前只支持zlib算法。采用压缩算法时必须是appendonly表。

· compresslevel：压缩级别，与压缩算法时关联的，数值范围在1-9，建议值为5，采用压缩表时必须是appendonly表。

· checksum：该参数仅对AO表有效。如果设置为true则表示启用CRC校验和验证。

· oids：对象标识符，表示对象的序列号。一般不使用，默认为false。

· distributed by：用于指定分布策略，默认为Hash分布。关于分布策略详情请参见3.2.3 选择分布策略。

2. 创建表示例

创建一个名称为catalog_sales的内部表。命令如下：

create table catalog_sales(

user_id varchar(14) ,

open_date date ,

update_time timestamp ,

total_cost decimal(10,2)

)with (appendonly=true ,

orientation=column ,

compresstype=zlib ,

compresslevel=5 ,

oids=false)

distributed by (user_id);

3.2.3 选择分布策略

分布策略决定了表中的数据如何分片存储到每一个Segment Instance上。SeaSQL MPP提供Hash分布和随机分布这两种分布策略。在创建表时，可以选择分布策略，如果未指定，默认会使用Hash分布。

关于分布键的选取原则，详情请参见9.7 分布键选取原则。

1. 默认分布键参数设置

用户创建表时如果没有指定分布策略，SeaSQL MPP将会默认选择前32个字段做为分布键，然后根据分布键对数据进行Hash分布。默认分布键数量可以通过gp_max_numdistrcolumns参数进行控制。

· gp_max_numdistrcolumns参数可以在会话中修改，如果在会话中修改，那么只会影响当前会话，不会影响其他会话。

a. 查看当前会话gp_max_numdistrColumns参数设置，命令如下：

show gp_max_numdistrcolumns;

图3-1 查看参数设置

b. 修改当前会话的gp_max_numdistrColumns参数，命令如下：

set gp_max_numdistrcolumns=2;

图3-2 修改会话参数

· gp_max_numdistrcolumns参数也可以通过修改Master节点的postgresql.conf文件修改全局会话。

a. 修改全局的gp_max_numdistrColumns参数。修改Master节点上的postgresql.conf文件中的gp_max_numdistrColumns参数。如果文件中没有该参数则需添加一条。

gp_max_numdistrColumns=<int>

b. 执行以下命令重新加载配置参数

gpstop -u

2. Hash分布

Hash分布可选一个或多个列作为分布键，对分布键做Hash算法来确保数据存放到对应的Segment Instance上。使用Hash分布时，尽量选择区分度比较大的字段，以保证数据均衡分布到各个Segment Instance上，默认选择表的前32个字段作为分布键。

SeaSQL MPP通过参数gp_max_numdistrcolumns可指定表的前几个字段作为分布键，默认为32。

· 创建Hash分布表，以id列作为分布键进行hash分布，命令如下：

create table t_hash(id int,name varchar(50)) distributed by (id);

图3-3 创建Hash分布表

· 更改分布键

将主键name升级为分布键代替id列作为分布键，命令如下：

alter table t_hash add primary key (name);

图3-4 更改分布键

3. 随机分布

使用随机分布时，数据会随机分散在每一个数据节点中，可以保证数据平均分布，但是在执行 SQL 的过程中，关联等操作都需要进行数据重分布，性能较差。

创建随机分布表，执行如下命令：

create table t_random(id int ,name varchar(100)) distributed randomly;

随机分布不可以创建主键或唯一键。

3.2.4 分区表

· 一个表的分区表过多，会带来数据库维护成本的大幅上升，建议分区表数量不要超过4000个。

· 在创建分区表时，需要指定分区表的名称，否则在后续的分区DDL操作中将无法操作。

· 需要指定一个默认子分区default_p，防止插入的数据找不到分区报错。

· 默认子分区中的数据比较大时，会严重影响查询效率。

· 如果默认子分区中的数据是垃圾数据，可以使用truncate命令进行删除；如果非垃圾数据，需要先创建一个临时表，将默认子分区中的数据导入到临时表中，然后使用truncate命令删除default分区数据，临时表中的数据可以根据现场业务情况进行二次处理。

使用分区表可以将大批量的数据分而治之，提高查询效率。分区表可分为范围分区和List分区，关于分区表的选择原则详情可参见9.4 分区选择原则。

1. 范围分区表

当一个大表中，某些字段可以按照范围进行合理分割时，可以使用范围分区表。

· 范围分区

例如，按照月份进行范围分区。执行如下命令：

create table test_p

(

id1 integer,

id2 integer,

id3 date

) with (appendonly=true ,orientation=column)

distributed by(id1)

partition by range(id3)

(

partition p_1 start('20180101'::date) inclusive end('20180201'::date) exclusive,

partition p_2 start('20180201'::date) inclusive end('20180301'::date) exclusive,

partition p_3 start('20180301'::date) inclusive end('20180401'::date) exclusive,

partition p_4 start('20180401'::date) inclusive end('20180501'::date) exclusive,

partition p_5 start('20180501'::date) inclusive end('20180601'::date) exclusive,

partition p_6 start('20180601'::date) inclusive end('20180701'::date) exclusive,

partition p_7 start('20180701'::date) inclusive end('20180801'::date) exclusive,

default partition default_p

);

· 快速范围分区

可以指定分区条件进行快速范围分区。例如按照月份将一年的数据进行快速分区，执行如下命令：

create table test_p

(

id1 integer,

id2 integer,

id3 date

) with(appendonly=true, orientation=column) distributed by(id1)

partition by range(id3)

(

partition p start('2010-01-01'::date) end ('2010-12-31'::date) every('1 month'::interval),

default partition default_p

);

· 执行select partitionrangestart,partitionname, partitiontablename from pg_partitions where tablename = 'test_p';命令可以查看分区信息，其中test_p为用户创建的分区表名。

· 执行alter table test_p add partition p_11 start('20181101'::date) inclusive end('20181201'::date) exclusive;命令可以添加一个分区，但如果存在默认分区时，不能使用add，只能使用分割（split）分区。

· 分割（split）分区：执行alter table test_p split default partition start('20180801'::date) inclusive end('20180901'::date) into (partition p_8, partition default_p);命令会从默认子分区分割；执行alter table test_p split partition p_6 at('20180602'::date) into (partition p_6_20180601, partition p_6_20180602);命令会从其他子分区分割。

· 执行alter table test_p exchange partition p_8 with table test_p_tmp;命令进行分区交换，该方式要求test_p_tmp和test_p的表结构一致。

· 执行alter table test_p drop partition if exists p_1 cascade; 命令可以删除指定分区。

2. List分区表

当一个大表中，某些字段可以按照字段值进行合理分组时，可以使用List分区。例如将相同数据归类到一组，按照城市进行分组，执行如下命令：

create table public.test_p_list

(

member_id numeric,

name varchar(32)

)with (appendonly=true,orientation=column)

distributed by (member_id)

partition by list(name)

(

partition guangzhou values('guangzhou'),

partition hangzgou values('hangzgou'),

partition shanghai values('shanghai'),

partition beijing values('beijing'),

default partition default_p

);

3. 分区表的自动化管理

在数据库中，过多的子分区表会增加数据库自身的维护成本。针对大表分区情况下，为了加快查询数据，会在子分区上创建索引。但是当入库数据量比较大时，带索引入库非常影响数据库性能，需要对分区表进行自动化管理。

例如，将一个大表按月进行范围分区，当每月的数据量较大时，为了加快查询，可以在每月子分区上创建索引，但是外部又在不停的进行大数据量的入库操作（例如每天超过100W条记录），会造成系统I/O很高的情况。

为了解决上述问题，可以将当月的子分区再按照天进行子分区分割，只在当前的子分区上不进行创建索引，从而解决带索引入库的情况。而子分区的分割会导致分区表的大量增加，可以在本月月初将上一个月的分区合并，同时将本月的月分区表进行按天自动化分区，可以减少运维工作量。

合并分区示例详情请参见8.3 合并分区示例。

4. 装载分区表

· 一旦创建了分区表，顶级表总是空的。数据会存储在最底层的表中。在多级分区表中，仅仅在层级最低的子分区中有数据。

· 如果某行记录无法匹配到子分区，该数据将会被拒绝并致使装载失败。若选择定义默认分区，所有不能匹配分区CHECK约束的数据将会装载到默认分区。

· 在运行期间，查询规划器会扫描整个表的层级结构并使用CHECK约束适配查询条件来决定哪些子表需要被扫描。默认分区总是会被扫描，如果默认分区中包含数据，会拖慢整体的扫表时间。

· 使用COPY或者INSERT向父级表装载数据时，数据会默认自动路由到正确的分区。既可以像普通的未分区表一样装载分区表，也可以直接把数据装载到子表中，通过先创建一个中间表装载数据，然后与分区表进行分区交换，这种分区交换的性能会高于直接的COPY和INSERT的性能。

3.2.5 索引

在表的指定列上创建索引可以提高数据库的查询效率。SeaSQL MPP数据库支持bitmap、btree、gin、gist、spgist索引类型。

1. 创建索引

(1) 创建索引，命令如下：

CREATE [ UNIQUE ] INDEX [ name ] ON table_name [ USING method ]

( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

[ WITH ( storage_parameter = value [, ... ] ) ]

[ TABLESPACE tablespace_name ]

[ WHERE predicate ]

(2) 创建索引示例

¡ 创建Btree索引。在表films的title列创建Btree索引，执行如下命令：

CREATE INDEX title_idx ON films USING btree (title);

¡ 创建bitmap 索引。

- 在表employee的gender列创建bitmap索引，执行如下命令：

CREATE INDEX gender_bmp_idx ON employee USING bitmap (gender);

- 创建表达式索引，执行如下命令：

CREATE INDEX lower_title_idx ON films ((lower(title)));

¡ 创建指定填充因子的索引，执行如下命令：

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);

创建索引后，请进行表分析，以便于优化器做信息统计，在选择执行计划的时候会考虑走索引而不是全表扫描。

2. 修改索引

(1) 修改索引，命令如下：

ALTER INDEX [ IF EXISTS ] name RENAME TO new_name

ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name

ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] )

ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )

(2) 修改索引示例

重命名一个已存在的索引，命令如下：

ALTER INDEX distributors RENAME TO suppliers;

3. 删除索引

(1) 删除索引，命令如下：

DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

(2) 删除索引示例，执行如下命令：

DROP INDEX IF EXISTS index_name;

4. 重建索引

使用存储在索引表中的数据可以重建索引，替换旧的索引的副本。

· 如果索引失效，使用REINDEX可以作为一个恢复方法

· 如果索引性能变差，可以尝试使用REINDEX来修复索引

重建索引，命令如下：

REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]

重建索引示例：

· 重建一个单独的索引，执行如下命令：

REINDEX INDEX my_index;

· 重建表mytable上的所有索引，执行如下命令：

REINDEX TABLE my_table;

5. 多字段索引

可以在一个表的多个字段上创建索引。

(1) 创建名为test2的表，表信息如下：

CREATE TABLE test2 (

major int,

minor int,

name varchar);

(2) 如果需要经常查询表test2中minor = constant和major = constant的信息，可以在字段major和minor 上联合定义一个索引，执行如下命令：

CREATE INDEX test2_mm_idx ON test2 (major, minor);

· 一个多字段的 Btree 索引可以用在包含索引字段子集的查询条件里，不过如果在前导字段(major)上有约束条件，可将限制所扫描的索引范围。

· 谨慎使用多字段索引。通常在单字段上的索引就足够了，并且节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引不建议使用。

6. 查看索引类型

· 使用\d+ table_name命令可以查看表的索引类型

图3-5 查看表的索引类型

· 使用\di命令可以查看当前数据库下所有的索引

图3-6 查看数据库索引

3.3 集群管理工具

3.3.1 gpstart（启动集群）

1. 用法

gpstart [-d master_data_directory] [-B parallel_processes] [-R]

[-m] [-y] [-a] [-t timeout_seconds] [-l logfile_directory]

[-v | -q]

gpstart -?

gpstart -h

gpstart --help

gpstart -version

其中：

· -d master_data_directory：可选。Master主机的数据目录。如果未指定，则使用$MASTER_DATA_DIRECTORY设置的值。

· -B parallel_processes：并行启动的Segment数。如果未指定，则将启动最多64个并行进程，具体进程数取决于需要启动的Segment实例。

· -R：以受限模式启动SeaSQL MPP数据库（只允许数据库超级用户连接）。

· -m：可选。仅启动Master实例，这可能对维护任务有用。该模式只允许连接到utility模式下的Master。例如：

· PGOPTIONS='-c gp_role=utility' psql

· -y：可选。不启动Standby Master主机。默认是启动Standby Master主机和同步进程。

· -a：不要提示用户确认。

· -t timeout_seconds ：指定等待Segment实例启动的超时时间（秒）。默认为60秒

· -l logfile_directory：写入日志文件的目录。默认为~/ssadminLogs。

· -q：以静默模式运行。命令输出不显示在屏幕上，但仍然写入日志文件。

· -v：显示输出的详细状态，进度和错误消息。

· --version：显示版本信息。

2. gpstart使用示例

· 启动SeaSQL MPP数据库系统，执行如下命令：

gpstart -v -a

· 以受限模式启动SeaSQL MPP数据库系统（仅允许超级用户连接），执行如下命令：

gpstart -R

· 仅启动SeaSQL MPP主实例并以utility模式连接，执行如下命令：

gpstart -m

PGOPTIONS='-c gp_role=utility' psql

3.3.2 gpstop（停止集群）

1. 用法

gpstop [-d master_data_directory] [-B parallel_processes]

[-M smart | fast | immediate] [-t timeout_seconds] [-r] [-y] [-a]

[-l logfile_directory] [-v | -q]

gpstop -m [-d Master_data_directory] [-y] [-l logfile_directory] [-v | -q]

gpstop -u [-d Master_data_directory] [-l logfile_directory] [-v | -q]

gpstop --version

gpstop -?

gpstop -h

gpstop --help

其中：

· -d master_data_directory ：可选。Master主机的数据目录。若未指定，则使用环境变量$MASTER_DATA_DIRECTORY设置的值。

· -B parallel_processes ：并行停止的Segment数。如果未指定，则启动最多64个并行进程，具体进程数量取决于需要停止的Segment实例数。

· -M smart：智能关闭。如果存在活动连接，则此命令将失败并显示警告。这是默认的关闭模式。

· -M fast：快速关闭。任何正在进行的事务都会中断并回滚。

· -M immediate：立即关闭。任何正在进行的事务都会中止。

· -t timeout_seconds ：指定等待Segment实例关闭的超时时间，默认600秒。

· -r：停止完成后重新启动。

· -y：不停止Standby Master进程。默认是停止Standby Master。

· -a：不要提示用户确认。

· -l logfile_directory ：写入日志文件的目录。默认为~/ssadminLogs。

· -v：显示输出的详细状态，进度和错误消息。

· -q：以静默模式运行。命令输出不显示在屏幕上，但仍然写入日志文件。

· -m：可选。关闭在维护模式下启动的SeaSQL MPP主实例。

· -u：此选项将重新加载Master和Segment的pg_hba.conf文件以及postgresql.conf文件的运行相关的配置参数，但不会停止数据库。

· --version：显示版本信息。

2. gpstop使用示例

· 在智能模式下关闭SeaSQL MPP数据库系统，执行如下命令：

gpstop

· 在快速模式下关闭SeaSQL MPP数据库系统，执行如下命令：

gpstop -M fast

· 停止所有的Segment实例，然后重新启动系统，执行如下命令：

gpstop -r

· 停止在维护模式下启动的Master实例，执行如下命令：

gpstop -m

· 进行配置更改后重新加载postgresql.conf和pg_hba.conf文件，执行如下命令：

gpstop -u

3.3.3 gpstate（查看集群状态）

1. 用法

gpstate [-d master_data_directory] [-B parallel_processes]

[-s | -b | -Q | -e] [-m | -c] [-p] [-i] [-f] [-v | -q]

[-l log_directory]

gpstate -?

gpstate -h

gpstate --help

其中：

· -d master_data_directory ：可选。Master的数据目录。如果未指定，则使用为$MASTER_DATA_DIRECTORY设置的值。

· -B parallel_processes ：并行检查的Segment数。如果未指定，将会根据需要检查的Segment实例数进行，最多启动60个并行进程。

· -s：可选。显示SeaSQL MPP数据库系统的详细状态信息。

· -b（简要状态）：可选。显示SeaSQL MPP数据库系统状态的简要概述。这是默认选项。

· -Q：可选。在Master主机上的系统表中检查Segment的状态。不直接向Segment查询状态。

· -e：显示有问题的primary/mirror实例的详细信息

· -m：可选。列出系统中mirror实例及其当前角色和状态。

· -c：可选。显示primary实例到mirror实例的映射和状态。

· -p：列出整个SeaSQL MPP数据库系统使用的端口号。

· -i：显示每个实例的的版本信息。

· -f：显示Standby Master主机的详细信息。

· -v：可选。显示错误消息并输出详细的状态和进度信息。

· -q：可选。以静默模式运行。

· -l logfile_directory ：写入日志文件的目录。默认为 ~/ssadminLogs。

2. gpstate使用示例

· 显示SeaSQL MPP数据库系统的详细信息

gpstate -s

· 在Master主机系统目录中快速检查下游Segment

gpstate -Q

· 显示关于镜像Segment的实例的信息

gpstate -m

· 显示关于备Master配置的信息

gpstate -f

· 显示SeaSQL MPP软件版本信息

gpstate -i

3.3.4 ssgather（一键收集集群日志）

ssgather命令仅可在Master节点或Standby Master节点上执行成功。

1. 用法

ssgather [-m message] [-o output_dir] [--tmp-dir=TMP_DIR] [-t type] [--begin=TIME] [--end=TIME] [--log-limit=NUM_GB] [--debug]

其中：

· -h：查看帮助信息。

· --version：查看版本信息。

· -n：待收集日志的节点列表，填写节点的Hostname或者IP地址，默认收集数据库集群所有节点的日志。

· -o：输出日志的目录位置，所填写目录必须存在，默认输出到当前所在目录。

· -m：本次收集的原因，信息会保存在结果中的reason.txt文件中。

· --tmp-dir：临时目录位置，该临时目录是在信息收集过程中文件存放位置，所有节点均会使用，默认为/tmp，所填写目录必须存在。

· -t：收集的信息类型，类型有all、gpseg、context、query，默认为all。

¡ all：收集所有信息。

¡ gpseg：仅收集各个实例的pg_log日志，pg_hba.conf，postgresql.conf文件。

¡ context：仅收集节点的操作系统信息。

¡ query：仅收集数据库的系统表信息。

· --begin：指定收集pg_log日志的起始时间，时间格式：

¡ Y-m-d H:M:S，如2020-09-01 12:00:00。

¡ [DAYS]d[HOURS]h[MINUTES]m，如2d2h1m，必须与--end配合使用。

· --end：指定收集pg_log日志的结束时间，时间格式：

¡ Y-m-d H:M:S,如2020-09-01 12:00:00。

¡ [DAYS]d[HOURS]h[MINUTES]m，如2d2h1m，必须与--begin一起使用。

· --log-limit：收集的每个日志文件的大小限制，限制可收集的最大值，默认为1GB，单位有B，KB，MB，GB。

· --debug：打开debug日志。

2. ssgather使用示例

(1) 在master或者standby master节点以ssadmin用户登录

su - ssadmin

(2) 执行ssgather收集命令

¡ 默认收集所有节点所有类型的信息

ssgather

¡ 指定节点收集，例如指定收集node1节点上的信息

ssgather -n node1

¡ 指定收集类型,例如指定收系统表类型的信息

ssgather -t query

¡ 指定时间段,例如

ssgather --begin=’2020-10-01 12:00:00’ --end=’2020-10-10 12:00:00’

¡ 指定输出目录

ssgather -o /opt/data

¡ 指定pg_log日志文件大小，例如指定为100MB

ssgather --log-limit=100MB

(3) 在输出目录下生成收集后的tar包，例如：

Ssgather.20201010055353.tar

3. 结果分析

解压生成的tar包之后，结果如图3-7所示。

图3-7 收集日志结果

· 节点目录：每个节点对应一个目录，该节点收集到的所有信息均保存在该目录下。

每个节点目录下存放是收集到的信息的以.tgz结尾的压缩包，压缩包名称为：节点名+日志类型+压缩包后缀。日志类型主要有三种：

¡ context：操作系统信息。

¡ query：数据库系统表的信息。

¡ gpseg*：收集到的实例的日志信息。

可通过解压对应的压缩包，查看对应的信息。

· ssgather_collection.log：ssgather执行过程的日志。

· tmp：临时目录，ssgather执行过程产生的临时问津。

· reason.txt：收集的原因，对应ssgather的-m参数。

4 ETL工具

4.1 gpfdist

gpfdist是一个使用HTTP协议的文件服务程序。它可以以并行方式向SeaSQL MPP数据库的Segment中导入外部数据。多个gpfdist进程可以同时运行，以实现高速的数据加载。

1. 用法

gpfdist [-d directory] [-p http_port] [-l log_file] [-t timeout]

[-S] [-w time] [-v | -V] [-s] [-m max_length]

[--ssl certificate_path [--sslclean wait_time] ]

gpfdist -?

gpfdist --help

gpfdist –version

其中：

· -d <directory>：gpfdist将为其提供可读外部表的文件或为可写外部表创建输出文件的目录。如果未指定，则默认为当前目录。

· -p <http_port>：提供文件的HTTP端口。默认为8080。

· -P <last_http_port>：指定HTTP端口范围内的最后一个端口号，即端口号范围为[http_port:last_http_port]。gpfdist会在成功绑定的范围内的第一个端口号上提供要导入的数据文件。

· -l <log_file>：日志文件名。

· -t <timeout>：超时时间，缺省值为5秒，允许值为2到600秒。

· -S：在需要I/O同步而打开文件时使用O_SYNC标识。在数据被物理写入到底层硬件之前，任何对结果文件描述符的写入都会阻塞gpfdist。

· -w <time>：关闭一个目标文件（例如命名管道）之前的延时。缺省值为0（不延时），最大值是600秒。

· -v：详细显示进度和状态信息。

· -V：详细展示所有由该程序产生的输出信息。

· -s：启用简化的日志记录。

· -m <max_length>：允许的最大数据行。缺省值为32768，有效范围是32K到256MB。

· --ssl <certificate_path>：为gpfdist传输的数据添加SSL加密服务。

· --sslclean wait_time：使用--ssl选项运行该工具时，关闭SSL会话和清除SSL资源之前要等待的时间。默认值是0，最大值是500秒。

· -?（帮助）：显示在线帮助。

· --version：显示该工具的版本。

2. gpfdist使用示例

(1) 后台启动gpfdist工具并指定文件目录及端口。执行如下命令：

nohup gpfdist -d /home/ssadmin/ -p 8081 -l /home/ssadmin/gpfdist.log &

其中：

¡ -d：数据存放目录

¡ -p：端口号，设置为8081

¡ -l：日志输出目录

¡ &：后台执行

(2) 准备数据文件。在/home/ssadmin目录下创建数据文件xa.txt。

(3) 在数据库中创建外部表（本示例为example），导入数据文件xa.txt。

create external table example (col1 int, col2 int) location('gpfdist://101.8.189.100:8081/xa.txt') format 'text' (delimiter ',' null as '' escape 'off');

(4) 外部表创建成功后，即可像正常表一样查询example表中数据。

说明：若想要将example表中数据导入到另一个数据库表（例如test表）中的话，需要在SeaSQL MPP数据库中创建test表，且要求test表和example表的字段及字段类型一致，然后执行insert into test as select * from example;命令即可。

(5) 停止后台的gpfdist服务，操作如下：

首先执行ps -ef|grep gpfdist命令找到gpfdist的进程号，然后执行kill gpfdist<进程号>杀掉该进程。

【注意】：

¡ 可以在同一集群内的不同主机之间进行文件加载，但要求集群内提供文件的其他主机也需开启gpfdist服务，并在创建外部表时的location中添加对应的URL。

¡ 在同一个主机启动多个gpfdist服务，需要为每个服务指定不同的目录和端口。例如：

gpfdist -d /var/load_files1 -p 8081 -l /home/ssadmin/log1 &

gpfdist -d /var/load_files2 -p 8082 -l /home/ssadmin/log2 &

4.2 gpload

gpload是SeaSQL MPP使用可读外部表和并行文件服务gpfdist的一个命令包，允许通过使用配置文件的方式设置数据格式来创建外部表。

gpload的执行通过使用一个定义在YAML格式控制的文件指定加载，加载工作在独立的事务中完成。需要按照YAML格式定义的装载说明配置文件，然后执行insert、update、merge操作将源数据装载到目标数据库表中。

1. 用法

gpload -f control_file [-l log_file] [-h hostname] [-p port]

[-U username] [-d database] [-W] [--gpfdist_timeout seconds]

[--no_auto_trans] [[-v | -V] [-q]] [-D]

gpload -?

gpload -v ersion

其中：

· -f <control_file>：指定YAML文件名，使用gpload进行数据加载时必须指定该文件。

· -l <log_file>：指定日志文件的位置，默认位于~/ssadminLogs/gpload_YYYYMMDD。

· -h <hostname>：指定SeaSQL MPP Master数据库服务器运行的主机名，如果没有指定，将会从加载控制文件中获取。

· -p <port>：指定SeaSQL MPP Master数据库服务器连接监听的端口号，如果没有指定，将会从加载控制文件中读取。

· -U <username>：要连接的数据库角色名，如果没有指定，将会从加载控制文件中读取。

· -d <database>：文件要加载的数据库，如果没有指定，将会从加载控制文件中读取。

· -W(force password prompt)：强制密码提示。

· -- gpfdist_timeout <seconds>：超时时间。

· --no_auto_trans：将使用该参数时，将禁止将加载操作作为单个事务处理。默认情况下，gpload在目标表上同时执行多个操作时，将每个加载操作作为一个事务处理，以防止数据不一致。

· -v（verbose mode）：按执行过程显示数据加载的细节。

· -V（very verbose mode）：显示十分详细的输出。

· -q（no screen output）：命令输出不会显示在屏幕上。

· -D（debug mode）：debug模式。

· -?：显示帮助，然后退出。

· --version：显示这个工具的版本，然后退出。

2. 控制文件格式

SeaSQL MPP支持多分隔符，即可以使用多个普通字符、ASCII字符或多个Unicode字符自由组合为分隔符。

gpload控制文件使用YAML文档格式，结构是：

VERSION: 1.0.0.1

DATABASE: db_name

USER: db_username

HOST: Master_hostname

PORT: Master_port

GPLOAD:

INPUT:

- SOURCE:

LOCAL_HOSTNAME:

- hostname_or_ip

PORT: http_port

| PORT_RANGE: [start_port_range, end_port_range]

FILE:

- /path/to/input_file

SSL: true | false

CERTIFICATES_PATH: /path/to/certificates

- COLUMNS:

- field_name: data_type

- TRANSFORM: 'transformation'

- TRANSFORM_CONFIG: 'configuration-file-path'

- MAX_LINE_LENGTH: integer

- FORMAT: text | csv

- DELIMITER: 'delimiter_character'

- ESCAPE: 'escape_character' | 'OFF'

- NULL_AS: 'null_string'

- QUOTE: 'csv_quote_character'

- HEADER: true | false

- ENCODING: database_encoding

- ERROR_LIMIT: integer

- LOG_ERRORS: true | false

EXTERNAL:

- SCHEMA: schema | '%'

OUTPUT:

- TABLE: schema.table_name

- MODE: insert | update | merge

PRELOAD:

- TRUNCATE: true | false

- REUSE_TABLES: true | false

SQL:

- BEFORE: "sql_command"

- AFTER: "sql_command"

其中：

· VERSION：自定义版本号（可选项）。

· DATABASE：指定需要连接的数据库，如果没有指定，根据$PGDATABASE变量确定。

· USER：确定指定用于连接的数据库角色。如果没有指定，默认为当前用户或者$PGUSER。

· HOST：可选。指定Master节点的主机名/IP。如果没指定，根据变量$PGHOST确定。

· PORT：可选。指定Master的端口，默认是5432或者$GPORT。

· GPLOAD：必选。开始装载的说明。一个GPLOAD部分必须包含一个INPUT和一个OUTPUT。

¡ INPUT：必选。定义加载数据的格式和位置。gpload在当前主机上启动一个或者多个gpfdist文件分布式实例。gpload命令所在主机可网络访问SeaSQL MPP集群中的每个节点。

¡ SOURCE：必选。INPUT说明的SOURCE块定义源文件的位置。一个INPUT可以定义多个SOURCE块。每个定义的SOURCE块对应于将在本地机器上启动的一个gpfdist文件分布程序的实例。每个SOURCE块定义必须指定一个source文件。

¡ LOCAL_HOSTNAME：可选项。指定gpload工具运行所在的主机名或者IP地址。如果这台主机有多个网卡，能同时使用每个网卡（每个网卡都有一个IP地址），通过设定LOCAL_HOSTNAME和PORT实现多个gpfdist实例，可提升数据加载速度。默认情况，只使用主机名或者IP地址。

¡ PORT：可选项。gpfdist实例需要的端口。

¡ PORT_RANGE：可选。用于代替PORT提供一个端口号范围，gpload可以从其中为这个gpfdist文件分布程序实例选择一个可用的端口。

¡ FILE：必选。指定文件位置，可同时指定多个相同格式的文件。如果使用gzip或bzip2压缩文件（具有.gz或.bz2文件扩展名），则文件将自动解压缩。当指定要加载的源文件时，可以使用通配符（*）或其他C样式模式匹配来表示多个文件。

¡ SSL：可选。指定使用SSL加密。如果SSL设置为true，gpload将使用gpfdists协议启动具有ssl选项的gpfdist服务器。

¡ CERTIFICATES_PATH：当SSL为true时需要配置该参数。CERTIFICATES_PATH中指定的位置必须包含服务器证书文件server.crt、服务器私钥文件server.key以及信任的证书机构root.crt，但根目录（/）不能被指定为CERTIFICATES_PATH。

· CLOUMNS：可选。以<field_name>：<data_type>的格式指定源数据文件的模式。源文件中的DELIMITER指明source文件中两个数据之间的分隔符。如果未指定COLUMNS选项，则表示source文件中的列的顺序、列的数量、数据类型都和目标表一致。

· TRANSFORM：可选。指定传递给gpload的输入XML转换的名称。其中：

¡ TRANSFORM_CONFIG：当TRANSFORM内容被指定时，必须指定TRANSFORM参数中指定的XML转换配置文件的位置。

¡ MAX_LINE_LENGTH：可选。一个整数，指定传递给gpload的XML转换数据中行的最大长度。

¡ FORMAT：可选。指定源数据文件的格式，纯文本（TEXT）或逗号分隔值（CSV）格式。如果未指定，则默认为TEXT。

¡ DELIMITER：可选。指定用于分隔每行数据中的单个ASCII字符。TEXT模式下默认值为制表符，CSV格式默认值为为逗号，也可以指定非打印的ASCII字符或非打印的unicode字符。转义字符串语法E'<character-code>'也支持非打印字符。ASCII或unicode字符必须用单引号括起来。

¡ ESCAPE：指定用于C转义序列的单个字符（例如\n等），以及用于转义的可能被视为行或列分隔符的数据字符。确保选择一个在实际列数据中没有被使用的转义字符。默认转义字符是文本格式文件的“\”和csv格式文件的“双引号”，但是可以指定另一个字符来表示转义。在格式化的文件中，也可以通过指定值“OFF”作为转义值禁用文本中的转义。

【说明】：该关键字对于文本格式的Web日志数据非常有用，因该类数据具有许多嵌入的“\”，但这些“\”并非旨在转义。

¡ NULL_AS：可选。用于指定表示空值的字符串。与此字符串匹配的源数据项将被视为空值。在TEXT模式下，默认值为“\n”；在CSV模式中为没有引号的空值。

¡ QUOTE：当FORMAT选项为CSV时需配置该参数，用于指定CSV模式的引用字符，默认值为双引号。

¡ HEADER：可选。指定数据文件中的第一行作为标题行（包含列的名称），不包含要加载的数据。如果使用多个数据源文件，则所有文件必须具有标题行。默认是假设输入文件没有标题行。

¡ ENCODING：可选。源数据的字符集编码。指定字符串常量（如“SQL_ASCII”），整数编码号或使用默认的客户端编码“DEFAULT”。如果未指定，则使用默认客户端编码。

· ERROR_LIMIT：可选。允许的错误行数。加载数据时，错误数据将被忽略。如果没有到达错误限制数量，所有正常行会加载到SeaSQL MPP中，问题行会存放到err_table中。如果超过错误值，正常数据也不会加载。

· LOG_ERRORS：当声明ERROR_LIMIT时可选。如果为true（默认为false），gpload将创建一个内部错误表，其中在单行错误隔离模式下运行时将记录具有格式错误的行。然后，可以使用SeaSQL MPP内置函数gp_read_error_log()来查看此错误表以查看未加载的错误行。

· EXTERNAL：可选项。定义外部表。

¡ SCHEMA：配置外部表的schema的名称。如果schema不存在，则返回错误。如果指定了%，则使用OUTPUT部分中由TABLE指定的表名称的schema。如果表名未指定schema，则使用默认schema。

· OUTPUT：必选。定义最终source文件加载到的目标表。

¡ TABLE：必选。目标表。

¡ MODE：可选。有insert、update以及merge三种模式。其中，insert表示插入数据，若不指定，默认为insert模式。

· RELOAD：可选。设置导入数据之前的操作。

¡ TRUNCATE：可选。若设定为true，gpload会在导入数据前清空目标表的所有行。默认为false。

¡ REUSE_TABLES：若设置为true，gpload将不会删除它创建的外部表对象和临时表对象。若再次对相同结构的表进行加载操作时，会复用这些外部表或临时表。如果LOG_ERRORS被指定为true，则该参数也须要指定为true，以获取数据库在数据加载时的错误信息。

· SQL：可选。定义开始运行gpload和gpload结束执行的SQL语句。

¡ BEFORE，开始运行gpload时执行SQL，SQL需用单引号括起来。

¡ AFTER，gpload结束后执行SQL，SQL需用单引号括起来。

3. gpload使用示例

(1) 创建yml文件my_load.yml，配置参数修改如下：

VERSION: 1.0.0.1

DATABASE: postgres

USER: ssadmin

HOST: 101.8.189.50

PORT: 5434

GPLOAD:

INPUT:

- SOURCE:

LOCAL_HOSTNAME:

- 101.8.189.50

FILE:

- /home/ssadmin/xa.txt

- COLUMNS:

- col1: int

- col2: int

- DELIMITER: ','

- FORMAT: text

OUTPUT:

- TABLE: test

(2) 在数据库中创建目标表test，执行如下命令：

create table test(col1 int, col2 int);

(3) 将xa.txt文件的数据导入到目标表test中，执行如下命令：

gpload -f my_load.yml

说明：

¡ 在冒号和-后必须要加空格，否则会报错。

¡ 在启动gpload时不必手动启动gpfdist，因为gpload运行时会自动执行gpfdist命令，执行完之后会自动关闭。

¡ 单个gpload加载多文件，只需相应的更改YAML文件的FILE部分就可以了，或者直接把各个文件写出来。

4.3 kafka connector

SeaSQL MPP集成了kafka Connector工具（gp_kafka_connector），可以直接使用该工具将流式数据从Apache Kafka高速并行加载至SeaSQL MPP数据库中。

部署SeaSQL MPP集群时，集群中的任一服务器节点都会默认安装kafka Connector工具。

1. 用法

gp_kafka_connector -B kafka_brokerlist -T kafka_topic -P kafka_partitionlist -f control_file [-r batch_rows | -t batch_time ] [-q]

gp_kafka_connector -h

gp_kafka_connector --help

其中：

· -B < kafka_brokerlist >：指定部分或全部kafka broker节点，以逗号分隔。格式为：”broker1, broker2,..”。 broker节点信息可以是URL或host，格式为：”[proto://]host[:port]“。Proto表示指定连接kafka的协议（可为PLAINTEXT、SSL、SASL或SASL_PLAINTEXT）；port表示指定连接kafka的端口号。

· -T <kafka_topic>：指定要消费的kafka topic。

· -P <kafka_partitionlist>：指定要消费的kafka topic中对应的partitions。多个partition之间用逗号分隔。

· -f <control_file>：指定YAML文件。关于YAML文件，详情请参见控制文件格式。

· -r <batch_rows> / -t <batch_time>：gp_kafka_connector使用微批量的方式实现流式数据传输。微批量分为按数据量提交与按时间间隔提交，可根据需要选择。其中，batch_rows表示每个微批量加载到数据库的数据量；batch_time表示每个微批量的时间间隔（单位为毫秒）。

· -q：kafka topic存量数据消费完成后程序退出。

· -h/--help：输出帮助信息。

2. Kafka Connector使用示例

将Kafka中test topic的数据通过Kafka Connector导入至SeaSQL MPP集群中testdb数据库的test表中。即目标数据库为testdb，目标表为test。操作如下：

(1) 登录SeaSQL MPP集群的任一节点，并创建控制文件（yml文件），关于控制文件各项参数的说明，详情请参见控制文件格式。

本示例文件为test.yml，配置示例如下：

VERSION: 1.0.0.1

DATABASE: testdb //用于指定目标数据库

USER: ssadmin

HOST: 101.8.144.41 //数据库的IP/HOSTNAME

PORT: 5434

GPLOAD:

INPUT:

- SOURCE:

LOCAL_HOSTNAME:

- 101.8.172.10 //本机IP/HOSTNAME

PORT: 8087 //将该参数修改为gpfdist要使用的端口号，须确认该端口号未被占用，默认8087

FILE:

- /tmp/kafka_dat/<topic>/loading/* //需要把<topic>修改为test topic

- FORMAT: text

- DELIMITER: '|' //源数据分隔符

- HEADER: false

- ERROR_LIMIT: 200

- LOG_ERRORS: true //该参数不能修改

- NULL_AS: ''

- ESCAPE: 'OFF'

OUTPUT:

- TABLE: public.test //目标表名

- MODE: INSERT

PRELOAD:

- REUSE_TABLES: true //该参数不能修改

(2) 如果gp_kafka_connector所在服务器节点不在kafka集群中，需要把kafka集群的所有Broker节点的ip/hostname加入到gp_kafka_connector所在服务器的/etc/hosts列表中。

(3) 在testdb数据库public schema中创建目标表test，本示例命令如下：

create table test(col1 int, col2 int);

(4) 在Kafka中创建test topic，并向test topic生产数据，本示例命令如下：

bin/kafka-topics.sh --create –zookeeper node1:2181,node2:2181 --topic test --partitions 2 --replication-factor 1

bin/kafka-console-producer.sh --broker-list node1:6667,node2:6667 --topic test < test.dat

(5) 把kafka数据加载到SeaSQL MPP数据库中。本示例命令如下：

gp_kafka_connector -B node1:6667,node2:6667 -T test -P 0,1 -r 1000 -f test.yml

4.4 集群间数据迁移

gptransfer工具将数据库对象从源SeaSQL MPP数据库系统复制到SeaSQL MPP数据库。用户可以执行以下类型的操作：

· --full选项：拷贝SeaSQL MPP数据库系统。

这一选项将源系统中所有用户创建的数据库复制到不同的目标系统。如果指定 --full选项，则必须同时指定源系统和目标系统。目标系统不能包含任何用户定义的数据库，仅可包含默认数据库postgres、template0和template1。

· 将一组用户定义的数据库表复制到目标系统。 -f和-t选项将一组用户定义的数据库表复制到目标系统。-d选项复制所有用户定义的表、表数据，并从指定的数据库重新创建表索引。

如果目标系统与源系统相同，则必须使用--dest-database选项指定目标数据库。指定目标数据库时，源数据库表将被复制到指定的目标数据库中。

· 对于分区表，可以使用-f选项指定--partition-transfer-non-partition-target或--partition-transfer选项，从源数据库复制分区表的特定叶子分区。叶子分区是分区数据库的最低级分区。

¡ 对于--partition-transfer选项，目标表是叶子分区。

¡ 对于--partition-transfer-non-partition-target选项，目标表是非分区表。

如果指定了一组无效的gptransfer选项或者指定的源表或数据库不存在，则gptransfer将返回一个错误并退出。不会有数据被复制。

使用gptransfer工具在SeaSQL MPP数据库系统之间复制数据库对象：

· 使用源数据库系统上的SeaSQL MPP数据库工具gpfdist，gpfdists协议不受支持。

· 使用源数据库系统上的可写外部表和目标数据库系统上可读外部表。

· 使用在可写外部表和可读外部表之间传输数据的命名管道。

1. 用法

gptransfer

{ --full |

{ [-d database1 [ -d database2 ... ]] |

[-t db.schema.table [ -t db.schema1.table1 ... ]] |

[-f table-file [--partition-transfer

| --partition-transfer-non-partition-target ]]

[-T db.schema.table [ -T db.schema1.table1 ... ]]

[-F table-file] } }

[--skip-existing | --truncate | --drop]

[--analyze] [--validate=type] [-x] [--dry-run]

[--schema-only ]

[--source-host=source_host [--source-port=source_port]

[--source-user=source_user]]

[--base-port=base_gpfdist_port]

[--dest-host=dest_host --source-map-file=host_map_file

[--dest-port=port] [--dest-user=dest_user] ]

[--dest-database=dest_database_name]

[--batch-size=batch_size] [--sub-batch-size=sub_batch_size]

[--timeout=seconds]

[--max-line-length=length]

[--work-base-dir=work_dir] [-l log_dir]

[--format=[CSV|TEXT] ]

[--quote=character ]

[--no-final-count ]

[-v | --verbose]

[-q | --quiet]

[--gpfdist-verbose]

[--gpfdist-very-verbose]

[-a]

gptransfer --version

gptransfer -h | -? | --help

其中：

更详细的参数，可参考gptransfer -h

· --analyze：在非系统表上运行ANALYZE命令。默认不运行ANALYZE命令。

· --batch-size=batch_size：设置gptransfer并发复制到目标数据库的表的最大数量。如果未指定，默认为2，最大值为10。

· -d database：指定待复制的源数据库。可多次指定该选项来讲多个数据库复制到目标系统。也可使用Python正则表达式来指定一组数据库。如果目标数据库不存在，则会创建一个数据库；如果目的数据库不存在，则会报错并退出。

· --dest-database=dest_database_name：指定目标SeaSQL MPP数据库系统中的数据库。

¡ 如果未指定，则将源表复制到与源系统数据库具有相同名称的目标系统数据库中。

¡ 如果源和目标SeaSQL MPP数据库系统相同，则此选项为必选。

¡ 如果目标数据库不存在，则会自动进行创建。

· --dest-host=dest_host：指定目标SeaSQL MPP数据库主机名或IP地址。如果未指定，则默认为运行gptransfer的系统的主机（127.0.0.1）。

· --dest-port=dest_port：指定目标SeaSQL MPP数据库端口号，如果未指定，则默认为5432。

· --dest-user=dest_user：指定用于连接到目标SeaSQL MPP数据库系统的用户标识。如果未指定，则默认为用户gpadmin。

· --drop：指定此选项以删除目标数据库中已存在的表。在复制表数据之前，会删除表并重新创建表。

【注意】：

¡ 最多只能指定--skip-existing--truncate或--drop中的一个选项。如果没有指定其中的一个，并且目标系统中存在该表，gptransfer将返回一个错误并退出。

¡ 不可以和--full、--partition-transfer或--partition-transfer-non-partition-target选项同时使用。

· --source-host=source_host：源SeaSQL MPP数据库主机名或IP地址。如果未指定，则默认主机为运行gptransfer的系统（127.0.0.1）。

· --source-map-file=host_map_file：列出源Segment主机名和IP地址的文件。如果缺少这个文件或没有列出所有的Segment主机，gptransfer会返回一个错误并退出。

¡ host_map_file 的格式为hostname,IPaddress。例如：

sdw1,192.0.2.1

sdw2,192.0.2.2

sdw3,192.0.2.3

sdw4,192.0.2.4

【注意】如果指定了--full选项，或者源SeaSQL MPP数据库系统与目标系统不同，则此选项是必需的。如果源系统和目标系统相同，则不需要此选项。

· --source-port=source_port：源SeaSQL MPP数据库端口号。如果未指定，则默认值为5432。

· --source-user=source_user：用于连接到源SeaSQL MPP数据库系统的用户标识。如果未指定，则默认为用户gpadmin。

· --sub-batch-size=sub_batch_size：指定迁移表时执行的操作（如启动gpfdist实例，为移动操作创建命名管道）所执行操作的最大并行度。如果未指定，则默认值为25。最大值为50。

· -t <db.schema.table>：指定待拷贝源集群数据库中的表。若目标数据库或表不存在，就会自动创建。

· --validate=type：对表数据执行数据验证。以下为支持的验证类型：

¡ count：指定此值以比较源表数据和目标表数据之间的行数。

¡ MD5：指定此值以比较源表和目标表数据之间的MD5值。

如果对表的验证失败，gptransfer将显示表的名称，并将文件名写入文本文件failed_migrated_tables_yyyymmdd_hhmmss.txt。yyyymmdd_hhmmss是gptransfer进程启动时的时间戳。该文件是在执行gptransfer的目录中创建的。

【注意】该文件包含表迁移期间验证失败或发生其他错误的表名。

· -x：

¡ 在源数据库上，当gptransfer插入到外部表中并在验证后被释放时，获得排它锁。

¡ 在目标数据库上，当gptransfer从外部表中选择并在验证之后释放时，获得排它锁。

¡ 如果未指定-x选项并指定了--validate，则在迁移过程中将数据插入到源表或目标表中时，会发生验证失败。如果发生验证错误，gptransfer工具将显示消息。

· --truncate：当目标数据库存在要迁移的表时，在迁移前，先执行truncate清除表数据

2. gptransfer使用示例

从源数据库（Master节点IP：101.8.5.180，Segment节点IP段：101.8.5.181-101.8.5.183）至目的数据库（Master节点IP：101.12.200.11，segment节点IP段：101.12.200.12-101.12.200.18）进行数据迁移操作。

(1) 密钥交换

源集群所有节点和目的集群的所有节点交换密钥：

生成文件诉讼，里面包含源集群和目的集群的所有主机IP。如：

101.8.5.180

101.8.5.181

101.8.5.182

101.8.5.183

101.12.200.11

101.12.200.12

101.12.200.13

101.12.200.14

101.12.200.15

101.12.200.16

101.12.200.17

101.12.200.18

执行以下命令交换密钥：

gpssh-exkeys-old -f exkeys_file

按照提示，输入数据库管理员密码。

(2) 生成迁移所必须的sourcemap文件

ssadmin用户登录SeaSQL MPP源集群Master节点后书写sourcemap文件，写明源数据库各个Segment实例节点（注意，只是含有segment的主机）主机名、IP的映射关系

node2.hde.h3c.com,101.8.5.181

node3.hde.h3c.com,101.8.5.181

node4.hde.h3c.com,101.8.5.181

(3) 执行数据迁移命令

对数据库test_move进行迁移，目的集群Master节点IP为101.12.200.11。

gptransfer –d test_move –analyze –source-port=5434 –source-map-file=’/home/ssadmin/sourcemap’ --source-user=ssadmin --dest-host=101.12.200.11 --dest-port=5434 --dest-user=sysadmin

【说明】在执行以上迁移命令时，如果gptransfer failed. (Reason='fe_sendauth: no password supplied错误。此时，修改目标集群Master节点上的/opt/MPP1/disk/master/gpseg-1/pg_hba.conf文件，修改文件中的以下内容：

host all all 0.0.0.0/0 md5

改为：

host all all 0.0.0.0/0 trust

然后执行gpstop -u命令。

在数据执行迁移完成后，为了安全考虑，需将pg_hba.conf中的修改项改回，然后再次执行gpstop -u。

4.5 并行备份与恢复

4.5.1 gpbackup

gpbackup工具用来备份数据库的内容到元数据文件集合和数据文件集合，这些文件可以通过gprestore工具来恢复数据库。当备份数据库时，可以通过指定表级别和模式级别选项来备份某些特定的表。

1. 用法

gpbackup --dbname database_name

[--backup-dir directory] [--compression-level level] [--data-only] [--debug]

[--exclude-schema schema_name] [--exclude-table schema.table]

[--exclude-table-file file_name] [--include-schema schema_name]

[--include-table schema.table] [--include-table-file file_name]

[--incremental [--from-timestamp backup-timestamp]] [--jobs int]

[--leaf-partition-data] [--metadata-only] [--no-compression]

[--plugin-config config_file_location] [--quiet] [--single-data-file] [--verbose]

[--with-stats]

gpbackup --help

其中：

· --dbname database_name：必选项，指定待备份的数据库。

· --backup-dir directory：可选。复制所有备份文件（元数据文件和数据文件）到指定路径。必须指定directory为绝对路径（不能是相对路径）。如果不提供该选项，元数据文件会保存在Master主机的$MASTER_DATA_DIRECTORY/backups/YYYYMMDD/YYYYMMDDhhmmss/ 路径下，Segment主机的CSV数据文件保存在<seg_dir>/backups/YYYYMMDD/YYYYMMDDhhmmss/ 路径下。

【注意】该选项不能和--plugin-config一起使用。

· --compression-level level：可选。指定用来压缩数据文件的gzip压缩级别（从1到9）。默认为1。

· --data-only：可选。指定仅将表数据备份到CSV文件，不备份用来重建表和其他数据库对象的元数据文件。

· --debug：可选。显示操作期间的调试信息。

· --exclude-schema schema_name：可选。指定要被排除备份的模式名。该参数可以指定多次以排除多个模式。

【注意】该选项不能与 --include-schema一起使用，也不能与表过滤选项一起使用，例如：--include-table。

· --exclude-table schema.table：可选。指定不进行备份的表，被排除的表填写格式为<schema-name>.<table-name> ，如果表或模式名使用了任何非小写字母、数字或下划线，必须使用双引号包裹名字。该选项可以指定多次。

【注意】

¡ 该选项不能与--exclude-schema或--include-table同时使用。该选项不能和--leaf-partition-data组合使用。

¡ gpbackup会忽略子分区名称。

· --exclude-table-file file_name：可选。指定一个包含需要排除在备份之外的表的列表文件。每行必须指定一个单独的表，格式为 <schema-name>.<table-name>，文件不能包含多余的行，如果表或模式名使用了任何非小写字母、数字或下划线，必须用双引号包裹名字。该选项可以指定多次。

【注意】

¡ 该选项不能与--exclude-schema或--include-table同时使用，不能和--leaf-partition-data组合使用。

¡ gpbackup会忽略子分区名称。

· --include-schema schema_name：可选。指定要包含在备份中的数据库模式名。可以多次指定该参数来包含多个备份模式。如果指定该选项，任何不包含在--include-schema中的模式都不会放在备份集中。

【注意】该选项不能与选项 --exclude-schema、--include-table或 --include-table-file一起使用。更多详情请见过滤备份或恢复的内容。

· --include-table schema.table：可选，该选项可以指定多次，指定一个需要包含在备份中的表、序列或视图，格式为<schema-name>.<table/sequence/view-name>，如果表或模式名使用了任何非小写字母、数字或下划线，必须用双引号包裹名字。有关模式和表名中支持的字符信息详情。

【注意】

¡ 该选项不能和模式筛选选项--include-schema或--exclude-table-file等一起使用。

¡ 如果指定该选项，该工具不会自动备份依赖对象。客户必须指定相关依赖对象。例如，如果备份了视图，必须备份视图所用的表。如果备份表用到了一个序列，必须也备份相关的序列。

¡ 可以在表名处通过指定--leaf-partition-data选项来指定表子分区名字，仅备份指定的分区。当指定子分区备份时，子分区数据和分区表的元数据信息都会被备份。

· --include-table-file file_name：可选，指定一个备份时需要的表名、序列和视图列表文件。文件中的每行必须定义为单独的表名，格式为<schema-name>.<table/sequence/view-name>。任何没有列在文件中的表都会被排除在备份集以外。

【注意】

¡ 该选项不能和模式筛选选项--include-schema或--exclude-table-file等一起使用。

· --incremental：指定该选项可以增加一个增量备份到增量备份集中。一个备份集包括一个全量备份和一个或多个增量备份。该备份集合必须是连续性的，以保证在恢复时是可用的。

默认情况下，gpbackup会找出最近的备份，如果该备份是全备份，工具会创建一个备份集合；如果该备份为增量备份，工具会将备份增加到已经存在的备份集中。增量备份将会被增加到备份集的最后。可以通过指定--from-timestamp来覆盖默认的行为。

· --from-timestamp backup-timestamp：可选，指定备份时间戳，指定的备份必须已经有增量备份存在，如果指定的备份只有全量备份，该工具会创建一个备份集；如果指定的备份是增量备份，工具会将增量备份直接备份到该备份集合。

如果备份操作不能增加备份到已经存在的增量备份集或者不能使用备份来创建一个备份集，工具将不能创建备份并且工具会返回错误。

有关创建和使用增量备份的详细信息，请见使用gpbackup和gprestore创建增量备份。

【注意】该选项必须与--leaf-partition-data选项一起使用。不能与--data-only 或--metadata-only一起使用。

· --jobs int：可选，指定进行表备份的并行任务数量，默认gpbackup采用1个任务（数据库连接），增加该参数的值可以提高数据备份的速度。运行多个任务时，每个任务会在一个单独的事务中备份表，例如，如果指定--jobs 2，工具会创建2个进程，每个进程启动一个单独的事务，工具会使用这两个进程并行的备份表。

· 【注意】

¡ 如果指定超过1的值，数据库在工具从表上获取备份用的锁时处于静默状态。如果数据库操作正在那些被备份的表上执行，此时进行备份的话这些表在不同事务中的锁处理和一致性问题不能被保证。

¡ 该选项不能和--metadata-only、--single-data-file或 --plugin-config一起使用。

· --leaf-partition-data：可选。对于分区表，该选项定义后会为每一个子分区创建一个单独的数据文件，而不是为整个表创建一个完成的文件（默认）。使用该选项要求客户通过--include-table-file选项指定包含在备份中的单独子节点分区。

【注意】该选项不能与--exclude-table-file或 --exclude-table选项同时使用。

· --metadata-only：可选。仅创建可用来重建数据库的元数据文件（DDL），不备份表的实际数据。

· --no-compression：可选。不对备份的数据集进行压缩。

· --plugin-config config-file_location：指定gpbackup插件配置文件的位置，该文件是一个YAML格式的文本文件。该文件包含gpbackup在备份操作期间使用的配置信息。

如果在备份数据库时指定了--plugin-config选项，那么在从备份恢复数据库时也需要同样指定对应的配置信息。

【注意】该选项不能与--backup-dir一起使用。

· --quiet：可选。不显示所有非告警、非错误日志信息。

· --single-data-file：可选。在每个Segment主机上为所有备份的表创建一个单独的数据文件。默认情况下，每个gpbackup 会为每张表创建一个压缩CSV文件。

【注意】如果选用--single-data-file选项来将每个Segment上的数据备份为1个文件，就不能在使用gprestore时指定--jobs选项为超过1的值来执行并行恢复。

· --version：可选。打印工具版本号并退出。

· --with-stats：可选。在备份集中包含查询计划统计信息。

· --help：显示在线帮助信息。

2. 返回码

gpbackup完成后会返回如下返回码之一：

· 0：成功完成备份。

· 1：备份完成，没有严重错误。具体信息见日志文件。

· 2：备份失败，有严重错误。具体信息见日志文件。

3. gpbackup使用示例

· 备份“demo”数据库中的所有模式和表，包括数据库全局系统对象：

gpbackup --dbname demo

· 备份“demo”数据库中除了“twitter”模式以外的所有模式和表：

gpbackup --dbname demo --exclude-schema twitter

· 仅备份“demo”数据库中的“twitter”模式：

gpbackup --dbname demo --include-schema twitter

· 备份“demo”数据库中的所有模式和表，包括Greenplum数据库全局系统对象，将所有备份文件复制到/home/ssadmin/backup路径下。

gpbackup --dbname demo --with-stats --backup-dir /home/ssadmin/backup

· 该示例使用--include-schema和--exclude-table 备份指定模式，除了单独的一张表。

gpbackup --dbname demo --include-schema mydata --exclude-table mydata.addresses

--exclude-schema选项不能与表过滤选项一起使用，例如--include-table。

4.5.2 gprestore

gprestore为备份恢复工具，主要是为了根据用户指定的时间点从gpbackup备份的备份集中获取备份数据对数据库进行恢复。

· 使用gprestore从备份集恢复时，必须使用--timestamp选项指定一个确切来自备份集的恢复时间值（YYYYMMDDHHMMSS）。如果在备份时指定了--backup-dir，那么gprestore同样需要指定--backup-dir来获取备份文件。

· 如果指定的是增量备份，则还需要一个完全备份的文件集合（一个全备和所有需要的增量备份）。 gprestore会在开始恢复之前验证备份集合是可用的。

1. 用法

gprestore --timestamp YYYYMMDDHHMMSS

[--backup-dir directory] [--create-db]

[--debug]

[--exclude-schema schema_name]

[--exclude-table schema.table]

[--exclude-table-file file_name]

[--include-schema schema_name]

[--include-table schema.table]

[--include-table-file file_name]

[--data-only | --metadata-only]

[--jobs int]

[--on-error-continue]

[--plugin-config config_file_location]

[--quiet]

[--redirect-db database_name]

[--verbose]

[--version]

[--with-globals]

[--with-stats]

gprestore --help

其中：

· --timestamp YYYYMMDDHHMMSS：必选，指定一个用来恢复gpbackup备份集的时间戳。默认gprestore 会尝试从Master主机的$MASTER_DATA_DIRECTORY/backups/YYYYMMDD/YYYYMMDDhhmmss/路径下定位时间戳对应的元数据文件，从每个Segment主机的<seg_dir>/backups/YYYYMMDD/YYYYMMDDhhmmss/路径下定位CSV数据文件。

· --backup-dir directory：可选，从指定的路径下找所有备份文件（元数据文件和数据文件）。必须指定directory为一个绝对路径（不能是相对路径）。如果没有指定该选项，gprestore会尝试从Master主机的$MASTER_DATA_DIRECTORY/backups/YYYYMMDD/YYYYMMDDhhmmss/ 路径下定位时间戳对应的元数据文件，从每个Segment主机的<seg_dir>/backups/YYYYMMDD/YYYYMMDDhhmmss/ 路径下定位CSV数据文件。

【注意】如果gpbackup操作指定了该选项，那么恢复时请同时指定该选项。该选项不能和--plugin-config选项一起使用。

· --create-db：可选，在恢复数据库对象元数据之前先创建数据库。通过复制空的系统标准数据库template0来创建指定的数据库。

· --data-only：可选，仅恢复gpbackup创建的表数据，不恢复创建数据库表的操作。该选项假定数据库中已经存在对应的表，如果要恢复指定的集合，可以通过指定选项来包含表/模式或排除表/模式。指定--with-stats选项来从备份集恢复表的统计信息。

备份集必须包含要被恢复的表数据。例如，gpbackup采用选项 --metadata-only备份的备份集不包含表数据，不能用来恢复数据。如果要仅恢复数据库表，不恢复表里的数据，参见选项 --metadata-only。

· --debug：可选。显示操作期间的详细信息和调试日志。

· --exclude-schema schema_name：可选。指定恢复操作期间需要排除的数据库模式。可以多次指定以排除多个模式。

【注意】该选项不能和 --include-schema或表过滤选项（例如：--include-table）一起使用。

· --exclude-table schema.table：可选，指定恢复操作期间需要排除的表，可以多次指定该选项。指定的格式必须为<schema-name>.<table-name>，如果表名或模式名使用了非小写字母、数字或下划线，则该名称需要用双引号包裹。如果表不在备份集中，恢复操作会失败。不能指定分区表的子分区。

【注意】该选项不能与--exclude-schema或表过滤选项（例如：--include-table）一起使用。

· --exclude-table-file file_name：可选，指定恢复期间待排除表的列表文件，可以多次指定该选项。文件为text格式，并且每行都必须定义一个单独的表，格式为 <schema-name>.<table-name>。文件不能包含多余的行。如果表名或模式名使用了非小写字母、数字或下划线，该名称可以用双引号包裹。如果表不在备份集中，恢复操作会失败。不能指定分区表的子分区。

【注意】该选项不能与--exclude-schema或表过滤选项（例如：--include-table）一起使用。

· --include-schema schema_name：可选。指定待恢复的数据库模式。可以多次指定该选项以包含多个模式。

¡ 如果指定了该选项，则定义的任何模式都必须在备份集中，未在--include-schema选项中指定的所有模式都会被忽略。

¡ 如果指定的模式在目标数据库中存在，该工具会产生一个错误并继续操作。如果被恢复的表在数据库中存在，该工具会失败。

¡ 如果备份集在多个模式下存在依赖关系，那不能使用该选项。

· --include-table schema.table：可选，指定一个要恢复的表，格式必须为<schema-name>.<table-name>。该选项可以多次指定，不能指定分区表的子分区，也可以指定一个有效的模式或视图。如果任何表名或模式名使用非小写字母、数字或下划线，可使用双引号包裹名称。

如果指定了该选项，工具不会自动恢复依赖对象。必须指定需要的依赖对象。例如，如果恢复一个视图，那么必须也恢复它对应的表。如果恢复的表使用到了一个序列，那么也必须恢复该序列。这些对应的对象也必须都在备份集中存在。

【注意】不能将该选项与--include-schema或表过滤选项（例如--exclude-table-file）一起使用。

· --include-table-file file_name：可选，指定一个包含恢复表名的列表文件，每行代表一张单独的表，格式必须为 <schema-name>.<table-name>，该文件不能包含多余的行，如果表名或模式名使用了非小写字母、数字或下划线，则该名称可使用双引号包裹。可以多次指定该选项。如果表不在备份集中，恢复操作会失败。也可以指定一个有效的模式或视图。不能指定分区表的子分区。

如果使用--include-table-file选项，gprestore不会创建表的角色或用户集合。工具会恢复表的索引和规则，触发器也会被恢复，但是SeaSQL MPP目前不支持触发器。

· --jobs int：可选，指定恢复表数据和元数据的并行连接的数量。默认gprestore使用1个连接。增加该参数会提高数据恢复的速度。

【注意】如果使用gpbackup --single-data-file选项合并表备份为每个Segment节点1个单独的文件，那么不能在恢复过程中指定--jobs超过1来进行并行恢复。

· --metadata-only：可选。从一个gpbackup创建的备份集中创建数据库表，不恢复数据。该选项假设目标数据库中不存在这些表，要从备份集创建指定的表集合，可以通过指定对应的选项来选择或排除相应的表或模式。指定选项--with-globals来恢复数据库系统对象。

备份集必须包含要恢复的表的DDL数据。例如，gpbackup选项--data-only生成的备份集就不包含表的DDL信息。

创建完数据库表后再恢复表数据，请参见选项--data-only。

· --on-error-continue：可选，指定该选项以在创建数据库元数据（例如表、对象或函数）或恢复数据过程中出现SQL错误时，忽略错误继续执行。gprestore会显示错误信息汇总并将错误信息写入到日志文件，并继续恢复操作。如果有其他类型错误出现，gprestore将会退出。

默认为第一次出现错误即退出。

· --plugin-config config-file_location：指定gpbackup插件配置文件位置，它是一个YAML格式的文本文件。该文件包含gprestore在恢复操作期间使用的插件的配置信息。

如果备份数据库时指定了--plugin-config选项，那么在从该备份集恢复数据时也必须指定该配置文件。

有关存储插件应用的详细信息，请见使用gpbackup存储插件。

【注意】该选项不能与--backup-dir同时使用。

· --quiet：可选。禁止任何非告警、非错误日志输出。

· --redirect-db database_name：可选。恢复到指定的database_name，而不是备份时的默认数据库名。

· --verbose：可选。显示恢复操作期间的详细日志信息。

· --version：可选。打印版本号并退出。

· --with-globals：可选。恢复备份集合中的数据库系统对象，和其余数据库对象。详见备份或还原中包含的对象。

· --with-stats：可选。从备份集合恢复查询计划统计信息。

· --help：显示在线帮助信息。

2. 返回码

gprestore完成后会返回如下返回码之一

· 0：成功完成恢复。

· 1：恢复完成，没有严重错误。具体信息见日志文件。

· 2：恢复失败，有严重错误。具体信息见日志文件。

3. gprestore使用示例

· 创建demo数据库并恢复备份集合中指定时间的所有表名和模式名：

dropdb demo

gprestore --timestamp 20171103152558 --create-db

· 恢复备份集到demo2数据库而不是默认备份的demo数据库：

createdb demo2

gprestore --timestamp 20171103152558 --redirect-db demo2

· 恢复数据库全局对象和查询计划统计信息，而不是默认的全部数据：

gprestore --timestamp 20171103152558 --create-db --with-globals --with-stats

· 使用创建在/home/ssadmin/backup下的文件恢复数据，创建8个并行连接：

gprestore --backup-dir /home/ssadmin/backups/ --timestamp 20171103153156 --create-db --jobs 8

· 仅恢复备份集中的wikipedia模式：

dropdb demo

gprestore --include-schema wikipedia --backup-dir /home/ssadmin/backups/ --timestamp 20171103153156 --create-db

· 如果从增量备份集中恢复，所有需要的备份文件都必须对gprestore可用。例如，以下时间戳指定增量备份集。20170514054532时增量备份集合的全备份。

20170514054532 (full backup)

20170714095512

20170914081205

20171114064330

20180114051246

· 以下gprestore命令指定时间戳20121114064330。时间戳为20120714095512和 20120914081205的增量备份和全量备份必须都能被恢复程序访问。

gprestore --timestamp 20121114064330 --redirect-db mystest --create-db

4.6 非并行备份与恢复（pg_dump与pg_restore）

非并行备份方法仅限在特殊情况时使用，比如：数据量较小且由于新旧集群节点数不一致引发的并行恢复不能实现等场景。因为其备份与恢复必须要通过Master节点，效率很低，且会对Master节点的运行性能产生较大的影响。如需要迁移的数据量比较大，不建议使用该方法进行数据备份与恢复。

非并行备份方法使用pg_dump命令来进行集群中数据的备份，将所有文件备份都保存在Master节点。恢复时将备份的数据拷贝到待导入数据的Master节点，然后执行pg_restore或psql命令进行数据的导入。

pg_dump可以在本地或者远程备份，只需要表的读取权限即可，pg_dump创建的备份是一致的，在运行期间数据库产生快照，不阻塞数据库的DML操作，但是会阻塞需要排他锁的操作，如alter table等操作，pg_dump只能备份数据，不能备份角色和表空间信息。

示例中使用了psql作为数据的导入命令，也可以使用pg_restore来进行数据的导入。关于该命令，可参考pg_restore --help。

1. 表级别备份与恢复

· 示例：备份test_db的test_table表

· 导出语句：

pg_dump -t test_table -U ssadmin -W test_db -f /home/ssadmin/test_table.dmp

· 查看test_table.dmp文件存在以下内容：

图4-1 查看文件

· 导入语句：

psql test_db -U ssadmin -W </home/ssadmin/test_table.dmp

图4-2 导入语句

2. 数据库级别备份与恢复

· 示例：备份test_db数据库

· 导出语句：

pg_dump test_db -U ssadmin -W -f /home/ssadmin/test_db.dmp

可将整个数据库导出到/home/ssadmin/test_db.dmp文件中

· 导入语句：

psql --single-transaction test_db < test_db.dmp

执行入库操作之前需要手动创建数据库。

图4-3 导入语句

恢复数据库前需使用psql登陆需要恢复的数据库test_db要执行以下命令删除对应的残余信息。

· drop function public.java_call_handler() cascade;

· drop function public.javau_call_handler cascade;

3. Schema级别备份与恢复

· 示例：备份test_db中的public schema

· 导出语句：

pg_dump test_db -U ssadmin -n public -W -f /home/ssadmin/test_db_public.dmp

· 导入语句：

psql --single-transaction test_db -U ssadmin -W < test_db_public.dmp

数据导入之前需要手动创建public schema

图4-4 导入语句

5 SeaSQL MPP特性功能

5.1 MADlib

MADlib是一个开源的机器学习库，提供了多种数据转换、数据探索、统计、数据挖掘和机器学习方法，使用它能够简易的对结构化数据进行分析和挖掘。

SeaSQL MPP集成了MADlib库，方便用户将MADlib加载到数据库中，以扩展数据库的分析功能。

1. 安装MADlib库

安装MADlib库操作如下：

(1) 登录Master节点所在服务器。

(2) 切换至ssadmin用户，命令如下

su - ssadmin

(3) 安装MADlib库。命令如下：

madpack install -s <madlib> -p greenplum -c <ssadmin@node1:5434/testdb>

其中：

¡ -s：用于指定schema，新建的模式名

¡ -p：用于指定greenplum数据库平台

¡ -c：用于指定Master节点信息。格式为：用户名@节点名：端口号/数据库名

(4) 连接到安装MADlib的数据库，命令如下：

psql -d <db_name>

其中<db_name>为已安装MADlib库的数据库名称。

2. MADlib使用示例

· Madlib会安装指定的schema中，因此在调用MADlib函数时，需要使用“schema名称.函数名”来调用对应的函数。

· 关于MADlib的更多使用方法，详情可参见MADlib官网https://madlib.apache.org/docs/latest/index.html。

示例场景：调用MADlib库的函数进行矢量的加减乘除以及求点积的运算。

(1) 创建向量表，命令如下：

create table array_tb1 (id integer, array1 integer[], array2 integer[]);

(2) 插入数据，命令如下：

insert into array_tb1 values

(1, '{1,2,3,4,5,6,7,8,9}', '{9,8,7,6,5,4,3,2,1}'),

(2, '{1,3,5,7,9,11,13,15}', '{2,4,6,8,10,12,14,16}');

(3) 向量加减运算，命令如下：

select id,madlib.array_add(array1,array2),madlib.array_sub(array1,array2) from array_tb1;

5.2 postGIS

postGIS是对象PostgreSQL数据库的扩展，PostGIS提供空间信息服务功能，例如空间对象、空间索引、空间操作函数和空间操作符等。

SeaSQL MPP集成了postGIS库，方便用户使用。

1. 安装postGIS扩展到数据库中

登录某数据库所在服务器节点，执行create extension postgis;命令即可将postGIS安装到该数据库中。

2. postGIS使用示例

示例场景：调用postGIS计算两个城市之间的地理距离。

(1) 创建一个带有geography类型的列，命令如下：

CREATE TABLE global_points (

id SERIAL PRIMARY KEY,

name VARCHAR(64),

location GEOGRAPHY(POINT,4326)

);

其中：

¡ id：序号

¡ name：城市名

¡ location：该城市的地理坐标

(2) 插入数据并创建基于球面的索引，命令如下：

INSERT INTO global_points (name, location) VALUES ('Town', ST_GeographyFromText('SRID=4326;POINT(-110 30)') );

INSERT INTO global_points (name, location) VALUES ('Forest', ST_GeographyFromText('SRID=4326;POINT(-109 29)') );

INSERT INTO global_points (name, location) VALUES ('London', ST_GeographyFromText('SRID=4326;POINT(0 49)') );

CREATE INDEX global_points_gix ON global_points USING GIST ( location );

(3) 计算一架飞机从西雅图飞到伦敦这条航线(LINESTRING(-122.33 47.606, 0.0 51.5))到雷克雅末克（冰岛首都）(POINT(-21.96 64.15))的距离。命令如下：

SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography);

关于postGIS的更多使用方法，详情请参见postGIS官网http://postgis.net/documentation/。

5.3 全文检索

SeaSQL MPP支持全文检索，并可以支持中英文等多种语言。中文检索默认未开启，需手动启用中文检索。目前SeaSQL MPP提供了zhparser和jieba这两种中文检索扩展包，用户可根据需要选择。

5.3.1 启用中文检索

1. 启用zhparser

启用zhparser中文检索扩展包，操作如下：

登录或切换至到目标数据库。

(1) 创建zhparser扩展，命令如下：

create extension zhparser;

(2) 扩展创建后可以通过\dF命令查看到新增了一个zhprscfg的全文检索配置，后续即可在全文检索函数中使用该配置。

(3) 若要修改全局生效的全文检索默认配置，需要使用ssadmin用户登录到SeaSQL MPP Master所在节点执行命令，命令如下：

gpconfig -c default_text_search_config -v zhprscfg

执行结束后需要执行如下命令使配置生效。

gpstop -u

2. 启用jieba

启用jieba中文检索扩展包，操作如下：

(1) 登录或切换至目标数据库。

(2) 创建jieba扩展，命令如下：

create extension pg_jieba;

(3) 修改全文检索默认配置，命令如下：

set default_text_search_config=jiebacfg;

(4) 扩展创建后可以通过\dF命令查看到新增了一个jiebacfg的全文检索配置，后续即可在全文检索函数中使用该配置。

(5) 若要修改全局生效的全文检索默认配置，需要使用ssadmin用户登录到SeaSQL MPP Master所在节点执行命令，命令如下：

gpconfig -c default_text_search_config -v jiebacfg

执行结束后需要执行如下命令使配置生效。

gpstop -u

5.3.2 使用全文检索

1. 全文检索搜索示例

一个简单的搜索文档的例子，命令如下：

SELECT to_tsvector('english', 'fat cats ate fat rats') @@ to_tsquery('english', 'fat & rat');

其中：

· 命令搜索操作符“@@”的左右两边的数据类型须分别为tsvector和tsquery。

· 文本转换函数to_tsvector和to_tsquery可以将普通文本转换成全文检索所需的文本类型。函数to_tsvector和to_tsquery的第一个参数为全文检索配置的名称，可以根据需要指定，可使用\dF命令查看已有的检索配置。

¡ to_tsvector可以对文档进行分析处理。

¡ to_tsquery可以对待搜索的单词进行包装，且to_tsquery中的参数可以使用与、或、非运算符。

2. 对全文检索建立索引

GiST索引（广义搜索树索引）和GIN索引（广义倒置索引）可以加速全文检索效率。其中：

· 创建基于GiST（广义搜索树）的索引，语法如下：

CREATE INDEX name ON table USING gist(column);

说明：该column参数可以是tsvector或tsquery类型。

· 创建基于GIN（广义倒置索引）的索引，语法命令如下：

CREATE INDEX name ON table USING gin(column);

说明：该column参数必须是tsvector类型。

例如，以下语句创建了一个GIN索引，在查询时可以使用该索引来加速查询。操作如下：

a. 创建示例表article，命令如下：

CREATE TABLE article (title varchar(200), body text) DISTRIBUTED RANDOMLY;

b. 创建GIN索引，命令如下：

CREATE INDEX gin_idx ON article USING gin(to_tsvector('zhprscfg', body));

其中，zhprscfg是中文检索的配置名称，body是表article的一个字段。设置配置名称前需要已安装zhparser，如果未安装将无法使用zhprscfg配置，可使用\dF命令查看已有的检索配置。

c. 向示例表article中插入两条数据，供测试使用，命令如下：

INSERT INTO article VALUES ('pgsql is very fast after 2017', 'postgresql is an opensource database. it is a very fast database.'), ('Mysql is very popular', 'mysql is an opensource database. it is a very popular database before.');

· GIN索引查询速度通常比GiST快，但GIN索引的建立和更新比GiST索引慢，并且GIN索引占用空间比GiST索引更大。

· GiST索引适用于唯一词较少的场景（少于10万个词素lexemes），且GIN索引可以更好地处理10万以上词素，但更新速度较慢。

3. 全文检索排序函数示例

(1) 使用排序函数ts_rank进行，对搜索结果进行排序。命令如下：

select title,ts_rank(to_tsvector('english', body), to_tsquery('english', 'fast | database')) as rank from article order by rank desc;

(2) 使用标记函数ts_headline进行标记，与标记函数匹配的会以“<b>”和“</b>”进行标记，命令如下：

select title, body, ts_headline('english', body, query) from article, to_tsquery('english', 'fast | database') as query where to_tsvector('english', body) @@ query;

5.4 RoaringBitMap

RoaringBitMap是一种高效的位图压缩算法，可以有效提升位图在内存的使用效率，解决稀疏位图不适应稀疏存储的问题。Bitmap位计算非常适合大数据基数计算，常用于去重、标签筛选、时间序列等计算中。gpdb_roaringbitmap插件将Roaringbitmap功能集成到SeaSQL数据库中，将Roaringbitmap作为一种数据类型提供原生的数据库函数、操作符、聚合等功能支持。

Roaringbitmap仅支持正整数操作。

1. 安装gpdb_roaringbitmap插件

(1) 使用ssadmin用户登录数据库所在服务器节点。

(2) 安装gpdb_roaringbitmap插件，命令如下：

psql -c "create extension roaringbitmap;"

2. 关闭优化器

在执行roaringbitmap的功能函数之前，需要在该会话关闭优化器否则聚合函数将会出错。关闭优化器的指令需要登录SeaSQL MPP数据库后执行以下命令：

postgres=# set optimizer=off;

3. gpdb_roaringbitmap插件使用示例

(1) 创建表

CREATE TABLE t1 (id integer, bitmap roaringbitmap);

(2) 生成Bitmap

INSERT INTO t1 SELECT 1,RB_BUILD(ARRAY[1,2,3,4,5,6,7,8,9,200]);

INSERT INTO t1 SELECT 2,RB_BUILD_AGG(e) FROM GENERATE_SERIES(1,100) e;

(3) Bitmap计算

SELECT RB_OR(a.bitmap,b.bitmap) FROM (SELECT bitmap FROM t1 WHERE id = 1) AS a,(SELECT bitmap FROM t1 WHERE id = 2) AS b;

(4) Bitmap聚合

SELECT RB_OR_AGG(bitmap) FROM t1;

SELECT RB_AND_AGG(bitmap) FROM t1;

SELECT RB_XOR_AGG(bitmap) FROM t1;

SELECT RB_BUILD_AGG(e) FROM GENERATE_SERIES(1,100) e;

关于gpdb_roaringbitmap的更多使用方法，详情请参见官网https://github.com/zeromax007/gpdb-roaringbitmap。

5.5 数据脱敏

数据脱敏(Data Masking),又称数据漂白、数据去隐私化或数据变形。指对某些敏感信息通过脱敏规则进行数据的变形，实现敏感隐私数据的可靠保护。在涉及客户安全数据或者一些商业性敏感数据的情况下，在不违反系统规则条件下，对真实数据进行改造并提供测试使用，如身份证号、手机号、卡号、客户号等个人信息都需要进行数据脱敏。

SeaSQL MPP ANON插件实现了库内数据脱敏功能，其特征包括：

· 脱敏规则由用户定义，使用方便灵活

· 在表定义的时候声明脱敏规则，相当于是对DDL的扩展

· 多种脱敏函数可选，例如：随机、伪造、部分加扰等

· 脱敏方式多样化，定义脱敏规则以后，用户可以通过以下两种方式访问脱敏数据：

¡ 静态脱敏：去除库内敏感信息，数据库内敏感数据被覆盖，不可恢复

¡ 动态脱敏：对指定用户屏蔽敏感信息，其他未被脱敏的用户仍将访问原始数据

SeaSQL MPP提供两种方式进行数据脱敏操作：

· 通过Monitor进行数据脱敏操作，建议用户通过Monitor快捷方便的使用数据脱敏功能，具体内容请参见Monitor联机帮助。

· 通过后台进行数据脱敏操作，建议仅专业应用程序开发人员选择。

5.5.1 安装ANON插件

使用管理员用户登录SeaSQL MPP数据库，执行以下语句安装ANON插件：

create extension anon cascade;

5.5.2 设计脱敏规则

1. 选择脱敏函数

根据需要，选择所需的脱敏函数并设计脱敏规则，设计完成后，根据2. 添加脱敏规则章节的示例语句声明脱敏规则。

脱敏类型	脱敏类型说明	脱敏规则	脱敏规则说明	示例
常量	在许多情况下，隐藏列内容的最佳方法是用单个静态值替换所有值。可以使用此规则函数实现常量字符串替换	cast(const_str AS TEXT)	· 返回TEXT类型常量字符串const_str · 参数const_str为TEXT类型字符串	cast('CONFIDENTIAL' AS TEXT) 将所选列内容替换为'CONFIDENTIAL'常量字符串
随机	随机是用随机值替换敏感数据（日期、整型数值、字符串），可根据需要选择脱敏规则使用	anon.random_date()	· 返回一个1900-01-01到当前时间之间的随机时间 · 返回值类型为TIMESTAMP WITH TIME ZONE	-
		anon.random_date_between(d1 DATE,d2 DATE)	· 返回介于日期d1和d2之间的一个随机时间。参数d1为起始日期，d2为结束日期 · d1/d2类型为DATE、或者能被强制转换为DATE的类型，如以下格式的文本：'yyyy-mm-dd'或者'mm/dd/yyyy'	-
		anon.random_int_between(i1 integer,i2 integer)	· 返回整数i1和i2之间的一个随机整数	-
		anon.random_string(n integer)	· n为正整数，字符范围为'[0-9][A-Z]' · 返回包含n字符的TEXT类型随机字符串	-
		anon.random_zip()	· 返回包含6位数字的TEXT类型随机字符串 · 字符范围为'[0-9]'	-
		anon.random_phone(prefix text)	· 返回带有prefix前缀的TEXT类型9位随机电话号（不包含prefix长度），返回字符串字符范围为'[0-9]' · 参数prefix字符范围为'[0-9]'，prefix缺省值是'0'	anon.random_phone('010') 返回带有'010'前缀的9位数字随机电话号码
伪造	伪造是用随机但合理的值替换敏感数据，可根据需要选择脱敏规则	anon.fake_first_name()	返回TEXT类型通用名	-
		anon.fake_last_name()	返回TEXT类型通用姓氏	-
		anon.fake_email()	返回TEXT类型有效的电子邮件地址	-
		anon.fake_city()	返回TEXT类型城市名	-
		anon.fake_region()	返回TEXT类型区域名	-
		anon.fake_country()	返回TEXT类型国家名	-
		anon.fake_company()	返回TEXT类型公司名称	-
		anon.fake_iban()	返回TEXT类型有效的IBAN	-
		anon.fake_siret()	返回TEXT类型有效的SIRET	-
		anon.fake_siren()	返回TEXT类型有效的SIREN	-
部分加扰	部分加扰屏蔽了字符串中间的部分数据。例如：卡号可以替换为"40XXXXXXXXXXXX96"。可根据需要选择脱敏规则	anon.partial(ov text, prefix integer, padding text, suffix integer)	· 返回部分加扰后的TEXT类型字符串 · 参数ov为所选列的名字，列的类型为TEXT类型或者能被强制转换为TEXT类型的类型 · 参数prefix为不加扰前缀的长度 · 参数padding为加扰的字符串 · 参数suffix为不加扰后缀的长度	anon.partial('abcdefgh',1,'xxxx',3) 将返回'axxxxfgh'
部分加扰	部分加扰屏蔽了字符串中间的部分数据。例如：卡号可以替换为"40XXXXXXXXXXXX96"。可根据需要选择脱敏规则	anon.partial_email(ov text)	· 返回部分加扰后的TEXT类型邮箱地址 · 参数ov为所选列的名字，列的类型为TEXT类型邮箱地址	anon.partial_email('daamien@gmail.com') 将返回如下格式的字符串： 'da****@gm****.com'

2. 添加脱敏规则

使用COMMENT关键字添加脱敏规则语法如下：

COMMENT ON COLUMN table_name.column_name IS 'MASKED WITH FUNCTION anon_func';

其中：

· table_name标识表名

· column标识列名

· anon_func标识所选的脱敏函数

例如，如果要在player表中的name列添加脱敏规则anon.fake_last_name()，需要执行以下操作：

COMMENT ON COLUMN player.name IS 'MASKED WITH FUNCTION anon.fake_last_name()';

执行加脱敏规则语句后，系统表anon.pg_masks中会增加相应行，以保存脱敏规则，具体表结构及内容请参见5.5.4 数据脱敏相关系统表。

具体完整使用脱敏规则示例，请参见5.5.3 使用脱敏规则。

3. 数据类型转换

各种脱敏函数将返回特定的数据类型。例如：

· 伪造函数（例如fake_email()）将返回TEXT数据类型的值

· 随机函数将返回TEXT，INTEGER 或TIMESTAMP WITH TIMEZONE

如果需要脱敏的列与脱敏函数返回的是不同的数据类型（例如 VARCHAR(30)），则需要直接在脱敏规则声明中添加显式强制类型转换，如下所示：

COMMENT ON COLUMN clients.family_name IS 'MASKED WITH FUNCTION anon.fake_last_name()::VARCHAR(30)';

4. 移除脱敏规则

把脱敏规则设置为NULL即可移除脱敏规则。

语法如下：

COMMENT ON COLUMN table_name.column_name IS NULL;

其中：

· table_name标识表名

· column_name标识列名

例如，如果要移除player表中name列对应的脱敏规则，需要执行以下操作：

COMMENT ON COLUMN player.name IS NULL;

注：执行加脱敏规则语句后，系统表anon.pg_masks中会删除相应行，以删除已经保存的脱敏规则。

5.5.3 使用脱敏规则

可通过静态脱敏或动态脱敏两种方式使用脱敏规则。

1. 静态脱敏

静态脱敏将改变数据库相应列的真实数据，此操作不可逆，请谨慎使用。

静态脱敏即根据脱敏规则，改变数据库中的真实数据。

静态脱敏执行函数：

anon.anonymize_database()

返回值：boolean类型，执行成功返回’t’，执行失败返回’f’。

实施静态脱敏数据库内敏感数据被覆盖，不可恢复，请谨慎使用。

静态脱敏示例：

(1) 创建表并插入数据

CREATE TABLE customer(

id SERIAL,

full_name TEXT,

birth DATE,

employer TEXT,

zipcode TEXT,

fk_shop INTEGER

);

INSERT INTO customer

VALUES

(911,'Chuck Norris','1940/03/10','Texas Rangers', '75001',12),

(312,'David Hasselhoff','1952/07/17','Baywatch', '90001',423);

(2) 查看脱敏前表结构和表中数据。

SELECT * FROM customer;

查询结果：

-----+------------------+------------+---------------+---------+---------

911 | Chuck Norris | 1940-03-10 | Texas Rangers | 75001 | 12

312 | David Hasselhoff | 1952-07-17 | Baywatch | 90001 | 423

(3) 加载扩展

CREATE EXTENSION IF NOT EXISTS anon cascade;

SELECT anon.load();

(4) 添加脱敏规则

COMMENT ON COLUMN customer.full_name IS 'MASKED WITH FUNCTION anon.fake_first_name() || '' '' || anon.fake_last_name()';

COMMENT ON COLUMN customer.employer IS 'MASKED WITH FUNCTION anon.fake_company()';

COMMENT ON COLUMN customer.zipcode IS 'MASKED WITH FUNCTION anon.random_zip()';

(5) 执行静态脱敏，替换已屏蔽列中的真实数据

SELECT anon.anonymize_database();

(6) 查看静态脱敏后的数据

SELECT * FROM customer;

查询结果：

-----+-------------+------------+---------------------+---------+---------

911 | jesse Kosel | 1940-03-10 | Marigold Properties | 62172 | 12

312 | leolin Bose | 1952-07-17 | Inventure | 20026 | 423

2. 动态脱敏

· 在一个数据库内，同一时刻只能打开一个动态脱敏引擎，如果需要对其它Schema进行动态脱敏，请先关闭已打开的动态脱敏引擎。

· 对于所有被脱敏用户，动态脱敏行为只对打开脱敏引擎后新建立的Session生效，对打开动态脱敏引擎时已经存在的Session不生效。

· 动态脱敏对具有管理员权限的用户不生效，例如：ssadmin、pg_signal_backend和gpmon等。

· 被脱敏用户对脱敏Schema中表的引用不需要加“schema.”前缀，对非脱敏Schema中表的引用需要加“schema.”前缀。

· 在执行动态脱敏的过程中，除管理员用户外的其它用户禁止进行DDL操作。

动态脱敏即通过将数据库某用户声明为”MASKED”来动态脱敏该用户的某些数据，当声明用户访问已配置脱敏规则的列时，将展示脱敏数据；未声明用户将展示真实数据。

动态脱敏相关函数：

· 打开动态脱敏引擎，对schema ‘sourceschema’ 进行动态脱敏。

anon.start_dynamic_masking(sourceschema TEXT DEFAULT 'public');

返回值：boolean类型，执行成功返回“t”，执行失败返回“f”。

执行此函数成功后，会更新系统表anon.config，以保存动态脱敏的相关参数信息，具体表结构及内容请参见5.5.4 数据脱敏相关系统表。

· 关闭动态脱敏引擎。

anon.stop_dynamic_masking();

返回值：boolean类型，执行成功返回“t”，执行失败返回“f”。

执行此函数成功后，会更新系统表anon.config，以删除动态脱敏的相关参数信息，具体表结构及内容请参见5.5.4 数据脱敏相关系统表。

动态脱敏示例：

(1) 创建数据库、表并插入数据

create database peopledb;

\c peopledb

CREATE TABLE people ( id TEXT, fistname TEXT, lastname TEXT, phone TEXT);

INSERT INTO people VALUES ('T1','Sarah', 'Conor','0609110911');

(2) 查看脱敏前表结构和表中真实数据

SELECT * FROM people;

查询结果：

id | fistname | lastname | phone

----+----------+----------+------------

T1 | Sarah | Conor | 0609110911

(1 row)

(3) 加载扩展，打开动态脱敏引擎

CREATE EXTENSION IF NOT EXISTS anon cascade;

SELECT anon.load();

SELECT anon.start_dynamic_masking();

(4) 创建并声明被脱敏的用户

创建被脱敏用户

createuser skynet

在文件$MASTER_DATA_DIRECTORY/pg_hba.conf尾部加入下面一行数据，使用户skynet具有访问peopledb数据库的权限

local peopledb skynet trust

执行以下命令，使权限修改生效

gpstop -u

COMMENT ON ROLE skynet IS 'MASKED';

执行此语后，系统表anon.pg_masked_roles相应行的hasmask列值会被更新为“t”，表明相应用户被标识为动态脱敏用户。

(5) 添加脱敏规则

COMMENT ON COLUMN people.lastname IS 'MASKED WITH FUNCTION anon.fake_last_name()';

COMMENT ON COLUMN people.phone IS 'MASKED WITH FUNCTION anon.partial(phone,2,$$******$$,2)';

(6) 使用被脱敏的用户访问数据库

psql peopledb -U skynet -c 'SELECT * FROM people;'

查询结果：

id | fistname | lastname | phone

----+----------+-----------+------------

T1 | Sarah | Stranahan | 06******11

(1 row)

(7) 取消动态脱敏用户

想要把skynet用户重新标记为非动态脱敏用户，需要执行以下语句：

COMMENT ON ROLE skynet IS NULL;

执行此语后，系统表anon.pg_masked_roles相应行的hasmask列值会被更新为“f”，表明相应用户被标识为非动态脱敏用户。

5.5.4 数据脱敏相关系统表

1. anon.config

anon.config系统表存储动态脱敏的参数信息。

表5-1 anon.config系统表

名称	类型	描述
param	TEXT	动态脱敏参数名称，包括： · sourceschema · maskschema
value	TEXT	动态脱敏参数的值，打开动态脱敏引擎后，会根据所传入的参数自动更新对应参数的值 · sourceschema：动态脱敏时源数据所属的schema · maskschema：动态脱敏时脱敏数据所属的schema

名称

类型

描述

param

TEXT

动态脱敏参数名称，包括：

· sourceschema

· maskschema

value

TEXT

动态脱敏参数的值，打开动态脱敏引擎后，会根据所传入的参数自动更新对应参数的值

· sourceschema：动态脱敏时源数据所属的schema

· maskschema：动态脱敏时脱敏数据所属的schema

2. anon.pg_masks

anon.pg_masks系统表存储脱敏规则信息。

表5-2 anon.pg_masks系统表

名称	类型	描述
attrelid	oid	列所属的表oid
attname	name	列名
relid	oid	列所属的表oid
relname	name	表名
format_type	text	列类型
col_description	text	列脱敏规则
masking_function	text	列脱敏函数

3. anon.pg_masked_roles

anon.pg_masks系统表存储脱敏用户信息。

表5-3 anon.pg_masks系统表

名称	类型	描述
rolname	name	角色名
rolsuper	bolean	角色是否具有超级用户权限
rolinherit	bolean	是否继承权限
rolcreaterole	bolean	能否创建角色
rolcreatedb	bolean	能否创建数据库
rolcanlogin	bolean	是否具有登录权限
rolreplication	bolean	是否具有流复制权限
rolconnlimit	integer	可以发起的最大并发连接数
rolpassword	text	脱敏后的口令，比如：”********”
rolvaliduntil	timestamp with time zone	口令失效时间，为空表示永不失效
rolbypassrls	boolean	是否绕过每一条行级安全性策略
rolconfig	text[]	角色相关的缺省值
rolresqueue	oid	所属资源队列oid
oid	oid	角色oid
rolcreaterextgpfd	boolean	是否具有创建gpfdist协议可读外部表权限
rolcreaterexthttp	boolean	是否具有创建http协议可读外部表权限
rolcreatewextgpfd	boolean	是否具有创建gpfdist协议可写外部表权限
rolresgroup	oid	所属资源组oid
hasmask	boolean	是否为动态脱敏用户

5.6 SeaSQL FDW

SeaSQL MPP支持在外部数据包装器（foreign-data wrapper，FDW）的帮助下访问外部数据。外部数据包装器是与远程数据源通信的库，该库隐藏了特定于源的连接和数据访问详细信息。

使用流程：

(1) 创建外部服务器对象（Foreign Server）：访问外部数据，需先创建外部服务器对象，该对象根据其支持的外部数据包装器所使用的选项集，定义如何连接到特定的远程数据源。

(2) 进行用户映射（User Mapping）：访问远程数据需要对远程数据源进行身份验证。该信息由用户映射提供，该映射可以基于当前的SeaSQL MPP数据库角色提供用户名和密码等附加数据。

(3) 创建外部表（Foreign Table）：创建一个或多个外部表，这些表定义远程数据的结构。外表可以像普通表一样在查询中使用，但外表在SeaSQL MPP数据库服务器中没有存储数据。每当访问外部表时，SeaSQL MPP数据库都会要求外部数据包装器从远程源中获取数据或更新数据。

5.6.1 postgres_fdw

postgres_fdw模块提供外部数据包装器（Foreign Data Wrapper）的功能，SeaSQL MPP数据库通过它可以访问存储在外部的PostgreSQL服务上的数据。

1. 使用要求

要求PostgreSQL为8.3及以上版本

2. 安装扩展

使用如下命令安装postgres_fdw扩展：

CREATE EXTENSION postgres_fdw;

3. 命令选项

· Foreign Server选项

¡ host（必选）

远程PostgreSQL数据库的主机名或IP地址。

¡ port（必选）

远程PostgreSQL数据库的端口号。

¡ dbname（必选）

远程PostgreSQL数据库的名称。

· User Mapping 选项

¡ user（必选）

PostgreSQL数据库的用户名。

¡ password（必选）

PostgreSQL用户的密码。

· Foreign Table选项

¡ schema_name（可选）

此选项可以为外部表指定模式名，和远程服务器上的表做关联。若忽略此选项，远程表本身的模式名将会被外部表使用。

¡ table_name（可选）

此选项可以为外部表指定表名，和远程服务器上的表做关联。若忽略此选项，远程表本身的表名会被外部表使用。

¡ column_name（可选）

此选项可以为外部表列名和远程服务器上的列做关联。若忽略此选项，远程表本身的列名会被外部表使用。

Foreign Table中声明的字段数据类型和其他属性需要与远程表一致。字段名也必须匹配，但可附加column_name选项到单独的字段以显式指定它们在远程表中的命名。

4. 使用示例

(1) 使用CREATE SERVER命令创建一个外部服务器：

CREATE SERVER foreign_server

FOREIGN DATA WRAPPER postgres_fdw

OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

在此命令中：连接到主机为192.83.123.89的PostgreSQL服务器，监听端口为5432；远程服务器上被连接的数据库名为foreign_db。

(2) 使用CREATE USER MAPPING命令定义用户映射，指出在远程服务器上使用的用户：

CREATE USER MAPPING FOR local_user

SERVER foreign_server

OPTIONS (user 'foreign_user', password 'password');

其中：

¡ local_user：指登录本地数据库执行此命令的数据库用户名。

¡ foreign_user：指远程数据库的用户名。

¡ password：为远程数据库foreign_user用户的密码。

(3) 使用CREATE FOREIGN TABLE命令创建一个外部表。

CREATE FOREIGN TABLE foreign_table (

id serial NOT NULL,

data text

)

SERVER foreign_server

OPTIONS (schema_name 'some_schema', table_name 'some_table');

在此命令中：访问远程服务器上名为some_schema.some_table的表，该表的本地名为foreign_table。

(4) 验证本地外部表数据是否与远程数据库一致：

SELECT * FROM foreign_table;

5.6.2 file_fdw

file_fdw模块提供外部数据包装器（Foreign Data Wrapper）的功能，SeaSQL MPP数据库可通过file_fdw模块访问服务器文件系统中的数据文件，数据文件必须采用COPY FROM可读取的格式。目前，file_fdw对此类数据文件的访问权限是只读的。

1. 安装扩展

使用如下命令安装file_fdw扩展：

CREATE EXTENSION file_fdw;

2. 命令选项

使用此包装器创建的外表（foreign table）具有以下选项供用户选择：

· filename

指定待读取的文件。必选项，需填写绝对路径名。

· format

指定文件的格式，与COPY的FORMAT选项相同。

· header

指定文件是否具有标题行，与COPY的HEADER选项相同。

· delimiter

指定文件的分隔符，与COPY的DELIMITER选项相同。

· quote

指定文件的引号字符，与COPY的QUOTE选项相同。

· escape

指定文件的转义字符，与COPY的ESCAPE选项相同。

· null

指定文件的空字符串，与COPY的NULL选项相同。

· encoding

指定文件的编码，与COPY的ENCODING选项相同。

· force_not_null

指定列值可否为空。如果为true，则指定对应列值不能为空。与COPY的FORCE_NOT_NULL选项相同。

· force_null

指定与空字符串匹配的，被双引号包括的空值列值是否返回NULL。如果为true，则指定与空字符串匹配的列的值将返回为NULL，包括被双引号包括的空值（” ”）。若不指定此选项，则只有与空字符串匹配的不带引号的值将返回为NULL。这与COPY的FORCE_NULL选项相同。

file_fdw不支持COPY命令的OIDS和FORCE_QUOTE选项。

3. 使用示例

(1) 创建本地文件/tmp/test.csv，内容如下：

1,aa

2,bb

3,cc

(2) 创建一个外部服务器，命令如下：

CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

(3) 使用CREATE FOREIGN TABLE命令，需要定义表的列、CSV文件名及其格式：

CREATE FOREIGN TABLE file_test (

a int,

bb varchar(32))

SERVER file_server

OPTIONS ( filename '/tmp/test.csv', format 'csv' ,delimiter ',');

(4) 使用如下命令查询外表：

select * from file_test ;

查询结果：

a | bb

---+----

1 | aa

2 | bb

3 | cc

(3 rows)

5.6.3 oracle_fdw

oracle_fdw是一个SeaSQL MPP数据库的扩展，它提供了一个外部数据包装器（Foreign Data Wrapper），可以简单高效地访问Oracle数据库。

1. 使用要求

要求Oracle为10.2及以上版本

2. 环境配置

对SeaSQL MPP集群所有host主机进行以下操作，配置Oralce环境变量。

(1) 进入Oracle官网https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html，下载以下文件：

¡ instantclient-basic-linux.x64-12.1.0.2.0.zip

¡ instantclient-sdk-linux.x64-12.1.0.2.0.zip

¡ instantclient-sqlplus-linux.x64-12.1.0.2.0.zip

(2) 将下载的文件拷贝至编译环境的/opt目录下面，分别执行unzip命令解压文件。

(3) 三个文件的内容均会被解压到/opt/instantclient_12_1/目录下，进入该目录创建软链接。

cd instantclient_12_1/

ln -s libclntsh.so.12.1 libclntsh.so

(4) 将动态库libclntsh.so.12.1路径加入到系统搜索路径：

echo “/opt/instantclient_12_1/”>/etc/ld.so.conf.d/oracle_fdw.conf

ldconfig

3. 安装扩展

使用如下命令安装oracle_fdw扩展：

CREATE EXTENSION oracle_fdw;

4. 命令选项

· FDW选项

¡ nls_lang（可选）

设置Oracle的NLS_LANG环境变量。

NLS_LANG的格式为“language_territory.charset”（例如AMERICAN_AMERICA.AL32UTF8），必须与您的数据库编码相匹配。

· Foreign Server选项

¡ dbserver（必选）

远程Oracle数据库的连接字符串。配置了Oracle客户端后，可以采用Oracle支持的任何形式。

· User Mapping 选项

¡ user（必选）

会话的Oracle用户名。

¡ password（必选）

Oracle用户的密码。

· Foreign Table选项

¡ table（必选）

Oracle表名。此名称必须与Oracle系统目录中的名称完全相同，因此通常仅由大写字母组成。

如果基于任意Oracle查询定义外部表，需将此选项设置为括在括号中的查询，例如：

OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')')

此时，请勿设置schema选项。

如需避免定义在简单查询上的外表的INSERT、UPDATE和DELETE操作，请使用readonly选项。

· schema（可选）

Oracle表模式。此名称必须与Oracle系统目录中的名称完全相同，因此通常仅由大写字母组成。

5. 使用示例

(1) 使用CREATE SERVER创建一个外部服务器。

CREATE SERVER foreign_server

FOREIGN DATA WRAPPER oracle_fdw

OPTIONS (dbserver '//101.8.172.11:1521/orcl');

(2) 使用CREATE USER MAPPING定义用户映射，指定在远程服务器上使用的用户。

CREATE USER MAPPING FOR local_user

SERVER foreign_server

OPTIONS (user 'foreign_user', password 'password');

(3) 使用CREATE FOREIGN TABLE创建一个外部表。

CREATE FOREIGN TABLE foreign_table (

id serial NOT NULL,

data text

)

SERVER foreign_server

OPTIONS (table 'TEST');

其中，访问远程服务器上名为some_schema.some_table的表，该表的本地名为foreign_table。

CREATE FOREIGN TABLE中声明字段的数据类型需要与Oracle数据类型能够自动转换（可参考6. 数据类型）。定义外表时，Oracle表的列按其定义的顺序映射到SeaSQL列。

(4) 验证本地外表数据是否与远程数据库一致：

SELECT * FROM foreign_table;

6. 数据类型

在使用oracle_fdw时，必须使用oracle_fdw可以转换的数据类型定义SeaSQL列，如表5-4所示，这些转换由oracle_fdw自动处理。如果Oracle值超过SeaSQL列的大小（例如，varchar列的长度或最大整数值），运行时将会出现错误。

表5-4 类型对照表

Oracle类型	可选SeaSQL类型
CHAR	char, varchar, text
NCHAR	char, varchar, text
VARCHAR	char, varchar, text
VARCHAR2	char, varchar, text
NVARCHAR2	char, varchar, text
CLOB	char, varchar, text
LONG	char, varchar, text
RAW	uuid, bytea
BLOB	bytea
BFILE	bytea (read-only)
LONG RAW	bytea
NUMBER	numeric, float4, float8, char, varchar, text
NUMBER(n,m) with m<=0	numeric, float4, float8, int2, int4, int8, boolean, char, varchar, text
FLOAT	numeric, float4, float8, char, varchar, text
BINARY_FLOAT	numeric, float4, float8, char, varchar, text
BINARY_DOUBLE	numeric, float4, float8, char, varchar, text
DATE	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH TIME ZONE	date, timestamp, timestamptz, char, varchar, text
TIMESTAMP WITH LOCAL TIME ZONE	date, timestamp, timestamptz, char, varchar, text
INTERVAL YEAR TO MONTH	interval, char, varchar, text
INTERVAL DAY TO SECOND	interval, char, varchar, text
MDSYS.SDO_GEOMETRY	geometry (see "PostGIS support" below)

· 如果将NUMBER转换为布尔值，则0表示false，其他所有值为true。

· 目前不支持NCLOB，因为Oracle无法自动将其转换为客户端编码。

· 如果您需要的转换超出上述范围，请在Oracle或SeaSQL中定义适当的视图。

5.6.4 mysql_fdw

mysql_fdw是一个SeaSQL MPP数据库扩展，提供外部数据包装器（Foreign Data Wrapper）功能，可以简单高效地访问MySQL数据库。

1. 使用要求

要求MySQL为5.0及以上版本。

2. 环境配置

对SeaSQL MPP集群所有host主机进行以下操作，配置MySQL环境变量。

(1) mysql_fdw的编译依赖mysql_config，因此需要首先安装mysql-devel。进入MySQL官网https://dev.mysql.com/downloads/mysql/8.0.html，下载以下文件：

¡ mysql-community-common-8.0.16-2.el6.x86_64.rpm

¡ mysql-community-libs-8.0.16-2.el6.x86_64.rpm

¡ mysql-community-devel-8.0.16-2.el6.x86_64.rpm

(2) 安装文件，三个安装包之前存在依赖关系，必须按如下顺序执行。

rpm -ivh mysql-community-common-8.0.16-2.el6.x86_64.rpm

rpm -ivh mysql-community-libs-8.0.16-2.el6.x86_64.rpm

rpm -ivh mysql-community-devel-8.0.16-2.el6.x86_64.rpm

(3) 将动态库libmysqlclient.so的路径添加到系统搜索路径中，默认安装路径为/usr/lib64/mysql/libmysqlclient.so。

echo “/opt//usr/lib64/mysql/”>/etc/ld.so.conf.d/mysql_fdw.conf

ldconfig

3. 安装扩展

使用如下命令安装mysql_fdw扩展：

CREATE EXTENSION mysql_fdw;

4. 命令选项

· Foreign Server选项

¡ host（可选）

远程MySQL服务器的主机名或IP地址，默认为127.0.0.1。

¡ port（可选）

远程MySQL服务器的端口号，默认为3306。

· User Mapping选项

¡ username（必选）

连接MySQL数据库的用户名。

¡ password（必选）

连接MySQL数据库用户的密码。

· Foreign Table选项

¡ dbname（必选）

待连接的MySQL数据库名。

¡ table_name（可选）

待连接的MySQL数据库的表名。默认与外表名相同。

5. 使用示例

(1) 使用CREATE SERVER创建一个外部服务器。

CREATE SERVER mysql_server

FOREIGN DATA WRAPPER mysql_fdw

OPTIONS (host '101.8.172.13', port '3306');

(2) 使用CREATE USER MAPPING定义用户映射，指出在远程服务器上使用的用户：

CREATE USER MAPPING FOR gpadmin6

SERVER mysql_server

OPTIONS (username 'root', password 'passwd');

(3) 使用CREATE FOREIGN TABLE创建一个外部表。

CREATE FOREIGN TABLE warehouse(

warehouse_id int,

warehouse_name text,

warehouse_created datetime)

SERVER mysql_server

OPTIONS (dbname 'mysql', table_name 'warehouse');

其中，访问远程服务器上名为warehouse的表，该表的本地名为warehouse。

(4) 向MySQL warehouse表中插入数据。CREATE FOREIGN TABLE中声明的字段的数据类型需要与MySQL数据类型能够自动转换。

INSERT INTO warehouse values (1, 'UPS', sysdate());

INSERT INTO warehouse values (2, 'TV', sysdate());

INSERT INTO warehouse values (3, 'Table', sysdate());

(5) 查询外表：

SELECT * FROM warehouse;

查询结果：

warehouse_id | warehouse_name | warehouse_created

--------------+----------------+--------------------

1 | UPS | 29-SEP-14 23:33:46

2 | TV | 29-SEP-14 23:34:25

3 | Table | 29-SEP-14 23:33:49

5.6.5 json_fdw

json_fdw模块提供外部数据包装器（Foreign Data Wrapper）的功能，SeaSQL MPP数据库可以通过json_fdw模块访问服务器的文件系统中存储的JSON格式数据文件，且支持数组类型、嵌套字段和异构文档的分析查询。

1. 使用要求

· 仅支持只读方式访问本地JSON格式文件。

· 仅支持每行包括一个JSON文档的文件，不支持跨多行的对象。

2. 安装扩展

使用如下命令安装json_fdw扩展：

CREATE EXTENSION json_fdw;

3. 命令选项

使用此包装器创建的外表（foreign table）具有以下选项：

· filename

指定待读取的JSON文件。必选项，需为绝对路径名。

· max_error_count

错误输出前允许忽略的最大错误行数，默认为0。

4. 表模式约定

· 使用点分隔符引用JSON文档中的嵌套字段。

例如，在JSON文档中定义为”review”:{“rating”:5}的字段在外表模式中声明为”review.rating”。必须保留”review.rating”的引号，因为包含点的标识符在Postgres中无效。

· 在读取时定义外表模式。

如果除”review.rating”外有其他要查询的字段，例如”review.votes”，则只需添加列名称并开始查询数据即可。支持为相同的JSON文件创建多个表模式，并通过多个表模式进行查询。

· json_fdw假设文本数据可以是异构的。

如果要查询列，并且该字段在文档中不存在，或者字段的数据类型与声明的列类型不匹配，则json_fdw将该特定字段视为null。

5. 使用示例

(1) 使用如下命令下载JSON格式样例文件。

wget http://examples.citusdata.com/customer_reviews_nested_1998.json.gz

(2) 创建一个外部服务器

CREATE SERVER json_server FOREIGN DATA WRAPPER json_fdw;

(3) 使用CREATE FOREIGN TABLE命令，定义表的列、JSON文件名及其格式。

CREATE FOREIGN TABLE customer_reviews

(

customer_id TEXT,

"review.date" DATE,

"review.rating" INTEGER,

"product.id" CHAR(10),

"product.group" TEXT,

"product.title" TEXT,

"product.similar_ids" CHAR(10)[]

)

SERVER json_server

OPTIONS (filename '/home/citusdata/customer_reviews_nested_1998.json.gz');

(4) 查询外表（条件查询），对比查询结果与数据源中数据是否一致。

SELECT

customer_id, "review.rating", "product.id", "product.title"

FROM

customer_reviews

WHERE

customer_id ='A27T7HVDXA3K2A' AND

"product.title" LIKE '%Dune%' AND

"review.date" >= '1998-01-01' AND

"review.date" <= '1998-12-31';

(5) 查询外表（聚合查询），对比查询结果与数据源中数据是否一致

SELECT

width_bucket(length("product.title"), 1, 50, 5) title_length_bucket,

round(avg("review.rating"), 2) AS review_average,

count(*)

FROM

customer_reviews

WHERE

"product.group" = 'Book'

GROUP BY

title_length_bucket

ORDER BY

title_length_bucket;

5.6.6 redis_fdw

redis_fdw模块提供外部数据包装器（Foreign Data Wrapper）的功能，通过redis_fdw模块SeaSQL MPP数据库可以简单高效的访问Redis数据库。

1. 使用要求

要求Redis为2.8及以上版本。

2. 安装扩展

使用如下命令安装redis_fdw扩展：

CREATE EXTENSION redis_fdw;

3. 命令选项

· Foreign Server选项

¡ address（可选）

Redis Server的主机名或IP地址，默认为127.0.0.1。

¡ port（可选）

Redis Server的监听端口，默认为6379。

· User Mapping 选项

¡ password（可选）

Redis Server的密码，默认为空，表示无密码。

· Foreign Table选项

¡ database（可选）

Redis数据库的ID，默认为0。

¡ tabletype（可选）

可选值：'hash'、'list'、'set'或'zset'，默认为none，表示仅查找标量值。

¡ tablekeyprefix（可选）

获取与指定key前缀匹配的数据。默认为none

¡ tablekeyset（可选）

获取与指定key集合匹配的数据。默认为none

¡ singleton_key（可选）

获取单一命名对象的所有值。默认为none，不使用单一命名对象。

Tablekeyprefix、tablekeyset和singleton_key三个选项，仅能同时指定一个。

4. 使用示例

(1) 创建一个外部服务器

CREATE SERVER redis_server

FOREIGN DATA WRAPPER redis_fdw

OPTIONS (address '127.0.0.1', port '6379');

(2) 创建用户映射

CREATE USER MAPPING FOR PUBLIC

SERVER redis_server

OPTIONS (password 'secret');

(3) 使用CREATE FOREIGN TABLE命令创建外表，需要定义表的列，指定Redis数据库ID。

CREATE FOREIGN TABLE redis_db0 (key text, val text)

SERVER redis_server

OPTIONS (database '0');

(4) 在Redis中插入测试数据。

set 123 aaa

set 456 bbb

(5) 查询外表中的数据。

postgres=# select * from redis_db0 ;

key | val

-----+-----

123 | aaa

456 | bbb

(2 rows)

5.6.7 hdfs_fdw

hdfs_fdw模块是一个SeaSQL MPP数据库扩展，提供外部数据包装器（Foreign Data Wrapper）的功能，通过hdfs_fdw模块可以简单高效的访问Hadoop HDFS。

1. 环境配置

对SeaSQL MPP集群所有host主机进行以下操作，配置HDFS环境变量。

(1) 下载Hive客户端驱动：

¡ hadoop-common-2.7.1.2.3.4.0-3485.jar

¡ hive-jdbc-1.2.1.2.3.4.0-3485-standalone.jar

(2) 安装驱动到各个节点的/usr/local/lib//hive1.2-driver目录。

(3) 配置动态库libjvm.so所在路径

gpconfig -c hdfs_fdw.jvmpath -v "'/usr/local/jdk/jre/lib/amd64/server/'" –skipvalidation

(4) 配置hdfs客户端驱动路径

gpconfig -c hdfs_fdw.classpath -v "'/usr/local/seasql/lib//HiveJdbcClient-1.0.jar:/usr/local/lib/hive1.2-driver/hadoop-common-2.7.1.2.3.4.0-3485.jar:/usr/local/lib/hive1.2-driver/hive-jdbc-1.2.1.2.3.4.0-3485-standalone.jar'" --skipvalidation

(5) 在Master节点执行以下命令，重启数据库。

gpstop -raf

2. 安装扩展

使用如下命令安装hdfs_fdw扩展：

CREATE EXTENSION hdfs_fdw;

3. 命令选项

· Foreign Server选项

¡ host（可选）

远程Hive Thrift Server或者 Spark Thrift Server的主机名或IP地址，默认为127.0.0.1。

¡ port（可选）

远程Hive Thrift Server或者 Spark Thrift Server的端口号，默认为10000。

· User Mapping 选项

¡ username（可选）

连接Hive Server2的用户名。

¡ password（可选）

连接Hive Server2的密码。

· Foreign Table选项

¡ dbname（必选）

待连接的Hive Matastore数据库名。

¡ table_name（可选）

待连接的Hive Matastore数据库的表名。默认与外表名相同。

4. 使用示例

(1) Hive数据准备

a. 下载weblogs_parse数据，链接如下：

http://wiki.pentaho.com/download/attachments/23531451/weblogs_parse.zip?version=1&modificationDate=1327096242000

b. 使用以下命令将下载的weblogs_parse.txt文件加载到HDFS中：

su - hdfs

hdfs dfs -mkdir /weblogs

hdfs dfs -mkdir /weblogs/parse

hdfs dfs -put weblogs_parse.txt /weblogs/parse/part-00000

hdfs dfs -cp /weblogs/parse/part-00000 /user/hcat/warehouse/weblogs/

c. 使用beeline客户端连接HiveServer2。

./beeline

!connect jdbc:hive2://localhost:10000 'hdfs' '' org.apache.hive.jdbc.HiveDriver

d. 在Hive中创建表，命令如下：

CREATE TABLE weblogs (

client_ip STRING,

full_request_date STRING,

day STRING,

month STRING,

month_num INT,

year STRING,

hour STRING,

minute STRING,

second STRING,

timezone STRING,

http_verb STRING,

uri STRING,

http_status_code STRING,

bytes_returned STRING,

referrer STRING,

user_agent STRING)

row format delimited

fields terminated by '\t';

e. 加载数据到表中。

load data inpath '/user/hcat/warehouse/weblogs/part-00000' into table weblogs;

(2) 在SeaSQL MPP中使用weblogs表。

a. 使用CREATE SERVER创建一个外部服务器。

CREATE SERVER hdfs_server

FOREIGN DATA WRAPPER hdfs_fdw

OPTIONS (host '101.12.32.226');

b. 使用CREATE USER MAPPING定义用户映射，指出在远程服务器上使用的用户。

CREATE USER MAPPING FOR gpadmin SERVER hdfs_server OPTIONS (username 'hdfs', password '');

c. 使用CREATE FOREIGN TABLE创建一个外表。

CREATE FOREIGN TABLE weblogs

(

client_ip TEXT,

full_request_date TEXT,

day TEXT,

Month TEXT,

month_num INTEGER,

year TEXT,

hour TEXT,

minute TEXT,

second TEXT,

timezone TEXT,

http_verb TEXT,

uri TEXT,

http_status_code TEXT,

bytes_returned TEXT,

referrer TEXT,

user_agent TEXT

)

SERVER hdfs_server

OPTIONS (table_name 'weblogs');

d. 查询外表中的数据。

SELECT client_ip IP, count(*) FROM weblogs GROUP BY IP HAVING count(*) > 5000;

ip | count

-----------------+-------

13.53.52.13 | 5494

683.615.622.618 | 13505

14.323.74.653 | 16194

322.6.648.325 | 13242

363.652.18.65 | 10561

361.631.17.30 | 64979

325.87.75.36 | 6498

325.87.75.336 | 6500

(8 rows)

5.7 平台扩展框架

SeaSQL MPP支持在PXF扩展框架的帮助下访问外部数据，当前仅支持访问HDFS、HBase或Hive上的外部数据。

PXF暂不支持访问开启Kerberos认证的集群内的数据。

5.7.1 配置PXF服务

以下操作需要在数据库集群的所有节点上均进行配置。

(1) 进入$GPHOME/pxf目录下唯一文件的名称，即为PXF版本号。

(2) 进入$GPHOME目录下修改greenplum_path.sh文件，将pxf/bin/添加到path环境变量中并设置PXF_CONF环境变量

修改以下环境变量：

PATH=$GPHOME/bin:$GPHOME/madlib/bin:$PYTHONHOME/bin:$JAVA_HOME/bin:$PATH:$GPHOME/pxf/pxf-gpdb7.0.0-5.14.1-SNAPSHOT-0/pxf/bin

其中pxf-gpdb7.0.0-5.14.1-SNAPSHOT-0为PXF版本号

添加以下配置项：

PXF_CONF=$GPHOME/pxf/pxf_servers_conf

export PXF_CONF

(3) 执行source $GPHOME/greenplum_path.sh使配置生效。

(4) 执行pxf init命令初始化PXF服务，此时会在$PXF_CONF下生成以下目录。

(5) 如果需要通过PXF访问多个外部数据，可以在$PXF_CONF/servers下创建对应的服务目录，此处以hp3目录为例。

(6) 自行下载待访问外部集群的配置文件，上传到创建的hp3目录下。以下文件是通过MPP访问HDFS、Hive、HBase所需的配置文件。

需要检查以上配置文件是否是使用了域名，如果使用需要转换成对应的IP或者将域名和IP的映射添加到/etc/hosts文件中。

(7) 执行pxf start命令启动pxf服务。

5.7.2 加载pxf_fdw组件

通过psql命令登录SeaSQL MPP数据库，执行create extension pxf_fdw命令，加载pxf_fdw扩展组件。

5.7.3 访问HDFS使用示例

(1) 在外部待访问的HDFS中，修改core-site.xml配置文件的configuration标签，添加自定义访问HDFS的用户（以ssadmin用户为例）并重启受影响的服务。

…………………………………….

<name>hadoop.proxyuser.ssadmin.groups</name>

</property>

<name>hadoop.proxyuser.ssadmin.hosts</name>

</property>

………………………..

</configuration>

(2) 在HDFS中添加示例数据，示例数据如下：

-sh-4.1$ cat test.dat

xx|yy

aa|bb

cc|dd

-sh-4.1$ hdfs dfs -put test.dat /user/

-sh-4.1$

-sh-4.1$ hdfs dfs -chown -R ssadmin:ssadmin /user/test.dat

-sh-4.1$

(3) 通过psql命令登录SeaSQL MPP数据库，执行以下命令创建访问HDFS的服务。

create server hdfs_hdp foreign data wrapper hdfs_pxf_fdw options(config 'hp3')

其中：

此处config配置项hp3为5.7.1 (5)章节在servers下创建的hp3目录。

(4) 执行以下命令创建user mapping

create user mapping for ssadmin server hdfs_hdp;

(5) 执行以下命令创建外部表，表结构需要与HDFS中一致

CREATE FOREIGN TABLE hdfs_text

(

name varchar(20),

type varchar(20)

) SERVER hdfs_hdp

OPTIONS ( resource '/user/test.dat',format 'test' , delimiter '|');

(6) 执行查询语句，查看链接到SeaSQL MPP中的外部表。

5.7.4 访问HBase使用示例

(1) 在外部待访问的HBase中，修改hbase-site.xml配置文件，添加自定义访问HBase的用户（此处以ssadmin用为例），并重启受影响的服务。

………….

<name>hbase.superuser</name>

<value>hbase,root,hdfs,ssadmin</value>

</property>

………………..

(2) 在HBase中添加示例数据。

a. 执行hbase shell访问集群

$ hbase shell

hbase(main):001:0>

b. 添加示例数据，示例数据如下所示：

hbase(main):> create 'order_info', 'product', 'shipping_info'

put 'order_info', '1', 'product:name', 'tennis racquet'

put 'order_info', '1', 'product:location', 'out of stock'

put 'order_info', '1', 'shipping_info:state', 'CA'

put 'order_info', '1', 'shipping_info:zipcode', '12345'

put 'order_info', '2', 'product:name', 'soccer ball'

put 'order_info', '2', 'product:location', 'on floor'

put 'order_info', '2', 'shipping_info:state', 'CO'

put 'order_info', '2', 'shipping_info:zipcode', '56789'

put 'order_info', '3', 'product:name', 'snorkel set'

put 'order_info', '3', 'product:location', 'warehouse'

put 'order_info', '3', 'shipping_info:state', 'OH'

put 'order_info', '3', 'shipping_info:zipcode', '34567'

(3) 通过psql命令登录SeaSQL MPP数据库，执行以下命令创建访问HBase的服务。

create server hbase_hdp foreign data wrapper hbase_pxf_fdw options(config ‘hp3’)

其中：

此处config配置项hp3为5.7.1 (5)章节在servers下创建的hp3目录。

(4) 执行以下命令创建user mapping

create user mapping for ssadmin server hbase_hdp

(5) 执行以下命令创建外部表，表结构需与HBase中一致。

create foreign table orderinfo_hbase

(

“product:name” varchar,

“shipping_info:zipcode” int)

server hbase_hdp options(resource ‘order_info’);

(6) 执行查询语句，查看连接到SeaSQL MPP中的外部表。

5.7.5 访问Hive使用示例

(1) 在外部待访问的Hive中，修改hiveserver2-site.xml配置文件，将hive.security.authorization.enabled配置项修改为false，使ssadmin用户可以访问Hive中的数据，并重启受影响的服务。

…………………………..

<name>hive.security.authorization.enabled</name>

<value>false</value>

</property>

……………………….

</configuration>

(2) 在Hive中添加示例数据。

a. 创建文本文件。

$ vi /tmp/pxf_hive_datafile.txt

b. 添加数据到文本文件中。

Prague,Jan,101,4875.33

Rome,Mar,87,1557.39

Bangalore,May,317,8936.99

Beijing,Jul,411,11600.67

San Francisco,Sept,156,6846.34

Paris,Nov,159,7134.56

San Francisco,Jan,113,5397.89

Prague,Dec,333,9894.77

Bangalore,Jul,271,8320.55

Beijing,Dec,100,4248.41

c. 将对应的文件上传到Hadoop中。

hadoop fs -put /tmp/pxf_hive_datafile.txt /tmp/

d. 修改文件权限,使用hdfs用户执行以下命令。

hdfs dfs -chown -R ssadmin:ssadmin /tmp/pxf_hive_datafile.txt

e. 使用ssadmin用户登录Hive，密码为空。

f. 创建表并载入数据。

CREATE TABLE sales_info_ssadmin (location string, month string,

number_of_orders int, total_sales double)

ROW FORMAT DELIMITED FIELDS TERMINATED BY ','

STORED AS textfile;

LOAD DATA INPATH '/tmp/pxf_hive_datafile.txt' INTO TABLE sales_info_ssadmin;

(3) 通过psql命令登录SeaSQL MPP数据库，执行以下命令创建访问Hive的服务。

create server hive_hdp foreign data wrapper hive_pxf_fdw options(config 'hp3')

其中：

此处config配置项hp3为5.7.1 (5)章节在servers下创建的hp3目录。

(4) 执行以下命令创建user mapping

create user mapping for ssadmin server hive_hdp

(5) 执行以下命令创建外部表，表结构需与Hive中一致。

create foreign table hive_hdp

(location text,

month text,

number_of_orders int,

totals_sales float8)

server hive_hdp

options(resource 'sales_info_ssadmin'); //resource为Hive中待访问的表名

(6) 执行查询语句，查看连接到SeaSQL MPP中的外部表。

6 SeaSQL MPP Monitor

SeaSQL MPP Monitor（简称“Monitor”）是数据库的监控管理组件，它以图形化界面的方式来展示集群的运行状态。使用Monitor可以监控集群数据库的性能、资源等信息，方便用户监管数据库。

Monitor进入方式如下：

在云操作系统中，选择[集群列表]页签，单击某集群对应的<控制台>按钮，可直接进入Monitor首页。

· Monitor中含联机帮助，单击页面右上角的帮助按钮，弹出窗口中提供各功能的详细说明、操作指导以及注意事项等，可帮助用户更好的使用Monitor。

· 表6-1仅对各功能进行概括说明，以便用户快速了解Monitor的主要功能，关于各功能的详细说明请参见Monitor的联机帮助。

表6-1 Monitor主要功能说明

功能	描述
首页	在Monitor首页可以查看数据库概况、SQL查询情况、系统CPU/内存的使用情况等信息
监控信息	在[监控信息]页面可以查看数据库的历史查询、系统性能（CPU/内存）等信息
查询监控	在[查询监控]页面可以查看数据库最近1小时的SQL查询排队、运行时长等信息
实例详情	在[实例详情]页面可以查看数据库实例概况以及数据库实例详细信息，包括实例角色、状态、节点类型等信息，并进行实例修复、主备切换等操作
日志查询	在[日志查询]页面可以查看数据库的历史操作日志
会话信息	在[会话信息]页面可以查看客户端的相关连接信息，包括客户端名称、客户端IP以及连接时间等
资源组信息	在[资源组信息]页面可以查看资源组的配置和使用情况【说明】设置资源组信息可以限制该资源组中Role执行查询的数量，指定共享CPU资源的使用优先级，将Role分配到合适的资源组，方便管理员可以有效的控制查询数量，避免系统超负荷运行
数据库对象	在[数据库对象]页面可以查看当前集群中的所有数据库对象，包括数据库、模式、表等
告警设置	在[告警设置]页面可以配置事件告警规则和设置接收告警事件的邮箱服务器
配置信息	在[配置信息]页面用户可自定义配置客户端的访问权限以及身份验证方式，并可以执行新增、编辑或删除配置等操作
数据脱敏	在[数据脱敏]页面可自定义数据脱敏规则，并静态/动态启用脱敏规则，对数据库中的真实数据进行改造并提供测试使用；还可对脱敏规则进行管理

7 集群扩容

7.1 集群扩容

· 建议在集群空闲状态下进行扩容操作，否则可能导致集群扩容失败。

· 扩容分为实例扩容和主机扩容这两种方式，可根据需要任选其一。

· 实例扩容：当Segment节点资源富裕时，通过实例扩容可以提高资源的利用率。

· 主机扩容：当数据库集群的容量、计算能力等资源出现瓶颈时，可以通过扩容主机节点增加数据库集群的整体容量，提高计算能力。

· 实例扩容和主机扩容完成之后都需要进行数据重分布操作，详情请参见7.2 数据重分布。

当数据库的容量，计算能力等资源不能满足需要时，可以对集群进行扩容操作。

(1) 在SeaSQL MPP服务管理页面的左侧导航树中选择[集群管理]，进入集群管理页面。

(2) 在集群管理页面，选择[集群列表]页签，单击某集群对应的<扩容>按钮，并在弹窗中进行确定后会进行集群健康度检查。若集群健康度不满足要求时页面会弹出相应的错误提示。

(3) 若集群健康度满足要求时则会弹出扩容窗口。此时可根据需要选择实例扩容或主机扩容，如图7-1所示。根据提示配置对应参数项的值，其中：

¡ 重分布：实例扩容或主机扩容后都需要对数据进行重分布操作。若选择开启<重分布>按钮表示扩容完成后后台会自动进行重分布；若选择不开启<重分布>则需要在集群扩容完成后，在集群列表中手动单击某集群对应的<重分布>按钮进行重分布操作。

¡ 实例扩容：选择[实例扩容]页签，可根据需要输入扩容的数据库实例数。实例数必须为大于当前实例数的偶数，且最大只能允许12个实例。

· 数据库实例是单个Segment节点上运行同一个查询时启动的计算存储单元。

· 可以根据Segment服务器的硬件条件来配置Segment节点上的数据库实例数，建议单个实例的内存配置在32GB以上，CPU配置在4核以上。例如某Segment服务器的硬件配置为256G 32核，通过min(256GB/32GB=8,32/4=8)得出计算结果为8，那么在该Segment节点上配置的数据库实例数需小于等于8。

¡ 主机扩容：选择[主机扩容]页签，可根据需要选择扩容的主机节点，扩容主机时应至少选择2个主机节点。

图7-1 集群扩容

7.2 数据重分布

· 在集群列表中，对于数据状态显示为“未重分布”的集群可以执行重分布操作。

· 由于数据重分布操作耗时较长，强烈建议在业务空闲时间进行数据重分布操作。

实例扩容或主机扩容后均需要对集群数据进行重分布操作。

(1) 在SeaSQL MPP服务管理页面的左侧导航树中选择[集群管理]，进入集群管理页面。

(2) 在集群管理页面，选择[集群列表]页签，单击某集群对应的<重分布>按钮并在弹窗中进行确定后，即可进行数据重分布操作。

8 开发指南

8.1 jdbc连接数据库示例

JDBC连接数据库操作步骤如下：

(1) 从PostgreSQL官网（https://jdbc.postgresql.org/）下载最新的JDBC工具并作为第三方jar包添加到程序外部库的引用路径下。

(2) 使用URL指定要连接的数据库。

使用JDBC时，由URL（统一资源定位符）指定一个数据库。可以采用如下形式定义URL：

jdbc:postgresql:database

jdbc:postgresql:/

jdbc:postgresql://host/database

jdbc:postgresql://host/

jdbc:postgresql://host:port/database

jdbc:postgresql://host:port/

其中：

¡ host：服务器的主机名。默认为localhost。要指定IPv6地址，参数host必须使用方括号括起例如：jdbc:postgresql://[::1]:5740/accounting

¡ port：服务器正在侦听的端口号。默认为PostgreSQL标准端口号5432，对于SeaSQL MPP要指定为5434

¡ database：数据库名称

(3) 连接数据库。

· 标准连接参数

可以使用DriverManager.getConnection()方法，从JDBC Connection获取实例。

Connection db = DriverManager.getConnection(url, username, password);

· 非标准连接参数

JDBC还支持许多其他属性，这些属性可用于指定PostgreSQL特有的其他驱动程序行为。可以在连接URL或其他Properties对象参数中指定这些属性到DriverManager.getConnection。例如：

String url = "jdbc:postgresql://localhost/test";

Properties props = new Properties();

props.setProperty("user","fred");

props.setProperty("password","secret");

Connection conn = DriverManager.getConnection(url, props);

String url = "jdbc:postgresql://localhost/test?user=fred&password=secret ";

Connection conn = DriverManager.getConnection(url);

其中：

¡ user：代表其建立连接的数据库用户

¡ password：数据库用户的密码

1. JDBC连接数据库示例

下面是使用JDBC连接数据库的代码示例，获取表test.dev的所有数据行，test为用户创建的Schema，可根据实际情况替换该参数：

import java.sql.*;

public class javaTest {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

Class.forName("org.postgresql.Driver");

String url = "jdbc:postgresql://101.8.100.41:5434/postgres?currentSchema=test";

String username = "gpmon";

String password = "gpmon";

Connection conn = DriverManager.getConnection(url, username, password);

String ssql = "SELECT id,name from test.dev";

Statement pre2 = conn.createStatement();

ResultSet rs2 = pre2.executeQuery(ssql);

ResultSetMetaData rsmd2 = rs2.getMetaData();

int columns=rsmd2.getColumnCount();

while(rs2.next())

{

for(int i=1;i<=columns;i++)

{

System.out.print(rs2.getString(i));

System.out.print(" ");

}

System.out.println();

}

8.2 odbc连接数据库示例

ODBC连接数据库操作步骤如下：

(1) 从PostgreSQL官网（https://www.postgresql.org/ftp/odbc/versions/src/）下载最新的ODBC驱动包。同时还需要从unixodbc官网（www.unixodbc.org ）下载安装UnixODBC安装包

(2) 安装完成之后，查看驱动配置 cat /etc/odbcinst.ini

[PostgreSQL]

Description = ODBC for PostgreSQL

Driver = /usr/lib/psqlodbcw.so

Setup = /usr/lib/libodbcpsqlS.so

Driver64 = /usr/lib64/psqlodbcw.so

Setup64 = /usr/lib64/libodbcpsqlS.so

FileUsage = 1

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib/libmyodbc5.so

Setup = /usr/lib/libodbcmyS.so

Driver64 = /usr/lib64/libmyodbc5.so

Setup64 = /usr/lib64/libodbcmyS.so

FileUsage = 1

(3) 配置DSN

[seasql]

Description = Test to gp

Driver = PostgreSQL

Database = postgres

Servername = 127.0.0.1

UserName = ssadmin

Password = passwd

Port = 5434

ReadOnly = 0

其中：

¡ Database：数据库名

¡ Servername：主机名或主机IP

¡ UserName：数据库用户名

¡ Password：数据库用户密码

¡ port：数据库端口号

(4) 连接数据库

echo "select count(*) from pg_class"|isql seasql

8.3 合并分区示例

· 分区表索引需要自己创建，SQL脚本不会对索引进行维护

· 在执行分区表的分割、合并时，会对表加exclusive级别的锁，影响数据导入速度

· 可根据实际的业务需求，选择分区表的合并、分割的时间段（可以是当前月，也可以是非当前月）

在数据库中，过多的子分区表会增加数据库的维护成本。针对大表分区情况，为了加快查询数据，会在子分区上创建索引。但是当入库数据量比较大时，带索引入库会非常影响数据库的性能。

例如，一个大表按照月进行范围分区，当每月的数据量也比较大时，此时为了加快查询，会在每个月的子分区上创建索引，但是外部又在不停的进行大数据量的入库操作（例如每天超过100W条的记录）时。就会出现系统IO很高的情况。

为解决以上问题，将当前月（系统时间）的子分区再按照天进行子分区的分割，只在当天的子分区上不进行创建索引，从而解决带索引入库的情况。

但是，子分区的分割会导致分区表的大量增加，为了解决该问题，在当前月初将上一个月的分区再进行合并。从而解决子分区过多的情况，同时将本月的月分区表按天自动化分区，减少运维工作量。

合并分区示例的操作步骤如下：

(1) 创建表分割函数partition_split（需要执行以下完整代码）

· 表分割函数仅限于按月进行范围分区使用。

· 如果脚本中有出现失败异常退出的情况发生，整个事务进行回退。

· 使用过程中search_path被修改为了传入的i_schemaname。

· schema和table名称不能包含特殊字符，不包含空格。

· 分区名称必须是p1...default_p。

\echo partition_split

drop function if exists partition_split(varchar, varchar, varchar);

create or replace function partition_split

(

i_schemaname in varchar,

i_tablename in varchar,

i_datetime in varchar,

o_retcode out integer,

o_desc out varchar

)

returns record as

$body$

declare

v_tmpint bigint ;

v_day bigint ;

v_daynum bigint ;

v_curmonth varchar(32) ;

v_tablename varchar(255) ;

v_schemaname varchar(255) ;

v_datetime varchar(255) ;

v_str varchar(1024) ;

v_tmpstr varchar(32) ;

v_tmpstrb varchar(32) ;

v_tmpstre varchar(32) ;

v_recordtmp record ;

v_pname varchar(32) ;

v_pnametmpb varchar(32) ;

v_pnametmpe varchar(32) ;

v_ptablename varchar(32) ;

begin

o_retcode := 0;

o_desc := 'success';

v_tablename := trim(i_tablename);

v_schemaname:= trim(i_schemaname);

v_datetime := trim(i_datetime);

v_curmonth := substr(to_char(to_date(v_datetime,'yyyymmdd'),'yyyymmdd'),1,6);

v_daynum := to_char((to_date(v_datetime,'yyyymmdd') + interval '1 month' - to_date(v_datetime,'yyyymmdd')), 'yyyymmdd')::int;

--check schema whether exists

select count(1) into v_tmpint from pg_catalog.pg_namespace where nspname = v_schemaname;

if v_tmpint <> 1 then

o_retcode := -1;

o_desc := 'Schema not exists!';

return;

end if;

--set the search_path

--v_str := 'set search_path to '||v_schemaname;

--execute v_str;

--check table whether exists

select count(1) into v_tmpint from pg_catalog.pg_class c, pg_catalog.pg_namespace n

where n.oid = c.relnamespace and c.relname = v_tablename and n.nspname = v_schemaname;

if v_tmpint <> 1 then

o_retcode := -2;

o_desc := 'Table not exists!';

return;

end if;

--check datetime format

select length(v_datetime) into v_tmpint;

if v_tmpint <> 6 then

o_retcode := -3;

o_desc := 'Datetime format is error!';

return;

end if;

--chech whether current month

select substr(v_datetime,1,6) into v_tmpstr;

if v_tmpstr <> v_curmonth then

o_retcode := -4;

o_desc := 'Split success, but not current month!';

--return;

end if;

--find the partition under the datetime

v_tmpint := 0;

for v_recordtmp in (select to_char(substr(partitionrangestart,2,10)::date,'yyyymmdd') as p_datetime,partitionname,partitiontablename from pg_partitions

where tablename = v_tablename and schemaname = v_schemaname and trim(partitionrangestart) <> '') loop

if substr(v_recordtmp.p_datetime,1,6) = v_datetime then

v_pname := v_recordtmp.partitionname;

v_ptablename := v_recordtmp.partitiontablename;

v_tmpint := v_tmpint + 1;

end if;

if v_tmpint > 1 then

o_retcode := -5;

o_desc := 'Has more than one partition in this month!';

return;

end if;

end loop;

if v_tmpint = 0 then

o_retcode := -6;

o_desc := 'Has no partition in this month!';

return;

end if;

--lock table

v_str := 'lock '||v_schemaname||'.'||v_tablename||' in exclusive mode';

execute v_str;

--drop the index on the partition

for v_recordtmp in (select c.relname from pg_class a,pg_index b,pg_class c,pg_namespace n where a.oid = b.indrelid and b.indexrelid = c.oid and a.relname = v_ptablename

and n.oid = c.relnamespace and n.nspname = v_schemaname ) loop

v_str := 'drop index if exists '||v_schemaname||'.'||v_recordtmp.relname||' cascade';

execute v_str;

raise notice 'drop %.% over', v_schemaname, v_recordtmp.relname;

end loop;

--split this partition

v_tmpstr := v_pname;

for v_day in 2 .. v_daynum loop

v_tmpstrb := '';

v_tmpstre := '';

if v_day < 10 then

v_tmpstrb := v_curmonth||'0'||v_day::varchar;

else

v_tmpstrb := v_curmonth||v_day::varchar;

end if;

if v_day < 11 then

v_tmpstre := v_curmonth||'0'||(v_day - 1)::varchar;

else

v_tmpstre := v_curmonth||(v_day - 1)::varchar;

end if;

v_pnametmpb := v_tmpstr||'_'||v_tmpstre;

v_pnametmpe := v_tmpstr||'_'||v_tmpstrb;

v_str := 'alter table '||v_schemaname||'.'||v_tablename||' split partition '||v_pname||' at('''||v_tmpstrb||''') into (partition '||v_pnametmpb||', partition '||v_pnametmpe||')';

--raise notice '%', v_str;

execute v_str;

v_pname := v_pnametmpe;

end loop;

--over

exception when others then

--rollback;

o_retcode := -10000;

o_desc := 'execute partition_split function failed!';

raise exception '(%)', sqlerrm;

return;

end;

$body$

LANGUAGE plpgsql;

(2) 创建合并分区函数partition_merge。

· 合并函数仅限于按照月进行range分区使用。

· 如果脚本中有出现失败异常退出的情况发生，整个事务进行回退。

· schema和table名称不能包含特殊字符，不包含空格。

· 分区名称必须是p1...default_p。

· 创建合并之后的索引，需要单独在其他地方创建。

\echo partition_merge

drop function if exists partition_merge(varchar, varchar, varchar);

create or replace function partition_merge

(

i_schemaname in varchar,

i_tablename in varchar,

i_datetime in varchar,

o_retcode out integer,

o_desc out varchar

)

returns record as

$body$

declare

v_tmpint bigint ;

v_numint bigint ;

v_str varchar(1024) ;

v_tmpstr varchar(32) ;

v_tablename varchar(255) ;

v_schemaname varchar(255) ;

v_datetime varchar(255) ;

v_tablenametmp varchar(255) ;

v_pname varchar(255) ;

v_starttime char(8) ;

v_endtime char(8) ;

v_syslastmonth char(6) ;

v_nextmonth char(6) ;

v_curmonth char(6) ;

v_daynum bigint ;

v_recordtmp record ;

begin

o_retcode := 0;

o_desc := 'sucess';

v_tablename := trim(i_tablename);

v_schemaname := trim(i_schemaname);

v_datetime := trim(i_datetime);

v_syslastmonth := substr(to_char(now() - interval '1 month','yyyymmdd'),1,6);

v_nextmonth := substr(to_char(to_date(v_datetime,'yyyymmdd') + interval '1 month','yyyymmdd'),1,6);

v_curmonth := substr(to_char(to_date(v_datetime,'yyyymmdd'),'yyyymmdd'),1,6);

v_starttime := v_curmonth||'01';

v_endtime := v_nextmonth||'01';

raise notice 'merge starttime is %', v_starttime;

raise notice 'merge endtime is %', v_endtime;

--check schema

select count(1) into v_tmpint from pg_catalog.pg_namespace where nspname = v_schemaname;

if v_tmpint <> 1 then

o_retcode := -1;

o_desc := 'Schema not exists!';

return;

end if;

--set the search path

--v_str := 'set search_path to '||v_schemaname;

--execute v_str;

--check table

select count(1) into v_tmpint from pg_catalog.pg_class c, pg_catalog.pg_namespace n

where n.oid = c.relnamespace and c.relname = v_tablename and n.nspname = v_schemaname;

if v_tmpint <> 1 then

o_retcode := -2;

o_desc := 'Table not exists!';

return;

end if;

--check datetime format

select length(v_datetime) into v_tmpint;

if v_tmpint <> 6 then

o_retcode := -3;

o_desc := 'Datetime format is error!';

return;

end if;

--chech whether the last month

select substr(v_datetime,1,6) into v_tmpstr;

if v_tmpstr <> v_syslastmonth then

o_retcode := 0;

o_desc := 'Merger success, But not last month!';

--return;

end if;

--find the partition under the datetime

select count(1) into v_tmpint from pg_partitions where tablename = v_tablename and schemaname = v_schemaname and trim(partitionrangestart) <> ''

and substr(to_char(substr(partitionrangestart,2,10)::date,'yyyymmdd'),1,6) = v_datetime;

if v_tmpint <= 1 then

o_retcode := -5;

o_desc := 'Only one partition or no partition in this month!';

return;

end if;

--create temp table

v_tablenametmp := v_tablename||v_syslastmonth||'_tmp';

v_str := 'drop table if exists '||v_schemaname||'.'||v_tablenametmp||' cascade';

execute v_str;

v_str := 'create table '||v_schemaname||'.'||v_tablenametmp||' (like '||v_schemaname||'.'||v_tablename||') with (appendonly=true, orientation=column)';

execute v_str;

--lock table

v_str := 'lock '||v_schemaname||'.'||v_tablename||' in exclusive mode';

execute v_str;

--insert table

v_tmpint := 0;

for v_recordtmp in (select partitiontablename from pg_partitions

where tablename = v_tablename and schemaname = v_schemaname and trim(partitionrangestart) <> '' and substr(to_char(substr(partitionrangestart,2,10)::date,'yyyymmdd'),1,6) = v_datetime) loop

v_str := 'insert into '||v_schemaname||'.'||v_tablenametmp||' select * from '||v_schemaname||'.'||v_recordtmp.partitiontablename;

execute v_str;

raise notice 'insert into from %.% over',v_schemaname,v_recordtmp.partitiontablename;

v_str := 'select count(1) from '||v_schemaname||'.'||v_recordtmp.partitiontablename;

execute v_str into v_numint;

v_tmpint := v_tmpint + v_numint;

end loop;

--check result

v_str := 'select count(1) from '||v_schemaname||'.'||v_tablenametmp;

execute v_str into v_numint;

if v_tmpint <> v_numint then

o_retcode := -6;

raise notice 'The rownum in %.% not equal to %.%', v_schemaname, v_tablename, v_schemaname, v_tablenametmp;

o_desc := 'rownum in temp table is error';

return;

end if;

--drop partition table

for v_recordtmp in (select partitionname,partitiontablename from pg_partitions

where tablename = v_tablename and schemaname = v_schemaname and trim(partitionrangestart) <> '' and substr(to_char(substr(partitionrangestart,2,10)::date,'yyyymmdd'),1,6) = v_datetime) loop

v_pname := v_recordtmp.partitionname;

v_str := 'alter table '||v_schemaname||'.'||v_tablename||' drop partition if exists '||v_recordtmp.partitionname||' cascade';

execute v_str;

raise notice 'drop partition %.% over',v_schemaname, v_recordtmp.partitiontablename;

end loop;

--add new partition

if length(v_pname) = 11 then

v_pname := substr(v_pname,1,2);

elsif length(v_pname) = 12 then

v_pname := substr(v_pname,1,3);

elsif length(v_pname) = 13 then

v_pname := substr(v_pname,1,4);

elsif length(v_pname) = 14 then

v_pname := substr(v_pname,1,5);

elsif length(v_pname) = 15 then

v_pname := substr(v_pname,1,6);

else

o_retcode := -7;

raise notice 'Partition name % is too long', substr(v_pname,1,7);

o_desc := 'Partition name is too long';

return;

end if;

v_str := 'alter table '||v_schemaname||'.'||v_tablename||' split default partition start('''||v_starttime::date||''') inclusive end('''||v_endtime::date||''') exclusive into (partition '||v_pname||', partition default_p)';

execute v_str;

--exchange partition

v_str := 'alter table '||v_schemaname||'.'||v_tablename||' exchange partition '||v_pname||' with table '||v_schemaname||'.'||v_tablenametmp||'';

execute v_str;

--drop tmp table

v_str := 'drop table '||v_schemaname||'.'||v_tablenametmp;

execute v_str;

--over

exception when others then

o_retcode := -10000;

o_desc := 'execute partition_merge function failed!';

raise exception '(%)', sqlerrm;

return;

end;

$body$

LANGUAGE plpgsql;

(3) 合并分区测试

drop table if exists test_p;

create table test_p

(

id1 integer,

id2 integer,

id3 date

) with(appendonly=true, orientation=column) distributed by(id1)

partition by range(id3)

(

partition p start('2010-01-01'::date) end ('2020-12-31'::date) every('1 month'::interval),

default partition default_p

);

alter table test_p split partition p_104 at('20180802'::date) into (partition p_104_20180801, partition p_104_20180802);

alter table test_p split partition p_104_20180802 at('20180803'::date) into (partition p_104_20180803, partition p_104_20180804);

alter table test_p split partition p_104_20180804 at('20180804'::date) into (partition p_104_20180805, partition p_104_20180806);

select * from partition_merge('public','test_p','201808');

select * from partition_split('public','test_p','201809');

9 最佳实践

9.1 数据类型选取原则

· 类型一致。表间连接中用到的列必须使用相同的数据类型。如果数据类型不同，SeaSQL MPP数据库会动态地转换其中一列的数据类型，以正确地比较数据值。

· 使用最小空间的数据类型

¡ 通过选择最有效的数据类型存储数据，可以增加数据库容量和改进查询执行效率。

¡ 使用TEXT或者VARCHAR而不是CHAR，降低使用的存储空间。

¡ 使用能容纳数据的最小数字数据类型，可以节省存储空间。例如，对于适合于INT或SMALLINT的数据，不要使用BIGINT。

9.2 堆存储追加优化存储选择

堆存储是默认模型，并且是PostgreSQL为所有数据库表使用的模型。

· 频繁进行UPDATE、DELETE以及单个INSERT操作的表和分区适合使用堆存储

· 并发执行UPDATE、DELETE以及INSERT操作的表和分区适合使用堆存储

· 初始装载后，很少被更新，且后续只会以批操作执行插入的表和分区适合使用追加优化存储

· 不要在追加优化表上执行单个INSERT、UPDATE或者DELETE操作。

· 追加优化表时，可以执行并发的批量INSERT操作，但不可以执行并发的批量UPDATE或者DELETE操作。

· 追加优化存储模型适合用于一次装载、很少更新且频繁进行分析查询处理的大型表，但不适合频繁更新的表。

9.3 索引选择

在SeaSQL MPP数据库中通常不建议使用索引。在SeaSQL MPP数据库中，顺序读取是一种比较高效的方法，因为每个Segment都含有一张表的一部分数据，并且所有Segment都并行工作来读取数据。SeaSQL MPP数据库支持bitmap、btree、gin、gist、spgist的索引类型。

1. 索引使用原则

(1) 返回非常大的数据集的场景下，使用索引并不是很有效，但对于精确查询或者返回较小的结果集，索引可以提高性能。

(2) 创建索引后，请进行表分析（ANALYZE 表名）。便于优化器做信息统计，在选择查询方案的时候会考虑走索引而不是全表扫描。

(3) 索引是有成本的，可以尝试创建索引和不创建索引，进行查询性能的比较。如果发现创建索引没有改善查询性能，请删除索引。

(4) 批量入库前最好删掉索引。向带索引的表中导入大量数据，会占用大量资源，严重影响其它操作（例如查询）。建议先删掉索引并且在数据装载完成后重建索引，这常常比直接向带索引的表中导入大量数据更快。

(5) 避免在频繁更新的列上建立索引，在被频繁更新的列上建立索引会增加该列被更新时所要求的写操作数据量。

(6) 选择性的创建Btree索引，一个列中具有的不同值的数量除以表中总行数得到的比值为索引选择度。例如，如果一个表有1000行并且一个列中有800个可区分的值，则该索引的选择度为0.8，这种情况下可以使用Btree索引。

(7) 索引以idx作为前缀标识，不能与表同名。在长度允许的情况下，最好能标识出该索引对应的表名和字段名。

(8) 如果建分区之后，还想再提高查询效率可以继续创建索引，但是索引列必须不同于分区列。在分区表上创建索引会导致较小的Btree，其性能比未分区表上的Btree更好。

2. 索引使用示例

例如，在一张1000万行数据的表中过滤1行数据。

(1) 创建索引前，耗时750多毫秒。

(2) 在id列上创建btree索引后，耗时只有3毫秒左右。

9.4 分区选择原则

· 只分区大型表，不要分区小型表。

· 优先选择范围分区而不是List分区。

· 当查询包含表的使用不可变操作符（例如=、<、<= 、>、>=、<>以及between and）时，查询规划器才能有选择地扫描分区表。

· 选择性扫描会识别STABLE和IMMUTABLE函数，但是不识别查询中的VOLATILE函数。例如：date > CURRENT_DATE之类的WHERE子句会导致查询规划器选择性地扫描分区表，但time > TIMEOFDAY的WHERE子句却不行。

需要通过检查查询的EXPLAIN计划来验证查询是否选择性地扫描分区表（分区被消除）。

· 不要使用默认分区。默认分区总是会被扫描，且在很多情况下会导致性能下降。

· 不要在相同的列上对表分区和分布。

· 不要使用多级分区。不推荐使用子分区，因为通常子分区包含很少的数据或者不包含数据。为了性能、可扩展性以及客观性，请在分区扫描性能和总体分区数量之间作出平衡。

· 不要对列式存储使用太多分区。

· 考虑负载并发性，为所有并发查询打开和扫描相同的分布数。

9.5 行存列存选择

· 对于一次装载、很少更新且频繁进行分析查询处理的大型表适用于列存表。即OLAP应用场景较多的时候选择列式表。

· 对于宽表中，大部分情况只查询其中几个字段，并对某些字段进行统计分析的时候选择列表。

· 对于UPDATE、DELETE以及单个INSERT操作比较频繁的场合适合行存表，对于窄表（只有几个列的表）尽量选择行表。即OLTP应用较多的场景选择行存。

· 经常需要使用所有列或者大部分列的查询，请选择行存。

9.6 压缩表

使用压缩表的优势：

· 可以节约存储空间，列存压缩表空间占用远小于普通的heap表空间。

· 查询列很少的时候，无需读取其它的列，IO消耗少。

· 追加写速度快。

· 有些表的字段有规律或者重复比较多的情况下，压缩表不但会省空间而且会加快查询速度。

9.7 分布键选取原则

· 尽量选择经常需要JOIN的列。

¡ 当关联键和分布键一致时，可以在Segment中完成JOIN，不需要重分布或者广播小表。

¡ 当关联键和分布键不一致时，则需要重分布不一致的表或者广播小表，带来额外的开销。除此之外，group by、window function会造成数据重分布。

· 尽量选择分布均匀的列或者多列，若选择的分布列值分布不均匀，可能会导致数据倾斜。实际应用中，如果单列不能达到分布均匀的时候建议选用多列进行联合分布，以达到均匀分布的目的。

· 尽量选择高并发查询的条件列。如果数据经常被高并发的键值或离散查询，可以将查询条件的列作为分布列，这样不需要连接和查询所有的 Segment，可以大大提高并发能力。

10 常见问题解答

10.1 SeaSQL MPP运维类问题

10.1.1 数据节点和主节点故障恢复

1. Standby Master异常修复

SeaSQL MPP数据库Master节点具有高可用的功能，当Master节点出现故障时Standby Master节点会激活成Master保证数据库集群正常工作，此时集群状态显示为“Standby Master异常”。

修复方式：

(1) 在Monitor左侧导航树中选择[实例详情]，进入实例详情页面。

(2) 原故障Master节点修复后，在实例详情页面下的数据库实例列表中，在表头操作的下拉菜单中选择<实例修复>按钮，数据库将自动对Standby Master节点进行修复，如图10-1所示。

图10-1 Standby Master异常修复

2. 实例异常修复

当Segment节点上有实例状态为Down时，集群状态显示为“实例异常”。

修复方式：

(1) 在Monitor左侧导航树中选择[实例详情]，进入实例详情页面。

(2) 在实例详情页面下的数据库实例列表中，在表头操作的下拉菜单中选择<实例修复>按钮，数据库将自动尝试启动处于Down状态的实例，如图10-2所示。

图10-2 实例异常修复

3. 角色异常修复

当Primary实例处于Available状态，且Mirror实例处于Active状态时，此时需要进行角色转换，集群状态显示为“角色异常”。

修复方式：

(1) 在Monitor左侧导航树中选择[实例详情]，进入实例详情页面。

(2) 在实例详情页面下的数据库实例列表中，在表头操作的下拉菜单中选择<主备切换>按钮，数据库将自动启动Primary实例，并将Mirror实例置于Avaliable状态，如图10-3所示。

图10-3 角色异常修复

4. 集群整体故障恢复

当集群整体断电、机房停电导致数据库服务不可用或某Segment instance主备节点均失效导致数据库服务不可用时，可使用如下方式修复。

方法1：集群管理页面启动集群

(1) 在云服务管理页面的左侧导航树中选择[集群管理]，进入集群管理页面。

(2) 在集群管理页面，选择[集群列表]页签，单击某集群对应的<启动>按钮并在弹窗中进行确认后，即可启动该集群，如图10-4所示。

图10-4 在集群管理页面启动集群

方法2：如果集群管理界面启动失败，可以后台手工启动。

所有节点操作系统启动成功后，切换至Master节点的ssadmin用户下，执行如下操作：

(1) 若集群所有节点全部断电，需执行gpstart命令，重启数据库。

(2) 若集群仅部分节点断电，需执行如下操作：

a. 以维护模式启动Master节点，命令如下：

gpstart -m

b. 停止集群，命令如下：

gpstop -M fast -a

c. 启动集群，命令如下：

gpstart -v -a

说明，启动集群后，若有Segment实例故障，详情请参见2. 实例异常修复。

如果以上方式均无法恢复故障节点，请联系技术支持工程师。

10.1.2 普通用户创建内部表无法copy本地数据至该内部表中怎么办？

现象描述：

普通用户创建内部表，使用copy命令将本地数据加载至该内部表时，会出现如下图所示的报错信息。

解决方法：

copy本地数据到内部表命令必须在超级用户ssadmin下使用。在ssadmin用户下创建表，才可利用copy命令加载本地数据文件。

图10-5 copy命令报错信息

10.1.3 数据库变慢了，怎么解决？

引发数据库变慢的原因可能有很多，以下列出一些常见的排除基本项，可以作为问题定位的思路。

(1) 查看常用查询表的统计信息是否正确，如果不正确需要进行analyze <tablename>的操作，重新收集统计信息。查询统计信息是否过期的命令如下：

¡ 查看表的具体信息

select * from pg_class where relname = 'tablename';

¡ 查看表中字段的具体信息

select * from pg_statistic where starelid = 'tablename'::regclass;

说明：一般情况下，只需要查看pg_class中的reltuples字段和实际字段是否相差很大，如果相差较大，则说明需要进行analyze操作了。

(2) 频繁进行DML操作表的垃圾回收

由于SeaSQL MPP采用了MVCC的方式实现RDBMS的ACID，所以对表进行更新和删除之后，会查询表膨胀的现象。如果出现了数据库变慢的情况，可以对频繁进行更新和删除的表进行vacuum操作，同时对pg_catalog下的系统表也进行一次vacuum操作，命令如下：

vacuum pg_catalog.pg_class;

vacuum public.user_table;

说明：vacuum是一个相对比较耗时的操作，需要在系统比较空闲的时候进行。对于系统表，每天凌晨3:30进行vacuum操作；对于用户自定义表，可以通过一下命令判断是否需要进行vacuum操作：

¡ 查看表是否出现膨胀：

select * from gp_toolkit.ss_bloat_tables;

(3) 查看表分区个数

针对于常见业务，为了解决大表查询慢的问题，很多时候会采用分区表的方式去解决。该方法虽然可以解决上述问题，但是也会带来数据库对分区表进行维护的压力，特别是分区表过多的情况下，会影响查询的效率。因此，一般情况下分区表个数不要超过4000个。查询分区表总个数的SQL命令如下：

select count(1) from pg_partitions;

(4) 查看数据是否出现倾斜

SeaSQL MPP是一种分布式数据库，正常情况下，对于一个大表中的数据，应该均匀的分布在每个节点上，这样才能达到并行存储、计算的效果。但是，有些时候由于分布键的选取不合适，导致出现数据倾斜的现象。查询数据是否倾斜的SQL命令如下：

postgres=# select gp_segment_id, count(1) from table_name group by 1;

gp_Segment_id | count

---------------+-------

2 | 108

1 | 96

3 | 108

0 | 92

如果出现数据倾斜的现象，需要重新选择分布键，SQL命令如下：

postgres=# alter table tablename set distributed by (colname);

ALTER TABLE

其中，colname可以使用组合分布键，中间用逗号分隔，例如：colname1,colname2……

(5) 索引的使用

对于查询过滤条件比较固定的SQL语句，可以按照where过滤条件添加索引，加快查询效率。

SeaSQL MPP中的索引主要有两种：Btree index和bitmap index，其中Btree index主要用于选择性较高的查询，bitmap index主要用于选择性较低的查询，例如性别、颜色等。

【说明】：对一个表创建索引之后，需要对该表进行一次analyze操作。

(6) 使用explain查看执行计划

如果以上几条都没有问题，可以使用explain analyze对SQL进行执行计划的查看，SQL命令如下：

explain analyze select relname from pg_class where relname = 'xx';

10.1.4 SQL查询慢，怎么解决？

SQL语句查询比较慢的原因有很多，常见解决方法有以下几种：

1. 确认集群状态

(1) 确认集群状态是否正常，命令如下：

gpstate -c

2. 确认物理环境负载

(1) 确认网络负载情况，命令如下：

netstat

(2) 确认磁盘IO负载情况，命令如下：

iotop

(3) 确认CPU负载情况，命令如下：

top

(4) 确认磁盘使用量，命令如下：

df -h

(5) 确认内存使用量，命令如下：

free –h

3. 确认优化器是否打开

(1) 优化器默认是关闭的，优化器对复杂查询优化效果明显，简单查询可能反而会降低查询速度

，确认优化器是否打开，命令如下：

show optimizer;

(2) 建议对于复杂查询，在查询语句前打开优化器，命令如下：

set optimizer=on;

(3) 建议对于简单查询，在查询语句前关闭优化器，命令如下：

set optimizer=off;

4. 确认数据是否分布均匀

数据是否分布均匀，对SQL查询速度影响很大。确认数据是否分布均匀，命令如下：

select count(1),gp_segment_id from <tablename> group by gp_segment_id;

<tablename>为待检测数据是否均匀的表名称。

5. 确认是否有索引

表是否有索引对其SQL查询速度影响很大，确认表是否有索引，操作如下：

(1) 查看某个表是否有索引

\d+ table_name

(2) 查看所有索引

\di

6. 查看执行计划并进行表分析

通过查看执行计划找出耗时大的地方，进行SQL优化。

analyze explain <SQL语句>

其中<SQL语句>为待检查SQL执行慢的SQL语句。

10.1.5 数据导入慢怎么解决？

1. 确认数据导入方式

(1) Insert方式：单条导入，速度很慢

(2) copy方式：可以直接导入外部数据文件，速度比insert快

(3) gpfdist方式：并行导入，速度最快

(4) gpload方式：对gpfdist的封装，速度跟gpfdist一样

2. 确认是否有带索引入库

带索引入库会严重影响数据导入速度，建议尽量避免索引入库。

查看要导入的表中是否创建了索引，有的话需先删除索引，待数据导入完成之后，再重建索引，命令如下：

\d+ tablename

3. 查看表结构和SQL语句

确认表结构和SQL语句是否有逻辑上不合理的地方，当表结构中存在有超长的字段的时候会影响数据的导入速度。SQL语句的逻辑中有锁表或锁等待的情况时，也会导致数据的导入速度变慢。

不同款型规格的资料略有差异, 详细信息请向具体销售和400咨询。H3C保留在没有任何通知或提示的情况下对资料内容进行修改的权利!

热门推荐

热门推荐

H3C服务器

HPE服务器

热门推荐

H3C存储

HPE存储

热门推荐

商用台式机

商用笔记本

商用显示器

配件

热门推荐

热门推荐

智能终端

百业灵犀

技术解决方案

行业解决方案

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

专业安全服务

安全运营服务

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

热门推荐

公司刊物

加入我们

国家/地区

H3C SeaSQL MPP 用户手册(云形态) E5134-5W112

目录

01-正文

1 SeaSQL MPP简介

1. 主节点（Master）

2. 数据节点（Segment节点）

3. Interconnect

2 快速入门

2.1.1 连接数据库

1. psql

2. pgAdmin连接

3 使用指南

1. 用户与权限

2. 用户权限控制

4. 行级安全策略

5. 列级访问控制

1. 创建数据库

2. 创建数据库示例

3.2.2 创建表

1. 创建表

2. 创建表示例

3.2.3 选择分布策略

1. 默认分布键参数设置

2. Hash分布

2. List分区表

3.2.5 索引

1. 创建索引

3.3.1 gpstart（启动集群）

1. 用法

2. gpstart使用示例

3.3.2 gpstop（停止集群）

1. 用法