tutorial.html

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Scriptella ETL Tutorial: Java, SQL, CSV and Database Migration</title>
  <meta name="description" content="Learn Scriptella ETL step by step: generate SQL from CSV, understand row processing, and copy data from MySQL to PostgreSQL.">
  <link rel="canonical" href="https://scriptella.org/tutorial.html">
  <meta property="og:title" content="Scriptella ETL Tutorial: Java, SQL, CSV and Database Migration">
  <meta property="og:description" content="Learn Scriptella ETL step by step: generate SQL from CSV, understand row processing, and copy data from MySQL to PostgreSQL.">
  <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-og.png">
  <meta property="og:image:type" content="image/png">
  <meta property="og:image:alt" content="Scriptella">
  <meta property="og:image:width" content="1731">
  <meta property="og:image:height" content="909">
  <meta name="twitter:card" content="summary_large_image">
  <meta name="twitter:image" content="https://scriptella.org/images/scriptella-og.png">
  <meta name="twitter:title" content="Scriptella ETL Tutorial: Java, SQL, CSV and Database Migration">
  <meta name="twitter:description" content="Learn Scriptella ETL step by step: generate SQL from CSV, understand row processing, and copy data from MySQL to PostgreSQL.">
  <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?v=20261022">
</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" aria-current="page">Tutorial</a></li>
          <li><a href="installation.html">Install</a></li>
          <li><a href="download.html">Download</a></li>
          <li><a href="https://github.com/scriptella/scriptella-etl">GitHub</a></li>
        </ul>
        <div class="header-controls">
        <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>
      </div>
    </nav>
  </header>

  <main id="main-content" class="page-shell page-shell--reading">
    <article class="page-content">
      <h1>Scriptella ETL tutorial</h1>
      <p>With Scriptella, you write regular SQL to query and update databases through JDBC. XML provides a thin layer that declares
        connections and controls when queries and scripts run. Other drivers let you work with CSV, text, and scripting languages
        in the same ETL file.</p>
      <p>In this tutorial, you will run a job from the command line, see how it processes rows, and use the same pattern to copy data
        between databases.</p>
      <nav aria-label="Table of contents"><h2>On this page</h2><ol>
        <li><a href="#Installation">Install Scriptella</a></li>
        <li><a href="#first-job">Your first ETL job</a></li>
        <li><a href="#how-it-works">How Scriptella works</a></li>
        <li><a href="#Copy+table+to+another+database">Copy rows between databases</a></li>
        <li><a href="#next-steps">Where to go next</a></li>
      </ol></nav>
      <h2 id="Installation">1. Install Scriptella</h2>
      <p>You need Java 17 or newer. Check Java, then install Scriptella on macOS or Linux:</p>
<pre><code>java -version
curl -fsSL https://scriptella.org/install.sh | sh</code></pre>
      <p>Follow the installer's instructions to add Scriptella to your <code>PATH</code>. Open a new shell, then verify the installation:</p>
<pre><code>scriptella.sh --version</code></pre>
      <p>On Windows, follow the <a href="installation.html#windows-installation">Windows installation instructions</a> and use
        <code>scriptella.bat</code> for the commands below. See the <a href="installation.html#manual-installation">installation guide</a>
        for manual installation.</p>
      <h2 id="first-job">2. Your first ETL job</h2>
      <p>Generate a SQL file from a CSV file. This job uses the bundled CSV and text drivers, so you need no database or extra driver downloads.</p>
      <figure>
        <picture>
          <source media="(max-width: 600px)" srcset="images/tutorial-csv-mobile.svg" width="320" height="500">
          <img src="images/tutorial-csv.svg" width="760" height="220" alt="people.csv passes through a Scriptella ETL job to produce load.sql.">
        </picture>
        <figcaption>Two input rows become two INSERT statements.</figcaption>
      </figure>
      <p>Create a new working directory and put these two files in it.</p>
      <h3>people.csv</h3>
<pre><code>id,name
1,Ada
2,Grace</code></pre>
      <p>By default, Scriptella treats the first CSV row as column headers (<code>id,name</code>). These names become <code>$id</code> and
        <code>$name</code> variables, while subsequent rows contain the data. For headerless CSV files, set <code>headers=false</code> in
        the CSV connection properties.</p>
      <h3>csv-to-sql.etl.xml</h3>
