Reference: CCDS Script Example Amazon S3

Test Connection.
Import Structure.
Import Data.

Amazon S3 Sample Script

/******************

Test Connection

******************/
function testConnection(context) {
    //var keys = ai.awss3.listKeys('bucketName');
    var keys = ai.awss3.listKeys('bucketName', 'filePath', 'region', 'AWSAccessKey', 'AWSSecretKey');
    return true;
}


/******************

Preview Data

******************/
/* JavaScript Document
function previewData(context)
{
    // not supported
}


/******************

Import Structure

******************/
function importStructure(context) {

    var addColumn = function (table, column, order) {
        var recordColumn = table.addColumn(column.name);
        recordColumn.setDisplayName(column.displayName);
        switch (column.type) {
            case "text":
                recordColumn.setTextColumnType(column.length); //this is where the default gets overruled, having both `type : "text"` and `length : x` defined in ExternalMetadataDefiniton
                break;
            case "int":
                recordColumn.setIntegerColumnType();
                break;
            case "float":
                recordColumn.setFloatColumnType();
                break;
            case "boolean":
                recordColumn.setBooleanColumnType();
                break;
            case "datetime":
                recordColumn.setDateTimeColumnType();
                break;
            default:
                recordColumn.setTextColumnType(200);
                break;
        }
        recordColumn.setDisplayOrder(order);
        recordColumn.setIncludeByDefault(column.includeByDefault || false);
        recordColumn.setIncludeInKey(column.includeInKey || false);
        recordColumn.setMandatoryForImports(column.mandatoryForImport || false);
        recordColumn.setRemoteColumnType(column.remoteColumnType || "String");
    };

    var builder = context.getStructureBuilder();

    for (var i = 0; i < ExternalSystem.Tables.length; i++) {
        var externalTable = ExternalSystem.Tables[i]; //a dig into the external metadata definition contents
        var table = builder.addTable(externalTable.name);
        table.setDisplayName(externalTable.displayName);

        for (var j = 0; j < externalTable.columns.length; j++) {
            addColumn(table, externalTable.columns[j], j + 1);
        }
    }
}

ExternalSystem = {
    Tables: [
        {
            name: "ge_gl_detail",
            displayName: "ge_gl_detail",
            bucketname: 'david.elliott.workday.test.bucket',
            key: 'sampleFile.csv',
            region: 'us-east-2',
            columns: [
                { name: "Purchase Order", displayName: "Purchase Order" },
                { name: "Company", displayName: "Company" },
                { name: "Business Unit", displayName: "Business Unit" },
                { name: "G/L Date", displayName: "G/L Date", type: "datetime" },
                { name: "Amount", displayName: "Amount", type: "float" },
            ]
        }
    ]
};

/******************

Import Data

******************/
function importData(context) {
    // Step 1: Make use of passed in contextual information to create a rowset object. 
    // Here, I am simply assigning tableId, maxRows and columnNames to variables to be used elsewhere in this script.
    var rowset = context.getRowset();
    var dataSource = context.getDataSource();
    var tableId = rowset.getTableId();


    //Use the tableId, defined in the structure process (and ExternalSystem object) to soon unarchive the appropriate file path.
    var internalTableId = getInternalObjectKey(tableId);

    //Return details from ExternalSystem definition to get ready for call to S3 library method
    var filePath = getS3Key(internalTableId);
    var bucketName = getS3BucketName(internalTableId);
    var region = getS3Region(internalTableId);
    var AWSAccessKey = getAWSAccessKey(dataSource);
    var AWSSecretKey = getAWSSecretKey(dataSource);
    ai.log.logVerbose('internalTableId: ' + internalTableId + ' ;filePath: ' + filePath + ' ; bucket: ' + bucketName + ' ; region: ' + region);

    //enable smart parsing for easier datatype matching at import
    rowset.setSmartParsingEnabled(true);

    var keys = ai.awss3.listKeys(bucketName, region, AWSAccessKey, AWSSecretKey);
    var keysList = '';
    for (var keyIndex = 0; keyIndex < keys.length; keyIndex++) {
        keysList += keyIndex + ' = ' + keys[keyIndex] + ' ';
    }
    ai.log.logVerbose('keys: ' + keysList); 

    var file;

    // Step 3: Make call to S3 AI library to get the file.
    try {
        file = ai.awss3.getFile(bucketName, filePath, region, AWSAccessKey, AWSSecretKey);
    } catch (e) {
        ai.log.logError('' + e);
        throw '' + e + '. Check logs for additional information';
    }

    if (file) {
        ai.log.logVerbose('showing some of the data returned from S3; : length : ' + file.length, file);
    }
    else {
        ai.log.logError('no data returned to us; nothing to process!');
        return;
    }

    //process CSV into an array of records.
    var rows = CSVToArray(file);
    // ai.log.logInfo('CSVToArray returned : ', '' + rows.length);
    //load array of records into staging.
    addRows(rowset, rows, internalTableId);

}

