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:
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:
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.