Greenplum中SQL特征

1、Consult Build Deploy ManageGreenplum SQL介紹 鄧才應(yīng)現(xiàn)代設(shè)備（中國(guó)）有限公司北京環(huán)亞時(shí)代信息技術(shù)有限公司深圳發(fā)展銀行ODS項(xiàng)目組Overview Greenplum 數(shù)據(jù)庫(kù)介紹 PostgreSql語(yǔ)法特征 Pgsql與Plsql主要區(qū)別 Pgsql操作注意事項(xiàng)Greenplum 數(shù)據(jù)庫(kù)介紹 Massively Parallel Processing (MPP) DBMS 基于開(kāi)源的PostgreSQL1 postgres(master)+n postgres (instance)external tables / parallel loadingcop

2、y commandresource managementquery optimizer enhancementsGreenplum特性DK分布 HASH分布 避免數(shù)據(jù)傾斜 隨機(jī)分布 不能建立唯一索引PARTITION類(lèi)型 RANGE分區(qū) LIST分區(qū) -建議表記錄小于2000W不使用分區(qū)表表存儲(chǔ)類(lèi)型 heap table、Column-Oriented table、Row-Oriented tableINDEX策略 一個(gè)表只建一個(gè)唯一索引 唯一索引全包含DK字段分析表 vacuum analyze user table； vacuum full system table；PostgreSql語(yǔ)

3、法特征 PostgreSQL 是一種關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)(RDBMS)。 Greenplum基于PostgreSQL，但也不是Postgres所有特性都能使用，比如： 沒(méi)有觸發(fā)器 不能指定表空間 不能在function中建立新的事務(wù)保存點(diǎn) 不推薦使用外鍵和索引(特殊情況除外)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, )TRUNC

5、(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 expressionSELE

6、CT 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 INDEX over

7、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 FUNCTION

8、 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; func_cmd

9、 := 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_keys ORDER

10、 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_referrer_type(

11、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常用函數(shù)length(string)octet_length(string)replace(string text, from text, to text)strpos(string, substring)substr(string, from , coun

12、t)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函數(shù)例子crc64_str.sqlCREATE OR REPLACE FUNCTION crc64_str(text) RETURNS integ

13、er 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) . 分區(qū)表 Oracle: (range、list、hash) select * from sales partition (sdb_partition_20100101); 分區(qū)表

14、以邏輯表形式存在 Greenplum： (range、list) select * from sales_1_prt_sdb_partition_20100101; 分區(qū)表以物理表形式存在系統(tǒng)表/視圖 gp_configuration pg_tables pg_partition pg_partitions pg_proc pg_indexes pg_class pg_stat_activity pg_locks 對(duì)象授權(quán)Tables, Views, SequencesSELECTINSERTUPDATEDELETERULEALLDatabasesCREATETEMPORARYALLFunct

15、ionsEXECUTEProcedural LanguagesUSAGESchemasCREATEUSAGEALL注意：例如給某個(gè)角色賦予操作表的權(quán)限，必須對(duì)數(shù)據(jù)庫(kù)、模式和表同時(shí)有權(quán)限Greenplum優(yōu)化建議 使用執(zhí)行計(jì)劃 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.878.2.36

16、7 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=100) Inde

17、x 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優(yōu)化建議 用明確的 JOIN 控制規(guī)劃器 關(guān)閉自動(dòng)提交 使用 COPY 刪除索引和外鍵約束 事后運(yùn)行 VACUUM ANALYZE COPY命令COPY table (column , .) FROM fi

18、le | 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 , .) | (query)

19、 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:/seghost:port/p

20、ath/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 | PERCENT 20

21、22-3-21注意事項(xiàng)1 不能對(duì)分布字段即distributed by ()子句中的字段進(jìn)行更新 在更新表時(shí)，如果存在表關(guān)聯(lián)，那么相對(duì)應(yīng)的關(guān)聯(lián)字段都必須是對(duì)應(yīng)的distributed by 字段注意事項(xiàng)2現(xiàn)象：不支持Not exists語(yǔ)句，只支持exists語(yǔ)句結(jié)論：使用left join替代注意事項(xiàng)3現(xiàn)象：or關(guān)系不能出現(xiàn)在兩個(gè)子查詢(xún)中結(jié)論： 1、GP數(shù)據(jù)庫(kù)并不支持對(duì)多個(gè)子查詢(xún)集的or操作，遇到這類(lèi)語(yǔ)句，就要考慮用語(yǔ)句替換。 2、即使是一個(gè)子查詢(xún)查與一個(gè)非子查詢(xún)集的or操作，也是不允許的。 3、對(duì)于多個(gè)非子查詢(xún)集的or操作，是允許的。 4、對(duì)于多子查詢(xún)集的and操作，是允許的。 注意事項(xiàng)4 現(xiàn)象：Case when語(yǔ)句中不能出現(xiàn)子嵌套查詢(xún)結(jié)論： GreenPlum并不支持在case when中含有子嵌套查詢(xún)的復(fù)雜語(yǔ)句,通過(guò)left join等操作替代注意事項(xiàng)5現(xiàn)象：在ORACLE腳本中，常常會(huì)通過(guò)rowid進(jìn)行一些數(shù)據(jù)的特殊處理，如刪除重復(fù)的記錄；PostgreSQL數(shù)據(jù)庫(kù)有一個(gè)與oracle的rowid類(lèi)似的偽列oid，但會(huì)重復(fù)。結(jié)論： 總的來(lái)說(shuō)，gp沒(méi)有嚴(yán)格兼容oracle的rowid，如果segment instance不共用相同