BigQuery

Section header image mobile

Discover the limitless possibilities of data analytics as Coefficient seamlessly integrates with BigQuery. Unleash the combined power of these two dynamic platforms to elevate your data-driven decision-making. Dive into our insights to learn how this integration enhances analytics for businesses of all sizes, providing unparalleled efficiency and actionable insights for informed strategies

Setting up a Service Account

Within the Google Cloud Platform

Connecting to BigQuery

Import from BigQuery

Import from Tables

Custom SQL Query

Schedule your Import, Snapshots, and Add Automations

FAQs with BigQuery

Setting up a Service Account

Before setting up a connection between Coefficient and BigQuery, a Service Account needs to be created. There are a couple of ways to set up this account, please see the options below to determine which method will work best for you and your team. 

Using Google Cloud Platform

There are a few steps you need to complete in order to set up the necessary Service Account on the Google Cloud Platform.

1. Go to the Google Cloud Platform's homepage, then navigate to IAM & Admin and select Service Accounts.

Screenshot 2024-01-20 at 10.29.52 AM.png

2. Click "CREATE SERVICE ACCOUNT". 

sdgvag.png

3. Provide a Service Account Name, Service Account ID, and Service Account Description (optional) for this new Service Account.

Screenshot 2024-01-20 at 10.30.20 AM.png

4. Grant this Service Account access to the project. Coefficient supports only the "BigQuery Admin" role at this time. Select "Done" when finished.

fef.png

5. Click on the "Keys" tab and "Create a new key" for this Service Account.

fhgfthd.png

6. A window will appear for this new PRIVATE key, select "JSON" for the Key type, and click "Create" This will save the JSON key to your computer. dethdth.png

Connecting to BigQuery

When you begin a BigQuery import for the first time, you will need to go through a few steps to connect to your BigQuery Project to Coefficient.

1. Open the Coefficient Sidebar and click the Menu.

Menu_Icon (1).png

2. Select “Connected Sources”. 

Screenshot 2023-10-10 at 5.18.29 AM.png

3. Select “Add Connection” at the bottom and then “Connect” to BigQuery.

Screenshot 2023-11-18 at 6.09.39 AM.png

4. Enter your GCP Project ID (Google Cloud Platform Project ID), Service Account Email, and JSON key. Click Connect.

Screenshot 2024-02-27 at 2.22.00 PM.png

5. (Optional) You can share your connection with your team members.

Screenshot 2024-01-27 at 7.48.07 AM.png

Import from BigQuery

There are a couple of ways to import data using Coefficient from BigQuery, from Tables & Columns, or from a Custom SQL Query
Importing from Tables and columns allows you to create imports without having to write SQL. Using a Custom SQL Query gives you additional flexibility in the data that you are importing into Coefficient.

Import from Tables

1. Open the Coefficient Sidebar and click on the “Import from…”.

ImportFrom (1).png

2. Select “BigQuery” from the list.

Screenshot 2024-01-27 at 7.28.05 AM.png

3. Select “From Tables & Columns”.

Screenshot 2024-01-27 at 7.28.41 AM.png

4. The Import Preview window opens showing all the table schemas from your BigQuery database. Select the table for your import. (eg. ”angus_test.angus_test”) Screenshot 2024-01-27 at 7.29.38 AM.png

5. Once the table is selected, the fields within that table will appear in a list on the left side of the Import Preview window. Select the fields you want to include in your import by checking/unchecking the corresponding boxes. Customize your import by adding filters, sorts, limits, or even grouping the data into a cloud pivot table. Then "Import" when done.

ℹ️ NOTE: The Import Preview shows only a sample of your data (50 rows). This sample data will be updated if there are any changes to the import's criteria.  sdfgzg.png

6. Congratulations on your successful BigQuery import using Tables & Columns! 🎉Import.png

Custom SQL Query

1. Open the Sidebar and click on the “Import from…”.

ImportFrom (1).png

2. Select “BigQuery” from the list.

Screenshot 2024-01-27 at 7.28.05 AM.png

3. Select "Custom SQL Query".

Screenshot 2024-01-27 at 7.29.04 AM.png

4. The Import Preview window opens allowing you to enter your custom SQL query in the blue text box shown below. For further flexibility, you can use Coefficient’s SQL Parameters feature to point a value to a specific cell/range of cells for your query.Screenshot 2024-01-30 at 10.39.47 PM.pngℹ️  NOTE: Whenever you make changes to your query, the data within the preview window will automatically update. 

5. When you click “Import” you will be prompted to give your import a name. The name MUST be UNIQUE as it will also be the name of the tab in your Google Sheet when imported. (You can always change the name later if needed).Screenshot 2024-01-30 at 10.39.56 PM.png6. Congratulations on your successful BigQuery Custom SQL import with Coefficient! 🎉Screenshot 2024-01-30 at 10.40.13 PM.png

Schedule your Import, Snapshots, and Add Automations

Once you have pulled your data into Sheets using Coefficient, you can set up the following:

Schedule your Import

a.png

Turn on Snapshots

b.png

Set Up Automations

c.png

FAQs with BigQuery

Can I connect multiple BigQuery accounts at the same time?

Yes, using the new method (Connecting using a Service Account), Coefficient supports multiple BigQuery connections. 

If I connected to BigQuery using the old method (OAuth), do I need to switch my connection to using a Service Account?

For users who have existing BigQuery connections that used OAuth, the interface for viewing and re-authenticating is the same, however, if you delete that connection OR if you are creating a new connection we recommend exclusively using the Service Account method. This is a more streamlined and user-friendly experience. This change aims to simplify future interactions while preserving the integrity of existing connections for a seamless transition.

Can I use the GPT SQL Builder with BigQuery?

At this time, our GPT SQL Builder feature is not currently supported with BigQuery. We are looking to build out support for this feature in upcoming roadmaps. If you have any questions or would like to note a request for this feature please contact support (support@coefficient.io) and we will be glad to help.

 

Was this article helpful? 0 out of 0 found this helpful