Integrate data from Google BigQuery to Databricks using Matillion

Creating an Identity in Google BigQuery

Google BigQuery does not manage users directly within the database using SQL scripts like traditional RDBMS platforms. Instead, identity and access are controlled through Google Cloud IAM (Identity and Access Management), where you assign roles and permissions to Google identities (such as Google accounts, service accounts, or Google Groups).

Below are steps to grant a user access to your BigQuery project or dataset.

1. Identify the User or Service Account

You will need the email address of the user, Google Group, or service account you wish to grant access to, e.g.,

[email protected]

2. Assigning Roles via Google Cloud Console

1. Open the Google Cloud Console.
2. Navigate to IAM & Admin > IAM.
3. Click Add.
4. Enter the user’s email address.
5. Select a BigQuery role (e.g.,

   BigQuery Data Viewer

   ,

   BigQuery Data Editor

   , or

   BigQuery Admin

   ).
6. Click Save.

Alternatively, to grant dataset-level access:

• Go to BigQuery in the console.
• Find and select your dataset.
• Click SHARE DATASET.
• Add the member’s email and assign a dataset-level permission (e.g.,

  roles/bigquery.dataViewer

  ).

3. Granting Access Using the gcloud CLI

You can also use the Google Cloud CLI to assign roles:

gcloud projects add-iam-policy-binding [PROJECT_ID] \
  --member="user:[email protected]" \
  --role="roles/bigquery.dataEditor"

• Replace

  [PROJECT_ID]

  with your Google Cloud project ID.
• Adjust the role as required.

4. Granting Access to a Specific Dataset Using SQL

Within BigQuery, you can execute SQL to grant dataset-level access to specific users using the

GRANT

GRANT `roles/bigquery.dataEditor`
ON SCHEMA `project_id.dataset_id`
TO "user:[email protected]";

Common BigQuery Roles

• roles/bigquery.dataViewer — Read-only access
• roles/bigquery.dataEditor — Read and write access to data
• roles/bigquery.user — Ability to run queries, create datasets

Additional roles (see documentation for details):
https://cloud.google.com/bigquery/docs/access-control

Important Considerations

• “Users” in BigQuery are Google Cloud identities.
• Role assignments can be given at the project or dataset level.
• There is no in-database CREATE USER statement as in traditional SQL databases; user management is externalized via Google Cloud IAM.

Installing the JDBC Driver

For Matillion Data Productivity Cloud users integrating with Google BigQuery, a JDBC driver is required to enable connectivity between the platform and your BigQuery instance. Due to current licensing or redistribution restrictions, the BigQuery JDBC driver is not bundled with Matillion Data Productivity Cloud. Instead, users must download and upload it manually as an external driver to their Matillion environment.

The following steps outline the process for obtaining and installing the Google BigQuery JDBC driver:

Step 1: Download the BigQuery JDBC Driver

1. Visit the official Google BigQuery JDBC driver download page, provided by Simba Technologies, at:
   https://cloud.google.com/bigquery/providers/simba-drivers/

2. On the download page, identify the download for the Type 4 JDBC driver. Type 4 drivers are pure Java drivers, ideal for cloud-based platforms such as Matillion Data Productivity Cloud.

3. Review the licensing terms to ensure compliance before proceeding with the download.

4. Download the driver package (typically a

   .jar

   file) to a secure and accessible location on your device.

Step 2: Upload the Driver to Matillion Data Productivity Cloud

1. Refer to the Matillion Data Productivity Cloud documentation for instructions on uploading external drivers:
   https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

2. Using the guidance provided:

3. Access the Matillion Agent interface or appropriate configuration area as described.
4. Locate the option to upload a new JDBC driver.
5. Upload the previously downloaded BigQuery JDBC driver

   .jar

   file.

6. Ensure that the driver is enabled and properly registered within the Agent environment.

7. After uploading, refresh or restart any relevant services as per the documentation to ensure the new driver is recognized and ready for use.

Step 3: Connecting to BigQuery

1. Once installed, you can configure and use the new driver to connect to Google BigQuery within your Matillion Data Productivity Cloud workflows.

2. For specifics on how to utilize the driver in database query orchestration, follow Matillion’s usage guide:
   https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

