Uncategorized

Eliminating Manual DDLs: Automated Foreign Schema Import in Tanzu Greenplum & PXF

, ,

Enterprises now manage vast amounts of data across relational databases, data warehouses, and cloud object stores with open table formats like Apache Iceberg. To query these disparate systems without costly data migration, modern platforms rely on federated query execution.

VMware Tanzu Greenplum addresses this challenge through its Platform Extension Framework (PXF), which allows high-performance parallel execution over external sources.

Now, with the release of PXF bulk import of foreign tables capabilities, Tanzu Greenplum enables teams to bypass manual DDL configuration to integrate an external database or data lake containing multitudes of tables or the toil of creating and maintaining individual CREATE FOREIGN TABLE statements for every dataset. Native support in Tanzu Greenplum for PostgreSQL’s IMPORT FOREIGN SCHEMA command across Apache Iceberg and JDBC sources enables teams to move faster when querying disparate data sources at scale.

The Problem: High Cost Manual Schema Mapping

Before this update, mapping an external data store into Tanzu Greenplum meant doing the heavy lifting by hand:

  1. Inspect remote catalog/schema definitions manually.
  2. Manually translate remote data types into PostgreSQL/Tanzu Greenplum equivalents.
  3. Write and execute repetitive CREATE FOREIGN TABLE DDLs for every target dataset.
-- Repetitive approach: Write individual DDL statements for each table
CREATE FOREIGN TABLE local_schema.orders (    
order_id INT,    
customer_id INT,    
order_date DATE,    
total_amount NUMERIC(12,2)
) 
SERVER pxf_iceberg_server 
OPTIONS (resource 'sales.orders', format 'parquet');

CREATE FOREIGN TABLE local_schema.customers ( ... );
CREATE FOREIGN TABLE local_schema.line_items ( ... );
-- Repeat for 100+ tables...

For large or evolving schemas, this manual process created significant friction:

  • Time Consumption: Engineers spent hours or days writing and debugging SQL syntax.
  • Type Mismatch Risks: Hand-translating complex or custom data types increased the likelihood of runtime errors.
  • Access Delays: Downstream users had to wait for manual configuration tickets before querying newly ingested tables.

The Solution: Single-Command Schema Ingestion

PXF’s implementation of IMPORT FOREIGN SCHEMA lets engineers map an entire remote database schema or Iceberg namespace into Tanzu Greenplum with a single SQL command.

IMPORT FOREIGN SCHEMA sales FROM SERVER pxf_iceberg_server INTO 
local_schema;

When the command runs, Tanzu Greenplum queries the remote source’s catalog metadata, validates type compatibility, and dynamically creates all corresponding foreign table definitions in the target schema.

Key Features & Capabilities

1. Multi-Source Support (Apache Iceberg & JDBC)

The bulk import engine interfaces directly with modern open table formats and legacy relational engines:

  • Apache Iceberg: Reads Iceberg catalog metadata and converts Iceberg schema definitions and data types directly into Tanzu Greenplum foreign tables.
  • JDBC Connectors: Queries relational platforms (Oracle, PostgreSQL, MySQL) via standard DatabaseMetaData interfaces to extract column attributes and schema layouts.

2. Selective Filtering (LIMIT TO & EXCEPT)

You don’t have to import everything. PXF supports standard ANSI/PostgreSQL schema filtering, so you can keep target schemas clean and skip datasets you don’t need:

Import only specific tables

IMPORT FOREIGN SCHEMA analytics_db
LIMIT TO (orders, line_items, products)
FROM SERVER pxf_iceberg_serverINTO local_lakehouse;

Exclude sensitive or staging tables:

IMPORT FOREIGN SCHEMA public
EXCEPT (user_passwords, audit_logs, temp_staging)
FROM SERVER pxf_postgres_serverINTO local_staging;

3. Explicit Incompatibility Guardrails

External sources often use proprietary or domain-specific data types that don’t map cleanly to PostgreSQL types. PXF gives you two ways to handle them:

  • Default Mode (warn): The default behavior when no explicit option is set. Skips unmappable columns, issues a PostgreSQL WARNING message, and imports the remaining supported fields.
  • Strict Mode (strict ‘true’): Aborts the entire import operation if any unmappable column is detected.
IMPORT FOREIGN SCHEMA finance_namespace
FROM SERVER pxf_jdbc_server
INTO local_financeOPTIONS (strict 'true');

Frictionless Federation at Scale

The introduction of IMPORT FOREIGN SCHEMA in PXF marks a significant step forward for Tanzu Greenplum’s data federation ecosystem. By combining Apache Iceberg lakehouse metadata discovery, JDBC schema reflection, and sub-second DDL generation, Tanzu Greenplum empowers enterprises to query disparate data sources effortlessly and at scale. This automated workflow reduces schema setup times from hours to seconds, eliminates manual scripting errors, lowers total cost of ownership, and accelerates time-to-insight across unified data lakehouse environments.

Related Articles