Scriptella -动态命名csv文件

Question

我希望创建一个csv文件，其中包含在执行etl脚本时得到的值。例如，我从序列中得到一个新值，并希望将其附加到csv的名称中。听起来很简单，但我真的被困住了.

我的剧本：

<!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>

我的产出：

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

正如您所看到的，csv文件名是错误的：

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

Answer 1

不可能有动态连接元素，因为Scriptella在启动时(从5%的日志行)处理所有连接：

23.02.2015 17:33:58 <INFO> Execution Progress.Initialized connection id=transfer-csv, CsvConnection, Dialect{CSV 1.0}, properties {null_string=, quote=}: 5%

最好的选择是使用scriptella驱动程序，它允许您调用另一个etl.xml作为子例程(实际上不需要全局)：

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>

注意： ${var}语法在dynamic.xml文件的连接url中是必需的。

而且，没有办法将scriptella附加到csv文件(每次都会截断)，所以我认为您要完成的任务可能需要重新考虑您的流程。关于使用CSV数据的Scriptella常见问题建议使用HSQLDB文本表，这可能会有所帮助--使用HSQLDB或H2来完成您需要导出的数据，这可能会提高性能，并使您的过程在长期内更易于维护。

Answer 2

在Scriptella1.2(我不确定它是否适用于较早的Scriptella版本)中，您可以像这样动态地设置CSV文件名：

<connection id="out" driver="csv" url="my_report_${date:today('yyyyMMdd_HHmmss')}.csv">

如：http://scriptella.org/reference/index.html#%3Cproperties%3E，请参阅“表达式和变量替换”。

顺便说一句，Scriptella缺乏更结构化和更方便的文档。