BigQuery Insert Documentation

Version: 3.1.0

Retrieved: 2025-10-09 15:15:46

Google BigQuery Insert

Insert row(s) into Google BigQuery tables.

The module accepts arrays or a single object as source. If source is an array the module will write multiple rows.

Settings

Name Requirements Purpose Default
Project Id String with length 1 to 64 The project id in Google Cloud  
Dataset Id String with length 1 to 64 The dataset id in Google Cloud  
Table Id String with length 1 to 64 The table id in Google Cloud  
Insert Id Property String with length 0 to 64 The property that contains the Insert Id. Mandatory if Insert Options -> Allow Empty InsertIds is false  
Source Property String with length 1 to 64 The property on the incoming message that contains the data to send data

Insert Options

Name Requirements Purpose Default
Allow Unknown Fields Boolean Whether or not to accept rows with fields that are not specified in the schema, ignoring the extra fields. false
Skip Invalid Rows Boolean Insert all valid rows of a request, even if invalid rows exist. If false, the entire request will fail if any invalid rows exist. false
Suppress Insert Errors Boolean If true, insert errors don't lead to an exception. This setting has no effect on which rows are inserted and which are not. false
Allow Empty InsertIds Boolean If true, Insert Id Property can be unspecified. This in turns allows for faster inserts, at the expense of possible record duplication if the operation needs to be retried. true

Insert Id

When using the Insert Id Property you need know what type of data you insert. Depending on inserting single row vs multiple rows the path to the Insert Id Property will be different

Single

{
    data: {
        'col1': 'value 1',
        'col2': 'value 2',
        'col3': '123'
    }
}

If col3 is the Insert Id Property the configuration would be

Insert Id Property = data.col3

Multiple

{
    data: [
        {
            'col1': 'value 1',
            'col2': 'value 2',
            'col3': '123'
        },
        {
            'col1': 'value 1',
            'col2': 'value 2',
            'col3': '456'
        }
    ]
}

If col3 is the Insert Id Property the configuration would be

Insert Id Property = col3

So when doing bulk inserts the path to the Insert Id Property will be based on the array and not on the message as it is when inserting single rows.

Credential

This module contains an option to select credentials to use in the module. All credentials supported by the module are presented in a drop-down.

Example - Single Row

The example below will insert the data found under the data property into Google BigQuery

# Settings:
Project Id = myGoogleProjectId
Dataset Id = myGoogleDatasetId
Table Id = myGoogleTableId


# Incoming message:
{
    'data':{
        'name':'machine-Q352',
        'temp':465.3,
        'timestamp': '2019-08-10 16:23:44'
    }
}

# Outgoing message:
{
    'data':{
        'name':'machine-Q352',
        'temp':465.3,
        'timestamp': '2019-08-10 16:23:44'
    },
    'crosser':{
        'success':true
    }
}

Example - Multiple Rows

The example below will insert all the objects found in the array under the data property into Google BigQuery

# Settings:
Project Id = myGoogleProjectId
Dataset Id = myGoogleDatasetId
Table Id = myGoogleTableId


# Incoming message:
{
    'data':[{
        'name':'machine-Q352',
        'temp':465.3,
        'timestamp': '2019-08-10 16:23:44'
    },{
        'name':'machine-Q353',
        'temp':203.1,
        'timestamp': '2019-08-10 16:23:47'
    },{
        'name':'machine-Q354',
        'temp':139.8,
        'timestamp': '2019-08-10 16:23:45'
    },]
}

# Outgoing message:
{
    'data':[{
        'name':'machine-Q352',
        'temp':465.3,
        'timestamp': '2019-08-10 16:23:44'
    },{
        'name':'machine-Q353',
        'temp':203.1,
        'timestamp': '2019-08-10 16:23:47'
    },{
        'name':'machine-Q354',
        'temp':139.8,
        'timestamp': '2019-08-10 16:23:45'
    },],
    'crosser':{
        'success':true
    }
}

Example Failed Processing

If the insert fails you will get a message out with information about the error. The original message will also be included.

Below we have a bad configuration using

Project Id = fakeProject
Dataset Id = fakeDataset
Table Id = fakeTable

# Outgoing message:
{
    'data':{
        'name':'machine-Q352',
        'temp':465.3,
        'timestamp': '2019-08-10 16:23:44'
    },
    'crosser':{
        'success':false,
        'message':'Google.Apis.Requests.RequestError\nNot found: Table fakeProject:fakeDataset.fakeTable [404]\nErrors [\n\tMessage[Not found: Table fakeProject:fakeDataset.fakeTable] Location[ - ] Reason[notFound] Domain[global]\n]\n'
    }
}

Search Documentation

Process & Analyze

Google