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 AAC Files and then write to BigQuery in a Designer Cloud workflow.

  • Read from BigQuery and then write to AAC 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 only works with Alteryx Photon, Alteryx AMP, and BigQuery Engines.

  • Support for reading from CSV files and writing to BigQuery.

  • Support for reading from BigQuery and writing to CSV files.

  • Support for reading from BigQuery and writing back to BigQuery.

  • Limited scope with BigQuery as an execution engine. BigQuery Pushdown engine only works if:

    • The workspace's Private Date Storage is provisioned as Google Cloud Storage. For more information, go to GCS as Private Data Storage.

    • All inputs in the workflow are from BigQuery and all outputs in the workflow are to BigQuery.

    • The workflow uses the same BigQuery connection (same Google Project ID) for all inputs and outputs.

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

Google BigQuery Setup Guide

Establish a secure connection between AAC and Google BigQuery.

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 AAC workspace.

    3. Choose a role for the service account. For example Project > Editor or specific API roles depending on your needs. Note that AAC 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.

Caution

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

Step 2: Create BigQuery Connection in AAC

  1. Sign in to your AAC workspace.

  2. Go to the Connections Pageand 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 you copied previously in Step 2.

  7. Under API Key, copy and paste the entire JSON key you created previously in Step 2.

  8. Select Test Connection.

  9. If the connection is successful, select Create to establish the 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 AAC 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 Input Data Tool and Output Data Tool in your Designer Experience workflows.

In this section: