scriptella.github.io/faq.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
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>FAQ — Scriptella</title>
  <meta name="description" content="Frequently asked questions about Scriptella ETL.">
  <link rel="canonical" href="https://scriptella.org/faq.html">
  <meta property="og:title" content="FAQ — Scriptella">
  <meta property="og:description" content="Frequently asked questions about Scriptella ETL.">
  <meta property="og:type" content="website">
  <meta property="og:url" content="https://scriptella.org/faq.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="FAQ — Scriptella">
  <meta name="twitter:description" content="Frequently asked questions about 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>Scriptella ETL Frequently Asked Questions</h1>

      <nav aria-label="Table of contents">
        <h2>Contents</h2>
        <ul>
          <li><a href="#general">1. General</a>
            <ul>
              <li><a href="#DRIVERS">1.1. Where can I find documentation on script syntax for driver FOO/BAR/...?</a></li>
              <li><a href="#ODBC">1.2. Does Scriptella support Microsoft Access?</a></li>
              <li><a href="#BIND_VARIABLES">1.3. What is the difference between $variable / ${expression} and ?variable / ?{expression} syntax?</a></li>
              <li><a href="#SET_VARIABLE">1.4. How to share a mutable variable between script/query elements of an ETL file?</a></li>
              <li><a href="#EXECUTE_STATIC_METHOD">1.5. How to call a static method in JEXL expression?</a></li>
              <li><a href="#COLUMN_SPACES">1.6. How to escape spaces in field names?</a></li>
            </ul>
          </li>
          <li><a href="#sql">2. SQL</a>
            <ul>
              <li><a href="#PLSQL">2.1. How to create an Oracle trigger (run a PL/SQL block)?</a></li>
              <li><a href="#COMMIT">2.2. How to control commit options and transactions?</a></li>
              <li><a href="#UPSERT">2.3. How to UPSERT (update or insert into a table)?</a></li>
            </ul>
          </li>
          <li><a href="#csvtext">3. CSV/Text</a>
            <ul>
              <li><a href="#NULLS">3.1. How to output null value as empty string in a CSV/Text file.</a></li>
            </ul>
          </li>
        </ul>
      </nav>

      <h2 id="general">1. General</h2>

      <h3 id="DRIVERS">1.1. Where can I find documentation on script syntax for driver FOO/BAR/...?</h3>
      <p>An up to date drivers documentation is available in the <a href="docs/api/">Scriptella Javadoc</a>. <a href="reference/index.html">Reference Documentation</a> also covers drivers usage and scripts syntax.</p>

      <h3 id="ODBC">1.2. Does Scriptella support Microsoft Access?</h3>
      <p>Microsoft Access and other databases with ODBC interface are supported via ODBC-JDBC bridge driver which comes with Sun's JRE. Download <a href="download.html">ODBC example</a> for more details. Connection declaration example:</p>
      <pre><code class="language-xml">&lt;connection url="jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=Northwind.mdb"/&gt;</code></pre>

      <h3 id="BIND_VARIABLES">1.3. What is the difference between $variable / ${expression} and ?variable / ?{expression} syntax.</h3>
      <p>Binding variables syntax varies between drivers. JDBC drivers use the following rules for properties substitution:</p>
      <ul>
        <li><code>$variable</code> - inserts a value of the variable as text content.</li>
        <li><code>${expression}</code> - braces are used for JEXL expressions. For example <code>${column1+column2}</code> inserts a sum of 2 columns.</li>
        <li><code>?variable</code>, <code>?{expression}</code> - syntax is the same as in 2 previous examples, but the result of evaluation is set as a prepared statement parameter, thus increasing the performance and eliminating the need to escape values.</li>
      </ul>
      <aside class="note"><strong>Note:</strong> Currently <code>?{}</code> syntax is only supported by the JDBC drivers.</aside>
      <p>See <a href="reference/index.html#BIND_VARIABLES">Reference Manual</a> for additional details.</p>

      <h3 id="SET_VARIABLE">1.4. How to share a mutable variable between script/query elements of an ETL file?</h3>
      <p>In general such practice in not recommended because often it's a sign of a bad design. It's like using GOTOs or global variables in programming languages. But in several cases using a global variable may help to achieve required goal. We propose 2 approaches:</p>
      <ol>
        <li>Use <code>etl.globals</code> map to work with global variables. Example <code>${etl.globals['globalVar']}</code></li>
        <li>Another approach is similar to the technique utilized in anonymous inner classes to modify a single-element array declared as a final variable.</li>
      </ol>
      <p>The following example demonstrates both approaches:</p>
      <pre><code class="language-xml">&lt;etl&gt;
    &lt;connection driver="script" id="js"/&gt;
    &lt;connection url="jdbc:...." id="db"/&gt;

    &lt;!-- Set number of records as a global variable --&gt;
    &lt;!-- Note that JEXL syntax etl.globals['globalVar'] does not work in JavaScript --&gt;
    &lt;query connection-id="db"&gt;
        select count(id) as c from Errors
        &lt;script connection-id="js"&gt;
            etl.globals.put('errorsCount', c);
        &lt;script&gt;
    &lt;/query&gt;

    &lt;!-- Then reuse this variable in other parts of ETL file --&gt;
    &lt;script connection-id="js" if="etl.globals.get('errorsCount') gt 0"&gt;
        java.lang.System.out.println('errors count ='+etl.globals.get('errorsCount'));
    &lt;/script&gt;

    &lt;!-- Alternatively an outer query can be used to share an array based variable between scripts --&gt;
    &lt;query connection-id="js"&gt;
        var pseudoGlobalVar = []; //Declare pseudo-global variable available to nested element
        query.next(); //Executes child scripts
        &lt;script&gt; //Updates the variable
           pseudoGlobalVar[0] = 1;
        &lt;/script&gt;
        &lt;script&gt; //Outputs updated value of global variable
             java.lang.System.out.println('pseudoGlobalVar[0]='+pseudoGlobalVar[0]);
        &lt;/script&gt;
    &lt;/query&gt;
