Reference: CCDS Script Example Azure

Test Connection.
Import Structure.
Import Data.

Azure Sample Script

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

Test Connection

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

// The below settings need to be created on the data source for the script to pick up and connect successfully.
// Filename - Text
// Client Id - Password
// Client Secret - Password
// Tenant Id - Password
// Please default the script name to Azure Data Lake CCDS, with Basic Authentication. The script is assuming the data read from a CSV file from Azure Data Lake.

function testConnection(context) {
    var dataSource = context.getDataSource();

    var tenant_id = dataSource.getSetting("Tenant Id").getValue();
    var client_id = dataSource.getSetting("Client Id").getValue();
    var client_secret = dataSource.getSetting("Client Secret").getValue();
    //ai.log.logVerbose('deets', 'tenant_id:'+tenant_id + '; client_id'+client_id + '; client_secret'+client_secret);

    // Step 1: Create a https request to send to your API
    var access_token = azureAPI.requestNewToken(tenant_id, client_id, client_secret);

    return (access_token !== null);
}





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

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(1024);
                break;
        }
        recordColumn.setDisplayOrder(order);
        recordColumn.setIncludeByDefault(column.includeByDefault || true);
        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);
        }
    }
}

//If Oauth is used, please use the following:
externalSystem = {
    token_url: 'https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token',
    scope: 'https://storage.azure.com/.default',

    listDir: '',
    tables: [
        {
            name: 'Account',
            displayName: 'Account',
            fileNameBase: 'Account_',
            fileExtension: '.csv',
            filesStartNumber: 1, // must be a positive number; normally 0 or 1
            filesEndNumber: 7,    // must be greater than or equal filesStartNumber
            /* if filesStartNumber = 0 and filesEndNumber = 3 we will fetch
            Account_0.csv
            Account_1.csv
            Account_2.csv
            Account_3.csv
            */
            columns: []
          }
    ]
};

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

Import Data

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

