Google BigQuery

Google BigQuery is a cloud-based data warehouse and analytics platform provided by Google Cloud. It uses a columnar storage format, which allows for fast query performance and efficient data compression, making it well-suited for handling large-scale analytical workloads on vast amounts of data.

Prerequisites

Authorize the Connection to Google BigQuery

In Google Cloud Console

Dataddo supports both authorization via standard User account and Service account. Each option has its pros and cons.

In general, for larger and more complex deployments we generally recommend using a Service Account.

Create an User Account for Authorization

1. In your Google Cloud Console, select your project and navigate to IAM & Admin and IAM.
2. Click on + Add to add a new user and set the user's email address.
3. Assign the following roles to the user:
   1. BigQuery Data Editor
   2. BigQuery Job User
   3. BigQuery Read Sessions
4. Save your configuration.

Create a Service Account for Authorization

1. In your Google Cloud Console, select your project and navigate to IAM & Admin and Service accounts.
2. At the top of the page click on + Create Service Account.
3. Name your service account and in the Service account permissions (optional) section, assign the following roles:
   1. BigQuery Data Editor
   2. BigQuery Job User
4. Save your Service Account.
5. Click on three dots next to your newly created Service Account, choose Manage keys, then Add key.
6. Choose JSON as key type. This will download the JSON file to your computer which you will later need to upload to Dataddo.

In Dataddo

Authorization via User Account

Authorization via standard User Account is the default option and will follow the standard OAuth 2.0 mechanism.

In the Authorizers tab, click on Authorize New Service and select Google BigQuery. Follow the prompts to finish the authorization process.

Authorization via Service Account

Make sure you have created a Service Account for authorization and have downloaded the key in JSON.

1. On the Security page, navigate to the Certificates tab and click on Add Certificate.
2. Name your certificate, select Google Service Account Key as certificate type and upload the file you have obtained when creating a Service Account for authorization.
3. In the Authorizers tab, click on Authorize New Service and select Google BigQuery (Service account).
4. Select the newly added certificate.
5. Click on Save.

Create a New BigQuery Destination

Create a Flow to BigQuery

Table Naming Convention

When naming your table, please make sure the table name does NOT contain:

• Whitespaces
• Dashes

table_{{1d1|Ymd}}

Troubleshooting

Invalid Grant Error

This issue may be caused by:

1. Revoked or expired token: Please reauthorize your account.
2. Insufficient permissions: Make sure that both BigQuery Data Editor and BigQuery Job User roles are assigned to the User account or Service account that you are using.

Invalid Configuration Error

This issue is caused by insufficient row-level uniqueness of you composite key. To solve the issue, please follow these steps:

1. In the Flows tab, click on your flow.
2. Edit the composite key to include more columns for distinct row identification when using upsert or delete write modes.

Failed to Prepare Table Error

This issue may be caused by:

1. Table name already in use: Please edit the flow and choose a different table name.
2. Insufficient permissions: Your BigQuery account does not have the sufficient permissions. Make sure that both BigQuery Data Editor and BigQuery Job User roles are assigned to your User account or Service account.

Failed to Convert Value GBQ Data Type Error

This issue is caused by data type mismatch between the table in BigQuery and sources attached to the flow. To solve the issue, please follow these steps:

1. Check the error message to identify which column is affected and change the data type.
2. Manually load data in the source.
3. Restart the flow.

Column Not Found in Schema Error

This issue may be caused by:

1. Updated schema due to a column in the source being renamed:
   • Dataddo generates flow schemas based on the attached sources. If you changed a column name in the source connected to the flow, it will result in a mismatch between the expected target table schema and the current one.
   • Either revert the column name in the source to its previous value or adjust the column name in the target table to match the new name.
2. Changed source within flow that lead to a schema discrepancy: For the sake of data integrity, changes in the source schema are not passed down to downstream database destinations. This means that table schemas won't automatically update without your explicit consent.
   1. If you can delete data in the BigQuery table: Delete the table in its entirety and reset the flow. Upon the next execution, Dataddo will attempt to establish a new table.
   2. If you cannot delete data in the BigQuery table: Consider manually adding any absent columns directly in Google BigQuery.