Chapter 18 Greenplum 特性总结
This sectionprovides a high-level overview of the system requirements and feature set ofGreenplum Database. It contains the following topics:
Greenplum SQL Standard Conformance
The SQLlanguage was first formally standardized in 1986 by the American NationalStandards Institute (ANSI) as SQL 1986. Subsequent versions of the SQL standardhave been released by ANSI and as International Organization forStandardization (ISO) standards: SQL 1989, SQL 1992, SQL 1999,
SQL 2003, SQL 2006, and finally SQL2008, which is the current SQL standard. The official name of the standard isISO/IEC 9075-14:2008. In general, each new version adds more features, althoughoccasionally features are deprecated or removed.
It is important to note that there areno commercial database systems that are fully compliant with the SQL standard.Greenplum Database is almost fully compliant with the SQL 1992 standard, withmost of the features from SQL 1999. Several features from SQL 2003 have alsobeen implemented (most notably the SQL OLAP features).
This section addresses the importantconformance issues of Greenplum Database as they relate to the SQL standards.For a feature-by-feature list of Greenplum's support of the latest SQLstandard, seeSQL 2008 Optional Feature Compliance.
SQL语言是首次正式在1986年由美国国家标准学会（ANSI）作为SQL标准SQL标准的1986年后续版本已经由ANSI和国际标准化组织（ISO）标准发布：1989年的SQL，SQL 1992年， 1999年的SQL，
SQL 2003，SQL 2006年，终于SQL 2008，这是当前的SQL标准。该标准的正式名称是ISO/IEC9075-14：2008。_般情况下，每一个新版本增加了更多的功能，虽然偶尔功能已过时或删除。
要注意，有_些是和SQL标准完全兼容没有商业数据库系统是很重要的。 Greenplum的数据库与SQL 1992标准的几乎完全兼容，大部分的功能从1999年的SQL 2003SQL几项功能也已落实（最明显的是SQL OLAP功能）。
因为这涉及到SQL标准本节讨论Greenplum数据引擎的重要_致性的问题。对于Greenplum的支持最新的SQL标准的一个特征逐个功能列表，请参阅SQL 2008可选功能合规性。
In the process of building a parallel,shared-nothing database system and query optimizer, certain common SQLconstructs are not currently implemented in Greenplum Database. The followingSQL constructs are not supported:
1. Some setreturning subqueries in exists or not exists clauses that Greenplum's paralleloptimizer cannot rewrite into joins.
2. union all of joined tables with subqueries.
3. Set-returningfunctions in the from clause of a subquery.
4. Backwardsscrolling cursors, including the use of fetch prior, fetch first, fetch abolute, and
FETCH RELATIVE.
5. In create table statements (on hash-distributedtables): a unique or primary key clause must include all of (or asuperset of) the distribution key columns. Because of this restriction, onlyone unique clause or primary key clause is allowed in a create table statement. unique or primary key clauses are not allowed onrandomly-distributed tables.
6. create unique index statements that do not contain all of(or a superset of) the distribution key columns. create uniqueindex is not allowedon randomly-distributed tables.
Note that unique indexes(but not uniqueconstraints) are enforcedon a part basis within a partitioned table. They guarantee the uniqueness ofthe key within each part or sub-part.
7. volatile or stable functions cannot execute on thesegments, and so are generally limited to being passed literal values as thearguments to their parameter .
8. Triggers arenot supported since they typically rely on the use of volatile functions.
9. Referentialintegrity constraints (foreign keys) are not enforced in Greenplum Database.Users can declare foreign keys and this information is kept in the systemcatalog, however.
10.Sequence manipulation functions currval and lastval.
11.delete wherecurrent of and update wherecurrent of (positioneddelete and positioned update operations).
在建立一个平行的，无共享数据库系统和查询优化的过程中，某些常见的SQL构造当前未在Greenplum数据引擎实现的。下面的SQL构造不支持：
1._些返回集合中的子查询的存在与否存在Greenplum的并行优化器无法改写为连接从句。
2. UNION ALL子查询与连接表的。
3.设置返回功能在FROM子查询的条款。
4.向后滚动游标，包括使用FETCH PRIOR，FETCH FIRST，FETCH ABOLUTE，并FETCH相对的。
5.在CREATETABLE语句（散列分布表）：一个UNIQUE或PRIMARY KEY子句必须包括所有（或的超集）的分布键列。由于此限制，只有一个唯一的条款或PRIMARY KEY子句允许在CREATE TABLE语句。 UNIQUE或PRIMARY KEY子句中不允许随机分布表。
6.创建不包含所有（或的超集）的分布键列UNIQUEINDEX语句。创建唯一索引不允许随机分布表。
需要注意的是唯一索引（但不是唯一约束）强制执行分区表中的部分基础。他们保证每个零件或子部件内键的唯一性。
7.挥发或稳定的功能不能执行区段上，所以_般只限于被传递文字值作为parameter 的parameter 。
8.触发器不支持，因为它们通常依赖于使用volatile功能。
9.参照完整性约束（外键）在Greenplum数据不强制执行。然而，用户可以声明外键和此信息保存在系统目录中。
10.Sequence操作函数CURRVAL和LASTVAL。
11.DELETE WHERE CURRENT OF和UPDATE WHERE CURRENT OF（定位删除和定位的更新操作）。
The followingfeatures of SQL 1992 are not supported in Greenplum Database:
1. NATIONAL CHARACTER (NCHAR) and NATIONAL CHARACTER VARYING (NVARCHAR). Users candeclare
the nchar and nvarchar types, however they are just synonymsfor char and varchar in Greenplum Database.
2. create assertion statement.
3. interval literals are supported in GreenplumDatabase, but do not conform to the standard.
4. get diagnostics statement.
5. grant insert or update privileges on columns. Privileges canonly be granted on tables in Greenplum Database.
6. GLOBALtemporary tables and LOCALtemporary tables. Greenplum temporarytables do notconform to the SQL standard, but many commercial database systems haveimplemented temporary tables in the same way. Greenplum temporary tables arethe same as volatile tables in Teradata.
7. unique predicate.
8. match partial for referential integrity checks (mostlikely will not be implemented in Greenplum Database).
1992年SQL的以下功能不支持Greenplum数据：
1.国家字符（NCHAR）和国家字符改变（NVARCHAR）。用户可以申报
在NCHAR和NVARCHAR类型，但他们是在Greenplum数据CHAR和VARCHAR只是同义词。
2.创建断言语句。
3.间隔文字在Greenplum数据支持，但不符合标准。
4. GETDIAGNOSTICS语句。
5. GRANTINSERT或UPDATE权限的列。权限只能在Greenplum的数据库表授予。
6.全局临时表和局部临时表。 Greenplum的临时表不符合SQL标准，但是许多商业数据库系统都以同样的方式实施临时表。 Greenplum的临时表中的相同的Teradata挥发表。
7. UNIQUE谓词。
8. MATCHPARTIAL参照完整性检查（很可能不会在Greenplum数据引擎实现）。
The followingfeatures of SQL 1999 are not supported in Greenplum Database:
1. Large Objectdata types: blob, clob, nclob. However, the bytea and text columns can store very large amountsof data in Greenplum Database (hundreds of megabytes).
2. module (SQL client modules).
3. create procedure (sql/psm). This can be worked around inGreenplum Database by creating a function that returns void, and invoking the function asfollows:
SELECT myfunc(args);
4. ThePostgreSQL/Greenplum function definition language (pl/pgsql) is a subset of Oracle's pl/sql, rather than being compatible withthe sql/psm functiondefinition language. Greenplum Database also supports function definitionswritten in Python, Perl, Java, and R.
5. bit and bit varying data types (intentionally omitted).These were deprecated in SQL 2003, and replaced in SQL 2008.
6. Greenplumsupports identifiers up to 63 characters long. The SQL standard requiressupport for identifiers up to 128 characters long.
7. Prepared transactions (preparetransaction, commitprepared, rollbackprepared). This alsomeans Greenplum does not support xa Transactions (2 phase commit coordinationof database transactions with external transactions).
8. character set option on the definition of char() or varchar() columns.
9. Specification of characters or octets (bytes) on thelength of a char() or varchar() column. Forexample, varchar(15 characters) or varchar(15octets) or varchar(15 bytes).
10. CURRENT-SCHEMAfunction.
11. create distinct type statement. create domain can be used as a work-around inGreenplum.
12. The explicit table construct.
1999年SQL的以下功能不支持Greenplum数据：
1.大对象数据类型：BLOB，CLOB，NCLOB。然而，BYTEA和TEXT列可以存储大量的Greenplum数据引擎（数百MB）的数据。
2.模块（SQL客户端模块）。
3. CREATE PROCEDURE（SQL/PSM）。这可以在Greenplum数据通过创建一个返回void的函数，调用函数如下工作围绕：
SELECT MYFUNC（parameter ）;
4. PostgreSQL的/ Greenplum的功能定义语言（PL/PGSQL）是Oracle的PL/SQL的一个子集，而不是与SQL/PSM函数定义语言兼容。 Greenplum的数据库还支持用Python编写的，Perl和Java和R.函数定义
5. BIT和BIT VARYING数据类型（故意省略）。这些都是在2003年SQL过时，并在2008年取代SQL。
6.支持Greenplum的识别最多63个字符长。 SQL标准要求标识符最多128个字符长的支持。
7.准备交易（PREPARETRANSACTION，COMMIT的准备，ROLLBACK编制）。这也意味着Greenplum的不支持XA事务（2阶段提交与外部交易数据库事务协调）。
在CHAR（）或VARCHAR（）列的定义8 CHARACTERSET选项。
9.规格上CHAR（）或VARCHAR（）字段的长度字符或八位字节的。例如，VARCHAR（15字符）或VARCHAR（15字节）或VARCHAR（15字节）。
10. CURRENT-SCHEMA功能。
11.创建DISTINCT TYPE语句。 CREATE DOMAIN可以作为一个变通的Greenplum的。
12.明确的表结构。
The followingfeatures of SQL 2003 are not supported in Greenplum Database:
1. merge statements.
2. identity columns andthe associated generated always/generated by default clause. The serial or bigserial data types arevery similar to int or BIGINT GENERATED BY DEFAULT ASIDENTITY.
3. multiset modifiers on data types.
4. row data type.
5. GreenplumDatabase syntax for using sequences is non-standard. For example, nextvai('seq')is used inGreenplum instead of the standard next value for seq.
6. generated always as columns. Views can be used as awork-around.
7. The sampleclause (tablesample) on select statements. The random() function can be used as a work-aroundto get random samples from tables.
8. nulls first/nulls last clause on select statements and subqueries (nulls arealways last in Greenplum Database).
9. The partitionedjoin tables construct (partition by in a join).
10.grant select privileges on columns. Privileges canonly be granted on tables in Greenplum Database. Views can be used as awork-around.
11. For create table x(LIKE(y)) statements, Greenplum does not supportthe [including|
EXCLUDING][DEFAULTS|CONSTRAINTS|INDEXES]clauses.
12.Greenplumarray data types are almost SQL standard compliant with some exceptions.Generally customers should not encounter any problems using them.
2003 SQL的以下功能不支持Greenplum数据：
1. MERGE语句。
2. IDENTITY列和相关GENERATED ALWAYS/GENERATED BY DEFAULT子句。串行或BIGSERIAL数据类型非常相似，INT或BIGINT GENERATED默认身份。
3.数据类型MULTISET修饰。
4. ROW数据类型。
5.使用序列Greenplum数据引擎的语法是非标准的。例如，nextvai（'SEQ'）中的Greenplum是用来代替标准的NEXT VALUE FOR起。
6.总是生成为列。意见可以作为一个变通。
7. SELECT语句的样本条款（TABLESAMPLE）。随机（）函数可以作为一个变通从表中获取随机样本。
8. NULLS的SELECT语句和子（空值始终持续在Greenplum数据）FIRST/LAST NULLS子句。
9.分区表连接构造（PARTITION通过在加入）。
10. GRANT列的SELECT权限。权限只能在Greenplum的数据库表授予。意见可以作为一个变通。
11.对于CREATE TABLE X（赞（Y））语句，Greenplum的不支持[包括|
排除] [DEFAULTS | CONSTRAINTS |指标]条款。
12. Greenplum的数组数据类型都差不多SQL符合标准有_些例外。_般使用它们的客户应该不会遇到任何问题。
The followingfeatures of SQL 2008 are not supported in Greenplum Database:
1. binary and varbinary data types. bytea can be used in place of varbinary in Greenplum Database.
2. fetch first or fetch next clause for select, for example:
SELECT id, name FROM tab1 ORDER BY idOFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Greenplum has limit and limit offset clauses instead.
3. The order by clause is ignored in views andsubqueries unless a limit clause is also used. This is intentional,as the Greenplum optimizer cannot determine when it is safe to avoid the sort,causing an unexpected performance impact for such order by clauses. To work around, you canspecify a really large LIMIT. For example: SELECT * FROMmytable ORDER BY 1 LIMIT 9999999999
4. The row subquery construct is not supported.
5. truncate tabledoes not accept the continueidentity and restartidentity clauses.
SQL 2008的以下功能不支持Greenplum数据：
1. BINARY和VARBINARY数据类型。 BYTEA可以代替VARBINARY在Greenplum数据被使用。
2. FETCH FIRST或FETCH NEXT子句SELECT，例如：
SELECT ID，名称FROM TAB1ORDER BY ID偏移20行只能取整下10行;
Greenplum的有限度和限价抵消条款代替。
3. ORDER BY子句中的意见和子查询被忽略，除非LIMIT子句也用。这是故意的，因为Greenplum的优化不能确定何时可以安全地避免排序，造成这种订单意外性能影响BY子句。要解决，你可以指定一个真正的大的限制。例如：SELECT *FROM MYTABLE ORDER BY1 LIMIT9999999999
4.不支持该行子查询构造。
5. truncatetable不接受CONTINUE身份和RESTARTIDENTITY子句。
GreenplumDatabase is based on PostgreSQL 8.2 with a few features added in from the 8.3release.
To support thedistributed nature and typical workload of a Greenplum Database system, someSQL commands have been added or modified, and there are a few PostgreSQLfeatures that are not supported. Greenplum has also added features not found inPostgreSQL, such as physical data distribution, parallel query optimization,external tables, resource queues for workload management and enhanced tablepartitioning. For full SQL syntax and references, see theSQL Command Reference.
Greenplum的数据库是基于PostgreSQL的8.2与8.3版本中添加了_些功能。
支持Greenplum数据系统的分布式性质，典型的工作负载，_些SQL命令已被添加或修改的，并且有不支持的_些PostgreSQL特性。 Greenplum的还增加了在PostgreSQL里没有的功能，如物理数据分布，并行查询优化，外部表，资源队列工作负载管理和增强的表分区。对于完整的SQL语法和参考，请参阅SQL命令参考。
| Table 144: SQL Support in Greenplum Database
|
|
|
| SQL Command | Supported in Greenplum | Modifications, Limitations, Exceptions |
| ALTER TABLE | YES | Unsupported Clauses/Options: CLUSTER ON ENABLE/DISABLE TRIGGER Greenplum Database Clauses: ADD | DROP | RENAME | SPLIT | EXCHANGE PARTITION | SET SUBPARTITION TEMPLATE | SET WITH (REORGANIZE=true | false) | SET DISTRIBUTED BY |
| ALTER TABLESPACE | YES |
|
| ALTER TRIGGER | NO |
|
| ALTER TYPE | YES |
|
| ALTER USER | YES | An alias for ALTER ROLE |
| ANALYZE | YES |
|
| BEGIN | YES |
|
| CHECKPOINT | YES |
|
| CLOSE | YES |
|
| CLUSTER | YES |
|
| COMMENT | YES |
|
| COMMIT | YES |
|
| COMMIT PREPARED | NO |
|
| COPY | YES | Modified Clauses: escape [ as ] 1 escape1 | 'OFF' Greenplum Database Clauses: [log errors into error_table] SEGMENT REJECT LIMIT COUnf [ROWSIPERCENT] |
| CREATE AGGREGATE | YES | Unsupported Clauses/Options: [,sortop = sort_operator ] Greenplum Database Clauses: [,prefunc = prefunc ] Limitations: The functions used to implement the aggregate must be immutable functions. |
|
|
| SQL Command | Supported in Greenplum | Modifications, Limitations, Exceptions |
| CREATE CAST | YES |
|
| CREATE CONSTRAINT TRIGGER | NO |
|
| CREATE CONVERSION | YES |
|
| CREATE DATABASE | YES |
|
| CREATE DOMAIN | YES |
|
| CREATE EXTERNAL TABLE | YES | Greenplum Database parallel ETL feature - not in PostgreSQL 8.2.15. |
| CREATE FUNCTION | YES | Limitations: Functions defined as STABLE or volatile can be executed in Greenplum Database provided that they are executed on the master only. stable and VOLATILE functions cannot be used in statements that execute at the segment level. |
| CREATE GROUP | YES | An alias for CREATE ROLE |
| CREATE INDEX | YES | Greenplum Database Clauses: using bitmap (bitmap indexes) Limitations: UNIQUE indexes are allowed only if they contain all of (or a superset of) the Greenplum distribution key columns. On partitioned tables, a unique index is only supported within an individual partition - not across all partitions. CONCURRENTLY keyword not supported in Greenplum. |
| CREATE LANGUAGE | YES |
|
| CREATE OPERATOR | YES | Limitations: The function used to implement the operator must be an immutable function. |
| CREATE OPERATOR CLASS | NO |
|
| CREATE OPERATOR FAMILY | NO |
|
| CREATE RESOURCE QUEUE | YES | Greenplum Database workload management feature - not in PostgreSQL 8.2.15. |
|
|
| SQL Command | Supported in Greenplum | Modifications, Limitations, Exceptions |
| CREATE ROLE | YES | Greenplum Database Clauses: resource queue | none |
| CREATE RULE | YES |
|
| CREATE SCHEMA | YES |
|
| CREATE SEQUENCE | YES | Limitations: The lastval and currval functions are not supported. The setval function is only allowed in queries that do not operate on distributed data. |
| CREATE TABLE | YES | Unsupported Clauses / Options: [GLOBAL | LOCAL] REFERENCES FOREIGN KEY [DEFERRABLE | NOT DEFERRABLE] Limited Clauses: UNIQUE or PRIMARY KEY constraints are only allowed on hash-distributed tables (DISTRIBUTED BY), and the constraint columns must be the same as or a superset of the distribution key columns of the table and must include all the distribution key columns of the partitioning key. Greenplum Database Clauses: DISTRIBUTED BY (column,[. ..])| DISTRIBUTED RANDOMLY PARTITION BY type (column [,...]) ( p specification, [...]) WITH (appendonly=true [,compresslevel= value,blocksize=value]) |
| CREATE TABLE AS | YES | See CREATE TABLE |
|
|
| SQL Command | Supported in Greenplum | Modifications, Limitations, Exceptions |
| CREATE TABLESPACE | NO | Greenplum Database Clauses: filespace filespace_name |
| CREATE TRIGGER | NO |
|
| CREATE TYPE | YES | Limitations: The functions used to implement a new base type must be immutable functions. |
| CREATE USER | YES | An alias for CREATE ROLE |
| CREATE VIEW | YES |
|
| DEALLOCATE | YES |
|
| DECLARE | YES | Unsupported Clauses/Options: SCROLL FOR UPDATE [ OF column [,. ..]] Limitations: Cursors are non-updatable, and cannot be backward-scrolled. Forward scrolling is supported. |
| DELETE | YES | Unsupported Clauses/Options: RETURNING |
| DROP AGGREGATE | YES |
|
| DROP CAST | YES |
|
| DROP CONVERSION | YES |
|
| DROP DATABASE | YES |
|
| DROP DOMAIN | YES |
|
| DROP EXTERNAL TABLE | YES | Greenplum Database parallel ETL feature - not in PostgreSQL 8.2.15. |
| DROP FILESPACE | YES | Greenplum Database parallel tablespace feature - not in PostgreSQL 8.2.15. |
| DROP FUNCTION | YES |
|
| DROP GROUP | YES | An alias for DROP ROLE |
| DROP INDEX | YES |
|
| DROP LANGUAGE | YES |
|
|
|
| SQL Command | Supported in Greenplum | Modifications, Limitations, Exceptions |
| DROP OPERATOR | YES |
|
| DROP OPERATOR CLASS | NO |
|
| DROP OWNED | NO |
|
| DROP RESOURCE QUEUE | YES | Greenplum Database workload management feature - not in PostgreSQL 8.2.15. |
| DROP ROLE | YES |
|
| DROP RULE | YES |
|
| DROP SCHEMA | YES |
|
| DROP SEQUENCE | YES |
|
| DROP TABLE | YES |
|
| DROP TABLESPACE | NO |
|
| DROP TRIGGER | NO |
|
| DROP TYPE | YES |
|
| DROP USER | YES | An alias for DROP ROLE |
| DROP VIEW | YES |
|
| END | YES |
|
| EXECUTE | YES |
|
| EXPLAIN | YES |
|
| FETCH | YES | Unsupported Clauses/Options: LAST PRIOR BACKWARD BACKWARD ALL Limitations: Cannot fetch rows in a nonsequential fashion; backward scan is not supported. |
| GRANT | YES |
|
| INSERT | YES | Unsupported Clauses/Options: RETURNING |
| LISTEN | NO |
|
| LOAD | YES |
|
| LOCK | YES |
|
|
|
| SQL Command | Supported in Greenplum | Modifications, Limitations, Exceptions |
| MOVE | YES | See FETCH |
| NOTIFY | NO |
|
| PREPARE | YES |
|
| PREPARE TRANSACTION | NO |
|
| REASSIGN OWNED | YES |
|
| REINDEX | YES |
|
| RELEASE SAVEPOINT | YES |
|
| RESET | YES |
|
| REVOKE | YES |
|
| ROLLBACK | YES |
|
| ROLLBACK PREPARED | NO |
|
| ROLLBACK TO SAVEPOINT | YES |
|
| SAVEPOINT | YES |
|
| SELECT | YES | Limitations: Limited use of volatile and STABLE functions in FROM or where clauses Text search (Tsearch2) is not supported FETCH FIRST or FETCH NEXT clauses not supported Greenplum Database Clauses (OLAP): [group by grouping_eleme^t [,•••]] [window window_name as (window—specification)] [filter (where condition)] applied to an aggregate function in the SELECT list |
| SELECT INTO | YES | See SELECT |
| SET | YES |
|
| SET CONSTRAINTS | NO | In PostgreSQL, this only applies to foreign key constraints, which are currently not enforced in Greenplum Database. |
| SET ROLE | YES |
|
| SET SESSION AUTHORIZATION | YES | Deprecated as of PostgreSQL 8.1 -see SET ROLE |
|
|
| SQL Command | Supported in Greenplum | Modifications, Limitations, Exceptions |
| SET TRANSACTION | YES |
|
| SHOW | YES |
|
| START TRANSACTION | YES |
|
| TRUNCATE | YES |
|
| UNLISTEN | NO |
|
| UPDATE | YES | Unsupported Clauses: RETURNING Limitations: set not allowed for Greenplum distribution key columns. |
| VACUUM | YES | Limitations: vacuum full is not recommended in Greenplum Database. |
| VALUES | YES |
|
|
|
扫一扫
1549
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
