基础原理

架构

OushuDB 是一款云原生架构的分布式数据库，具备高并发、高吞吐和高性能的弹性计算能力
多主节点、多虚拟计算集群和多虚拟存储集群
每一层级的节点，都可以水平扩展
存储与计算完全分离

digraph 架构{
node [fontsize=25]
架构-> {虚拟存储集群,虚拟计算集群,主节点,客户端}   
}

主节点

digraph 架构{
node [fontsize=25]
主节点-> {资源管理器,查询解析器,优化器,容错服务,事务管理,权限安全,调度服务}   
}

资源管理器：资源管理器负责整个集群的资源管理。资源管理模块能够感知集群健康状态，动态识别集群节点。协调并发查询之间的资源分配，避免查询之间相互影响，提升系统的可用性
查询解析器：负责解析查询，并检查语法及语义。最终生成查询树传递给优化器。
分析器：针对语法树结构进行检查，对语法树进行数据补充或者必要的优化改进，在 OushuDB 中分析器和查询解析器是合并的。
优化器：负责接受查询树，生成查询计划。针对一个查询，可能有数亿个可能的等价的查询计划，但执行性能差别很大。优化器的作用是找出优化的查询计划。
容错服务：负责检测哪些节点可用，哪些节点不可用。不可用的机器会被排除出资源池。
事务管理：提供分布式事务的控制能力，OushuDB 能够提供基于多版本的事务隔离机制。
权限安全：负责控制用户的行为，对用户权限进行约束，保障数据库访问安全。
调度器：优化器优化完查询以后，查询派遣器派遣计划到各个节点上执行，并协调查询执行的整个过程。查询派遣器是整个并行系统的粘合剂。

计算层

计算层可以被划分为多个虚拟计算集群（Virtual Compute Cluster），相互独立，可动态弹性扩展。
每个虚拟计算集群下可以划分多个虚拟计算集群实例，
每个虚拟计算集群实例又包括多个计算节点
每个计算节点上会运行一个 OushuDB 子节点（Segment），可动态添加，即可接受查询任务。
子节点实现了 OushuDB 的计算，在执行查询时，会在资源容器中启动多个查询执行器（Query Executor），实现对数据的并行处理。计算层支持全新的 SIMD 向量化执行引擎，相比于传统的 MPP 数据库，查询性能可以实现数量级提升。计算层的集群虚拟化能够在物理上进行计算资源隔离，高效隔离执行错误，快速容错，增强计算弹性，集群内的数据共享也解决了数据孤岛问题。

虚拟存储集群（Virtual Storage Cluster）是 OushuDB 的存储层
由存储节点组成的多个可插拔存储集群组成
OushuDB 支持可插拔存储，包括 Magma、HDFS、S3，并支持用户对存储集群按照不同的特性与用途进行集群分组，
这些不同的存储集群是 OushuDB 中的虚拟存储集群。存储集群虚拟化可以支持更大规模的存储
支持多租户的存储资源隔离，并提供全球级别的数据分布和更高的容灾级别

产品特性

存储与计算分离架构
多个主节点处理用户对数据库的连接请求(JDBC/ODBC)，处理用户认证及协调分布式执行计划引擎、数千个计算/存储节点分布式调度服务来完成后续 SQL 查询的处理流程。

分布式执行计划引擎
负责用户端标准 SQL 语句的解析、语意检查、基于代价的查询优化到最终产生分布式的查询任务规划工作。

分布式任务调度引擎 QD
负责执行计划的分发与执行状态跟踪，同时也负责节点间的数据分发。

支持 SIMD 的极速执行引擎
负责将分布式调度引擎转发的逻辑计划转换成物理计划并完成资源配置，启动并控制整个查询计划的执行并将执行结果透过分布式调度引擎传回上层执行节点。

分布式可插拔存储引擎
提供支持 HDFS/S3/本地表存储等多种存储引擎及数据格式（行存、列存）数据的编码、压缩、存储与读写等机制

分布式事务管理
提供全局的数据库读写 ACID 机制， 支持分布式锁， 支持并发读写能力

数据库元数据管理
提供数据对象管理与存储，支持基于 MVCC 的事务处理机制

全局资源管理
提供支持多租户的基于多级资源队列的全局任务资源统一分配

进程架构

Master 节点和 Segment 节点共有

PostMaster 进程
监听用户请求的守护进程，为每个客户端 fork 连接出单独的 postgres 服务。
当 postgres 进程出错时尝试修复；当 Segment 节点的 postgres 挂掉时候会认为节点已经 down 掉了。
Logger 进程
负责收集各个进程的输出并将其写入 pg_log，这里包括成功 log 和出错的 log
Stats collector 进程
统计收集数据的信息，会生成描述数据库、表等统计信息的。stat 文件
Writer 进程
定期将共享内存的数据写到磁盘

