Google Cloud Pub/Sub - Cloud Function & Bigquery - Data insert is not happening

Question

I am using a Google Cloud Platform Function that listens to a Pub/SubTopic and inserts the data in BigQuery.

The input data which I am passing from pub/sub console is in JSON format {"NAME", "ABCD"}, but from the console log, I could see that message is coming as {NAME, ABCD}, and during execution, it error as well. 2 common errors I faced

SyntaxError: Unexpected token n in JSON at position 1 at Object.parse (native) at exports.helloPubSub"
"ERROR: { Error: Invalid value at 'rows[0].json' "

Input given:

gcloud pubsub topics publish pubsubtopic1 --message {"name":"ABCD"}

Tried various formats of input data with single quotes and square brackets and other possible options as well, nothing helps

Workarounds tried like using JSON.parse, JSON.stringfy which helps to avoid the 1st issue which mentioned above but ends up with row[0] issue

When I pass the JSON input data as hard-coded values inside the cloud function like {"NAME", "ABCD"}, data is getting inserted properly.

/**This is working code since i hardcoded the data in JSON format, commented the lines which i tried and did not helped**/

/**
 * Triggered from a message on a Cloud Pub/Sub topic.
 *
 * @param {!Object} event Event payload and metadata.
 * @param {!Function} callback Callback function to signal completion.
 */
exports.helloPubSub = (event, callback) => {
  const pubsubMessage = event.data;
  console.log(Buffer.from(pubsubMessage.data, 'base64').toString());
  const {BigQuery} = require('@google-cloud/bigquery');
  const bigquery = new BigQuery();
  //console.log(Buffer.from(pubsubMessage.data, 'base64').toString());
  //console.log(JSON.parse(Buffer.from(pubsubMessage.data, 'base64').toString()));
  var myjson='{"NAME":"ABCD","STATE":"HHHH","AGE":"12"}';
  console.log(myjson);
   bigquery
    .dataset("DEMO")
    .table("EMP")
    .insert(JSON.parse(myjson),
    {'ignoreUnknownValues':true, 'raw':false})
  //.insert(JSON.parse(Buffer.from(pubsubMessage.data, 'base64').toString()), 
    .then ((data) => {
      console.log('Inserted 1 rows');
      console.log(data);
    })
    .catch(err => {
      if (err && err.name === 'PartialFailureError') {
        if (err.errors && err.errors.length > 0) {
          console.log('Insert errors:');
          err.errors.forEach(err => console.error(err));
        }
      } else {
        console.error('ERROR`enter code here`:', err);
      }
    });
  };

ch_mike ch_mike · Accepted Answer · 2019-03-28T17:55:23

I ran a quick test using gcloud to publish and to pull the message as well.

Using the syntax you mentioned I get the following result:

gcloud pubsub topics publish pubsubtopic1 --message {"name":"ABCD"}
gcloud pubsub subscriptions pull pubsubsubscription1

The result is:

DATA │ {name:ABCD}

If you use this syntax instead:

gcloud pubsub topics publish pubsubtopic1 --message "{\"name\":\"ABCD\"}"
gcloud pubsub subscriptions pull pubsubsubscription1

The result is:

DATA | {"name":"ABCD"}

EDIT 2019-04-01

The workaround above is for test purposes,the need to use escape characters is a caveat of using the command line. To publish from your real application, you may use a REST call or a client library as listed here.Please note the Pub/Sub API expects the message to be base64 encoded. For example:

POST https://pubsub.googleapis.com/v1/projects/{YOUR_PROJECT_ID}/topics/{YOUR_TOPIC}:publish?key={YOUR_API_KEY}

{
 "messages": [
  {
   "data": "eyJuYW1lIjoiQUJDRCJ9"
  }
 ]
}

Google Cloud Pub/Sub - Cloud Function & Bigquery - Data insert is not happening

1 Answers

EDIT 2019-04-01