函数/存储过程
1. 内置的plpgsql，一种类似oracle的plsql的语言
2. 支持的脚本语言有：PL/Lua、PL/LOLCODE、PL/Perl、PL/HP、PL/Python、PL/Ruby、PL/sh、PL/Tcl和PL/Scheme
3. 编译语言有C、C++、JAVA
4. 统计语言PL/R
索引：支持用户歹意的索引访问方法，并且内置B-tree、哈希和GiST索引。特点如下：
1. 可以从后向前扫描
2. 可以创建表达式索引
3. 部分索引
触发器
并发管理（MVCC，多版本并发机制）
规则（RULE）：规则允许我们队由一个查询生成的查询数进行改写
数据类型
1. 任意精度的数值类型
2. 无线长度的文本类型
3. 几何原语
4. IPv4和IPv6类型
5. CIDR块和MAC地址
6. 数组
7. 自定义类型
用户定义对象：索引、操作符（内部操作符可以被覆盖）、聚集函数、域、类型转换、编码转换
继承：表可以相互继承。一个表可以有父表，父表的结构变化会导致子表的结构变化，而对子表的插入和数据更新等也会反映到父表中
其他特性与扩展
1. 二进制和文本大对象存储
2. 在线备份
3. TOAAT（The Oversized-Attribute Storage Technique）用于透明地在独立的地方保存大的数据库属性。当数据超过一定大小的时候，会自动进行压缩以节省空间
4. 正则表达式
5. Slony-I
6. pgcluster
7. Mammoth replicator
8. Bucardo

1.3.2 Greenplum

本质上讲，greenplum 是一个关系型数据库集群，是由多个独立的数据库服务组合成的逻辑数据库。

与oracle RAC的shared-Everything架构不同，greenplum采用Shared-Nothing架构，整个集群由很多个数据节点（Segment Host）和控制节点（master Host）组成，其中每个数据节点上可以运行多个数据库。简单来说，Shared-Nothing是一个分部式的架构，每个节点相对独立。在典型的Shared-Nothing中，每一个节点上所有的资源（CPU，内存，磁盘）都是独立的，每个节点都只有全部数据的一部分，也只能使用本节点的资源。

在greenplum中，需要存储的数据在进入数据库时，将先进行数据分布的处理工作，将一个表中的数据平均分布到每个节点上，并为每个表指定一个分布列（distribute Column），之后便根据Hash来分布数据。基于Shared-Nothing的原则，Greenplum这样处理可以充分发挥每个节点处I/O的处理能力

1.4 greenplum特性及应用场景

1.4.1 特性

支持海量数据存储和处理
高性价比
支持Just In Time BI：通过准实时、实时的数据加载方式，实现数据仓库的实时更新，进而实现动态数据仓库（ADW），基于动态数据仓库，业务用户能对当前业务数据进行BI实时分析（Just In Time BI）
系统易用性
1. 支持主流的sql语法，使用起来十分方便，学习成本低
2. 扩展性好，支持多语言的自定义函数和自定义类型等
3. 提供了大量的维护工具，使用维护起来很方便
4. 在internet上有这丰富的postgreSQL资源供用户参考
支持线性扩展：采用MPP并行处理架构。在MPP结构中增加节点就可以线性提供系统的存储容量和处理能力。
较好的并发支持及高可用性支持除了提供硬件级的Raid技术外，还提供数据库层Mirror机制保护，也剧场将每个节点的数据在另外的节点中同步镜像，单个节点的错误不影响整个系统的使用。对于主节点，还提供Master/Stand by机制进行主节点容错，当主节点发生错误时，可以切换到Stand by节点继续服务。
支持MapReduce：一种大规模数据分析技术。
数据库内部压缩。

1.4.2 greenplum应用场景

greenplum的主要特点：查询速度快、数据装载速度快、批量DML处理快、性能可以随着硬件的添加呈线性增加、拥有非常良好的课扩展性。

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

第二章 greenplum 快速入门

2.1 软件安装及数据库初始化

2.1.1 greenplum 架构

Greenplum Database通过在多个服务器或主机之间分配数据和处理工作负载来存储和处理大量数据。Greenplum数据库是一组基于PostgreSQL 8.2的单独数据库，它们协同工作以呈现单个数据库映像。主服务器是Greenplum数据库系统的入口点。它是客户端连接和提交SQL语句的数据库实例。主设备与系统中的其他数据库实例（称为段）协调其工作，这些实例存储和处理数据。

Master主机与Segment主机对比：

