文庫發(fā)布：dsql介紹

DSQLexecutorforTeradataAgendaWhyDSQLWhatisDSQLHowtouseDSQLDSQLbackgroundDSQLisdesignedin2009當(dāng)前ETL腳本模式(SDATAPDATA)BteqDML(Insert/Delete/Updata)

cannotsupportparameterinSQL-Requestdirectly.SoBteqscriptnormalyisembedintosh/perlscriptforvariablesupport.PerlDBD-ODBC/DBD-Teradata(Select)為bteq啟動(dòng)做前期準(zhǔn)備ex:colnames使得bteq的SQL語句“動(dòng)態(tài)”Dbname,TXDATEicecodeLogonpassword

encrypt/revert是否“真正”地實(shí)現(xiàn)了password的保護(hù)？TD14tdwalletisnotreadywhenDSQLwasbornWhatisDSQL？DSQLissimilartobteq,itcansubmitSQLrequesttoTeradata。DSQLScript

alsoconsistsofcontrolcommandandSQLCommand(DDL/DML/DCL)，wecanput“variable”/parameterinanywhereofSQLCommand。Theresult-setofSelectputintovariables,butbteqonlydisplyintoconsole.--獲取PIColumnSELECTColumnNamePICOLSFROMDBC.IndicesxWHEREDataBaseName='$TARGETDB'ANDTableName='$HT'ANDIndexType='P'ANDcolumnNameNOTIN('$HT_s_date','$HT_e_date')ORDERBYcolumnPosition;.IFERRORCODE<>0THEN.QUIT12;--獲取歷史表中除了s_date,e_date以外，其它字段SELECTColumnNameCOLSFROMDBC.ColumnsxWHEREDataBaseName='$TARGETDB'ANDTableName='$HT'ANDcolumnNameNOTIN('$HT_s_date','$HT_e_date')ORDERBYColumnID;.IFERRORCODE<>0THEN.QUIT12;--創(chuàng)建當(dāng)日表PI與歷史表同CREATEVOLATILEMULTISETTABLE$NDAS(SELECT$COLSFROM$TARGETDB.${HT})WITHNODATAPRIMARYINDEX($PICOLS)ONCOMMITPRESERVEROWS;.IFERRORCODE<>0THEN.QUIT12;StartupmethodofDsqlDsql–c<logon_sys.env>-f<script-file>[v1=txt1][v2=nn1][…]<logon_sys.env>isaconfigurefileofconnectiontoteradata,ithastoinclude3line：

TD_SYS=dbc TD_USER=bill TD_PASS=bill #Automaticencrypt。

#len<=16

regardasplaintext,afterencryptthe32characters.Dsql腳本中包含兩類命令控制命令,與Bteq一樣以“.”開始SQLRequest命令，非以“.”開始HowtouseDSQLWorkvariableinDSQL VariableofDSQLisonlysupportstringtype，itmayatanywhereinDSQLscript。 Referencemethodofvariablehastwostyle:1)‘value’likeassh/perl($var1

or

${var1}),whensubmittheSQLrequest,DSQLreplaceall$varintothevalueofthem2)‘Parameter:var1’addusingclauseatthebeginningofSQLrequest,onlysupportDML(Insert/Update/Delete).WhatisvariableofDSQL?Howtoassignintoavariable?

1)Fromconnectionconfigurefile

2)Fromstartupcommand-line

3)Fromtheresult-setofSQLselect，thenamewillbecolumn-name，thevaluewillbe“column-content”；Whentheresult-sethasmulti-rows,thevalueofvariablewillbeconcatenationofallrowswithjoincharacter(thedefaultiscomma”,”)

4)Whencallsub-script .runfile=<sub_script.dsql>V5=‘a(chǎn)bc’V6=’22’WhatisvariableofDSQL?DSQLcontrolcommandalmostlikeasbteq,butithasmanyextension.DSQLcontorlcommand

1).IFmayreferenceallvariableandthreespecialvariable ERRORCODE,ERRORLEVEL,ACTIVITYCOUNT

2).Goto/.Label .GOTOmaybackword.SoDSQLsupportloop.

2).RunFilecallsub-scriptofDSQL

3).Return子腳本返回

4).QuitDSQL結(jié)束命令

5).OS操作系統(tǒng)功能調(diào)用

6).DefineSQL-Block定義命令and&call 7)..OS/..END…Whatiscontrol-commandofDSQLWhatisadvantageofDSQL?ELT

scripthasa“single”executionenvironment.Noneedperl/shell,Noneedsed,NoIceCode,etc.Scriptchangedinto“Dynamic”/”Activated”SQLScriptmaybechangedintosimpleandreadableMayputsinglerowresult-setintovariable,Insertintotarget1(f1,f2)selectf1,f2fromsrc1,(selectmax(f2)maxf2fromsrc2)MWheref2=maxf2-------------Selectmax(f2)maxf2fromsrc2;Insertintotarget1(f1,f2)Selectf1,f2Wheref2=$maxf2Knowledgeshare