/******************

Supporting Functions

******************/
/**
 * build columns during importStructure
 */
function addColumn(table, column) {
    var recordColumn = table.addColumn(column.name);
    recordColumn.setDisplayName(column.displayName);
    switch (column.type) {
        case "text":
            recordColumn.setTextColumnType(column.length);
            break;
        case "int":
            recordColumn.setIntegerColumnType();
            break;
        case "float":
            recordColumn.setFloatColumnType();
            break;
        case "boolean":
            recordColumn.setBooleanColumnType();
            break;
        case "date":
            recordColumn.setDateTimeColumnType();
            break;
        case "datetime":
            recordColumn.setDateTimeColumnType();
            break;
        default:
            recordColumn.setTextColumnType(20);
            break;
    }
    recordColumn.setDisplayOrder(column.order);
    recordColumn.setIncludeByDefault(column.includeByDefault || false);
    recordColumn.setIncludeInKey(column.includeInKey || false);
    recordColumn.setMandatoryForImports(column.mandatoryForImport || false);
    recordColumn.setRemoteColumnType(column.remoteColumnType || "String");
}


/**
 * loads records to the staging area
 */
function addRows(rowset, rows, internalTableId) {
    var columnMetaData = getColumnMetaData(rowset.getColumns());
    // some biz rule transformations are filename specific
    var fileName = getFileName(internalTableId);

    var rowLength;
    var metadataLength = columnMetaData.length;
    // we start at rowIndex 1 since 0 is the header row
    var addRowsErrors = 'metadata length, which should be ' + metadataLength + ' and row length mismatch: ';
    var addRowsErrorsInitialLength = addRowsErrors.length;

    for (var rowIndex = 1; rowIndex < rows.length; rowIndex++) {
        var logStatusMessageRow = ' ';

        rowLength = rows[rowIndex].length;

        if (rowLength >= metadataLength) {
            var cells = [];
            var row = rows[rowIndex];
            for (var columnIndex = 0; columnIndex < metadataLength; columnIndex++) {
                var colName = columnMetaData[columnIndex].id;
                var colType = columnMetaData[columnIndex].type;
                var colOrder = columnMetaData[columnIndex].order;
                var colLength = columnMetaData[columnIndex].textSize;
                var value = row[colOrder];

                var cleanResult = cleanTheData(value, colType, colLength, colName, fileName);

                cells.push(cleanResult.theValue);
                if (typeof cleanResult.logStuff != "undefined" && cleanResult.logStuff !== null) {
                    // log details about the column/cell we just cleaned
                    logStatusMessageRow += cleanResult.logStuff;
                }
            }
            rowset.addRow(cells);
            if (typeof logStatusMessageRow != "undefined" && typeof logStatusMessageRow.length != "undefined") {
                // only emit meaningful errors/information
                if (logStatusMessageRow.length > 3) {
                    ai.log.logVerbose('row ' + rowIndex + ' details (' + logStatusMessageRow.length + ')', logStatusMessageRow);
                }
            }
        }
        // if we're here, then the number of columns in the source don't match the number expected
        else {
            addRowsErrors += '; row ' + rowIndex + ' length = ' + rowLength;
        }
    }
    if (addRowsErrorsInitialLength < addRowsErrors.length) {
        ai.log.logError('errors in addRows ', addRowsErrors);
    }
}


