Greenplum中SQL特征

1、Consult Build Deploy ManageGreenplum SQL介紹 鄧才應現代設備（中國）有限公司北京環(huán)亞時代信息技術有限公司深圳發(fā)展銀行ODS項目組Overview Greenplum 數據庫介紹 PostgreSql語法特征 Pgsql與Plsql主要區(qū)別 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分區(qū) LIST分區(qū) -建議表記錄小于2000W不使用分區(qū)表表存儲類型 heap table、Column-Oriented table、Row-Oriented tableINDEX策略 一個表只建一個唯一索引 唯一索引全包含DK字段分析表 vacuum analyze user table； vacuum full system table；PostgreSql語

3、法特征 PostgreSQL 是一種關系型數據庫管理系統(tǒng)(RDBMS)。 Greenplum基于PostgreSQL，但也不是Postgres所有特性都能使用，比如： 沒有觸發(fā)器 不能指定表空間 不能在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, )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常用函數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函數例子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 對象授權Tables, Views, SequencesSELECTINSERTUPDATEDELETERULEALLDatabasesCREATETEMPORARYALLFunct

15、ionsEXECUTEProcedural LanguagesUSAGESchemasCREATEUSAGEALL注意：例如給某個角色賦予操作表的權限，必須對數據庫、模式和表同時有權限Greenplum優(yōu)化建議 使用執(zhí)行計劃 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ī)劃器 關閉自動提交 使用 COPY 刪除索引和外鍵約束 事后運行 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注意事項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不共用相同