function importData(context) {

    /**
     * internal helper function to importData - log numRecordsToLog records
     * NOTE: prior to calling this function, the file should have been downloaded
     * via client.downloadEncryptedFile() or client.downloadFile()
     * @param {*} numRecordsToLog - when 0, ALL of the records will be logged
     * @param {*} rows - the array of data we want to log
     */
    function logTheFile(numRecordsToLog, rows) {
        //Read and process the file
        let line = 'xxx';
        let lineCount = 0;
        while (line !== null && lineCount < numRecordsToLog) {
            line = rows[lineCount];
            if (line) {
                logContentDetails(JSON.stringify(line), 'record ' + ("000" + lineCount).slice(-3));
            }
            lineCount++;
        }
    }

    /**
    * internal function to importData
    * @returns 
    */
    function validateHeaderRow(rows) {
        let line = rows[0];
        // validate that header row has expected content; if not bail now
        var errMsg = 'Header row invalid';
        if (typeof line === 'undefined' || line === null) {
            errMsg += ' it is null or empty!';
            ai.log.logError(errMsg);
            throw errMsg + '; see log for more details';
        }

        let headerRow = line;
        if (headerRow.length === Row_Length) {
            ai.log.logVerbose('header row', line.join());
            return;
        }


        errMsg += '; length is ' + headerRow.length + ' but should be ' + Row_Length;
        ai.log.logError(errMsg, 'content: ' + line);
        // go ahead and log the next few to provide more hints to user
        line = rows[1];
        if (typeof line === 'undefined' || line === null) {
            throw errMsg + '; see log for more details';
        }
        ai.log.logError('line 2 of file', 'content: ' + line);

        line = rows[2];
        if (typeof line === 'undefined' || line === null) {
            throw errMsg + '; see log for more details';
        }
        ai.log.logError('line 3 of file', 'content: ' + line);

        line = rows[3];
        if (typeof line === 'undefined' || line === null) {
            throw errMsg + '; see log for more details';
        }
        ai.log.logError('line 4 of file', 'content: ' + line);
        throw errMsg + '; see log for more details';
    }


    var rowset = context.getRowset();
    rowset.setSmartParsingEnabled(true);
    var tableId = rowset.getTableId();
    var columns = rowset.getColumns();
    var columnRemoteIds = getColumnRemoteIds(columns);
    var Row_Length = columnRemoteIds.length;
    var dataSource = context.getDataSource();

    // get variable details
    var tenant_id = dataSource.getSetting("Tenant Id").getValue();
    var client_id = dataSource.getSetting("Client Id").getValue();
    var client_secret = dataSource.getSetting("Client Secret").getValue();
    let internalTableId = getInternalObjectKey(tableId);
    let filenameBase = getFileNameBase(internalTableId);
    let fileExtension = getFileExtension(internalTableId);
    let filesStart = getFilesStartNumber(internalTableId);
    let filesEnd = getFilesEndNumber(internalTableId);
    ai.log.logVerbose('filesStart:' + filesStart + '; filesEnd:' + filesEnd);
    // Step 1: Create a https request to send to your API
    var access_token = azureAPI.requestNewToken(tenant_id, client_id, client_secret);
    //ai.log.logInfo('access_token', access_token);

    let filesToFetch = [];
    if (filesStart >= 0 && filesEnd >= filesStart) {
        while (filesStart <= filesEnd) {
            filesToFetch.push(filenameBase + "" + filesStart + fileExtension);
            filesStart++;
        }
    }
    else {
        filesToFetch.push(filenameBase);
    }
    ai.log.logVerbose('files to fetch', filesToFetch.join());

    let filesFetched = [];
    //var directory = azureAPI.readDirectory(access_token);
    //throw 'readDirectory';

    for (var fileIndex in filesToFetch) {

        let currentFileName = filesToFetch[fileIndex];
        ai.log.logInfo('fetching file' + currentFileName);

        let result = azureAPI.readCSV(currentFileName, access_token);
        if (result.success === false) {

            if (result.exception) {
                ai.log.logError('readCSV HTTPS Request failed with exception', '' + result.exception);
                // NOTE - we don't necessarily have to throw this exception
                // we could just silently consume it and skip this file... TBD
                //throw result.exception;
            }
            if (result.httpCode) {
                ai.log.logInfo('readCSV HTTPS Request failed with HTTP Code ' + result.httpCode);
            }
            // since the call didn't succeed, bail now; nothing to do
            // and we should presume that we've exhausted the list of files to read
            ai.log.logInfo('since fetching ' + currentFileName + ' failed, we will not fetch remaining ones in this set');
            break;
        }
        filesFetched.push(currentFileName);
        //logTheFile(2, result.data.split('\r\n'));
        //throw 'xxx';   

        let rows = CSVToArray(result.data);


        validateHeaderRow(rows);

        /*
        logContentDetails(JSON.stringify(rows[0]), 'row - 0', 3);
        logContentDetails(JSON.stringify(rows[1]), 'row - 1', 3);
        logContentDetails(JSON.stringify(rows[2]), 'row - 2', 3);
        throw 'rows!';
        */
       
        addRows(rowset, rows);
    }

    sleepX(1000);
    ai.log.logVerbose('processed these files for ' + tableId, filesFetched.join());
}