Master resource manager 和 Segment resource manager
Master resource manager 负责管理、分配、回收资源，定期查询、接收、处理 Segment 的心跳信息，从而获取整个集群可用的节点；Segment resource manager 是查询本节点的状态，包括查询临时文件是否可读写，发送 Segment 的心跳信息。

Master 节点独有

DFS metadata cache
读取并缓存 block location 信息，从而计算数据的存储，也就是存储数据的这些 block 是如何分布的，因此在生成查询计划时，可以确定去某些节点访问某些数据的速度，从而进一步保证本地读。
**Checkpoint **
负责周期性做 checkpoint 或响应常规的 checkpoint 请求。
**Sequence **
产生序列，也我们通常说的自增长列。

保证 Master 的节点和 Standby 节点元数据信息进行同步

**Wal send server **
负责把 write ahead log 发给 Standby Master
**Syncagent **
负责和 Master 上 wal send server 通信的进程，处理 Master 和 standby 节点状态。

计算引擎及其执行器、优化器

SQL 查询语句流程

优化器

早期数据库的查询优化器通常采用启发式规则进行优化 RBP（Rule Based Optimization），这种优化方式不够准确，往往难以获得最优
的执行计划，而基于代价的优化 CBO(Cost Based Optimization)（P8）则能够针对大多数场景高效筛选出性能最好的执行计划。
因此，包括 OushuDB 在内的高性能数据库引擎往往使用基于代价的优化器。

执行器

执行器是数据库内核最重要的部件之一。提升执行器的性能，会很大程度上提升数据库性能。
提升执行器性能的手段主要有两种技术路线， 一种是向量计算（vectorized execution），另外一种是代码生成（code generation）

OushuDB 使用了向量计算外加 SIMD 优化技术

SIMD

SIMD(single instruction multi-data)，即单指令多数据流，以同步的方式在同一时间内执行同一条指令。相比单指令单数据流（SISD），单指令多数据流一次性获得所有操作数进而加快了运算，充分利用 CPU 性能，特别是数据密集型运算。

OushuDB 新执行器充分利用列式存储 ORC、Magma 存储格式。以 ORC 存储格式举例，ORC 是列式存储，有多种文件压缩方式，并且有着很高的压缩比。文件是可切分（Split）的，因此，OushuDB 中使用 ORC 作为表的文件存储格式，不仅节省存储资源，并且使查询任务的输入数据量减少。ORC 存储格式提供了多种索引，row group index、bloom filterindex。此外，还可以支持复杂的数据结构（比如 Map 等）

新执行器全量支持 TPCH 和 TPCDS，TPCH 比 Greenplum 大约快 5-10 倍比 SparkSQL 3.0 以上的版本要快几十倍。

查询执行过程

第一个橘色的框表示一个查询在 Master 节点上，Master 节点会 fork 出来 QD 进程。
QD 进程会将 query 通过 parser 来生成语法树，通过 planer 来生成查询计划
这个 plan 会 dispatcher 到各个节点上执行。
第二个橘色框表示了在各个节点上的 Executor
各个节点上的 postMaster 会 fork 出来 QE 进程来完成任务
最后将结果返回给 Master。

外部接口

PSQL
JDBC、ODBC
偶数自研的 Lava 平台可以让用户通过 UI 界面操作数据库

Magma

Magma 存储，分布式，高可用，支持事务。
尽管 OushuDB 的数据可以存储在 HDFS，但 HDFS 只支持以追加的方式写入，对表数据的更新和删除操作很不友好，因此开发了 Magma。
Magma 的主要功能
Magma 存储的数据是表数据，是结构化数据，不是简单的 KV。
Magma 支持对表数据做更新和删除操作。
它的高可用是通过 Raft 的协议数据复制来实现的。
它的事务特性是通过多版本来实现的。
它的高性能的是通过行列混合的存储格式来支持 OLAP 高性能查询。
它提供了数据索引功能，可以建立主键索引和非主键索引。
作为分布式的存储，它整合数据预处理技术，提供了便捷的集群扩展、集群容错以及负载均衡。
总结 Magma 的功能，主要包括数据分布、数据清理、事务模型、索引、数据副本、负载均衡。

OushuDB 由 master 和多个 segment 组成，master 负责对 SQL 进行解析，优化生成查询计划。通过 Dispatcher 分发到每个 segment。每个 segment 执行计划的过程中就涉及到对数据的存储和读取。
Magma 通过直接操作本地文件系统，实现了支持表数据的更新和删除操作。
作为分布式存储，Magma 在集群的每个节点上启动 Magma 的进程。整个集群中的多个 Magma 进程组成了 Magma 集群。

HDFS

