Skip to main content

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:

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:

  1. Go to the Google Cloud Console and then sign in with your Google account.

  2. 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.

  3. Note and copy the Project ID. You will use this later in Step 2.

  4. On the left pane, select IAM & Admin and then select Service Accounts.

  5. Select Create Service Account.

  6. Enter Service Account Details:

    1. Enter a name for your service account.

    2. [Optional] Enter a description. For example, the name of your AACAAC workspace.

    3. 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

       

    4. Select Continue.

  7. In the Keys section, select Create Key and then select the JSON key type.

  8. 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.

Attention

Keep the JSON key file secure as it provides access to your service account.

Step 2: Create BigQuery Connection in AACAAC

  1. Sign in to your AACAAC workspace.

  2. Go to the Connections Page and then select Create Connection.

  3. 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.

  4. Enter a unique Connection Name for the connection in your workspace.

  5. [Optional] Enter a Connection Description for your connection.

  6. Enter the ProjectId for the target BiqQuery Warehouse location ID you copied previously in Step 2.

  7. Under the Credential Type dropdown, select Service Account Key.

  8. Under Service Account Key Key, copy and paste the entire JSON key you created previously in Step 2.

  9. Select Test Connection.

  10. If the connection is successful, select Create to establish the Google BigQuery connection.

OAuth 2.0 Authentication

Important

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

  1. Sign in to your AACAAC workspace.

  2. Go to the Connections Page and then select Create Connection.

  3. Search for Google BigQuery.

  4. Enter a unique Connection Name for the connection in your workspace.

  5. [Optional] Enter a Connection Description for your connection.

  6. Enter the ProjectId for the target BiqQuery Warehouse location ID

  7. Under the Credential Type dropdown, select OAuth 2.0.

  8. From the OAuth 2.0 client dropdown, select the client app you created.

  9. Select Authenticate.

  10. 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:

  1. Sign in to your AACAAC workspace.

  2. Go to the Data page.

  3. Select Import Data. On the left pane, you should see the Connection Name of the BigQuery connection as an Import Data option.

  4. Select the connection to access your data.

You can also access your data with the Outil Entrée de données and Outil Sortie de données in your Designer Experience workflows.

Dans cette section​: