Greenplum中SQL特征

1、Consult Build Deploy ManageGreenplum SQL介绍 邓才应现代设备（中国）有限公司北京环亚时代信息技术有限公司深圳发展银行ODS项目组Overview Greenplum 数据库介绍 PostgreSql语法特征 Pgsql与Plsql主要区别 Pgsql操作注意事项Greenplum 数据库介绍 Massively Parallel Processing (MPP) DBMS 基于开源的PostgreSQL1 postgres(master)+n postgres (instance)external tables / parallel loadingcop

2、y commandresource managementquery optimizer enhancementsGreenplum特性DK分布 HASH分布 避免数据倾斜 随机分布 不能建立唯一索引PARTITION类型 RANGE分区 LIST分区 -建议表记录小于2000W不使用分区表表存储类型 heap table、Column-Oriented table、Row-Oriented tableINDEX策略 一个表只建一个唯一索引 唯一索引全包含DK字段分析表 vacuum analyze user table； vacuum full system table；PostgreSql语

3、法特征 PostgreSQL 是一种关系型数据库管理系统(RDBMS)。 Greenplum基于PostgreSQL，但也不是Postgres所有特性都能使用，比如： 没有触发器 不能指定表空间 不能在function中建立新的事务保存点 不推荐使用外键和索引(特殊情况除外)Data Type/CommandData Type / CommandORACLEPOSTGRESNumericNUMBER (p, s)SMALLINT (2 bytes)INTEGER (4 bytes)BIGINT (8 bytes)DECIMAL (p, s) (11 + p/2 bytes)NUMERIC (p

4、, s) (11 + p/2 bytes)REAL (4 bytes)DOUBLE (8 bytes)SERIAL (4 bytes)BIGSERIAL (8 bytes)CharacterCHAR and NCHARCHARVARCHAR2 and NVARCHAR2VARCHARDate & Time Date FunctionsDATE (includes time to sec)DATE orTIMESTAMP without Time ZoneTIMESTAMPTIMESTAMPINTERVALINTERVAL/TIMETRUNC ()DATE_TRUNC (day, )TR

5、UNC (sysdate)current_dateSysdatecurrent_timestamp or now()sysdate + 1.5current_timestamp + 36 hours:intervalBinaryBFILE ( 1 GB)Large Objects (up to 2 GB)RAW BFILE ( Limit & OffsetROWIDOID（不推荐使用）NVL()COALESCE()Union，intersect，minusUnion，intersect，exceptDECODE()CASE expression WHENCASE WHEN expres

6、sionSELECT SUM(*) TOTAL FROM tab1;SELECT SUM(*) AS TOTAL FROM tab1;SELECT * FROM a, b WHERE a.col1 = b.col1(+);SELECT * FROM aLEFT JOIN b ON a.col1 = b.col1;SELECT * FROM (SELECT id FROM a ORDER BY name) WHERE rownum 8 AND rownum = 20;SELECT id FROM a ORDER BY name LIMIT 12OFFSET 8;PartitionsOne IND

7、EX over all dataOne INDEX for each partitionFunction ORACLE Procedure function Greenplum functionOracle例子CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS CURSOR referrer_keys IS SELECT * FROM cs_referrer_keys ORDER BY try_order; func_cmd VARCHAR(4000); BEGIN func_cmd := CREATE OR REPLACE

8、FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN; FOR referrer_key IN referrer_keys LOOP func_cmd := func_cmd | IF v_ | referrer_key.kind | LIKE | referrer_key.key_string | THEN RETURN | referrer_key.referrer_type | ; END IF; END LOOP;

9、func_cmd := func_cmd | RETURN NULL; END; EXECUTE IMMEDIATE func_cmd; END; / Greenplum例子CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$DECLARE referrer_key RECORD; func_body text; func_cmd text;BEGIN func_body := BEGIN; FOR referrer_key IN SELECT * FROM cs_referrer_ke

10、ys ORDER BY try_order LOOP func_body := func_body | IF v_|referrer_key.kind | LIKE quote_literal(referrer_key.key_string) | THEN RETURN | quote_literal(referrer_key.referrer_type) | ; END IF; ; END LOOP; func_body := func_body | RETURN NULL; END; func_cmd := CREATE OR REPLACE FUNCTION cs_find_referr

11、er_type(v_host varchar, v_domain varchar, v_url varchar) RETURNS varchar AS | quote_literal(func_body) | LANGUAGE plpgsql; ; EXECUTE func_cmd;END;$func$ LANGUAGE plpgsql;Greenplum常用函数length(string)octet_length(string)replace(string text, from text, to text)strpos(string, substring)substr(string, fro

12、m , count)trim(string)lpad(string text, length int , fill text)rpad(string text, length int , fill text)to_charto_dateto_numberto_timestampdate_part(text, timestamp)date_trunc(text, timestamp)extract(field from timestamp)。Greenplum自定义C函数例子crc64_str.sqlCREATE OR REPLACE FUNCTION crc64_str(text) RETUR

13、NS integer AS /usr/local/greenplum-db/lib/cgcrc64,crc64_str LANGUAGE C;Cgcrc64.C#include #include #include #include postgres.h #include #include fmgr.h PG_FUNCTION_INFO_V1(crc64_str);Datum crc64_str(PG_FUNCTION_ARGS) . 分区表 Oracle: (range、list、hash) select * from sales partition (sdb_partition_201001

14、01); 分区表以逻辑表形式存在 Greenplum： (range、list) select * from sales_1_prt_sdb_partition_20100101; 分区表以物理表形式存在系统表/视图 gp_configuration pg_tables pg_partition pg_partitions pg_proc pg_indexes pg_class pg_stat_activity pg_locks 对象授权Tables, Views, SequencesSELECTINSERTUPDATEDELETERULEALLDatabasesCREATETEMPORARY

