关于sql:Oracle-sqlldr-使用

36次阅读

共计 6789 个字符，预计需要花费 17 分钟才能阅读完成。 

sqlldr 应用
# ctl_jpf.ctl
load data
infile 'jpf01.csv'
insert into table TEST_BUD_SN_ID
fields terminated by ','
TRAILING NULLCOLS
(
  SN,
  TEST_ID
)

# insert : 默认形式，在导入记录前要求表为空；# append : 在表中追加新导入的记录；表不为空时须要追加数据
# replace : 删除旧记录 (等价 delete from table 语句)，替换成新导入的记录；# truncate: 删除旧记录 (等价 truncate table 语句)，替换成新导入的记录；# into table 前面指定导入数据库表 USER_INFO，sed -i 's/jpf11/jpf12/g' ctl_jpf.ctl
sed -i 's/insert/append/g' ctl_jpf.ctl
sqlldr userid=IMES_T/'xxxxxx' control=ctl_jpf.ctl data=jpf12.csv log=jpflog.log bad=jpfbad.bad errors=99999999


sqlldr userid=IMES_T/'xxxxxx' control=test.ctl data=test.txt log=test.log bad=test.bad errors=99999999
# test.ctl
options(skip=1,BINDSIZE=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)
load data
infile '/home/oracle/USER_INFO.csv'
insert into table "USER_INFO"
fields terminated by ','
Optionally enclosed by "'"(MSISDN,PROVINCE_CODE,CREATE_TIME"to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')")

sqlldr userid=yuwen/xxxxxx control=loaddata_USER_INFO_direct.ctl direct=true
direct=true ,options 中的 BINDSIZE 换成了 COLUMNARRAYROWS，因为 BINDSIZE 是惯例门路绑定数组的大小，而 COLUMNARRAYROWS 是间接门路列数组的行数。options(skip=1,COLUMNARRAYROWS=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999)
load data
infile '/home/oracle/USER_INFO.csv'
insert into table "USER_INFO"
fields terminated by ','
Optionally enclosed by "'"(MSISDN,PROVINCE_CODE,CREATE_TIME"to_date(:CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss')")

https://www.jianshu.com/p/498c26ba95a7

Usage: SQLLDR keyword=value [,keyword=value,...]

局部关键字:
                userid -- ORACLE username/password
               control -- 管制文件
                   log -- 记录的日志文件
                   bad -- 坏数据文件
                  data -- 数据文件
               discard -- 抛弃的数据文件
            discardmax -- 容许抛弃数据的最大值        (默认全副)
                  skip -- Number of logical records to skip  (默认 0)
                  load -- Number of logical records to load  (默认全副)
                errors -- 容许的谬误记录数          (默认 50)
                  rows --（每次提交的记录数，如每 2000 条提交一次。默认: 惯例门路 64, 间接门路 全副，所以应用间接门路的话，效率会比一般的好太多太多）bindsize --（每次提交记录的缓冲区的大小，字节为单位，默认 256000)
                silent -- 禁止输入信息 (header,feedback,errors,discards,partitions)
                direct -- 应用直通门路形式导入 (默认 FALSE)
               parfile -- parameter file: name of file that contains parameter specifications。参数文件：蕴含参数阐明的文件的名称
              parallel -- 并行导入 ( 默认 FALSE，并行形式仅仅在 direct=true 形式时无效, 并行形式只反对 append 导入。留神：parallel 并不是让一个 sqlldr 语句起多个过程来加载数据, 而是不锁住加载表, 容许别的间接门路加载. 所以要使 parallel 起作用, 应该先将要加载的数据文件分成多个, 用多个 sqlldr 语句同时加载, 如下例: 
 skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默认 FALSE)，不容许 / 容许应用无用的索引 (默认 FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable(默认 FALSE, 这个最好设置为 TRUE)。不保护索引，将受到影响的索引标记为生效 (默认 FALSE)
                  file -- file to allocate extents from  要从以下对象中调配区的文件
   commit_discontinued -- commit loaded rows when load is discontinued (默认 FALSE)。提交加载中断时已加载的行 (默认 FALSE)
              readsize -- size of read buffer (默认 1048576)。读取缓冲区的大小 (默认 1048576)
        external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)。应用内部表进行加栽：NOT_USED,GENERATE_ONLY,EXECUTE (默认 NOT_USED)
       columnarrayrows -- number of rows for direct path column array (默认 5000)。间接门路列数组的行数 (默认 5000)
            streamsize -- size of direct path stream buffer in bytes (默认 256000)。间接路径流缓冲区的大小 (默认 256000, 单位字节)
        multithreading -- use multithreading in direct path。在间接门路中应用多线程
             resumable -- enable or disable resumable for current session (默认 FALSE)。启用或禁用以后的可复原会话 (默认 FALSE)
        resumable_name -- text string to help identify resumable statement。有助于标识可复原语句的文本字符串
     resumable_timeout -- wait time (in seconds) for RESUMABLE (默认 7200)。RESUMABLE 的等待时间 (以秒计)(默认 7200)
            date_cache -- size (in entries) of date conversion cache (默认 1000)。日期转换高速缓存的大小 (以条目计)(默认 1000)

