Fetch data from BigQuery

Step 1

To start this guide, you'll need a Bearer token from Google Cloud saved as a Secret. If you don't have this from before, please check out the following guide:

Retrieve a Bearer token from Google Cloud

Step 2

Go to Appfarm Create -> Services, and select the service you want to use (or create a new one). Then, create a new service endpoint, which will be used to fetch data from BigQuery. Give the endpoint a name and a readable ID.

Go down to "Process action", and click the value field to create a new action. You may name the Action "Process BigQuery".

Step 3

Inside Actions -> Process BigQuery, add a new Action Node "Web request". Set the following properties:

• URL: https://bigquery.googleapis.com/bigquery/v2/projects/[bq_project_id]/queries

• Authorization: Bearer token

• Auth token: Data-bind to Secrets.[The Bearer Token from Google]

• Prefix Bearer Keyword: true

• Body Type: Raw

Then, set the Body Content to the following:

return {
  "query": "SELECT [column] FROM [bq_project_id].[bq_dataset_name].[bq_table_name]",
  "parameterMode": "NAMED",
  "useLegacySql": false
}

To make the response readable for the Result Mapping in Appfarm, you need to flatten the result from BigQuery. To do so, copy/paste this code into the Result Parser:

//Flatten the result from BigQuery
function convertBQToFlattenResults(schema, rows) {

    let resultRows = []
    let result = {};
    
    function recurse (schemaCur, rowsCur, colName) {
    
        if (Array.isArray(schemaCur) && !Array.isArray(result[colName])) {
            for(let i=0, l=schemaCur.length; i<l; i++) {
                if (colName === "")
                    recurse(schemaCur[i], rowsCur.f[i], colName + schemaCur[i].name)
                else
                    recurse(schemaCur[i], rowsCur.f[i], colName + "." + schemaCur[i].name)
            }    
        }

        if (schemaCur.type && schemaCur.type === "RECORD") {
            if (schemaCur.mode !== "REPEATED") {
                let valIndex = 0
                for (let p in schemaCur.fields) {
                    if (rowsCur.v === null) {
                        recurse(schemaCur.fields[p], rowsCur, colName + "." + schemaCur.fields[p].name)
                    } else {
                        recurse(schemaCur.fields[p], rowsCur.v.f[valIndex], colName + "." + schemaCur.fields[p].name)
                    }
                    
                    valIndex++
                }
            } 
            
            if (schemaCur.mode === "REPEATED") {   
                result[colName] = [] 
                for (let x in rowsCur.v) {
                    recurse(schemaCur.fields, rowsCur.v[x], colName)
                }
            }
        } else {
            if (schemaCur.mode === "REPEATED") {
                if (rowsCur.v !== null) {
                    result[colName] = rowsCur.v.map( (value, index) => { return value.v })
                } else {
                    result[colName] = [ null ]
                }
                
            } else if (Array.isArray(result[colName])) {
                let nextRow = {} 
                for (let j in schemaCur) {
                    nextRow[colName + "." + schemaCur[j].name] = rowsCur.v.f[j].v 
                }
                result[colName].push(nextRow)
            } else {
                if (colName !== "")
                    result[colName] = rowsCur.v
            }
        }
    }

    for (let r=0, rowsCount=rows.length; r<rowsCount; r++) {
        result = {};
        recurse(schema, rows[r], "")
        resultRows.push(result)
    }

    return resultRows
}

let bqQueryApiResult = rawResponseData

let output = convertBQToFlattenResults(bqQueryApiResult.schema.fields, bqQueryApiResult.rows)

return output

Finally, add a new Result Mapping. Choose the data source you'd like to write data to, and write the column names from BigQuery into the value fields on the Object Class Properties.

Run the service

Now everything is set up to retrieve data from BigQuery. Make sure to first retrieve the Bearer token. Then, run the service to fetch data from BigQuery. If everything is set up correctly, all the objects requested in your query will be added to the selected data source.