Google Cloud Database Connectors

Google Cloud Database connectors enable seamless integration with various Google Cloud database services, allowing your workflows to query, retrieve, and process data from different database types.

1.1 GCP MySQL Loader

The GCP MySQL Loader connects to MySQL instances running on Google Cloud Platform, such as Cloud SQL for MySQL. It allows you to execute SQL queries and retrieve structured data from relational databases.

GCP MySQL Loader Interface

Use Cases

Retrieving customer records from operational databases
Extracting product information for e-commerce applications
Accessing transaction data for financial analysis
Integrating with existing MySQL-based applications
Processing structured relational data for reporting

Inputs

Service Account Key File Path: Path to the service account key file (required)
Example: /path/to/service_account_key.json
Instance connection: MySQL connection information (required)
Example: mysql://user:password@hostname:3306/database
Database: MySQL database name (required)
Example: customer_database
Username: Database username (required)
Example: dbuser
Password: Database password (required)
Example: ********
SQL Query: SQL query to execute (required)
Example: SELECT id, name, email FROM customers WHERE signup_date > '2023-01-01'

Outputs

JSON formatted data representing the query results.

Example Output:

[
  {
    "id": 1001,
    "name": "John Doe",
    "email": "john.doe@example.com"
  },
  {
    "id": 1002,
    "name": "Jane Smith",
    "email": "jane.smith@example.com"
  }
]

Implementation Notes

Use prepared statements to prevent SQL injection attacks
Configure connection pooling for efficient database usage
Keep credentials secure by using environment variables or secure vaults
Implement proper error handling for database connection failures

1.2 Google SQL Schema Retriever

The Google SQL Schema Retriever connects to Google Cloud SQL databases and extracts schema information including tables, columns, relationships, and constraints. It enables you to understand and map database structures for data integration, migration, or documentation purposes.

Google SQL Schema Retriever Interface

Use Cases

Database documentation generation
Data modeling and entity relationship mapping
Schema-based data validation rule creation
Database migration planning
Database structure analysis and optimization

Inputs

Service Account Key Path: Path to the service account key file (required)
Example: /path/to/service_account_key.json
Database type: Type of database to connect to (required)
Example: Select a db type (MySQL, PostgreSQL, etc.)
Instance Connection Name: Connection name for the Cloud SQL instance (required)
Example: project-id:region:instance-name
Database User: Username for database authentication (required)
Example: admin
Database Password: Password for database authentication (required)
Example: ********
Database Name: Name of the database to analyze (required)
Example: customer_db
Use Private IP: Whether to connect using private IP (optional)
Example: no

Outputs

Detailed schema information in structured JSON format, including tables, columns, data types, relationships, constraints, and indexes.

Example Output:

{
  "tables": [
    {
      "name": "customers",
      "columns": [
        {
          "name": "id",
          "dataType": "INT",
          "nullable": false,
          "isPrimaryKey": true,
          "autoIncrement": true
        },
        {
          "name": "name",
          "dataType": "VARCHAR(255)",
          "nullable": false
        },
        {
          "name": "email",
          "dataType": "VARCHAR(255)",
          "nullable": false,
          "isUnique": true
        }
      ],
      "indexes": [
        {
          "name": "idx_customer_email",
          "columns": ["email"],
          "isUnique": true
        }
      ]
    },
    {
      "name": "orders",
      "columns": [
        {
          "name": "id",
          "dataType": "INT",
          "nullable": false,
          "isPrimaryKey": true
        },
        {
          "name": "customer_id",
          "dataType": "INT",
          "nullable": false,
          "foreignKey": {
            "table": "customers",
            "column": "id"
          }
        }
      ]
    }
  ]
}

Implementation Notes

Use read-only database accounts with minimum permissions needed
Consider performance implications when retrieving schema from large databases
Schedule schema retrieval during off-peak hours for production databases
Filter to specific tables or schemas of interest for large databases
Use the retrieved schema information to generate documentation or data dictionaries

1.3 GCP Postgres Loader

The GCP Postgres Loader connects to PostgreSQL instances running on Google Cloud Platform, such as Cloud SQL for PostgreSQL. It allows you to execute SQL queries and retrieve structured data from PostgreSQL databases with full support for PostgreSQL-specific features.

GCP Postgres Loader Interface

Use Cases