Master主机负责	Segment主机负责
1. 建立与客户端的会话连接和管理	1. 业务数据的存储和存取
2. sql的解析并形成分布式的执行计划	2. 执行由master分发的sql语句
3. 将生成好的执行计划分发到每个Segment上执行	3. 对于master来说，每个Segment都是对等的，负责对应数据的存储和计算
4. 收集Segment的执行结果	4. 每一台机器上可以配置一到多个Segment
5. master不存储业务数据，只存储数据字典	5. 由于每个Segment都是对等的，建议采用相同的机器配置
6. master主机可以一主一备，分布在两台机器上	6. Segment分primary 和mirror两种，一般交错第存放在子节点上
7. 为了提高性能，master最好单独占用一台机器

Master和Segment的关系：

Master和Segment其实都是一个单独的PostgreSQL数据库，每一个都有自己单独的一套元数据字典。

Client一般只能与Master界面进行交互，Client将SQL发给Master，然后Master对SQL进行分析后，再将其分配给所有的Segment进行操作，并且将汇总结果返回给客户端。

2.1.2 环境搭建

greenplum集群介绍

安装linux
数据库存储：linux下建议使用XFS，Solaris下建议使用ZFS；Raid根据需求选择硬Raid或软Raid，需要更大空间则Raid5，性能要求高则Raid1+0
网络（hosts）：所有机器的网络都通，防火墙都是关闭的，使用ping确定所有hostname都通
创建用户及用户组
1. 删除原有用户：
#groupdel gpadmin
# userdel gpadmin
1. 创建新的用户和用户组：
2. #groupadd -g 530 gpadmin
3. #useradd -g 530 -m -d /home/gpadmin -s /bin/bash gpadmin
4. 对文件夹进行赋权，为新用户创建密码:
5. #chown -R gpadmin:gpadmin /home/bash gpadmin
6. #passwd gpadmin
7. Changing password for user gpadmin.
8. New UNIX password:
9. Retype new UNIX password:

2.1.3 greenplum安装

参考数据库安装手册

2.1.4 数据库启动与关闭

1. 启动数据库

gpstart -a

2.关闭数据库

gpstop -s

gpstop –u 可重新加载配置文件

gpstate 查看数据库状态及配置信息

2.1.5 创建数据库

创建测试数据库：

createdatabase testDB -E utf-8

没有设置PGDATABASE这个环境变量时，使用psql进行登陆，默认的数据库是与操作系统用户名一致的，这时候会报错：

[gpadmin@dw-greenplum-l ~]$ psql

psql: FATAL: databasse "gpadmin" does not exist

然后设置（export）环境变量PGDATABASE=testDB，这样就默认testDB数据库：

[gpadmin@dw-greenplum-l ~]$ export PGDATABASE=testDB

[gpadmin@dw-greenplum-l ~]$ psql

psql (8,2,15)

Type "help" for help

testDB=#

查询数据库版本并创建一张表：

testDB=# select version();

testDB=# create table test01(id int primary key,name varchar(128));

2.2 常用运维sql语句

调用plsql命令

psql -d test01 -h master -p 5432 -U gpadmin

创建GP表空间

psql gpdb

create tablespace TBS_DW_DATA filespace gpfsdw;

SET default_tablespace = TBS_DW_DATA;

建表语句：

删除GP数据库

gpdeletesystem -d /gpmaster/gpseg-1 -f

查看segment配置

select * from gp_segment_configuration;

文件系统

select * from pg_filespace_entry;

磁盘、数据库空间

SELECT * FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;

SELECT * FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname;

日志

SELECT * FROM gp_toolkit.__gp_log_master_ext;

SELECT * FROM gp_toolkit.__gp_log_segment_ext;

表描述

/d+ <tablename>

表分析

VACUUM ANALYZE tablename;

表数据分布

SELECT gp_segment_id, count(*) FROM <table_name> GROUP BY gp_segment_id;

表占用空间

SELECT relname as name, sotdsize/1024/1024 as size_MB, sotdtoastsize as toast, sotdadditionalsize as other

FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class

WHERE sotd.sotdoid = pg_class.oid ORDER BY relname;

索引占用空间

SELECT soisize/1024/1024 as size_MB, relname as indexname

FROM pg_class, gp_toolkit.gp_size_of_index

WHERE pg_class.oid = gp_size_of_index.soioid

AND pg_class.relkind='i';

OBJECT的操作统计

SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time

FROM pg_stat_operations

WHERE objname = '<name>';

锁

SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query

FROM pg_locks l, pg_class c, pg_stat_activity a

WHERE l.relation=c.oid

AND l.pid=a.procpid

ORDER BY c.relname;

队列

