Google BigQuery Integration

Integrate Toolio with your Google BigQuery data warehouse

Updated over a week ago

Toolio can integrate directly with your Google BigQuery dataset for Imports and Exports. Please see below for the details to facilitate BigQuery integration.

Importing Data into Toolio

Table Naming Conventions

You will need to set up a table in your BigQuery dataset for each Toolio core model. Below are the required table names:

Data Model

Table Name

Product

toolio_products

Inventory

toolio_inventory

Sale

toolio_sales

Purchase Order

toolio_purchase_orders

Receipt

toolio_receipt

Required Table Schema

You can think of each column on your table corresponding to an attribute on Toolio’s data models. Column names should be the same as the slugs. Please see core model documentation for the column types you should use.

Creating a Service Account for Toolio and Sharing Access

Toolio will require the following credentials in order to integrate with your BigQuery project:

  • Dataset Id (Dataset name)

  • From JSON KEY:

    • Account Type

    • Project Id

    • Private Key Id

    • Private Key

    • Client Email

    • Client Id

    • Auth URI

    • Token URI

    • Auth Provider x509 Cert URL

    • Client x509 Cert URL

In order to capture these credentials, you will need to create a Service Account for Toolio. Please see steps below:

  1. Navigate to IAM & Admin > Service Accounts in your Google Cloud Platform

  2. Click Create Service Account

  3. Enter Service Account details:

    1. Service account name: Toolio

    2. Service account ID: Toolio

  4. Grant Access to Project as BigQuery Admin

  5. Once the Toolio service account is created, click on the Actions menu (3 dots) and select Manage Keys

  6. Click the ADD KEY menu and select Create new Key

  7. Create the private key for the Toolio service account as a JSON file.

All of the credentials above except Dataset Id will be stored in the JSON key created for the Toolio Integration Service Account. You can find the Dataset Id in your BigQuery workspace directly.

Please follow instructions outlined here to share your integration credentials securely with the Toolio team.

Pagination & UpdatedAt Field

If possible, make sure to add an updatedAt field to each of the tables that you create. This helps Toolio keep track of the last value that has been synced from your Data Warehouse. To learn more please see how Toolio handles pagination here.

Exporting Plan values from Toolio into BigQuery

In order to send plan data from Toolio > BigQuery, you'll need to export the data first to either Microsoft Azure or Google Cloud Storage. Please find detailed instructions on each export destination detailed in the linked articles.

Depending on your selection above, you can set up a Scheduled Transfer from either:

Please follow the steps linked above accordingly. Once configured, Toolio will export plan values directly into Azure or GCS. Your Scheduled Transfer will then pick up the files and make them available for your analysis and use in BigQuery.

Related Articles

Did this answer your question?