Introduction
In this article, we're going to show you how to connect your Astrato workspace to a data source in Google BigQuery.
Checklist
Before you can set up your data connection, you'll need the following:
Data connection: project and dataset names.
User account: Enter your username and password when asked. If you are using a Google account, when you log in, Google will prompt you to consent to Astrato accessing your Google Workspace.
Any user who uses the connection needs these permissions in BigQuery:
bigquery.jobs.createThis permission allows creating and running query jobs on the project level. It is included in predefined roles such as BigQuery Job User.bigquery.tables.getData Permission on all tables and views referenced in the query grants read access to the data in those tables and views. It is included in the BigQuery Data Viewer role.
If using a JWT Token, you'll need the token you've created.
How to set necessary permissions in BigQuery
The best way is to use a Google Cloud service account to log in and make a safe connection to BigQuery.
Create a Service Account
In the Google Cloud Console, navigate to the project where your BigQuery instance is located.
Go to IAM & Admin → Service Accounts, then click Create Service Account.
Click the Create and Continue button under the 1-step section to proceed with the service account setup.
Grant the service account the following minimum set of permissions:
BigQuery Connection Service Agent
BigQuery Data Viewer
BigQuery Job User
Click Done to complete the setup.
Creating a data connection to Google BigQuery
Select Data + AI in the Astrato sidebar and go to the Data Connection tab. Click on New connection to open the screen with available data connection providers. Click the Google BigQuery icon to start.
Select one of the available authentication options: OAuth or JWT Token.
OAuth - Allow Astrato permission to access external data
You can use the already defined default OAuth connection or define your own.
After selection, Astrato checks whether you already have an integration with Google BigQuery; if not, it displays a message that permission is required.
Click Confirm to continue.
The Google authentication page appears; select the account you want to use, review the required permissions, and click Continue.
These are the 3 services to which Astrato has access.
If the user account authentication is successful, Astrato displays the message that permissions are granted. Click OK to continue.
OAuth - Enter data connection and project details
Enter the details (project name, dataset name) and click Next. This completes the data connection process.
The only thing to establish before you go to The Semantic Layer Editor in Astrato is accept or modify access to the created data connection for Astrato users and /or groups.
How create a JWT (a Service Account Key)
With the JWT authentication method, a Google Cloud service account is used to authenticate and establish a secure connection to BigQuery.
The JWT method is recommended for server-to-server integrations, as it avoids manual user authentication and ensures reliable, automated access to your BigQuery datasets.
The service account key will be used by Astrato to establish the connection with Google BigQuery.
In the Google Cloud Console, go to IAM & Admin → Service Accounts.
Select the service account created in the previous step.
Navigate to the Keys tab, then click Add Key → Create New Key. Ensure the Key type is set to JSON.
The key file will be generated and automatically downloaded to your computer. Store this file securely, as it will be required for the Astrato connection
JWT - Enter data connection details
Once you've selected the data connection, slide the toggle to the right to select JWT Token.
Enter the details (JSON key, dataset) and click Next.
Set the data connection name, then click Next.
The only thing to establish before you go to The Semantic Layer Editor in Astrato is accept or modify access to the created data connection for Astrato users and /or groups.
JWT- Dynamic Data Connection
A dynamic data connection allows users to work on different projects using a single data model. This is especially useful when combined with embedded analytics for OEM solutions.
Setting up a dynamic data connection is similar to setting up a JWT connection.
Follow these steps:
Create a new BigQuery connection (check the use dynamic connection checkbox)
Paste the JWT key
Select the default project (this will be the default project to build the data model and define measures and dimensions)
Select the dataset to use. This part is not dynamic, and all users will use the same dataset.
Click next
Add more users (who must be part of the Astrato tenant) to the connection settings. Define the authentication key and the project to use for each user's connection.
Click next
Give the data connection a name.
Define who has the right to use the connection.
10. Click connect
Known Limitation
Only users who use the same JWT key and project as the defaults will be able to edit and see the data in the Semantic Layer Editor
The dynamic data connection is only used in published workbooks.
Users who aren't defined in the dynamic connection table won't be able to see data in the workbook.
Next steps > Using the Semantic Layer Editor ...