SELECT * FROM pg_resqueue_status;

查看segment分布

select * from gp_segment_configuration

查看数据存放绝对路径

select * from pg_filespace_entry

查询所有表、视图、序列、索引的原数据信息

Select * from pg_class

2.3 greenplum整体概况

2.3.1 greenplum数据分布

gp是基于开源数据库软件PostgreSQL 8.3 开发的，其大部分语法和数据字典都与PostgreSQL一样，很多工具使用的规范也基本一样

可以说gp将PostgreSQL改造成一个分布式数据库。其中Segment节点都是一个单独的PostgreSQL数据库，Master本身也是一个PostgreSQL数据库

Master本身不储存数据，所有数据拆分保存到每一个节点上，在指定分布键的时候，数据按照分布键的Hash值来分布数据，称为哈希分布。

还以一种分布不用指定分部件，数据随机分布到每一个节点，称作随机分布（也叫平均分布）

2.3.2 基本语法介绍

1. 获取语法介绍

可以使用\h查看gp支持的所有语法

在psql中使用 \h command 可以获取具体命令的语法：

testDB=# \h create view

2.CREATE TABLE

gp建表语句与其他数据库不同的地方：

在gp中建表时需要制定表的分布键
如果表需要用某个字段分区，可以通过partition by将表建成分区表
可以使用like操作创建与like的表一样结构的表，功能类似create table t1 as select * from t2 limit 0

由于gp是一个分布式数据库，数据是分布在每一个节点上的。在gp中有两种数据分布策略：

hash分布。指定一个或多个分布键，计算hash值，并且通过hash值路由到特定的Segment节点上，语法为Distributed by(..)。如果不指定分布键，默认将第一个字段作为分布键。
随机分布，也叫平均分布。数据随机分散在每一个节点中，这样无论数据是什么内容，都可以平均分布在每个节点上，但是在执行SQL的过程中，关联等操作都需要将数据重分布，性能较差。语法为在表字段定义的后面加上Distributed randomly。

下面两个建表语句的执行结果一样，都是以id作为分布键：

在下面的建表语句中指定了多个分布键：

在下面的建表语法中采用了随机分布：

采用随机分布策略的表默认将主键或唯一键作为分布键，因为每一个Segment都是一个单一的数据库，单个的数据库可以保证唯一性，多个数据库节点就无法保证全局的跨库唯一性。故只能按照唯一键分布，同一个值得数据都在一个节点上，以此来保证唯一性

如果指定的分布键与主键不一样，那么分布键会被更改为主键：

在创建表的时候，如果要建一张表结构一模一样的表，可以利用create table like 命令：

使用like创建的表，只是表结构会与原表一模一样，表的一些特殊属性并不会一样，例如压缩、只增（appendonly）等属性。如果不指定分布键，则默认分布键与原表一样

3.select

需要注意的是，gp的数据切分放在所有的Segment上。当从一个表查询数据的时候，Master的数据展现顺序是以Master先接收到的数据的顺序，每个Segment的数据到达Master的顺序是随机的，不是固定的，所以执行SELECT的结果的顺序是随机的，即使表中数据一点变化都没有。这一点跟其他数据库是不一样的

4. create table as 与select into

create table as 可以加入distributed指定分布键，select into 只能使用默认的分布键

5. explain

explain用于查询一个表的执行计划，它在SQL优化的时候经常要用到（详细的执行计划解释参考第五章执行计划详解）

下面代码演示了简单的执行计划的查看方法：

上面的执行计划是一个层次关系，先从最右边开始查看：

数据库先顺序扫描test2表，扫描大概有118单位的消耗，有1667行数据，平均长度为15字节。其中，1667行数据是一个估计值，是一个Segment的数据量，如果数据分布均匀，大概是总数据量除以Segment的个数。由于这个gp集群有6个Segment节点，因此可以推断test2表大概有1万行数据
扫描出test2表，并且计算hash值，将其保存在内存中
顺序扫描test1表
在扫描test1表的过程中，与test2表进行hash后的结果关联(hash join),关联的条件是两表的id字段相同
将数据汇总到Master上。Master将数据结果进行汇总并展现

6.insert、update和delete

几点数据切片带来的问题

insert：在执行insert语句的时候，要留意分布键不要为空，否则分布键默认会变成null，数据都被保存在一个节点上，造成数据分布不均

insert可以批量操作，语法如下：

