Workflows - Handling null values on bulk insert, BigQuery Resource GUI mode

[DiegoMorales]

Hello everyone,
I'm trying to perform a bulk insert of records to a BigQuery resource. The source of the data is the result of a MySQL query, I have made sure the schemas match. The bulk insert even works when there are no null values on the selected data from MySQL. My BigQuery block looks like so:

The error is giving me:

But I don't know how to modify my bulk insert statement in order to include the provided example "set first_name = {{ {RT$BQ_TYPE: 'STRING', value: null} }}".

[Darren]

Hey @DiegoMorales - welcome to the community!

This is definitely something that has come up before, as you can see here. The solution is relatively straightforward for simple queries but will be kind of a pain for bulk inserts.

Essentially, you'll need to iterate through query1.data and replace any null values with { RT$BQ: 'DATATYPE', value: null }. I'd probably recommend defining a transformer on query1 that does this. It might look something like:

Let me know if you have any questions about implementing this!

[DiegoMorales]

Thanks a lot @Darren! Adding the transformation worked!

[DiegoMorales]

Hi @Darren! Thanks again for the help! Wanted to share the code I ended up using for future reference for anyone facing this. Added a dictionary to map the fields of the data to the types BigQuery is expecting:

// Get array of objects
const dataArray = query1.data;

// Define the data types for each field
const dataTypesDict = {
    'field1': 'INT64',
    'field2': 'FLOAT64',
    'field3': 'STRING',
    };

// Iterate through each record
dataArray.forEach(record => {
  // Iterate through each field in the record
  for (let field in record) {
    // Check if the field is null or empty
    if (!record[field]) {
      // Replace with an object containing the expected data type and a null value
      record[field] = { RT$BQ_TYPE: dataTypesDict[field], value: null };
    }
  }
});

// Return the modified data array
return dataArray;