Riferimenti: Esempio di script CCDS Google BigQuery

Verifica connessione
Importa struttura
Importare dati

Script di esempio di Google BigQuery

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

Test Connection

******************/
function testConnection(context) {
    
    var dataSource = context.getDataSource();
    var projectId = dataSource.getSetting('projectId').getValue();
    var datasetId = dataSource.getSetting('datasetId').getValue();
    var url = 'https://www.googleapis.com/bigquery/v2/projects/' + projectId + '/datasets/' + datasetId +'?maxResults=1';
    
    try {
        httpsGET(url);
    } catch(e) {
        ai.log.logInfo('error logging in...', 'confirm user privileges for dataset access- ' + projectId + ':' + datasetId);
        ai.log.logError('message from service', '' + e);
        return false;
    }
    ai.log.logInfo('success!', 'user authentication is valid for ' + projectId + ':' + datasetId);
    return true;
    
}


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

Import Structure

******************/
function importStructure(context) {
    var dataSource = context.getDataSource();
    var projectId = dataSource.getSetting('projectId').getValue();
    var datasetId = dataSource.getSetting('datasetId').getValue();
    
    var builder = context.getStructureBuilder();
    
    //generate URL to find all tables in project:dataset
    var url = 'https://www.googleapis.com/bigquery/v2/projects/' + projectId + '/datasets/' + datasetId +'/tables'; 
    
    //there's a try-catch built into this function
    var response = httpsGET(url);

    //initialize array of tables from response
    var bigQueryTables = JSON.parse(response).tables;
    ai.log.logInfo('tables', JSON.stringify(bigQueryTables));
    
    ai.log.logInfo('found ' + bigQueryTables.length + ' table(s) in ' + projectId + ':' + datasetId);
    
    //iterate over table array
    for(var i = 0; i < bigQueryTables.length; i++){
        
        //set current table for structure import
        var currentTable = bigQueryTables[i].tableReference.tableId;
        
        try {
            ai.log.logVerbose('adding table: ' + currentTable);
            
            //CCDS APIs to add table
            var table = builder.addTable(currentTable);
            table.setDisplayName(currentTable);
            
            //generate URL to return current table's metadata
            url = 'https://www.googleapis.com/bigquery/v2/projects/' + projectId + '/datasets/' + datasetId + '/tables/' + currentTable;
            var fieldSet = JSON.parse(httpsGET(url)).schema.fields;
            
            //build column structure
            for(var j = 0; j < fieldSet.length; j++) {
                var order = j+1;
                addColumn(table, fieldSet[j], order);
            }    
        } catch(e) {
            ai.log.logError('message received importing structure', '' + e);
        }
    
        
    }

    return true;
}

function addColumn(table, column, index){
    var recordColumn = table.addColumn(column.name);
    recordColumn = recordColumn.setDisplayName(column.name);
    
    //known GBQ datatypes.
    switch(column.type){
        case "STRING": recordColumn.setTextColumnType(500);break;
        
        case "BYTES": recordColumn.setIntegerColumnType();break;
        case "INTEGER": recordColumn.setIntegerColumnType();break;
        case "INT64": recordColumn.setIntegerColumnType();break;
        
        case "FLOAT": recordColumn.setFloatColumnType();break;
        case "FLOAT64": recordColumn.setFloatColumnType();break;
        
        case "BOOLEAN": recordColumn.setBooleanColumnType();break;
        case "BOOL": recordColumn.setBooleanColumnType();break;
        
        case "TIMESTAMP": recordColumn.setDateTimeColumnType();break;
        case "DATE": recordColumn.setDateTimeColumnType();break;
        case "TIME": recordColumn.setDateTimeColumnType();break;
        case "DATETIME": recordColumn.setDateTimeColumnType();break;
        
        default: recordColumn.setTextColumnType(20);break;
    }
    recordColumn.setDisplayOrder(index);
}


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

Import Data

******************/
function importData(context) {

    var dataSource = context.getDataSource(),
        projectId = dataSource.getSetting('projectId').getValue(),
        datasetId = dataSource.getSetting('datasetId').getValue(),
        rowset = context.getRowset();

    rowset.setSmartParsingEnabled(true);
    
     
    var tableId = rowset.getTableId();
    //adjust maxResults to see more or less records at import.
    var tableURL = 'https://www.googleapis.com/bigquery/v2/projects/' + projectId + '/datasets/' + datasetId + '/tables/' + tableId + '/data?maxResults=10';
    var response = JSON.parse(httpsGET(tableURL));
    
    ai.log.logInfo('response', JSON.stringify(response));
    
    var rows = response.rows;
    addRecords(rows, rowset);
        
    
    return true;
    
}

function addRecords(rows, rowset) {
    
    for (var i = 0; i < rows.length; i++) {

        var cells = [];
        var row = rows[i].f;
        
        for (var j = 0; j < rowset.getColumns().length; j++) {
            //GBQ provides position-based response values, so we resolve them by using each column's displayOrder created during structure import.
            var order = rowset.getColumns()[j].getDisplayOrder() - 1;
            var type = rowset.getColumns()[j].getColumnType();
            
            var value = row[order].v;
            
            //the reason this evaluation is in place is moderately absurd, but essential nonetheless... https://www.epochconverter.com/
            if (type == 'DateTimeColumn') {
                //take GBQ seconds-based date, and convert to JS-friendly milliseconds. 
                //milliseconds = seconds * 1000
                cells.push(new Date(value * 1000));
            } else {
                cells.push(value);
            }
        }
        rowset.addRow(cells);
    }
    
}

function httpsGET(url){
    
    var method = 'GET';
    var body = null;
    var headers = {};

    var response = null;
    try {
        ai.log.logVerbose('reaching out to external application service...', url);
        response = ai.https.authorizedRequest(url, method, body, headers);
    } catch (exception) {
        ai.log.logError('Test Connection HTTPS Request failed', '' + exception);
        throw 'failed to receive response';
    }
    return response.getBody();
}