Retrieving complex structured data with PostgreSQL-specific features
Working with geospatial data using PostGIS extensions
Leveraging PostgreSQL's advanced data types (arrays, JSON, etc.)
Accessing time-series data in specialized PostgreSQL databases
Building analytics pipelines with PostgreSQL's analytical capabilities

Inputs

Service Account Key File Path: Path to the service account key file (required)
Example: /path/to/service_account_key.json
Project ID: Google Cloud project identifier (required)
Example: my-project-id
Region: Cloud SQL instance region (required)
Example: us-central1
Instance: PostgreSQL instance name (required)
Example: postgres-instance-1
Database: PostgreSQL database name (required)
Example: application_db
Username: Database username (required)
Example: postgres
Password: Database password (required)
Example: ********
SQL Query: SQL query to execute (required)
Example: SELECT id, data->'profile'->>'name' as name FROM users WHERE data->'settings'->>'active' = 'true'

Outputs

JSON formatted data representing the query results, with complex PostgreSQL data types appropriately converted.

Example Output:

[
  {
    "id": 101,
    "name": "Alex Thompson",
    "tags": ["premium", "early-adopter"],
    "metadata": {
      "last_login": "2023-06-12T15:30:45Z",
      "preferences": {
        "theme": "dark",
        "notifications": true
      }
    },
    "location": {
      "type": "Point",
      "coordinates": [-122.4194, 37.7749]
    }
  },
  {
    "id": 102,
    "name": "Jamie Lee",
    "tags": ["standard", "referral"],
    "metadata": {
      "last_login": "2023-06-10T09:15:22Z",
      "preferences": {
        "theme": "light",
        "notifications": false
      }
    },
    "location": {
      "type": "Point",
      "coordinates": [-74.0060, 40.7128]
    }
  }
]

Implementation Notes

Use parameterized queries to prevent SQL injection
Take advantage of PostgreSQL's JSON operations for complex data handling
Configure appropriate connection pooling for optimal performance
Set reasonable query timeouts to prevent long-running operations
Use PostgreSQL-specific features like LISTEN/NOTIFY for event-driven architectures

1.4 GCP MS SQL Loader

The GCP MS SQL Loader connects to Microsoft SQL Server instances running on Google Cloud Platform. It allows you to execute SQL queries and retrieve data from SQL Server databases.

GCP MS SQL Loader Interface

Use Cases

Migrating data from legacy SQL Server systems
Integrating with Microsoft-based enterprise systems
Accessing business data stored in SQL Server databases
Building cross-platform data pipelines
Supporting hybrid cloud environments

Inputs

Service Account Key File Path: Path to service account key file (required)
Example: /path/to/service_account_key.json
Instance connection: SQL Server connection string details (required)
Example: sqlserver://hostname:1433
Database: SQL Server database name (required)
Example: CustomerDB
Username: Database username (required)
Example: sqluser
Password: Database password (required)
Example: ********
SQL Query: SQL query to execute (required)
Example: SELECT CustomerID, Name, Email FROM Customers WHERE Region = 'West'

Outputs

JSON formatted data from the SQL query results.

Example Output:

[
  {
    "CustomerID": "CUST001",
    "Name": "Acme Corporation",
    "Email": "contact@acme.com"
  },
  {
    "CustomerID": "CUST002",
    "Name": "TechSolutions Inc",
    "Email": "info@techsolutions.com"
  }
]

Implementation Notes

Use parameterized queries to prevent SQL injection
Configure proper connection pooling for efficiency
Set appropriate timeout values for long-running queries
Implement proper error handling for connectivity issues

Security Considerations

Store credentials securely using environment variables or secret managers
Use service accounts with minimum required permissions
Encrypt sensitive data in transit and at rest
Regularly rotate service account keys and credentials
Implement IP allowlisting when possible for database access

Performance Best Practices

Optimize SQL queries to retrieve only necessary data
Use appropriate indexes in database tables
Implement caching strategies for frequently accessed data
Monitor query performance and resource usage
Consider data partitioning for large datasets

Documentation

Google Cloud Database Connectors

1.1 GCP MySQL Loader

Use Cases

Inputs

Outputs

Implementation Notes

1.2 Google SQL Schema Retriever

Use Cases

Inputs

Outputs

Implementation Notes

1.3 GCP Postgres Loader

Use Cases

Inputs

Outputs

Implementation Notes

1.4 GCP MS SQL Loader

Use Cases

Inputs

Outputs

Implementation Notes