Oracle高速导入CSV数据\高速导出sql数据

超、凢脫俗 2022-02-17 07:57 629阅读 0赞

说着是导入CSV，其实格式不定，文本也可以，只是导入数据几十个G的时候大多数都是用CSV来存的，所以就把名字写成了导入CSV。

这里说的是导入数据，之前我也写过导入库、表、schema等方式，对于外来的纯数据，没有怎么纪录过，所以这篇文章主要是针对数据的导入。

方式用Oracle自带的SQL loader。命令式sqlldr，这个工具是安装完Oracle就有的，不需要额外下载。效率有多快呢？我4个varchar的字段每秒差不多写入20万条吧。

注意，这种超快的导入方式是在服务器上执行的，所以需要相关权限，一般拿不到权限的就用PLsql吧，虽然相对SQL loader慢，不过对于小批量数据还是可以的

本次我导入的数据大小为25G.

环境：centos7

工具：SQL Loader

相关原理：官网文档

操作方法：1、需要定义好一个Oracle执行的脚本， .ctl结尾的文件

2、需要sqlldr 命令启动导入程序，程序会找到之前写的脚本按照脚本信息去处理

我的具体步骤：

1、把数据文件上传到服务器指定文件夹
    
    2、写好导入配置脚本
    
    3、启动命令

脚本格式：

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行，可以不写
    LOAD DATA
    INFILE "users_data.csv" --指定外部数据文件，可以写多个 INFILE "another_data_file.csv" 指定多个数据文件
    --这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，
    
    --操作类型参数，多个可选，新表也可以不用
    /*
    truncate  用 truncate table 来清除表中原有记录
    insert     --为缺省方式，在数据装载开始时要求表为空
    append  --在表中追加新记录
    replace  --删除旧记录(用 delete from table 语句)，替换成新装载的记录
    */
    
    INTO TABLE users -- 要插入记录的表
    Fields terminated by "," -- 数据中每行记录用 "," 分隔
    Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时
    
    /*
    trailing nullcols --表的字段没有对应的值时允许为空，都有值得新表可以不写
    */
    
    (
      virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号，可以不写
      --字段类型可以指定，也可以不写，先默认是表的字段类型，或者默认是 CHARACTER，  
      user_id number, 
      user_name,
      login_times,
      last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换
    --建议除了时间指定字段类型，其他的都不要写，根据表默认就行了
    )

我的脚本：

因为我有多个不同表的文件导入，所以写了多个配置文件，举例两个吧：
    
    1、单文件，无空值，无特殊情况，新表：
    load data
    infile '/home/oracle/csvdata/tcd.csv'
    into table T_NEW_COMMODITY_DUMMY_DTAILE
    fields terminated by ',' optionally enclosed by '"'
    (
    id,
    cardno,
    passwd
    )
    
    
    
    2、多文件，表中已有数据不能删除和覆盖，只能增加，数据有空值:
    [oracle@dwmain tcd]$ ls
    tcd0.bad  tcd0.csv  tcd1.csv  tcd2.bad  tcd2.csv  tcd3.csv  tcd4.csv  tcd.csv  tcd.ctl  tcd.log
    [oracle@dwmain tcd]$ cat tcd.ctl
    load data
    infile '/home/oracle/csvdata/tcd/tcd1.csv' 
    infile '/home/oracle/csvdata/tcd/tcd2.csv' 
    infile '/home/oracle/csvdata/tcd/tcd3.csv' 
    infile '/home/oracle/csvdata/tcd/tcd4.csv'
    
    append --增加参数
    
    into table T_NEW_COMMODITY_DUMMY
    fields terminated by ',' optionally enclosed by '"'
    (
    id,
    cardno,
    passwd,
    codeurl
    )

脚本写完了后，在linux的Oracle用户角色下执行脚本命令，不要去SQL plus里面执行

命令：

sqlldr 用户/密码 control=/配置文件地址/配置文件.ctl

执行后就开始数据刷屏了，速度极快，在ctl文件目录下会打出日志，报错了可以去看日志，跟多详细参数和定义，可以去官网看文档，很详细的。

一点小秘密：

如果导入的时候有错误产生，那么错误数据会被存到bad文件中，该文件和数据文件、日志文件同目录下，举个例子，我导入的数据varchar设定为100，但是有些数据超多了100，所以报错没有导入，怎么处理呢，先修改字段长度，之后不是覆盖重新导入，而是直接把配置文件的指向改成bad文件，bad文件的格式和导入文件格式会是一致的，直接导入bad文件就行了，不用重新开始来个几千万的导入。导入完后bad即使数据成功了也不会被删除，那么怎么看是否被处理成功了呢，再去查看日志文件没有报错就是成功的。如果有，就再继续处理。

--------------------

2019-6.24更新

Oracle的sql报表，在plsql工具上通过导出保存的形式，速度也不算是很慢，在十几万的数据级别以内感觉还可以，但是当我们到达千万级别的大型报表的时候，sqlplus导出几个G的数据是按天计算的，所以大型报表需要一个高性能数据导出工具。这里我推荐大家使用主流的sqluldr2工具进行导出。

sqluldr2工具不是Oracle默认的导出工具，是第三方导出工具，需要放在Oracle安装的机器上运行，所以没有DBA权限的朋友可以洗洗睡了。安装教程网上很多，就不做说明。具体的使用方法是：

./sqluldr2_linux64_10204.bin user=root/12345678 sql=c.sql file=c.csv head=yes field=">"

执行以上命令，意思是运行sqluldr2的bin文件，后面的参数是账户名 密码 ，sql指向写好的sql文件，这里的路径可以是绝对路径，如果不是绝对路径默认是在sqluldr2 bin文件的相同目录下，file指定是导出文件名和文件形式，超级数据不可能用excel保存，毕竟excel有数据量限制。head代表是否导出sql的字段名，就像sqlplus里面的copy有copy和copy with header这中选项一样,field是两个字段间以什么作为分隔符，我这里用的是 > 符号，当然如果导出的字段内容有>符号的，建议换个不会出现的符号作为分隔符。

一般使用这个工具都是到大数据，导出的csv没有G级别大小也应该有七八百兆大小吧。所以导出的csv给运营或者分析师，他们用excel是打不开的哦，所以建议在导出完后直接做一个数据切分，命令很简单，用linux的split：

split -l 1000000 qjs.csv -d qjs_

这里-l 1000000是代表按照1000000数据行切分成一个新数据，后面按qjs\_前缀命名。如果切分了三个文件出来，那么就会有qjs\_0,qjs\_1,qjs\_2三个文件，之后直接用excel导入可以，注意，split参数切分后只有第一个文件会带上导出的字段名，所以之后的几个文件字段名（列名）需要手动添加。

导出速度有多块呢？

![20190624090841580.png][]

4秒100万够不够？还是持续写入方式哦，就算几个G也没问题，如果数据特别大，在执行命令的时候查询阶段会比较耗时，一般来说几分钟可能要的，倒是导出速度非常快，所以建议在sql脚本里面写好多线程查询，这样可以减少查询时间，我的复杂报表一般是开20线程查询的。

[20190624090841580.png]: /images/20220217/89c6dd8432a741a0bcfee0fe57c38216.png