9.1

Edit

Manipulate Data Stores

Iterate through Data Store query result

Convert Data Store to Map:

<script>
    <![CDATA[
        Map answerToSlotName = new HashMap();
    ]]>
</script>
<loop item="row">
    <list>
        <datastore name="slot_name_mapping">
            select * from @this;
        </datastore>
    </list>
    <body>
        <script>
            <![CDATA[
                answerToSlotName.put(row.get("task_answer_code").toString(), row.get("slot_name").toString());
            ]]>
        </script>
    </body>
</loop>

Save all input records to Data Store

Save records to Data Store:

<insert-datastore datastore-name="${datastore_name}" create="true">
    <script return="new com.google.gson.Gson().toJson(hit_submission_data_item.getWrappedObject().getItemValueMap())"/>
</insert-datastore>

Save archived Data Store to S3

Input:

  • Data Store name
  • S3 bucket name
  • Amount of records in one archive
  • Path to a temp folder

Result:

  • link to S3 for each created archive

Code example

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://web-harvest.sourceforge.net/schema/1.0/config" charset="UTF-8">

    <!-- Name of DS to save to s3 -->
    <required name="ds_name"/>

    <!-- bucket where archived results will be saved -->
    <var-def name="s3bucketName">temp_bucket</var-def>

    <var-def name="base_file_name">
        <!-- folder should be accessible for both WF and Postgres -->
        <template>/tmp/${ds_name}</template>
    </var-def>
    <log level="warn" message="${ds_name}: Generating CSV file"/>

    <script><![CDATA[
        result = new ArrayList();
    ]]></script>

    <var-def name="shouldContinue">true</var-def>
    <var-def name="counter">1</var-def>
    <var-def name="start">0</var-def>
    <var-def name="amount">200000</var-def><!-- Split DS to several parts as it can be huge and WF failed with OOM -->
    <while condition="${shouldContinue.toBoolean()}">
        <var-def name="end">
            <template>${start.toInt() + amount.toInt()}</template>
        </var-def>
        <var-def name="count">
            <datastore name="${ds_name}">
                SELECT count(*) FROM @this WHERE @id > ${start.toInt()} AND @id &lt;= ${end.toInt()};
            </datastore>
        </var-def>
        <var-def name="count">
            <template>${count.get(0).get(0).toInt()}</template>
        </var-def>
        <case>
            <if condition="${count.toInt() &gt; 0}">
                <log level="warn" message="${ds_name}: iteration ${counter}"/>

                <var-def name="file_name">
                    <template>${base_file_name}_${counter}</template>
                </var-def>
                <datastore name="${ds_name}">
                    COPY (
                       SELECT * FROM @this WHERE @id > ${start.toInt()} AND @id &lt;= ${end.toInt()}
                    ) TO '${file_name}' DELIMITER ',' CSV HEADER;
                </datastore>
                <log level="warn" message="${ds_name}: iteration ${counter}: CSV file for ${start}-${end} created"/>

                <var-def name="zipFile">
                    <zip>
                        <zip-entry name="${ds_name}_${counter}.csv">
                            <file path="${file_name}"/>
                        </zip-entry>
                    </zip>
                </var-def>
                <log level="warn" message="${ds_name}: iteration ${counter}: ZIP file for created"/>

                <var-def name="result_link">
                    <s3 bucket="${s3bucketName}">
                        <s3-put-public path="temp/${ds_name}_${counter}.zip" content="${zipFile}" content-type="application/zip"
                            content-disposition="inline"/>
                    </s3>
                </var-def>
                <script><![CDATA[
                    result.add(result_link.toString());
                ]]></script>
                <log level="warn" message="${ds_name}: iteration ${counter}: ZIP file sent to s3"/>

                <!-- Remove temp CSV file from server -->
                <script><![CDATA[
                    import java.io.File;

                    try {
                        File file = new File(file_name.toString());
                        // it is worth to check whether WF has access to delete file
                        // otherwise MT can fail with IOException due to insufficien space
                        if(!file.delete()){
                            log.warn("Cannot delete file: " + file_name.toString());
                        }
                    } catch(Exception e){
                        throw new Exception("Can't delete file: " + file_name.toString() + e.getMessage());
                    }
                ]]></script>

                <var-def name="start">
                    <template>${end.toInt()}</template>
                </var-def>
                <var-def name="counter">
                    <template>${counter.toInt() + 1}</template>
                </var-def>
            </if>
            <else>
                <var-def name="shouldContinue">false</var-def>
            </else>
        </case>
    </while>

    <export include-original-data="true">
        <multi-column list="result" split-results="true">
            <put-to-column name="archive_link"/>
        </multi-column>
    </export>
</config>

Fill Data Store from a ZIP file located on S3

Input:

  • Data Store name
  • S3 link to a Data Store archive

Output:

  • fills a Data Store on a target WF instance

Code example

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://web-harvest.sourceforge.net/schema/1.0/config" charset="UTF-8">

    <!-- Name of DS to which add records -->
    <required name="ds_name"/>
    <!-- link to archived CSV file -->
    <required name="archive_link"/>

    <var-def name="file_name">
        <template>/tmp/${ds_name}</template><!-- folder should be accessible for both WF and Postgres -->
    </var-def>

    <file path="${file_name}" action="write">
        <unzip>
            <http url="${archive_link}"/>
        </unzip>
    </file>
    <log level="warn" message="${ds_name}: File stored in file system"/>

    <datastore name="${ds_name}">
        COPY @this FROM '${file_name}' DELIMITER ',' CSV HEADER;
    </datastore>
    <log level="warn" message="${ds_name}: Records added to DS"/>

    <!-- Remove temp CSV file from server -->
    <script><![CDATA[
        import java.io.File;

        try {
            File file = new File(file_name.toString());
            // it worth to check whether WF has access to delete file
            // otherwise MT can fail with IOException due to insufficien space
            if(!file.delete()){
                log.warn("Cannot delete file: " + file_name.toString());
            }
        } catch (Exception e) {
            throw new RuntimeException("Cannot delete file: " + file_name.toString() + e.getMessage());
        }
    ]]></script>

    <export include-original-data="true">
    </export>
</config>

← PreviousNext →