ETL基础及常用技术培训课件

ETL基础及

主要内容ETL基本概念ETL常用逻辑架构ETL实施过程ETL常用技术（shell,oracle,datastage）ETL基础及常用技术培训ETL基本概念

ETL(Extract-Transform-Load)即数据的抽取、转换与加载。ETL是从各种原始的业务系统(异构多源)中提取数据，按照预先设计好的规则将抽取到的数据进行转换，最后将转换完的数据按计划增量或全部导人到目标数据库，成为联机分析处理、数据挖掘的基础。ETL为BI/DW的核心和灵魂.ETL就是一个批量数据加工的过程.ETL基础及常用技术培训ETL常用逻辑架构ETL基础及常用技术培训

数据抽取数据清洗与转换数据加载

ETL实施过程ETL基础及常用技术培训

数据抽取确定所有数据源来源于哪些源系统，核心系统，信贷，信用卡等定义数据接口对每个源文件及接口的每个字段进行详细说明数据抽取方法主动抽取还是源系统提供文件，增量还是全量,每日还是每月ETL实施过程ETL基础及常用技术培训

1.代码标准化2.数据粒度的转换3.根据业务规则计算

确定数据清洗和转换规则后细化mapping,编码实现数据清洗与转换数据清洗

1.不完整的数据

2.错误的数据

3.重复的数据

数据转换ETL实施过程ETL基础及常用技术培训

数据加载将数据缓冲区的数据直接加载到数据库对应的表中，如果是全量采用load方式，如果是增量则根据业务规则merge进数据库

ETL实施过程ETL基础及常用技术培训

ETL常用技术SHELL(unix基本操作)SQLPL/SQLPROCDATASTAGEETL基础及常用技术培训

shell基础Linux中有好多种不同的shell，如bsh，csh，同其他语言一样，可以通过我们使用任意一种文字编辑器，比如vi等来编写我们的shell程序。程序必须以下面的行开始（必须放在文件的第一行）：#!/bin/sh符号#!用来告诉系统它后面的参数是用来执行该文件的程序。在这个程序中我们使用/bin/sh来执行程序。当编辑好脚本时，如果要执行该脚本，还必须使其可执行。要使脚本可执行：chmod+x然后，可以通过输入：./来执行脚本。ETL基础及常用技术培训