azureAPI = {
    requestNewToken: function (tenant_id, client_id, client_secret) {
        // Step 1: Create a https request to send to your API
        var method = 'POST';

        // Setup request in body, sample is using xml, but could be json
        var body = 'grant_type=client_credentials&scope=' + externalSystem.scope;

        var base64 = ai.util.encodeBase64(client_id + ':' + client_secret, "");

        var headers = {
            "Content-Type": "application/x-www-form-urlencoded",
            "Authorization": 'Basic ' + base64,
            "x-ms-version": "2018-11-09",
        };

        // Step 2: Send request and receive response
        var response = null;
        try {
            var url = externalSystem.token_url.replace('{tenant_id}', tenant_id);
            response = ai.https.request(url, method, body, headers);
        }
        catch (exception) {
            ai.log.logError('requestNewToken HTTPS Request failed', '' + exception);
            return false;
        }

        // Step 3: Interrogate response to see if it was successful. 
        // Return true or false depending on the result.
        // Check that http communication was successful
        if (response.getHttpCode() == '200') {
            var responseBody = response.getBody();
            // Parse body and look for a success flag. Body could be xml or 
            // json depending on the use case.

            // ******* parsing logic here *******
            //ai.log.logVerbose('responseBody', responseBody);
            return JSON.parse(responseBody)['access_token'];

        } else {
            ai.log.logError('got this HttpCode: ' + response.getHttpCode());
            return null;
        }
    },

    /**
     * 
     * @param {*} filename 
     * @param {*} access_token 
     * @returns an object that looks like this: {success: false, data: null, httpCode: null, exception: null}
     */
    readCSV: function (filename, access_token) {

        let returnObj = { success: false, data: null, httpCode: null, exception: null };

        // Step 1: Create a https request to send to your API
        var method = 'GET';

        // Setup request in body, sample is using xml, but could be json
        var body = '';

        var headers = {
            "Content-Type": "application/x-www-form-urlencoded",
            "x-ms-version": "2018-11-09",
            "Authorization": "Bearer " + access_token,
        };

        let url = externalSystem.outboundBB + filename; // + 'xxxISNOTThere';
        // Step 2: Send request and receive response
        var response = null;
        try {
            response = ai.https.request(url, method, body, headers);
        }
        catch (exception) {

            returnObj.exception = exception;
            return returnObj;
        }

        // Step 3: Interrogate response to see if it was successful. 
        // Return true or false depending on the result.
        // Check that http communication was successful
        let httpCode = response.getHttpCode();
        if (httpCode == '200') {
            var responseBody = response.getBody();
            //logContentDetails(JSON.stringify(responseBody), 'responseBody', 3);
            //throw 'responseBody';
            returnObj.data = responseBody;
            returnObj.success = true;
            return returnObj;
        } else {
            returnObj.httpCode = httpCode;
            return returnObj
        }
    },

    readDirectory: function (access_token) {
        // Step 1: Create a https request to send to your API
        var method = 'GET';

        // Setup request in body, sample is using xml, but could be json
        var body = '';

        var headers = {
            "Content-Type": "application/x-www-form-urlencoded",
            "x-ms-version": "2019-12-12",
            "Authorization": "Bearer " + access_token,
        };

        // Step 2: Send request and receive response
        let url = externalSystem.listDir;
        var response = null;
        try {
            response = ai.https.request(url, method, body, headers);
        }
        catch (exception) {
            // Example of logging to the CCDS log using the Error loglevel
            ai.log.logError('readDirectory HTTPS Request failed', '' + exception);
            return false;
        }

        // Step 3: Interrogate response to see if it was successful. 
        // Return true or false depending on the result.
        // Check that http communication was successful
        if (response.getHttpCode() == '200') {
            var responseBody = response.getBody();
            var parser = ai.xml.createParser();
            var xmlDoc = parser.parse(responseBody);
            logContentDetails(JSON.stringify(responseBody), 'responseBody');
            throw 'responseBody';
            ai.log.logVerbose('responseBody', responseBody);
            var progress = '';
            var xmlElementRoot = xmlDoc.getRootElement();
            if (xmlElementRoot) {
                progress = xmlElementRoot.getName();
                ai.log.logVerbose('xmlElementRoot', progress); // NextMarker
            }

            let children = xmlElementRoot.getChildElements();
            //logContentDetails(JSON.stringify(children), 'children');
            ai.log.logVerbose('children', JSON.stringify(children)); // NextMarker


            var child0 = xmlElementRoot.getChildElementAt(0);
            if (child0) {
                progress = child0.getName();
                ai.log.logVerbose('child0', progress); // Name
                var child00 = child0.getChildElementAt(0);
                if (child00) {
                    progress = child00.getName();
                    ai.log.logVerbose('child00', progress); // Name
                }
            }

            //logContentDetails(JSON.stringify(responseBody), 'responseBody');
            throw 'responseBody';
            return responseBody;
        } else {
            ai.log.logError('readDirectory got this HttpCode: ' + response.getHttpCode());
            sleepX(1000);
            ai.log.logInfo('readDirectory url= ' + url);
            return null;
        }
    }
}

