ETL基础及常用技术培训.ppt

ETL基础及常用技术 2 主要内容 ETL基本概念ETL常用逻辑架构ETL实施过程ETL常用技术 shell oracle datastage 3 ETL基本概念 ETL Extract Transform Load 即数据的抽取 转换与加载 ETL是从各种原始的业务系统 异构多源 中提取数据 按照预先设计好的规则将抽取到的数据进行转换 最后将转换完的数据按计划增量或全部导人到目标数据库 成为联机分析处理 数据挖掘的基础 ETL为BI DW的核心和灵魂 ETL就是一个批量数据加工的过程 4 ETL常用逻辑架构 5 数据抽取数据清洗与转换数据加载 ETL实施过程 6 数据抽取确定所有数据源来源于哪些源系统 核心系统 信贷 信用卡等定义数据接口对每个源文件及接口的每个字段进行详细说明数据抽取方法主动抽取还是源系统提供文件 增量还是全量 每日还是每月 ETL实施过程 7 1 代码标准化2 数据粒度的转换3 根据业务规则计算确定数据清洗和转换规则后细化mapping 编码实现 数据清洗与转换数据清洗1 不完整的数据2 错误的数据3 重复的数据数据转换 ETL实施过程 8 数据加载将数据缓冲区的数据直接加载到数据库对应的表中 如果是全量采用load方式 如果是增量则根据业务规则merge进数据库 ETL实施过程 9 ETL常用技术 SHELL unix基本操作 SQLPL SQLPROCDATASTAGE 10 shell基础 Linux中有好多种不同的shell 如bsh csh 同其他语言一样 可以通过我们使用任意一种文字编辑器 比如vi等来编写我们的shell程序 程序必须以下面的行开始 必须放在文件的第一行 bin sh符号 用来告诉系统它后面的参数是用来执行该文件的程序 在这个程序中我们使用 bin sh来执行程序 当编辑好脚本时 如果要执行该脚本 还必须使其可执行 要使脚本可执行 chmod xfilename然后 可以通过输入 filename来执行脚本 11 shell基础 Vi 一 进入vi在系统提示字符 如 下敲入vi 档案名称 vi可以自动帮你载入所要编辑的文件或是开启一个新文件 如果该文件不存在或缺少文件名 进入vi后萤幕左方会出现波浪符号 凡是列首有该符号就代表此列目前是空的 二 两种模式如上所述 vi存在两种模式 指令模式和输入模式 在指令模式下输入的按键将做为指令来处理 如输入a vi即认为是在当前位置插入字符 而在输入模式下 vi则把输入的按键当作插入的字符来处理 指令模式切换到输入模式只需键入相应的输入命令即可 如a A 而要从输入模式切换到指令模式 则需在输入模式下键入ESC键 如果不晓得现在是处於什麽模式 可以多按几次 ESC 系统如发出哔哔声就表示已处于指令模式下了 12 shell基础 Vi由指令模式进入输入模式的指令 新增 append a 从光标所在位置後面开始新增资料 光标後的资料随新增资料向後移动 A 从光标所在列最後面的地方开始新增资料 插入 insert i 从光标所在位置前面开始插入资料 光标後的资料随新增资料向後移动 I 从光标所在列的第一个非空白字元前面开始插入资料 开始 open o 在光标所在列下新增一列并进入输入模式 O 在光标所在列上方新增一列并进入输入模式 13 shell基础 Vi 三 基本编辑配合一般键盘上的功能键 像是方向键 Insert Delete 等等 现在你应该已经可以利用vi来编辑文件了 当然vi还提供其他许许多多功能让文字的处理更为方便 何谓编辑 一般认为是文字的新增 修改以及删除 甚至包括文字区块的搬移 复制等等 先这里介绍vi的如何做删除与修改 删除与修改文件的命令 x 删除光标所在字符 dd 删除光标所在的列 r 修改光标所在字元 r後接著要修正的字符 R 进入取替换状态 新增文字会覆盖原先文字 直到按 ESC 回到指令模式下为止 s 删除光标所在字元 并进入输入模式 S 删除光标所在的列 并进入输入模式 在指令模式下移动光标的基本指令是h j k l 14 shell基础 Vi 四 退出vi在指令模式下键入 q q wq或 x 注意 号 就会退出vi 其中 wq和 x是存盘退出 而 q是直接退出 如果文件已有新的变化 vi会提示你保存文件而 q命令也会失效 这时你可以用 w命令保存文件后再用 q退出 或用 wq或 x命令退出 如果你不想保存改变后的文件 你就需要用 q 命令 这个命令将不保存文件而直接退出vi 15 shell基础 注释 变量在shell编程中 所有的变量都由字符串组成 并且不需要对变量进行声明 要赋值给一个变量 可以这样写 变量名 值取出变量值可以加一个美元符号 在变量前面shell脚本中的三类命令1 Unix命令2 管道 重定向和backtick3 流程控制 16 shell基础 unix命令 在shell脚本中可以使用任意的unix命令 这些命令通常是用来进行文件和文字操作的 常用命令语法及功能 echo sometext 将文字内容打印在屏幕上ls 文件列表wc lfilewc wfilewc cfile 计算文件行数计算文件中的单词数计算文件中的字符数cpsourcefiledestfile 文件拷贝mvoldnamenewname 重命名文件或移动文件rmfile 删除文件grep pattern file 在文件内搜索字符串比如 grep searchstring file txtcut bcolnumfile 指定欲显示的文件内容范围 并将它们输出到标准输出设备比如 输出每行第5个到第9个字符cut b5 9file txt千万不要和cat命令混淆 这是两个完全不同的命令 17 shell基础 unix命令 ftp 远程传输文件ftpip然后输入用户名密码 put getcatfile txt 输出文件内容到标准输出设备 屏幕 上filesomefile 得到文件类型readvar 提示用户输入 并将输入赋值给变量sortfile txt 对file txt文件中的行进行排序uniq 删除文本文件中出现的行列比如 sortfile txt uniqexpr 进行数学运算Example add2and3expr2 3find 搜索文件比如 根据文件名搜索find namefilename printtee 将数据输出到标准输出设备 屏幕 和文件比如 somecommand teeoutfilebasenamefile 返回不包含路径的文件名比如 basename bin tux将返回tux 18 shell基础 unix命令 dirnamefile 返回文件所在路径比如 dirname bin tux将返回 binheadfile 打印文本文件开头几行tailfile 打印文本文件末尾几行sed Sed是一个基本的查找替换程序 可以从标准输入 比如命令管道 读入文本 并将结果输出到标准输出 屏幕 该命令采用正则表达式 见参考 进行搜索 不要和shell中的通配符相混淆 比如 将linuxfocus替换为LinuxFocus cattext file sed s linuxfocus LinuxFocus newtext fileawk awk用来从文本文件中提取字段 缺省地 字段分割符是空格 可以使用 F指定其他分割符 catfile txt awk F print 1 3 这里我们使用 作为字段分割符 同时打印第一个和第三个字段 如果该文件内容如下 AdamBor 34 IndiaKerryMiller 22 USA命令输出结果为 AdamBor IndiaKerryMiller USAtar 将文件或文件夹打包解包 tar cvfa tarabctar xvfa tar 19 shell基础 管道 重定向和backtick命令 管道 将一个命令的输出作为另外一个命令的输入 grep hello file txt wc l在file txt中搜索包含有 hello 的行并计算其行数 在这里grep命令的输出作为wc命令的输入重定向将命令的结果输出到文件 而不是标准输出 屏幕 写入文件并覆盖旧文件 加到文件的尾部 保留旧文件内容 反短斜线 使用反短斜线可以将一个命令的输出作为另外一个命令的一个命令行参数 命令 V date date F 20 shell基础 流程控制命令 if if Then elif Then Else fi大多数情况下 可以使用测试命令来对条件进行测试 比如可以比较字符串 判断文件是否存在及是否可读等等 通常用 来表示条件测试 注意这里的空格很重要 要确保方括号的空格 f somefile 判断是否是一个文件 x bin ls 判断 bin ls是否存在并有可执行权限 n var 判断 var变量是否有值 a b 判断 a和 b是否相等 21 shell基础 流程控制命令 if 字符串比较 string1 string2如果相等则为真string1 string2如果不等则为真 nstring如果不空则为真 zstring如果为空则为真算术比较 expression1 eqexpression2如果相等则为真expression1 neexpression2如果不等则为真expression1 gtexpression2如果大于则为真expression1 geexpression2大于等于则为真expression1 ltexpression2如果小于则为真expression1 leexpression2小于等于则为真 22 shell基础 流程控制命令 if 文件 dfile如果为目录则为真 efile如果存在则为真 在这里要注意的是 由于历史原因 e选项并不可移植 所以常用的是 f选项 ffile如果为常规文件则为真 gfile如果设置了组ID则为真 rfile如果文件可读则为真 sfile如果文件大小不为零则为真 ufile如果设置了用户ID则为真 wfile如果文件可写则为真 xfile如果文件可执行则为真 23 shell基础 流程控制命令 1 while Do Donewhile loop将运行直到表达式测试为真 willrunwhiletheexpressionthatwetestforistrue 关键字 break 用来跳出循环 而关键字 continue 用来不执行余下的部分而直接跳到下一个循环 2 forvarin do Donefor loop表达式查看一个字符串列表 字符串用空格分隔 然后将其赋给一个变量 3 case in Esaccase表达式可以用来匹配一个给定的字符串 24 oracle基础 数据库安装 Windows环境下 网上下载安装包点击安装全选默认配置即可 其它机器上已有server端 可只安client端 PLSQL软件是一个优秀的oracle工具 建议安装unix环境下 建oracle组和用户在oracle用户下安装授予其它用户执行oracle命令的权限 25 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 128 160 96 21 PORT 1521 CONNECT DATA SERVER DEDICATED SID mdb 26 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 运行语句 27 oracle基础 数据库的连接 Plsql是一款优秀的操作oracle图形界面软件Sqlwindow可单条执行sqlCommandwindow可批量执行多条sql 28 oracle基础 表空间 ORACLE数据库被划分成称作为表空间的逻辑区域 形成ORACLE数据库的逻辑结构 一个ORACLE数据库能够有一个或多个表空间 而一个表空间则对应着一个或多个物理的数据库文件 表空间是ORACLE数据库恢复的最小单位 容纳着许多数据库实体 如表 视图 索引 聚簇 回退段和临时段等 每个ORACLE数据库均有SYSTEM表空间 这是数据库创建时自动创建的 SYSTEM表空间必须总要保持联机 因为其包含着数据库运行所要求的基本信息 关于整个数据库的数据字典 联机求助机制 所有回退段 临时段和自举段 所有的用户数据库实体 其它ORACLE软件产品要求的表 一个小型应用的ORACLE数据库通常仅包括SYSTEM表空间 然而一个稍大型应用的ORACLE数据库采用多个表空间会对数据库的使用带来更大的方便 29 oracle基础 表空间 建立本地管理表空间CREATETABLESPACEtablespace nameDATAFILE file name SIZEfile 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 EXTENTMANAGEMENTLOCALUNIFORMSIZE32MSEGMENTSPACEMANAGEMENTAUTOblocksize32k 30 oracle基础 表空间 建立UNDO表空间CREATEUNDOTABLESPACEtablespace nameDATAFILE file name SIZEfile size UNIFORMSIZEextent size AUTOALLOCATE 建立临时表空间 存放查询 排序 分组等生成的临时数据CREATETEMPORARYTABLESPACEtablespace nameTEMPFILE file name SIZEfile size UNIFORMSIZEextent size AUTOALLOCATE 31 oracle基础 表空间 改变表空间可用性ALTERTABLESPACEtablespace name online offline 改变表空间读写状态ALTERTABLESPACEtablespace name READWRITE READONLY 改变表空间名称ALTERTABLESPACEtablespace nameRENAMETOnew tbs name设置默认表空间ALTERDATABASEDEFAULTTABLESPACEtablespace name 数据库默认表空间ALTERDATABASEDEFAULTTEMPORARYtablespace name 数据库默认临时表空间删除表空间DROPTABLESPACEtablespace name INCLUDINGCONTENTS ANDDATAFILES 32 oracle基础 用户管理 建立用户createuserETLidentifiedby defaulttablespaceETLDATA01temporarytablespaceTEMP1profileDEFAULT 给用户授权grantconnecttoetl grantselect catalog roletoetl grantselectanytabletoetl grantcreatesessiontoetl grantaltersessiontoetl grantcreatetabletoetl grantcreateviewtoetl grantcreatetypetoetl grantcreatesynonymtoetl grantcreatesequencetoetl grantcreateproceduretoetl 33 oracle基础 用户管理 修改口令ALTERUSERuser nameIDENTIFIEDBYnew password 修改默认表空间ALTERUSERuser nameDEFAULTTABLESPACEnew tablespace name 锁定用户ALTERUSERetlACCOUNTLOCK 解锁用户ALTERUSERetlACCOUNTUNLOCK 删除用户DROPUSERusername CASCADE 34 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 35 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 表对应索引需重建 36 oracle基础 表建立及管理 截断表 快速删除数据释放空间保留表结构 是DDL语句不可回退TRUNCATETABLEtable name delete是DML语句可回退删除表 PURGE是oracle10g新特性表永久删除DROPTABLEtable name CASCADECONSTRAINTS PURGE 从回收站Recyclebin恢复被删除表FLASHBACKTABLEtable nameTOBEFOREDROP 清空回收站PURGERECYCLEBIN 查看回收站SELECT FROMRECYCLEBIN 37 oracle基础 表建立及管理 表SELECT FROMUSER TABLES 对象SELECT FROMUSER OBJECTS 表占用的空间selectsum 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 FROMdual SELECTdbms metadata get ddl TABLE upper tmpcg ctl paravalue upper etl FROMdual 38 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 FROMdual SELECTdbms metadata get ddl INDEX upper idx tmpcg ctl paravalue upper etl FROMdual 39 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 FROMdual 40 oracle基础 分区表 ORACLE的分区 PartitioningOption 是一种处理超大型表的技术 分区是一种 分而治之 的技术 通过将大表和索引分成可以管理的小块 从而避免了对每个表作为一个大的 单独的对象进行管理 为大量数据提供了可伸缩的性能 分区通过将操作分配给更小的存储单元 减少了需要进行管理操作的时间 并通过增强的并行处理提高了性能 通过屏蔽故障数据的分区 还增加了可用性 ORACLE的分区表的划分方法包括 按字段值进行划分的范围分区 按字段的HASH函数值进行的划分HASH分区 先按范围划分 再按HASH划分的复合分区 在ORACLE9i中又增强了按字段值列表进行划分的列表 Listing 分区方法 管理员可以指定每个分区的存储属性 分区在宿主文件系统中的放置情况 这样便增加了对超大型数据库的控制粒度 granularity 分区可以被单独地删除 卸出或装入 备份 恢复 因此减少了需要进行管理操作的时间 还可以为表分区创建单独的索引分区 从而减少了需要进行索引维护操作的时间 此外 还提供了种类繁多的局部和全局的索引技术 分区操作也可以被并行执行 分区技术还提高了数据的可用性 当部分数据由于故障或其它原因不可用时 其它分区内的数据可不受影响继续使用 分区对应用是透明的 可以通过标准的SQL语句对分区表进行操作 Oracle的优化器在访问数据时会分析数据的分区情况 在进行查询时 那些不包含任何查询数据的分区将被忽略 从而大大提高系统的性能 总之 Oracle的分区表技术能满足高性能 高可用性 数据易管理性等综合需求 41 oracle基础 分区表设计原则 表的大小对于大表进行分区 将有益于大表操作的性能和大表的数据维护 通常当表的大小超过1 5GB 2GB 或对于OLTP系统 表的记录超过1000万 都应考虑对表进行分区 数据访问特性基于表的大部分查询应用 只访问表中少量的数据 对于这样表进行分区 可充分利用分区排除无关数据查询的特性 数据维护某些表的数据维护 经常按时间段删除成批的数据 例如按月删除历史数据 对于这样的表需要考虑进行分区 以满足维护的需要 因为删除 Delete 大量的数据 对系统开销很大 有时甚至是不可接受的 只读数据如果一个表中大部分数据都是只读数据 通过对表进行分区 可将只读数据存储在只读表空间中 对于数据库的备份是非常有益的 并行数据操作 ParallelDML 对于经常执行并行操作 如ParallelInsert ParallelUpdate等 的表应考虑进行分区 表的可用性当对表的部分数据可用性要求很高时 应考虑进行表分区 42 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 43 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 44 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 45 oracle基础 分区表 分区信息查询SELECT FROMUSER TAB PARTITIONS SELECT FROMUSER TAB SUBPARTITIONS SELECT FROMUSER IND PARTITIONS 加上WHERESTATUS UNUSABLE 可查询失效的分区索引SELECT FROMUSER IND SUBPARTITIONS 加上WHERESTATUS UNUSABLE 可查询失效的子分区索引SELECT FROMUSER SEGMENTS 46 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 parfile 参数文件 包含参数说明的文件的名称parallel 执行并行加载 默认FALSE file 要从以下对象中分配区的文件 47 oracle基础 sqlldr 控制文件LOADDATAINFILE test txt INTOTABLEDEPTREPLACEFIELDSTERMINATEDBY OPTIONALLYENCLOSEDBY DEPTNO DNAME LOC 48 oracle基础 文本数据导出 Oracle没有提供将数据导出到一个文件的工具 但是我们可以用SQL Plus的select及format数据来输出到一个文件 setechooffnewpage0space0pagesize0feedoffheadofftrimspoolonspooloradata txtselectcol1 col2 col3fromtab1wherecol2 XYZ spooloff 49 oracle基础 数据库备份导入导出 EXP exphelp y有三种主要的方式 完全 用户 表 1 完全 EXPSYSTEM MANAGERBUFFER 64000FILE C FULL DMPFULL Y如果要执行完全导出 必须具有特殊的权限2 用户模式 EXPSONIC SONICBUFFER 64000FILE C SONIC DMPOWNER SONIC这样用户SONIC的所有对象被输出到文件中 3 表模式 EXPSONIC SONICBUFFER 64000FILE C SONIC DMPOWNER SONICTABLES SONIC 这样用户SONIC的表SONIC就被导出 50 oracle基础 数据库备份导入导出 IMP imphelp y具有三种模式 完全 用户 表 1 完全 IMP sys sys123assysdba BUFFER 64000FILE C FULL DMPFULL Y2 用户模式 IMPSONIC SONICBUFFER 64000FILE C SONIC DMPFROMUSER SONICTOUSER SONIC这样用户SONIC的所有对象被导入到文件中 必须指定FROMUSER TOUSER参数 这样才能导入数据 3 表模式 impSONIC SONICBUFFER 64000FILE C SONIC DMPOWNER SONICTABLES SONIC 这样用户SONIC的表SONIC就被导入 51 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 52 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表示 53 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时为止 54 oracle基础 常用函数 字符型函数LTRIM c1 c2 功能 删除左边出现的字符串默认为空格RTRIM c1 c2 功能 删除右边出现的字符串TRIM c1 功能 删除左边和右边出现的空格SUBSTR c1 n1 n2 功能 取子字符串 说明 多字节符 汉字 全角符等 按1个字符计算 参数 在字符表达式c1里 从n1开始取n2个字符 若不指定n2 则从第y个字符直到结束的字串 55 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 的第一天日期 56 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 57 oracle基础 常用函数 转换函数TO DATE X c2 c3 功能 将字符串X转化为日期型 参数 c2 c3 字符型 参照to char TO NUMBER X c2 c3 功能 将字符串X转化为数字型 参数 c2 c3 字符型 参照to char 58 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列的最大值 59 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是连续排序 有两个第二名时仍然跟着第三名 60 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条件 值1THENRETURN 翻译值1 ELSIF条件 值2THENRETURN 翻译值2 ELSIF条件 值nTHENRETURN 翻译值n ELSERETURN 缺省值 ENDIF 61 oracle基础 常用函数 其他函数case whenthen whenthen else end 功能 当 时 返回对应当不为条件表达式时 与函数decode 相同 decode 参数 默认为true 逻辑型 类型要与类型一致 若为字符型 则也要为字符型 注意点 1 以CASE开头 以END结尾2 分支中WHEN后跟条件 THEN为显示结果3 ELSE为除此之外的默认情况 类似于高级语言程序中switchcase的default 可以不加4 END后跟别名5 只返回第一个符合条件的值 剩下的when部分将会被自动忽略 得注意条件先后顺序 62 oracle基础 存储过程 FUNCTIONCREATEORREPLACEFUNCTIONFN ACCT ORG T acct typeINVARCHAR acct noINVARCHAR RETURNVARCHAR2IScore orgVARCHAR2 10 BEGINcore org test RETURNcore org EXCEPTIONWHENOTHERSTHENNULL ENDFN ACCT ORG T 63 oracle基础 存储过程 PROCEDURECREATEORREPLACEPROCEDURESP ACCT ORG T acct typeINVARCHAR acct noINVARCHAR IScore orgVARCHAR2 10 BEGINcore org test EXCEPTIONWHENOTHERSTHENNULL ENDSP ACCT ORG T DataStage框架 65 server一般安装在unix linux系统 也可以安装在windows系统上 详细安装步骤参见相关文档 客户端安装client只能安装在windows系统上 详细安装步骤参见相关文档服务端安装 Dat