搜索
DSQL技术培训-----王伟Wei.wang3@teradata.comDSQL简介DSQL技术培训DSQL简介TeradataCLIDynamicSQLExecutorforTeradataDSQL命令（变量定义、逻辑控制等）标准SQLDSQL+Dsql是基于teradata的CLI开发的一款应用程序，可基本替代存储过程与宏甚至函数的大部分功能DSQL简介变量•多样性:可以支持文件（例如控制文件）中拆分变量，命令行输入变量，SQL查询结果变量，自定义多值变量程序•逻辑判断灵活性:支持IF…THEN语句，支持FOR循环,支持GOTO语句，支持操作系统命令，支持简单的导入导出数据，支持文本文件输出功能。•使用方便性:程序可以给其他程序共享，日志可读性好安全•在用户登陆信息保存方面，D-SQL把密码的加密和还原都集中在D-SQL中，当参数文件中password是明文时，自动加密并保存到参数文件中。DSQL简介DSQL的特点灵活简洁高效安全DSQL技术培训DSQL安装配置及调用方法DSQL安装配置DSQL的安装•DSQL为独立运行程序，将DSQL拷贝到自己的目录下，赋予可执行权限即可。支持以下平台：•Win32•Linux-i386/x64•Solaris-sparc/HP-UX/AIX•MP-RASDSQL的运行环境要求•DSQL运行需要安装有TeradataCLI的环境。•在Windows平台，DSQL运行还需安装一个VC2013运行环境（vcredist_x86.exe）。DSQL配置及调用方法Dsql–clogon_sys.env-fscript-file[var1=txt1][var2=nn1][…]•-clogon_sys.env指定数据库登陆参数文件，其内容包括数据登陆必须的参数及工作变量初始设置。当不指定该参数时，默认使用环境变量TD_LOGON所定义的文件•-fscript-file指定要执行的DSQL脚本文件名称，当此参数未指定时，则使用标准输入。•[var1=txt1]后面可以跟任意多个工作变量初始化参数和赋值在参数文件中可定义DSQL运行时环境变量，其中登录Teradata的三项内容是必须指定的：注意：数据库用户密码长度目前要小于16位，DSQL将自动加密成32位的加密密码串DSQL配置及调用方法DSQL的配置TD_SYS=dbc#Teradata数据库IP地址或主机名TD_USER=bill#Teradata数据库用户名TD_PASS=bill#Teradata数据库用户密码示例一（DSQL调用时指定数据库登陆参数文件和脚本）示例二（使用标准输入指定要执行的DSQL脚本）示例三（通过环境变量TD_LOGON指定数据库登陆参数文件）DSQL调用方法Dsql–c/etl/logon.env–f/etl/test/test.dsqlTX_DATE=20140525Dsql–c/etl/logon.envTX_DATE=20140525/etl/test/test.dsqlexportTD_LOGON=/etl/logon.envDsqlTX_DATE=20140525/etl/test/test.dsqlDsql–f/etl/test/test.dsqlTX_DATE=20140525DSQL技术培训DSQL脚本编写DSQL脚本编写DSQL脚本编写DSQL工作变量DSQL流程控制其他技术要点DSQL脚本编写1.SQL请求常规的SQL命令文本，以“;”结束•例如：•DELFROM$TAB_NAME;2.DSQL控制命令控制命令以“.”开始，块控制命令以“..”开始，以“..END”结束•例如：•.IFvar=0THEN.QUIT0;•..IMPORTVARTEXT''FILE=$dataFileName•USING$COL_VAL•INSERTINTO$TAB_NAME($COL)VALUES($COL_VAL)•..END脚本中的两种命令类型DSQL脚本编写DSQL中的工作变量只支持String类型工作变量的名称是“大小写”敏感的,如$AA$aa•ERRORCODE:SQL执行的返回码•ERRORLEVEL:系统调用的返回码，例如.os命令的系统返回码•ACTIVITYCOUNT:SQLStatement执行有效记录数。如果是multi-SQLrequest,该变量只是最后一个SQLStatement的执行的有效记录数。•ACTIVITYCOUNTn：Multi-SQLrequest的每个Request的ActivityCount。可用于记录目标表的记录变动情况。固有的工作变量DSQL工作变量约定DSQL脚本编写DSQL工作变量的定义和赋值1)在数据库登陆参数文件（xxx.env）中定义•LOG_TABLE=ETL_LOAD_LOG•STG_DDL=STG_DDL2)SELECT命令（仅返回一条记录）给工作变量赋值•SELECTMAX(trad_date)TXDATE1FROMpdata.cmncalWHEREtrad_date‘$TX_DATE’;•SELECTTOP1trad_dateTXDATE1FROMpdata.cmncalWHEREtrad_date‘$TXDATE’ORDER1DESC;•在处理select返回的结果时，自动忽略NULL值。当返回结果为0条记录时，相应的工作变量被赋值为长度为0的字符串。DSQL脚本编写3)在DSQL调用命名中定义•Dsql–c/etl/logon.env–f/etl/test/test.dsqlTX_DATE=201405254)SELECT命令（返回多条记录）给变量赋值•当SELECT语句返回多条记录时，默认情况下工作变量内容是每个值以“，”连接起来的字符串。如果希望用其它字符串（||”，”空格”）连接，可以对一个特殊的变量（JOINSTRING）进行赋值就可以了。•SELECT'||'JOINSTRING;•SELECTColumnNameCOLSFROMDBC.Columnsx•WHEREDataBaseName='$TARGETDB'ANDTableName='$HT‘•如果不使用别名方式，返回的变量名称为原始字段名称。•DSQL自动忽略NULL，只把NOTNULL的值连接起来赋值给变量。•直接Select数值字段时，采用该字段的format转换成字符串DSQL脚本编写5).RUN子脚本调用时•.runfile=sub2.dsqlVAR1=tab1VAR2=$TEMPDB.test1•调用子脚本时还可指定传入子脚本执行的工作变量（参数）,同时子脚本与主脚本公用所有的变量。即：所有工作变量是“全局的”6)..SET/..END多行变量赋值..SETCOLScol1,col2..END•把字符串的内容进行变量替换后再赋值到目标变量中，类似SELECT命令（返回多条记录）•varList由新变量的序列组成当变量名称为”*”，则忽略该位置所对应的格式定义的值•formatString由一对双引号中内容组成，有两种形式，按指定分隔符或定长•n表示引用sourcefilename的第n行内容•示例1：.ACCEPT(*,*,*,JOBID),3$CTLFILE•示例2：.ACCEPT(JOBID)[*10][5]1$CTLFILE7).ACCEPT从文件中解析数据到变量ACCEPT(varList)formatStringnsourcefilenameDSQL脚本编写$变量引用$varName或${varName}•变量引用时，如果后面紧跟着字母、数字或下划线(_)，引用应使用${varName}方式。如果变量名称中如有特殊符号（空格等），变量的引用也要用${varName}方式参数变量引用:varName或:”varName”•当参数名称是Teradata保留字或参数名称中包含空格时，则需要使用参数变量引用，并把变量名称用两个双引号括起来•参数变量引用仅限于普通的DML语句(SEL/INS/DEL/UPD)中的常数值，不能出现在DDL/DCL语句中•参数变量方式最大好处就是，变量的值可以是任意值，比较之下效率高些。•INSERTINTO$WORKDB.tab1VALUES(:”varName”,,’abc’);DSQL工作变量的引用DSQL脚本编写变量定义变量使用ACCEPT使用DSQL脚本编写DSQL脚本编写DSQL工作变量DSQL流程控制其他技术要点DSQL脚本编写•.IFvar=0THEN.QUIT0;•.IFERRORCODE0THEN.QUIT12•.IFERRORCODE=3807THEN.GOTOlabelname•注：.IF后面的变量不加$符号.IFTHEN简单条件语句•..FORQuery-SQL•..DO•DSQL-BLOCK•.IFERRORCODE0THEN.EXIT-FOR•..END-FOR.EXIT-FOR跳出循环•DSQL中GOTO可以向前，也可以向后，这样就可以实现简单的循环。•.LABELlbl1•….•.GOTOlbl1.GOTOlabel和.LABELlabel跳转语句•DSQL休眠一段时间，sleepseconds秒钟.SLEEPseconds/.HANGsecondsDSQL脚本编写•runfile=sub2.dsqlVAR1=tab1VAR2=$TEMPDB.test1.RUN子脚本调用•退出当前返回到上层•在第一层，等价于.QUIT0;.RETURN退出当前返回到上层•从中间执行过程中退出。退出返回码为n•如果执行到脚本结束，自动返回0..QUITn退出•..FORQuery-SQL•..DO•..END-FOR..FOR/..DO/..END-FOR循环DSQL脚本编写•ERRORLEVEL返回外部命令的返回码•把..OS后面的内容作为操作系统命令，同时把从下面一行开始到..END前的内容作为标准输入，这样可以方便地调用fastload/fexp•.OSCURDIR=`pwd`.OS和..OS/..END运行外部命令•当前只支持VARTEXT分隔符文本文件，该功能只使用1个Session进行数据导入，非常适用小批量数据(10000条以下)导入。•..IMPORTVARTEXT''FILE=$dataFileName•USING$USE_COLINSERTINTO$TAB_NAME($COL)VALUES($COL_VAL)•..END..IMPORT/..END数据导入功能•数据导出功能,语句中可以引用$变量•..EXPORTVARTEXT''FILE=ex_$tbName.txt•SELECT$COLSFROM$dbName.$tbName•..END..EXPORT/..END数据导出功能•简单文本文件输出功能，w(覆盖)a(追加)。•..WRITEw$HOME/tmp/out$TD_SESSION.txt•$TD_SESSION|$dbUser•$dbDate/$dbUser•..END..WRITE/..END简单文本文件输出功能DSQL脚本编写•..IFIFCONDITIONTHEN•DSQL-BLOCK•..ELSE•DSQL-BLOCK•..END-IF•注：.IF后面的变量不加$符号,且整个体系不能有GOTO语句。绿色的..ELSE语句如没有需求可以略掉不写。控制语句的新功能..IFIFCONDITIONTHEN..ELSE..END-IF.DEFINE参数宏定义和使用定义：.DEFINEchar8_to_date(1,2=$NULLDATE,3=$ILLDATE)$HOME/char8_to_date.psql使用select&char8_to_date(f1),&char8_to_date(f2,’00000101’,’00000102’).RUN子脚本调用.RUNf