Just another WordPress.com site

Archive for the ‘external table’ Category

mysql: Create external table using CSV engine and file

One thing that the CSV engine allows for is the instantaneous data transfer from flat files to information accessible via SQL in MySQL. For example, imagine having the need to load a file containing 5 million records into a MySQL table that uses the MyISAM storage engine:

mysql> desc client_detail;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| client_transaction_id | decimal(22,0) | YES  |     | NULL    |       |
| transaction_timestamp | datetime      | YES  |     | NULL    |       |
| transaction_comment   | varchar(30)   | YES  |     | NULL    |       |
+-----------------------+---------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> load data infile '/usr/local/mysql519/data/gim/flatdata.dat' 
    ->  into table client_detail
    ->  fields terminated by ',';
Query OK, 5023575 rows affected, 0 warnings (27.38 sec)
Records: 5023575  Deleted: 0  Skipped: 0  Warnings: 0

While MyISAM accepts the data pretty quickly, you can make all the data in the flat file instantly available to MySQL in the following manner. First, create a CSV table that mirrors the format of the incoming flat file:

mysql> create table client_detail_csv (client_transaction_id decimal(22,0),
    -> transaction_timestamp datetime, transaction_comment varchar(30))
    -> engine=csv;
Query OK, 0 rows affected (0.01 sec)

Then, go to the operating system and simply rename the operating system flat file to the file MySQL created for the new CSV table:

[mysql@linux1 ~]$ cd /usr/local/mysql519/data/gim

[mysql@linux1 gim]$ ls -l
total 785848
-rw-rw----  1 mysql mysql        35 May  1 06:54 client_detail_csv.CSM
-rw-rw----  1 mysql mysql         0 May  1 06:54 client_detail_csv.CSV
-rw-rw----  1 mysql mysql      8718 May  1 06:54 client_detail_csv.frm
-rw-rw----  1 mysql mysql      8718 May  1 05:56 client_detail.frm
-rw-rw----  1 mysql mysql 221037300 May  1 06:00 client_detail.MYD
-rw-rw----  1 mysql mysql      1024 May  1 06:00 client_detail.MYI
-rw-rw----  1 mysql mysql 291367350 May  1 05:55 flatdata.dat

[mysql@linux1 gim]$ mv flatdata.dat client_detail_csv.CSV

The rename operation occurs instantly as nothing data-wise changes at the operating system level. At that point, MySQL has all the data available to it:

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from client_detail_csv;
+----------+
| count(*) |
+----------+
|  5023575 |
+----------+
1 row in set (16.17 sec)

Thus, you’ve effectively loaded 5 million records instantaneously into MySQL. The great thing is the same effect would be experienced if the file contained 10 million, 20 million, or 100 million records.

Shell Script for updating an Oracle table through a file (from a remote Server)

a simple Script for updating an Oracle table through a file (from a remote Server)
we will b using a shell script for all this
process is described step by step as

1. first Of all create a virtual directory and external table use this page http://eaziweb.blogspot.com/2012/03/oracle-create-external-table.html
2. get file from remote server using ncftpget
3. create Oracle Procedure for data insertions
4. Oracle Procedure uses  UTL_FILE utility for logging different file events like success and failure

Summary

Serrver : server IP like 192.168.168.0
directory Object Name : DIR_3352
directory Object Path : /home/xyz/3352
External File : xtern_3352.txt
Upload File Name Format : add_20120612.csv
Upload File Format : 03007236317,09-MAR-12,C2676M0749
External Table :
create table xtern_3352 ( mobile varchar2(100),dateCol varchar2(20),id varchar2(20))
organization external (
type oracle_loader
default directory DIR_3352
access parameters (
records delimited by newline
FIELDS TERMINATED BY ‘,’
)
location (‘xtern_3352.txt’)
)
reject limit unlimited;
Procedure : TEST_PARAMETER

Shell Script :

