Showing posts with label External Tables. Show all posts
Showing posts with label External Tables. Show all posts

Using Oracle External Tables



  • External tables do not reside in the database, and can be in any format for which an access driver is provided.
  • You can select, join, or sort external table data.
  • You can also create views and synonyms for external tables.
  • You CANNOT execute DML operations (UPDATE, INSERT, or DELETE) , and CANNOT create indexes on external tables.

External tables and Data Warehouse
  • Exporting data from a database:
    • External tables provide a framework to unload the result of an arbitrary SELECT statement into a platform-independent Oracle-proprietary format that can be used by Oracle Data Pump.
  • External tables provide a valuable means for performing basic extraction, transformation, and loading (ETL) tasks that are common for data warehousing.

Creating External tables
  • CREATE TABLE...ORGANIZATION EXTERNAL statement.
  • This statement creates only metadata in the data dictionary.
  • External tables can be thought of as views that allows running any SQL query against external data without requiring that the external data first be loaded into the database.

Accessing External Data
  • An access driver is the actual mechanism used to read the external data in the table.
  • When you use external tables to unload data, the metadata is automatically created based on the data types in the SELECT statement.

Oracle Database provides two access drivers for external tables:
  • ORACLE_LOADER driver (default)
    • allows the reading of data from external files using the Oracle loader technology.
    • provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility.
  • ORACLE_DATAPUMP driver
    • lets you unload data: read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.








Scenario 1:
Unload data to flat files and create External Table.
ORACLE_LOADER driver









Step 1 - Export data from table sh.customers

/* Step 1 - Export data from table sh.customers 
   Procedure UNLOAD_DATA receives table_name and table_owner as parameter and 
     unloads the table data to a flat file */

set serveroutput on

DECLARE
  procedure unload_data( 
       p_table_name in varchar2,
       p_owner  in varchar2)
  IS
   sqltext varchar2(1000);
   v_textrecord varchar2(2000);
   cv sys_refcursor;

   fdest utl_file.file_type;
   type FileAttrRec is Record (
                      vfilexists BOOLEAN,
                      vfilelength number,
                      vblocksize  binary_integer);
   vfilerec fileattrrec;
   vfilename varchar2(30);

    procedure get_sql (
       p_sqltext in out varchar2,
       p_table_name in varchar2,
       p_owner in varchar2) 
    IS
      cursor c is
        select column_name
        from dba_tab_columns
        where table_name = p_table_name and owner = p_owner
        order by column_id;
      i integer;
      sql_stmt varchar2(2000);
      from_clause varchar2(100);
    begin
      i :=0;
      sql_stmt := 'SELECT ';
      from_clause :=  'FROM ' || p_owner ||'.' || p_table_name;
      for r in c loop
        if i = 0 then
          sql_stmt := sql_stmt || ' ' || r.column_name;
          i := 1;
        else
          sql_stmt := sql_stmt || '|| ''|''|| ' || r.column_name;
        end if;
      end loop;
      sql_stmt := sql_stmt || ' as qrystr ' || chr(10) || from_clause;
      p_sqltext := sql_stmt;
      -- dbms_output.put_line( sql_stmt);
    end get_sql; 

  begin
  -- 1. Get Query SQL
   dbms_output.enable(20000);
   sqltext := '';  
   get_sql(sqltext, p_table_name, p_owner);
   --dbms_output.put_line('Select stmt:' || sqltext); 

  -- 2. Open cursor for query. Write each row in the OS file
       vfilename := 'unload_' || p_owner ||'_'|| p_table_name || '.dat';
       utl_file.fgetattr('DATA_DUMP_DIR', vfilename, vfilerec.vfilexists, 
                              vfilerec.vfilelength, vfilerec.vblocksize);
       if vfilerec.vfilexists then 
          fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'a', 2048);
          dbms_output.put_line('Destination file exists. Appending..'); 
       else
          fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'w', 2048);
       end if;
  
   open cv for sqltext;
   LOOP
       fetch cv into v_textrecord;
       EXIT WHEN cv%NOTFOUND;
       utl_file.put_line(fdest, v_textrecord, true);
   END LOOP;
   utl_file.fclose(fdest);
   close cv; 
  END unload_data;

BEGIN
 unload_data('CUSTOMERS' , 'SH');
END;


Step 2 - Split unloaded file and create external table using the resulting files

$ ls -ltr
total 482928
...
-rw-r--r-- 1 oracle oinstall  10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat

-- spliting the file in two smaller files
$ split -30000 unload_SH_CUSTOMERS.dat unload_SH_CUSTOMERS.dat
$ ls -ltr
total 503760
...
-rw-r--r-- 1 oracle oinstall  10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat
-rw-r--r-- 1 oracle oinstall   5768182 2012-03-20 22:15 unload_SH_CUSTOMERS.dataa
-rw-r--r-- 1 oracle oinstall   4893721 2012-03-20 22:15 unload_SH_CUSTOMERS.datab

 Step 2 - Create external table using unloaded file(s)
 
 -- create the external table
