本节主要从数据库中数据操作和管理的角度学习gp数据库。
目录：
- 定义数据库对象
- 管理数据
- 装载和卸载数据
基本概念：
表空间 | 一个表空间是一个存储位置，其中底层的基础数据库的对象可以保留。它在物理和逻辑数据之间提供了一个抽象层并用于所有DBMS管理的段分配储存。 创建后，可以在创建数据库段时按名称引用表空间。表空间仅指定数据库的储存位置，不指定数据库结构或数据库架构。 |
schema | （发音“skee-muh” 或者“skee-mah”，中文叫模式）是数据库的组织和结构，schemas andschemata都可以作为复数形式。 |
cascade | 删除操作时，自动删除依赖对象 |
1.定义数据库对象
（一）创建和管理数据库
- 模版数据库
不要在template1中创建任何对象，除非用户想要在每一个用户创建的数据库中都有那些对象。
用户可以使用template0来创建一个只包含Greenplum数据库在初始化时预定义的标准对象且完全干净的数据库
- 创建一个数据库
=> CREATE DATABASE new_dbname;客户端创建：
代码语言：javascript复制$ createdb -h masterhost -p 5432 mydatabase //可省略 -h -p 默认本地- 克隆一个数据库
=> CREATE DATABASE new_dbname TEMPLATE old_dbname;testdb=# create database newtestdb template testdb;
CREATE DATABASE
testdb=# l
List of databases
Name | Owner | Encoding | Access privileges
----------- --------- ---------- ---------------------
komablog | gpadmin | UTF8 |
newtestdb | gpadmin | UTF8 |
postgres | gpadmin | UTF8 |
template0 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
template1 | gpadmin | UTF8 | =c/gpadmin
: gpadmin=CTc/gpadmin
testdb | gpadmin | UTF8 |
(6 rows)- 查看数据库列表
testdb=# ltestdb=# SELECT datname from pg_database;
datname
-----------
template1
template0
postgres
testdb
newtestdb
komablog
(6 rows)- 修改一个数据库
修改search path配置参数
代码语言：javascript复制testdb=# alter database testdb set search_path to public, pg_catalog;
ALTER DATABASE- 删除一个数据库
testdb=# c newtestdb
You are now connected to database "newtestdb" as user "gpadmin".
newtestdb=# drop database testdb;
DROP DATABASE$ dropdb -h masterhost -p 5432 mydatabase
[gpadmin@gp-master ~]$ dropdb newtestdb;（二）创建和管理表空间
一个表空间可以让多个数据库使用；而一个数据库可以使用多个表空间。属于"多对多"的关系。
查看现有表空间
代码语言：javascript复制komablog=# db
List of tablespaces
Name | Owner | Filespace Name
------------ --------- ----------------
pg_default | gpadmin | pg_system
pg_global | gpadmin | pg_system
(2 rows)- 创建一个文件空间
greenplum为什么会引入filespace的概念？
因为主机目录结构可能不一样，所以原有的目录结构式的方法来创建表空间，可能不够灵活。一个表空间需要一个文件系统位置来存放它的数据库文件。文件空间可以被一个或者多个表空间使用
代码语言：javascript复制$ su - gpadmin查看现有的表空间和文件空间：
代码语言：javascript复制komablog=# SELECT spcname as tblspc, fsname as filespc,
komablog-# fsedbid as seg_dbid, fselocation as datadir
komablog-# FROM pg_tablespace pgts, pg_filespace pgfs,
komablog-# pg_filespace_entry pgfse
komablog-# WHERE pgts.spcfsoid=pgfse.fsefsoid
komablog-# AND pgfse.fsefsoid=pgfs.oid
komablog-# ORDER BY tblspc, seg_dbid;
tblspc | filespc | seg_dbid | datadir
------------ ----------- ---------- ----------------------
pg_default | pg_system | 1 | /data/master/gpseg-1
pg_default | pg_system | 2 | /data/primary/gpseg0
pg_default | pg_system | 3 | /data/primary/gpseg1
pg_default | pg_system | 4 | /data/master/gpseg-1
pg_default | pg_system | 5 | /data/mirror/gpseg0
pg_default | pg_system | 6 | /data/mirror/gpseg1
pg_global | pg_system | 1 | /data/master/gpseg-1
pg_global | pg_system | 2 | /data/primary/gpseg0
pg_global | pg_system | 3 | /data/primary/gpseg1
pg_global | pg_system | 4 | /data/master/gpseg-1
pg_global | pg_system | 5 | /data/mirror/gpseg0
pg_global | pg_system | 6 | /data/mirror/gpseg1
(12 rows)
komablog=# select * from pg_tablespace;
spcname | spcowner | spclocation | spcacl | spcprilocations | spcmirlocations | spcfsoid
------------ ---------- ------------- -------- ----------------- ----------------- ----------
pg_default | 10 | | | | | 3052
pg_global | 10 | | | | | 3052
(2 rows)TEMPORARY_FILES和TRANSACTION_FILES对应的filespace如下：
代码语言：javascript复制[gpadmin@gp-master ~]$ gpfilespace --showtempfilespace
20191230:16:37:13:018274 gpfilespace:gp-master:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.
20191230:16:37:13:018274 gpfilespace:gp-master:gpadmin-[INFO]:-Getting filespace information for TEMPORARY_FILES
20191230:16:37:14:018274 gpfilespace:gp-master:gpadmin-[INFO]:-Checking for filespace consistency
20191230:16:37:14:018274 gpfilespace:gp-master:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
20191230:16:37:15:018274 gpfilespace:gp-master:gpadmin-[INFO]:-TEMPORARY_FILES OIDs are consistent for pg_system filespace
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-TEMPORARY_FILES entries are consistent for pg_system filespace
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-Obtaining current filespace entries used by TEMPORARY_FILES
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-Current Filespace for TEMPORARY_FILES is pg_system
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-1 /data/master/gpseg-1
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-4 /data/master/gpseg-1
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-2 /data/primary/gpseg0
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-5 /data/mirror/gpseg0
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-3 /data/primary/gpseg1
20191230:16:37:16:018274 gpfilespace:gp-master:gpadmin-[INFO]:-6 /data/mirror/gpseg1gpfilespace --showtransfilespace
[gpadmin@gp-master ~]$ gpfilespace --showtransfilespace
20191230:16:38:09:018508 gpfilespace:gp-master:gpadmin-[INFO]:-
A tablespace requires a file system location to store its database
files. A filespace is a collection of file system locations for all components
in a Greenplum system (primary segment, mirror segment and master instances).
Once a filespace is created, it can be used by one or more tablespaces.
20191230:16:38:09:018508 gpfilespace:gp-master:gpadmin-[INFO]:-Getting filespace information for TRANSACTION_FILES
20191230:16:38:09:018508 gpfilespace:gp-master:gpadmin-[INFO]:-Checking for filespace consistency
20191230:16:38:09:018508 gpfilespace:gp-master:gpadmin-[INFO]:-Obtaining current filespace entries used by TRANSACTION_FILES
20191230:16:38:11:018508 gpfilespace:gp-master:gpadmin-[INFO]:-TRANSACTION_FILES OIDs are consistent for pg_system filespace
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-TRANSACTION_FILES entries are consistent for pg_system filespace
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-Obtaining current filespace entries used by TRANSACTION_FILES
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-Current Filespace for TRANSACTION_FILES is pg_system
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-1 /data/master/gpseg-1
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-4 /data/master/gpseg-1
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-2 /data/primary/gpseg0
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-5 /data/mirror/gpseg0
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-3 /data/primary/gpseg1
20191230:16:38:12:018508 gpfilespace:gp-master:gpadmin-[INFO]:-6 /data/mirror/gpseg1如果我们的greenplum集群中，有SSD硬盘，又有SATA硬盘。怎样更好的利用这些空间呢？ 方法1： 用flashcache或bcache，通过device mapper技术，将SSD和SATA绑定，做成块设备。再通过 逻辑卷管理 或者 软RAID 或者 brtfs or zfs管理起来，做成大的文件系统。（还有一种方法是用RHEL 7提供的LVM，可以将SSD作为二级缓存）这种方法对GP来说，是混合动力，可以创建一个或多个文件系统（都具备混合动力）。所以建议只需要一个pg_system filespace就够了(除非容量到了文件系统管理的极限，那样的话可以分成多个文件系统)。用多个文件系统的情况下，就需要对每个文件系统，创建对应的目录，以及filespace。
方法2. SSD和SATA分开，各自创建各自的文件系统。 对每个文件系统，创建对应的目录，以及filespace。
- 删除表空间和文件空间
DROP TABLESPACE命令移除一个空的表空间。
DROP FILESPACE命令移除一个空的文件空间。
（二）创建和管理schema
- 创建一个模式
komablog=# CREATE SCHEMA myschema;
CREATE SCHEMACREATE SCHEMA schemaname AUTHORIZATION username;- 设置搜索路径
komablog=# alter database testdb set search_path to myschema,public,pg_catalog;
ALTER DATABASEALTER ROLE sally SET search_path TO myschema, public,
pg_catalog;- 查看当前方案
komablog=# select current_schema;
current_schema
----------------
public
(1 row)komablog=# drop schema myschema;
DROP SCHEMA要删除一个方案连同其中的所有对象（表、数据、函数等等），可以使用：
代码语言：javascript复制=> DROP SCHEMA myschema CASCADE;- 系统schema
pg_catalog | 包含着系统目录表、内建数据类型、函数和操作符 |
|---|---|
information_schema | 包含数据库中对象信息的视图集合组成 |
pg_toast | 存储大型对象，如超过页面尺寸的记录 |
pg_bitmapindex | 存储位图索引对象，例如值的列表。 |
pg_aoseg | 存储追加优化表对象 |
gp_toolkit | 包含用户可以用SQL命令访问的外部表、视图和函数 |
（三）创建和管理表
- 设置表和字段约束
CHECK约束只能引用它所在的表。 UNIQUE和PRIMARY KEY约束必须和它们所在表的分布键和分区键（如果有）兼容。
允许FOREIGN KEY约束，但不会被强制。
用户在分区表上定义的约束将作为整体应用到分区表上。用户不能在该表的单独的部分上定义约束。
- check约束
=> CREATE TABLE products
( product_no integer,
name text,
price numeric CHECK (price > 0) );- not null
=> CREATE TABLE products
( product_no integer NOT NULL,
name text NOT NULL,
price numeric );- unique
=> CREATE TABLE products
( product_no integer UNIQUE,
name text,
price numeric)
DISTRIBUTED BY (product_no);- primary key
=> CREATE TABLE products
( product_no integer PRIMARY KEY,
name text,
price numeric)
DISTRIBUTED BY (product_no);- 外键
不支持，gp数据库目前不支持外键
- 表的分布策略
DISTRIBUTED BY（哈希分布）或者 DISTRIBUTED RANDOMLY（循环分布）
分布策略基本原则：
均匀数据分布
本地和分布式操作
均匀查询处理
- 声明分布键
=> CREATE TABLE products
(name varchar(40),
prod_id integer,
supplier_id integer)
DISTRIBUTED BY (prod_id);=> CREATE TABLE random_stuff
(things text,
doodads text,
etc text)
DISTRIBUTED RANDOMLY;（四） 选择表的存储模型
Greenplum数据库支持多种存储模型和一种混合存储模型
- 堆存储
堆表最适合于较小的表，例如维度表，它们在初始载入数据后会经常被更新。常用语OLTP业务中
- 追加优化存储
追加优化表的存储模型是为批量数据装载优化的，因此不推荐单行的INSERT语句。
- 创建一个堆表
komablog=# CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);
CREATE TABLE
komablog=# dt
List of relations
Schema | Name | Type | Owner | Storage
-------- ---------- ------- --------- ---------
public | foo | table | gpadmin | heap
public | twitters | table | gpadmin | heap
public | users | table | gpadmin | heap
(3 rows)=> CREATE TABLE bar (a int, b text)
WITH (appendonly=true)
DISTRIBUTED BY (a);
komablog=# CREATE TABLE bar (a int, b text)
komablog-# WITH (appendonly=true)
komablog-# DISTRIBUTED BY (a);
CREATE TABLE
komablog=# dt
List of relations
Schema | Name | Type | Owner | Storage
-------- ---------- ------- --------- -------------
public | bar | table | gpadmin | append only
public | foo | table | gpadmin | heap
public | twitters | table | gpadmin | heap
public | users | table | gpadmin | heap
(4 rows面向行的存储：适合OLTP类型业务
面向列的存储：适合于在少量列上计算数据聚集的数据仓库负载，或者是用于需要对单列定期更新但不修改其他列数据的情况。
- 创建一个面向列的表
komablog=# CREATE TABLE bar1 (a int, b text) WITH (appendonly=true, orientation=column) DISTRIBUTED BY (a);
CREATE TABLE
komablog=# dt
List of relations
Schema | Name | Type | Owner | Storage
-------- ---------- ------- --------- ----------------------
public | bar | table | gpadmin | append only
public | bar1 | table | gpadmin | append only columnar
public | foo | table | gpadmin | heap
public | twitters | table | gpadmin | heap
public | users | table | gpadmin | heap
(5 rows)- 使用压缩
表方向 | 可用的压缩类型 | 支持的算法 |
|---|---|---|
行 | 表 | ZLIB以及 QUICKLZ1 |
列 | 列和表 | RLE_TYPE、ZLIB以及 QUICKLZ1 |
- 创建一个压缩表
komablog=# d foo
Append-Only Table "public.foo"
Column | Type | Modifiers
-------- --------- -----------
a | integer |
b | text |
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Distributed by: (a)- 检查追加优化表的压缩和分布
=# SELECT get_ao_distribution('lineitem_comp');
get_ao_distribution
---------------------
(0,7500721)
(1,7501365)
(2,7499978)
(3,7497731)
(4 rows)- 存储指令示例
CREATE TABLE T1 (c1 int ENCODING (compresstype=zlib),
c2 char ENCODING (compresstype=quicklz, blocksize=65536),
c3 char WITH (appendonly=true, orientation=column);- type中增加压缩
定义comptype
代码语言：javascript复制CREATE TYPE comptype (
internallength = 4,
input = comptype_in,
output = comptype_out,
alignment = int4,
default = 123,
passedbyvalue,
compresstype="quicklz",
blocksize=65536,
compresslevel=1
);CREATE TABLE t2 (c1 comptype)
WITH (APPENDONLY=true, ORIENTATION=column);- 修改一个表
ALTER TABLE可以更改表的属性，例如列定义、分布策略、存储模型以及分区结构
代码语言：javascript复制ALTER TABLE address ALTER COLUMN street SET NOT NULL;ALTER TABLE sales SET DISTRIBUTED BY (customer_id); ALTER TABLE sales SET DISTRIBUTED RANDOMLY;改成随机分布，不会立即产生效果。
- 数据重新分布
ALTER TABLE sales SET WITH (REORGANIZE=TRUE);重新组织数据对于更正一个数据倾斜问题是必要的，当系统中增加了Segment资源后也需要重新组织数据
- 修改表的存储类型
创建新表，载入原始数据，删除旧表，改名新表，重新授权用户
代码语言：javascript复制CREATE TABLE sales2 (LIKE sales)
WITH (appendonly=true, compresstype=quicklz,
compresslevel=1, orientation=column);
INSERT INTO sales2 SELECT * FROM sales;
DROP TABLE sales;
ALTER TABLE sales2 RENAME TO sales;
GRANT ALL PRIVILEGES ON sales TO admin;
GRANT SELECT ON sales TO guest;- 增加压缩列
ALTER TABLE T1
ADD COLUMN c4 int DEFAULT 0
ENCODING (COMPRESSTYPE=zlib);- 设置压缩继承
CREATE TABLE ccddl (i int, j int, k int, l int)
WITH
(APPENDONLY = TRUE, ORIENTATION=COLUMN)
PARTITION BY range(j)
SUBPARTITION BY list (k)
SUBPARTITION template(
SUBPARTITION sp1 values(1, 2, 3, 4, 5),
COLUMN i ENCODING(COMPRESSTYPE=ZLIB),
COLUMN j ENCODING(COMPRESSTYPE=QUICKLZ),
COLUMN k ENCODING(COMPRESSTYPE=ZLIB),
COLUMN l ENCODING(COMPRESSTYPE=ZLIB))
(PARTITION p1 START(1) END(10),
PARTITION p2 START(10) END(20))
;
ALTER TABLE ccddl
ADD PARTITION p3 START(20) END(30)
;- 删除一个表
DROP TABLE mytable; //删除
TRUNCATE mytable; //清空