######################################################################
##                                              USAGE                                                                         ##
##      call it as 3355.sh action-parameter                                                                          ##
##      eg. source 3355.sh add                                                                                          ##
##      current action parameters are                                                                                  ##
##      1.add                                                                                                                     ##
##      2.delete                                                                                                                  ##
######################################################################
#!/bin/sh
action=$1
day=1
date_var=`date -d “-$day days” +%Y-%m-%d`
#backup old files
mv -f /home/xyz/3352/xtern_3352.txt /home/xyz/3352/xtern_3352_`echo $date_var`.txt
#get the file from remote Server and put in /path/to/local/dir/ directory
ncftpget -u ftp-user-name -p paSSword rem.ote.Ser.ver.IP /path/to/local/dir/ /file`echo $date_var`_sub.csv
mv -f  /home/xyz/file`echo $date_var`_sub.csv  /home/xyz/new.txt
cat /home/xyz/new.txt | cut -d ‘ ‘ -f1> /home/xyz/xtern_3352.txt
source /file_validation_script.sh 3352/xtern_3352.txt
ORACLE_HOME=/Oracle/app/oracle/product/11.2.0/dbhome_1
export ORACLE_HOME
ORACLE_SID=orcl
export ORACLE_SID
/Oracle/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /NOLOG << EOF

connect db_user/db_pwd
set serveroutput on
exec TEST_PARAMETER(‘`echo $action`’);
EXIT;
echo “Now making file”
EOF

Oracle Procedure : TEST_PARAMETER

 ( action IN varchar2 )
  AS CURSOR C_CONTENT IS SELECT * from xtern_3352 ;
–============================================================

Initialization
–===========================================================–
datecheck number;
v_message varchar2(100);
ERROR_mobile_FORMAT exception;
ERROR_mobile_VALUE exception;
ERROR_DATE_VALUE exception;
ERROR_DUPLICATE_mobile exception;
ERROR_NO_mobile_FOUND  exception;
v_file  UTL_FILE.FILE_TYPE;
s_file  UTL_FILE.FILE_TYPE;
cur_date varchar2(100);
file_Name varchar2(100);
success_File_Name varchar2(100);
var_query varchar2(100);
–=============================================================–

                       Start of Logic  —                                                                  
–=============================================================–
BEGIN

    ——————————————————————————————-
    —                       File details               —
    ——————————————————————————————-
  SELECT  to_char(sysdate, ‘DDMonYYYY_HH24MISS’) into cur_date from dual;
  file_Name := ” ;
  success_File_Name := ” ;
  if ( action = ‘add’) then
    dbms_output.put_line(‘add Action’);
    file_Name := ‘add_error_’||cur_date||’.txt’ ;
    success_File_Name := ‘add_success_’||cur_date||’.txt’ ;
    –var_query := ‘insert into WHITE_LIST_auto values(‘||wl_DATA.mobile||’,’||wl_DATA.dateCol||’,’||wl_DATA.ID||’)’ ;
    var_query := ‘insert into WHITE_LIST_auto values(wl_DATA.mobile,wl_DATA.dateCol,wl_DATA.ID)’ ;
  else if ( action = ‘delete’) then
    dbms_output.put_line(‘delete Action’);
    file_Name := ‘delete_error_’||cur_date||’.txt’ ;
    success_File_Name := ‘delete_success_’||cur_date||’.txt’ ;
    var_query := ‘delete from WHITE_LIST_auto where mobile=wl_DATA.mobile’ ;
  else
    dbms_output.put_line(‘What do u want to do ??? Please Select an action (add or delete)’);
    return;
 
    dbms_output.put_line(‘else Action’);
    –return ‘Please Select an action (add or delete)’;
  end if;
  end if;

  SELECT  to_char(sysdate, ‘DDMonYYYY_HH24MISS’) into cur_date from dual;
  –file_Name := ‘error_’||cur_date||’.txt’ ;
  dbms_output.put_line(‘error is ‘|| file_Name);
  v_file := UTL_FILE.FOPEN(location     => ‘dir_3352’,
                           filename     => file_Name,
                           open_mode    => ‘w’,
                           max_linesize => 32767);
  –success_File_Name := ‘success_’||cur_date||’.txt’ ;
  dbms_output.put_line(‘success_File_Name is ‘|| success_File_Name);
  s_file := UTL_FILE.FOPEN(location     => ‘dir_3352’,
                           filename     => success_File_Name,
                           open_mode    => ‘w’,
                           max_linesize => 32767);

FOR wl_DATA in C_CONTENT
LOOP
  begin
    begin
    ——————————————————————————————-
    — Error Validations      —
    ——————————————————————————————-
      if length(wl_DATA.mobile) <> 11 or substr(wl_DATA.mobile,0,2) <> ’03’ then
raise ERROR_mobile_FORMAT;
      else if  ( (LENGTH(TRIM(TRANSLATE(wl_DATA.mobile, ‘+0123456789′,’ ‘))) is not null)  ) then
raise ERROR_mobile_VALUE;
      else if (is_date(wl_DATA.dateCol, ‘dd-mon-yy’) = 0) then
