Pulling together data from multiple tables

[Tod Olson]

I've used Scriptella for some simple ETL tasks, straightforward table-to-delimited text sorts of things. Now I have a more complex task, and I'm not certain how to attack it with Scriptella.

Complicating factors:

1. Data is in a few different tables in an RDBMS, and I need to pull the data together into XML files.

2. For any given record, it might have a single row in table1, and 0-N rows in table2, and table3

3. The keys are all the same in tables1-3, but the keys for table4 are in a column in table2

In a nutshell, the data in the RDBMS has a hierarchy to it, but that hierarchy makes the relations in the RDBMS rather complex, and export will require some business logic, likely non-trivial.

Does this sound like something I should even contemplate attacking with Scriptella, or should I just to straight to a custom export program?

Thank you for any input.

-Tod

Tod Olson <t...@uchicago.edu>

Systems Librarian     

University of Chicago Library

[Fyodor Kupolov]

Hi Tod,

Described scenario is achievable with Scriptella, but be prepared to use Java (or JavaScript) for implementing complex parts of business logic. For complex migration scenarios I recommend a hybrid approach, when conceptual, workflow level tasks are defined in XML, while some specific transformations or business logic details are done in Java classes. So in your example you can start with something like this:

<connection id="db"/>

<connection id="text" driver="text"/> <!-- Use pure text driver to output XML - no validation or escaping. -->

<query connection-id="db"> <!-- start with main table1 -->

   select * from table1 

   <script connection-id="text">

      <main-record id="${id}">

   </script>

   <query> <!-- find all records from table2 with the same id -->

       select * from table2 where id=?id

       <script connection-id="text"> <!-- output them -->

           <child-record>....</child-record>

       </script>

       <query connection-id="db"> 

           <!-- For each records in table2, find child rows in Table4 using keys from Table2.

              Assume Table4keysColumn contains comma separated list of IDs.

              If it's not the case, consider writing helper Java method+use Janino or play with ${"a,b,c".replace(";", ",")} -->

             select * from table4 where ID in ($Table4keysColumn); 

            <script connection-id="text">

                <child-record4>....</child-record4>

            </script>

       </query>

   </query>

   <script connection-id="text">

      </main-record>

   </script>

</query>

Regards,

Fyodor

--
You received this message because you are subscribed to the Google Groups "Scriptella ETL" group.
To post to this group, send email to scrip...@googlegroups.com.
To unsubscribe from this group, send email to scriptella+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/scriptella?hl=en.

[Fyodor Kupolov]

The ETL is just a skeleton, and have issues, e.g. please use <![CDATA[ ...  ]]> to surround XML outputting parts.