如何利用Oracle外部表导入文本文件的数据
来源:互联网 发布:女生围巾推荐 知乎 编辑:程序博客网 时间:2024/06/06 04:44
同事最近在忙数据一致性比对工作,需要对不同文本文件中的数据进行比对,有的文件较大,记录较多,如果用普通的文本编辑器打开的话,很显然,会很卡,甚至打不开。
基于此,可将该文本文件的数据导入到数据库中,在集合的层面进行比对。
那么如何将文本文件的数据导入到数据库中呢?在这里,主要利用了Oracle的外部表特性。
Oracle外部表支持两种类型的驱动:一种是ORACLE_LOADER,外部表的数据必须来源于文件文件,另一种则是ORACLE_DATAPUMP,外部表的数据必须是二进制dump文件,该dump文件是先前将Oracle内部表的数据导入到外部表中填充的文件。很显然,Oracle希望将数据保留在数据库内部进行处理。
首先,我们来看一下该文本文件的大小及记录。
[oracle@node2 ~]$ du -sm P_20150626010000_2002371.0003479598 274 P_20150626010000_2002371.0003479598[oracle@node2 ~]$ wc -l P_20150626010000_2002371.0003479598 2899265 P_20150626010000_2002371.0003479598
从上面的输出可以看出,该文件274M,有2899265条记录。
其次,构建创建外部表语句。
CREATE TABLE emp_load (subsid number(18), servnumber VARCHAR2(20 CHAR), subsprodid NUMBER(18), prodid VARCHAR2(32 CHAR), startdate date, enddate date, owner VARCHAR2(4 CHAR))ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY tmp ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY "|" ( subsid DECIMAL EXTERNAL, servnumber CHAR(20), subsprodid DECIMAL EXTERNAL, prodid CHAR(32), startdate date "yyyymmddhh24miss", enddate date "yyyymmddhh24miss", owner CHAR(4) ) ) LOCATION ('P_20150626010000_2002371.0003479598') );
注意,目录tmp必须存在,因为我是在scott用户下执行的,所以scott用户必须对该路径有读写权限。
第三、在scott用户下执行该建表语句。
第四、查看生成的外部表是否有问题
SQL> select count(*) from emp_load; COUNT(*)------------------ 2899265
记录与wc-l查看的记录数吻合。
注意,建表过程中没有报错并不一定意味着数据已经成功加载在外部表中。必须通过查询外部表来判定数据是否已成功加载,倘若有错误提示,可参看当前目录下生成的日志文件,具体在本例中,是EMP_LOAD_2000.bad和EMP_LOAD_2000.log。
当然,外部表中的数据只能查询,不能做DML操作,譬如,随机删除表中的一条数据
SQL> delete from emp_load where rownum=1;delete from emp_load where rownum=1 *ERROR at line 1:ORA-30657: operation not supported on external organized table
如果想对该外部表数据进行DML操作,可先将外部表的数据导入到内部表中。具体步骤如下:
SQL> create table test as select * from emp_load where 1=0;Table created.Elapsed: 00:00:00.26SQL> INSERT /*+ APPEND */ INTO test select * from emp_load;2899265 rows created.Elapsed: 00:01:00.29SQL> select * from test where rownum<=100;select * from test where rownum<=100 *ERROR at line 1:ORA-12838: cannot read/modify an object after modifying it in parallelElapsed: 00:00:00.10SQL> commit;Commit complete.Elapsed: 00:00:00.07SQL> select * from test where rownum<=100;
在这里,为了节省时间,我用了直接路径插入,可以看出,插入近300万数据,只用了1分左右的时间,考虑到我虚拟机上的数据库,只给它分配了300M的内存,加载的效率还是相当可观的。
SQL> show parameter memoryNAME TYPE VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address integer 0memory_max_target big integer 300Mmemory_target big integer 300Mshared_memory_address integer 0
注意:在SQL*PLUS中,number字段的输出默认为10,这样会导致对于937116510102250300这样的数值,可能会显示为9.3712E+17,在这里,可通过set numwidth 18来显示完整的number字段的值。
====================================================================
create table 表名(
列名1,列名2,......
)
organization external ###说明创建外部表
(
type 访问类型 ###一般是 ORACLE_LOADER ,ORACLE_DATAPUMP
default directory 路径名 ####指定默认目录对象
access parameter ( ###数据源文件与表中行之间的映射关系
records delimited by 设置分隔符 ###一般是newline
[badfile 路径名:'文件名'] ##存放错误日志的目录和文件名
[logfile 路径名:'文件名'] ##日志的目录和文件名
[discardfile 路径名:'文件名'] ###废弃文件存放的目录和文件名
fields terminated by '分隔符' ###设置文件中字段的分隔符
[ missing field values are null] ##无值字段的处理
[reject rows with all null fields] ##无值行的处理
( 列名1, 列名2, .....)
)
location (路径名:'数据文件名')
)
[reject limit(或unlimited) 数值 ] ###设置多少行转换失败时返回oracle错误,默认为0
[parallel]
==================================================经常有需求向表中导入大量的数据,使用insert不靠谱,太慢
oracle提供了sqlldr的工具
也有时需要讲数据导入到文本,oracle的spool可以轻松实现
方便的实现oracle导出数据到txt、txt导入数据到oracle
一、导出数据到txt
这里用all_objects表做测试
SQL> desc all_objects; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1)
拿object_id,object_name做导出、导入测试
这里需要一些设置满足数据导出的样式
vi exp_table.sql
set line 1000 --设置行的长度set pagesize 0 --输出不换页set feedback off --默认的当一条sql发出的时候,oracle会给一个反馈,比如说创建表的时候,如果成功命令行会返回类似:Table created的反馈,off后不显示反馈set heading off --不显示表头信息set trimspool on --如果trimspool设置为on,将移除spool文件中的尾部空set trims on --去掉空字符set echo off; --显示start启动的脚本中的每个sql命令,缺省为onset colsep '|' --设置分隔符set termout off --不在屏幕上显示结果spool db1.txt --记录数据到db1.txtselect object_id,object_name from all_objects; --导出数据语句spool off --收集完毕exit
一切就绪后导出数据
[oracle@centos5 ~]$ sqlplus test/test @exp_table.sql SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:35:14 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@centos5 ~]$ sed -i 's/ //g' db1.txt --可选,去除每行开头部分的空格[oracle@centos5 ~]$ more db1.txt 20|ICOL$44|I_USER128|CON$15|UNDO$29|C_COBJ#3|I_OBJ#25|PROXY_ROLE_DATA$
导出后检查数据的记录数是否正确
[oracle@centos5 ~]$ cat db1.txt |wc -l49988[oracle@centos5 ~]$ sqlplus test/testSQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:36:21 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select count(*) from all_objects; COUNT(*)---------- 49988 --数据正确
二、从txt导入数据到oracle
sqlldr是通过一个control文件设定后,从文本导入数据
建立一张测试表
SQL> create table tb_sqlldr (id number,name varchar2(50));Table created.
建立一个control文件
vi tb_sqlldr.ctl
load data infile 'db1.txt' --数据来源文本append into table tb_sqlldr --数据导入到表tb_sqldr中,导入方式为追加,如果想覆盖fields terminated by "|" --4、字段终止于X'09',是一个制表符(tab)(id,name) --定义对应的字段名称,注意顺序
导入数据分成四种模式,可以根据需求选择:
APPEND // 原先的表有数据 就加在后面
INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值
REPLACE // 原先的表有数据 原先的数据会全部删除
TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据
执行导入操作
sqlldr userid=test/test control=tb_sqlldr.ctl
差不多5w的数据短短2s解决
执行导入后验证数据
SQL> select count(*) from tb_sqlldr; COUNT(*)---------- 49988
导入成功
再执行一次导入操作,由于设置为追加
SQL> select count(*) from tb_sqlldr; COUNT(*)---------- 99976
记录翻倍
sqlldr还有很多参数供选择,比如log、bad这些,查看帮助即可
[oracle@centos5 ~]$ sqlldrSQL*Loader: Release 10.2.0.4.0 - Production on Thu Jun 13 17:07:26 2013Copyright (c) 1982, 2007, Oracle. All rights reserved.Usage: SQLLDR keyword=value [,keyword=value,...]Valid Keywords: userid -- ORACLE username/password control -- control file name log -- log file name bad -- bad file name data -- data file name discard -- discard file name discardmax -- number of discards to allow (Default all) skip -- number of logical records to skip (Default 0) load -- number of logical records to load (Default all) errors -- number of errors to allow (Default 50) rows -- number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) bindsize -- size of conventional path bind array in bytes (Default 256000) silent -- suppress messages during run (header,feedback,errors,discards,partitions) direct -- use direct path (Default FALSE) parfile -- parameter file: name of file that contains parameter specifications parallel -- do parallel load (Default FALSE) file -- file to allocate extents from skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) readsize -- size of read buffer (Default 1048576)external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)columnarrayrows -- number of rows for direct path column array (Default 5000)streamsize -- size of direct path stream buffer in bytes (Default 256000)multithreading -- use multithreading in direct path resumable -- enable or disable resumable for current session (Default FALSE)resumable_name -- text string to help identify resumable statementresumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)date_cache -- size (in entries) of date conversion cache (Default 1000)PLEASE NOTE: Command-line parameters may be specified either byposition or by keywords. An example of the former case is 'sqlldrscott/tiger foo'; an example of the latter is 'sqlldr control=foouserid=scott/tiger'. One may specify parameters by position beforebut not after parameters specified by keywords. For example,'sqlldr scott/tiger control=foo logfile=log' is allowed, but'sqlldr scott/tiger control=foo log' is not, even though theposition of the parameter 'log' is correct.
- 如何利用Oracle外部表导入文本文件的数据
- 如何利用Oracle外部表导入文本文件的数据
- 如何利用SQLSERVER2000的DTS 导入数据到Oracle?
- Oracle利用外部表实现数据的迁移
- 将外部数据导入oracle
- 文本文件中的数据导入到Oracle数据库表
- Oracle利用传输表空间导出导入数据的步骤
- oracle如何把数据导入不同的表空间
- 外部数据导入Oracle数据库命令----sqlldr
- ORACLE-SQLLOAD导入外部数据详解
- ORACLE-SQLLOAD导入外部数据详解
- oracle 导入文本文件
- 导入文本文件到ORACLE
- 如何有效地利用oracle的数据字典
- 从文本文件导入数据到数据库表
- oracle数据迁移快速导入导出:利用sqluldr2导出数据和sqlldr导入数据的方法
- sqlldr导入文本文件数据
- 利用TOAD实现EXCEL数据在oracle的导入导出
- java GC机制与内存分配策略
- 《CSS权威指南》读书笔记7
- Redis SETNX命令实现分布式锁
- 数据库
- 写程序熟悉read()、write()、stat()、open()、access()等函数
- 如何利用Oracle外部表导入文本文件的数据
- 架构设计之Spring-Session分布式集群会话管理
- swustoj蛇形填数(1183)
- HTPPS请求 证书 解决方案
- 计算机文化学习笔记2
- MySQL 视图
- Manacher算法
- 几种内部排序算法
- EGL接口介绍