GCP: BigQuery Node

The GCP: BigQuery Node allows a workflow to interact with Google BigQuery by fetching, creating, updating, or deleting data and optionally storing the result of the operation on the workflow payload.

Configuration

Configuration for the node is broken up into four sections.

Service Account Auth Token

A service account auth token is required for the workflow to authenticate with Google BigQuery. You may enter this token one of two ways:

• JSON Template accepts a JSON template for the token.
• Payload Path accepts a payload path for the token.

Resource & Action

Choose a resource to edit and an action to take. Currently the node supports the following resources and actions:

• Jobs

  • Query

• Datasets

  • Insert
  • Get
  • List
  • Update
  • Patch
  • Delete

• Tables

  • Insert
  • Get
  • List
  • Update
  • Patch
  • Delete

• Tabledata

  • Insert All
  • List

Request Config

The available options here will change depending on the selected resource and action. For example, if the selected action creates or updates a resource then a request body is required that contains the new or updated resource. Some actions require a dataset ID, some require both a dataset ID and a table ID, and some require neither. There are also some options that are only relevant to a specific resource and action, such as the “include hidden datasets” option when “Datasets: List” is selected.

In the example above, the selected resource and action value is “Tabledata: List” and the relevant configuration is being shown.

Result

Specify a payload path at which to place the results. This may be optional depending on the selected resource and action. If the request succeeds but no result is returned by BigQuery (for example, when deleting a resource) the result will be { success: true }.

Query Request Format

The expected format for the Query Request JSON Template section depends on the selected Resource and Action value. Below you’ll find example JSON templates for operations that require them.

Jobs: Query

Requests must match the structure outlined here. Properties that are marked as ”[Output-only]” do not need to be included in the request (id, kind, etag, etc.) An example request is shown below:

{
  "maxResults": 10,
  "query": "SELECT * FROM [my_dataset.my_database] WHERE id = '{{data.recordId}}' LIMIT 10"
}

Datasets: Insert / Update / Patch

Requests must match the structure outlined here. Properties that are marked as ”[Output-only]” do not need to be included in the request (id, kind, etag, etc.) An example request is shown below:

{
  "datasetReference": {
    "projectId": "my-project-id-1234",
    "datasetId": "my_dataset"
  },
  "friendlyName": "My dataset",
  "description": "A description of the dataset"
}

Tables: Insert / Update / Patch

Requests must match the structure outlined here. Properties that are marked as ”[Output-only]” do not need to be included in the request (id, kind, etag, etc.) An example request is shown below:

{
  "tableReference": {
    "projectId": "my-project-id-1234",
    "datasetId": "my_dataset",
    "tableId": "my_table"
  },
  "friendlyName": "My table",
  "description": "A description of the table",
  "schema": {
    "fields": [
      {
        "name": "id",
        "type": "INTEGER",
        "mode": "REQUIRED",
        "description": "The record ID"
      },
      {
        "name": "created_on",
        "type": "DATETIME",
        "mode": "REQUIRED",
        "description": "The date & time when the record was created"
      },
      {
        "name": "is_public",
        "type": "BOOLEAN",
        "mode": "NULLABLE",
        "description": "Set to true if the record is public"
      }
    ]
  }
}

Tabledata: Insert All

Requests must match the structure outlined here. Properties that are marked as ”[Output-only]” do not need to be included in the request (id, kind, etag, etc.) An example request is shown below:

{
  "rows": [
    {
      "json": {
        "id": 1,
        "created_on": "2018-01-17 15:36:22",
        "is_public": true
      }
    },
    {
      "json": {
        "id": {{data.recordId}},
        "created_on": "{{format data.recordCreatedOn 'YYYY-MM-DD HH:mm:ss'}}",
        "is_public": {{data.recordIsPublic}}
      }
    }
  ]
}

Note that the format of each key’s value under “json” depends on the field type - in our example above, we’re inserting data into the example table shown in the previous section, so id and is_public are not quoted because their types are INTEGER and BOOLEAN, respectively, while created_on is quoted.