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. 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 |
|
|