I would like to create a csv file with a value I get during executing the etl script. E.g. I get a new value from a sequence and want to append it to the name of the csv. Sounds simple, but I'm really stuck...
My script:
<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
<description>Scriptella ETL</description>
<properties>
<include href="etl.properties"/> <!--Load from external properties file-->
</properties>
<!-- Connection declarations -->
<connection id="mypostgres" driver="$driver" url="$url" user="$user" password="$password" classpath="$classpath"/>
<connection driver="jexl" id="jexl"/>
<connection id="log" driver="text"/>
<query connection-id="mypostgres">
select nextval('transfer_id_seq') as tid
<script connection-id="jexl">
etl.globals['transferID'] = tid;
</script>
<script connection-id="log">
TransferID: ${etl.globals['transferID']}
</script>
</query>
<script connection-id="log">
TransferID (Outside query): ${etl.globals['transferID']}
</script>
<connection id="transfer-csv" driver="csv" url="transfer_${etl.globals['transferID']}.csv">
null_string=
quote=
</connection>
<script connection-id="transfer-csv">
col1, col2, col3
</script>
</etl>
My output:
C:\scriptella>scriptella
C:\java\jdk1.8\bin\java.exe -cp ;C:\dev\scriptella-1.1\lib\commons-compiler-jdk.jar;C:\dev\scriptella-1.1\lib\commons-compiler.jar;C:\dev\scriptella-1.1\lib\commons-jexl.jar;C:\dev\scriptella-1.
1\lib\commons-logging.jar;C:\dev\scriptella-1.1\lib\janino.jar;C:\dev\scriptella-1.1\lib\scriptella-core.jar;C:\dev\scriptella-1.1\lib\scriptella-drivers.jar;C:\dev\scriptella-1.1\lib\scriptella-tools
.jar scriptella.tools.launcher.EtlLauncher
23.02.2015 17:33:58 <WARNING> XML configuration warning in file:/C:/scriptella/etl.xml(35:7): The content of element type "etl" must match "(description?,properties?,connection*,(script*,
query*)*)".
23.02.2015 17:33:58 <INFO> Execution Progress.Initializing properties: 1%
23.02.2015 17:33:58 <INFO> Execution Progress.Initialized connection id=mypostgres, JdbcConnection{org.postgresql.jdbc4.Jdbc4Connection}, Dialect{PostgreSQL 9.3.2}, properties {}: 2%
23.02.2015 17:33:58 <INFO> Execution Progress.Initialized connection id=jexl, JexlConnection, Dialect{JEXL 2.0}, properties {}: 3%
23.02.2015 17:33:58 <INFO> Execution Progress.Initialized connection id=log, TextConnection, Dialect{Text 1.0}, properties {}: 4%
23.02.2015 17:33:58 <INFO> Execution Progress.Initialized connection id=transfer-csv, CsvConnection, Dialect{CSV 1.0}, properties {null_string=, quote=}: 5%
23.02.2015 17:33:58 <INFO> Execution Progress./etl/query[1] prepared: 6%
23.02.2015 17:33:58 <INFO> Execution Progress./etl/script[1] prepared: 7%
23.02.2015 17:33:58 <INFO> Execution Progress./etl/script[2] prepared: 10%
23.02.2015 17:33:58 <INFO> Registered JMX mbean: scriptella:type=etl,url="file:/C:/scriptella/etl.xml"
TransferID: 171
23.02.2015 17:33:58 <INFO> Execution Progress./etl/query[1] executed: 38%
TransferID (Outside query): 171
23.02.2015 17:33:58 <INFO> Execution Progress./etl/script[1] executed: 66%
23.02.2015 17:33:58 <INFO> Execution Progress./etl/script[2] executed: 95%
23.02.2015 17:33:58 <INFO> Execution Progress.Complete
23.02.2015 17:33:58 <INFO> Execution statistics:
Executed 1 query, 4 scripts, 4 statements
/etl/query[1]: Element successfully executed (1 statement). Working time 11 milliseconds. Avg throughput: 89,63 statements/sec.
/etl/query[1]/script[1]: Element successfully executed. Working time 9 milliseconds.
/etl/query[1]/script[2]: Element successfully executed (1 statement). Working time 4 milliseconds. Avg throughput: 206,37 statements/sec.
/etl/script[1]: Element successfully executed (1 statement). Working time 2 milliseconds. Avg throughput: 432,13 statements/sec.
/etl/script[2]: Element successfully executed (1 statement). Working time 2 milliseconds. Avg throughput: 447,04 statements/sec.
Total working time: 0,26 second
23.02.2015 17:33:58 <INFO> Successfully executed ETL file C:\scriptella\etl.xml
As you see the csv filename is wrong:
Directory of C:\scriptella
23.02.2015 17:33 <DIR> .
23.02.2015 17:33 <DIR> ..
23.02.2015 11:28 282 etl.properties
23.02.2015 17:32 1.239 etl.xml
23.02.2015 17:33 133 transfer_transferID.csv
3 File(s) 1.654 bytes
2 Dir(s) 741.036.032 bytes free
There is no way to have a dynamic connection element, as Scriptella processes all connections at startup (from your 5% log line):
23.02.2015 17:33:58 <INFO> Execution Progress.Initialized connection id=transfer-csv, CsvConnection, Dialect{CSV 1.0}, properties {null_string=, quote=}: 5%
The best option is to use the scriptella driver, which will allow you to call another etl.xml as a subroutine (and no need for globals, actually):
etl.xml:
<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
<description>Scriptella ETL</description>
<properties>
<include href="etl.properties"/> <!--Load from external properties file-->
</properties>
<!-- Connection declarations -->
<connection id="mypostgres" driver="$driver" url="$url" user="$user" password="$password" classpath="$classpath"/>
<connection id="log" driver="text"/>
<connection id="scriptella" driver="scriptella"/>
<query connection-id="mypostgres">
select nextval('transfer_id_seq') as tid
<script connection-id="log">
TransferID: $tid
</script>
<script connection-id="scriptella">
dynamic.xml
</script>
</query>
</etl>
dynamic.xml:
<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
<connection id="transfer-csv" driver="csv" url="transfer_${tid}.csv">
null_string=
quote=
</connection>
<script connection-id="transfer-csv">
col1, col2, col3
</script>
</etl>
NOTE: The ${var} syntax is required in the dynamic.xml file's connection url.
Also, there is no way to have scriptella append to a csv file (it will truncate each time), so I think what you are trying to accomplish might require a rethinking of your process. The Scriptella FAQ on Working with CSV Data suggests using HSQLDB text tables, which might help -- using HSQLDB or H2 to stage the data you need to export will probably be a performance gain and make your process more maintainable in the long run.