/**
 * strictly as a debugging aid
 */
function displayColumnMetaData(columnMetaData) {
    var whatIsThis = '';
    for (var i = 0; i < columnMetaData.length; i++) {
        whatIsThis += 'id=' + columnMetaData[i].id + ';type=' + columnMetaData[i].type + ';order=' + columnMetaData[i].order;
        if (columnMetaData[i].type === 'TextColumn') {
            whatIsThis += ';textSize=' + columnMetaData[i].textSize;
        }
        whatIsThis += '\n';
    }
    ai.log.logVerbose('column metadata (part 1)', whatIsThis.substring(0, 400));
    ai.log.logVerbose('column metadata (part 2)', whatIsThis.substring(400, 800));
    ai.log.logVerbose('column metadata (part 3)', whatIsThis.substring(800, 1200));
    ai.log.logVerbose('column metadata (part 4)', whatIsThis.substring(1200, 1600));

}


/**
 * returns an array of objects with the following properties:
 * 1. id
 * 2. type
 * 3. order
 * 4. textSize (text fields only)
 */
function getColumnMetaData(columns) {
    var columnMetaData = [];
    var whatIsThis = '';
    for (var i = 0; i < columns.length; i++) {
        columnMetaData[i] = {};
        columnMetaData[i].id = columns[i].getId();
        var colType = columns[i].getColumnType();
        columnMetaData[i].type = colType;
        // order - 1 to account for the initialization of 1.  
        // Adaptive returns undesirable values when display order starts at 0, yet, js needs the 0 so we offset the delta.
        columnMetaData[i].order = columns[i].getDisplayOrder() - 1;
        var colLength = 0;
        if (colType === 'TextColumn') {
            colLength = columns[i].getLength();
            columnMetaData[i].textSize = colLength;
        }
        whatIsThis += 'id=' + columnMetaData[i].id + ';type=' + colType + ';order=' + columnMetaData[i].order;
        if (colLength > 0) {
            whatIsThis += ';textSize=' + colLength;
        }
        whatIsThis += '\n';
    }
    //ai.log.logVerbose('column metadata (part 1)', whatIsThis.substring(0, 400));
    //ai.log.logVerbose('column metadata (part 2)', whatIsThis.substring(400, 800));
    //ai.log.logVerbose('column metadata (part 3)', whatIsThis.substring(800, 1200));
    //ai.log.logVerbose('column metadata (part 4)', whatIsThis.substring(1200, 1600));

    return columnMetaData;
}


/**
 * takes XML response as a parameter, then interrogates it for success / failure tags.
 */
function parseResponse(responseBody) {
    var parser = ai.xml.createParser();
    var xmlDoc = parser.parse(responseBody);

    try {
        var root = xmlDoc.getRootElement();
        var faultstringsearchResponse = root.getChildElement('faultstring');

        return faultstringsearchResponse.getText();
    }
    catch (exception) {
        // Example of logging to the CCDS log using the Error loglevel
        ai.log.logInfo('parse response failed!', '' + exception.message);
        return null;
    }
}


/**
 * Return ExternalSystem object's index for a given rowset.getTableId().
 */
function getInternalObjectKey(tableId) {
    var rowsetId;
    for (var i = 0; i < ExternalSystem.Tables.length; i++) {
        if (ExternalSystem.Tables[i].name == tableId) {
            rowsetId = i;
            break;
        }
    }
    return rowsetId;
}


/**
 * given an internalTableId, return the corresponding path to where the file lives on S3
 * depends on getInternalObjectKey
 */
function getS3Key(internalTableId) {
    return ExternalSystem.Tables[internalTableId].key;
}