SQL>  Create table ext_customers1
     ( 
         "CUST_ID"                NUMBER,
         "CUST_FIRST_NAME"        VARCHAR2(20 BYTE),
         "CUST_LAST_NAME"         VARCHAR2(40 BYTE),
         "CUST_GENDER"            CHAR(1 BYTE),
         "CUST_YEAR_OF_BIRTH"     NUMBER(4,0),
         "CUST_MARITAL_STATUS"    VARCHAR2(20 BYTE),
         "CUST_STREET_ADDRESS"    VARCHAR2(40 BYTE),
         "CUST_POSTAL_CODE"       VARCHAR2(10 BYTE),
         "CUST_CITY"              VARCHAR2(30 BYTE),
         "CUST_CITY_ID"           NUMBER,
         "CUST_STATE_PROVINCE"    VARCHAR2(40 BYTE),
         "CUST_STATE_PROVINCE_ID" NUMBER,
         "COUNTRY_ID"             NUMBER,
         "CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
         "CUST_INCOME_LEVEL"      VARCHAR2(30 BYTE),
         "CUST_CREDIT_LIMIT"      NUMBER,
         "CUST_EMAIL"             VARCHAR2(30 BYTE),
         "CUST_TOTAL"             VARCHAR2(14 BYTE),
         "CUST_TOTAL_ID"          NUMBER,
         "CUST_SRC_ID"            NUMBER,
         "CUST_EFF_FROM" DATE,
         "CUST_EFF_TO" DATE,
         "CUST_VALID" VARCHAR2(1 BYTE)
       )
  ORGANIZATION EXTERNAL 
  (
     TYPE ORACLE_LOADER
     DEFAULT DIRECTORY data_pump_dir
     ACCESS PARAMETERS
      (
          records delimited by newline
          badfile data_pump_dir:'cust1ext%a_%p.bad' 
          logfile data_pump_dir:'cust1ext%a_%p.log'
          fields terminated by '|'
          missing field values are null
      )
     LOCATION ('unload_SH_CUSTOMERS.dataa', 'unload_SH_CUSTOMERS.datab')
  )
  PARALLEL
  REJECT LIMIT UNLIMITED;

table EXT_CUSTOMERS1 created.

SQL> select table_name, type_name, default_directory_name, reject_limit, access_parameters, property 
 from dba_external_tables;
 
TABLE_NAME      TYPE_NAME       DEFAULT_DIRECTORY_NAME   REJECT_LIMIT    ACCESS_PARAMETERS                       
--------------- --------------- ------------------------ --------------- ------------------------------------------
EXT_CUSTOMERS1   ORACLE_LOADER   DATA_PUMP_DIR            UNLIMITED       records delimited by newline             
                                                                          badfile data_pump_dir:'cust1ext%a_%p.bad'
                                                                          logfile data_pump_dir:'cust1ext%a_%p.log' 
                                                                          fields terminated by '|'                  
                                                                          missing filed values are null             
 

SQL> select count(*) from ext_customers1;

  COUNT(*)
----------
     55500









Scenario 2: Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver






Step 1
  • (a) Use CREATE TABLE ... ORGANIZATION EXTERNAL ... AS SELECT ...
  • This will create a file with the data resulting from the specified query.
  • The file is created with a binary format that can only be read by the ORACLE_DATAPUMP access driver
  • The example below creates an external table and populates the dump file for the external table with the data from table sh.customers.