HDFS 是在一个大规模分布式服务器集群上，对数据分片后进行并行读写及冗余存储。
因为 HDFS 可以部署在一个比较大的服务器集群上，集群中所有服务器的磁盘都可供 HDFS 使用，所以整个 HDFS 的存储空间可以达到 PB 级容量。

对象存储

OushuDB 支持的对象存储类型

1. Amazon Simple Storage Service (简称 S3)
   亚马逊 AWS 服务在 2006 年正式对外推出的云计算对象存储服务。提供行可扩展性、数据可用性、安全性和性能。
2. 腾讯云对象存储（Cloud Object Storage, COS）
   腾讯云提供的一种用于存储海量文件的简单、可拓展、方便获取和分享数据的分布式存储服务，类似于网盘，可以存储任何类型的数据。腾讯云对象存储 COS 是简单、可拓展、方便获取和分享数据的云端存储服务。对象存储 COS 有费用低廉、零运维成本、弹性伸缩、安全可靠等优势

S3 配置和操作举例

$OUSHU_HOME/conf/oushudb/oushudb-site.xml 路径查看和修改配置文件
要配置 S3 存储，需要修改如下字段，将 value 设置为自定义的目录地址。

<property>
<name>hawq_s3_url</name>
<value>s3.cn-north-1.amazonaws.com.cn:80/ccb-test/s3_system</value>
<description>URL for accessing S3.</description>
</property>

配置完 hawq_s3_url 后，需要同步配置 hawq_s3_config 来指定 config 文件的路径，否则启动时会报错

<property>
    <name>hawq_s3_config</name>
    <value>/usr/local/hawq/feature-test/s3/credentials</value>
    <description>The absolute path to the S3 configuration file.</description>
</property>

新增 hawq_s3_config 的注意事项：所有 filespace 共用一个 config 文件如果修改 config 文件路径需要重启如果修改 config 文件内容，需要停掉现有的 active 的连接

操作

外部表

OushuDB 支持 S3 只读外部表以及可写外部表，数据存储格式支持 TEXT/CVS/ORC 三种，支持外表是希望可以操作用户放在 S3 上的数据，支持的功能如下：

1. DDL：CREATE/DROP/RENAME
2. DML：SELECT/INSERT/COPY（新旧执行器）
3. DCL：支持通过 GRANT/REVOKE 给用户授权是否可操作 S3 外表
   只读外表可以指定单个或者多个文件 location，可写表 location 只能指定一个。

单个路径的 S3 外部表–读写均可

create external tasble test_s3_ext_single_location(c1 int)
location(‘s3:// cos.ap-beijing.myqcloud.com/ oushudbtest-1304271688/
cis3test/ dasituer908176m1_86142537/ part1/ / usr/ local/ oushu/ oushudb/
credentials’) format ‘csv’( DELIMITER ‘,’);

多个路径的 S3 外部表—只能创建可读外部表

create readable external table test_s3_ext_multi_location(c1 int)
location(‘s3:// cos.ap-beijing.myqcloud.com/ oushudbtest-1304271688/
cis3test/ dasituer908176m1_86142537/ part2/ config=/ usr/ local/
oushu/ oushudb/ credentials’, ‘s3:// cos.ap-beijing.myqcloud.com/
oushudbtest-1304271688/ cis3test/ dasituer908176m1_86142537/ part3/
config=/usr/local/oushu/oushudb/credentials’) format ‘csv’( DELIMITER
‘,’);

内表

S3 内表数据的控制权完全在 OushuDB，所以相对而言使用 S3 内表会有更好的可控性。
目前 S3 内表支持功能如下：
• 只支持 ORC 的存储格式
• 支持事务（强一致性）：强一致事务要求在任意时刻各节点数据在任意时刻都是一致的
• 支持 Random/Hash 表
• DDL：CREATE/ DROP/ ALTER TABLE (RENMAE/ DROP COLUMN/ ADD COLUMN)⋯/
TRUNCATE
• DML：SELECT/INSERT/COPY（新旧执行器）
• datalocality 元数据缓存
• direct dispatch 优化
• UPDATE/DELETE
内表示例

用户指南

数据定义

创建和管理数据库

查看现有数据库的集合

SELECT datname FROM pg_database;

psql 程序中通过\l 命令

postgres-# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 postgres  | tbase | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
 template0 | tbase | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/tbase         +
           |       |          |            |            | tbase=CTc/tbase
 template1 | tbase | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/tbase         +
           |       |          |            |            | tbase=CTc/tbase
(3 rows)

postgres-# 

创建一个数据库

CREATE DATABASE name;

删除数据库

DROP DATABASE name;

基本概念

CREATE TABLE

CREATE TABLE my_first_table (
first_column text,
second_column integer
);

OushuDB 现在支持多种存储格式：ROW、ORC 和 Magma。
ROW 是按行存储的格式，而 ORC、Magma 是按行列存储的格式。

创建表的命令

