DSQL介绍16.pptVIP

DSQL executor for Teradata Agenda Why DSQL What is DSQL How to use DSQL DSQL background DSQL is designed in 2009 当前ETL脚本模式(SDATA?PDATA) Bteq DML(Insert/Delete/Updata) can not support parameter in SQL-Request directly. So Bteq script normaly is embed into sh/perl script for variable support. Perl DBD-ODBC/DBD-Teradata(Select) 为bteq启动做前期准备 ex: colnames 使得bteq的 SQL语句“动态” Dbname, TXDATE icecode Logon password encrypt/revert 是否“真正”地实现了password的保护？ TD14 tdwallet is not ready when DSQL was born What is DSQL？ DSQL is similar to bteq, it can submit SQL request to Teradata。DSQL Script also consists of control command and SQL Command(DDL/DML/DCL)，we can put “variable”/parameter in anywhere of SQL Command。 The result-set of Select put into variables, but bteq only disply into console. -- 获取 PI Column SELECT ColumnName PICOLS FROM DBC.Indicesx WHERE DataBaseName=$TARGETDB AND TableName=$HT AND IndexType=P AND columnName NOT IN($HT_s_date,$HT_e_date) ORDER BY columnPosition; .IF ERRORCODE0 THEN .QUIT 12; -- 获取历史表中除了 s_date,e_date以外，其它字段 SELECT ColumnName COLS FROM DBC.Columnsx WHERE DataBaseName=$TARGETDB AND TableName=$HT AND columnName NOT IN($HT_s_date,$HT_e_date) ORDER BY ColumnID; .IF ERRORCODE 0 THEN .QUIT 12; -- 创建当日表 PI 与历史表同 CREATE VOLATILE MULTISET TABLE $ND AS (SELECT $COLS FROM $TARGETDB.${HT} ) WITH NO DATA PRIMARY INDEX($PICOLS) ON COMMIT PRESERVE ROWS; .IF ERRORCODE0 THEN .QUIT 12; Startup method of Dsql Dsql –c logon_sys.env -f script-file [v1=txt1] [v2=nn1] […] logon_sys.env is a configure file of connection to teradata, it has to include 3 line： TD_SYS = dbc TD_USER=bill TD_PASS=bill # Automatic encrypt。 # len = 16 regard as plain text, after encrypt the 32 characters. Dsql脚本中包含两类命令 控制命令, 与Bteq一样以“.”开始 SQL Request 命令，非以“.”开始 How to use DSQL Work variable in DSQL Variable of DSQL is only support string type，it may at anywhere in DSQL script。 Reference method of variable has two style: 1) ‘value’ like as sh/perl($var1