scriptella

How to default a value of variable if etl.properties do not list a variable


I want to write a ETL for rows in given time duration.

I am thinking of passing start_time and end_time in etl.properties. However I am not sure how do I define the defaults if the properties file do not have them defined.

I was thinking of something like, but not sure if such this is possible or not.

<script connection-id="in" if="not properties.start_time">
    select @starttime := last_day(now() - interval 1 month);
</script>

If properties.start_time is not defined use the value of start time as one month from now.

How do I go about it.

Thanks


Solution

  • You can set the default value of the property by adding an assignment after the <include> element. Example:

    <properties>
        <include href="etl.properties"/>
        <!-- The new value is set only if it was not defined before -->
        start_time=value 
    </properties>
    

    In case of multiple declarations of the same property, the one that comes first takes precedence over subsequent declarations. This is why <include> comes first in the above example.

    ---- Update ----

    Alternative option would be to use a a ternary expression, e.g. ${start_time==null?'':a} or COALESCE SQL function which is supported by many databases. The latter should be more suitable for your example. Try if something like this will work:

    INSERT INTO SomeTable VALUES (COALESCE(?start_time, last_day(now() - interval 1 month)));