<pre><code class="language-xml">&lt;!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd"&gt;
&lt;etl&gt;
    &lt;connection id="input" driver="csv" url="people.csv"/&gt;
    &lt;connection id="output" driver="text" url="load.sql"/&gt;

    &lt;query connection-id="input"&gt;
        &lt;script connection-id="output"&gt;
            INSERT INTO people (id, name) VALUES ($id, '$name');
        &lt;/script&gt;
    &lt;/query&gt;
&lt;/etl&gt;</code></pre>
      <h3 id="Command-Line+Execution">Run the job</h3>
      <p>From that directory, run:</p>
<pre><code>scriptella.sh csv-to-sql.etl.xml</code></pre>
      <p>Open the generated <code>load.sql</code>. It contains these statements (leading whitespace may differ):</p>
<pre><code class="language-sql">INSERT INTO people (id, name) VALUES (1, 'Ada');
INSERT INTO people (id, name) VALUES (2, 'Grace');</code></pre>
      <p>You have read two CSV rows and generated one SQL statement for each. No SQL has been executed against a database. Running this job
        again replaces the output file.</p>
      <aside class="note"><strong>Text output:</strong> <code>$id</code> and <code>$name</code> insert text into the output. This small
        example assumes names without apostrophes; it does not escape SQL strings. For inserting real data through JDBC, use bound
        parameters as shown below.</aside>
      <h2 id="how-it-works">3. How Scriptella works</h2>
      <p>An ETL file declares connections and the queries and scripts that use them. A connection can point to a database, a file, or
        another supported resource.</p>
      <figure>
        <picture>
          <source media="(max-width: 600px)" srcset="images/tutorial-rows-mobile.svg" width="320" height="500">
          <img src="images/tutorial-rows.svg" width="760" height="220" alt="A source query emits two rows. Each row exposes id and name variables and runs the nested destination script once.">
        </picture>
        <figcaption>A query supplies rows; its nested script runs once for every row.</figcaption>
      </figure>
      <ol>
        <li><code>&lt;query connection-id="input"&gt;</code> reads the CSV rows. An empty CSV query reads all data rows using the header
          names as variables.</li>
        <li>For the first row, <code>id</code> is <code>1</code> and <code>name</code> is <code>Ada</code>. The nested script writes one
          statement through the <code>output</code> connection.</li>
        <li>The query advances to Grace's row and runs the same script with the new values.</li>
      </ol>
      <p>The <code>connection-id</code> selects which connection executes a query or script. With a JDBC source, the query contains a SQL
        <code>SELECT</code>; each result row exposes its columns to the nested script.</p>
      <h2 id="Copy+table+to+another+database">4. Copy rows between databases</h2>
      <p>Now use that pattern to copy a <code>people</code> table from MySQL to PostgreSQL. This example assumes both servers are running
        and you can create tables in <code>source_db</code> and <code>target_db</code>.</p>
      <figure>
        <picture>
          <source media="(max-width: 600px)" srcset="images/tutorial-databases-mobile.svg" width="320" height="500">
          <img src="images/tutorial-databases.svg" width="760" height="220" alt="MySQL source_db sends people rows through Scriptella to PostgreSQL target_db using bound JDBC parameters.">
        </picture>
        <figcaption>The source SELECT supplies values for the target INSERT.</figcaption>
      </figure>
      <h3>Prepare the databases and drivers</h3>
      <p>Create this table in both databases. Start with an empty target table:</p>
<pre><code class="language-sql">CREATE TABLE people (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);</code></pre>
      <p>Insert the sample rows in MySQL only:</p>
<pre><code class="language-sql">INSERT INTO people (id, name) VALUES (1, 'Ada'), (2, 'Grace');</code></pre>
      <p>Download <a href="https://dev.mysql.com/downloads/connector/j/">MySQL Connector/J</a> and the
        <a href="https://jdbc.postgresql.org/download/">PostgreSQL JDBC driver</a>. Create a <code>lib</code> directory beside your new ETL
        file. Copy the downloaded JARs into it and rename them to match these paths:</p>