# 默认创建的是ROW表
CREATE TABLE rank1 (id int, rank int, year smallint,gender char(1), count int );
# 和上面的创建的表一样， 显式指定存储格式类型
CREATE TABLE rank2 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =row);
# 创建一个snappy压缩的ROW表
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =row, compresstype = snappy);
# 创建一个不压缩的ORC表， 如果不指定压缩类型的话， 默认不压缩。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =orc);
# 创建一个带压缩的ORC表， 需指定压缩类型。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) with (appendonly =true, orientation =orc, compresstype = lz4);
# 创建一个压缩的magma表， magma 内部自动实现了压缩。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int ) format 'magmaap';
# 创建一个有primary key的magma表， magma 内部自动实现了压缩。
CREATE TABLE rank3 (id int, rank int, year smallint,gender char(1), count int，primary key(id) ) format 'magmaap';

DROP TABLE

DROP TABLE my_first_table;

缺省值

没有明确声明缺省值，那么缺省值是 NULL，表示”未知”。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);
CREATE TABLE purchaseHistory (
    purchase_id integer,
    product_no integer,
    time timestamp DEFAULT now()
);

MAGMA 格式的表不支持为列设置缺省值

约束

检查约束

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

列约束也可以写成表约束，但反过来很可能不行

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)    
);

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);
#和列约束一样，我们也可以给表约束赋予名称，方法也相同
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

当约束表达式计算结果为真或 NULL 的时候，检查约束会被认为是满足条件的。因为大多数表达式在含有 NULL 操作数的时候结果都是 NULL ，所以这些约束不能阻止列值为 NULL 。要确保一个列值不为 NULL ，可以使用下面介绍的非空约束。

非空约束

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

一个非空约束总是写成一个列约束，它等效于创建一个检查约束 CHECK (column_name IS NOT NULL)，但在 OushuDB 里，创建一个明确的非空约束效率更高。

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

主键

在 OushuDB 支持的表格式(ROW,ORC,MAGMA) 中，只有 MAGMA 表支持主键约束，而且在创建 MAGMA 表时，可以声明主键列，且主键约束的列里数据类型为非定长时需要将该列放置最后一列。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    PRIMARY KEY (product_no)
) FORMAT 'MAGMAAP';

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
) FORMAT 'MAGMAAP';

如果是 MAGMA 格式的表，是不支持列约束和非空约束的，但支持主键约束。

修改表

增加列

ALTER TABLE products ADD COLUMN description text DEFAULT value;

表中已经存在的行在新增列后，会在该列先填充所给出的缺省值( 注意： ROW 格式的表在添加新列时必须设置缺省值)。

ALTER TABLE products ADD COLUMN description text DEFAULT value CHECK (description <> '');

删除列

ALTER TABLE products DROP COLUMN description;

增加约束

ALTER TABLE products ADD CHECK (name <> ''); 
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

删除约束

ALTER TABLE products DROP CONSTRAINT some_name; 
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

改变列的缺省值

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

改变列的数据类型

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2); 

重命名列

ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表

ALTER TABLE products RENAME TO items;

表分区

查询优化器可以针对分区表进行优化，如果查询只涉及到某些分区，则查询计划只需要扫描这些分区，从而加速查询
如果我们按照日期进行分区的话，我们可以简单的加入分区和删除过期的分区。

OushuDB 支持基于 Range 和 List 的两种分区方式：
• Range 分区：表被一个或者多个关键列分区成”范围”，这些范围在不同的分区里没有重叠。比如依据日期、价格数值范围进行分区。
• List 分区：依据一个值的列表进行分区，比如依据地区列表进行分区。
创建新的分区表使用 CREATE TABLE ⋯PATITION BY

1. 确定分区类型：范围分区 RANGE 或列表分区 List。
2. 选定分区字段：范围分区多为日期、数值类型字段；列表分区多为枚举类型的特定列表；
3. 确定分区级别：OushuDB 支持子分区 SUBPARTITION，可以按照不同维度实现多级分区。

定义 Range 分区

按月的范围分区

CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int)
    DISTRIBUTED BY (city_id)
    PARTITION BY RANGE (logdate)
    (START (date '2020-01-01') INCLUSIVE END (date '2020-02-01') EXCLUSIVE EVERY (INTERVAL '1 month'));

我们来创建一个 sales 表，按照 date 列 Range 分区，从 2008 年到 2009 年每月创建一个分区：