15、ALLFunctionsEXECUTEProcedural LanguagesUSAGESchemasCREATEUSAGEALL注意：例如给某个角色赋予操作表的权限，必须对数据库、模式和表同时有权限Greenplum优化建议 使用执行计划 EXPLAIN ANALYZE SQL EXPLAIN SQLEXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 Bitmap Heap Scan on tenk1 t1 (cost=2.37.232.35 rows=106 width=244) (actual time=0.

16、878.2.367 rows=100 loops=1) Recheck Cond: (unique1 Bitmap Index Scan on tenk1_unique1 (cost=0.00.2.37 rows=106 width=0) (actual time=0.546.0.546 rows=100 loops=1) Index Cond: (unique1 Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00.3.01 rows=1 width=244) (actual time=0.067.0.078 rows=1 loops=1

17、00) Index Cond: (outer.unique2 = t2.unique2) Total runtime: 14.452 msParallel Query PlansSELECT customer, amount FROM sales JOIN customer USING (cust_id) WHERE date=04302008;Query Worker ProcessesGreenplum优化建议 用明确的 JOIN 控制规划器 关闭自动提交 使用 COPY 删除索引和外键约束 事后运行 VACUUM ANALYZE COPY命令COPY table (column , .)

18、 FROM file | STDIN WITH BINARY OIDS DELIMITER AS delimiter NULL AS null string ESCAPE AS escape | OFF NEWLINE AS LF | CR | CRLF CSV HEADER QUOTE AS quote FORCE NOT NULL column , . FILL MISSING FIELDS LOG ERRORS INTO error_table KEEP SEGMENT REJECT LIMIT count ROWS | PERCENT COPY table (column , .) |

19、 (query) TO file | STDOUT WITH BINARY OIDS DELIMITER AS delimiter NULL AS null string ESCAPE AS escape | OFF NEWLINE AS LF | CR | CRLF CSV HEADER QUOTE AS quote FORCE QUOTE column , . EXTERNAL TABLECREATE EXTERNAL TABLE table_name ( column_name data_type , .| LIKE other_table )LOCATION (file:/seghos

20、t:port/path/file , .)| (gpfdist:/filehost:port/file_pattern , .)FORMAT TEXT | CSV( DELIMITER AS delimiter | OFFNULL AS null stringESCAPE AS escape | OFFHEADERQUOTE AS quoteFORCE NOT NULL column , .FILL MISSING FIELDS ) ENCODING encoding LOG ERRORS INTO error_table SEGMENT REJECT LIMIT countROWS | PE

21、RCENT 2022-2-21注意事项1 不能对分布字段即distributed by ()子句中的字段进行更新 在更新表时，如果存在表关联，那么相对应的关联字段都必须是对应的distributed by 字段注意事项2现象：不支持Not exists语句，只支持exists语句结论：使用left join替代注意事项3现象：or关系不能出现在两个子查询中结论： 1、GP数据库并不支持对多个子查询集的or操作，遇到这类语句，就要考虑用语句替换。 2、即使是一个子查询查与一个非子查询集的or操作，也是不允许的。 3、对于多个非子查询集的or操作，是允许的。 4、对于多子查询集的and操作，是允许的。 注意事项4 现象：Case when语句中不能出现子嵌套查询结论： GreenPlum并不支持在case when中含有子嵌套查询的复杂语句,通过left join等操作替代注意事项5现象：在ORACLE脚本中，常常会通过rowid进行一些数据的特殊处理，如删除重复的记录；PostgreSQL数据库有一个与oracle的rowid类似的伪列oid，但会重复。结论： 总的来说，gp没有严格兼容oracle的rowid，如果segment instance不共用相