Periodically download txt files from a remote FTP server and import the local Oracle database

The business data in the group was previously based on the ETL process in the region acquisition-group cleaning-distribution area, and since the group established the software company, the business data required by the subsidiaries must be applied to the group, but the business system's underlying DC did not provide the corresponding data interface. So there's the need to prototype: Get the TXT data file from the remote FTP server regularly and import the data into the local Oracle database.

There are 40-50 txt files that need to be downloaded from FTP every day, except for the first full-volume data file at 10G or so, the download time is longer, and the subsequent incremental files are within 500M.

Techniques and tools to use: FtpClient, Java Multithreading, Oracle-provided SQLLDR commands, Oracle provides merge, Spring Quartz timing package, ExtJS foreground framework.

The specific process of functional modules is not described in detail here, the main record in the implementation process encountered problems have been resolved:

Download files:1, using multi-threaded download files from FTP, each thread needs to open a new ftpclient, or there will be FTP deny connection, link timeout and other issues.

2, the use of ftpclient storefile (remote, local) method, more than a certain number of files will appear after the program blocked, suspended animation phenomenon, find a lot of solutions are not, Later using the FtpClient retrievefilestream (remote) method to obtain the input stream manually loop read () Save TXT file, there is no suspended animation, blocking phenomenon. The following is the download code:

1  Public voidrun () {2       Try { 3               //ftp.enterlocalpassivemode (); switch to local mode4 Ftp.setfiletype (ftp.ascii_file_type);5               6File Dirfile =NewFile (dir);7               8               if(!dirfile.exists ()) {9 dirfile.mkdirs ();Ten               } OneFile LocalFile =NewFile (dir+localfilename);  A  -OutputStream OS =NewFileOutputStream (localfile); -            theInputStream is =Ftp.retrievefilestream (ftpfilename); -              -BufferedReader br =NewBufferedReader (NewInputStreamReader (IS, "UTF-8")); -              +BufferedWriter BW =NewBufferedWriter (NewOutputStreamWriter (OS, "GBK"))); -              +             intRead =Br.read (); A              while(Read!=-1){ at Bw.write (read); -Read =Br.read (); -             } - br.close (); - is.close (); - Bw.flush (); in bw.close (); - os.close (); to            //Ftp.completependingcommand ();  +End =System.currenttimemillis (); -             the             //Log Records *         $}Catch(IOException e) {Panax Notoginseng              //Log Records - e.printstacktrace (); the        } +}

Execute the SQLLDR command:

1, to solve the field of each table there need to format the conversion, so through each table in the file system to establish a TXT file to access the header field information, the field information will be used to generate SQLLDR required CTL file, table header field information Description TXT file content as follows:

Id,bill_no,bill_type,status,biz_type,ref_bill_no,ref_bill_type,ref_biz_type,company_no,order_unit_no,order_ Unit_name,sys_no,supplier_no,supplier_name,store_no,store_name,order_no,contract_no,invoice_no,send_out_date Date ' Yyyy-mm-dd HH24:MI:SS ', tax_rate,merchandiser,create_user,create_time timestamp ' yyyy-mm-dd HH24:MI:SS ', Auditor,audit_time timestamp ' yyyy-mm-dd HH24:MI:SS.ff9 ', remark,update_time timestamp ' yyyy-mm-dd HH24:MI:SS ', trans_ No,send_detail_total,zone_yyyymm,sharding_flag,is_imported,yw_update_time timestamp ' yyyy-mm-dd HH24:MI:SS '

The specific conversion format supported by Sqlldr can be queried on-line.

2, Sqlldr required CTL file content format:

 load   data characterset ZHS16GBK infile   "  data File location     

3. Generate SQLLDR command:

Sqlldr Database user Name+"/"+Database Password+"@"+database service name); control='...'                //control file absolute path bad='...'                          //exception data File save pathLog='...'                          //log File save path parallel=True//Parallel Errors=0                        //number of error rows allowed rows= the             //5000 lines to submit a skip=1                //skips the first line of the data file Bindsize=10000000ReadSize=10000000      //Buffer size

4. Turn on multithreading execute the SQLLDR command on the command line:

adjourned

Periodically download txt files from a remote FTP server and import the local Oracle database