scriptella.github.io/tutorial.html at master · scriptella/scriptella.github.io · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Tutorial — Scriptella</title>
  <meta name="description" content="Two-minute tutorial for Scriptella ETL.">
  <link rel="canonical" href="https://scriptella.org/tutorial.html">
  <meta property="og:title" content="Tutorial — Scriptella">
  <meta property="og:description" content="Two-minute tutorial for Scriptella ETL.">
  <meta property="og:type" content="website">
  <meta property="og:url" content="https://scriptella.org/tutorial.html">
  <meta property="og:image" content="https://scriptella.org/images/scriptella-logo.svg">
  <meta name="twitter:card" content="summary_large_image">
  <meta name="twitter:title" content="Tutorial — Scriptella">
  <meta name="twitter:description" content="Two-minute tutorial for Scriptella ETL.">
  <link rel="icon" href="favicon.ico" sizes="32x32">
  <link rel="icon" type="image/png" href="favicon-192.png" sizes="192x192">
  <script src="theme.js"></script>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <a class="skip-link" href="#main-content">Skip to content</a>
  <header class="site-header">
    <nav class="primary-nav" aria-label="Primary navigation">
      <div class="site-header__inner">
        <a class="site-logo" href="index.html" aria-label="Scriptella home">
          <img class="site-logo__dark" src="images/scriptella-logo.svg" width="190" height="38" alt="Scriptella">
          <img class="site-logo__light" src="images/scriptella-logo-light.svg" width="190" height="38" alt="Scriptella">
        </a>
        <ul>
          <li><a href="reference/index.html">Documentation</a></li>
          <li><a href="tutorial.html">Tutorial</a></li>
          <li><a href="reference/drivers.html">Drivers</a></li>
          <li><a href="https://github.com/scriptella/scriptella-etl">GitHub</a></li>
          <li><a href="download.html">Download</a></li>
        </ul>
        <button class="theme-toggle" type="button" data-theme-toggle data-theme-current="system" aria-label="Color theme: System. Switch to Light." title="System theme — switch to Light">
          <span class="visually-hidden">Change color theme</span>
        </button>
      </div>
    </nav>
  </header>

  <main id="main-content" class="page-shell page-shell--reading">
    <article class="page-content">
      <h1>Two Minute Tutorial</h1>

      <nav aria-label="Table of contents">
        <h2>Contents</h2>
        <ul>
          <li><a href="#Installation">Installation</a></li>
          <li><a href="#Use+Scriptella+as+Ant+Task">Use Scriptella as Ant Task</a></li>
          <li><a href="#Command-Line+Execution">Command-Line Execution</a></li>
          <li><a href="#Executing+ETL+Files+from+Java">Executing ETL Files from Java</a></li>
          <li><a href="#Examples">Examples</a>
            <ul>
              <li><a href="#Copy+table+to+another+database">Copy table to another database</a></li>
              <li><a href="#Working+with+BLOBs">Working with BLOBs</a></li>
              <li><a href="#Supporting+several+SQL+dialects">Supporting several SQL dialects</a></li>
            </ul>
          </li>
        </ul>
      </nav>

      <h2 id="Installation">Installation</h2>
      <ul>
        <li><a href="download.html">Download</a> the Scriptella binary distribution or <a href="https://github.com/scriptella/scriptella-etl#build-from-source">build from source</a>.</li>
        <li>Unpack it and add <code>&lt;SCRIPTELLA_DIR&gt;/bin</code> to a system <code>PATH</code> variable.
          <p>Use <code>set PATH=%PATH%;SCRIPTELLA_DIR\bin</code> for Windows and <code>export PATH=${PATH}:SCRIPTELLA_DIR/bin</code> for Unix.</p>
        </li>
        <li>Confirm a Java 8 JRE is available by running <code>java -version</code>. Scriptella 1.3 targets Java 8 bytecode.</li>
        <li><em>Optional step:</em> Put JDBC drivers required by your scripts in the <code>&lt;SCRIPTELLA_DIR&gt;/lib</code> directory, or set the <code>classpath</code> attribute on connection elements.</li>
      </ul>

      <h2 id="Use+Scriptella+as+Ant+Task">Use Scriptella as Ant Task</h2>
      <p>In order to use Scriptella as Ant task you will need the following taskdef declaration:</p>
      <pre><code class="language-xml">&lt;taskdef resource="antscriptella.properties" classpath="/path/to/scriptella.jar[;additional_drivers.jar]"/&gt;</code></pre>
      <aside class="note"><strong>Note:</strong> Additional drivers classpath entries are optional. You may directly specify classpath attribute in a connection XML element declaration. Example: <code>&lt;connection driver="sybase" classpath="jconn3.jar"/&gt;</code></aside>
      <p>Running Scriptella files from Ant is simple:</p>
      <pre><code class="language-xml">&lt;etl/&gt; &lt;!-- Execute etl.xml file in the current directory --&gt;</code></pre>
      <p>or</p>
      <pre><code class="language-xml">&lt;etl file="path/to/your/file"/&gt; &lt;!-- Execute ETL file from specified location --&gt;</code></pre>

      <h2 id="Command-Line+Execution">Command-Line Execution</h2>
      <p>Just type <code>scriptella</code> to run the file named <code>etl.xml</code> in the current directory. Alternatively you can use java launcher:</p>
      <pre><code>java -jar scriptella.jar [arguments]</code></pre>

      <h2 id="Executing+ETL+Files+from+Java">Executing ETL Files from Java</h2>
      <p>It is extremely easy to run Scriptella ETL files from java code. Just make sure scriptella.jar is on classpath and use any of the following methods to execute an ETL file:</p>
      <pre><code class="language-java">EtlExecutor.newExecutor(new File("etl.xml")).execute();