willbeveryeasy"abstract"thealgorithmintoprocesspatternCallpatternwithdifferentparameter .runfile=<subFunction.dsql>…Ex. .runfile=$HOME/bin/insert_his.dsqlHT=‘tab1’PseudoUDFparameterSQLBlock,itcanbecallfrommanyDSQLscript..Definechar8_to_date(1,…)Select&char8_to_date(f1), &char8_to_date(f2)WhatisadvantageofDSQL?(continue)DSQLSample1(char8_to_date)--char8_to_date:ConvertstringdataintodatebaseDATEformat"YYYYMMDD"--Paramenter1:Inputdata(CHAR,len>=8)--Paramenter2:(NULL-DATEvalue,whenP1isblank)--Paramenter3:(ILL-DATEvalue,WhenP1isinvalidated當(dāng)參數(shù)1內(nèi)容不是合法日期時(shí)，要轉(zhuǎn)換成的日期ILLDATECASE WHENCHARACTERS(TRIM($1))=0THEN$2-19000000 WHENCHARACTERS(TRIM($1))<>8 ORSUBSTR($1,1,1)<'0'ORSUBSTR($1,1,1)>'9' ORSUBSTR($1,2,1)<'0'ORSUBSTR($1,2,1)>'9' ORSUBSTR($1,3,1)<'0'ORSUBSTR($1,3,1)>'9' ORSUBSTR($1,4,1)<'0'ORSUBSTR($1,4,1)>'9' ORSUBSTR($1,5,1)<'0'ORSUBSTR($1,5,1)>'9' ORSUBSTR($1,6,1)<'0'ORSUBSTR($1,6,1)>'9' ORSUBSTR($1,7,1)<'0'ORSUBSTR($1,7,1)>'9' ORSUBSTR($1,8,1)<'0'ORSUBSTR($1,8,1)>'9' ORSUBSTR($1,1,4)='0000'ORSUBSTR($1,5,2)='00' ORSUBSTR($1,7,2)='00' THEN$3-19000000 ELSE( CASE WHEN(CAST(SUBSTR($1,7,8)ASINTEGER)<29ANDSUBSTR($1,5,2)='02') OR(CAST(SUBSTR($1,7,8)ASINTEGER)<31ANDSUBSTR($1,5,2)<>'02'ANDSUBSTR($1,5,2)<=12) OR(CAST(SUBSTR($1,7,8)ASINTEGER)=31ANDSUBSTR($1,5,2)IN('01','03','05','07','08','10','12')) THEN $1-19000000 WHENSUBSTR($1,5,4)='0229' AND(CAST(SUBSTR($1,1,4)ASINTEGER)MOD400=0OR (CAST(SUBSTR($1,1,4)ASINTEGER)MOD4=0ANDCAST(SUBSTR($1,1,4)ASINTEGER)MOD100<>0)) THEN $1-19000000 ELSE$3-19000000 END ) END11>DSQLSample1--DSQLDemoScript.DEFINEchar8_to_date(1,2=$NULLDATE,3=$ILLDATE)$HOME/APP/public/char8_to_date.psql.DEFINEdecimal_to_date(1,2=$NULLDATE,3=$ILLDATE)$HOME/APP/public/decimal_to_date.psql--Createtoday(test_t)Tablebasetest_his(excepts_date/e_date)andwithsamePIcolumns.RUNFILE=$HOME/APP/public/crtDailyTable.dsqlHT='test_his'ND='test_t'INSERTINTOtest_t($COLS)SELECT'C'||f1,&char8_to_date(cf2) --test_c該表中cf2字段是char(8)類型FROM${SOURCEDB}.test_c;.IFERRORCODE<>0THEN.QUIT12;.RUNFILE$HOME/APP/public/insert_his.dsqlHT=test_hisND=test_tMASTABLE=''.QUIT0;12>DSQLSample2(ExtractFRRDBMSandLoadintoTeradata)13>Mission: Fromsourcesystem(RDBMS)extractanddirectlyFastloadintoTeradata.Mechanism: DataExtract.jarextractfromsourceRDBMSintoFastloadindicatorformat,typeofallcolumnisvarcharandputrecordsinto<stdout>. Access-module(amExtPgm.so)likeabridgejoinanexternalProgram-processandFastloadtogether.Itreaddatafromthepipeofexternalprogramstdout,andpassthedatatoFastload.Advantage: Nodata-landing,Nodelimiter,Nocolumnsplit. DataExtract.jarconnectstosourcesystemviaJDBC,multi-platformsupportisveryeasy.DSQLSample2(ExtractfromRDBMSandLoadintoTeradata)14>--ParamentdbName,andtbNameDROPTABLE$dbName.$tbName;DROPTABLE$dbName.${tbName}_E1;DROPTABLE$dbName.${tbName}_E2;CREATEMULTISETTABLE$dbName.$tbNameas$SDDL.$tbNamewithnodata;.IFERRORCODE<>0THEN.QUIT12;SELECTtrim(columnname)||'(VARCHAR('||CASEWHENcolumntypelike'C_'THENtrim(columnlength)ELSE'40'END||'))'||'0a'xcDEF_COLS,':'||trim(columnname)COL_VALUESFROMdbc.columnsWHEREdatabasename='$dbName'ANDtablename='$tbName'orderbycolumnid;.IFERRORCODE<>0THEN.QUIT12;--Startfastlo