/**
 * given an internalTableId, return the corresponding bucket name to where the file lives on S3
 * depends on getInternalObjectKey
 */
function getS3BucketName(internalTableId) {
    return ExternalSystem.Tables[internalTableId].bucketname;
}

/**
 * given an internalTableId, return the corresponding region where the file lives on S3
 * depends on getInternalObjectKey
 */
function getS3Region(internalTableId) {
    return ExternalSystem.Tables[internalTableId].region;
}

/**
 * given an internalTableId, return the corresponding name we're using for the file
 * depends on getInternalObjectKey
 */
function getFileName(internalTableId) {
    return ExternalSystem.Tables[internalTableId].name;
}

/**
 * given an internalTableId, return the corresponding display name we're using for the file
 * depends on getInternalObjectKey
 */
function getDisplayName(internalTableId) {
    return ExternalSystem.Tables[internalTableId].displayName;
}

/**
 * given a datasource, return the AWS Access Key to where the file lives on S3
 * the default name of this key is AWS_Access_Key
 * depends on datasource
 */
function getAWSAccessKey(dataSource, settingName) {
    if (settingName) return dataSource.getSetting(settingName).getValue();

    // use default setting name
    return dataSource.getSetting("AWS_Access_Key").getValue();;
}

/**
 * given a datasource, return the AWS Secret Key to where the file lives on S3
 * the default name of this key is AWS_Secret_Key
 * depends on datasource
 */
function getAWSSecretKey(dataSource, settingName) {
    if (settingName) return dataSource.getSetting(settingName).getValue();

    // use default setting name
    return dataSource.getSetting("AWS_Secret_Key").getValue();;
}


/**
 * Do simple data cleanup such as truncating large fields, removing $ and |
 */
function cleanTheData(inValue, propType, colLength, colName, fileName) {
    if (typeof inValue === 'undefined' || inValue === 'NaN') {
        return { "theValue": null, "logStuff": null };
    }

    var logStatusMessage = 'cleaning ' + colName;
    var retValue = inValue;
    var anErrorOccurred = false;

    // probably should only do on fields that will eventually be coerced to float and int
    // TODO - change logic to only do this on float/numeric fields and also make it global so all occurrences of $ will be removed
    // retValue = retValue.replace('$', '');

    if (propType === 'TextColumn') {
        // convert newline to tab 
        retValue = retValue.replace(/\n/g, "\t"); // turn newlines into tabs; saves a little whitespace when rendered
        // don't attempt this on datetime fields; won't work  replace pipes (which will cause us grief post-staging)
        retValue = retValue.replace(/\|/g, '-');
        try {
            // truncate - esp important when crazy large data comes in and would overflow the column allocated size
            if (retValue.length >= colLength) {
                //logStatusMessage = ';length before=' + retValue.length + '; colLength=' + colLength;
                //logStatusMessage += ';starts with' + retValue.substring(0, 30);
                retValue = retValue.substring(0, colLength);
                //logStatusMessage += ';length after =' + retValue.length;
            }
        } catch (error) {
            logStatusMessage += '; error occurred:' + error;
        }
    }

    // TODO add more replaces here

    if (anErrorOccurred) {
        return { "theValue": retValue, "logStuff": logStatusMessage };
    }
    return { "theValue": retValue, "logStuff": '' };
}

/**
 * we sometimes get dates in dd/mm/yyyy format
 * return a Date from this input
 */