&lt;/etl&gt;</code></pre>

      <h3 id="EXECUTE_STATIC_METHOD">1.5. How to call a static method in JEXL expression?</h3>
      <p>Load a class by name by using <code>class:forName</code> Scriptella function.</p>
      <p>Example. Call <code>System.getProperty</code>:</p>
      <pre><code>${class:forName('java.lang.System').getProperty('propName')}</code></pre>

      <h3 id="COLUMN_SPACES">1.6. How to escape spaces in field names?</h3>
      <p>You can use <a href="docs/api/scriptella/core/EtlVariable.html"><code>etl context variable</code></a> <code>${etl.getParameter('var name')}</code> to reference any column names. Additionally several drivers including CSV and JDBC allow referencing columns by an index, i.e. <code>$1</code>, <code>$2</code> ... <code>$n</code>.</p>
      <p>Example:</p>
      <pre><code class="language-xml">&lt;query connection-id="csv"&gt; &lt;!-- Read CSV file content --&gt;
    &lt;script connection-id="text"&gt;
        &lt;!-- Print columns, column 2,3 are referenced by name, 1,4 - by index --&gt;
        $1,$secondColumn,${etl.getParameter('My Column')},$4
    &lt;/script&gt;
&lt;/query&gt;</code></pre>

      <h2 id="sql">2. SQL</h2>

      <h3 id="PLSQL">2.1. How to create an Oracle trigger (run a PL/SQL block)?</h3>
      <p>To recognize Oracle PL/SQL statement blocks you'd have to specify <code>plsql=true</code> connection property (supported only by <a href="reference/drivers.html#oracle">Scriptella Adapter for Oracle</a>). In this case a slash (/) on a single line is used as a statement separator:</p>
      <pre><code class="language-xml">&lt;connection driver="oracle" ...&gt;
    plsql=true
&lt;/connection&gt;
&lt;script&gt;
    CREATE OR REPLACE TRIGGER secure_del_trigger
       BEFORE DELETE
       ON emp
       FOR EACH ROW
    DECLARE
       unauthorized_deletion   EXCEPTION;
    BEGIN
       IF &lt;your business rule is violated&gt; THEN
         RAISE unauthorized_deletion;
       END IF;
    EXCEPTION
       WHEN unauthorized_deletion
       THEN
          raise_application_error (-20500,
            'This record cannot be deleted');
    END;
    /

    -- Other statements separated with a slash on a single line