LOAD DATA INFILE "users_data.csv"-- 指定内部数据文件，能够写多个 INFILE "another_data_file.csv" 指定多个数据文件 -- 这里还能够应用 BADFILE、DISCARDFILE 来指定坏数据和抛弃数据 -- 的文件，truncate -- 操作类型，用 truncate table 来革除表中原有记录
INTO TABLE users -- 要插入记录的表
Fields terminated by ","-- 数据中每行记录用 "," 分隔
Optionally enclosed by '"'-- 数据中每个字段用'"' 框起，比方字段中有 "," 分隔符时
trailing nullcols -- 表的字段没有对应的值时容许为空
(
virtual_column FILLER, -- 这是一个虚构字段，用来跳过由 PL/SQL Developer 生成的第一列序号
user_id number, -- 字段能够指定类型，否则认为是 CHARACTER 类型, log 文件中有显示
user_name,
login_times,
last_login DATE"YYYY-MM-DD HH24:MI:SS"-- 指定承受日期的格局，相当用 to_date() 函数转换)

0) 配置文件
  ************* 以下是 4 种装入表的形式 
  APPEND // 原先的表有数据 就加在前面 
  INSERT // 装载空表 如果原先的表有数据 sqlloader 会进行 默认值 
  REPLACE // 原先的表有数据 原先的数据会全副删除 
  TRUNCATE // 指定的内容和 replace 的雷同 会用 truncate 语句删除现存数据
    *************

import.ctl(含序列, 不能设 direct=true):
load data
infile '/home/oracle/data/import.dat'
append
into table sys_login_records_detail_1
fields terminated by ','
(
LOGIN_DATE DATE "yyyy-mm-dd" TERMINATED BY whitespace,
FILLER_1 FILLER,
GAME_ID,
FILLER_2 FILLER,
IP,
MAC,
NETBAR_ID,
PROVINCE,
CITY,
LOGIN_TIMES,
id POSITION(1:1) "sys_login_records_detail_seq_1.nextval"  --- 序列
)


import.ctl(不含序列, 可设 direct=true):
Load data
infile '/home/oracle/data/import.dat'
append
into table sys_login_records_detail_1
fields terminated by ','
(
LOGIN_DATE DATE "yyyy-mm-dd" TERMINATED BY whitespace,
FILLER_1 FILLER,
GAME_ID,
FILLER_2 FILLER,
IP,
MAC,
NETBAR_ID,
PROVINCE,
CITY,
LOGIN_TIMES
)

