­

Snova运维篇（九）：gp数据库中数据的基本操作-1

  • 2019 年 12 月 30 日
  • 筆記

本节主要从数据库中数据操作和管理的角度学习gp数据库。

目录：

  1. 定义数据库对象
  2. 管理数据
  3. 装载和卸载数据

基本概念：

表空间

一个表空间是一个存储位置，其中底层的基础数据库的对象可以保留。它在物理和逻辑数据之间提供了一个抽象层并用于所有DBMS管理的段分配储存。 创建后，可以在创建数据库段时按名称引用表空间。表空间仅指定数据库的储存位置，不指定数据库结构或数据库架构。

schema

（发音“skee-muh” 或者“skee-mah”，中文叫模式）是数据库的组织和结构，schemas andschemata都可以作为复数形式。

cascade

删除操作时，自动删除依赖对象

1.定义数据库对象

（一）创建和管理数据库

  • 模版数据库

不要在template1中创建任何对象，除非用户想要在每一个用户创建的数据库中都有那些对象。

用户可以使用template0来创建一个只包含Greenplum数据库在初始化时预定义的标准对象且完全干净的数据库

  • 创建一个数据库
=> CREATE DATABASE new_dbname;

客户端创建：

$ 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=# l
testdb=# SELECT datname from pg_database;    datname  -----------   template1   template0   postgres   testdb   newtestdb   komablog  (6 rows)
  • 修改一个数据库

修改search path配置参数

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;

（二）创建和管理表空间

一个表空间可以让多个数据库使用；而一个数据库可以使用多个表空间。属于"多对多"的关系。

查看现有表空间

komablog=# db            List of tablespaces      Name    |  Owner  | Filespace Name  ------------+---------+----------------   pg_default | gpadmin | pg_system   pg_global  | gpadmin | pg_system  (2 rows)
  • 创建一个文件空间
greenplum为什么会引入filespace的概念？  因为主机目录结构可能不一样，所以原有的目录结构式的方法来创建表空间，可能不够灵活。

一个表空间需要一个文件系统位置来存放它的数据库文件。文件空间可以被一个或者多个表空间使用

$ su - gpadmin

查看现有的表空间和文件空间：

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如下：

[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/gpseg1
gpfilespace --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 SCHEMA
CREATE SCHEMA schemaname AUTHORIZATION username;
  • 设置搜索路径
komablog=# alter database testdb set search_path to myschema,public,pg_catalog;  ALTER DATABASE
ALTER 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

要删除一个方案连同其中的所有对象（表、数据、函数等等），可以使用：

=> 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

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可以更改表的属性，例如列定义、分布策略、存储模型以及分区结构

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资源后也需要重新组织数据

  • 修改表的存储类型

创建新表，载入原始数据，删除旧表，改名新表，重新授权用户

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; //清空

VirMach 便宜 VPS

QNews

QNews