greenplum获取建表语句_Greenplum数据库基本语句 - 树懒学堂

这一节提供了Greenplum数据库中有关操纵数据和并发访问的信息。这个主题包括下列小标题：

关于Greenplum数据库中的并发控制

插入行

更新现有行

删除行

使用事务

清理数据库

关于Greenplum数据库中的并发控制

Greenplum数据库和PostgreSQL不为并发控制使用锁。它们使用一种多版本模型来维护数据一致性，即多版本并发控制(MVCC)。MVCC为每一个数据库会话实现了事务隔离，并且每一个查询事务会看到一个数据的快照。这保证该事务会看到一致的不受其他并发事务影响的数据。

因为MVCC不会为并发控制使用显式锁，锁竞争被小化并且Greenplum数据库在多用户环境中维持了合理的性能。为查询(读取)数据获得的锁不与为写数据获得的锁冲突。

Greenplum数据库提供了多种锁模式来控制对表中数据的并发访问。大部分Greenplum数据库的SQL命令自动获取适当的锁来确保在命令执行期间被引用的表不会被删除或者被以不兼容的方式被修改。对于不能轻易适应于MVCC行为的应用，可以使用LOCK命令来获取显式锁。不过，MVCC的正确使用通常能提供更好的性能。

锁模式相关的SQL命令冲突模式ACCESS SHARESELECTACCESS EXCLUSIVE

ROW SHARESELECT FOR SHARE、SELECT FOR UPDATEEXCLUSIVE、ACCESS EXCLUSIVE

ROW EXCLUSIVEINSERT、COPYSHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE

SHARE UPDATE EXCLUSIVEVACUUM(不带FULL)、ANALYZESHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE

SHARECREATE INDEXROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE

SHARE ROW EXCLUSIVEROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE

EXCLUSIVEDELETE、UPDATE、SELECT FOR UPDATEROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE

ACCESS EXCLUSIVEALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULLACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE

注意： Greenplum数据库会为UPDATE、DELETE和SELECT FOR UPDATE获取更加严格的EXCLUSIVE锁(而不是PostgreSQL中的ROW EXCLUSIVE)。

插入行

使用INSERT命令在一个表中创建行。这个命令要求该表的名称和表中每一个列的值，可以选择性地以任意顺序指定列名。如果没有指定列名，以那些列在表中的顺序列出数据值，用逗号分隔它们。

例如，指定要插入的列名和值：

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);

要插入数据到一个分区表，应指定根分区表，即用CREATE TABLE命令创建的表。也可以在一个INSERT命令中指定该分区表的一个叶子子表。如果数据对于指定的叶子子表无-效，会返回一个错误。不支持在INSERT命令中指定一个不是叶子子表的子表。

要插入大量数据，使用外部表或者COPY命令。对于插入大量行，这些装载机制比INSERT更加有效。

追加优化表的存储模型是为批量数据装载而优化。Greenplum不推荐对追加优化表的单行INSERT语句。对于追加优化表，Greenplum数据库支持多127个并发INSERT事务插入到一个追加优化表。

更新现有行

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

要执行一次更新，需要：

要更新的表和列的名称

这些列的新值

指定要更新的行的一个或者更多条件。

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

UPDATE products SET price = 10 WHERE price = 5;

在Greenplum数据库中使用UPDATE由下列限制：

Greenplum的分布键列不能被更新。

如果使用了镜像，不能在UPDATE语句中使用STABLE或VOLATILE函数。

Greenplum数据库不支持RETURNING子句。

Greenplum数据库的分区列不能被更新。

删除行

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

DELETE FROM products WHERE price = 10;

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

DELETE FROM products;

在Greenplum数据库中使用DELETE具有和使用UPDATE类似的限制：

如果使用了镜像，不能在DELETE语句中使用STABLE或VOLATILE函数。

Greenplum数据库不支持RETURNING子句。

截断一个表

使用TRUNCATE命令可以快速地移除一个表中的所有行。例如：

TRUNCATE mytable;

这个命令在一次操作中清空一个表的所有行。注意TRUNCATE不扫描该表，因此它不会处理继承的子表或者ON DELETE的重写规则。该命令只截断所提到的表中的行。

使用事务

事务允许用户把多个SQL语句捆绑在一个要么全做要么全不做的操作中。

下面是Greenplum数据库的SQL事务命令：

BEGIN或者START TRANSACTION 开始一个事务块。

END或者COMMIT提交一个事务的结果。

ROLLBACK放弃一个事务而不做任何更改。