By following these steps, you ensure that Matillion Data Productivity Cloud can establish secured and fully functional JDBC connections to Google BigQuery using your custom-installed JDBC driver.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your Google BigQuery database, you must configure your network settings to allow incoming connections based on your deployment type:

• Full SaaS agent configuration:
  Allow incoming connections from the set of IP addresses used by the Matillion Data Productivity Cloud SaaS agents. The current list of required IP addresses can be found here. You must add these addresses to your allowlist or firewall rule set as needed for BigQuery access.

• Hybrid SaaS deployment:
  Incoming connections should be permitted from the IP addresses that originate within your own Virtual Private Cloud (VPC). This allows the Hybrid SaaS agent to communicate directly with Google BigQuery. If you need to validate your setup, you can use a suite of network access checking utilities available at the Matillion Exchange.

Additionally, if the Google BigQuery database is being accessed via a DNS hostname, ensure that your Full SaaS or Hybrid SaaS agent can successfully resolve the DNS address to connect. Proper DNS resolution is critical for establishing and maintaining connectivity.

Querying Data from a Google BigQuery Database

This guide explains how to query data from a Google BigQuery database, including example SQL queries, considerations for data type compatibility (especially when interacting with Databricks), and best patterns for initial and incremental loading.

Examples of Google BigQuery SQL SELECT Statements

To retrieve data from BigQuery, you typically use standard SQL. Below are example queries, assuming a dataset called

my_dataset

sales_orders

``
-- Select all columns from the table
SELECT *
FROM

-- Select specific columns and apply filters SELECT order_id, customer_id, amount, order_date FROM

project_id.my_dataset.sales_orders

-- Aggregate results SELECT customer_id, SUM(amount) AS total_spent FROM

project_id.my_dataset.sales_orders

Note: Replace

project_id

my_dataset

sales_orders

Datatype Conversion Between Google BigQuery and Databricks

When loading or querying data from BigQuery into Databricks, watch out for data type compatibility. For example:

• BigQuery STRING <--> Databricks STRING
• BigQuery INT64 <--> Databricks LONG
• BigQuery FLOAT64 <--> Databricks DOUBLE
• BigQuery TIMESTAMP <--> Databricks TIMESTAMP
• BigQuery BOOLEAN <--> Databricks BOOLEAN

Opaque or complex datatypes such as BigQuery's

STRUCT

ARRAY

SELECT
    CAST(order_id AS STRING) AS order_id_str,
    SAFE_CAST(amount AS FLOAT64) AS order_amount
FROM `project_id.my_dataset.sales_orders`;

Data Loading Patterns: Initial & Incremental Loads

The recommended pattern for loading data from BigQuery is:

1. Initial Load: One-time full copy (typically used to populate a target system for the first time).
2. Incremental Loads: Ongoing, smaller updates (based on new or changed records).

The Matillion Exchange Article provides detailed guidance on this approach.

Using the Database Query Component

Use the same Database Query component for both load types, but vary your SQL

WHERE

• Initial Load: Run the query without any filters.

SELECT *
    FROM `project_id.my_dataset.sales_orders`;

• Incremental Load: Add a filter using a high water mark (e.g., a last-modified timestamp or incrementing key).

SELECT *
    FROM `project_id.my_dataset.sales_orders`
    WHERE updated_at > '2024-06-01 00:00:00'; -- replace with a dynamic value

The value for the filter (

updated_at > ...

For more information on best practices and advanced strategies, consult Matillion’s incremental load guide.

Data Integration Architecture

Loading data in advance of integration represents a strategic 'divide and conquer' approach, breaking the overall task into two manageable steps: first loading raw data into the Databricks database, and then transforming and integrating it as needed. This separation is a key advantage of the ELT (Extract, Load, Transform) architecture. Data integration inherently involves transforming data to meet analytical or operational requirements, and data transformation pipelines offer an efficient and reliable mechanism to automate these processes. Another significant benefit of the ELT architecture is that both transformation and integration activities take place directly within the target Databricks database environment. This ensures seamless, rapid, and scalable processing of data, allowing organizations to perform transformations on-demand without incurring the costs or administrative overhead associated with additional external data processing infrastructure.