Big Query is a fully managed data warehouse and analytics platform provided by Google Cloud designed to handle and analyze large-scale datasets. Toric users can connect to BigQuery as a source, destination, and storage option within Toric.

With the BigQuery connector, Toric users can:

• Write data to your Big Query data warehouse from Toric.
• Query from your data tables and choose the specific table you want to connect to.
• Clean, process, and transform data into reports and dashboards, or write clean data back to an external database.

Data Access

Don't see the access you are looking for? Contact us here!

Configuration guide

Setup time: 4.5 minutes

Requirements:

• Big Query Subscription
• Web Browser (Safari, Chrome, Edge, Firefox)

1. Create a BigQuery Warehouse

Before you configure Google BigQuery in Toric, you will need to create a BigQuery data warehouse.

1.1 General steps to Create a BigQuery Project

1. Go to the Google Cloud Console

2. Click the project drop-down and select or create the project that you want to use with BigQuery (detailed documentation here).

3. Click the hamburger menu (top left) and select the BigQuery option in the list.

4. Click the Create dataset button (detailed documentation here)

5. In the Create dataset dialog:

5.1 Enter a dataset name.

5.2 Select a data location.

5.3 (Optional) To create a dataset with a specific expiration time, select Custom expiration time and enter a number of days or a date.

5.4 Click Create dataset.

‍

6. (Optional) In the dataset's details page, you can do the following:

6.1 View and modify the dataset's access controls.

6.2 View and modify the dataset's expiration time.

6.3 View and modify the dataset's labels.

‍

7. Open the kebab (three dots) menu on the dataset you created, and click Create a table (detailed documentation here). Use the Schema editor to add fields and select their data type.

That's it! You have successfully set up warehouse(table) in Google BigQuery. For a more detailed set of steps please see the Google support guide here.

1.2 Create a BigQuery Service Account

First you will need to enable the BigQuery Data Transfer API.

‍

1. Open the hamburger menu and select API & Services.

‍

2. Click the ENABLE APIS AND SERVICES, then click Enable.

‍

Once a warehouse is created, and the API services are enabled, you need to generate a private key service account. Please follow the steps to create a Service Account and generate a Private Key.

1. In your BigQuery project, click the hamburger menu and select IAM & Admin.

‍

2. Click the Service accounts tab (detailed documentation here).

‍

3. Click the Create service account button.

4. In the Create service account dialog:

4.1 Enter a name for the service account.

4.2 Select a role for the service account.

4.3 (Optional) Add any additional roles or permissions. The most important in the following list is BigQuery Admin.

4.4 (Optional) Grant users access to this service account.

4.5 Click Create.

5. In the Service account permissions dialog: click Continue.

‍

6. In the Grant users access to this service account dialog:

6.1 (Optional) Select the users or groups that you want to grant access to the service account.

6.2 Click Done.

‍

7. In the Service account details page:

7.1 Click the Create key button.

7.2 In the Create key dialog, select the key type and click Create.

7.3 The private key file will be downloaded to your computer. Save it in a secure location, you will need to copy the Private key later in step 2.

‍

Note: We recommend using a password vault like 1Password to store all your credentials.

‍

2. Configure Big Query in Toric

1. Navigate to the BigQuery Connector

To find connectors in Toric, you must be signed in to your account. On the left side panel of your Workflow, click on "Integrations".

‍2. Select the BigQuery Connector

Under the ‘Databases’ header, navigate to the ‘BigQuery’ thumbnail and click on ‘Setup Integration’.

3. Click on Create a Connection

4. Fill in your credentials

Enter the following information:

1. Configuration Name
2. Private Key Content
3. Datasets Location (optional)

5. Click on Validate Configuration

Now that you have all Configuration details filled in, click on Validate Configuration.

If the information entered is valid, a Validation Successful message will appear.

- Invalid fields will be highlighted in red and a successful validation will show in green.

6. Click “Add Configuration” to finalize your configuration

Integration Capabilities Supported by the Connector

Toric supports BigQuery as a source, destination, and storage option within our workspace. Users can ingest data from BigQuery, send data from Toric, or use Redshift to create a Toric-managed database.

Related articles

https://www.toric.com/support/configure-big-query-connector

Common Issues & Troubleshooting

1. Connecting to BigQuery Database
   • Has Toric IP's been approved by the user? See this article for a list of Toric's IPs
   • Does the machine have a public facing IP?
   • Are the correct ports open?
   • Does the public facing IP route to the proper host?
2. Accessing a BigQuery Database
   • Has the proper correct Database name been provided?
   • Does the account that was setup allow Database access?
   • Is the password correct?
3. 400 error: Does the Data being written exceed BigQuery’s Storage Write API Limit?
   • BigQuery Storage Write API has a 10 MB limit per request. When the overall table exceeds 10 MB, Toric will break it up into batches of less than 10MB to send to BigQuery. However, a single record/row of data cannot exceed 10MB since it cannot be broken up by Toric on the backend. Reducing the number of columns will reduce the size of a single record/row that otherwise may be bigger than 10MB.

Questions?

W‍e're very happy to help answer any questions you may have. Contact support here or send us an email at support@toric.com.