/**
 * Insert a single row into the staging table.
 * @param {*} rowset 
 * @param {*} row 
 */
function addRows(rowset, rows) {
    var columnRemoteIds = getColumnRemoteIds(rowset.getColumns());
    //logContentDetails(JSON.stringify(columnRemoteIds), 'columnRemoteIds');
    //throw 'columnRemoteIds';
    var row_length = columnRemoteIds.length;

    const MAX_TEST_ROWS = 20000;
    // start at 1 because 0 is the header row
    for (let rowIndex = 1; rowIndex < rows.length; rowIndex++) {
        //for (let rowIndex = 1; rowIndex < MAX_TEST_ROWS && rowIndex < rows.length; rowIndex++) {
        var cells = [];
        var row = rows[rowIndex];
        //logContentDetails(JSON.stringify(row), 'row');
        //throw 'row';
        if (row.length === row_length) {
            for (var columnIndex in columnRemoteIds) {

                var colIndex = columnRemoteIds[columnIndex].order;
                var colID = columnRemoteIds[columnIndex].id;
                var propType = columnRemoteIds[columnIndex].type;

                var value = row[colIndex];
                value = typeof value === 'undefined' || value === 'NaN' ? null : value.replace('$', '');

                // get rid of pipes
                if (propType == "TextColumn" && value !== null) {
                    value = value.replace(/\|/g, ' '); // this doesn't seem to be working
                    value = value.replace("|", " ");   // will this work? unfortunately, no :(
                }

                cells.push(value);
            }

            //logContentDetails(JSON.stringify(cells), 'cells');
            rowset.addRow(cells);
        }
        else {
            ai.log.logInfo('row ' + rowIndex + ' is wrong length. Should be ' + row_length + ', but is ' + rows[rowIndex].length);
            sleepX(500);
        }
    }
}

/**
 * returns an array of objects with the following properties:
 * 1. id
 * 2. type
 * 3. order
 * @param {*} columns  = context.getRowset().getColumns()
 */
function getColumnRemoteIds(columns) {
    var columnIds = [];
    for (var i = 0; i < columns.length; i++) {
        columnIds[i] = {};
        columnIds[i].id = columns[i].getId();
        columnIds[i].type = columns[i].getColumnType();
        columnIds[i].order = columns[i].getDisplayOrder() - 1; //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.
    }
    return columnIds;
}

// convert date to yyyy-mm-dd
function parseDate(value, delimeter) {
    var str = value.substring(0, 10).split(delimeter); //yyyy-mm-dd

    var Y = parseInt(str[0]);
    var M = parseInt(str[1]) - 1; // javascript months, Expected values are 0-11
    var D = parseInt(str[2]);

    // because tenant timezone is in UTC we need to convert the date into UTC timezone before importing it into staging tables
    return new Date(Date.UTC(Y, M, D));
}

// convert date to mm/dd/yyyy
function parseDate_mmmyy(value, delimeter) {
    var str = value.split(delimeter); //mmm yyyy | mmm-yy
    var monthArr = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];

    var Y = parseInt(str[1]);
    var M = monthArr.indexOf(str[0]); // javascript months, Expected values are 0-11
    var D = 1;

    // because tenant timezone is in UTC we need to convert the date into UTC timezone before importing it into staging tables
    return new Date(Date.UTC(Y, M, D));
}

// **********************************************************************
// CSV to Array
// **********************************************************************
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.
    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);
}

/**
 * 
 * @param {*} milliSeconds 
 */
function sleepX(milliSeconds) {
    var startTime = new Date().getTime(); // get the current time
    while (new Date().getTime() < startTime + milliSeconds);
}


