Greenplum表的管理实践-1

最新推荐文章于 2024-01-23 20:12:15 发布

Murkey学习之旅

最新推荐文章于 2024-01-23 20:12:15 发布

阅读量764

点赞数

分类专栏： # 数据库管理 Greenplum 文章标签： postgresql

本文链接：https://blog.csdn.net/murkey/article/details/105936042

版权

Greenplum 同时被 2 个专栏收录

35 篇文章 6 订阅

订阅专栏

数据库管理

8 篇文章 0 订阅

订阅专栏

Greenplum表的管理实践-1

本文章主要介绍和实践如何创建、修改、删除表，包括临时表的管理，同时针对表的约束，包括非空，唯一，主外键，默认等，另外还会简单进行数据的插入和修改，删除的实验操作。

首先需要申明的是Greenplum数据库的表与任何一种关系型数据库中的表类似，不过其表中的行被分布在系统中的不同Segment上。当用户创建一个表时，用户会指定该表的分布策略。

Greenplum的表分布策略，存储特殊属性，已经大表的分区，我会在表实践-2上进行总结整理。

Greenplum创建的表的策略或者注意事项：

该表的列以及它们的数据类型。参见选择列的数据类型。
任何用于限制列或者表中能包含的数据的表或者列约束。参见设置表和列约束。
表的分布策略，这决定了Greenplum数据库如何在Segment之间划分数据。参见选择表分布策略。
表存储在磁盘上的方式。参见选择表存储模型。
大型表的表分区策略。参见创建和管理数据库。

1 创建表

create table department(deptid int not null,
                       deptname varchar(20),
                       createtime timestamp)
                       DISTRIBUTED BY(deptid);
                       

