Greenplum中SQL特征.

Consult•Build•Deploy•ManageGreenplumSQL介绍—邓才应现代设备（中国）有限公司北京环亚时代信息技术有限公司深圳发展银行ODS项目组Overview•Greenplum数据库介绍•PostgreSql语法特征•Pgsql与Plsql主要区别•Pgsql操作注意事项Greenplum数据库介绍•MassivelyParallelProcessing(MPP)DBMS•基于开源的PostgreSQL1postgres(master)+npostgres(instance)externaltables/parallelloadingcopycommandresourcemanagementqueryoptimizerenhancementsGreenplum特性•DK分布HASH分布避免数据倾斜随机分布不能建立唯一索引•PARTITION类型RANGE分区LIST分区--建议表记录小于2000W不使用分区表•表存储类型heaptable、Column-Orientedtable、Row-Orientedtable•INDEX策略一个表只建一个唯一索引唯一索引全包含DK字段•分析表vacuumanalyzeusertable；vacuumfullsystemtable；PostgreSql语法特征•PostgreSQL是一种关系型数据库管理系统(RDBMS)。•Greenplum基于PostgreSQL，但也不是Postgres所有特性都能使用，比如：没有触发器不能指定表空间不能在function中建立新的事务保存点不推荐使用外键和索引(特殊情况除外)DataType/CommandDataType/CommandORACLEPOSTGRESNumericNUMBER(p,s)SMALLINT(2bytes)INTEGER(4bytes)BIGINT(8bytes)DECIMAL(p,s)(11+p/2bytes)NUMERIC(p,s)(11+p/2bytes)REAL(4bytes)DOUBLE(8bytes)SERIAL(4bytes)BIGSERIAL(8bytes)CharacterCHARandNCHARCHARVARCHAR2andNVARCHAR2VARCHARDate&TimeDateFunctionsDATE(includestimetosec)DATEorTIMESTAMPwithoutTimeZoneTIMESTAMPTIMESTAMPINTERVALINTERVAL/TIMETRUNC(date)DATE_TRUNC(‘day’,date)TRUNC(sysdate)current_dateSysdatecurrent_timestampornow()sysdate+1.5current_timestamp+’36hours’::intervalBinaryBFILE(1GB)LargeObjects(upto2GB)RAWBFILE(1GB)BYTEACLOBandNCLOBTEXTDataType/CommandDataType/CommandORACLEPOSTGRESSequencesSELECTmy_sequence_name.nextvalFROMdual;SELECTnextval(‘my_sequence_name’);SQLNoimplied‘AS’ANSIJoins–no(+)forouterjoinsRownum=Limit&OffsetROWIDOID（不推荐使用）NVL()COALESCE()Union，intersect，minusUnion，intersect，exceptDECODE()CASEexpressionWHENCASEWHENexpressionSELECTSUM(*)TOTALFROMtab1;SELECTSUM(*)ASTOTALFROMtab1;SELECT*FROMa,bWHEREa.col1=b.col1(+);SELECT*FROMaLEFTJOINbONa.col1=b.col1;SELECT*FROM(SELECTidFROMaORDERBYname)WHERErownum8ANDrownum=20;SELECTidFROMaORDERBYnameLIMIT12OFFSET8;PartitionsOneINDEXoveralldataOneINDEXforeachpartitionFunction•ORACLEProcedurefunction•GreenplumfunctionOracle例子•CREATEORREPLACEPROCEDUREcs_update_referrer_type_procIS•CURSORreferrer_keysIS•SELECT*FROMcs_referrer_keys•ORDERBYtry_order;•func_cmdVARCHAR(4000);•BEGIN•func_cmd:='CREATEORREPLACEFUNCTIONcs_find_referrer_type(v_hostINVARCHAR,•v_domainINVARCHAR,v_urlINVARCHAR)RETURNVARCHARISBEGIN';•FORreferrer_keyINreferrer_keysLOOP•func_cmd:=func_cmd||•'IFv_'||referrer_key.kind•||'LIKE'''||referrer_key.key_string•||'''THENRETURN'''||referrer_key.referrer_type•||''';ENDIF;';•ENDLOOP;•func_cmd:=func_cmd||'RETURNNULL;END;';•EXECUTEIMMEDIATEfunc_cmd;•END;•/Greenplum例子CREATEORREPLACEFUNCTIONcs_update_referrer_type_proc()RETURNSvoidAS$func$DECLAREreferrer_keyRECORD;func_bodytext;func_cmdtext;BEGINfunc_body:='BEGIN';FORreferrer_keyINSELECT*FROMcs_referrer_keysORDERBYtry_orderLOOPfunc_body:=func_body||'IFv_'||referrer_key.kind||'LIKE'quote_literal(referrer_key.key_string)||'THENRETURN'||quote_literal(referrer_key.referrer_type)||';ENDIF;';ENDLOOP;func_body:=func_body||'RETURNNULL;END;';func_cmd:='CREATEORREPLACEFUNCTIONcs_find_referrer_type(v_hostvarchar,v_domainvarchar,v_urlvarchar)RETURNSvarcharAS'||quote_literal(func_body)||'LANGUAGEplpgsql;';EXECUTEfunc_cmd;END;$func$LANGUAGEplpgsql;Greenplum常用函数•length(string)•octet_length(string)•replace(stringtext,fromtext,totext)•strpos(string,substring)•substr(string,from[,count])•trim(string)•lpad(stringtext,lengthint[,filltext])•rpad(stringtext,lengthint[,filltext])•to_char•to_date•to_number•to_timestamp•date_part(text,timestamp)•date_trunc(text,timestamp)•extract(fieldfromtimestamp)•。。。Greenplum自定义C函数例子crc64_str.sqlCREATEORREPLACEFUNCTIONcrc64_str(text)RETURNSintegerAS'/usr/local/greenplum-db/lib/cgcrc64','crc64_str'LANGUAGE'C';Cgcrc64.C#includestdio.h#includectype.h#includestring.h#includepostgres.h#includestring.h#includefmgr.hPG_FUNCTION_INFO_V1(crc64_str);Datumcrc64_str(PG_FUNCTION_ARGS){...}分区表•Oracle:(range、list、hash…)select*fromsalespartition(sdb_partition_20100101);分区表以逻辑表形式存在•Greenplum：(range、list)select*fromsales_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,SequencesSELECTINSERTUPDATEDELETERULEALL•DatabasesCREATETEMPORARYALL•FunctionsEXECUTE•ProceduralLanguagesUSAGE•SchemasCREATEUSAGEALL注意：例如给某个角色赋予操作表的权限，必须对数据库、模式和表同时有权限Greenplum优化建议•使用执行计划EXPLAINANALYZESQLEXPLAINSQLEXPLAINANALYZESELECT*FROMtenk1t1,tenk2t2WHEREt1.unique1100ANDt1.unique2=t2.unique2;QUERYPLAN-----------------------------------------------------------------------------------------------------------------------------------NestedLoop(cost=2.37..553.11rows=106width=488)(actualtime=1.392..12.700rows=100loops=1)-BitmapHeapScanontenk1t1(cost=2.37..232.35rows=106width=244)(actualtime=0.878..2.367rows=100loops=1)RecheckCond:(unique1100)-BitmapIndexScanontenk1_unique1(cost=0.00..2.37rows=106width=0)(actualtime=0.546..0.546rows=100loops=1)IndexCond:(unique1100)-IndexScanusingtenk2_unique2ontenk2t2(cost=0.00..3.01rows=1width=244)(actualtime=0.067..0.078rows=1loops=100)Index