google cloud functions - Write query in BigQuery from Pubsub

Question

need some help.

I'm receiving messages with data in a PubSub topic, I need to insert the data I get from the message and query in BigQuery with a background cloud function(PUB/SUB)...

What I manage to do:

/**
 * Triggered from a message on a Cloud Pub/Sub topic.
 *
 * @param {!Object} event Event payload.
 * @param {!Object} context Metadata for the event.
 */
exports.insertBigQuery = (message, context) => {
    extractQuery(message.data);
   
};

function extractQuery(pubSubMessage){
    // Decide base64 the PubSub message
    let logData = Buffer.from(pubSubMessage, 'base64').toString();
    // Convert it in JSON
    let logMessage= JSON.parse(logData);

    console.log(logMessage.customerToken)
    console.log(logMessage.fbclid)
    console.log(logMessage.fbc)
    console.log(logMessage.fbp)
    console.log(logMessage.firstHitTS)
    console.log(logMessage.consentFB)

    main();
    
    return logMessage

    }

"use strict";

function main() {
  const { BigQuery } = require("@google-cloud/bigquery");
  const bigquery = new BigQuery();

  async function query() {
    const query = `INSERT INTO MYTABLE( customerToken, fbclid, fbc, fbp, firstHitTS, consentFB)
    VALUES ("customerTokenSCRIPTCLOUD","fbclidSCRIPT"," fbcSCRIPTCLOUD"," fbpSCRIPTCLOUD","2021-01-05",TRUE )`;

    const options = {
      query: query,
      location: "US",
    };

    const [job] = await bigquery.createQueryJob(options);
    console.log(`Job ${job.id} started.`);

    const [rows] = await job.getQueryResults();

    console.log("Rows:");
    rows.forEach((row) => console.log(row));
  }

  query();
}

Now every time I receive a message I query in bigQuery, but my VALUES is hard coded, as you can see here:

const query = `INSERT INTO devsensetestprojects.TestDataSet.fbSimpleData( customerToken, fbclid, fbc, fbp, firstHitTS, consentFB)
    VALUES ("customerTokenSCRIPTCLOUD","fbclidSCRIPT"," fbcSCRIPTCLOUD"," fbpSCRIPTCLOUD","2021-01-05",TRUE )`;

What I'm not able to do is to get the values from function extractQuery(pubSubMessage) and use them in my query the same way I use in the function (logMessage.SOMEVALUE) to have the correct values I need.

Thanks in advance!

Answer 1

As you said, you are beginner in development. Here a more concise and efficient code. I didn't tested it but it is closer of what you want. Let me know is some part are mysterious for you!

// Make them global to load them only when the Cloud Function instance is created
// They will be reused in the subsequent processing and until the instance deletion
const { BigQuery } = require("@google-cloud/bigquery");
const bigquery = new BigQuery();



exports.insertBigQuery = async (message, context) => {

    // Decode base64 the PubSub message
    let logData = Buffer.from(message.data, 'base64').toString();
    // Convert it in JSON
    let logMessage= JSON.parse(logData);

    const query = createQuery(logMessage)

    const options = {
        query: query,
        location: "US",
    };

    const [job] = await bigquery.createQueryJob(options);
    console.log(`Job ${job.id} started.`);

    // Only wait the end of the job. Theere is no row as answer, it's only an insert
    await job.getQueryResults();

}

function createQuery(logMessage) {
    // You maybe have to format correctly the logMessage.firstHitTS to be accepted by BigQuery as a date.
    return `INSERT INTO MYTABLE(customerToken, fbclid, fbc, fbp, firstHitTS, consentFB)
                   VALUES (logMessage.customerToken, logMessage.fbclid, logMessage.fbc, logMessage.fbp,
                           logMessage.firstHitTS, logMessage.consentFB)`;
}