Google BigQuery Connections
Google BigQuery is a data warehouse that enables scalable data analysis across large amounts of data. Use the Google BigQuery connection on Alteryx Analytics Cloud (AAC) to:
Read from your AACAAC Files and then write to BigQuery in a Designer Cloud workflow.
Read from BigQuery and then write to AACAAC Files in a workflow.
Read from BigQuery and then write back to BigQuery in a workflow.
Use Alteryx Engine to write to a BigQuery table in a workflow.
Use BigQuery Pushdown as a Job Engine Option in a workflow.
Processing is available for pushdown when 100% of the workflow is compatible.
Limitations
BigQuery works with Alteryx Photon, Alteryx AMP, Dataproc and BigQuery Engines.
Support for reading from all supported file types and writing to BigQuery.
Support for reading from BigQuery and writing to all supported files types.
Scenarios to consider with BigQuery as an execution engine:
The workspace’s Private Date Storage is provisioned as Google Cloud Storage. For more information, go to GCS as Private Data Storage.
All inputs and outputs in the workflow should be from BigQuery source or a File storage(eg., SharePoint).
If Billing Project is enabled, only the billing project id is used for the execution otherwise the Data Project Id at input is used for BigQuery execution.
The Google Service Account of the BigQuery connection used in a workflow must be the same as the account provisioned for Google Cloud Storage as Private Data Storage.
Prerequisites
Have administrative access to the target Google Cloud Platform project.
Prerequisites for OAuth 2.0
If you are connecting to your Google BigQuery deployment using OAuth 2.0 authentication, additional configuration is required:
You must enable OAuth 2.0 authentication in AACAAC. For more information, go to Enable OAuth 2.0 Authentication.
OAuth 2.0 requirements:
Create a client app in your Google BigQuery deployment.
Create an OAuth 2.0 client in AACAAC.
For more information, go to OAuth 2.0 for Google BigQuery.
Google BigQuery Setup Guide
Establish a secure connection between AACAAC and Google BigQuery using a Service Account Key or OAuth 2.0.
Service Account Key Authentication
Step 1: Create a Google Service Account Key
Service Account Keys authenticate applications, scripts, or services with Google APIs. To create a key, follow these steps:
Go to the Google Cloud Console and then sign in with your Google account.
If you have an existing project, select the project where you want to create the Service Account Key. If you don't have a project, create a project now.
Note and copy the Project ID. You will use this later in Step 2.
On the left pane, select IAM & Admin and then select Service Accounts.
Select Create Service Account.
Enter Service Account Details:
Enter a name for your service account.
[Optional] Enter a description. For example, the name of your AACAAC workspace.
Choose a role for the service account. For example Project > Editor or specific API roles depending on your needs. Note that AACAAC requires these permissions...
bigquery.datasets.get
bigquery.routines.list
bigquery.tables.updateData
bigquery.datasets.getIamPolicy
bigquery.tables.create
storage.buckets.get
bigquery.jobs.create
bigquery.tables.createSnapshot
storage.buckets.list
bigquery.models.export
bigquery.tables.export
storage.objects.create
bigquery.models.getData
bigquery.tables.get
storage.objects.delete
bigquery.models.getMetadata
bigquery.tables.getData
storage.objects.get
bigquery.models.list
bigquery.tables.getIamPolicy
storage.objects.list
bigquery.routines.get
bigquery.tables.list
Select Continue.
In the Keys section, select Create Key and then select the JSON key type.
Select the JSON key type and then select Create. The private key automatically generates and downloads to your computer. You will use this key later in Step 2.
Attenzione
Keep the JSON key file secure as it provides access to your service account.
Step 2: Create BigQuery Connection in AACAAC
Sign in to your AACAAC workspace.
Go to the Connections Page and then select Create Connection.
Search for
Google BigQuery
. If the workspace admin enabled Billing Project for the workspace, the connection creation experience will be blocked until you configure a billing project in AACP. For more information on configuration, see Billing Project.Enter a unique Connection Name for the connection in your workspace.
[Optional] Enter a Connection Description for your connection.
Enter the ProjectId for the target BiqQuery Warehouse location ID you copied previously in Step 2.
Under the Credential Type dropdown, select Service Account Key.
Under Service Account Key Key, copy and paste the entire JSON key you created previously in Step 2.
Select Test Connection.
If the connection is successful, select Create to establish the Google BigQuery connection.
OAuth 2.0 Authentication
Importante
You must first set up OAuth authentication for Google BigQuery. For more information, go to OAuth 2.0 for Google BigQuery.
Create BigQuery Connection in AACAAC
Sign in to your AACAAC workspace.
Go to the Connections Page and then select Create Connection.
Search for
Google BigQuery
.Enter a unique Connection Name for the connection in your workspace.
[Optional] Enter a Connection Description for your connection.
Enter the ProjectId for the target BiqQuery Warehouse location ID
Under the Credential Type dropdown, select OAuth 2.0.
From the OAuth 2.0 client dropdown, select the client app you created.
Select Authenticate.
If the connection is successful, select Create to establish the Google BigQuery connection.
Browse Datasets from Google BigQuery on AAC
After you set up the Google BiqQuery connection, you can browse and import datasets from the target BiqQuery Warehouse. To browse data, follow these steps:
Sign in to your AACAAC workspace.
Go to the Data page.
Select Import Data. On the left pane, you should see the Connection Name of the BigQuery connection as an Import Data option.
Select the connection to access your data.
You can also access your data with the Strumento Dati di input and Strumento Dati di output in your Designer Experience workflows.