Integrate data from Google AlloyDB to Databricks using Matillion

Creating a User in Google AlloyDB

To manage access control in Google AlloyDB, you create users within the database, similar to PostgreSQL. The following instructions guide you through creating a new user account using SQL scripts.

Prerequisites

• You must have appropriate privileges (typically a user with

  CREATEROLE

  or superuser rights) to create new users.
• Connect to your AlloyDB instance using your preferred PostgreSQL-compatible client (such as

  psql

  , DBeaver, or Cloud Shell).

Step 1: Connect to the Database

Use the

psql

psql -h [ALLOYDB_HOST] -U [ADMIN_USER] -d [DATABASE_NAME]

Replace the placeholders with your AlloyDB instance details.

Step 2: Create the User

Once connected, run the following SQL statement to create a new user. Optionally, you can specify a password.

CREATE USER new_user_name WITH PASSWORD 'strong_password';

• Replace

  new_user_name

  with your desired username.
• Replace

  strong_password

  with a secure password.

Step 3: Grant Privileges (Optional)

By default, the user has no privileges on existing databases or tables. Grant access as required. For example, to grant basic read and write access on all tables in a schema:

GRANT CONNECT ON DATABASE [DATABASE_NAME] TO new_user_name;
GRANT USAGE ON SCHEMA public TO new_user_name;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO new_user_name;

Step 4: Confirm User Creation

To verify the user has been created, you can query the

pg_roles

\du

SELECT rolname FROM pg_roles;

The new user should appear in the list.

Note: For advanced user management—such as specifying roles, login restrictions, or group membership—refer to the PostgreSQL documentation, as AlloyDB is compatible with most PostgreSQL features.

Installing the JDBC Driver

At the time of writing, the JDBC driver required for connecting to Google AlloyDB is not included natively in Matillion Data Productivity Cloud. This is due to licensing or redistribution restrictions, so you will need to download and install it manually.

To complete the installation, follow these steps:

1. Download the JDBC Driver

The Google AlloyDB database is PostgreSQL-compatible, and uses the PostgreSQL JDBC driver (also known as "pgJDBC"). Only the Type 4 JDBC driver (a pure Java implementation) is required and recommended.

• Visit https://jdbc.postgresql.org/ to access the official downloads page.
• Download the latest stable release of the “Type 4” JDBC driver in

  .jar

  format.

2. Upload the Driver to Matillion Data Productivity Cloud

Since the driver is not bundled, you will need to add it as an external driver to your Matillion environment.

• Installation instructions are detailed at: Uploading external drivers to Matillion Data Productivity Cloud
• Follow the documented procedure to upload the downloaded

  .jar

  file to your Matillion agent or platform environment.

3. Configure Usage in Database Query Components

Once the driver installation is complete, you can configure and utilize it within Matillion Data Productivity Cloud to connect and interact with your AlloyDB database.

• Usage instructions are provided here: Database Query component usage
• Ensure you reference the newly uploaded JDBC driver in your Matillion connection configuration.

By following the steps above and consulting the relevant documentation, you can enable Google AlloyDB support within your Matillion Data Productivity Cloud deployments.

Checking network connectivity

To enable connectivity between Matillion Data Productivity Cloud and your Google AlloyDB database, you must ensure that the AlloyDB instance allows incoming network connections from the appropriate sources, based on your deployment configuration:

• Full SaaS agent configuration:
  Allow incoming connections from the IP addresses documented at https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/. These are the public IP addresses used by the Matillion Data Productivity Cloud SaaS agent to access your database.

• Hybrid SaaS deployment:
  Allow incoming connections from your own virtual private cloud (VPC) where the Matillion agent is deployed. If you need to check or troubleshoot network accessibility, you can use the utilities provided at https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

Additionally, if your Google AlloyDB instance is referenced using a DNS hostname, ensure that the Full SaaS or Hybrid SaaS agent can resolve the DNS address. This may require appropriate DNS configuration or resolution from within your VPC or environment.

Querying Data from Google AlloyDB

Google AlloyDB is a fully managed, PostgreSQL-compatible database service. To extract data from AlloyDB, you use PostgreSQL-compatible SQL queries. This guide demonstrates querying AlloyDB data, managing type conversions when integrating with Databricks, and recommended patterns for initial and incremental data loads using ETL tools.

1. Example SQL SELECT Statements

Below are sample queries you can run against a table named

sales_orders

``` -- Select all columns and rows SELECT * FROM sales_orders;

-- Select specific columns SELECT order_id, customer_name, order_total FROM sales_orders;

-- Filter rows by a column value SELECT order_id, order_total FROM sales_orders WHERE order_date >= '2024-01-01';

-- Aggregate sales by customer SELECT customer_id, SUM(order_total) AS total_sales FROM sales_orders GROUP BY customer_id; ```

2. Data Type Conversion: AlloyDB and Databricks

Data types in AlloyDB (PostgreSQL) may not have direct equivalents in Databricks (which uses Spark SQL). Common conversions include:

• timestamp with time zone

  (AlloyDB) →

  timestamp

  (Databricks)

• numeric

  (AlloyDB) →

  decimal

  (Databricks)

• jsonb

  (AlloyDB) →

  string

  (Databricks)
• Arrays (

  text[]

  , etc.) may be handled as delimited strings or arrays in Databricks

Tip: Always review Databricks' mapping for source PostgreSQL/AlloyDB data types when designing ETL pipelines.

3. Initial and Incremental Loads: Best Pattern

The recommended pattern to replicate data from AlloyDB uses an initial full load, followed by incremental loads:

a. Initial Load

• Use the Database Query component (such as in Matillion or another ETL tool) with no filter clause to extract all records.
• Example (full table load):

SELECT * FROM sales_orders;

b. Incremental Load

• For subsequent loads, only fetch data updated or created since the last load. This is done by adding a suitable filter clause using a timestamp or incrementing key.

SELECT * FROM sales_orders
    WHERE updated_at > '${lastSuccessfulLoadTimestamp}';

• Replace

  ${lastSuccessfulLoadTimestamp}

  with the value recorded after your last ETL run.

Note: Use the same Database Query component for both initial and incremental loads, adjusting only the query filter.

Read more on this strategy: Incremental Load Data Replication Strategy (Matillion Exchange)

By following these patterns, you can robustly and efficiently extract data from Google AlloyDB for downstream analytics and machine learning use cases.

Data Integration Architecture

One of the key advantages of the ELT (Extract, Load, Transform) architecture is its approach to dividing the data integration process into discrete, manageable steps. By loading data into the Databricks environment prior to integrating it, organizations can "divide and conquer" complex integration tasks—first ensuring that all relevant data is available, and then focusing on transformation and integration within the platform. Data integration inherently requires transformation, and the most effective way to manage this is through the use of data transformation pipelines, which allow for repeatable, scalable, and automated processing of diverse data sets. An additional benefit of the ELT approach is that all transformation and integration are performed directly within the target Databricks database. This not only maximizes performance by leveraging Databricks's distributed processing capabilities, but also enables on-demand scalability, and alleviates the need for additional data movement or investment in separate data processing infrastructure.