&lt;/script&gt;</code></pre>
      <p>If you are using Oracle JDBC driver directly then set the following configuration properties:</p>
      <pre><code class="language-xml">&lt;connection driver="oracle.jdbc.driver.OracleDriver" ...&gt;
    statement.separator=/
    statement.separator.singleline=true
&lt;/connection&gt;</code></pre>
      <p>This idea is similar to <a href="http://www.javaddicts.net/blog/index.php/2005/06/13/executing-oracle-plsql-from-ant/">Ant solution</a>.</p>

      <h3 id="COMMIT">2.2. How to control commit options and transactions?</h3>
      <p>The following connection parameters are supported by the JDBC bridge:</p>
      <ul>
        <li><code>transaction.isolation</code> - Transaction isolation level name</li>
        <li><code>autocommit</code> - Enables/disables auto-commit mode.</li>
        <li><code>autocommit.size</code> - Enables/disables auto-commit mode.</li>
      </ul>
      <aside class="note"><strong>Note:</strong> In general avoid using <code>autocommit.size</code>, because in this case an ETL process cannot be rolled back correctly. Use this parameter only for performance critical operations (bulk inserts etc.). If the <code>autocommit</code> is true, then setting <code>autocommit.size</code> has no effect.</aside>
      <p><strong>Example:</strong></p>
      <pre><code class="language-xml">&lt;connection driver="auto" url="jdbc:hsqldb:mem:test"&gt;
    autocommit.size=4 &lt;!-- Automatically commit after every 4th statement --&gt;
    transaction.isolation=SERIALIZABLE &lt;!-- Sets TX level to Serializable --&gt;
&lt;/connection&gt;</code></pre>

      <h3 id="UPSERT">2.3. How to UPSERT (update or insert into a table)?</h3>
      <p>You can leverage database-specific SQL statements like MERGE or UPSERT. See answers on StackOverflow:</p>
      <ul>
        <li><a href="http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table">Oracle</a></li>
        <li><a href="http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql">MySQL</a></li>
        <li><a href="http://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server">MS SQL Server</a></li>
      </ul>
      <p>An alternative approach, which is universal but is typically slower, is to use count(*) statement and do insert or update accordingly:</p>
      <pre><code class="language-xml">&lt;query connection-id="in"&gt;
    SELECT * FROM Persons_In
    &lt;query connection-id="out"&gt;
       SELECT COUNT(*) as cnt FROM Person_Out WHERE Person_ID=?Person_ID
       &lt;!-- If nothing found - insert --&gt;
       &lt;script if="cnt==0"&gt;
          INSERT INTO Person_OUT VALUES (Person_ID, Person_Name, ...);
       &lt;/script&gt;
       &lt;!-- Otherwise - update --&gt;
       &lt;script if="cnt gt 0"&gt;
          UPDATE Person_OUT SET Person_Name=?Person_Name WHERE Person_ID = ?Person_ID;
       &lt;/script&gt;
    &lt;/query&gt;
&lt;/query&gt;</code></pre>

      <h2 id="csvtext">3. CSV/Text</h2>

      <h3 id="NULLS">3.1. How to output null value as empty string in a CSV/Text file.</h3>
      <p>Suppose we have 3 variables - a='valueA'; b=null and c='valueC'.</p>
      <p>Here is a <a href="docs/api/scriptella/driver/csv/package-summary.html#package.description">CSV</a> script which outputs them in a single line:</p>
      <pre><code>$a,$b,$c</code></pre>
      <p>The output for this script:</p>
      <pre><code>valueA,<strong>$b</strong>,valueC</code></pre>
      <p>By default NULL variables are not substituted, because Scriptella substitution engine cannot distinguish null value from undeclared variable. This behaviour is not always desired. As a workaround you can specify a null_string connection property as follows:</p>
      <pre><code class="language-xml">&lt;connection driver="text"&gt;
    null_string=&lt;!-- Expand nulls to empty string --&gt;
&lt;/connection&gt;</code></pre>
      <p>And the output is:</p>
      <pre><code>valueA,,valueC</code></pre>
    </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>