SAVEPOINT在一个事务中标记一个位置并且允许做部分回滚。用户可以回滚在一个保存点之后执行的命令但保留该保存点之前执行的命令。

ROLLBACK TO SAVEPOINT 回滚一个事务到一个保存点。

RELEASE SAVEPOINT 销毁一个事务内的保存点。

事务隔离级别

Greenplum数据库接受下列标准SQL事务级别：

读未提交和读已提交的行为像标准的读已提交

不允许可重复读。如果要求可重复读的行为，可使用可序列化。

可序列化的行为像SQL标准的可序列化

下列信息描述了Greenplum事务级别的行为：

读已提交/读未提交 — 提供快速、简单、部分的事务隔离。使用读已提交和读未提交事务隔离，SELECT、UPDATE和DELETE事务在一个查询开始时取得的数据库快照上操作。

一个SELECT查询：

看得见该查询开始前被提交的数据。

看得见在该事务内执行的更新。

看不见事务外未提交的数据。

如果并发事务在该查询所在事务初的读操作之前就被提交，该查询可能会看到这个并发事务所作的更改。

如果其他并发事务在同一个事务中后续的SELECT查询开始前提交更改，这些查询能够看到不同的数据。UPDATE和DELETE命令只找在该命令开始前提交的行。

读已提交或读未提交事务隔离允许并发事务在UPDATE或者DELETE找到行之前修改或者锁定该行。读已提交或读未提交事务隔离可能不适合执行复杂查询和更新并且要求该数据库的一致性视图的应用。

可序列化 — 提供了严格的事务隔离，在其中事务的执行就好像没有并行化而是一个接一个执行。可序列化级别上的应用必须被设计为在序列化失败的情况下重试事务。在Greenplum数据库中，SERIALIZABLE能防止脏读、不可重复读和幻读，而且不需要昂贵的锁定，但是在Greenplum数据库的一些SERIALIZABLE事务之间可能发生其他的相互影响而阻止它们变成真正地可序列化。并发运行的事务应该被检查来识别出不会因为不允许对同一数据的并发更新而被阻止的相互影响。通过使用显式表锁或者要求冲突事务更新一个被引入来表示该冲突的虚拟行可以阻止所发现的问题。

一个SELECT查询：

看得到一个事务开始时(不是该事务中当前查询开始时)的数据快照。

只看得到在查询开始前被提交的数据。

看得到该事务内执行的更新。

看不到该事务外部的未提交数据。

看不到并发事务所作的更改。一个单一事务中的后续SELECT命令总是看到相同的数据。UPDATE、DELETE、SELECT FOR UPDATE和SELECT FOR SHARE命令只会发现在该命令开始前被提交的行。如果一个目标行被找到时一个并发事务已经更新、删除或者锁定该行，可序列化或者可重复读事务会等待该并发事务更新该行、删除该行或者回滚。如果该并发事务更新或者删除该行，可序列化或者可重复读事务会回滚。如果并发事务回滚，那么可序列化或者可重复读事务更新或者删除该行。

Greenplum数据库中的默认事务隔离级别是读已提交。要为一个事务更改隔离级别，在BEGIN该事务时声明隔离级别或者在事务开始后使用SET TRANSACTION命令设置隔离级别。

清理数据库

虽然新事务看不到被删除或者被更新的数据行，但是它们仍然在磁盘上占用物理空间。周期性地运行VACUUM命令可以移除这些过期的行。例如：

VACUUM mytable;

VACUUM命令会收集表级别的统计信息，例如行数和页数。在装载数据后清理所有的表，包括追加优化表。有关推荐的例行清理操作的信息。重要：如果在数据库数据上频繁地执行更新和删除， VACUUM、VACUUM FULL和VACUUM ANALYZE命令应该被用来维护Greenplum数据库中的数据。有关使用VACUUM命令的信息请见Greenplum数据库参考指南。

配置空闲空间映射

过期行被放在空闲空间映射中。空闲空间映射的尺寸应该足够大以保存数据库中所有的过期行。如果空间不足，一个普通的VACUUM命令也不能回收使空闲空间映射溢出的过期行所占用的空间。

VACUUM FULL会回收所有的过期行空间，但是它是一种很昂贵的操作，并且在大型的分布式的Greenplum数据库表上可能会花很长的不可接受的时间来完成。如果空闲空间映射溢出，可以用一个CREATE TABLE AS 语句重建该表并且删除旧表。不鼓励使用VACUUM FULL。用下列服务器配置参数调整空闲空间映射的尺寸：

max_fsm_pages

max_fsm_relations


本文来源：https://blog.csdn.net/weixin_39696518/article/details/111723917