PartialFailureError when inserting data from pubsub to bigquery

Question

Im following this guide: GUIDE and i keeping getting an "PartialFailureError: A failure occurred during this request." when im trying to insert data to bigQuery with cloud functions.

I have 2 functions: insertIntoBigquery() and updateCurrentDataFirebase(). The updateCurrentDataFirebase() function works as expected. This means that the reason for error must be by the function bigquery.dataset('weather_station_iot').table('raw_data').insert(data);

const data = { 
    deviceId: deviceId,
    timestamp: context.timestamp,
    temp: payload.temp,
    humidity: payload.hum
}; 

return Promise.all([
    insertIntoBigquery(data),
    updateCurrentDataFirebase(data)
]);

this works

function updateCurrentDataFirebase(data) {
    return db.ref(`/devices/${data.deviceId}`).set({
        humidity: data.humidity,
        temp: data.temp,
        lastTimestamp: data.timestamp
    });
}

this return the error

function insertIntoBigquery(data) {
    return bigquery
    .dataset('weather_station_iot')
    .table('raw_data') 
    .insert(data); 
}

Answer 1

For whoever seeking for relevant assistant over this issue:

After investing couple of hours in this, I agree that this is indeed a schema misfit issue.

Problem: it is pretty hard to understand what went wrong (error message is pretty unclear)

Solution:

1. Create a test table with a single field (of type string)
2. Use your code to insert data (without any flags or custom configuration)
3. Verify record is properly inserted
4. Extend schema with another field
5. Repeat testing and inspect errors

In my case I use the following code (Node JS):

  try {
    await cluster.dataset('{DATASET-GOES-HERE}').table('{TABLE}').insert(items)
  } catch (response) {
    console.log(response.errors)
  }

Where items variable is a simple json. My schema used fields name a, b, c, d, e (for testing purposes), so I've started with a json looks like this

{ "a": "test record" }

After verifying it works I proceeded to other data types (b -> integer, c -> timestamp, etc.) and my payload updated as well

{ "a": "test record", "b": 800, "c": 1653825665 }

What I find:

• The errors section inside the response object (when error occurs) is any array of errors with an actual meaningful error description
• When using timestamp - you either need to divide your value by 1000 or simply send ISO string using new Date().toISOString() or else you will get an error similar to this - Timestamp field value is out of range
• When defining a record you MUST switch mode to repeated or else you probably get Array specified for non-repeated field which means you can't populate record with values properly
• It takes a couple of seconds for schema to update so if you get no such field error - just wait a bit and give it another try
• I've used the graphical console to create test table as well as updating its schema and delete it in the end - very convenient and straightforward
• The preview option to view table data is not refreshing very often - prefer to use a query to inspect your results instead

---

Here's the schema I tested (in red - the wrong definition of a record)

---

And this is how I query and verified my results

---