insert into test001 values(100,'tom'),(101,'lily'),(102,'jack);

update：不能批量对分布键执行update，因为对分布键执行update需要将数据重分布，而gp暂时不支持这个功能

delete：在gp 3.x的版本中，如果delete操作涉及子查询，并且子查询的结果还会涉及数据重分布，这样的删除语句会报错，如下（gp 4.x中支持该操作）：

如果对整张表执行Delete会比较慢，建议使用TRUNCATE

7.TRUNCATE

执行TRUNCATE直接删除表的物理文件，然后创建新的数据文件，TRUNCATE操作比delete在性能上有非常大的提升，当前如果有sql正在操作这张表，那么TRUNCATE操作会被锁住，知道表上面的所有锁被释放

testDB=# truncate test001;

TRUNCATE TABLE

2.3.4 常用数据类型

1. 数值类型

2.字符类型

3.时间类型

2.3.5 常用函数

1.字符串函数

2.时间函数

3.数值计算函数

4.其他常用函数

（1）序列号生成函数：generate_series(start,end,step)

（2）字符串列转行函数：string_agg

（3）字符串行转列-regexp_split_to_table

（4）hash函数：md5，hashbpchar

2.3.6 分析函数

1.开窗函数

聚合函数返回各个分组的结果，开窗函数则为每一行返回结果：

{ rank() | row_number() | sum(...) | count(...) | ...} over ( [ partition by ...] [ order by ... ] )

2.grouping sets

如果需要对几个字段的组合进行group by，就需要用到Grouping Sets的功能：

2.3.7 分区表

对分区表的一些常用操作：

（1）新增分区：

testDB=# alter table public. test_partirion_every add partition p20120105_6 START ('2012-01-05'::date) END ('2012-01-07'::date);

NOTICE: CREATE TABLE will create partition "test_partition_l_l_prt_p20120105_6" for table "test_partition_l"

ALTER TABLE

（2）drop/truncate 分区

alter table public. test_partition_every drop partition p20120105_6;

（3）拆分分区：

alter table public. test_partition_every split partition p20120105_6 at(('2012-01-06'::date)) into (PARTITION p20120105,PARTITION p20120106;)

（4）交换分区

alter table public. test_partition_every exchange partition p20120102 with table public.test_one_partition;

2.3.8 外部表

gp在数据加载上有一个明显的优势，就是支持数据并发加载，gpfdist就是并发加载的工具，在数据库中对应的就是外部表

外部表，就是一张表的数据是指向数据库之外的数据文件的。在gp中，我们可以对一个外部表执行正常的DML操作，当读取数据时，数据库就从数据文件中加载数据。外部表支持在Segment上并发地告诉从gpfdist导入数据，由于是直接从Segment上导入数据，所以效率非常高

创建外部表的语法：

外部表需要制定 gpfdist 的 IP 和端口，还要有详细的目录地址，其中文件名支持通配符匹配。可以编写多个 gpfdist 的地址，但是不能超过总的 Segment 数，否则会报错。在创建外部表的时候可以制定分隔符、 err 表、制定允许出错的数据条数，以及原文件的字符编码等信息。

外部表还支持本地文本文件的导入，不过效率较低，不建议使用。外部表还支持 HDFS 的文件操作

启动 gpfdist 及创建外部表的实际步骤如下：

1）首先在文件服务器（假设是 10.20.151.11 ）上启动 gpfdist 的服务，指定文件目录及端口。

nohup $GPHOME/bin/gpdfist -d /home/admin -p 8888 > /tmp/gpfdist.log 2>&1 &

启动 gpfdist 后，在 log 中可以看到：

Serving HTTP on plrt 8888, directory /home/admin

说明程序已经成功启动了，端口是8888，这个服务只需要启动一次以后就不用启动了 nohup 保证程序在 Server 端执行，当前会话关闭后，程序仍然正常运行

2）准备好需要加载的数据文件，将其放在 10.20.151.11 机器上 /home/admin/ 目录或该目录的子目录下，在 gp 中创建对应的外部表：

3）外部表查询及数据加载：

4）如果加载报错，报错的数据会被插入到err表中，并显示报错详细信息：

2.3.9 COPY命令

使用COPY命令可以实现将文件导出和导入，只不过要通过Master，效率没有外部表高，但是在数据量比较小的情况下，COPY命令比外部表要方便很多

使用COPY命令的语法如下：

gp 4.x 中引入了可写外部表，在导出数据的时候可以用可写外部表并发导出，性能很好，但是在 gp 3.x 版本中，导出数据只能通过 COPY 命令实现，数据在 Master 上汇总导出。

如果需要将数据远程导出到其他机器上，可以使用 copy to stdout ，远程执行 psql 连接到数据库上，然后通过管道将数据重定向成文件。

标签

#数据库