Data Integration: Google BigQuery with Mage
Published: 2023-07-21 19:01:33

Guest blog by [Shashank Mishra](https://www.linkedin.com/in/shashank219/), Data Engineer @ _Expedia_

## TLDR
This article outlines the integration between [Mage](https://www.mage.ai) and [Google BigQuery](https://cloud.google.com/bigquery), a serverless data warehousing service. We’ll discuss the integration process, its benefits, and how it aids businesses in making data-driven decisions.

## Outline
- Introduction to Mage
- Overview of Google BigQuery
- Step by step process to integrate Google BigQuery with Mage
- Conclusion

## Introduction to Mage
In an age where data is the new oil, efficient and reliable data management tools are essential. [Mage](https://www.mage.ai/github) is a platform committed to simplifying data integration and analytics. Designed for seamless data transformation and loading, Mage is transforming how businesses approach data management. Here are its key features:
- _Automated Data Pipeline:_ Mage automates data extraction, transformation, and loading (ETL) processes. It can extract data from multiple sources, transform it to a desirable format, and load it into a data warehouse.
- _Data Connectors:_ Mage offers various data connectors to widely-used data sources like Shopify, Facebook Ads, Google Ads, Google Analytics, etc. This makes it easier to import data from these platforms.
- _Easy Integration:_ Mage provides easy integration with popular data warehouses including Google BigQuery, Amazon Redshift, and Snowflake.
- _Pre-built SQL Models:_ Mage comes with pre-built SQL models for popular e-commerce platforms like Shopify and WooCommerce. These models simplify the process of data analysis.
- _Incremental Loading:_ Mage supports incremental loading, which means only new or updated data is loaded into the data warehouse. This saves storage space and improves efficiency.
- _Data Transformations:_ Mage performs automatic data transformations, converting raw data into a more usable format. This process makes the data ready for analysis and reporting.
- _Scheduled Refresh:_ Data refreshes can be scheduled in Mage, ensuring that the data in the warehouse is always up-to-date.
- _Data Security:_ Mage places a high emphasis on data security, ensuring data privacy and compliance with GDPR and other data protection regulations.

_<center>![Image description](https://miro.medium.com/v2/resize:fit:960/0*95p91uIO4M9HqIxL.gif) (Source: Giphy)_</center>

## Overview of Google BigQuery
Google BigQuery is a highly scalable, serverless data warehouse offered by Google as part of its Google Cloud Platform (GCP). It is designed to streamline and simplify the processing of big data.

- _Serverless Architecture:_ BigQuery operates on a serverless model, which means users don’t need to manage any servers or infrastructure. This means you can focus more on analysis and less on maintenance. It allows you to query massive datasets in seconds and get insights in real-time, without needing to worry about resource provision.
- _Real-Time Analytics:_ BigQuery is engineered for real-time analytics. It allows users to analyze real-time data streams instantly. With its ability to run SQL queries on petabytes of data, it delivers speedy results on real-time data analytics, enabling businesses to make timely decisions.

Google BigQuery, with its serverless architecture and real-time analytics, serves as a robust platform to handle, analyze, and draw insights from massive datasets with ease.

_<center>![Image description](https://miro.medium.com/v2/resize:fit:960/0*DMVvOFKd0P3gRC8Q.gif) (Source: Giphy)_</center>

## Step by step process to migrate Google BigQuery with Mage
Before we begin, we’ll need to create a service account key. Please read [Google Cloud’s documentation](https://cloud.google.com/iam/docs/keys-create-delete) on how to create that.

Once we are finished, following these steps:

1. Create a new pipeline or open an existing pipeline.
2. Expand the left side of the screen to view the file browser.
3. Scroll down and click on a file named **io_config.yaml**
4. Enter the following keys and values under the key named default (we can have multiple profiles, add it under whichever is relevant for us)
5. Note: we only need to add the keys under **GOOGLE_SERVICE_ACC_KEY** or the value for key **GOOGLE_SERVICE_ACC_KEY_FILEPATH** (both are not simultaneously required).

```
version: 0.1.1
default:
  GOOGLE_SERVICE_ACC_KEY:
    type: service_account
    project_id: project-id
    private_key_id: key-id
    private_key:
      "-----BEGIN PRIVATE KEY-----\nyour_private_key\n-----END_PRIVATE_KEY"
    client_email: your_service_account_email
    auth_uri: "https://accounts.google.com/o/oauth2/auth"
    token_uri: "https://accounts.google.com/o/oauth2/token"
    auth_provider_x509_cert_url: "https://www.googleapis.com/oauth2/v1/certs"
    client_x509_cert_url: 
"https://www.googleapis.com/robot/v1/metadata/x509/your_service_account_email"
  GOOGLE_SERVICE_ACC_KEY_FILEPATH: "/path/to/your/service/account/key.json"
```

**Using SQL block**

1. Create a new pipeline or open an existing pipeline.
2. Add a data loader, transformer, or data exporter block.
3. Select **SQL**.
4. Under the **Data provider** dropdown, select **BigQuery**
5. Under the **Profile** dropdown, select **default** (or the profile we added credentials underneath).
6. Next to the **Database** label, enter the database name we want this block to save data to.
7. Next to the **Save to schema** label, enter the schema name we want this block to save data to.
8. Under the **Write policy** dropdown, select **Replace** or **Append** (please see [SQL blocks guide](https://docs.mage.ai/guides/sql-blocks) for more information on write policies).
9. Enter in this test query: **SELECT 1**
10. Run the block.

**Using Python block**
1. Create a new pipeline or open an existing pipeline.
2. Add a data loader, transformer, or data exporter block (the code snippet below is for a data loader).
3. Select **Generic** (no template).
4. Enter this code snippet (note: change the **config_profile** from default if we have a different profile):

```
from mage_ai.data_preparation.repo_manager import get_repo_path
from mage_ai.io.bigquery import BigQuery
from mage_ai.io.config import ConfigFileLoader
from os import path
from pandas import DataFrame
```

```
if 'data_loader' not in globals():
    from mage_ai.data_preparation.decorators import data_loader
```

```
@data_loader
def load_data_from_big_query(**kwargs) -> DataFrame:
    query = 'SELECT 1'
    config_path = path.join(get_repo_path(), 'io_config.yaml')
    config_profile = 'default'
```

```
    return BigQuery.with_config(ConfigFileLoader(config_path, config_profile)).load(query)
```

5. Run the block.

_<center>![Image description](https://miro.medium.com/v2/resize:fit:960/0*j-ErIDx43sZdUfXo.gif) (Source: Giphy)_</center>

## Conclusion
Integrating Mage with Google BigQuery provides your team with a potent combination of automated data pipeline management and robust data warehousing. This partnership not only simplifies data extraction, transformation, and loading but also provides a seamless pathway for data analysis and insight generation. As we’ve demonstrated in this step-by-step guide, the integration process is straightforward, making it an accessible option for businesses of all sizes. By leveraging this integration, you can unlock the full potential of your data, streamline operations, and drive data-informed decisions.

_Link to the original blog: [https://www.mage.ai/blog/data-integration-google-bigquery-with-mage](https://www.mage.ai/blog/data-integration-google-bigquery-with-mage)_