BigQuery ETL Integration

Use Singular's BigQuery ETL integration to export your Singular data automatically into a BigQuery data warehouse. If ETL is enabled for your account, you can follow the instructions below to set the integration up.

For general information about Singular's ETL offering, see the Singular ETL FAQ.

Setup Instructions

1. Select a Google Cloud Project

Select or create a Google Cloud project and enable BigQuery API for the project (see Google's  instructions).

Write down the project ID.

mceclip0.png

Note: The instructions below grant Singular access to all datasets in the Google Cloud project. Singular will only use its own dataset, but if you want more controlled access, consider creating a dedicated project for Singular.

2. Grant Access

Singular loads data into BigQuery using Google Cloud Compute Service Accounts. We support two methods.

Option #1: Create a Service Account (Recommended)

  1. In the Google Cloud platform, go to IAM & Admin > Service Accounts and click Create Service Account.
  2. Enter a name, an ID and a description for the service account and click Create.

    mceclip4.png

  3. In the Service Account Permissions window, give the new account the following permissions:
    • BigQuery Data Owner - allows Singular to create and manage the dataset and tables.
    • BigQuery Job User - allows Singular to create load jobs into the dataset.
      mceclip5.png
  4. To create a JSON key for the account, click Create Key, choose a JSON key type, and click Create.

    mceclip6.png

  5. Download the key file and save it in a safe location, you will need to upload it to Singular on step #3.

Option #2: Grant Access to Singular's Pre-made Service Account

  1. In the Google Cloud platform, go to IAM & Admin > IAM and click Add
  2. Enter "singular-etl@singular-etl.iam.gserviceaccount.com" and add the following roles:

    • BigQuery Data Owner - allows Singular to create and manage the dataset and tables.
    • BigQuery Job User - allows Singular to create load jobs into the dataset.

    mceclip1.png

3. Add a BigQuery Data Connector

To add a BigQuery data connector in Singular:

  1. In your Singular account, go to Settings > Data Connectors and click Add New Connector.
  2. Type in "BigQuery Destination".

    mceclip0.png

    1. In the window that opens, fill in the relevant details:

      mceclip0.png

      Field Description
      Service Account Type Choose the appropriate service account type (user created or Singular's pre-made) based on Step #2.
      Credentials File Upload the file you created in Step #2, wouldn't show if you chose to give access to Singular's pre-made Service Account.
      Project ID Enter the Project ID from Step #1.
      Dataset Name Enter a name for the dataset that Singular will write to. Default: "singular". If the dataset does not exist yet, it will be created.
      Table Name Enter a name for the table that Singular will write to. Default: "marketing_data_raw". If the table does not exist yet, it will be created.
      View Name Enter a name for the view Singular will create. Default: "marketing_data".
      Dataset Location Set your BigQuery dataset location (US is BigQuery's default location).
      Data Schema The schema of the data loaded into the destination, see ETL Data Schema.

Note: Singular creates both a table and a view.

  • The table contains the raw data from Singular, partitioned by date. The table may contain multiple copies of the data, distinguished by the "query_timestamp" column.
  • The view is created on top of the raw table. It deduplicates the data and filters for the most up-to-date information.

You should typically query the view, not the table.

Was this article helpful?
0 out of 0 found this helpful