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
Within the Google Cloud Platform
Schedule your Import, Snapshots, and Add Automations
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.
2. Click "CREATE SERVICE ACCOUNT".
3. Provide a Service Account Name, Service Account ID, and Service Account Description (optional) for this new Service Account.
4. Grant this Service Account access to the project. Coefficient supports only the "BigQuery Admin" role at this time. Select "Done" when finished.
5. Click on the "Keys" tab and "Create a new key" for this Service Account.
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.
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. |
2. Select “Connected Sources”. |
3. Select “Add Connection” at the bottom and then “Connect” to BigQuery. |
|
4. Enter your GCP Project ID (Google Cloud Platform Project ID), Service Account Email, and JSON key. Click Connect. |
5. (Optional) You can share your connection with your team members. |
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…”. |
2. Select “BigQuery” from the list. |
3. Select “From Tables & Columns”. |
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”)
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.
6. Congratulations on your successful BigQuery import using Tables & Columns! 🎉
Custom SQL Query
|
1. Open the Sidebar and click on the “Import from…”. |
2. Select “BigQuery” from the list. |
3. Select "Custom SQL Query". |
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.ℹ️ 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 Sheets/Excel when imported. (You can always change the name later if needed).6. Congratulations on your successful BigQuery Custom SQL import with Coefficient! 🎉
Schedule your Import, Snapshots, and Add Automations
Once you have pulled your data into your spreadsheet using Coefficient, you can set up the following:
|
|
|
|
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.