archdata=# create table department(deptid int not null,
archdata(#                        deptname varchar(20),
archdata(#                        createtime timestamp)
archdata-#                        DISTRIBUTED BY(deptid);
CREATE TABLE
archdata=# 
archdata=# 
archdata=# \dt department
                List of relations
 Schema |    Name    | Type  |  Owner  | Storage 
--------+------------+-------+---------+---------
 public | department | table | gpadmin | heap
(1 row)

archdata=# \dt+ department
                       List of relations
 Schema |    Name    | Type  |  Owner  | Storage | Description 
--------+------------+-------+---------+---------+-------------
 public | department | table | gpadmin | heap    | 
(1 row)  

 查看表大小
         
 select pg_size_pretty(pg_relation_size('department'));
         
archdata=#  select pg_size_pretty(pg_relation_size('department'));
 pg_size_pretty 
----------------
 0 bytes
(1 row)

archdata=#

2 创建临时表

PostgreSQL支持两类临时表，会话级和事务级临时表。在会话级别的临时表中，在整个会话的生命周期中，数据一直保存。事务级临时表，数据只存在于这个事务的生命周期中。不指定临时表的属性，

PostgreSQL中，不管是事务级还是会话级临时表，当会话结束时，临时表就会消失。这与oracle数据库不同，在oracle数据库中，只是临时表中的数据消失，而临时表还存在。

PostgreSQL临时表是schema下所生成的一个特殊的表，这个schema的名称为“pg_temp_n”，其中n代表数字，不同的session数字不同。

一个会话创建的临时表不能被其他会话访问。

默认情况下，创建的临时表是会话级的，如果需要创建事务。需要添加“on commit delete rows”子句。（注：“on commit”子句形式有三种：“on commit preserve rows”，默认值，会话级；“on commit delete rows”，事务级，事务结束，删除数据；“on commit drop”，事务级，事务结束，删除临时表）

创建临时表的关键字“temporary”可以缩写为“temp”。

PostgreSQL为了与其他数据库创建临时表的语句保持兼容，还没有“GLOBAL”和“LOCAL”关键字，但两个关键字没有用处。

2.1 会话级临时表

创建临时表

create temporary table temp_t as select * from pg_class;

archdata=# create temporary table temp_t as select * from pg_class;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relname' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
SELECT 411

archdata=# \dt pg_class;
                 List of relations
   Schema   |   Name   | Type  |  Owner  | Storage 
------------+----------+-------+---------+---------
 pg_catalog | pg_class | table | gpadmin | heap
(1 row)

archdata=# select count(*) from pg_class;
 count 
-------
   411
(1 row)

archdata=# 

在本session中是可以看到表的
archdata-# \dt temp_t
                 List of relations
    Schema    |  Name  | Type  |  Owner  | Storage 
--------------+--------+-------+---------+---------
 pg_temp_1428 | temp_t | table | gpadmin | heap
(1 row)

archdata-#

在其他session中去查询相关临时表

archdata=# select pg_backend_pid();
 pg_backend_pid 
----------------
           4334
(1 row)

\dt temp_t
archdata=# \dt temp_t
No matching relations found.
archdata=# 

发现直接/dt的方式是查询不到temp表的

指定schema方式进行
\dt pg_temp_1428.temp_t
archdata-# \dt pg_temp_1428.temp_t
                 List of relations
    Schema    |  Name  | Type  |  Owner  | Storage 
--------------+--------+-------+---------+---------
 pg_temp_1428 | temp_t | table | gpadmin | heap
(1 row)

archdata-# 
archdata=# select * from  pg_temp_1428.temp_t;
                            relname                             | relnamespace | reltype | relowner |
 relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasi
ndex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys 
| relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid |                re
lacl                 | reloptions 
----------------------------------------------------------------+--------------+---------+----------+
-------+-------------+---------------+----------+-----------+---------------+---------------+--------
-----+-------------+---------+------------+----------+-----------+-------------+----------+----------
+---------+------------+------------+-------------+--------------

2.2 创建会话级别的临时表

archdata=# create temporary table temp_t2(id int,note text) on commit delete rows;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
archdata=#

archdata=# insert into temp_t2 values(1,'a');
INSERT 0 1
archdata=# 
archdata=# select * from temp_t2;
 id | note 
----+------
(0 rows) ---看不到记录了

archdata=# 

使用事务控制方式

postgres=# begin;
BEGIN
postgres=# insert into temp_t2 values(1,'a');
INSERT 0 1
postgres=# insert into temp_t2 values(1,'b');
INSERT 0 1
postgres=# select * from temp_t2;
 id | note
----+------
  1 | a
  1 | b
(2 行记录)
postgres=# end;
COMMIT
postgres=# select * from temp_t2;
 id | note
----+------
(0 行记录)


看到在事务结束后，表中的数据也都没有了，在别的会话中也是无法看到这个临时表的。

3 约束

3.1 字段级别的check

archdata=# CREATE TABLE department1 (
archdata(# deptid int NOT NULL,
archdata(# deptname varchar(20),
archdata(# createtime timestamp CONSTRAINT create_check CHECK (createtime > '1970-01-01')
archdata(# ) DISTRIBUTED BY(deptid);
CREATE TABLE
archdata=# 
archdata=# 
archdata=# \dt department1
                List of relations
 Schema |    Name     | Type  |  Owner  | Storage 
--------+-------------+-------+---------+---------
 public | department1 | table | gpadmin | heap
(1 row)

archdata=# \d department1
              Table "public.department1"
   Column   |            Type             | Modifiers 
------------+-----------------------------+-----------
 deptid     | integer                     | not null
 deptname   | character varying(20)       | 
 createtime | timestamp without time zone | 
Check constraints:
    "create_check" CHECK (createtime > '1970-01-01 00:00:00'::timestamp without time zone)
Distributed by: (deptid)

archdata=#

3.2 表级的check

archdata=# CREATE TABLE department2 (
archdata(# deptid int NOT NULL,
archdata(# deptname varchar(20) not null,
archdata(# createtime timestamp,
archdata(# parentcreatetime timestamp,
archdata(# check(parentcreatetime>createtime)
archdata(# ) DISTRIBUTED BY(deptid);
CREATE TABLE
archdata=# 
archdata=# 
archdata=# \d department2
                 Table "public.department2"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 deptid           | integer                     | not null
 deptname         | character varying(20)       | not null
 createtime       | timestamp without time zone | 
 parentcreatetime | timestamp without time zone | 
Check constraints:
    "department2_check" CHECK (parentcreatetime > createtime)
Distributed by: (deptid)

archdata=#

3.3 非空约束

关键字NOT NULL

archdata=# CREATE TABLE department3 (
archdata(# deptid int NOT NULL check (deptid>0),
archdata(# deptname varchar(20) not null,
archdata(# createtime timestamp,
archdata(# parentcreatetime timestamp
archdata(# ) DISTRIBUTED BY(deptid);
CREATE TABLE
archdata=# \d department3
                 Table "public.department3"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 deptid           | integer                     | not null
 deptname         | character varying(20)       | not null
 createtime       | timestamp without time zone | 
 parentcreatetime | timestamp without time zone | 
Check constraints:
    "department3_deptid_check" CHECK (deptid > 0)
Distributed by: (deptid)

archdata=#

3.4 唯一约束

关键件unique

archdata=# CREATE TABLE department4  (
archdata(# deptid int unique,
archdata(# deptname varchar(20) not null,
archdata(# createtime timestamp,
archdata(# parentcreatetime timestamp
archdata(# ) DISTRIBUTED BY(deptid);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "department4_deptid_key" for table "department4"
CREATE TABLE
archdata=# \d department4
                 Table "public.department4"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 deptid           | integer                     | 
 deptname         | character varying(20)       | not null
 createtime       | timestamp without time zone | 
 parentcreatetime | timestamp without time zone | 
Indexes:
    "department4_deptid_key" UNIQUE, btree (deptid)
Distributed by: (deptid)

archdata=# \di department4
No matching relations found.
archdata=# \di department4_deptid_key
                             List of relations
 Schema |          Name          | Type  |  Owner  | Storage |    Table    
--------+------------------------+-------+---------+---------+-------------
 public | department4_deptid_key | index | gpadmin | heap    | department4
(1 row)

archdata=#

3.5 主键约束

关键是primary key，就是唯一非空的，主键可以多个字段组合。

CREATE TABLE department5  (
	deptid int pr1mary key,
	deptname varchar(20) not null,
	createtime timestamp,
	parentcreatetime timestamp
) DISTRIBUTED BY(deptid);


archdata=# CREATE TABLE department5  (
archdata(# deptid int primary key,
archdata(# deptname varchar(20) not null,
archdata(# createtime timestamp,
archdata(# parentcreatetime timestamp
archdata(# ) DISTRIBUTED BY(deptid);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "department5_pkey" for table "department5"
CREATE TABLE
archdata=# 
archdata=# \d department5
                 Table "public.department5"
      Column      |            Type             | Modifiers 
------------------+-----------------------------+-----------
 deptid           | integer                     | not null
 deptname         | character varying(20)       | not null
 createtime       | timestamp without time zone | 
 parentcreatetime | timestamp without time zone | 
Indexes:
    "department5_pkey" PRIMARY KEY, btree (deptid)
Distributed by: (deptid)

archdata=#

3.6 外键约束

不支持外键,但是语法上允许外键约束,但不会起作用。

archdata=# create table emp(
archdata(# empid int not null,
archdata(# empname varchar(20),
archdata(# deptid int not null references department5(deptid),
archdata(# constraint pk_emp primary key(empid))
archdata-# DISTRIBUTED BY(empid);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "emp_pkey" for table "emp"
WARNING:  Referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced.
CREATE TABLE
archdata=# 

Greenplum支持外键

3.7 默认约束

archdata=# CREATE TABLE department6  (
archdata(# deptid int primary key,
archdata(# deptname varchar(20) not null,
archdata(# createtime timestamp,
archdata(# parentcreatetime timestamp default '2019-01-01 00:00:00'
archdata(# ) DISTRIBUTED BY(deptid);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "department6_pkey" for table "department6"
CREATE TABLE
archdata=# \d department6
                                         Table "public.department6"
      Column      |            Type             |                         Modifiers                  
        
------------------+-----------------------------+----------------------------------------------------
--------
 deptid           | integer                     | not null
 deptname         | character varying(20)       | not null
 createtime       | timestamp without time zone | 
 parentcreatetime | timestamp without time zone | default '2019-01-01 00:00:00'::timestamp without ti
me zone
Indexes:
    "department6_pkey" PRIMARY KEY, btree (deptid)
Distributed by: (deptid)

archdata=#

3.8 自增加

方法一先创建序列，然后设置字段的自增。

create sequence seq_test start with 1 increment by 1 no minvalue no maxvalue cache 1

Sequence(序列)是数据库经常使用自增列属性，对于单机PostgreSQL实例，数据库维护一个自增变量即可。但是对于Greenplum的MPP架构，如果每个节点都维护自己的Sequence，那么Sequence将会出现重复，那么Greenplum是如何处理的呢？序列是master统一维护的。

archdata=# create sequence seq_test start with 1 increment by 1 no minvalue no maxvalue cache 1
archdata-# ;
CREATE SEQUENCE

create table tb_test(a int not null default nextval('seq_test'))

方案二使用serial数据烈性

create table tb_test1(a serial not null,b text)

archdata=# \d tb_test1
                         Table "public.tb_test1"
 Column |  Type   |                      Modifiers                       
--------+---------+------------------------------------------------------
 a      | integer | not null default nextval('tb_test1_a_seq'::regclass)
 b      | text    | 
Distributed by: (a)

archdata=#

4 修改表

4.1 修改表名

alter table tb_test1 rename to tb_test_new;

4.2 修改字段名

alter table department rename column createtime to deptcreatetime;

4.3 添加字段

alter table department add column pid int not null;

4.4 删除字段

alter table department drop column pid;

4.5 删除表外键

首先找出数据库表的外键名称：

\d [tablename]
....
"table_name_id_fkey" FOREIGN KEY (id) REFERENCES other_table(id) ....
1
2
3
然后使用下面的命令删除外键：

ALTER TABLE [tablename] DROP CONSTRAINT table_name_id_fkey;

5 删除表

PostgreSQL 使用 DROP TABLE 语句来删除表格，包含表格数据、规则、触发器等，所以删除表格要慎重，删除后所有信息就消失了。

drop table table_name;

如果想删除主键的表，需要添加cascade，这样就可以删除主键表，同时删除了子表的外键约束，其他的不会删除。

6 数据的插入、修改和删除

6.1 插入

例如，指定要插入的列名和值：
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

只指定要插入的值:

INSERT INTO products VALUES (1, 'Cheese', 9.99);


通常，数据值都是常量，但也可以使用标量表达式。例如： 

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < 
'2016-05-07';


可以在单个命令中插入多行。例如：

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

6.2 更新

UPDATE 命令在一个表中更新行。可以更新一个表中所有的行、所有行的一个子集或者单个行。可以单独更新每一列而不影响其他列。 

要执行一次更新，需要： 
•要更新的表和列的名称
•这些列的新值
•指定要更新的行的一个或者更多条件。

例如，下面的命令把所有价格为5 的产品更新为价格为 10: 

UPDATE products SET price = 10 WHERE price = 5;


在Greenplum数据库中使用 UPDATE 由下列限制:
•GPORCA可以为Greenplum分布键列提供更新支持，Postgres planner则不会 。
•如果使用了镜像，不能在UPDATE语句中使用STABLE或VOLATILE 函数。
•Greenplum数据库的分区列不能被更新.

6.3 删除数据

DELETE命令从一个表中删除行。指定一个WHERE子句可以删除满足特定条件的行。如果不指定WHERE 子句，该表中所有的行都会被删除。其结果是一个合法的但为空的表。例如，从产品表中删除所有价格为10的行：

DELETE FROM products WHERE price = 10;


要从一个表中删除所有行:

DELETE FROM products; 


在Greenplum数据库中使用 DELETE 具有和使用UPDATE类似的限制:
•如果使用了镜像，不能在 UPDATE 语句中使用STABLE 或VOLATILE 函数。