EtlExecutor.newExecutor(getClass().getResource("etl.xml")).execute();
EtlExecutor.newExecutor(
    servletContext.getResource("/WEB-INF/db/init.etl.xml")).execute();</code></pre>
      <p>See <a href="docs/api/scriptella/execution/EtlExecutor.html">EtlExecutor Javadoc</a> for more details on how to execute ETL files from Java code.</p>

      <h2 id="Examples">Examples</h2>
      <p>For a quick start type <code>scriptella -t</code> to create a template <code>etl.xml</code> file.</p>

      <h3 id="Copy+table+to+another+database">Copy table to another database</h3>
      <p><img src="images/table_copy.png" alt="Migration of table records"></p>
      <p>Assume Database #1 contains Table Product with id, category and name columns. The following script copies software products from this table to Database #2. Additionally Name column is changed to Product_Name.</p>
      <p><strong>etl.xml:</strong></p>
      <pre><code class="language-xml">&lt;etl&gt;
    &lt;connection id="db1" url="jdbc:database1:sample" user="sa" password="" classpath="external.jar"/&gt;
    &lt;connection id="db2" url="jdbc:database2:sample" user="sa" password=""/&gt;
    &lt;query connection-id="db1"&gt;
        SELECT * FROM Product WHERE category='software';
        &lt;script connection-id="db2"&gt;
            INSERT INTO Product(id, category, product_name) values (?id, ?{category}, ?name);
        &lt;/script&gt;
    &lt;/query&gt;
&lt;/etl&gt;</code></pre>

      <h3 id="Working+with+BLOBs">Working with BLOBs</h3>
      <p><img src="images/blob_insert.png" alt="Inserting BLOB content from URL"></p>
      <p>The following sample initializes table of music tracks. Each track has a DATA field containing a file loaded from an external location. File song1.mp3 is stored in the same directory as etl.xml and song2.mp3 is loaded through the web.</p>
      <p><strong>etl.xml:</strong></p>
      <pre><code class="language-xml">&lt;etl&gt;
    &lt;connection url="jdbc:hsqldb:file:tracks" user="sa" password=""/&gt;
    &lt;script&gt;
        CREATE TABLE Track (
          ID INT,
          ALBUM_ID INT,
          NAME VARCHAR(100),
          DATA LONGVARBINARY
        );
        INSERT INTO Track(id, album_id, name, data) values
               (1, 1, 'Song1.mp3', ?{file 'song1.mp3'});
        INSERT INTO Track(id, album_id, name, data) values
               (2, 2, 'Song2.mp3', ?{file 'http://musicstoresample.com/song2.mp3'});
    &lt;/script&gt;
&lt;/etl&gt;</code></pre>

      <h3 id="Supporting+several+SQL+dialects">Supporting several SQL dialects</h3>
      <p><code>&lt;dialect&gt;</code> element allows including vendor specific content. The following example creates database schema for Oracle/HSQLDB or MySql depending on a selected driver:</p>
      <pre><code class="language-xml">&lt;etl&gt;
    &lt;properties&gt;
        &lt;include href="etl.properties"/&gt;
    &lt;/properties&gt;
    &lt;connection url="$url" user="$user"
        password="$password" classpath="$classpath"/&gt;
    &lt;script&gt;
        &lt;dialect name="hsql"&gt;
            &lt;include href="hsqldb-schema.sql"/&gt;
        &lt;/dialect&gt;
        &lt;dialect name="oracle"&gt;
            &lt;include href="oracle-schema.sql"/&gt;
        &lt;/dialect&gt;
        &lt;dialect name="mysql"&gt;
            &lt;include href="mysql-schema.sql"/&gt;
        &lt;/dialect&gt;
        INSERT INTO Product(id, category, product_name)
            VALUES (1, 'ETL', 'Scriptella ETL');
        INSERT INTO Product(id, category, product_name)
            VALUES (2, 'Development', 'Java SE 6');
     &lt;/script&gt;
&lt;/etl&gt;</code></pre>
      <aside class="note"><strong>Note:</strong> See <a href="https://github.com/scriptella/scriptella-examples">Scriptella examples on GitHub</a> or <a href="download.html">download</a> Scriptella examples distribution.</aside>
    </article>
  </main>

  <footer class="site-footer">
    <div class="site-footer__inner">
      <p><strong>Scriptella</strong> · Licensed under the Apache License, Version 2.0.</p>
      <p class="site-footer__credit">Built by <a class="pvrlabs-wordmark" href="https://pvrlabs.xyz" aria-label="PVR Labs"><span class="pvrlabs-wordmark__accent">PVR</span><span class="pvrlabs-wordmark__light">Labs</span></a></p>
      <nav aria-label="Footer navigation">
        <ul>
          <li><a href="reference/index.html">Documentation</a></li>
          <li><a href="faq.html">FAQ</a></li>
          <li><a href="docs/api/index.html">API Docs</a></li>
          <li><a href="docs/dtd/index.html">DTD Reference</a></li>
          <li><a href="https://github.com/scriptella/scriptella-etl">GitHub</a></li>
          <li><a href="changes.html">Change History</a></li>
          <li><a href="license.html">License</a></li>
          <li><a href="support.html">Support</a></li>
        </ul>
      </nav>
    </div>
  </footer>
  <!-- StatCounter: existing Scriptella project configuration -->
  <script>
    var sc_project = 10775960;
    var sc_invisible = 1;
    var sc_security = "53eaed1c";
  </script>
  <script defer src="https://secure.statcounter.com/counter/counter.js"></script>
  <!-- End StatCounter -->
</body>
</html>