Oracle external table just like external table in Greenplum

posted Feb 23, 2013, 9:10 AM by Sachchida Ojha   [ updated Feb 23, 2013, 5:08 PM ]
In the Greenplum database, we use external table combined with gpfdist to read the data from a  csv file located in in a directory and then then load that data in a regular table. This is a great feature of Greenplum database as gpfdist protocol uses MPP architecture to load the data. this is amazingly fast. I have tested it in an environment where I have a DCA connected with DIA.  The steps involved are

1. Place the .csv file in data directory of  DIA server.
2. start the gpfdist on DIA with the location same as csv file data directory.
3. Create external table and regular table in the greenplum database.
4. insert into <regular_table> select * from <external_table>.


After loading this data, I was curious if I can do the same thing in Oracle database. Oracle database can also use Oracle external table to load the data in the same way as Greenplum does. Here is the sample.

1. Create a directory in the oracle database and grant READ/WRITE priv to the user running the test.

CREATE OR REPLACE DIRECTORY mydir AS '/home/oracle/exttable';
GRANT READ ON DIRECTORY mydir TO sachi;
GRANT WRITE ON DIRECTORY mydir TO sachi;

2. Copy the .csv file to /home/oracle/exttable directory. Make sure that file is readable.

3. Create external table in oracle.  Here is the sample of 2 distinct set of data.

a)

056,baker,beth,mary
057,smith,teddy,john

2)

SCHEMA NAME                    TABLE NAME                     ROW COUNT                                                                                                                                                                                                                                     
------------------------------ ------------------------------                            ----------                                                                                                                                                                                                                                    
HR                       EMPLOYEE                                         504567 


External table for COMMA delimited file (a)

CREATE TABLE emp_load
    (employee_number      CHAR(3),
     employee_last_name   CHAR(20),
     employee_middle_name CHAR(15),
     employee_first_name  CHAR(15)
     )
  ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY mydir
     ACCESS PARAMETERS
       (RECORDS DELIMITED BY NEWLINE
        BADFILE mydir:'EMP.BAD'
        LOGFILE mydir:'EMP.LOG'
        FIELDS TERMINATED BY ','
       )
    LOCATION ('external_table_test1.dat')
)
REJECT LIMIT UNLIMITED;

SQL> select * from emp_load;

EMP EMPLOYEE_LAST_NAME   EMPLOYEE_MIDDLE EMPLOYEE_FIRST_
--- -------------------- --------------- ---------------
056 baker                beth            mary
057 smith                teddy           john

SQL>


External table for TAB delimited file (b)

CREATE TABLE tblcnt
(
SCHEMA_NAME CHAR(1028),
TABLE_NAME CHAR(1028),
ROW_COUNT CHAR(1028)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE mydir:'tablecount.BAD'
LOGFILE mydir:'tablecount.LOG'
FIELDS TERMINATED BY X'9'
)
LOCATION('tablecount.txt')
)
REJECT LIMIT UNLIMITED;

SQL> select * from tblcnt;

SCHEMA_NAME
----------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------
ROW_COUNT
--------------------------------
HR
EMPLOYEE
504567 

External table for fixed width file (c)

CREATE TABLE tblcnt1
(
SCHEMA_NAME CHAR(30),
TABLE_NAME CHAR(32),
ROW_COUNT CHAR(1028)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE mydir:'tablecount.BAD'
LOGFILE mydir:'tablecount.LOG'
FIELDS TERMINATED BY X'9' 
        (
SCHEMA_NAME POSITION(1:30),
TABLE_NAME POSITION(31:60),
ROW_COUNT   POSITION(61:1024)
)
)
LOCATION('tablecount.txt')
)
REJECT LIMIT UNLIMITED;

SQL> select * from tblcnt1;

SCHEMA_NAME
----------------------------------------------------------------
TABLE_NAME
----------------------------------------------------------------
ROW_COUNT
--------------------------------
HR
EMPLOYEE
504567 


Comments