<pre><code>database-copy/
├── mysql-to-postgres.etl.xml
└── lib/
    ├── mysql-connector-j.jar
    └── postgresql.jar</code></pre>
      <h3>Create mysql-to-postgres.etl.xml</h3>
      <p>Use the following file, replacing the JDBC URLs, usernames, and password placeholders with the values for your databases. The
        driver paths are relative to the ETL file.</p>
<pre><code class="language-xml">&lt;!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd"&gt;
&lt;etl&gt;
    &lt;connection id="source" driver="mysql"
                url="jdbc:mysql://localhost:3306/source_db"
                user="source_user" password="replace-with-source-password"
                classpath="lib/mysql-connector-j.jar"/&gt;
    &lt;connection id="target" driver="postgresql"
                url="jdbc:postgresql://localhost:5432/target_db"
                user="target_user" password="replace-with-target-password"
                classpath="lib/postgresql.jar"/&gt;

    &lt;query connection-id="source"&gt;
        SELECT id, name FROM people ORDER BY id;
        &lt;script connection-id="target"&gt;
            INSERT INTO people (id, name) VALUES (?id, ?name);
        &lt;/script&gt;
    &lt;/query&gt;
&lt;/etl&gt;</code></pre>
      <p><code>?id</code> and <code>?name</code> bind the current row's values as JDBC prepared-statement parameters. Leave them unquoted:
        JDBC handles value types and string escaping, including names containing apostrophes.</p>
      <aside class="note"><strong>Passwords:</strong> For real jobs, use environment variables such as
        <code>password="${env.DB_PASSWORD}"</code> instead of hardcoding passwords in the ETL file. This keeps credentials out of files you
        share or commit. Environment variables still need protection; see the
        <a href="https://github.com/scriptella/scriptella-etl/blob/master/docs/cli-usage.md#environment-variables">credential guidance and
        security considerations</a>.</aside>
      <h3>Run and verify</h3>
      <p>From the directory containing the ETL file, run:</p>
<pre><code>scriptella.sh mysql-to-postgres.etl.xml</code></pre>
      <p>In your PostgreSQL client, verify the target data:</p>
<pre><code class="language-sql">SELECT id, name FROM people ORDER BY id;</code></pre><pre><code>id | name
---+------
 1 | Ada
 2 | Grace</code></pre>
      <p>This example inserts rows into an empty target. A second run with the same IDs will fail on the primary key. For repeatable
        imports, choose an upsert or clear a dedicated staging table before loading.</p>
      <h2 id="next-steps">5. Where to go next</h2>
      <ul>
        <li><a href="https://github.com/scriptella/scriptella-etl/blob/master/docs/first-migration.md">MySQL-to-PostgreSQL migration
          walkthrough</a>: another complete database copy, using customer records.</li>
        <li><a href="reference/index.html">Reference documentation</a>: connections, queries, scripts, properties, and error handling.</li>
        <li><a href="examples.html">Examples and patterns</a>: more database examples and integration techniques.</li>
        <li id="Executing+ETL+Files+from+Java"><a href="examples.html#Executing+ETL+Files+from+Java">Java embedding</a>: execute ETL files
          from your application.</li>
        <li><a href="reference/index.html#Command+Line+Execution">Command-line reference</a> and
          <a href="howto/create-database.html">database initialization how-to</a>: more ways to run Scriptella.</li>
      </ul>
      <p>Looking for a section from the previous tutorial? These examples now live in <a href="examples.html">Examples and patterns</a>:</p>
      <ul>
        <li id="Examples"><a href="examples.html#Examples">Additional database examples</a></li>
        <li id="Working+with+BLOBs"><a href="examples.html#Working+with+BLOBs">Working with BLOBs</a></li>
        <li id="Supporting+several+SQL+dialects"><a href="examples.html#Supporting+several+SQL+dialects">Supporting several SQL dialects</a></li>
        <li id="Use+Scriptella+as+Ant+Task"><a href="examples.html#Use+Scriptella+as+Ant+Task">Use Scriptella as an Ant task</a></li>
      </ul>
      <aside class="github-prompt" aria-label="Support Scriptella">
        <p>Scriptella useful? <a href="https://github.com/scriptella/scriptella-etl">Star it on GitHub</a> to help others discover it.</p>
      </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="benchmarks.html">Benchmarks</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>