connect sh/**;

SQL> create table customers1_xt
  organization external
  ( 
    type oracle_datapump
    default directory data_pump_dir
    location ('customers1_xt.dmp')
  )
  as select * from sh.customers;

table created.

SQL> 


(b) Check the OS files created: 

$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall      123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log

(c) Compare descriptions of customers and customers1_xt tables.

SQL> desc customers;

Name                   Null     Type         
---------------------- -------- ------------ 
CUST_ID                NOT NULL NUMBER       
CUST_FIRST_NAME        NOT NULL VARCHAR2(20) 
CUST_LAST_NAME         NOT NULL VARCHAR2(40) 
CUST_GENDER            NOT NULL CHAR(1)      
CUST_YEAR_OF_BIRTH     NOT NULL NUMBER(4)    
CUST_MARITAL_STATUS             VARCHAR2(20) 
CUST_STREET_ADDRESS    NOT NULL VARCHAR2(40) 
CUST_POSTAL_CODE       NOT NULL VARCHAR2(10) 
CUST_CITY              NOT NULL VARCHAR2(30) 
CUST_CITY_ID           NOT NULL NUMBER       
CUST_STATE_PROVINCE    NOT NULL VARCHAR2(40) 
CUST_STATE_PROVINCE_ID NOT NULL NUMBER       
COUNTRY_ID             NOT NULL NUMBER       
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25) 
CUST_INCOME_LEVEL               VARCHAR2(30) 
CUST_CREDIT_LIMIT               NUMBER       
CUST_EMAIL                      VARCHAR2(30) 
CUST_TOTAL             NOT NULL VARCHAR2(14) 
CUST_TOTAL_ID          NOT NULL NUMBER       
CUST_SRC_ID                     NUMBER       
CUST_EFF_FROM                   DATE         
CUST_EFF_TO                     DATE         
CUST_VALID                      VARCHAR2(1)


SQL> desc customers1_xt;

Name                   Null     Type         
---------------------- -------- ------------ 
CUST_ID                NOT NULL NUMBER       
CUST_FIRST_NAME        NOT NULL VARCHAR2(20) 
CUST_LAST_NAME         NOT NULL VARCHAR2(40) 
CUST_GENDER            NOT NULL CHAR(1)      
CUST_YEAR_OF_BIRTH     NOT NULL NUMBER(4)    
CUST_MARITAL_STATUS             VARCHAR2(20) 
CUST_STREET_ADDRESS    NOT NULL VARCHAR2(40) 
CUST_POSTAL_CODE       NOT NULL VARCHAR2(10) 
CUST_CITY              NOT NULL VARCHAR2(30) 
CUST_CITY_ID           NOT NULL NUMBER       
CUST_STATE_PROVINCE    NOT NULL VARCHAR2(40) 
CUST_STATE_PROVINCE_ID NOT NULL NUMBER       
COUNTRY_ID             NOT NULL NUMBER       
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25) 
CUST_INCOME_LEVEL               VARCHAR2(30) 
CUST_CREDIT_LIMIT               NUMBER       
CUST_EMAIL                      VARCHAR2(30) 
CUST_TOTAL             NOT NULL VARCHAR2(14) 
CUST_TOTAL_ID          NOT NULL NUMBER       
CUST_SRC_ID                     NUMBER       
CUST_EFF_FROM                   DATE         
CUST_EFF_TO                     DATE         
CUST_VALID                      VARCHAR2(1)  



(d) Check data on customers1_xt and compare with original table

SQL> select count(*) from customers1_xt;

COUNT(*)               
---------------------- 
55500

SQL> select * from customers minus select * from customers1_xt;

no rows selected


(e) You CANNOT perform DML on the external table:

SQL> delete from customers1_xt where cust_id = 100055;
delete from customers1_xt where cust_id = 100055
            *
ERROR at line 1:
ORA-30657: operation not supported on external organized table


(f) You can also Create an external table with multiple dump files and with a degree of parallelism higher than one.

SQL> create table customers1_xtp3
 organization external
 (
   type oracle_datapump
   default directory data_pump_dir
   location('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp' )
 )
 parallel 3
 as select * from sh.customers;

$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall      123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log
-rw-r--r-- 1 oracle oinstall       41 2012-03-21 10:13 CUSTOMERS1_XTP3_2690.log
-rw-r--r-- 1 oracle oinstall       41 2012-03-21 10:13 CUSTOMERS1_XTP3_2737.log
-rw-r--r-- 1 oracle oinstall       41 2012-03-21 10:13 CUSTOMERS1_XTP3_2739.log
-rw-r--r-- 1 oracle oinstall       41 2012-03-21 10:13 CUSTOMERS1_XTP3_2741.log
-rw-r----- 1 oracle oinstall  2990080 2012-03-21 10:13 customers1_xtp32.dmp
-rw-r----- 1 oracle oinstall  3792896 2012-03-21 10:13 customers1_xtp33.dmp
-rw-r----- 1 oracle oinstall  3592192 2012-03-21 10:13 customers1_xtp31.dmp


(g) You can transfer or copy the dump file(s) and use them for another external table either in the same or in a different database. 

SQL> Create table customers2_xt
     ( 
         "CUST_ID"                NUMBER,
         "CUST_FIRST_NAME"        VARCHAR2(20 BYTE),
         "CUST_LAST_NAME"         VARCHAR2(40 BYTE),
         "CUST_GENDER"            CHAR(1 BYTE),
         "CUST_YEAR_OF_BIRTH"     NUMBER(4,0),
         "CUST_MARITAL_STATUS"    VARCHAR2(20 BYTE),
         "CUST_STREET_ADDRESS"    VARCHAR2(40 BYTE),
         "CUST_POSTAL_CODE"       VARCHAR2(10 BYTE),
         "CUST_CITY"              VARCHAR2(30 BYTE),
         "CUST_CITY_ID"           NUMBER,
         "CUST_STATE_PROVINCE"    VARCHAR2(40 BYTE),
         "CUST_STATE_PROVINCE_ID" NUMBER,
         "COUNTRY_ID"             NUMBER,
         "CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
         "CUST_INCOME_LEVEL"      VARCHAR2(30 BYTE),
         "CUST_CREDIT_LIMIT"      NUMBER,
         "CUST_EMAIL"             VARCHAR2(30 BYTE),
         "CUST_TOTAL"             VARCHAR2(14 BYTE),
         "CUST_TOTAL_ID"          NUMBER,
         "CUST_SRC_ID"            NUMBER,
         "CUST_EFF_FROM" DATE,
         "CUST_EFF_TO" DATE,
         "CUST_VALID" VARCHAR2(1 BYTE)
       )
  ORGANIZATION EXTERNAL 
  (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY data_pump_dir
     LOCATION ('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp')
  );

SQL> select count(*) from customers2_xt;

COUNT(*)               
---------------------- 
55500   

SQL> Select * from sh.customers minus select * from customers2_xt;

no rows selected.