ORACLE DB 的學習者們

2018年1月18日 星期四

資料庫從TXT檔案直接匯入或讀取 -- DIRECT PATH LOAD 與 External Tables

DIRECT PATH LOAD : 從TXT檔案直接匯入資料庫

ORACLE可以從純文字檔直接匯入到資料表，稱之為 DIRECT PATH LOAD ，有別於傳統的匯入方式 (CONVENTIONAL)，一定使用INSERT指令，一定有COMMIT。

  1. 傳統的INSERT，會使用Database Buffer Cache，且資料寫入在HIGH WATER MARK範圍之內，DIRECT PATH LOAD跳過 DATABASE BUFFER CACHE，且寫入DATAFILE的時候， 從HIGH WATER MARK之後插入，稱之為 DATA SAVE
  2. 傳統方式需要COMMIT， DIRECT PATH LOAD不用COMMIT
  3. 傳統方式會受限於Referential Integrity Constraints ，且TRIGGER可被觸發。 DIRECT PATH LOAD必須停掉Referential Integrity Constraints，且TRIGGER不會觸發
  4. DIRECT PATH LOAD時，TABLE會被LOCK不允許DML
  5. DIRECT PATH LOAD插入資料時，只允許UNIQUE、PRIMARY KEY和NOT NULL 三個Constraints
  6. DIRECT PATH LOAD 不能被用在CLUSTER TABLE
DIRECT PATH LOAD 需要下列條件或準備
  1. INPUT DATA FILES : 純文字檔，通常以逗點來分隔欄位
  2. CONTROL FILE : 跟資料庫的 CONTROLFILE沒有關係，是一個純文字檔，描述匯入的程序和指令
  3. LOG FILES : 寫在前述 CONTROL FILE，紀錄匯入紀錄
  4. BAD FILES : 寫在前述 CONTROL FILE，已經讀出，但被SQL LOADER拒絕不能匯入的資料寫入此，例如因為格式不合
  5. REJECT FILES : 寫在前述 CONTROL FILE，因違反表格限制條件而不能匯入的資料寫入此
  6. 匯入前，必須在資料庫先將TABLE建立好
在DB先建立下列表格 
CREATE TABLE HR.DEPTS
(
  DEPTNO  NUMBER(2) NOT NULL,
  DNAME   VARCHAR2(14) ,
  LOC     VARCHAR2(13)
);
在主機中建立下列純文字檔，命名為 depts.txt 
60,CONSULTING,TORONTO
70,HR,OXFORD
80,EDUCATION
在主機中建立下列 CONTROL FILE指令檔，命名為 DEPTS.CTL 
load data
infile 'depts.txt'            --匯入的檔案
badfile 'depts.bad'           --壞掉的檔案放此
discardfile 'depts.dsc'       --被拒絕匯入的放此
append                        --以插入方式匯入到WATER MARK後面
into table depts              --插入新增到在資料庫中建好的表格
fields terminated by ','      --欄位，以逗點分隔
trailing nullcols             --空白的欄位，以NULL填寫
(deptno integer external(2),  --欄位的定義
dname,
loc)
指令檔建好以後，在命令列下執行下列命令 
[oracle@db01 plsql]$ sqlldr userid=hr/XXXXX control=DEPTS.CTL direct=true

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 05:23:56 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Load completed - logical record count 3.
匯入完成以後，看看紀錄檔產生些什麼 
[oracle@db01 plsql]$ cat DEPTS.log

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 05:23:56 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   DEPTS.CTL
Data File:      depts.txt
  Bad File:     depts.bad
  Discard File: depts.dsc
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table DEPTS, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     2   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  NEXT     *   ,       CHARACTER


Table DEPTS:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Thu Jan 18 05:23:56 2018
Run ended on Thu Jan 18 05:23:58 2018

Elapsed time was:     00:00:01.15
CPU time was:         00:00:00.01
插入後的表格可看到結果如下

使用SQL*LOADER : 從TXT檔案直接匯入資料庫

使用 SQL*LOADER 需要下列條件或準備
  1. DIRECTORY物件 : 需存在建好的目錄物件，並且賦予讀寫權限
  2. INPUT DATA FILES : 純文字檔，通常以逗點來分隔欄位，需放在上述目錄物件指向之位置
  3. SQL FILES : 在TOAD中執行即可
