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.
Authorize Connection to Google BigQuery
Prerequisites
- A running Google BigQuery instance reachable by Dataddo. If you use a firewall, allow the Dataddo IPs.
- A database user with rights to create tables and to insert, update, and delete rows in the target schema.
Create the authorizer
In Authorizers, click Authorize New Service and select Google BigQuery, then fill in the fields:
Google BigQuery (OAuth)
To authorize this service, use OAuth 2.0 to share specific data with Dataddo while keeping usernames, passwords, and other information private.
- On the Authorizers page, click on Authorize New Service and select your service.
- Follow the on-screen prompts to grant Dataddo the necessary permissions to access and retrieve your data.
- [Optional] Once your authorizer is created, click on it to change the label for easier identification.
Ensure that the account you're granting access to holds at least admin-level permissions. If necessary, assign a team member with the required permissions with the authorizer role to authenticate the service for you.
Google BigQuery service account
| Field | Description |
|---|---|
| A name for this authorizer in Dataddo | A label so you can recognize the connection later. |
| Configuration file | Upload the JSON configuration file, which you have downloaded from your Google Cloud account, to Dataddo. Navigate to Certificates & Tokens to complete this step. |
Click Save. Dataddo validates the connection.
Create a Google BigQuery Destination
Go to Destinations, click Create Destination, and select Google BigQuery. Give the destination a name, choose the authorizer, then set:
| Field | Description |
|---|---|
| Project | Select the BigQuery project you would like to load data to. Shown when oAuthId is ``. |
| Dataset | Select the dataset within the chosen project that you would like to load data to. Shown when projectId is ``. |
Click Save.
Write Modes
The write mode decides how each flow run changes the target table. Google BigQuery offers the standard set below, and the default is insert. Which modes are available, and whether you can switch modes on an existing flow, can depend on the table's current state.
| Mode | Meaning | Composite Key |
|---|---|---|
insert |
Appends new rows on every run. | No |
insert_ignore |
Appends rows, skipping rows that already exist. | Yes |
truncate_insert |
Empties the table, then writes the current data. | No |
upsert |
Inserts new rows and updates existing rows matched by the Composite Key. | Yes |
update |
Updates existing rows matched by the Composite Key; fails rows with no match. | Yes |
update_ignore |
Updates matched rows, skipping rows with no match. | Yes |
delete |
Deletes rows matched by the Composite Key. | Yes |
Composite Key
The Composite Key is the set of columns whose combined values identify each row. Every mode except insert and truncate_insert needs one. You can select up to 4 columns.
Table Naming
Dataddo creates the table on the first write if it does not exist. When you name the table:
- Table name: database table name should not contain whitespaces or dashes.
- You can put date-range patterns such as
{{1d1}}in the table name.
How to Create a Flow to Google BigQuery
- Go to Flows and click Create Flow.
- Add one or more sources.
- Add Google BigQuery as the destination and pick the authorizer.
- Choose a write mode, and a Composite Key for modes that need one.
- Set the schema and the table name.
- Set the schedule and click Save.
Troubleshooting
Invalid schema or table name
The schema or table name does not match Google BigQuery's naming rules. Fix the name in the flow to match the rules in Table Naming, then restart the flow.
Insufficient privileges
The database user cannot create tables or write rows in the target schema. Grant the user create, insert, update, and delete rights, then restart the flow.
Cannot connect to Google BigQuery
Dataddo cannot reach Google BigQuery. Check the credentials in the authorizer, and make sure the server is reachable by Dataddo. If you use a firewall, allow the Dataddo IPs.