CREATE TABLE sales (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') );

要查看创建的表信息，可以通过\d+ 给出该表的所有信息

CREATE TABLE sales_exp (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date)
( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE ,
    PARTITION Feb08 START (date '2008-02-01') INCLUSIVE ,
    PARTITION Mar08 START (date '2008-03-01') INCLUSIVE ,
    PARTITION Apr08 START (date '2008-04-01') INCLUSIVE ,
    PARTITION May08 START (date '2008-05-01') INCLUSIVE ,
    PARTITION Jun08 START (date '2008-06-01') INCLUSIVE ,
    PARTITION Jul08 START (date '2008-07-01') INCLUSIVE ,
    PARTITION Aug08 START (date '2008-08-01') INCLUSIVE ,
    PARTITION Sep08 START (date '2008-09-01') INCLUSIVE ,
    PARTITION Oct08 START (date '2008-10-01') INCLUSIVE ,
    PARTITION Nov08 START (date '2008-11-01') INCLUSIVE ,
    PARTITION Dec08 START (date '2008-12-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE );

DEFAULT PARTITION

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
PARTITION BY RANGE (year)
( START (2001) END (2008) EVERY (1),
DEFAULT PARTITION extra );

定义 List 分区

CREATE TABLE magma_measurement_city (
    city text,
    logdate date,
    peaktemp int,
    unitsales int
) format 'magmaap'
PARTITION BY LIST (city)
( PARTITION beijing VALUES ('BJ'),
PARTITION shanghai VALUES ('SH'),
PARTITION guangzhou VALUES ('GZ'),
PARTITION shenzhen VALUES ('SZ'),
DEFAULT PARTITION other );

定义多级分区

CREATE TABLE mult_measurement (
    city text,
    logdate date,
    peaktemp int,
    unitsales int
) WITH (APPENDONLY = true, OIDS = FALSE, ORIENTATION = orc)
PARTITION BY RANGE (logdate)
SUBPARTITION BY LIST (city)
    SUBPARTITION TEMPLATE
    (SUBPARTITION beijing VALUES ('BJ'),
    SUBPARTITION shanghai VALUES ('SH'),
    SUBPARTITION guangzhou VALUES ('GZ'),
    SUBPARTITION shenzhen VALUES ('SZ'),
    DEFAULT SUBPARTITION other)
(START (date '2020-01-01') INCLUSIVE END (date '2020-02-01') EXCLUSIVE EVERY (INTERVAL '1 month'));

分区现有表

1. 按照现有大表结构创建一个新的分区表
2. 将现有大表中的数据 load 到新创建的分区表中
3. 删除原来的大表
4. 按照原来大表名称重命名分区表名称

CREATE TABLE measurement_cp (like measurement)
PARTITION BY RANGE (logdate)
(START (date '2020-01-01') INCLUSIVE END (date '2020-04-01') EXCLUSIVE →EVERY (INTERVAL '1 month'));
INSERT INTO measurement_cp SELECT * FROM measurement;
DROP TABLE measurement;
ALTER TABLE measurement_cp RENAME TO measurement;
ANALYZE measurement;

查看你的分区设计

SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank
 FROM pg_partitions
 WHERE tablename='sales';

pg_partition_templates 查看使用子分区模板创建的子分区

select * from pg_partition_templates where tablename = 'mult_measurement_3'

小提醒：
• 数据量达到千万记录以上时使用分区表。数据仓库中的事实表适合作为分区表。对于小于这个数量级的表通常不需要分区。因为系统管理与维护分区的开销会抵消掉分区带来的可见的性能优势。
• 只有当实施了其它优化手段后，响应时间仍然不可接受时，再考虑使用分区。
• 根据分区定义条件，分区条件应尽可能使数据平均划分。例如，将一个大表分成 10 个相等的分区，如果查询条件中带有分区键，那么理论上查询应该比非分区表快将近 10 倍。
• 分区数不要超过 128 个，太多的分区将会减慢管理和维护任务。如检查磁盘使用、集群扩展、释放剩余空间。其次，只有在查询条件可以利用分区消除时，性能才会得到提升。否则，一个需要扫描所有分区的查询会比非分区表还慢。
• 除非必要，少用多级分区。多级分区会使分区文件的数量快速增长。例如，如果一个表按日期和城市做分区，1000 天的 1000 个城市的数据，就会形成 100 万个分区。假设表有 100 列，并且假设表使用面向列的物理存储格式，那么系统为此表需要管理 1 亿个文件。

数据操纵

插入数据

insert into department values(100, ‘CEO 办公室’,’中国’,’北京’);

更新数据

UPDATE department SET d_name = '测试部' WHERE d_id = 103;

删除数据

DELETE FROM department WHERE d_id = 101;

数据查询

SELECT select_list FROM table_expression [sort specification] ;

多表连接

• inner join
• out join
– left join
– right join
– full join
• cross join

数据类型

数值

• 整数类型
• 任意精度
• 浮点数

数字 2016.12 的精度为 6 而标度为 2，是一个类型为 NUMERIC(6, 2) 的数据

字符型

布尔类型

boolean 只能有”true”(真) 或”false”(假) 两个状态，或第三种用 NULL 表示的”unknown”(未知) 状态。
“真”值的有效文本值是：
• TRUE
• ‘yes’
• ‘1’
• ‘true’
• ‘y’
• ‘t’
同理对于”假”，你可以使用下面这些文本值：
• FALSE
• ‘no’
• ‘0’
• ‘false’
• ‘n’
• ‘f’

日期/时间

函数和操作符

逻辑操作符

首先，我们来看下逻辑操作符。
常用的逻辑操作符有三种：
• AND（与）
• OR（或）
• NOT（非）
而 SQL 语言里，除此之外还有第三个值 unknown，它的存储形式为 null，代表着”未知”

比较操作符

数学函数和操作符

日期/时间函数和操作符

字符串函数和操作符

聚合函数

索引

OushuDB 目前只有 Magma 表支持索引，ROW、ORC 表均不支持创建使用索引。

索引类型

CREATE INDEX name_idx ON employee (e_name);
CREATE INDEX d_name_idx ON department (d_id,d_name);
DROP INDEX title_idx;

别名与视图

FROM table_reference AS alias
FROM table_reference alias

视图

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

自定义函数

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
    DECLARE
    declaration;
    [...]
    BEGIN
    < function_body >
    [...]
    RETURN { variable_name | value }
END; 
LANGUAGE lang_name;

事务

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。
我们可以通过 BEGIN TRANSACTION、COMMIT、ROLLBACK 命令来控制事务：

BEGIN;
DELETE FROM department WHERE d_id = 101;
ROLLBACK;

锁

数据库中有两种基本的锁：排它锁（Exclusive Locks）和共享锁（Share Locks）。
如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。

LOCK [ TABLE ]
name
IN
lock_mode
ACCESS SHARE，ROW SHARE，ROW EXCLUSIVE， SHARE UPDATE EXCLUSIVE，SHARE，SHARE ROW EXCLUSIVE，EXCLUSIVE，
ACCESS EXCLUSIVE。

BEGIN;
LOCK TABLE department1 IN ACCESS EXCLUSIVE MODE;

管理指南

启动/停止 OushuDB

启动 OushuDB 有两种方式，一种是通过”oushudb start cluster”命令来启动整个集群，包括 master 和 segment。启动哪些 segment 是由”/hawq-install-path/etc/slaves”中包含的节点确定的。

source /usr/local/hawq/greenplum_path.sh # 设置OushuDB环境变量
oushudb start cluster # 启动整个OushuDB集群

另外一种方式是分别启动 OushuDB master 和 segment。因为 OushuDB master 和 segment 是解耦合的，分别启动 master 和 segment 是可行的。

oushudb start master # 启动master， 指的是启动本地master
oushudb start segment # 启动segment， 指的是启动本地segment

重新启动或者停止 OushuDB 也有两种方式。

# 方式一
oushudb restart cluster # 重启OushuDB集群
oushudb stop cluster # 停止OushuDB集群
# 方式二
oushudb restart master # 重启本机的OushuDB master
oushudb restart segment # 重启本机的OushuDB segment
oushudb stop master # 停止本机OushuDB master
oushudb stop segment # 停止本机OushuDB segment

客户端认证

OushuDB 和 PostgreSQL 兼容，提供多种不同的客户端认证方式，而 pg_hba.conf 就是客户端认证的配置文件，它存放在数据库集群的数据目录里。HBA 的意思是”host-based authentication”，也就是基于主机的认证。在 initdb 初始化数据目录的时候，它会安装一个缺省的 pg_hba.conf 文件。

pg_hba.conf 文件的常用格式是一组记录，每行一条。空白行将被忽略，# 开头的注释也被忽略。一条记录是由若干用空格和/或制表符分隔的字段组成，记录不能跨行存在。

TYPE
表示连接类型，表示允许用哪些方式连接数据库，它允许以下几个值：
• local： 使用本地 unix 套接字。
• host： 使用 TCP/IP 连接（包括 SSL 和非 SSL)，结合“IPv4 地址”使用
IPv4 方式，结合“IPv6 地址”则使用 IPv6 方式。
• hostssl： 只能使用 SSL TCP/IP 连接。
• hostnossl： 不能使用 SSL TCP/IP 连接。

DATABASE
声明记录所匹配的数据库名称。值 all 表明该记录匹配所有数据库，
值 sameuser 表 示如果被请求的数据库和请求的用户同名，则匹配。
值 samerole 表示请求的用户必须是一 个与数据库同名的角色中的成员。
在其它情况里，这就是一个特定的 OushuDB 数据库的名字。
可以通过用逗号分隔的方法声明多个数据库，也可以通过前缀@ 来声明一个包含数据库名的文件。

USER
指定哪个数据库用户或者角色，多个用户名可以通过用逗号分隔。

CIDR-ADDRESS
声明这条记录匹配的客户端机器 IP 地址范围，该地址用标准点分十进制声明并带有 CIDR 掩码长度，可以是 IPv4 地址或 IPv6 地址，可以定义某台主机或某个网段。
172.20.143.89/32 表示一个主机，172.20.143.0/24 表示一个小子网，10.6.0.0/16 表示一个大子网。

IP-ADDRESS IP-MASK
与 CIDR-ADDRESS 一样， 只是写法不同。这种写法不声明掩码的长度， 而是在另
外一个字段里声明实际的掩码。比如，255.0.0.0 表示 IPv4 CIDR 掩码长度 8 ，而
255.255.255.255 表示 CIDR 掩码长度 32 。

METHOD
指定如何处理客户端的认证，也就是认证方式。包括包含选项有： trust、md5、password、
krb5、ident、ldap、pam 等，我们将在下一节介绍一下常用的认证方式。

认证方式

信任认证 trust
ident 认证
口令认证
LDAP 认证
PAM 认证
Kerberos 认证

查看日志

启动日志

OushuDB 的启动日志在/home/gpadmin/hawqAdminLogs 下，可以看到如下文件：
⁃hawq_init_.log
⁃hawq_start_.log
⁃hawq_stop_.log

服务器日志

查看服务器端的日志， 可以在 $GPHOME/ etc/ oushudb-site.xml 中查找
hawq_master_directory 和 hawq_segment_directory 这两个目录对应的值，里面
包含带有 timestamp 的日志文件。
⁃hawq_master_directory/pg_log/hawq-.[csv|log]
⁃hawq_segment_directory/pg_log/hawq-.[csv|log]

虚拟集群

• 虚拟集群是若干个 Segment 的集合
• 每个虚拟集群都是彼此互斥的集合
• 每个虚拟集群只管理其内部的 Segment 资源
• 每个虚拟集群会维护其内部的资源队列
• 每个虚拟集群拥有个性化属性（VCProperty）
• 所有虚拟集群的信息全部持久化存储在元数据表 pg_vcluster 中

虚拟集群的属性

虚拟集群属性（VCProperty）实际上是从以前 GUC 中迁移过来的配置，目前支持以下 7 个属性

DDL 使用

数据库角色和权限

目录

操作角色的语句：

create role db_role1; /–创建角色/ 
drop role db_role1;/–删除角色/ 
select rolename from pg_roles; /–查看所有角色/ 
/du –在命令格式下查看所有角色的命令

create role db_role1 LOGIN; – 创建具有登录权限的角色db_role1 
create role db_role2 SUPERUSER; –创建具有超级用户权限的角色
create role db_role3 CREATEDB; – 创建具有创建数据库权限的角色
create role db_role4 CREATEROLE –创建具有创建角色权限的角色
alter role db_role1 nologin nocreatedb; –修改角色取消登录和创建数据库权限

create user db_user1 password ‘123’; – 创建用户
create role db_user1 password ‘123’LOGIN; – 同上一句等价
drop user db_user1; – 删除用户
alter user db_user1 password ‘123456’; –修改密码
alter user db_user1 createdb createrole; –对用户授权

create user db_user1; –创建用户1 
create user db_user2; –创建用户2 
create role db_role1 createdb createrole; –创建角色1 
grant db_role1 to db_user1,db_user2; –给用户1,2 赋予角色1, 两个用户就拥有了创建数据库和创建角色的权限
revoke db_role1 from db_user1; –从用户1移除角色1，用户不在拥有角色1 的权限

权限

OushuDB 中好多种不同的权限：
SELECT, INSERT, DELETE, TRUNCATE,REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE 和 USAGE 。
适用于特定对象的权限因对象类型不同而不同。

GRANT UPDATE ON department TO fred;
REVOKE ALL ON accounts FROM PUBLIC;

资源队列

资源队列的概念和行为特点

资源队列运行状况检查

集群可用性检查

检查集群节点状态： 查询 gp_segment_configuration，检查 Segment（注册的 role 为’p’的节点）的 status 是否为‘u’, 检查注册的 Segmet 数量是否符合期待。

role 列可能展示的值有：
• m：Master 节点
• p：Segment 节点
• s：Standby 节点
status 列表示此节点的健康状况：
• u：up
• d：down

检查集群节点状态历史

查询 gp_configuration_history， 可确认历史某时段是否发生节点不可用的问题导致资源量的变化。

资源队列定义和运行状态检查

检查资源队列定义：查询 pg_resqueue，可得到每个资源队列的定义和之间构成的树结构。

检查资源队列状态：查询 pg_resqueue_status，可得到每个资源队列的实际使用状况。

SQL 运行资源检查

使用 EXPLAIN ANALYZE 可以观察到资源分配方案相关内容，可以通过 EXPLAIN ANALYZE 最后的输出文本检查 Virtual Segment 的分配状态，尤其需要关注是否得到了足够数量的 Virtual Segment，是否在集群中 Virtual Segment 被均匀分布。

集群监控

常见的状态监控一般包括如下几种：
• 命令行检测 oushudb state
• SQL 检测

SELECT * FROM gp_segment_configuration; 我们可以通过这条命令所查询的表来查看节点的状态

• 数据库连接数检查

select count(1) from pg_stat_activity ; 来查看当前正在活跃任务的情况
select count(1) from pg_stat_activity where current_query<>’’ and
procpid<>pg_backend_pid();


select
datname --数据库名
,procpid --master进程号(该任务在master上ps到的pid)
,sess_id --chawq会话号(该任务在每节点共用的会话号,可以通过ps -ef|grep gpadmin|grep con${sess_id}查询)
,usename --数据库用户名
,waiting --对象等待(t为等待,f为不等待)
,waiting_resource --资源等待(t为等待,f为不等待)
,query_start --活动query开始时间, 同一个事务中一个query结束到下一个query会初始化开始时间
,xact_start --一个事务的开始时间,如果一个事务只有一个query,那么xact_start等于query_start
,application_name --连接客户端名
,current_query --sql内容
from pg_stat_activity --会话信息表
where current_query<>'<IDLE>' --不等于空闲的任务
and procpid<>pg_backend_pid() --不等于自己
and extract(epoch FROM (current_timestamp-'2021-05-23 22:30:00'))>=18000; --大于等于5小时,单位秒,可以自行定义

备份准备

查询当前数据库大小

SELECT sodddatsize FROM hawq_toolkit.hawq_size_of_database WHERE sodddatname=’mydb’;

【数据备份和恢复方法】包括：
通过外部表（External Tables）进行的高性能并行数据导入和导出，例如 gpfdist 外部表或者 hdfs 外部表。外部表允许用户直接查询 OushuDB 外部数据
简单的通过 CREATE Table table_name AS SELECT * FROM ext_table 并行导入数据到 OushuDB 内部表的方法。
最常用的并行加载数据到 OushuDB 的方法是通过基于 gpfdist 的外部表。
gpfdist 是一个 HTTP 服务器，用户可以在同一机器上，或者不同的机器上启动多个 gpfdist 实例，从而可以充分利用多台机器，多个网卡实现大规模并行加载。同时，基于 gpfdist 的外部表也可以支持数据的导出。

另一种是针对小规模数据可以通过 Copy 命令或者 pg_dump 和 pg_restore 来进行串行导入和导出。COPY 命令可以把单个文件加载到 OushuDB，也可以从 OushuDB 中导出数据到单个文件

gpfdist 外部表导入导出数据

导入

使用 gpfdist 进行数据加载通常有四个步骤，分别是：
• 启动 gpfdist 文件服务器
• 把需要加载的数据文件放到 gpfdist 数据目录
• 定义外部表
• 加载数据

gpfdist -d /data1/load-files -p 8081 -l /data1/log1
gpfdist -d /data2/load-files -p 8082 -l /data2/log2

CREATE EXTERNAL TABLE ext_expenses
( name text, date date, amount float4, category text, desc1 text )
LOCATION ('gpfdist://etlhost-1:8081/*', 'gpfdist://etlhost-1:8082/*')
FORMAT 'TEXT' (DELIMITER '|');

CREATE TABLE expenses AS SELECT * FROM ext_expenses;

导出

gpfdist 外部表导出数据一般有以下四个步骤。
• 启动 gpfdist 文件服务器
• 准备导出的表
• 定义外部表
• 导出数据

gpfdist -d /data1/load-files -p 8081 -l /data1/log1
gpfdist -d /data2/load-files -p 8082 -l /data2/log2

CREATE WRITABLE EXTERNAL TABLE expense_out (LIKE myexpenses)
LOCATION ('gpfdist://etlhost-1:8081/sales1.out', 'gpfdist://etlhost-1:8082/sales2.out')
FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')
DISTRIBUTED BY (name);

COPY

COPY (SELECT * FROM country) TO ‘list_countries.copy’;
COPY customers FROM ‘/database/data/test_data.copy’(DELIMITER ‘‘);

pg_dump 和 pg_restore

pg_dump -Ft -f mydb.tar mydb
pg_dump -Fc -Z3 -f mydb.dump mydb --压缩的备份。
pg_restore -d new_db mydb.dump 

编程接口

PL/pgSQL

CREATE FUNCTION populate() RETURNS integer AS $$
DECLARE
-- 声明段
BEGIN
PERFORM my_function();
END;
$$ LANGUAGE plpgsql;

JDBC

public static Connection getConn() {
Connection conn = null;
try {
Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://localhost:5432/postgres";
try {
conn = DriverManager.getConnection(url, "ChangLei", "");
}
catch (SQLException e) {
e.printStackTrace();
}
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
return conn;
}