1) 老例 导入
sqlldr user/xxxxxx control=/home/oracle/data/install.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
##Space allocated for bind array:                  132352 bytes(64 rows)
##Elapsed time was:      00:01:45.85

2) 1000 条提交一次
sqlldr user/password control=/home/oracle/data/install.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
rows=1000 readsize=2068000 bindsize=2068000
##Space allocated for bind array:                 2068000 bytes(1000 rows)
##Elapsed time was:      00:00:22.80

3) 10000 条提交一次
sqlldr user/[email protected]
control=/home/oracle/data/install.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
rows=10000 readsize=20680000 bindsize=20680000
##Space allocated for bind array:                20680000 bytes(10000 rows)
##Elapsed time was:      00:00:20.25

4) 设置 direct=true, 含序列
sqlldr user/[email protected]
control=/home/oracle/data/install.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
direct=true
##ORA-01400: cannot insert NULL into ("EMOA"."sys_login_records_detail_1"."ID")

5) 设置 direct=true, 去掉序列字段, 最快的口头
#SQL> alter table emoa.sys_login_records_detail_1 drop column id;
sqlldr user/[email protected]
control=/home/oracle/data/export.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
direct=true
##Elapsed time was:      00:00:10.98
## 然而如果 有频频数据, 会把惟一索引置为 unusable, 要确保数据源已剔重

6) 设置 readsize 和 bindsize 到最大值, 高效又稳当的口头 , 终极 回收 计划
sqlldr user/[email protected]
control=/home/oracle/data/export.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
rows=100160 readsize=20971520 bindsize=20971520 PARALLEL=TRUE
##Space allocated for bind array:                20970240 bytes(10160 rows)
##Elapsed time was:      00:00:14.36


进步 SQL*Loader 的性能
  1) 一个简略而容易疏忽的问题是，没有对导入的表应用任何索引和 / 或束缚 (主键)。如果这样做，甚至在应用 ROWS= 参数时，会很明显降低数据库导入性能。2) 能够增加 DIRECT=TRUE 来进步导入数据的性能。当然，在很多状况下，不能应用此参数。3) 通过指定 UNRECOVERABLE 选项，能够敞开数据库的日志。这个选项只能和 direct 一起应用。4) 能够同时运行多个导入工作。惯例导入与 direct 导入形式的区别
  惯例导入能够通过应用 INSERT 语句来导入数据。Direct 导入能够跳过数据库的相干逻辑 (DIRECT=TRUE)，而间接将数据导入到数据文件中。1.direct 对性能的影响是微小的，如果为 Fasle（默认值），1 万的记录须要 100 秒，太慢了，起初设置为 TRUE，配合其余的设置，200 万的记录，330 秒搞定，这个速度和 MySQL 中的 select ....into 的效率差不多，应该还算很不错的了。不过因为我在 Oracle 中
对大对象进行了压缩，所以须要 10 分钟

2. 如果表中有索引的话，是不能指定 direct=TRUE 的，除非应用 skip_index_maintenance=TRUE，这个就是在导入的时候疏忽索引，所以在数据导入结束当前，查看索引的状态应该都是有效的，须要重建之, 如下 SQL 语句
select  * from dba_indexes where table_name='?'
alter  idnex index_name rebuild

不过在我测试过程中，这种形式是比拟正当的，重建索引比新建索引要快很多，如我 200W 的记录，重建主键只需 1 分钟多，新建的话则要 7 分钟。3. 在数据导入的过程，让该表不记录日志, 数据库不开启归档日志
  alter database noarchivelog
  alter table BLOG nologging

--End--

正文完
sql
发表至： sql
2022-10-18
 0
关于sql:实时数仓以upsert的方式读写Kafka数据以Flink112为例
关于sql:免费下载-冬季实战营第三期MySQL数据库进阶实战
关于sql:Mysql数据库按时间点恢复实战
关于sql:官宣｜Apache-Flink-1130-正式发布流处理应用更加简单高效