/**
 * a utility function to log (1,000 bytes at a time) the content you want to analyze
 * NOTE - only effective when logging level is set to Verbose in Data Source Settings tab
 * @param {*} content - the content you want to analyze
 * @param {*} contentName - descriptor that will appear in the log file to help you identify what you're looking at
 * @param {*} limit - optional. If you don't want to log everthing; handy when you want, for example, just the header row and first few records of data
 */
function logContentDetails(content, contentName, limit) {

    /**
    * a utlility function - returns an array of the start and end to substring the content so that we can log
    * all of it in 1000-byte chunks (the max size of a verbose log detail)
    * so, for example, in the case of a 2025 length string this would return:
    * [ {start: 0, end: 1000}, 
    *   {start: 1000, end: 2000},
    *   {start: 2000, end: 2025} ]
    * @param {} content 
    */
    function getChunksToLog(content) {
        var bytesRemaining = content.length;
        var array = [];
        var startByte = 0;
        var endByte = 0;
        const maxDetailSize = 1000;
        while (bytesRemaining > 0) {
            endByte = Math.min(bytesRemaining, maxDetailSize);
            array.push({ start: startByte, end: startByte + endByte });
            startByte += endByte;
            bytesRemaining -= maxDetailSize;
        }

        return array;
    }

    if (ai.util.isUndefined(content)) {
        ai.log.logVerbose(contentName + ' 000 no content to display!');
        return;
    }

    var ChunksToLogArray = getChunksToLog(content);

    var limitUse = limit ? limit : ChunksToLogArray.length;
    if (limitUse > ChunksToLogArray.length) {
        limitUse = ChunksToLogArray.length;
    }

    for (var chunkIndexer = 0; chunkIndexer < limitUse; chunkIndexer++) {
        var chunkIdentifier = chunkIndexer + 1; // humans like to see number sequences that start with 1
        // to make sorting the log file easier, let's make each 'chunk' a 3-digit value with leading zeroes as necessary
        var chunkIdentifierPadded = ("000" + chunkIdentifier).slice(-3);

        ai.log.logVerbose(contentName + ' ' + chunkIdentifierPadded,
            content.substring(ChunksToLogArray[chunkIndexer].start, ChunksToLogArray[chunkIndexer].end));
        sleepX(1000); // to avoid excessive logging
    }
}

/**
 * Return ExternalSystem object's index for a given rowset.getTableId().
 * @param {*} tableId 
 */
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 fileNameBase we're using for the file
 * depends on getInternalObjectKey
 * return fileName rather than fileNameBase if fileNameBase is not present
 * @param {*} internalTableId 
 */
function getFileNameBase(internalTableId) {
    if (ai.util.isDefined(externalSystem.tables[internalTableId].fileNameBase)) {
        return externalSystem.tables[internalTableId].fileNameBase;
    }
    return externalSystem.tables[internalTableId].fileName;
}

/**
 * given an internalTableId, return the corresponding fileExtension we're using for the file
 * depends on getInternalObjectKey
 * @param {*} internalTableId 
 */
function getFileExtension(internalTableId) {
    return externalSystem.tables[internalTableId].fileExtension;
}

/**
 * given an internalTableId, return the corresponding filesStartNumber we're using for the file
 * depends on getInternalObjectKey
 * if not found, return -1; indicating this setting isn't present
 * @param {*} internalTableId 
 */
function getFilesStartNumber(internalTableId) {
    if (ai.util.isDefined(externalSystem.tables[internalTableId].filesStartNumber)) {
        return externalSystem.tables[internalTableId].filesStartNumber;
    }
    return -1;
}

/**
 * given an internalTableId, return the corresponding filesEndNumber we're using for the file
 * depends on getInternalObjectKey
 * if not found, return -1; indicating this setting isn't present
 * @param {*} internalTableId 
 */
function getFilesEndNumber(internalTableId) {
    if (ai.util.isDefined(externalSystem.tables[internalTableId].filesEndNumber)) {
        return externalSystem.tables[internalTableId].filesEndNumber;
    }
    return -1;
}