> ## Documentation Index
> Fetch the complete documentation index at: https://www.integrate.io/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# ETL: Google BigQuery Source

> Configure the Google BigQuery source component to read data from BigQuery tables or run queries in your Integrate.io ETL data pipeline.

## Connection Setup

Integrate.io ETL uses highly secured certificate-based authentication for connecting to your Google [BigQuery Dataset](https://www.integrate.io/blog/how-to-offload-data-processing-from-google-bigquery/). You need to provide your own service-account's JSON-key and grant Integrate.io ETL access only to projects and associated datasets that you specifically select. This article discusses providing Integrate.io ETL access on your Google BigQuery dataset and then details creating the BigQuery connection in Integrate.io ETL.

### Project Access

<Steps>
  <Step>
    Access your [Google Cloud Console](https://console.cloud.google.com/#/project).
  </Step>

  <Step>
    Click the project name for which you want to grant Integrate.io ETL access.
  </Step>

  <Step>
    On the sidebar, choose **IAM & Admin -> Service Accounts**
  </Step>

  <Step>
    Click **"+ CREATE SERVICE ACCOUNT"**
  </Step>

  <Step>
    Fill-out service account details and then grant following roles:

    * **BigQuery Data Viewer**
    * **BigQuery Job User**

    <Frame>
      <img src="https://mintcdn.com/integrateio/XpeHmuMzUiEmfOEz/images/connectivity-and-security/image-105.webp?fit=max&auto=format&n=XpeHmuMzUiEmfOEz&q=85&s=a3b8f499adb2abf28285d2c3f1110aab" alt="Granting BigQuery Data Viewer and Job User roles to service account" width="1348" height="1632" data-path="images/connectivity-and-security/image-105.webp" />
    </Frame>
  </Step>
</Steps>

### Dataset Access

<Steps>
  <Step>
    Access your [BigQuery console](https://console.cloud.google.com/bigquery).
  </Step>

  <Step>
    In the BigQuery sidebar, under **Resources**, select your project, then select your dataset, click **SHARE DATASET** and select **Permissions**
  </Step>

  <Step>
    Click **"+ ADD PRINCIPAL"** and then assign following roles:

    * **BigQuery Data Editor**
    * **BigQuery Data Viewer**

    <Frame>
      <img src="https://mintcdn.com/integrateio/XpeHmuMzUiEmfOEz/images/connectivity-and-security/image-106.webp?fit=max&auto=format&n=XpeHmuMzUiEmfOEz&q=85&s=eaedc8d66b3d629070c2bcf2e6aaa7b1" alt="Adding BigQuery Data Editor and Data Viewer roles for dataset access" width="1284" height="1266" data-path="images/connectivity-and-security/image-106.webp" />
    </Frame>
  </Step>
</Steps>

### Create a service-account JSON-key

<Steps>
  <Step>
    Access your [Google Cloud Console](https://console.cloud.google.com/#/project).
  </Step>

  <Step>
    Click the project name for which you want to grant Integrate.io ETL access.
  </Step>

  <Step>
    On the sidebar, choose **IAM & Admin -> Service Accounts**
  </Step>

  <Step>
    Select & click the service account that has been created in previous step, click "**KEYS**" tab on the top and then click "**ADD KEY**" -> "**Create new key**" -> choose "**JSON**" -> click "**CREATE**"
  </Step>

  <Step>
    A JSON-key file will be automatically downloaded to your machine.

    <Frame>
      <img src="https://mintcdn.com/integrateio/XpeHmuMzUiEmfOEz/images/connectivity-and-security/image-107.webp?fit=max&auto=format&n=XpeHmuMzUiEmfOEz&q=85&s=dabd3e6f7bcc440796527beac0bb7fc6" alt="Creating a JSON key for the service account" width="1942" height="1158" data-path="images/connectivity-and-security/image-107.webp" />
    </Frame>
  </Step>
</Steps>

### Create Google BigQuery connection in Integrate.io ETL

<Steps>
  <Step>
    Click the **Connections** icon (lightning bolt) on the top left menu.
  </Step>

  <Step>
    To create a connection, click **New connection**.

    <Frame>
      <img src="https://mintcdn.com/integrateio/XpeHmuMzUiEmfOEz/images/connectivity-and-security/image-108.webp?fit=max&auto=format&n=XpeHmuMzUiEmfOEz&q=85&s=77d2d2362e5020a5b4030053a26a8cdf" alt="Connections page with New connection button" width="1200" height="829" data-path="images/connectivity-and-security/image-108.webp" />
    </Frame>
  </Step>

  <Step>
    Choose **Google BigQuery**.

    <Frame>
      <img src="https://mintcdn.com/integrateio/XpeHmuMzUiEmfOEz/images/connectivity-and-security/image-109.webp?fit=max&auto=format&n=XpeHmuMzUiEmfOEz&q=85&s=7f93017f9ff3f73717fa6fab5aa89c20" alt="Selecting Google BigQuery from the connection type list" width="1200" height="829" data-path="images/connectivity-and-security/image-109.webp" />
    </Frame>
  </Step>

  <Step>
    Name the connection.
  </Step>

  <Step>
    Fill in Dataset ID and select the region where your BigQuery is hosted.
  </Step>

  <Step>
    Upload a JSON-key file that you have created on previous step. Service account email and Project ID will be filled in automatically.
  </Step>

  <Step>
    Click **Test Connection** to make sure the connection details are correct.
  </Step>

  <Step>
    Click **Create connection** to create the connection.

    <Frame>
      <img src="https://mintcdn.com/integrateio/XpeHmuMzUiEmfOEz/images/connectivity-and-security/image-110.webp?fit=max&auto=format&n=XpeHmuMzUiEmfOEz&q=85&s=3d9477b487ab0d2640f88cdad33782d1" alt="BigQuery connection form with Dataset ID, region, and JSON key upload" width="1200" height="829" data-path="images/connectivity-and-security/image-110.webp" />
    </Frame>

    <Frame>
      <img src="https://mintcdn.com/integrateio/XpeHmuMzUiEmfOEz/images/connectivity-and-security/image-111.webp?fit=max&auto=format&n=XpeHmuMzUiEmfOEz&q=85&s=335011588f6ff224ed9ed661ab1b2401" alt="BigQuery connection test successful and Create connection button" width="1200" height="829" data-path="images/connectivity-and-security/image-111.webp" />
    </Frame>
  </Step>
</Steps>

### Migrate from previous version of Google BigQuery connector

Customers who have created previous version of BigQuery connector and want to migrate to the latest version of BigQuery connector, please follow these steps:

<Steps>
  <Step>
    Select package containing BigQuery connector from your dashboard.
  </Step>

  <Step>
    On the package designer page, select BigQuery source and/or destination components that you want to modify.
  </Step>

  <Step>
    Replace previous version of BigQuery with connection that has prefix ID **BIGQUERYV2\_CONNECTION\_**

    <Frame>
      <img src="https://mintcdn.com/integrateio/XpeHmuMzUiEmfOEz/images/connectivity-and-security/image-112.webp?fit=max&auto=format&n=XpeHmuMzUiEmfOEz&q=85&s=9f8adbfe37b413d1b84a0c7a8d85a8ac" alt="Replacing the connection with BIGQUERYV2 prefix for migration" width="1200" height="829" data-path="images/connectivity-and-security/image-112.webp" />
    </Frame>
  </Step>

  <Step>
    Remove Integrate.io ETL's service account email (**[service-account-integrateio-for-xplenty@developer.gserviceaccount.com](mailto:service-account-integrateio-for-xplenty@developer.gserviceaccount.com)**) from your both project's and dataset's permission.

    1. To remove from your project's permissions, access your [Google Cloud Console](https://console.cloud.google.com/#/project) and go to **IAM & Admin -> Service Accounts,** select checkbox on Integrate.io ETL's service account email and click **DELETE**
    2. To remove from your dataset's permissions, access your [BigQuery console](https://console.cloud.google.com/bigquery). In the BigQuery sidebar, under **Resources**, select your project, then select your dataset, click **SHARE DATASET** and select **Permissions.** Remove Integrate.io ETL's service account email from each assigned role by clicking delete icon.
  </Step>
</Steps>

***

<Frame>
  <img src="https://mintcdn.com/integrateio/OwEKdS5aIKsEcmhX/images/creating-packages/using-components-google-bigquery-source/image-1.png?fit=max&auto=format&n=OwEKdS5aIKsEcmhX&q=85&s=d6454e9e3d4e9e0d427aeda3ab748aed" alt="Google BigQuery source component in the pipeline designer" width="1200" height="828" data-path="images/creating-packages/using-components-google-bigquery-source/image-1.png" />
</Frame>

## Connection

Select an existing [Google BigQuery connection](https://www.integrate.io/blog/how-to-offload-data-processing-from-google-bigquery/) or create a new one. For more information, see [Allowing Integrate.io ETL access to my Google BigQuery dataset](/etl/allowing-integrateio-etl-access-to-my-google-bigquery-dataset/ "Link: /etl/allowing-integrateio-etl-access-to-my-google-bigquery-dataset/").

## Source Properties

<Frame>
  <img src="https://mintcdn.com/integrateio/OwEKdS5aIKsEcmhX/images/creating-packages/using-components-google-bigquery-source/image-2.png?fit=max&auto=format&n=OwEKdS5aIKsEcmhX&q=85&s=8559aecb0c6abadc6c5b8fdaf6107ac1" alt="BigQuery source properties with access mode and table selection" width="541" height="400" data-path="images/creating-packages/using-components-google-bigquery-source/image-2.png" />
</Frame>

* **Access mode** - select table to extract an entire table or query to execute a query (access table views).
* **Source table** - the table name from which the data will be imported.
* **Query** - type in a SQL query. Make sure to name all columns uniquely.

## Source Schema

<Frame>
  <img src="https://mintcdn.com/integrateio/OwEKdS5aIKsEcmhX/images/creating-packages/using-components-google-bigquery-source/image-3.png?fit=max&auto=format&n=OwEKdS5aIKsEcmhX&q=85&s=e2678aedc6d786f6d1d9a8ab0fc229b5" alt="BigQuery source schema with field selection and data types" width="1016" height="1010" data-path="images/creating-packages/using-components-google-bigquery-source/image-3.png" />
</Frame>

After defining the source table/query select the fields to use in the source.

With table access mode, the fields you select are used to build the query that will be executed to read the data.

With query access mode, select all the fields that are defined in the query and make sure to use the same column names

Define the data type for the field. Use the following table when matching BigQuery data types to Integrate.io ETL data types.

| Google BigQuery   | Integrate.io ETL |
| :---------------- | :--------------- |
| String            | String           |
| Integer           | Long             |
| Float             | Double           |
| Timestamp         | Datetime         |
| Record            | Map              |
| Record (repeated) | Bag              |

## Related

<CardGroup cols={2}>
  <Card title="Google BigQuery Destination" icon="arrow-right" href="/etl/using-components-google-bigquery-destination" horizontal />

  <Card title="Running Jobs" icon="arrow-right" href="/etl/running-jobs" horizontal />

  <Card title="IP Allowlist" icon="arrow-right" href="/etl/integrateio-etls-ip-list" horizontal />
</CardGroup>