gp db开发相关知识GreenplumDB数据库

GreenplumDB GPDB数据库部署结构及运算规 GPDB建分布 创建 修改表结 ORACLE中有而GP数据库中没有的函 IF语句用 LOOP，WHILE，FOR循环语句用 While循环语句用 FOR循环用 移植/ETL开发过程中的注意事 GPDB数据加 备份数据 表数据清 GP分区 GP数据库下建立用户及 GPDB的SQL程序开发优化规 GPMaste服务器切换流 GPDB(Figure1.1GP数据库部署结构图Masterhost:存放所有表的全 （在数据库安装时，仅在Master主机上安装即可，但是预配置信息，除了主备Masterhostsegmenthost上以同样的方式进行配置【配置步骤，，SegmentStandbyMasterMasterhostSegmentHost:存放各个分布表的数据记录集合(即：所有表都存放在segmenthostMasterHost(Figure1.2:表分布在GP数据库中的物理分布情况(Figure1.3:GP并行查询内部运行流程图(Figurerace（空时进，要可需停外部以证导所的源导同个点的T）(Figure2.1：GreenplumParallelFileServerL,G,M,C,IWGPDBSegmentHostHashDistribution分布键个数大或等于1个column,并被hashhashsegment（2Random(Figure3.1：HashDistribution与RandomDistribution别 table_name integerPRIMARYKEY varchar(40)NOTNULLCHECK(column_3<> char(5)--为column_4字段定义一个唯一约束; constraint_name1CHECK(column_1>100ANDcolumn_2<> DISTRIBUTEDBYcolumn_1 tabletable_name1( 或 tabletable_name1( old_column_nameto altertabletable_nameaddcolumncolumn_namecolumn_type或altertabletable_nameaddcolumncolumn_namecolumn_typenotnulldefault column_namesetdefault修改列名/修改列名并设置为非空限制/ 或 column_namesetnotnull;或altertabletable_namealtercolumn_namedropnot column_namedrop设置字段模【in:用于定长的数值,内联且不压缩的；External:用于外部保存，不压缩的数据；EXTENDED:用于外部的压缩数据;altertabletable_namealtercolumncolumn_namesetstorage{in|external|extended|main} altertabletable_nameold_owner_nameB_TREE唯一性索引：B_TREE复合索引create where或groupbycolumn_name1column_name2column_name3全,B_TREE函数索引BIT_MAP函数索引R_TREE索引HASH索引:createindexindex_nameontable_nameusinghash(column_name);【仅在使用’=’操作时可以使用，但是效率很差,B_TREE索引代替】tablepace_namealtertablespaceold_tablespace_namerenametoaltertablespacetablespace_nameold_owner_nametoGPORACLE数据库数据类型/临时表/序列号/关联查询/函数用/REAL/DOUBLEdate(includestoSELECT1+1FROMSELECT1+1;1).创建同名的临时表如下：CREATEVIEWdualASSELECT'X'::VARCHAR(1)ASDUMMY;(2).在改造SELECT1+1FROMdual;(注:本文GPFROMFROMSELECT*FROMTABLE_NAMEWHERECODEISNOTNULLANDROWNUM<=5ORDERBYCODESELECT*FROMTABLE_NAMEWHERECODEISNOTNULLORDERBYCODEDESCLIMIT5OFFSETSELECTA.COL1A_COL1,A.COL2A_COL2FROMTABLE_NAMESELECTA.COL1ASA_COL1,A.COL2ASA_COL2FROMTABLE_NAMESELECT*FROMA_TABLEA,B_TABLEBWHERESELECT*FROMA_TABLEARIGHTOUTERJOINB_TABLEBONSELECT*FROMA_TABLEA,B_TABLEBWHEREANDSELECT*FROMA_TABLEARIGHTOUTERJOINB_TABLEBONA.ID=B.IDANDSELECT*FROMA_TABLEA,B_TABLEB,C_TABLEC,D_TABLEDWHEREA.ID=B.ID(+)ANDA.ID=C.ID(+)ANDSELECT*FROM(A_TABLEALEFTOUTERJOINB_TABLEBONA.ID=B.ID)LEFTOUTERJOINC_TABLEONA.ID=C.ID,D_TABLEDWHEREA.COL1=D.COL1SELECT*FROMA_TABLEAWHEREA.COL1(+)=0ANDA.COL2(+)='A_VALUE2'SELECT*FROMA_TABLEAWHEREA.COL1=0ANDSELECTNVL(SUM(VALUE11),0)NVL(SUM(VALUE21),0)FS_VALUE2FROMFIELD_SUMSELECTCOALESCE(SUM(VALUE11),0)ASFS_VALUE1,ASFROMSELECTCOL1FROMORDERBYSELECTCOL1FROMA_TABLEORDERBY[note：'999999'islengthofFROMSELECT(CASEWHEN'1'THEN'A'ELSE'B'END)ASENDFLAGFROMUPDATESETUPDATESETUPDATESETENTREDATE=TO_TIMESTAMPSELECTTO_DATE(SYSDATE,'YYYY-ASFROMSELECTTO_DATE(CURRENT_DATE,'YYYY-ASFROMSELECTCEIL(SYSDATE-TO_DATE('14:56:10','YYYYMMDDHH24:MI:SS'))ASDAYSFROMSELECTEXTRACT(DAYFROMDD-HH24-MI-SS')-TO_TIMESTAMP('2005-10-2714:56:10','YYYY-MM-DD-HH24-MI-))+1ASDAYSFROMDUAL[‘’SELECTLENGTH('')ASVALUE1FROMDUALSELECTLENGTH('')ASVALUE1FROMDUALoraclenotsupportIFIF...IF...THEN...IF...THEN...ELSEIF...THEN...ELSIF...THEN...IF...THEN...ELSEIF...THEN...IFv_user_id<>0UPDATEusersSET=v_WHEREuser_id=v_user_id;ENDIF;IFparentidISNULLORparentid=''

RETURNRETURNhp_true_filename(parentid)||'/'||ENDIFv_count>0INSERTINTOusers_count(count)VALUES(v_count);RETURN't';RETURNENDIFdemo_row. ='m'THEN :='man';IFdemo_row. ='f'THENpretty_:='woman';ENDENDIFnumber=0THENresult:=ELSIFnumber>0THENresult:='positive';ELSIFnumber<0THENresult:='negative';

result:=END【if...then...;elsif..then..;else..endif;类型语句在使用时，有可能会出现可编译但是无法执行的情况，casewhen…then…else…end方式来表示】LOOP，WHILE，FOR--IFcount>0THENEXIT;--exitloopENDIF;ENDLOOP;--EXITWHENcount>0;ENDLOOP;END.WHILEamount_owed>0ANDgift__balance>0--ENDENDENDFORiIN1..10--RAISENOTICE'iIS%',i;ENDLOOP;FORiINREVERSE10..1--END[...CREATETYPEcomplexAS double doubleCREATETYPEinventory_itemAS( CREATETABLEon_hand INSERTINTOon_handVALUES(ROW('fuzzydice',42,1.99),CREATEFUNCTIONprice_extension(inventory_item,integer)RETURNSnumericAS'SELECT$1.price*$2'LANGUAGESQL;SELECTprice_extension(item,10)FROM移植/ETL（将Oracle的特殊SQL语法转换为GreenplumDB能识别的ANSI】GPETL中的Oracle创建临时表SQL语句，需要修改，注意数据类型、数据分布等Oracle事务，CommitANSI语法OracleOLAPRANK，GROUPBYGreenplumDBOLAP语法DistributionKeyGreenplum的物理模型；Oracle中的过程，需要转换到Greenplum中的函数（Function），因为GP中没procedurepackageselect/update/deleteinsert时使用不到索引,因此在进行大批量数在过程(SP)编写过程中，在需要用到like过滤的时候，需使GPGP中自定义的函数或变量名等待是区分大小写OracleGP中没有的自己开发的公共函数命名采用:oracle绑定函数相in做限制操作的部分，能够使用existsnotexists代替的，尽量改成这并集用法UNION:query1UNIONquery2query1UNIONquery2UNIONquery3得到结果集UNIONALL:query1UNIONALLquery2得到结果集的并集，包含重复值交集用法:里的记录信息【注:INTERSECTALL差集用法EXCEPT:通过query1EXCEPT[ALL]query2得到query1里存在query2里不存在的记录信息【注:如果使用EXCEPT ALL方式，将会包含重复值】;GPDB :cd./usr/local/greenplum-db-psax|grepgpfdist(LinuxGPLinuxps–ef|grepgpfdist(Solaris)kill(gpfdist):killVERSION:1.0.0.1【注释：(Optional.)gpload1.0.0.1.】DATABASE:db_name【注释：(Optional.)gploadUSER:db_username(Optionalgploadgpload程序的用户不是superusergp_external_grant_privileges=on】HOST:master_hostname【注释：(Optional.)master主机名,默认localhost】PORT:master_port 【注释：(Optional.)master主机端口,默认5432】 【注释：(Required.)gpload语句】 - - PORT: |PORT_RANGE:[start_port_range,end_port_range](Optional.) 【注释：(Required.)所要加载源文件语句 field_name: FORMAT:text| -DELIMITER: ESCAPE:'escape_character'|OFF'(OptionalNULL_AS:'null_string' _NOT_NULL:true| QUOTE: ERROR_LIMIT:integer 【注释： 【注释：(Required.)gpload输出语句TABLE:schema.table_name inputMODE:insert|update|merge(Optional.)insert 【注释：(Required.updatemerge '【注释：( TRUNCATE:true|false【注释：(OptionaltrueBEFORE: mand"【注释：(Optional.)sqlAFTER: 【注释：(Optional.)sql命令在加载完成后运行】)控制文件名:--VERSION:1.0.0.1DATABASE:opsUSER:gpadminHOST:mdw-1PORT:5432PORT:8081name:amount:category:desc:date:FORMAT:DELIMITER:ERROR_LIMIT:ERROR_TABLE:public.err_expensesTABLE:MODE:INSERTBEFORE:"INSERTINTOauditVALUES('start',AFTER:"INSERTINTOauditVALUES('end',三.启动加载进程（linuxyml：gpload-fcreatetablenew_tablenameasselect*from或select*intonew_tablenamefromold_tablenamewhereselect*intonew_tablenamefromold_tablenamewhereselect'createtableback_'||tablename||'asselect*from'||tablename||';'frompg_catalog.pg_tablestwheret.schemaname='public';或select'select*intoback_'||tablename||'from'||tablename||'where1=1;'pg_catalog.pg_tablestwhereselect'select*intoback_'||tablename||'from'||tablename||'where1<>1;'frompg_catalog.pg_tablestwheret.schemaname='public';$pg_dumpmydb>$psql-ddatabase-fmydbBLOBtar$pg_dump-Ft-bmydb>把这个数据库（连同BLOB）newdb$pg_restore-dnewdb转储所有数据库(pg_dumpall$pg_dumpall> YZE。这样优化器就可以得到有用的统计。你也可以用vacuumdb-a-z清理所有数据库。VACUUM用法(VACUUM回收已删除元组占据的空间VACUUM[FULL ZE][VERBOSE][tableVACUUM[FULL ZE][VERBOSE]YZE[table[(column[,...])]VACUUMYZE先执行一个VACUUM然后是给每个选定的表执行一个Vacuumdb(用于整理PostgreSQLvacuumdb[connection-option...][--full|-f][--verbose|-v][- yze|-z][--table|-t[(column[,...])]]TRUNCATEDELETE它不做表扫描，因而快得多。在大表上最有用。TRUNCATE不会运行任何在该表上存在的ONDELETE触发器。DELETE用法说明GPGP超级管理员用户建立及修改(GPDB管理员使用如果可以减少IO的数据量，将可以提升性能。采用压缩、按列都可以减IO的数据量，从而提升性能，这是一个基本的办法。IO消耗。因此，以下原则可以应用于Greenplum尽可能使压缩、列虽然从能上会失PE和T,但