sqlldr导入

编写control文件：

1. 普通加载DM_F_SCREEN_DCARD_GR_M.ctl

OPTIONS(ROWS=1000000,bindsize=409600000)

LOAD DATA

CHARACTERSET UTF8

APPEND

INTO TABLE DM_F_SCREEN_DCARD_GR_M

FIELDS TERMINATED BY '\t'    --以\t制表符分隔

TRAILING NULLCOLS

(month_id,PROVINCE_ID,REGION_ID,

DCARD_USR_CNT "NULLIF(:DCARD_USR_CNT,'NULL')",  --将NULL转为空

OTNET_USR_CNT "NULLIF(:OTNET_USR_CNT,'NULL')",

OTNET_ARPU"NULLIF(:OTNET_ARPU,'NULL')",

MONET_ARPU"NULLIF(:MONET_ARPU,'NULL')"

)

2. 优化，并行加载，并行加载会破坏表的索引

OP_RE_ST_LTE_EC.ctl

OPTIONS(skip=1,DIRECT=TRUE,PARALLEL=TRUE,ROWS=1000000,date_cache=1000000,bindsize=20971520,readsize=20971520)

UNRECOVERABLE

LOAD DATA

CHARACTERSET UTF8

INFILE'/opt1/FTPDATA/data/KPI/2017102004/LTE_AH_perf_eutrancell_20171020040000.csv'

INFILE'/opt1/FTPDATA/data/KPI/2017102004/LTE_BJ_perf_eutrancell_20171020040000.csv'

INFILE '/opt1/FTPDATA/data/KPI/2017102004/LTE_GS_perf_eutrancell_20171020040000.csv'

APPEND

INTO TABLE OP_RE_ST_LTE_EC

WHEN START_TIME<>'START_TIME'     --过滤掉START_TIME=’start_time’

FIELDS TERMINATED BY '|'

TRAILING NULLCOLS

(Province_id                 ,

city_id                      ,

related_nodeb                ,

oid                          ,

Start_time  "to_date(:START_TIME,'yyyy/mm/ddhh24:mi:ss')",

Indicatorid                  ,

nbrRrcAttConnEstab           ,

LOAD_TIME SYSDATE 

)

3. 导入

sqlldr cachedb/cachedb@obidb_new  control=${CTL_DIR}/${table}.ctl skip=1  log=${LOG_DIR}/${D_DIR}/LTE_*_${table_name}_${D_DIR}.log bad=${BAD_DIR}/${D_DIR}/LTE_*_${table_name}_
${D_DIR}.bad