Knowledge base

Allowing Xplenty access to my Google BigQuery dataset

Xplenty uses highly secured certificate-based authentication for connecting to your Google BigQuery Dataset. You need to grant Xplenty access only to projects and associated datasets that you specifically select. This article discusses providing Xplenty access on your Google BigQuery dataset and then details creating the BigQuery connection in Xplenty.

Project Access

To run Load jobs, grant READ permissions to Xplenty for the relevant BigQuery dataset project container:

  1. Access your Google Cloud Console.
  2. Click the project name for which you want to grant Xplenty access.
  3. On the sidebar, choose IAM & Admin -> IAM
  4. Click +Add.
  5. Paste Xplenty's Google Cloud email under New Members
    869780223921-fkm5hu2ev8la67tqrntm6b5tjm6c6sgl@developer.gserviceaccount.com
    On Select a Role, add BigQuery Data Viewer. 
    undefined
  6. Then click Add Another Role and add BigQuery Job User. undefined
  7. Click Save.

    Dataset Access

    To enable Xplenty to write to a specific dataset, grant WRITE permissions to Xplenty for the relevant BigQuery Dataset:

    1. Access your BigQuery console.
    2. In the BigQuery sidebar, under Resources, select your project, then select your dataset
      Click SHARE DATASET
    3. Paste Xplenty's Google Cloud email under Add members
      869780223921-fkm5hu2ev8la67tqrntm6b5tjm6c6sgl@developer.gserviceaccount.com
      On Select a role, add BigQuery Data Editor
      undefined
    4. Click Add.

    Source Component Access mode permission

    BigQuery source component has two access modes, Table and Query


    For Table access mode, Xplenty connects directly to the dataset and BigQuery Data Viewer role would suffice.
    For Query access mode, Xplenty creates a staging table based on the query and BigQuery Data Editor role is needed.

    For more information, see Google BigQuery Access Control.

    External tables access

    In order to read data from external tables that are based on Google Sheets, share the sheets (or a directory containing them) with the service account 869780223921-fkm5hu2ev8la67tqrntm6b5tjm6c6sgl@developer.gserviceaccount.com.

    To create a Google BigQuery connection in Xplenty

    1. Click the Connections icon (lightning bolt) on the top left menu.
    2. To create a connection, click New connection. Screen+Shot+2020-03-16+at+3.44.54+PM.jpg
    3. Choose Google BigQuery.
    4. Name the connection.
    5. Enter the Project ID and Dataset ID in their respective fields.
    6. Select the region where your BigQuery is hosted.
    7. Click Test Connection to make sure the connection details are correct.
    8. Click Create connection to create the connection.
    9. undefined