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}
