第二章 greenplum 快速入门
本章将介绍如何快速安装部署greenplum，以及一些常用命令及工具
[TOC]
2.1 软件安装及数据库初始化
2.1.1 greenplum 架构
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都通
-
创建用户及用户组
- 删除原有用户：
#groupdel gpadmin # userdel gpadmin-
创建新的用户和用户组：
#groupadd -g 530 gpadmin #useradd -g 530 -m -d /home/gpadmin -s /bin/bash gpadmin -
对文件夹进行赋权，为新用户创建密码:
#chown -R gpadmin:gpadmin /home/bash gpadmin #passwd gpadmin Changing password for user gpadmin. New UNIX password: Retype new UNIX password:
2.1.3 greenplum安装
1. 安装数据库软件
下载地址：https://network.gopivotal.com/products/pivotal-gpdb
- 准备好安装文件：
greenplum-db-4.1.1.1-build-1-RHEL5-x86_64.zip
- 执行unzip命令解压安装文件：
unzip greenplum-db-4.1.1.1-build-1-RHEL5-x86_64.zip
-
解压后生成两个文件：
README_INSTALL; greenplum-db-4.1.1.1-build-1-RHEL5-x86_64.bin. -
为gp软件创建安装目录，并且赋给gp用户权限：
mkdir /opt/greenplum chown -R gpadmin:gpadmin /opt/greenplum -
执行以下命令开始安装软件：
./greenplum-db-4.1.1.1-build-1-RHEL5-x86_64.bin -
屏幕上回出现License的一些信息，按“空格”键使信息完全，如下图：
4 -
确认License之后，接着出现如下图：
5 -
输入“yes”之后会提示安装目录，选择安装在/opt/greenplum/greenplum-db-4.1.1.1下，如下图
6 -
完成以上步骤后，软件开始自动安装，最好显示安装成功，安装目录如下图：
7 gp的环境变量已经在greenplum_path.sh中设置了，这里需要应用一下这个环境变量配置
source /opt/greenplum/greenplum-db/greenplum_path.sh
2. 配置 hostlist
配置hostlist文件，将所有的服务器名记录在里面
[gpadmin@dw-greenplum-l conf]$ cat hostlist
mdw
sdw1
sdw2
sdw3
seg_hosts 只保存Segment节点的hostname。
[gpadmin@dw-greenplum-l conf]$ cat seg_hosts
sdw1
sdw2
sdw3
3. 使用gpssh-exkeys打通所有服务器
使用gpssh-exkeys将所有机器的通道打开，这样就不用输入密码使登录在每台机器之间跳转了，代码如下：
[gpadmin@dw-greenplum-l conf]$ grssh-exkeys -f hostlist
[STEP 1 of 5] create local ID and authorize on local host
[STEP 2 of 5] keyscan all hosts and update known_hosts file
[STEP 3 of authorize current user on remote hosts]
... send to sdwl
***
*** Enter password for sdwl;
... send to sdw2
... send to sdw3
[STEP 4 of 5] determine common authentication file content
[STEP 5 of 5] copy authentication files to all remote hosts
... finished key exchange with sdw1
... finished key exchange with sdw2
... finished key exchange with sdw3
[INFO] completed successfully
在打通所有机器通道后，我们就可以使用grssh命令对所有机器进行批量操作了
[gpadmin@dw-greenplum-l conf]$ grssh -f hostlist
=> pwd
[sdw3] /home/gradmin
[ mdw] /home/gradmin
[sdw1] /home/gpadmin
[sdw2] /home/gpadmin
4. 将软件分发到每一台机器上
接下来将安装后的文件打包：
tar -cf gp4.1.tar greenplum-db-4.1.1.1/
然后利用gpscp命令将这个文件复制到每一台机器上：
gpscp -f /home/gpadmin/conf/hostlist gp4.1.tar =:/opt.greenplum
使用gpssh命令批量解压文件包：
=> cd /opt/greenplum
[sdw3]
[ mdw]
[sdw1]
[sdw2]
=> tar -xf gp4.1.tar
[sdw3]
[ mdw]
[sdw1]
[sdw2]
建立软连接，如图：
下面创建数据库数据目录
MASTER目录：
=> mkdir -p /home/gpadmin/gpdata/gpmaster
Primary节点目录：
=> mkdir -p /home/gpadmin/gpdata/gpdatap1
=> mkdir -p /home/gpadmin/gpdata/gpdatap2
Mirror节点目录：
=> mkdir -p /home/gpadmin/gpdata/gpdatam1
=> mkdir -p /home/gpadmin/gpdata/gpdatam2
Gpmaster目录保存Master的数据，每个机器上的gpdatap1、gpdatap2否下对应这个机器的两个主数据节点目录，同样的，gpdatam1、gpdatam2对应备数据节点目录
5. 配置 ~/.bash_profile
要对系统的环境变量进行配置，需要修改~./bash.profile，添加以下内容：
source /opt.greenplum/greenplum-db/greenplum_path.sh
export MASTER_DATA_DIRECTORY=/home/gpadmin/gpdata/gpmaster/gpseg-l
export PGPORT=2345
export PGDATABASE=testDB
其中greenplum_path.sh保存了运行gp的以下环境变量设置，包括GPHOME、PYTHONHOME等设置
6. 初始化greenplum的配置文件
配置文件的模板可以在$GPHOME/docs/cli_help/gpconfigs、目录下找到。gpinitsystem_config文件是初始化greenplum的模板，在这个模板中，Mirror Segment的配置都被注释掉了，下面是初始化的配置文件initgp_config：
# 数据库的代号
ARRAY_NAME="Greenplum"
MACHINE_LIST_FILE=/home/gpadmin/conf/se_hosts
#Segment的名称前缀
SEG_PREFIX=gpseg
#Primary Segment起始的端口号
PROT_BASE=33000
# 指定 Primary Segment的数据目录
declare -a DATA_DIRECTORY=(/home/gpadmin/gpdatap1 /home/gpadmin/gpdata/gpdatap2)
#Master所在的机器的Hostname
MASTER_HOSTNAME=mdw
# 指定Master的数据目录
MASTER_DIRECTORY=/home/gpadmin/gpdata/gpmaster
#Master的端口
MASTER_PORT=2345
# 指定 Bash 的版本
TRUSRED_SHELL=/usr/bin/ssh
# 字符集 ENCODING=UNICODE
#MIRROR_PORT_BASE=43000
#Primary Segment 主备同步的起始端口号
REPLICATION_PORT_BASE=34000
#Mirror Segment 主备同步的起始端口号
MIRROR_REPLICATION_PORT_BASE=44000
#Mirror Segment 的数据目录
declare -a MIRROR_DATA_DIRECTORY=(/home/gpadmin/gpdata/gpdatam1 /home/gpadmin/gpdata/gpdatam2)
7.初始化数据库
使用gpinitsystem脚本来初始化数据库，命令如下：
gpinitsystem -c initgp_config -s sdw3
根据脚本出现的额提示操作即可，如下：
这样数据库就初始化成功了，尝试登陆greenplum默认的数据库postgres：
[gpadmin@dw-greenplum-l ~]$ psql -d postgres
psql (8.2.15)
Type "help" for help.
postgres=#
2.1.4 创建数据库
创建测试数据库
createdb 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.1.5 数据库启动与关闭
1. 启动数据库
可以用gpstart-help来查看帮助：
[gpadmin@inc-dw-hadoop-151-7 ~]$ gpstart --help
启动数据库，不用输入“yes”，输入后如下图:
gpstart -a
2.关闭数据库
关闭数据库的脚本是gpstop：
一般使用gpstop -a，不用输入“yes”：
gpstop -s
2.2 安装Greenplum的常见问题
安装gp最常见的错误就是环境变量设置错误，网卡配置错误，或者是每个Segment的通道或网络没有打通
2.2.1 /etc/hosts 配置错误
查询一张普通表时报如下错误，但是查询数据字典又不报错：
报这个错误时因为Master连接不到Segment。如果原先是一个正常的系统，突然报错了，就要想想是否修改了什么导致的。
也可以利用这个方法来判断一个操作是否需要与Master交互，比如生成执行计划是否只在Master上执行，与Segment有没有交互：
这样就可以看出生成分布式执行计划也是需要与Segment进行交互的
2.2.2 MASTER_DATA_DIRECTORY设置错误
没有设置MASTER_DATA_DIRECTORY，会报这样的错误：
这样会导致MASTER_DATA_DIRECTORY参数的目录设置错误：
在初始化数据库的时候要多注意环境变量的设置，如果环境变量设置不当，很容易造成数据库初始化错误
2.3 畅游Greenplum
2.3.1 如何访问Greenplum
1.psql
psql是greenplum/PostgreSQL默认的客户端。
可以在其他机器上使用psql连接到数据库中：
之所以报错，是因为Greenplum有权限控制，并不是所有的机器都可以连接到数据库上。
如果有其他计算机要登录Greenplum，先为数据库用户gpadmin创建一个密码，然后再pg_hba.conf文件中增加客户端机器的权限配置，这样就可以成功登录
testDB=# alter role gpadmin with password 'gpadmin';
ALTER ROLE
接着在$MASTER_DATA_DIRECTORY//pg_hba.conf文件中增加：
host testDB gpadmin 10.20.151.1/32 md5
之后通过gpstop -u命令使配置生效，如下图：
这样我们就可以在其他机器上登录数据库：
2.pgAdmin
下载地址：http://www.pgadmin.org/download/
2.3.2 数据库整体概况
1. greenplum基于PostgreSQL开发
gp是基于开源数据库软件PostgreSQL 8.3 开发的，其大部分语法和数据字典都与PostgreSQL一样，很多工具使用的规范也基本一样
2.gp的数据分布
可以说gp将PostgreSQL改造成一个分布式数据库。其中Segment节点都是一个单独的PostgreSQL数据库，Master本身也是一个PostgreSQL数据库
Master本身不储存数据，所有数据拆分保存到每一个节点上，在指定分布键的时候，数据按照分布键的Hash值来分布数据，称为哈希分布。
还以一种分布不用指定分部件，数据随机分布到每一个节点，称作随机分布（也叫平均分布）
2.3.3 基本语法介绍
1. 获取语法介绍
可以使用\h查看gp支持的所有语法
在psql中使用 \h command 可以获取具体命令的语法：
testDB=# \h create view
Command: CREATE VIEW
Description: define a new view
Syntax:
CREATE [ OR REPLACE ] [TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] AS query
2.CREATE TABLE
gp建表语句与其他数据库不同的地方：
- 在gp中建表时需要制定表的分布键
- 如果表需要用某个字段分区，可以通过partition by将表建成分区表
- 可以使用like操作创建与like的表一样结构的表，功能类似create table t1 as select * from t2 limit 0
- 可以使用inherits实现表的继承，棘突参考postgreSQL文档
由于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); INSERT 0 3 -
update：不能批量对分布键执行update，因为对分布键执行update需要将数据重分布，而gp暂时不支持这个功能
37 -
delete：在gp 3.x的版本中，如果delete操作涉及子查询，并且子查询的结果还会涉及数据重分布，这样的删除语句会报错，如下（gp 4.x中支持该操作）：
38如果对整张表执行Delete会比较慢，建议使用TRUNCATE
7.TRUNCATE
执行TRUNCATE直接删除表的物理文件，然后创建新的数据文件，TRUNCATE操作比delete在;x;ng上有非常大的提升，当前如果有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 分区表
在创建表时，关于partition的语法如下：
alter table 对分区表特有的一些操作的语法：
对分区表的一些常用操作：
（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 连接到数据库上，然后通过管道将数据重定向成文件。