raise ERROR_DATE_VALUE;
      else if (CHECK_WHITELIST(wl_DATA.mobile) = 1 and action = ‘add’) then
raise ERROR_DUPLICATE_mobile;
      else if (CHECK_WHITELIST(wl_DATA.mobile) = 0 and action = ‘delete’) then
raise ERROR_NO_mobile_FOUND;
      else
          —————————————————————————–
 — Dumping Valid Data      —
 —————————————————————————–
 if ( action = ‘add’) then
   insert into WHITE_LIST_auto values(wl_DATA.mobile,wl_DATA.dateCol,wl_DATA.ID);
 else if ( action = ‘delete’) then
   delete from WHITE_LIST_auto where mobile=wl_DATA.mobile;
 else
   dbms_output.put_line(‘else Action’);
   return;
   –return ‘Please Select an action (add or delete)’;
   end if;
   end if;
   
–dbms_output.put_line(var_query);
–execute immediate var_query ;
–insert into WHITE_LIST_auto values(wl_DATA.mobile,wl_DATA.dateCol,wl_DATA.ID);
UTL_FILE.PUT_LINE(s_file,wl_DATA.mobile|| ‘,’ ||wl_DATA.dateCol|| ‘,’ ||wl_DATA.ID);
      end if;
      end if;
      end if;
      end if;
      end if;
      exception
          —————————————————————————–
 — Exception Handling      —
 —————————————————————————–
        when ERROR_mobile_FORMAT then
          v_message:=’Mobile number Format is invalid’;
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ‘,’ ||wl_DATA.dateCol|| ‘,’ ||wl_DATA.ID|| ‘,’ ||v_message);
        when ERROR_mobile_VALUE then
          v_message:=’Mobile number is invalid’;
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ‘,’ ||wl_DATA.dateCol|| ‘,’ ||wl_DATA.ID|| ‘,’ ||v_message);
when ERROR_DATE_VALUE then
          v_message:=’date is invalid’;
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ‘,’ ||wl_DATA.dateCol|| ‘,’ ||wl_DATA.ID|| ‘,’ ||v_message);
        when ERROR_DUPLICATE_mobile then
          v_message:=’Duplictae mobile’;
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ‘,’ ||wl_DATA.dateCol|| ‘,’ ||wl_DATA.ID|| ‘,’ ||v_message);
        when ERROR_NO_mobile_FOUND then
          v_message:=’No mobile Found’;
 UTL_FILE.PUT_LINE(v_file,wl_DATA.mobile|| ‘,’ ||wl_DATA.dateCol|| ‘,’ ||wl_DATA.ID|| ‘,’ ||v_message);
    end;
  end;
  END LOOP;
  UTL_FILE.FCLOSE(v_file);        
  UTL_FILE.FCLOSE(s_file);        
commit;

END;
–=============================================================

End of Logic
— =============================================================

oracle : create external table

 in browser type url ip-Of-Server:1158/em/console/

  1. in browser type url ip-Of-Server:1158/em/console/
  2. login there
  3. go to Schema-> Directory Objects
  4. click the “create” Button
  5. fill in the form
  6. * Name DIR_WLIST
    * Path

    /tmp/test_v_dir

    where Name= name of virtual directory and Path= path to the directory

  7. now thru ftp client (or putty ) create a file test.txt in /tmp/test_v_dir/
  8. then go to sqlplus
  9. type in follwing query

create table XTERN_test( student_name varchar2(100) )
organization external (
type oracle_loader
default directory DIR_WLIST
access parameters (
records delimited by newline
)
location (‘test.txt’)
)
reject limit unlimited;

oracle : external table

1. in browser type url ip-Of-Server:1158/em/console/

  1. in browser type url ip-Of-Server:1158/em/console/
  2. login there
  3. go to Schema-> Directory Objects
  4. click the “create” Button
  5. fill in the form
  6. * Name DIR_WLIST
    * Path
    /tmp/test_v_dir

    where Name= name of virtual directory and Path= path to the directory

  7. now thru ftp client (or putty ) create a file test.txt in /tmp/test_v_dir/
  8. then go to sqlplus
  9. type in follwing query
  10. create table XTERN_test( student_name varchar2(100) )
    organization external (
    type oracle_loader
    default directory DIR_WLIST
    access parameters (
    records delimited by newline
    )
    location (‘test.txt’)
    )
    reject limit unlimited;
%d bloggers like this: