Oracle中使用CTL文件实现快速数据导入(oracle中ctl文件)
在Oracle数据库中,我们经常需要将数据导入到表中。虽然Oracle提供了多种数据导入工具和技术,但使用CTL文件是最常用的一种方式。CTL文件是Control文件的缩写,是Oracle数据导入工具SQL*Loader所使用的控制文件,可以大大加快数据导入速度,同时也可以提高数据导入的精度和可靠性。
一、CTL文件的基本结构
一个典型的CTL文件包含了多个控制语句,每个控制语句对应一个数据导入步骤。控制语句由一个关键字和相应的参数组成,下面是一个简单的CTL文件示例:
LOAD DATA
INFILE mydata.csv
APPEND INTO TABLE mytable
FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY
TRLING NULLCOLS
(id,
name,
age)
上述CTL文件包含了三个控制语句,分别指定了数据的来源、目的表格和字段映射规则。第一条控制语句指定了加载数据的位置,第二条控制语句指定了数据要追加到哪个表,第三条控制语句指定了数据的字段映射规则。
二、CTL文件的常用控制语句
1. LOAD DATA:指定要加载的数据来源。
2. INFILE:指定数据来源,可以是一个文件、一个命名管道或者一个外部表格。
3. APPEND INTO TABLE:指定数据要追加到哪个表中。
4. FIELDS TERMINATED BY:指定字段之间的分隔符。
5. OPTIONALLY ENCLOSED BY:指定字段内容是否使用引号包围。
6. TRLING NULLCOLS:指定当某个字段值不存在时,后面的空值也要当成有效值导入。
7. (… ):用圆括号包围字段名,指定数据导入的字段顺序和名称。
8. BEGINDATA:指定数据开始的行号。
三、CTL文件的使用方法
1. 创建CTL文件
首先要创建一个CTL文件,格式如下:
LOAD DATA
INFILE 文件名
APPEND INTO TABLE 表名
FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY
TRLING NULLCOLS
(字段名1,
字段名2,
…)
其中:
文件名是指要导入的数据文件的路径和名称。
表名是指要将数据导入的数据库表的名称。
字段名是指数据文件中每个字段的对应数据库表的字段名称。
2. 运行CTL文件
将数据文件和CTL文件放在同一个文件夹下(建议),执行以下命令:
sqlldr 用户名/密码@实例名 control=CTL_file_name.ctl
其中:
用户名/密码是指连接到数据库所需要的用户名和密码。
实例名是指连接到的Oracle数据库实例名。
CTL_file_name是指CTL文件的名称(不包含扩展名)。
在执行完上述命令后,Oracle SQL Loader将开始导入数据到数据库表中。
四、例子
以下是DELIMITED DATA导入的CTL文件示例:
load data
infile /u01/oradata/yourSchema/data/data_file.csv
append into table EMP
fields terminated by ,
optionally enclosed by
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE to_date(:HIREDATE, YYYY-MM-DD ) ,
SAL,
COMM,
DEPTNO
)
注意:数据文件名和路径需要根据实际情况修改,需要保证CTL文件和数据文件处于同一目录中,并且Oracle用户要有读取数据文件的权限。
最后附上python程序批量.sqlldr:
代码:
import os
import re
class Oracle_SQLLDR:
def __init__(self, user, pswd, instance):
:param user: 用户名
:param pswd: 密码
:param instance: 实例名或IP+port
self._user = user
self._pswd = pswd
self._instance = instance
def _connect(self, tnsp):
连接数据库
os.environ[ NLS_LANG ] = AMERICAN_AMERICA.AL32UTF8
self.tnsp = tnsp
l = os.popen(f sqlplus -S {self._user}/{self._pswd}@{self.tnsp} set linesize 32000 )
return l
def get_path(self, file_path):
返回文件名和路径
file_path = os.path.realpath(file_path)
dir_path, file_dir = os.path.split(file_path)
file_name, file_ext = os.path.splitext(file_dir)
return dir_path, file_name, file_ext
def ctl_auto_create(self, ctl_path, table_name, fields):
自动创建ctl文件
:param ctl_path: ctl文件路径
:param table_name: 表名称
:param fields: 数据表头 [ id , name , age ]
with open(ctl_path, w ) as file:
file.write(f LOAD DATA\n )
file.write(f APPEND INTO TABLE {table_name}\n )
file.write(f FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY \ TRLING NULLCOLS\n )
field_len = len(fields)
for i, f in enumerate(fields):
if i == 0:
file.write(f ({f} )
elif i
file.write(f ,{f} )
else:
file.write(f ,{f}) )
def _sqlldr(self, tnsp, ctl_path, file_path):
以append模式追加数据到数据库
:param tnsp: 数据库连接串
:param ctl_path: ctl文件路径
:param file_path: txt文件路径
:return: 返回结果
dir_path, file_name, file_ext = self.get_path(file_path)
os.popen( chmod 644 + file_path)
os.popen( chmod 644 + ctl_path)
self._connect(tnsp)
#print(f ctl_file_name={ctl_path} )
#print(f file_name={file_name + file_ext} )
sqlldr_cmd = f sqlldr {self._user}/{self._pswd}@{self._instance} control={ctl_path} data={file_path} log=data/{file_name}.log bad=data/{file_name}.bad errors=1000000 direct=true
ret = os.popen(sqlldr_cmd)
ret_text = ret.read().replace( \n , ).replace( , )
err_count = re.findall(r (\d+)rowsnotloadeddueto\:\s*ORACLE , ret_text)
err_count = int(err_count[0]) if len(err_count) 0 else 0
del_count = re.findall(r Totaldel*tions.*\=(\d+) , ret_text)
del_count = int(del_count[0]) if len(del_count) 0 else 0
ins_count = re.findall(r Total*inserts.*\=(\d+) , ret_text)
ins_count = int(ins_count[0]) if len(ins_count) 0 else 0
return { ret : ret_text, err : err_count, del : del_count, ins : ins_count}
if __name__ == __mn__ :
user = username
pswd = password
tnsp = dbip:port/dbname
osld = Oracle_SQLLDR(user, pswd, tnsp)
ctl_path = ./ctrl.ctl
table_name = tablename
fields = [ id , name , age ]
osld.ctl_auto_create(ctl_path, table_name, fields)
data_path = ./data.data
ret = osld._sqlldr(tnsp, ctl_path, data_path)
print(f"{data_path}, {ret} )
代码运行,输出结果如下。
./data.data, { ret : SQL*Loader: Release 19.0.0.0.0 Production on Thu Jul 22 16:09:28 2021\n Version 19.3.0.0.0\n , err : 0, del : 0, ins : 101}
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 Oracle中使用CTL文件实现快速数据导入(oracle中ctl文件)
相关文章
- 数据库Recovering Data from Oracle Database using DMP Files(dmp文件oracle)
- Oracle找不到文件的问题处理(oracle未找到文件)
- 积大Oracle 编译包:体积可观的缩小版(oracle编译包体)
- Oracle 存储迁移:实现数据高效运转(oracle存储迁移)
- Oracle数据库导出与导入指南(oracle导出与导入)
- Oracle 数据库导入步骤及实施指南(oracle导入步骤)
- 导出Oracle数据库:导入与导出技术指南(oracle数据库的导入)
- 探究Oracle过程长度对数据库性能的影响(oracle过程长度)
- Oracle查询默认排序解析(oracle查询默认排序)
- Oracle数据库中如何快速导入表?(oracle如何导入表)
- Oracle数据库快速导入指南(oracle导数据)
- Oracle数据导出为CSV文件的技术指南(oracle导出csv文件)
- 使用mdb导入Oracle从文件到数据库(mdb 导入oracle)
- 比较分析Hive与Oracle的异同(hive与oracle)
- Oracle背后隐藏的复杂机制(oracle为啥复杂)
- Oracle保护文件信息创造安全网络(oracle保留文件名)
- Oracle数据库中插入记录的使用技巧(oracle中插入语句)
- Oracle中复制一张表的步骤(oracle中复制一张表)
- 如何在Oracle中取得系统时间(oracle中取系统时间)
- Oracle中是否会包括空值(oracle中%包括空吗)
- Oracle 64位系统,实现超越极限(oracle 64位系统)