在上述目錄的位置放置要匯入的純文字檔，例如，在/home/oracle/bkup 目錄下有兩個純文字檔 empxt1.dat 和 empxt2.dat，如下圖所示
例如，文字檔有四個欄位 
123,Dah-Lih,Jeng,01-jan-2018
124,Mary,Jeng,02-may-2017
125,Ezekiel,Jeng,12-feb-2015
然後，透過下列SQL將文字匯入到資料庫中 
create table extab_employees
( employee_id   number(4),
  first_name    varchar2(20),
  last_name     varchar2(25),
  hire_date     date)
organization external
(  type  ORACLE_LOADER  DEFAULT DIRECTORY EXT_DIR
   ACCESS PARAMETERS
   (  records delimited by newline
      badfile EXT_DIR:'empxt%a_%p.bad'
      logfile EXT_DIR:'empxt%a_%p.log'
      fields terminated by ','
      missing field values are null
      ( employee_id, first_name, last_name,
        hire_date char date_format date mask "dd-mon-yyyy"))
        LOCATION ('empxt1.dat','empxt2.dat'))
        parallel reject limit unlimited;
匯入後查詢可確認是否成功

External Tables : 從資料庫直接讀取主機上以純文字建立之表格

External Tables 的使用是在有需要從主機的純文字檔讀取資料，可是又不想把該文字檔匯入成表格的時候，可以改用此方法，讀取外部的表格。該表格建立以後，可以用以查詢，使用於JOIN，或用以建立VIEWS或子查詢，但是不可以進行任何DML。

External Tables 的建立只要透過create table，配合 關鍵字 organization external ，就可以達成，但需要下列條件或準備

  1. 建立DIRECTORY : 以SYSDBA登入，建立DIRECTORY以後，將讀寫權限ASSIGN給該TABLE的USER
  2. 將純文字檔放至該DIRECTORY指定之目錄。
  3. 建立 External Tables
先建立DIRECTORY (以SYSDBA) 
CREATE OR REPLACE DIRECTORY 
EXT_DIR AS 
'/home/oracle/bkup';
GRANT READ, WRITE ON DIRECTORY EXT_DIR TO HR;
把剛才的純文字檔複製到該目錄去

[oracle@db01 plsql]$ cp depts.txt ../bkup/

複製完成後，使用TOAD或是SQLPLUS執行下列SQL，建立外部表格 

create table new_dept
( deptno number(2),
  dname varchar2(14),
  loc varchar2(13))
  organization external (
  type oracle_loader
  default directory ext_dir
  access parameters
  ( records delimited by newline
    badfile 'depts.bad'
    discardfile 'depts.dsc'
    logfile 'depts.log'
    fields terminated by ','
    missing field values are null)
    location ('depts.txt'));
該語法跟前述 DIRECT PATH LOAD的CONTROL FILE非常接近，然後透過TOAD可以看到該表格已經建立成功
當然，上述的CONTROL FILE稍嫌複雜，也可反向操作，將前述使用SQL LOADER匯入表格的命令，改為讓它產生EXTERNAL TABLE的SQL 

[oracle@db01 plsql]$  sqlldr userid=hr/xxxx control=DEPTS.CTL external_table=generate_only  

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Jan 18 08:07:03 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
執行完畢，去檢查所產生的LOG檔，該檔案有完整的步驟：建立目錄、執行SQL產生EXTERNAL TABLES ，但是在進行下一步前，使用編輯器將一些該檔案內的路徑、檔案名稱、TABLE名稱等資訊換成正確的路徑和名稱，同時先將文字檔放在目錄所指向的路徑上，最後再去執行該檔案的SQL就可順利建立一個EXTERNAL TABLE。

External Tables 支援以下的特性

  1. 欄位加密 (Encrypted Columns)
  2. Clustered Tables
  3. Different partition at unload and load time, and others (see Notes)

與External Table 有關的資料字典

External Tables 有關的資料字典如下
  1. (DBA/ALL/USER)_EXTERNAL_TABLES
  2. (DBA/ALL/USER)_EXTERNAL_LOCATIONS
  3. (DBA/ALL/USER)_TABLES, and others
張貼者： 於