MediaWiki API Result

You are looking at the HTML representation of the XML format.
HTML is good for debugging, but is unsuitable for application use.
Specify the format parameter to change the output format.
To see the non HTML representation of the XML format, set format=xml.
See the complete documentation, or API help for more information.
<?xml version="1.0"?>
<api>
  <query-continue>
    <allpages gapcontinue="Using_Eclipse" />
  </query-continue>
  <query>
    <pages>
      <page pageid="22" ns="0" title="Redistribution and managing dependencies">
        <revisions>
          <rev contentformat="text/x-wiki" contentmodel="wikitext" xml:space="preserve">=== Redistribution list ===

__FORCETOC__ 

{| class=&quot;wikitable&quot;
|-
! Folder !! File !! Required !! Needed for || Depends on
|-
| '''home''' || || '''Yes''' || Root folder || 

|-
| || etl.exe || No || Windows executable  ||
|-
| || etl.app || No || Mac application bundle || 
|-
| || etl.sh || No || Unix/Linux shell script ||
|-
| || etlappstart.properties || No || JVM properties ||
|-
| || etl.jar || '''Yes''' || Main executable jar ||

|-
| '''home\config''' || || No || Default configuration folder ||

|-
| || etl.properties || No || Configuration properties || 
|-
| || etl_config.xml || No || Default [[Configuration_file|ETL configuration file]]  ||
|-
| || log4j.properties || No || Log configuration  ||

|-
| '''home\license''' || || No || Folder for licenses ||
|-
| || *.lic || No || Licenses (ETL framework will also look at license.jar under home\lib folder) || 


|-
| '''home\data''' || || No || Default folder for data ||

|-
| '''home\data\scenario''' || || No || Default folder for ETL scenarios || 
|-
| '''home\data\schema''' || || No || Default folder for XLT schema files || 
|-
| || dataset2webrowset.xsl || No || XSL schema file for Toolseverse XML dataset to [http://docs.oracle.com/javase/7/docs/api/javax/sql/rowset/WebRowSet.html WebRowSet] XML transformation ||  
|-
| || webrowset2dataset.xsl || No || XSL schema file for [http://docs.oracle.com/javase/7/docs/api/javax/sql/rowset/WebRowSet.html WebRowSet] XML to Toolseverse XML dataset transformation ||  
|-
| || xmldataset.xsd || No || Schema file for Toolsverse XML dataset file format ||  

|-
| '''home\lib''' || || No || Folder for core and third party jar files. Files from this folder automatically added to the classpath ||

|-
| || antlr-3.0.1.jar || '''Yes'''|| Core third party file || 

|-
| || antlr.jar || '''Yes''' || Core third party file || 

|-
| || commons-beanutils-1.9.1.jar || '''Yes''' || Core third party file || 

|-
| || commons-codec-1.6.jar || '''Yes''' || Core third party file || 

|-
| || commons-collections4-4.0.jar || '''Yes''' || Core third party file || 

|-
| || commons-dbcp-1.4.jar || '''Yes''' || Core third party file || 

|-
| || commons-logging-1.1.3.jar  || '''Yes''' || Core third party file || 

|-
| || commons-math3-3.0.jar || '''Yes''' || Core third party file || 

|-
| || commons-net-2.0.jar || '''Yes''' || Core third party file ||

|-
| || commons-pool-1.5.4.jar || '''Yes''' || Core third party file ||

|-
| || dom4j-1.6.1.jar || '''Yes''' || Core third party file ||

|-
| || httpclient-4.3.3.jar  || '''Yes''' || Core third party file ||

|-
| || httpclient-cache-4.3.3.jar  || '''Yes''' || Core third party file ||

|-
| || httpcore-4.3.2.jar  || '''Yes''' || Core third party file ||

|-
| || httpmime-4.3.3.jar || '''Yes''' || Core third party file ||

|-
| || javassist.jar || '''Yes''' || Core third party file ||

|-
| || json-io.jar || '''Yes''' || Core third party file ||

|-
| || log4j-1.2.16.jar || '''Yes''' || Core third party file ||

|-
| || rsyntaxtextarea.jar || '''Yes''' || Core third party file ||

|-
| || RSyntaxTextArea.License.txt || '''Yes''' || Core third party file ||

|-
| || saxon9he.jar || '''Yes''' || Core third party file ||

|-
| || toolsverse-core.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-common.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-connector-sql.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-connector.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-core.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-driver.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-etl-metadata.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-io.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-license.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-mvc.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-security.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-service.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-storage.jar || '''Yes''' || Toolsverse core file ||

|-
| || toolsverse-updater.jar || '''Yes''' || Toolsverse core file ||


|-
| || derby.jar || No || Derby DB JDBC driver ||

|-
| || iText-4.2.0-com.itextpdf.jar || No || Read and write PDF files ||

|-
| || javax.json-1.0.4.jar || No || Read and write JSON files ||

|-
| || javax.mail.jar  || No || Send emails ||

|-
| || jsch-0.1.43.jar  || No || SFTP client ||

|-
| || poi-3.8-20120326.jar || No || Read and write Excel files ||

|-
| || poi-ooxml-3.8-20120326.jar || No || Read and write Excel files ||

|-
| || poi-ooxml-schemas-3.8-20120326.jar || No || Read and write Excel files ||

|-
| || servlet-api.jar || No || Required if you are going to call Web services  ||

|-
| || socks.jar || No || Socks proxy used together with FTP  ||

|-
| '''home\plugin''' || || No || Folder for plugins. In other to use files in this folder you must have a valid not expired license ||

|-
| || toolsverse-etl-connector-excel.jar || No || Required if you are going to read and write Excel files || toolsverse-etl-connector-json.jar

|-
| || toolsverse-etl-connector-html.jar || No || Required if you are going to read and write HTML files || 

|-
| || toolsverse-etl-connector-json.jar || No || Required if you are going to read and write JSON files || 

|-
| || toolsverse-etl-connector-pdf.jar || No || Required if you are going to read and write PDF files || 

|-
| || toolsverse-etl-connector-text.jar || No || Required if you are going to read and write CVS and fixed-length files || toolsverse-etl-connector-json.jar, toolsverse-etl-connector-xml.jar

|-
| || toolsverse-etl-connector-xml.jar || No || Required if you are going to read and write XML files || toolsverse-etl-connector-json.jar

|-
| || toolsverse-etl-db2.jar || No || Required if you are going to work with DB2 databases || 

|-
| || toolsverse-etl-informix.jar || No || Required if you are going to work with Informix databases || 

|-
| || toolsverse-etl-oracle.jar || No || Required if you are going to work with Oracle databases || 

|-
| || toolsverse-etl-mysql.jar || No || Required if you are going to work with MySQL databases || 

|-
| || toolsverse-etl-postgres.jar || No || Required if you are going to work with Postgres databases || 

|-
| || toolsverse-etl-sybase.jar || No || Required if you are going to work with Sybase ASE databases || 

|-
| '''home\jdbc''' || || No || Default folder for [[Installing_and_Configuring_ETL_Framework|JDBC drivers]] ||


|}

=== Example ===

&lt;pre&gt;
home
   etl.exe
   etl.app
   etl.jar
   etl.sh
   etlappstart.properties 
   config
      etl.properties
      etl_config.xml
      log4j.properties
   license
      *.lic
   data
      scenario
      schema
         dataset2webrowset.xsl
         webrowset2dataset.xsl
         xmldataset.xsd
   lib	
      antlr-3.0.1.jar                   
      antlr.jar                         
      commons-beanutils-1.9.1.jar          
      commons-codec-1.6.jar                
      commons-collections4-4.0.jar         
      commons-dbcp-1.4.jar                 
      commons-logging-1.1.3.jar            
      commons-math3-3.0.jar                
      commons-net-2.0.jar                  	
      commons-pool-1.5.4.jar            
      derby.jar                         
      dom4j-1.6.1.jar                   
      httpclient-4.3.3.jar              
      httpclient-cache-4.3.3.jar        
      httpcore-4.3.2.jar                
      httpmime-4.3.3.jar                
      iText-4.2.0-com.itextpdf.jar      
      javassist.jar                     
      javax.json-1.0.4.jar              
      javax.mail.jar                    
      jsch-0.1.43.jar                   
      json-io.jar                       
      log4j-1.2.16.jar                  
      poi-3.8-20120326.jar              
      poi-ooxml-3.8-20120326.jar        
      poi-ooxml-schemas-3.8-20120326.jar
      rsyntaxtextarea.jar               
      RSyntaxTextArea.License.txt       
      saxon9he.jar                      
      servlet-api.jar                   
      socks.jar                         
      toolsverse-core.jar               
      toolsverse-etl-aliasreader.jar    
      toolsverse-etl-common.jar         
      toolsverse-etl-connector-sql.jar  
      toolsverse-etl-connector.jar      
      toolsverse-etl-core.jar           
      toolsverse-etl-driver.jar         
      toolsverse-etl-metadata.jar       
      toolsverse-io.jar                 
      toolsverse-license.jar            
      toolsverse-mvc.jar                
      toolsverse-security.jar           
      toolsverse-service.jar            
      toolsverse-storage.jar            
      toolsverse-updater.jar            
      xmlbeans-2.3.0.jar                
   plugin
      toolsverse-etl-connector-excel.jar
      toolsverse-etl-connector-html.jar 
      toolsverse-etl-connector-json.jar 
      toolsverse-etl-connector-pdf.jar  
      toolsverse-etl-connector-text.jar 
      toolsverse-etl-connector-xml.jar  
      toolsverse-etl-db2.jar            
      toolsverse-etl-informix.jar       
      toolsverse-etl-mysql.jar          
      toolsverse-etl-oracle.jar         
      toolsverse-etl-postgres.jar       
      toolsverse-etl-sqlserver.jar
&lt;/pre&gt;

=== Managing dependencies, files and folders ===

Files and folders marked as '''required''' must exist in order for framework to work. Others are optional. 

Framework is tested with the particular versions of the third party libraries (see redistribution list). It is likely framework will work with more recent versions than with older versions.

Jars under '''lib''' and '''plugin''' folders are automatically added to the classpath on start-up if there is an available license. Only commercial components of the ETL framework require license. 

You can reduce number of folders ETL framework depends on:
* Move all files from the '''plugin''' folder to '''lib'''.
* Package existing licenses (*.lic files) in the '''license''' folder into jar file and copy jar into '''lib''' folder. Name of the jar file doesn't matter but lic files must be located in the '''com/toolsverse/license''' folder inside jar.
* Files in the '''config''' folder and '''config''' folder itself are not required. You can pass properties to the framework using -Dproperty_name=value. 

Framework uses '''HOME/data/''' folder for temporary files, scripts and data files. If folder does not exist it is automatically created.  

By default framework is looking for ETL scenarios in the '''HOME/data/scenario folder'''. If you want to change base folder for scenarios use -D property '''scenario.path'''.

Look at the src/com/toolsverse/config/SystemConfig.java how ETL frameworks uses system folders.

Look at the src/com/toolsverse/etl/core/config/EtlConfig.java how ETL frameworks uses ETL specific folders.</rev>
        </revisions>
      </page>
      <page pageid="24" ns="0" title="Running ETL Scenarios">
        <revisions>
          <rev contentformat="text/x-wiki" contentmodel="wikitext" xml:space="preserve">== Embedding ETL scenario into existing Java application ==

There are three easy steps:

# Instantiate EtlConfig
# Instantiate EtlProcess
# Execute scenario

Example:
&lt;syntaxhighlight lang=&quot;java&quot;&gt;
public static void main(String[] args)
{
        LoadScenarioAndConfigurationFromFile engine = new LoadScenarioAndConfigurationFromFile();
        
        try
        {
            // instantiates ETL configuration
            EtlConfig etlConfig = new EtlConfig();
            
            // set log level to INFO which increases verbosity of the etl engine
            Logger.setLevel(EtlLogger.class, Logger.INFO);
            
            // creates embedded ETL process
            EtlProcess etlProcess = new EtlProcess(EtlProcess.EtlMode.EMBEDDED);
            
            // print out framework version
            System.out.println(SystemConfig.instance().getTitle(
                    EtlConfig.DEFAULT_TITLE)
                    + &quot; &quot;
                    + SystemConfig.instance().getSystemProperty(
                            SystemConfig.VERSION));
            
            // load configuration which contains source and destination
            // connections and ETL scenario name. Load and execute ETL scenario.
            // If no full path
            // provided the configuration file test_etl_config.xml is expected
            // to be under app_home/config
            EtlResponse response = engine.loadConfigAndExecute(etlConfig,
                    &quot;test_etl_config.xml&quot;, etlProcess);
            
            // print out formatted output from the ETL response
            System.out.println(engine.getMessage(response,
                    &quot;Exampes/Engine/db2file.xml&quot;));
            
        }
        catch (Exception ex)
        {
            System.out.println(Utils.getStackTraceAsString(ex));
        }
        
        System.exit(0);
}
&lt;/syntaxhighlight&gt;

Look at other [http://www.toolsverse.com/products/etl-framework/java-examples/ examples of embedding ETL engine] into Java application.

== Using Web service to run ETL scenario ==

To run ETL scenario using Web service you need to have ETL server installed, configured, up and running. Please read [http://www.toolsverse.com/products/data-explorer/docs/doc.html#_Installing_Server here] how to install and configure ETL server.

You will also need to specify -Dnetwork.appserverurl JVM option. 

Example:

&lt;pre&gt;-Dnetwork.appserverurl=http://host:port/dataexplorer/ide&lt;/pre&gt;  

You can use ETL_FRAMEWORK_HOME/config/etl.properties to set network.appserverurl or set it programmatically:

&lt;pre&gt;
network.appserverurl=http://localhost:8080/dataexplorer/ide
network.appupdateurl=http://www.toolsverse.com/api/services/CheckForUpdates
app.update.key=etl
app.name=etlprocess
app.title=ETL Framework
&lt;/pre&gt;  
 
 
Once server is configured using Web service is as easy as:

# Instantiating EtlConfig and adding all required connection information
# Instantiating instance and initializing EtlRequest
# Instantiating instance of the EtlService interface using ServiceFactory and appropriate dynamic proxy
# Calling EtlService#executeEtl  
 
Example:
&lt;syntaxhighlight lang=&quot;java&quot;&gt;

/**
 * Creates connection aliases for ETL process, sets scenario name, remotely executes ETL scenario.
 * 
 * @return ETL response
 * @throws Exception in case of any error
 */
private EtlResponse execute()
        throws Exception
{
        // initializes system config, loads properties
        SystemConfig.instance();
        
        // instantiates ETL config
        EtlConfig config = new EtlConfig();
        
        // initializes ETl config
        config.init();
        
        // creates source alias
        Alias source = new Alias();
        source.setName(&quot;Java DB&quot;);
        source.setUrl(&quot;jdbc:derby:{app.root.data}/demo/javadb&quot;);
        source.setJdbcDriverClass(&quot;org.apache.derby.jdbc.EmbeddedDriver&quot;);
        
        // creates destination alias
        Alias destination = new Alias();
        destination.setName(&quot;JSON files&quot;);
        destination
                .setConnectorClassName(&quot;com.toolsverse.etl.connector.json.JsonConnector&quot;);
        destination.setUrl(&quot;{app.root.data}/*.json&quot;);
        
        // adds aliases. ETL process will create connections from these aliases
        config.addAliasToMap(EtlConfig.SOURCE_CONNECTION_NAME, source);
        config.addAliasToMap(EtlConfig.DEST_CONNECTION_NAME, destination);
        
        // creates empty ETL scenario, sets ETL action. The remote ETL process
        // will load it from file at the run-time.
        Scenario scenario = new Scenario();
        scenario.setName(&quot;Examples/Engine/db2file.xml&quot;);
        scenario.setAction(EtlConfig.EXTRACT_LOAD);
        
        // creates ETL request using given config, scenario and log level
        EtlRequest request = new EtlRequest(config, scenario, Logger.INFO);
        
        if (Utils.isNothing(SystemConfig.instance().getSystemProperty(
                SystemConfig.SERVER_URL)))
            SystemConfig.instance().setSystemProperty(SystemConfig.SERVER_URL,
                    &quot;http://localhost:8080/dataexplorer/ide&quot;);
        
        // gets ETL service from the factory. The ServiceProxyWeb used as a
        // dynamic proxy
        EtlService etlService = ServiceFactory.getService(EtlService.class,
                ServiceProxyWeb.class.getName());
        
        // remotely executes ETL process
        return etlService.executeEtl(request);
}
&lt;/syntaxhighlight&gt;

== Running ETL scenario using standalone executable ==

# Open APP_HOME/config/etl_config.xml file in your favorite text editor.
# Add connections for the particular ETL scenario
# Specify connections to use and scenarios to run
# Save
# Run ETL executable. For example c:\etl\etl.exe on Windows
# When it is finished check the etl.log file located under APP_HOME/logs

'''Example of the etl_config.xml:'''

&lt;syntaxhighlight lang=&quot;xml&quot;&gt;
&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;
&lt;config&gt;
   &lt;connections&gt;
      &lt;connection alias=&quot;test javadb&quot;&gt;
         &lt;driver&gt;org.apache.derby.jdbc.EmbeddedDriver&lt;/driver&gt;
         &lt;url&gt;jdbc:derby:{app.root.data}/demo/javadb&lt;/url&gt;
      &lt;/connection&gt;
      &lt;connection alias=&quot;test oracle&quot;&gt;
         &lt;driver&gt;oracle.jdbc.driver.OracleDriver &lt;/driver&gt;
         &lt;url&gt;jdbc:oracle:thin:@localhost:1521:orcl1&lt;/url&gt;
         &lt;userid&gt;user&lt;/userid&gt;
         &lt;password&gt;password&lt;/password&gt;   
         &lt;params/&gt;
      &lt;/connection&gt;
   &lt;/connections&gt;
   
   &lt;active.connections&gt;
      &lt;sourses&gt;
         &lt;source alias=&quot;test javadb&quot; /&gt;
      &lt;/sourses&gt;
      &lt;destination alias=&quot;test oracle&quot;/&gt; 
   &lt;/active.connections&gt;
   &lt;execute&gt;
       &lt;scenario name=&quot;move.xml&quot; action=&quot;extract_load&quot; /&gt;
   &lt;/execute&gt;
&lt;/config&gt;
&lt;/syntaxhighlight&gt;

In this example move.xml ETL scenario located under the {app.data}/scenario folder will be executed using extract_load action. Alias test javadb will be used for the source connection and alias test oracle for the destination.

Read more about [[Configuration_file|ETL Configuration File]].

You can pass properties file name (or any other -D property) as a command line argument:

&lt;pre&gt;etl.exe -Dconfig.file.name=import_data.properties&lt;/pre&gt;

You can also set the name of the XML configuration file (connections):

&lt;pre&gt;etl.exe -Detl.config.name=import_data_config.xml&lt;/pre&gt;

Last but not least you can use properties file to define XML configuration file (connections):

&lt;pre&gt;
# don't modify
network.appupdateurl=http://www.toolsverse.com/api/services/CheckForUpdates
app.update.key=etl
app.title=ETL Framework
etl.config.name=import_data_config.xml.xml
&lt;/pre&gt;

== Creating and running ETL scenario using Data Explorer ==

The best way to create, run and schedule ETL, data integration and data migration scenario is using [http://toolsverse.com/products/data-explorer/docs/doc.html#developetlscenarios Data Explorer] - an integrated ETL IDE.</rev>
        </revisions>
      </page>
    </pages>
  </query>
</api>