Report implementation of extracting data based on Scriptella

1. Query Analyzer

--Work now, write tomorrow!

2. Report account and Query analysis field mappings

--Work now, write tomorrow!

3. ETL Tools

I am using Scriptella, the advantage of this tool is that I do not have to find the SQL of the specified data with XML tools to re-parse the package, this design for the later product maintenance, two development save a lot of things.

Before the Scriptella starts, we can specify data table by query parser, through querying the table, get the data field in the table, field type information, and then set the field corresponding data account in the system interface, the setup process can choose the old version of the configuration map to modify The new mapping template is produced after saving.

After all the template settings are complete, the departure script generates an event that generates an XML script for Scriptella execution.

The XML instance is as follows:

<! doctype etl system  "Http://scriptella.javaforge.com/dtd/etl.dtd" ><etl>         <connection id= "DB1"  url= "Jdbc:oracle:thin: @uri_1:1521:orcl"  user= "username"  password= "password"  classpath= "External.jar"/>         <connection id= "DB2"  url= "Jdbc:oracle:thin: @uri_2:1521:pcis"  user= " Username " password=" password "/>        <query  Connection-id= "DB1" >            <!--  select product from software category in db1-->             SELECT * FROM web_org_oper;             <!-- for each row execute a  script -->             <script connection-id= "DB2" >                           <!--  You can also create tables here  -->              <!--               CREATE TABLE Track  (                ID INT,                ALBUM_ID INT,                name varchar (,        )        DATA LONGVARBINARY              );              -->                              <!-- Insert all selected products  to db2                      use ? to reference properties, columns or ? {expressions}-->                 insert into web_org_oper (C_oper_id, c_oper_cnm, c_passwd,c_is_valid,t_pwd_strt_tm,t_ Pwd_end_tm,c_src,c_rel_cde,c_dpt_perm,c_dpt_diff,c_prd_diff,c_op_diff,c_crt_cde,t_crt_tm,c_upd_cde,t_upd_tm,c_ Own_dpt_cde,c_css_style,c_edu_cde,c_status,c_lmt_flag,c_trans_mrk,t_trans_tm,c_id_no,c_oper_cnm_bak)                    values  (? c_oper_id, ? c_oper_cnm, ? C_PASSWD, huh? C_is_valid, huh? T_pwd_strt_tm, huh? T_pwd_end_tm, huh? C_src, huh? C_rel_cde, huh? C_dpt_perm, huh? C_dpt_diff, huh? C_prd_diff, huh? C_op_diff, huh? C_crt_cde, huh? T_crt_tm, huh? C_upd_cde, huh? T_upd_tm, huh? C_own_dpt_cde, huh? C_css_style, huh? C_edu_cde, huh? C_status, huh? C_lmt_flag, huh? C_trans_mrk, huh? T_trans_tm, huh? C_id_no, huh? C_oper_cnm_bak);            </script>         </query></etl>

Java code calls (relies on Odbc5.jar, Scriptella.jar)---Write only one, many ways to invoke it:
Etlexecutor.newexecutor (Test.class.getResource ("Etl.xml")). Execute (), 4, Report account check and log records.

--Work now, write tomorrow!

5. Output Settings

--Work now, write tomorrow!

Report implementation of extracting data based on Scriptella