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:
Navigate to IAM & Admin > Service Accounts in your Google Cloud Platform
Click Create Service Account
Enter Service Account details:
Service account name: Toolio
Service account ID: Toolio
Grant Access to Project as BigQuery Admin
Once the Toolio service account is created, click on the Actions menu (3 dots) and select Manage Keys
Click the ADD KEY menu and select Create new Key
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.