shell基础Vi（一）、进入vi在系统提示字符(如$、#)下敲入vi＜档案名称＞，vi可以自动帮你载入所要编辑的文件或是开启一个新文件（如果该文件不存在或缺少文件名）。进入vi后萤幕左方会出现波浪符号，凡是列首有该符号就代表此列目前是空的。(二）、两种模式如上所述，vi存在两种模式：指令模式和输入模式。在指令模式下输入的按键将做为指令来处理：如输入a，vi即认为是在当前位置插入字符。而在输入模式下，vi则把输入的按键当作插入的字符来处理。指令模式切换到输入模式只需键入相应的输入命令即可（如a,A），而要从输入模式切换到指令模式，则需在输入模式下键入ESC键，如果不晓得现在是处於什麽模式，可以多按几次[ESC]，系统如发出哔哔声就表示已处于指令模式下了。ETL基础及常用技术培训

shell基础Vi由指令模式进入输入模式的指令：新增(append)a：从光标所在位置後面开始新增资料，光标後的资料随新增资料向後移动。A：从光标所在列最後面的地方开始新增资料。插入(insert)i：从光标所在位置前面开始插入资料，光标後的资料随新增资料向後移动。I：从光标所在列的第一个非空白字元前面开始插入资料。开始(open)o：在光标所在列下新增一列并进入输入模式。O:在光标所在列上方新增一列并进入输入模式。ETL基础及常用技术培训

shell基础Vi（三）、基本编辑配合一般键盘上的功能键，像是方向键、[Insert]、[Delete]等等，现在你应该已经可以利用vi来编辑文件了。当然vi还提供其他许许多多功能让文字的处理更为方便。何谓编辑？一般认为是文字的新增、修改以及删除，甚至包括文字区块的搬移、复制等等。先这里介绍vi的如何做删除与修改。删除与修改文件的命令：x：删除光标所在字符。dd：删除光标所在的列。r：修改光标所在字元，r後接著要修正的字符。R：进入取替换状态，新增文字会覆盖原先文字，直到按[ESC]回到指令模式下为止。s：删除光标所在字元，并进入输入模式。S：删除光标所在的列，并进入输入模式。在指令模式下移动光标的基本指令是h,j,k,l。ETL基础及常用技术培训

shell基础Vi（四）、退出vi在指令模式下键入:q,:q!,:wq或:x(注意:号），就会退出vi。其中:wq和:x是存盘退出，而:q是直接退出，如果文件已有新的变化，vi会提示你保存文件而:q命令也会失效，这时你可以用:w命令保存文件后再用:q退出，或用:wq或:x命令退出，如果你不想保存改变后的文件，你就需要用:q!命令，这个命令将不保存文件而直接退出vi。ETL基础及常用技术培训

shell基础注释#变量在shell编程中，所有的变量都由字符串组成，并且不需要对变量进行声明。要赋值给一个变量，可以这样写：变量名=值取出变量值可以加一个美元符号（$）在变量前面shell脚本中的三类命令1）Unix命令2）管道,重定向和backtick3）流程控制ETL基础及常用技术培训

shell基础—unix命令在shell脚本中可以使用任意的unix命令，这些命令通常是用来进行文件和文字操作的。常用命令语法及功能:

echo"sometext":将文字内容打印在屏幕上

ls:文件列表

wc–lfilewc-wfilewc-cfile:计算文件行数计算文件中的单词数计算文件中的字符数

cpsource:文件拷贝

mvoldnamenewname:重命名文件或移动文件

rmfile:删除文件

grep'pattern'file:在文件内搜索字符串比如：grep'searchstring'

cut-bcolnumfile:指定欲显示的文件内容范围，并将它们输出到标准输出设备比如：输出每行第5个到第9个字符cut-b5-9千万不要和cat命令混淆，这是两个完全不同的命令ETL基础及常用技术培训

shell基础—unix命令

ftp:远程传输文件

然后输入用户名密码，put，getcat:输出文件内容到标准输出设备（屏幕）上

:得到文件类型

readvar:提示用户输入，并将输入赋值给变量

sort:对文件中的行进行排序

uniq:删除文本文件中出现的行列比如：sort|uniq

expr:进行数学运算Example:add2and3expr2"+"3

find:搜索文件比如：根据文件名搜索find.-name-print

tee:将数据输出到标准输出设备(屏幕)和文件比如：somecommand|teeoutfile

basenamefile:返回不包含路径的文件名比如：basename/bin/tux将返回tuxETL基础及常用技术培训

shell基础—unix命令

dirnamefile:返回文件所在路径比如：dirname/bin/tux将返回/bin

headfile:打印文本文件开头几行

tailfile:打印文本文件末尾几行

sed:Sed是一个基本的查找替换程序。可以从标准输入（比如命令管道）读入文本，并将结果输出到标准输出（屏幕）。该命令采用正则表达式（见参考）进行搜索。不要和shell中的通配符相混淆。比如：将linuxfocus替换为LinuxFocus：cattext.file|sed's/linuxfocus/LinuxFocus/'>newtext.file

awk:awk用来从文本文件中提取字段。缺省地，字段分割符是空格，可以使用-F指定其他分割符。cat|awk-F,'{print$1","$3}'这里我们使用，作为字段分割符，同时打印第一个和第三个字段。如果该文件内容如下：AdamBor,34,IndiaKerryMiller,22,USA命令输出结果为：AdamBor,IndiaKerryMiller,USAtar:将文件或文件夹打包解包，tar–cvfa.tarabctar–xvfa.tarETL基础及常用技术培训

shell基础—管道、重定向和backtick命令管道(|)将一个命令的输出作为另外一个命令的输入。grep"hello"|wc-l在中搜索包含有”hello”的行并计算其行数。在这里grep命令的输出作为wc命令的输入重定向将命令的结果输出到文件，而不是标准输出（屏幕）>写入文件并覆盖旧文件

>>加到文件的尾部，保留旧文件内容。反短斜线（`）使用反短斜线可以将一个命令的输出作为另外一个命令的一个命令行参数。命令：V_date=`date+%F`ETL基础及常用技术培训

shell基础—流程控制命令(if)if[]Thenelif[]ThenElse

fi大多数情况下，可以使用测试命令来对条件进行测试。比如可以比较字符串、判断文件是否存在及是否可读等等…通常用"[]"来表示条件测试。注意这里的空格很重要。要确保方括号的空格。[-f"somefile"]：判断是否是一个文件[-x"/bin/ls"]：判断/bin/ls是否存在并有可执行权限[-n"$var"]：判断$var变量是否有值["$a"="$b"]：判断$a和$b是否相等ETL基础及常用技术培训

shell基础—流程控制命令(if)字符串比较:

string1=string2如果相等则为真

string1!=string2如果不等则为真

-nstring如果不空则为真

-zstring如果为空则为真算术比较:

expression1-eqexpression2如果相等则为真

expression1-neexpression2如果不等则为真

expression1-gtexpression2如果大于则为真

expression1-geexpression2大于等于则为真

expression1-ltexpression2如果小于则为真

expression1-leexpression2小于等于则为真

ETL基础及常用技术培训

shell基础—流程控制命令(if)文件:

-dfile如果为目录则为真

-efile如果存在则为真(在这里要注意的是,由于历史原因,-e选项并不可移植,所以常用的是-f选项

-ffile如果为常规文件则为真

-gfile如果设置了组ID则为真

-rfile如果文件可读则为真

-sfile如果文件大小不为零则为真

-ufile如果设置了用户ID则为真

-wfile如果文件可写则为真

-xfile如果文件可执行则为真ETL基础及常用技术培训

shell基础—流程控制命令1)while...DoDonewhile-loop将运行直到表达式测试为真。willrunwhiletheexpressionthatwetestforistrue.关键字"break"用来跳出循环。而关键字”continue”用来不执行余下的部分而直接跳到下一个循环。2)forvarindoDonefor-loop表达式查看一个字符串列表(字符串用空格分隔)然后将其赋给一个变量：3)case...in...);;Esaccase表达式可以用来匹配一个给定的字符串ETL基础及常用技术培训

oracle基础—数据库安装Windows环境下:网上下载安装包点击安装全选默认配置即可。其它机器上已有server端，可只安client端。PLSQL软件是一个优秀的oracle工具，建议安装unix环境下:建oracle组和用户在oracle用户下安装授予其它用户执行oracle命令的权限ETL基础及常用技术培训

oracle基础—数据库的连接Client端连接配置到oracle安装目录下搜索tnsnames.ora增加修改相应连接内容ORCL=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cg)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))mdb=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=mdb)))ETL基础及常用技术培训

oracle基础—数据库的连接Unix环境先设置编辑器exportEDITOR=viselectparameter,valuefromnls_database_parameterswhereparameter='NLS_CHARACTERSET';--显示数据库字符集NLS_LANG应与数据库字符集一致exportNLS_LANG=“SIMPLIFIEDCHINESE.ZHS16GBK”exportNLS_DATE_FORMAT='YYYY-MM-DD-HH24.MI.SS‘命令行：

sqlplus[username]/password][@server][assysdba][assysoper]sqlplus"/assysdba"--管理员进入

sqlplusetl/etl@mdb--普通用户进入SQL>setlinesize300;--设置行长度SQL>setpagesize1;--设置页长度SQL>colnameformata30;--设置列格式SQL>selectsysdatefromdual;--运行语句ETL基础及常用技术培训

oracle基础—数据库的连接

Plsql是一款优秀的操作oracle图形界面软件Sqlwindow可单条执行sqlCommandwindow可批量执行多条sqlETL基础及常用技术培训

oracle基础—表空间ORACLE数据库被划分成称作为表空间的逻辑区域——形成ORACLE数据库的逻辑结构。一个ORACLE数据库能够有一个或多个表空间,而一个表空间则对应着一个或多个物理的数据库文件。表空间是ORACLE数据库恢复的最小单位,容纳着许多数据库实体,如表、视图、索引、聚簇、回退段和临时段等。每个ORACLE数据库均有SYSTEM表空间,这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着数据库运行所要求的基本信息(关于整个数据库的数据字典、联机求助机制、所有回退段、临时段和自举段、所有的用户数据库实体、其它ORACLE软件产品要求的表)。一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间,然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便。ETL基础及常用技术培训

oracle基础—表空间建立本地管理表空间CREATETABLESPACEtablespace_nameDATAFILE‘’SIZE[UNIFORMSIZEextent_size|AUTOALLOCATE]CREATETABLESPACEDATATBS01_32KDATAFILE'/home/mdb/oracle/oradata/mdb/datatbs01_01.dbf'SIZE100G,'/home/mdb/oracle/oradata/mdb/datatbs01_02.dbf'SIZE100G,'/home/mdb/oracle/oradata/mdb/datatbs01_03.dbf'SIZE100G,EXTENTMANAGEMENTLOCALUNIFORMSIZE32MSEGMENTSPACEMANAGEMENTAUTOblocksize32kETL基础及常用技术培训

oracle基础—表空间建立UNDO表空间CREATEUNDOTABLESPACEtablespace_nameDATAFILE‘’SIZE[UNIFORMSIZEextent_size|AUTOALLOCATE]建立临时表空间:存放查询、排序、分组等生成的临时数据CREATETEMPORARYTABLESPACEtablespace_nameTEMPFILE‘’SIZE[UNIFORMSIZEextent_size|AUTOALLOCATE]ETL基础及常用技术培训

oracle基础—表空间改变表空间可用性ALTERTABLESPACEtablespace_name{online|offline}改变表空间读写状态ALTERTABLESPACEtablespace_name{READWRITE|READONLY}改变表空间名称ALTERTABLESPACEtablespace_nameRENAMETOnew_tbs_name设置默认表空间ALTERDATABASEDEFAULTTABLESPACEtablespace_name--数据库默认表空间ALTERDATABASEDEFAULTTEMPORARYtablespace_name--数据库默认临时表空间删除表空间DROPTABLESPACEtablespace_name[INCLUDINGCONTENTS[ANDDATAFILES]];ETL基础及常用技术培训

oracle基础—用户管理建立用户createuserETLidentifiedby""defaulttablespaceETLDATA01temporarytablespaceTEMP1pro;给用户授权grantconnecttoetl;grantselect_catalog_roletoetl;grantselectanytabletoetl;grantcreatesessiontoetl;grantaltersessiontoetl;grantcreatetabletoetl;grantcreateviewtoetl;grantcreatetypetoetl;grantcreatesynonymtoetl;grantcreatesequencetoetl;grantcreateproceduretoetl;ETL基础及常用技术培训

oracle基础—用户管理修改口令ALTERUSERuser_nameIDENTIFIEDBYnew_password;修改默认表空间ALTERUSERuser_nameDEFAULTTABLESPACEnew_tablespace_name;锁定用户

ALTERUSERetlACCOUNTLOCK;解锁用户

ALTERUSERetlACCOUNTUNLOCK;删除用户

DROPUSERusername[CASCADE];ETL基础及常用技术培训

oracle基础—表建立及管理普通建表CREATETABLE[schema.]table_name(column_namedatatype[DEFAULTexpr][,…])[TABLESPACEtablespace_name];使用子查询建表CREATETABLE[schema.]table_name[(column_name,…)][TABLESPACEtablespace_name]ASsubquery;建临时表--事务临时表当前事务内有效CREATEGLOBALTEMPORARYTABLEtable_name[(column_name,…)]ONCOMMITDELETEROWS;--会话临时表当前会话内有效CREATEGLOBALTEMPORARYTABLEtable_name[(column_name,…)]ONCOMMITPRESERVEROWS;ETL基础及常用技术培训

oracle基础—表建立及管理增加列

ALTERTABLEtable_nameADD(columndatatype[DEFAULTexpr]…);修改列定义

ALTERTABLEtable_nameMODIFY(columndatatype[DEFAULTexpr]…);删除列ALTERTABLEtable_nameDROP(column);修改列名ALTERTABLEtable_nameRENAMECOLUMNcolumn_nametonew_column_name;增加注释COMMENTONTABLEtable_nameIS‘text’;COMMENTONCOLUMNtable_name.column_nameIS‘text’;迁移非分区表的表空间ALTERTABLEtable_nameMOVEnew_tablespace_name;--表对应索引需重建ETL基础及常用技术培训

oracle基础—表建立及管理截断表--快速删除数据释放空间保留表结构--是DDL语句不可回退TRUNCATETABLEtable_name;--delete是DML语句可回退删除表--PURGE是oracle10g新特性表永久删除DROPTABLEtable_name[CASCADECONSTRAINTS][PURGE];从回收站Recyclebin恢复被删除表FLASHBACKTABLEtable_nameTOBEFOREDROP;清空回收站PURGERECYCLEBIN;查看回收站SELECT*FROMRECYCLEBIN;ETL基础及常用技术培训

oracle基础—表建立及管理表SELECT*FROMUSER_TABLES;对象

SELECT*FROMUSER_OBJECTS;表占用的空间select

sum(bytes)fromuser_segmentswheresegment_name=table_name;清空回收站PURGERECYCLEBIN;查看回收站SELECT*FROMRECYCLEBIN;查看表的列SELECT*FROMUSER_TAB_COLUMNS;查看注释SELECT*FROMUSER_TAB_COMMENTS;SELECT*FROMUSER_COL_COMMENTS;查看建表语句SELECTdbms_metadata.get_ddl('TABLE','<OBJECT_NAME>','<SCHEMA_NAME>')FROMdual;SELECTdbms_metadata.get_ddl('TABLE',upper('tmpcg_ctl_paravalue'),upper(‘etl'))FROMdual;ETL基础及常用技术培训

oracle基础—索引建索引CREATEINDEX[UNIQUE]index_nameONtable_name(column_name,..)[TABLESPACEtablespace_name];--不设表空间则建在用户默认表空间上维护索引ALTERINDEXindex_nameREBUILD[TABLESPACEtablespace_name];--可改变索引表空间显示索引信息SELECT*FROMUSER_INDEXES;SELECT*FROMUSER_IND_COULMNS;--查找失效的非分区索引SELECT*FROMUSER_INDEXESWHERESTATUS=‘UNUSABLE’;查看建索引语句SELECTdbms_metadata.get_ddl(‘INDEX’,’<OBJECT_NAME>’,’<SCHEMA_NAME>’)FROMdual;SELECTdbms_metadata.get_ddl(‘INDEX’,upper(‘idx_tmpcg_ctl_paravalue’),upper(‘etl’))FROMdual;ETL基础及常用技术培训

oracle基础—序列序列（Sequence）是用于生成唯一数字的数据库对象，自动生成顺序递增的序列号，可提供唯一的主键值，事务回滚值不会减少建序列CREATESEQUENCEseq_name[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}];[{MINVALUEn|NOMINVALUE}][{CYCLE|NOCYCLE}][{CACHEn|NOCACHE}];使用序列INSERTINTOt1(id)VALUES(t1id_seq.NEXTVAL);SELECTt1id_seq.CURRVALFROMdual;删除序列

DROPSEQUENCEseq_name;显示序列信息SELECT*FROMSEQ;查看建序列语句SELECTdbms_metadata.get_ddl(‘SEQUENCE’,’<OBJECT_NAME>’,’<SCHEMA_NAME>’)FROMdual;ETL基础及常用技术培训

oracle基础—分区表ORACLE的分区(PartitioningOption)是一种处理超大型表的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。ORACLE的分区表的划分方法包括：按字段值进行划分的范围分区；按字段的HASH函数值进行的划分HASH分区；先按范围划分，再按HASH划分的复合分区；在ORACLE9i中又增强了按字段值列表进行划分的列表(Listing)分区方法。管理员可以指定每个分区的存储属性，分区在宿主文件系统中的放置情况，这样便增加了对超大型数据库的控制粒度(granularity)。分区可以被单独地删除、卸出或装入、备份、恢复，因此减少了需要进行管理操作的时间。还可以为表分区创建单独的索引分区，从而减少了需要进行索引维护操作的时间。此外，还提供了种类繁多的局部和全局的索引技术。分区操作也可以被并行执行。分区技术还提高了数据的可用性。当部分数据由于故障或其它原因不可用时，其它分区内的数据可不受影响继续使用。分区对应用是透明的，可以通过标准的SQL语句对分区表进行操作。Oracle的优化器在访问数据时会分析数据的分区情况，在进行查询时，那些不包含任何查询数据的分区将被忽略，从而大大提高系统的性能。总之，Oracle的分区表技术能满足高性能、高可用性、数据易管理性等综合需求。ETL基础及常用技术培训

oracle基础—分区表设计原则表的大小对于大表进行分区，将有益于大表操作的性能和大表的数据维护。通常当表的大小超过1.5GB－2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。数据访问特性基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。数据维护某些表的数据维护，经常按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。因为删除（Delete）大量的数据，对系统开销很大，有时甚至是不可接受的。只读数据如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。并行数据操作（ParallelDML）对于经常执行并行操作（如ParallelInsert,ParallelUpdate等）的表应考虑进行分区。表的可用性当对表的部分数据可用性要求很高时，应考虑进行表分区。ETL基础及常用技术培训

oracle基础—分区表范围分区表创建脚本CREATETABLEHIS_EVT_DCC_FCMAUDI(cm_rec_llinteger,cm_tx_dtvarchar2(8),……)PARTITIONBYRANGE(CM_TX_DT)(PARTITIONETL_LOAD_DATE_0508VALUESLESSTHAN(20050901)TABLESPACEetl0_r_jyrq_data_200508,PARTITIONETL_LOAD_DATE_0509VALUESLESSTHAN(20051001)TABLESPACEetl0_r_jyrq_data_200509,PARTITIONETL_LOAD_DATE_0510VALUESLESSTHAN(20051101)TABLESPACEetl0_r_jyrq_data_200510,PARTITIONETL_LOAD_DATE_0511VALUESLESSTHAN(20051201)TABLESPACEetl0_r_jyrq_data_200511,PARTITIONETL_LOAD_DATE_0512VALUESLESSTHAN(20060101)TABLESPACEetl0_r_jyrq_data_200512);CREATEUNIQUEINDEXHIS_EVT_DCC_FCMAUDI_UIDXONHIS_EVT_DCC_FCMAUDI(CM_TX_DTASC,CM_TX_LOG_NOASC,CM_TX_CODASC,CM_EC_FLGASC)LOCAL(PARTITIONETL_LOAD_DATE_0508TABLESPACEetl0_r_jyrq_idx_200508,PARTITIONETL_LOAD_DATE_0509TABLESPACEetl0_r_jyrq_idx_200509,PARTITIONETL_LOAD_DATE_0510TABLESPACEetl0_r_jyrq_idx_200510,PARTITIONETL_LOAD_DATE_0511TABLESPACEetl0_r_jyrq_idx_200511,PARTITIONETL_LOAD_DATE_0512TABLESPACEetl0_r_jyrq_idx_200512,);ETL基础及常用技术培训

oracle基础—分区表HASH分区表创建脚本CREATETABLEPERSONAL_CUSTOMER_INFO（……）PARTITIONBYHASH(CI-CUST-NO)PARTITIONS64TABLESPACETS_ODS_DATA;复合分区表创建脚本CREATETABLEFACT_DCC_SAACNACN_PERSONAL(txn_date,sa-acct-no)PARTITIONBYRANGE(时间字段)SUBPARTITIONBYHASH(SA-ACCT-NO)SUBPARTITIONTEMPLATE(SUBPARTITIONs1,SUBPARTITIONs2,SUBPARTITIONs3,……);ETL基础及常用技术培训

oracle基础—分区表查询SELECT*FROMhis_evt_dcc_fcmaudiPARTITION(ETL_LOAD_DATE_0508);清分区数据ALTERTABLEhis_evt_dcc_fcmaudiTRUNCATEPARTITION(ETL_LOAD_DATE_0508);新增分区ALTERTABLEhis_evt_dcc_fcmaudiADDPARTITIONETL_LOAD_DATE_0601VALUESLESSTHAN(20060201)TABLESPACEetl0_r_jyrq_data_200612;删除分区ALTERTABLEhis_evt_dcc_fcmaudiDROPPARTITIONETL_LOAD_DATE_0508;ETL基础及常用技术培训

oracle基础—分区表分区信息查询SELECT*FROMUSER_TAB_PARTITIONS;SELECT*FROMUSER_TAB_SUBPARTITIONS;SELECT*FROMUSER_IND_PARTITIONS;加上WHERESTATUS=‘UNUSABLE’可查询失效的分区索引SELECT*FROMUSER_IND_SUBPARTITIONS;加上WHERESTATUS=‘UNUSABLE’可查询失效的子分区索引SELECT*FROMUSER_SEGMENTS;ETL基础及常用技术培训

oracle基础—sqlldr用法:SQLLDRkeyword=value[,keyword=value,...]sqlldr$1control=tb.ctlerrors=10000rows=100000bindsize=8192000readsize=8192000log=tbldr.logbad=tbldr.baddirect=true有效的关键字:userid--ORACLE用户名/口令control--控制文件名log--日志文件名bad--错误文件名data--数据文件名discard--废弃文件名discardmax--允许废弃的文件的数目(全部默认)skip--要跳过的逻辑记录的数目(默认0)load--要加载的逻辑记录的数目(全部默认)errors--允许的错误的数目(默认50)rows--常规路径绑定数组中或直接路径保存数据间的行数(默认:常规路径64,所有直接路径)bindsize--常规路径绑定数组的大小(以字节计)(默认256000)silent--运行过程中隐藏消息(标题,反馈,错误,废弃,分区)direct--使用直接路径(默认FALSE)par参数文件:包含参数说明的文件的名称parallel--执行并行加载(默认FALSE)要从以下对象中分配区的文件

ETL基础及常用技术培训

oracle基础—sqlldr控制文件LOADDATAINFILE‘test.txt’INTOTABLEDEPTREPLACEFIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'(DEPTNO,DNAME,LOC)ETL基础及常用技术培训

oracle基础—文本数据导出

Oracle没有提供将数据导出到一个文件的工具。但是我们可以用SQL*Plus的select及format数据来输出到一个文件：setechooffnewpage0space0pagesize0feedoffheadofftrimspoolonspooloradata.txtselectcol1||','||col2||','||col3fromtab1wherecol2='XYZ';spooloff

ETL基础及常用技术培训

oracle基础—数据库备份导入导出EXP:exphelp=y

有三种主要的方式（完全、用户、表）

1、完全：

EXPSYSTEM/MANAGERBUFFER=64000FILE=C:\FULL.DMPFULL=Y

如果要执行完全导出，必须具有特殊的权限

2、用户模式：

EXPSONIC/SONIC

BUFFER=64000FILE=C:\SONIC.DMPOWNER=SONIC

这样用户SONIC的所有对象被输出到文件中。

3、表模式：

EXPSONIC/SONIC

BUFFER=64000FILE=C:\SONIC.DMPOWNER=SONICTABLES=(SONIC)

这样用户SONIC的表SONIC就被导出ETL基础及常用技术培训

oracle基础—数据库备份导入导出

IMP:imphelp=y

具有三种模式（完全、用户、表）

1、完全：

IMP"'sys/sys123assysdba'"BUFFER=64000FILE=C:\FULL.DMPFULL=Y

2、用户模式：

IMPSONIC/SONIC

BUFFER=64000FILE=C:\SONIC.DMPFROMUSER=SONICTOUSER=SONIC

这样用户SONIC的所有对象被导入到文件中。必须指定FROMUSER、TOUSER参数，这样才能导入数据。

3、表模式：

impSONIC/SONIC

BUFFER=64000FILE=C:\SONIC.DMPOWNER=SONICTABLES=(SONIC)

这样用户SONIC的表SONIC就被导入。ETL基础及常用技术培训

oracle基础—查看执行计划若有PLSQL等windows界面的工具，可点执行计划菜单查看命令行查看执行计划SQL>explainplanforsql语句；--并不真正执行，只是生成执行计划例：SQL>explainplanforselect*frometlcusorgwhereecif_cus_num='12345';已解释。SQL>@?/rdbms/admin/utlxplp--查看PLAN_TABLE_OUTPUT|Id|Operation|Name|Rows|Bytes|Cost||0|SELECTSTATEMENT||1|279|3||1|TABLEACCESSBYINDEXROWID|ETLCUSORG|1|279|3||2|INDEXUNIQUESCAN|SYS_C007050|1||2|ETL基础及常用技术培训

oracle基础—常用函数数值型函数ABS(x)【功能】返回x的绝对值mod(x,y)【功能】返回x除以y的余数power(x,y)【功能】返回x的y次幂round(x[,y])【功能】返回四舍五入后的值【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。trunc(x[,y])【功能】返回x按精度y截取后的值【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示ETL基础及常用技术培训

oracle基础—常用函数字符型函数CONCAT(c1,c2)【功能】连接两个字符串同||LOWER(c1)【功能】：将字符串全部转为小写UPPER(c1)【功能】将字符串全部转为大写LENGTH(c1)【功能】返回字符串的长度;【说明】多字节符(汉字、全角符等)，按1个字符计算LENGTHB(c1)【功能】返回字符串的长度;【说明】多字节符(汉字、全角符等)，按2个字符计算LPAD(c1,n[,c2])【功能】在字符串c1的左边用字符串c2填充，直到长度为n时为止RPAD(c1,n[,c2])【功能】在字符串c1的右边用字符串c2填充，直到长度为n时为止ETL基础及常用技术培训

oracle基础—常用函数字符型函数LTRIM(c1,[,c2])【功能】删除左边出现的字符串默认为空格RTRIM(c1,[,c2])【功能】删除右边出现的字符串TRIM(c1)【功能】删除左边和右边出现的空格SUBSTR(c1,n1[,n2])【功能】取子字符串【说明】多字节符(汉字、全角符等)，按1个字符计算【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第y个字符直到结束的字串.ETL基础及常用技术培训

oracle基础—常用函数日期函数sysdate【功能】：返回当前日期add_months(d1,n1)【功能】：返回在日期d1基础上再加n1个月后新的日期。last_day(d1)【功能】：返回日期d1所在月份最后一天的日期。round(d1[,c1])【功能】：给出日期d1按期间(参数c1)四舍五入后的期间的第一天日期（与数值四舍五入意思相近）【参数】：d1日期型,c1为字符型(参数)，c1默认为j（即最近0点日期)【参数表】：c1对应的参数表:最近0点日期:取消参数c1或j最近的星期日：day或dy或d最近月初日期：month或mon或mm或rm最近季日期：q最近年初日期：syear或year或yyyy或yyy或yy或y(多个y表示精度)最近世纪初日期：cc或scctrunc(d1[,c1])【功能】：返回日期d1所在期间(参数c1)的第一天日期ETL基础及常用技术培训

oracle基础—常用函数转换函数TO_CHAR(x[[,c2],C3])

【功能】将日期或数据转换为char数据类型

【参数】

x是一个date或number数据类型。

c2为格式参数

c3为NLS设置参数

如果x为日期nlsparm=NLS_DATE_LANGUAGE控制返回的月份和日份所使用的语言。

如果x为数字nlsparm=NLS_NUMERIC_CHARACTERS用来指定小数位和千分位的分隔符，以及货币符号。

NLS_NUMERIC_CHARACTERS="dg",NLS_CURRENCY="string"

ETL基础及常用技术培训

oracle基础—常用函数转换函数TO_DATE(X[,c2[,c3]])【功能】将字符串X转化为日期型【参数】c2,c3,字符型，参照to_char()TO_NUMBER(X[[,c2],c3])【功能】将字符串X转化为数字型【参数】c2,c3,字符型，参照to_char()

ETL基础及常用技术培训

oracle基础—常用函数聚组函数AVG([distinct|all]x)【功能】统计数据表选中行x列的平均值。

SUM([distinct|all]x)【功能】统计数据表选中行x列的合计值。count(*|[distinct|all]x)【功能】统计数据表选中行x列的合计值。【参数】*表示对满足条件的所有行统计，不管其是否重复或有空值(NULL)all表示对所有的值统计,默认为alldistinct只对不同的值统计，如果有参数distinct或all，需有空格与x(列)隔开，均忽略空值(NULL)。MAX([distinct|all]x)【功能】统计数据表选中行x列的最大值。MIN([distinct|all]x)【功能】统计数据表选中行x列的最大值。ETL基础及常用技术培训

oracle基础—常用函数分析函数RANK()dense_rank()【语法】RANK()OVER([query_partition_clause]order_by_clause)dense_RANK()OVER([query_partition_clause]order_by_clause)【功能】聚合函数RANK和dense_rank主要的功能是计算一组数值中的排序值。【参数】dense_rank与rank()用法相当，【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。ETL基础及常用技术培训

oracle基础—常用函数其他函数NVL(expr1,expr2)【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。注意两者的类型要一致decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)【功能】根据条件返回相应值【参数】c1,c2,...,cn,字符型/数值型/日期型，必须类型相同或null注：值1……n不能为条件表达式,这种情况只能用casewhenthenend解决·含义解释：decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)该函数的含义如下：IF条件=值1THEN

RETURN(翻译值1)

ELSIF条件=值2THEN

RETURN(翻译值2)

ELSIF条件=值nTHEN

RETURN(翻译值n)

ELSE

RETURN(缺省值)

ENDIFETL基础及常用技术培训

oracle基础—常用函数其他函数case[<表达式>]when<表达式条件值1>then<满足条件时返回值1>[when<表达式条件值2>then<满足条件时返回值2>……[else<不满足上述条件时返回值>]]end【功能】当：<表达式>＝<表达式条件值1……n>时，返回对应<满足条件时返回值1……n>当<表达式条件值1……n>不为条件表达式时，与函数decode()相同，decode(<表达式>,<表达式条件值1>,<满足条件时返回值1>,<表达式条件值2>,<满足条件时返回值2>……,<不满足上述条件时返回值>)【参数】<表达式>默认为true(逻辑型)<表达式条件值1……n>类型要与<表达式>类型一致，若<表达式>为字符型，则<表达式条件值1……n>也要为字符型【注意点】1、以CASE开头，以END结尾2、分支中WHEN后跟条件，THEN为显示结果3、ELSE为除此之外的默认情况，类似于高级语言程序中switchcase的default，可以不加4、END后跟别名5、只返回第一个符合条件的值,剩下的when部分将会被自动忽略，得注意条件先后顺序ETL基础及常用技术培训

oracle基础—存储过程FUNCTIONCREATEORREPLACEFUNCTIONFN_ACCT_ORG_T(acct_typeINVARCHAR,acct_noINVARCHAR)RETURNVARCHAR2IScore_orgVARCHAR2(10):='';BEGINcore_org:='test;RETURNcore_org;EXCEPTIONWHENOTHERSTHENNULL;ENDFN_ACCT_ORG_T;ETL基础及常用技术培训

oracle基础—存储过程PROCEDURECREATEORREPLACEPROCEDURESP_ACCT_ORG_T(acct_typeINVARCHAR,acct_noINVARCHAR)IScore_orgVARCHAR2(10):='';BEGINcore_org:='test;EXCEPTIONWHENOTHERSTHENNULL;ENDSP_ACCT_ORG_T;ETL基础及常用技术培训DataStage框架ETL基础及常用技术培训

server一般安装在unix/linux系统，也可以安装在windows系统上，详细安装步骤参见相关文档客户端安装

client只能安装在windows系统上，详细安装步骤参见相关文档

服务端安装DataStage安装ETL基础及常用技术培训

DataStage服务停止执行命令：uv–admin–stop

执行ps–ef|grepdsadm

查看dsrpcd和JobMonApp这两个进程是否存在，若不存在则服务停止成功。

DataStage服务启动

在/home/dsadm/Ascential/DataStage/DSEngine/bin目录下

执行命令：uv–admin–start

执行ps–ef|grepdsadm

查看dsrpcd和JobMonApp这两个进程是否存在，若存在则启动成功

注意：如果不能启动，一般是由于还有dsadm启动的进程未被停掉，通过ps–ef|grepdsadm,将所有dsadm启动的进程全部kill掉，然后再启动。DataStage启停ET