Teradata高级文档.docx
- 文档编号:23776010
- 上传时间:2023-05-20
- 格式:DOCX
- 页数:74
- 大小:84.68KB
Teradata高级文档.docx
《Teradata高级文档.docx》由会员分享,可在线阅读,更多相关《Teradata高级文档.docx(74页珍藏版)》请在冰豆网上搜索。
Teradata高级文档
TeradataSQL调优
1.优化过程:
依照运行时间,数据量和复杂度来定位瓶颈。
查看sql执行计划,判断其合理性。
性能监控==》目标选取==》性能分析==》过程优化==》运行跟踪(性能监控)
注意:
每个过程中都会产生必须的文档
2.性能分析:
?
ReviewPDM--表定义--PI的选择--表的记录数与空间占用?
ReviewSQL--关联的表--逻辑处理复杂度--整体逻辑--多余的处理?
测试运行--响应时间?
查看EXPLAIN--瓶颈定位
3.过程优化:
?
业务规则理解--合理选取数据访问路径?
PDM设计--调整PDM?
SQL写法不优化,忽略了Teradata的机理与特性--调整SQL?
Teradata优化器未得到足够的统计信息--CollectStatistics
4.MultipleInsert/select-->Multi-StatementInsert/Select*并行插入空表不记录TransientJournal*充分利用Teradata向空表Insert较快以及并行操作的特性如:
?
现状INSERTINTO${TARGETDB}.DES(Party_Id,Party_Name...)SELECT…FROMSRC1;INSERTINTO${TARGETDB}.DES(Party_Id,Party_Name...)SELECT…FROMSRC2;INSERTINTO${TARGETDB}.DES(Party_Id,Party_Name...)SELECT…FROMSRC3;说明:
串行执行,多个Transaction?
优化后:
INSERTINTO${TARGETDB}.DES(Party_Id,Party_Name...)SELECT…FROMSRC1;INSERTINTO${TARGETDB}.DES(Party_Id,Party_Name...)SELECT…FROMSRC2;INSERTINTO${TARGETDB}.DES(Party_Id,Party_Name...)SELECT…FROMSRC3;说明:
并行执行,单个Transaction
5.Insert/SelectwithUnion/Unionall-->Multi-StatementInsert/Select*Union需要排除重复记录,Unionall虽不需要排重,但都需要占用大量的Spool空间,都需要进行重新组织数据如:
现状:
INSERTINTO${TARGETDB}.DES(Party_Id,Party_Name...)SELECT…FROMSRC1;UNIONALLSELECT…FROMSRC2;UNIONALLSELECT…FROMSRC3;…调整后:
INSERTINTO${TARGETDB}.DES(Party_Id,Party_Name...)SELECT…FROMSRC1;INSERTINTO${TARGETDB}.T01_DES(Party_Id,Party_Name...)SELECT…FROMSRC2;INSERTINTO${TARGETDB}.T01_DES(Party_Id,Party_Name...)SELECT…FROMSRC3;
6.排除重复记录*针对单表内的重复记录使用ROW_NUMBER函数排重*排重方式多了一层子查询*增加了大量的数据重新分布的时间现状:
……INSERTINTO${TARGETDB}.T01_INDIV(Party_Id,Party_Name...)SELECTCOALESCE(b1.Party_Id,'-1'),COALESCE(TRIM(b1.Party_name),'')...FROM(selectparty_idparty_name,…,ROW_NUMBER()OVER(PARTITIONBYParty_IdORDERBYParty_Name)asrownumfrom${TEMPDB}.T01_INDIVb1…)AAwhereAA.rownum=1……建议做法:
INSERTINTO${TEMPDB}.T01_INDIV…INSERTINTO${TEMPDB}.T01_INDIV………INSERTINTO${TARGETDB}.T01_INDIV(Party_Id,Party_Name...)SELECTparty_idparty_name,…From${TEMPDB}.T01_INDIVb1QualifyROW_NUMBER()OVER(PARTITIONBYParty_IdORDERBYParty_Name)=1?
运用Qualify+ROW_NUMBER函数?
SQL语句简洁明了?
避免子查询优化前explain:
……4)Wedoanall-AMPsSTATFUNCTIONstepfromPTEMP.VT_T01_INDIV_curbywayofanall-rowsscanwithnoresidualconditionsintoSpool5(LastUse),whichisassumedtoberedistributedbyvaluetoallAMPs.TheresultrowsareputintoSpool3(all_amps),whichisbuiltlocallyontheAMPs.5)Wedoanall-AMPsRETRIEVEstepfromSpool3(LastUse)bywayofanall-rowsscanintoSpool1(all_amps),whichisbuiltlocallyontheAMPs.Theresultspoolfilewillnotbecachedinmemory.ThesizeofSpool1isestimatedwithnoconfidencetobe6,781,130rows.Theestimatedtimeforthisstepis16.01seconds.6)Wedoanall-AMPsRETRIEVEstepfromSpool1(LastUse)bywayofanall-rowsscanwithaconditionof("ROWNUMBER=1")intoSpool8(all_amps),whichisredistributedbyhashcodetoallAMPs.ThenwedoaSORTtoorderSpool8byrowhash.Theresultspoolfilewillnotbecachedinmemory.ThesizeofSpool8isestimatedwithnoconfidencetobe6,781,130rows.Theestimatedtimeforthisstepis1minute.7)Wedoanall-AMPsMERGEintoPDATA.T01_INDIVfromSpool8(LastUse).优化后explain:
……4)Wedoanall-AMPsSTATFUNCTIONstepfromPTEMP.VT_T01_INDIV_curbywayofanall-rowsscanwithnoresidualconditionsintoSpool5(LastUse),whichisassumedtoberedistributedbyvaluetoallAMPs.TheresultrowsareputintoSpool3(all_amps),whichisbuiltlocallyontheAMPs.5)Wedoanall-AMPsRETRIEVEstepfromSpool3(LastUse)bywayofanall-rowsscanwithaconditionof("Field_10=1")intoSpool1(all_amps),whichisredistributedbyhashcodetoallAMPs.ThenwedoaSORTtoorderSpool1byrowhash.Theresultspoolfilewillnotbecachedinmemory.ThesizeofSpool1isestimatedwithnoconfidencetobe6,781,130rows.Theestimatedtimeforthisstepis1minute.6)Wedoanall-AMPsMERGEintoPDATA.T01_INDIVfromSpool1(LastUse).
BTEQ中不能用length函数LENGTH()不是Teradata的标准函数,但是TeradataSQLAssitant支持它。
今天在bteq中用此函数,报错,搞半天,终于知道原因之所在。
具体操作如下:
SELECTCASEWHENETL_JOBLIKE'CHK_%'THENSUBSTR(TRIM(etl_job),5,LENGTH(TRIM(etl_job))-4)ELSEETL_JOBENDetl_job,CAST('200811'||'01'ASDATEformat'YYYYMMDD'),Last_JobStatus,max(Last_Endtime),JobTypeFROMPV_AUTO.ETL_JOBWHEREetl_jobin(……)groupby1,2,3,5;报错信息如下:
THENSUBSTR(TRIM(etl_job),5,LENGTH(TRIM(etl_job))-4)$***Failure3706Syntaxerror:
expectedsomethingbetween'('andthe'TRIM'keyword.Statement#1,Info=450思维惯性,以为sql中存在不可见字符,遂重写报错部分,不见效。
突然想起,TERADATA的标准求字符串长度的函数为CHAR,CHARS,CHARACTER等,将THENSUBSTR(TRIM(etl_job),5,LENGTH(TRIM(etl_job))-4)改成THENSUBSTR(TRIM(etl_job),5,CHARS(TRIM(etl_job))-4),问题解决。
Teradata数据压缩
1数据压缩的好处
1.Teradata数据压缩可以节省存储空间,从而让相同的存储单元存储更多的业务数据;
2.由于查询时需要检索的数据量相对要少,可以减少I/O,并且缓存中可以存储更多的压缩过的逻辑行,从而改善数据库性能;
3.Teradata使用的压缩算法非常有效,因为压缩原因,获取数据需要更少的的磁盘访问,算法将节省的CPU时间用来来执行压缩操作;
4.可以将压缩节省出来的空间创建高级索引;
2.teradata数据压缩Teradata版本v2r5采用无损压缩算法,允许在一列上对多达255个数据值进行压缩,数据压缩不会造成信息的丢失。
一般数据压缩的粒度可以分为数据行和数据块。
Teradata数据压缩的粒度是数据行,这是日常数据操作的粒度,数据行压缩可以独立的针对列进行,数据块压缩不行,并且数据块压缩都会造成额外的压缩/解压开销,会降低数据库性能。
Teradata可以直接访问压缩后的数据行—访问数据行时它不需要对数据做重构或者解压操作。
前面说过,Teradata可以独立的对行中的某列做压缩操作。
如果某列允许有空值,空值也允许被压缩。
最好的压缩候选项为此列中出现频率最高的值,压缩后的值存放在表头。
每行前面有1bit数据来标识此行有没有被压缩。
非主索引的定长列都可以是Teradata压缩的候选项。
如下的数据类型可以被压缩,括号中为该数据类型的长度(限制):
-IntegerDate(4)-CHAR(N,whereN<256)-BYTEINT
(1)-SMALLINT
(2)-INTEGER(4)-FLOAT/REAL(8)-DOUBLE(8)-DECIMAL(1,2,4or8)-BYTE(N,whereN<256)如果某列中有高频率出现的值,那么该列可以有很高的压缩比,常见的有下面一些情形:
-NULLs-Zeros-Defaultvalues-Flags-Spaces-Binaryindicators(e.g.,T/F)在经分系统中,一些产品字段,如Brand_Id,prd_id等,都会有很高的压缩比。
Teradata压缩对应用程序,ETL,查询等操作是完全透明的,并且其操作相当方便,用户只需要在表定义的时候加上压缩信息就可以了。
例如,下面是数据压缩的语法:
CREATETABLEProperties(AddressVARCHAR(40),CityCHAR(20)COMPRESS(‘WuHan’),StateCodeCHAR
(2));当记录中出现“WuHan”的频率很高时,数据压缩将非常有效。
3.**系统数据压缩操作方法基于以上数据压缩原理,我们开发了脚本create_comp_ddl.pl,用来依照压缩规则生成包含压缩信息的建表DDL.创建数据库表脚本文件用户配置文件名称输出脚本目标文件压缩值数目起始压缩表大小压缩值占比(百分数);对参数的解释如下:
?
?
创建数据库表脚本文件,老表ddl名称(必须将ddl导出放在指定目录);?
?
用户配置文件名称,配置信息主要是除varchar,pi,ppi以外的不希望被压缩的列,各列逗号分开?
?
将要生成的新表ddl文件名称;?
?
30,压缩值数目,表示取多少个压缩来做压缩操作;?
?
100000000,压缩表大小阀值,当表大小小于此阈值时,不进行压缩;?
?
20,当前所取压缩记录占表空间比例的阀值,当欲压缩记录占表空间比例小于此阈值时不做压缩处理;
操作步骤:
1)、获取压缩前的建表ddl,保存为src_table.ddl,文件名将作为参数;
2)、运行create_comp_ddl.pl脚本,如:
perlcreate_comp_ddl.plcsr_table.ddlconfig.txttarget_table.sql3010000000030运行结果将生成新的建表ddl(即target_table.sql);
3)、以新的ddl建表,将老表的数据导入新表,确认无误后,删除老表;
4.批量压缩若要压缩的表较多,也可以采取批量压缩的方式。
这里采用批处理执行perl脚本的方法来批量压缩。
如需要相继对prd_prd_inst_hist表和pty_cust_hist表做压缩,创建批处理compression.bat,其内容如下:
perlcreate_comp_ddl.plcsrc_table_prd.ddlconfig.txttar_table_prd.sql3010000000030>1.logperlcreate_comp_ddl.plsrc_table_pty.ddlconfig.txttar_table_pty.sql3010000000030>2.log注意,上述参数里面的源表ddl名和目标ddl名不能相同,否则第二次的结果可能覆盖第一次结果.常用sql使用技巧LIKE用法selectfirst_name,last_namefromemployeewherelast_namelikeall('%E%','%S%');selectfirst_name,last_namefromemployeewherelast_namelikeany/some('%E%','%S%');Teradata缺省不区分大小写,如果要区分,可以使用其扩展参数CASESPECIFICselectfirst_name,last_namefromemployeewherelast_name(CASESPECIFIC)like'%Ra%';
通配符作为一般字符使用例:
LIKE"%A%%AAA__"ESCAPE"A"在这个表达式中,讲字母A定义为ESCAPE字符,其中:
第一个%为通配符:
第一个A和其后的%联合表示字符%第三个%为通配符第二个A和其后的A联合表示字符A;第四个和其后的'_'联合表示字符_最后一个'_'为通配符NULL的使用当进行升序排列时,NULL在数字列排列在负数前,在字符排列在空格前优先级和括号逻辑运算符的缺省优先级顺序从高到低为:
NOT.AND.ORNOTNOT既可以否定操作符,也可以否定条件表达式否定操作符:
selectfirst_name,last_name,employee_numberfromemployeewheredepartment_numbernot=301;否定条件:
SELECTfirst_name,last_name,employee_numberFROMemployeeWHERENOT(department_number=301);
字符型数据在ANSI标准中关于字符型数据定义了两类:
CHAE和VARCHAR,Teradata除了上述两类基本字符数据外,还扩展了LONGVARCHAR类型,它等同于VARCHAR(64000),是最长的字符串char(size)固定长度的字符串最大长度:
64000字节varchar(size)charvarying(size)charactervarying(size)这三个是可变长度字符串,最大长度:
64000字节longvarchar等同于varchar(64000)二进制数据二进制数据类型是Teradata的扩展,ANSI标准没有此类型BYTE(size)固定长度的二进制串默认值:
(1)最大值:
64000字节varbyte(size)可变长的二进制串默认值:
(1)最大值:
64000字节
数字型数据在ANSI标准中关于数字型数据定义了四类:
SMALLINT.INTEGER.FLOAT.DECIMAL,Teradata还扩展了BYTEINT和DATEsmallint整数范围:
-32768~32767integer整数范围:
-2147483648~2147483647decimal(size,dec)小数最大:
18位numeric(precision,dec)decimal的同义词float表示浮点数float[(precision)]通floatreal同floatdoubleprecision双精度浮点数byteint有符号整数范围-128~127date特殊整数,格式为YYMMDD或yyymmdd表示日期
图形数据graphic[(n)]固定长度的图形字符串默认长度:
1vargraphic(n)可变长的图形字符串longvargraphic可变长的图形字符串
算术运算符**(求幂)mod(取模)
系统变量
date当前系统日期
time系统时间
user当前登陆的用户
database当前缺省的数据库
字符常量.数字常量和计算模式字符文字在ANSI方式下区分大小写,而在Teradata缺省模式下不区分大小写
数字型常量最多可以包含15个数字,数字前面的零是无意义的
计算模式是指在SQL的SELECT语句中直接进行数学计算,如下:
select2*2593;
日期计算例:
1997年3月31日的表达方式year=(1997-1900)*10000=970000month=(3*100)=300day=31date=970331
于日期有关的数据函数
EXTRACTANSI标准中EXTRACT函数允许选取日期和时间中任意段或任意间隔的值,Teradata中EXTRACT函数支持日期数据中选取年.月.日.从时间数据中选取小时.分钟和秒SELECTDATE;96/11/07SELECTEXTRACT(YEARFROMDATE);1996SELECTEXTRACT(MONTHFROMDATE+30);12SLEECTEXTRACT(DAYFROMDATE+2);09SELECTTIME;14:
52:
32SELECTEXTRACT(HOURFROMTIME);
14SELECTEXTRACT(SECONDFROMTIME+30);INVALIDTIME2.ADD_MONTHS
利用CAST作数据转换ANSI标准中利用CAST函数将一种数据类型转换成另一种数据类型selectcast(salary_amountasinteger)fromemployee;selectcast(salary_amountasdec(6,0))fromemployee;selectcast(last_nameaschar(5))fromemployeewheredepartment_number=401;
Teradata也可以利用CAST函数来完成上面的操作,另外,它也作了扩充.为了完成上面相同的操作,也可以使用下面的表达方式selectsalary_amount(integer);
selectsalary_amount(dec(6,0));selectlast_name(char(5));
Teradata对CAST函数本身也作了扩展,比如为了将显示结果以大写表示,可以使用下面的SQL语句
selectcast(last_nameaschar(5)uppercase)fromemployeewheredepartment_number=401;
简单的宏宏(Macro)的基本特征是:
可以包含一条或多条SQL语句可以包含多个BETQ语句可以包含注解存储在数据字典中
宏的定义createmacrobirthday_listas(selectlast_name,first_name,birthdate
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Teradata 高级 文档