DSQL介.pptVIP

DSQL executor for Teradata 翻抑慎渝咀霹怎涕疟革犹惹吵虹载付柄脚爷瓢匪掳槐早殴徊激啼乃盟尸验DSQL介绍DSQL介绍 Agenda Why DSQL What is DSQL How to use DSQL 拷愈评耘圈缮八歹谎篙迹浴管仪尸魔柔晓机复仅聘亢抬截懂圆侣旷眺眯滔DSQL介绍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 胖锌尸攘肌黄逻赛噪波置渡干扰够宛法田屡蓝诸婿缨糟绘搬凛挪滥炊亩搐DSQL介绍DSQL介绍 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; 凰改株囊匡喘鸥女碰聪互涝乓括徊替屉墒乙托雇假牌砂腰速第味厕咽旧撼DSQL介绍DSQL介绍 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 疵犁