function convertMMDDtoDDMM(dateStr) {

    var returnDate;
    var dateParts;
    var yearPart;

    /* Important - we must try to parse dates that look like this first
    1/2/2017
    28/2/2018
    ... and so on
    because in this dataset, these dates are dd/mm/yyyy and must be interpreted that way.
    It would be erroneous to treat 1/2/2017 as January 2, 2017; 
    in fact it's February 1, 2017
    */
    dateParts = dateStr.split("/");

    try {
        yearPart = dateParts[2].split(" ")[0]; // lop off the time part " 00:00...
        // js uses numbers 0 thru 11 for months, hence the following subtraction
        returnDate = new Date(yearPart, dateParts[1] - 1, dateParts[0], 0, 0, 0, 0);
        return returnDate;
    } catch (error) {
        // if we're here, dateStr didn't contain 2 /s; see if it's just a plain old date
        try {
            returnDate = new Date(dateStr);
            return returnDate;
        } catch (error) {
            // if we're here, the easy approaches failed...
            return null;
        }
    }

}

/**
 * Do simple data cleanup such as truncating large fields, removing $ and |
 */
function debugTheData(inValue, propType, colLength, colName) {
    if (typeof inValue === 'undefined' || inValue === 'NaN') {
        return { "theValue": null, "logStuff": null };
    }

    var logStatusMessage = ' ';
    var retValue = inValue;

    // probably should only do on fields that will eventually be coerced to float and int
    retValue = retValue.replace('$', '');

    if (propType === 'TextColumn' && colName === 'CRFDescription') {
        // try to convert newline to tab 
        retValue = retValue.replace(/\n/g, "\t");
        // don't attempt this on datetime fields; won't work  replace pipes (which will cause us grief post-staging)
        retValue = retValue.replace(/"|"/g, "-");
        try {
            logStatusMessage += ';CRFDescription starts with ' + retValue.substring(0, 200) + '\n and ends with ' + retValue.substring(retValue.length - 200) + '\n';
            logStatusMessage += ';length before=' + retValue.length + '; colLength=' + colLength;
            retValue = retValue.substring(0, colLength);
            logStatusMessage += ';length after =' + retValue.length;
        } catch (error) {
            logStatusMessage += ';exception occurred ' + error;
        }
        return { "theValue": retValue, "logStuff": logStatusMessage };
    }

    return { "theValue": null, "logStuff": null };
}

/**
 * 
 * returns an array of rows based on the string and delimiter
 */
function CSVToArray(strData, strDelimiter) {
    // Check to see if the delimiter is defined. If not,
    // then default to comma.
    strDelimiter = (strDelimiter || ",");

    // Create a regular expression to parse the CSV values.
    var objPattern = new RegExp(
        (
            // Delimiters.
            "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

            // Quoted fields.
            "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

            // Standard fields.
            "([^\"\\" + strDelimiter + "\\r\\n]*))"
        ),
        "gi"
    );


    // Create an array to hold our data. Give the array
    // a default empty first row.
    var arrData = [[]];

    // Create an array to hold our individual pattern
    // matching groups.
    var arrMatches = null;

    // Keep looping over the regular expression matches
    // until we can no longer find a match.

    // the ai environment will complain about the following = thinking
    // it should be ==
    // but that will be a bad thing, so don't do it
    while (arrMatches = objPattern.exec(strData)) {

        // Get the delimiter that was found.
        var strMatchedDelimiter = arrMatches[1];

        // Check to see if the given delimiter has a length
        // (is not the start of string) and if it matches
        // field delimiter. If id does not, then we know
        // that this delimiter is a row delimiter.
        if (
            strMatchedDelimiter.length &&
            strMatchedDelimiter !== strDelimiter
        ) {

            // Since we have reached a new row of data,
            // add an empty row to our data array.
            arrData.push([]);

        }

        var strMatchedValue;

        // Now that we have our delimiter out of the way,
        // let's check to see which kind of value we
        // captured (quoted or unquoted).
        if (arrMatches[2]) {

            // We found a quoted value. When we capture
            // this value, unescape any double quotes.
            strMatchedValue = arrMatches[2].replace(
                new RegExp("\"\"", "g"),
                "\""
            );

        } else {

            // We found a non-quoted value.
            strMatchedValue = arrMatches[3];

        }


        // Now that we have our value string, let's add
        // it to the data array.
        arrData[arrData.length - 1].push(strMatchedValue);
    }

    // Return the parsed data.
    return (arrData);
}