Foreign Tables

Efficiently manage foreign tables in DataLake using S3 and Object Storage. Enhance data integration and access with our comprehensive guide.

Foreign tables in Tacnode provide a powerful mechanism for accessing and querying external data sources as if they were native database tables. This feature enables seamless data integration without the need to physically import or migrate data, offering flexibility, efficiency, and real-time access to distributed data sources.

What Are Foreign Tables?

Foreign tables are virtual table definitions that reference data stored outside the Tacnode database. They act as a bridge between your Tacnode instance and external data sources, allowing you to query remote data using standard SQL syntax while maintaining the data in its original location.

Key Benefits

Strategic Use Cases

1. Data Integration and Migration

Foreign tables excel at connecting disparate data sources and facilitating data movement without complex ETL processes.

Common Applications:

• Legacy System Integration: Access data from older systems without complex migration projects
• Multi-Database Queries: Join data across different database instances or vendors
• Gradual Migration: Migrate data incrementally while maintaining access to source systems
• Real-time Data Federation: Create unified views across multiple operational systems

Example Scenario:

-- Access customer data from legacy system while querying local orders
SELECT 
    c.customer_name,
    c.customer_segment,
    COUNT(o.order_id) as total_orders,
    SUM(o.order_value) as total_spent
FROM legacy_customers c  -- Foreign table to legacy CRM
JOIN local_orders o ON c.customer_id = o.customer_id
WHERE c.created_date >= '2023-01-01'
GROUP BY c.customer_name, c.customer_segment;

2. Big Data Analytics and Processing

Connect to large-scale data storage systems for advanced analytics without moving massive datasets.

Key Advantages:

• Scalable Storage: Leverage object storage for cost-effective big data storage
• Compute Separation: Separate compute resources from storage for optimal resource allocation
• Query Optimization: Use Tacnode’s query engine to optimize access to distributed data
• Format Flexibility: Support multiple data formats (Parquet, ORC, JSON, CSV) in external storage

Implementation Example:

-- Foreign table pointing to partitioned data in S3
CREATE FOREIGN TABLE sales_data_s3 (
    transaction_id BIGINT,
    customer_id INTEGER,
    product_id INTEGER,
    sale_date DATE,
    amount DECIMAL(10,2),
    region VARCHAR(50)
)
SERVER s3_server
OPTIONS (
    location 's3://mybucket/sales-data/',
    format 'parquet',
    partition_by 'region,sale_date'
);

-- Efficient queries with partition pruning
SELECT region, SUM(amount) as total_sales
FROM sales_data_s3
WHERE sale_date BETWEEN '2024-01-01' AND '2024-03-31'
AND region IN ('US-WEST', 'US-EAST')
GROUP BY region;

3. Data Sharing and Collaboration

Enable secure, controlled access to shared datasets across teams, departments, or organizations.

Collaborative Benefits:

• Controlled Access: Share specific datasets without exposing entire databases
• Multi-tenant Support: Multiple teams can access shared data with appropriate permissions
• Version Control: Access different versions of datasets through different foreign table definitions
• Audit Trail: Track data access patterns and usage across teams

4. Cloud-Native Data Architectures

Build modern, scalable data architectures leveraging cloud storage and services.

Cloud Integration Patterns:

• Data Lake Integration: Query data lakes built on object storage platforms
• Serverless Analytics: Combine with serverless compute for cost-effective analytics
• Multi-cloud Strategies: Access data across different cloud providers
• Disaster Recovery: Maintain backup data access through foreign tables

Supported Foreign Data Source Types

Tacnode supports integration with various external data sources through specialized foreign data wrappers:

• File-based Data Sources: Connect to object storage systems like Amazon S3, Google Cloud Storage, and other compatible storage services
• AWS Glue Catalog: Integrate with AWS Glue Data Catalog for metadata management and data discovery
• Apache Iceberg: Access Iceberg table format for large-scale analytics workloads
• Delta Lake: Connect to Unity Catalog and Delta Lake tables for data lake house architectures

Getting Started with Foreign Tables

Step 1: Install Required Extensions

Foreign data wrappers are provided through extensions that must be installed before use. Each data source type requires its specific extension:

-- For file-based sources (S3, OSS, etc.)
CREATE EXTENSION IF NOT EXISTS file_fdw;

-- For AWS Glue integration
CREATE EXTENSION IF NOT EXISTS glue_fdw;

-- For Hive metastore compatibility
CREATE EXTENSION IF NOT EXISTS hive_fdw;

-- For MaxCompute integration
CREATE EXTENSION IF NOT EXISTS odps_fdw;

Step 2: Create a Foreign Server

Establish a connection configuration to your external data source:

CREATE SERVER <server_name> FOREIGN DATA WRAPPER <fdw_name>
OPTIONS (
    option1 'value1',
    option2 'value2'
);

Parameters:

• server_name: A unique identifier for your foreign server configuration
• fdw_name: The foreign data wrapper type (file_fdw, glue_fdw, hive_fdw, or odps_fdw)
• OPTIONS: Connection-specific parameters that vary by data source type

Step 3: Create User Mapping

For AWS-backed sources (S3, Glue, Iceberg, Delta Lake), you have two authentication options:

Option A: Static Credentials

CREATE USER MAPPING FOR current_user SERVER your_server
OPTIONS (
    AWS_ACCESS_ID 'AKIA...your-access-key-id',
    AWS_ACCESS_KEY 'your-secret-access-key'
);

Option B: IAM Role Assumption (Recommended for Production)

Tacnode’s IAM principal calls sts:AssumeRole on your behalf — no long-lived credentials are stored in the database. Complete these steps in order:

1. Create an IAM Policy in AWS

1. Log into the AWS IAM Console and select Account settings from the left navigation
2. Under Security Token Service (STS) Endpoints, find the AWS region where your Tacnode cluster runs and toggle it to Active
3. Select Policies from the left navigation, then Create policy
4. Choose JSON for the policy editor and paste the following. Replace your-bucket-name with your S3 bucket name, and your-folder-path with the folder path inside the bucket where your data files live (no leading or trailing slash). For example, if your files are at s3://my-bucket/data/contacts/file.parquet, use data/contacts.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:GetObjectVersion"
            ],
            "Resource": "arn:aws:s3:::your-bucket-name/your-folder-path/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::your-bucket-name"
        }
    ]
}

5. Select Next, enter a policy name (e.g., tacnode_s3_access), and select Create policy

For Glue-backed foreign tables also add glue:GetTable, glue:GetDatabase, and glue:GetPartitions as a third statement.

2. Create an IAM Role with a Placeholder Trust Policy

Tacnode runs in a separate AWS account, so the role must trust an external account. You will use a placeholder for now and replace it with the real Tacnode values in step 5.

1. In IAM, select Roles → Create role
2. Select AWS account as the trusted entity type, then select Another AWS account
3. In the Account ID field, temporarily enter your own AWS account ID
4. Check Require external ID and enter 0000 as a placeholder
5. Select Next
6. In the search box, type the name of the policy you created in step 1 (e.g., tacnode_s3_access), select the checkbox next to it, then select Next
7. On the Name, review, and create page, enter a Role name (e.g., tacnode-s3-role) and an optional description, then select Create role
8. Copy the Role ARN from the role summary page — you will need it in the next step

3. Create the User Mapping

Back in psql, create a user mapping that references the role ARN:

CREATE USER MAPPING FOR current_user SERVER s3_production
OPTIONS (
    aws_role_arn 'arn:aws:iam::123456789012:role/tacnode-s3-role'
);

Per-database-user mappings are also supported — create one USER MAPPING per database user that needs access.

4. Retrieve Tacnode’s IAM Principal

Now that the mapping exists, retrieve Tacnode’s IAM principal ARN and the unique external ID generated for this mapping:

SELECT sysadmin.get_user_mapping_info('s3_production');

The result is a JSON object with two values you will need:

• SYSTEM_AWS_ROLE_ARN — the IAM role ARN Tacnode will use to call sts:AssumeRole on your role
• USER_EXTERNAL_ID — the unique external ID scoped to this server mapping

5. Update the IAM Role Trust Policy

1. Return to the AWS Console and navigate to IAM → Roles
2. Click the name of the role you created in step 2
3. Click the Trust relationships tab
4. Click Edit trust policy and replace the placeholder policy with the values from step 4:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "<SYSTEM_AWS_ROLE_ARN>"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "<USER_EXTERNAL_ID>"
        }
      }
    }
  ]
}

The sts:ExternalId condition prevents the confused deputy problem — it ensures only your Tacnode cluster can use this role even if Tacnode’s IAM principal ARN is known to a third party.

Step 4: Create Foreign Tables

Define the structure of your external data as foreign tables:

CREATE FOREIGN TABLE <table_name> (
    column1 data_type,
    column2 data_type,
    -- Define columns matching your external data structure
)
SERVER <server_name>
OPTIONS (
    location '/path/to/external/data',
    format 'parquet'  -- or csv, json, etc.
);

Step 5: Query Foreign Tables

Once configured, query foreign tables using standard SQL:

-- Basic query
SELECT * FROM my_foreign_table LIMIT 10;

-- Complex analytics query
SELECT 
    region,
    DATE_TRUNC('month', transaction_date) as month,
    COUNT(*) as transaction_count,
    SUM(amount) as total_revenue
FROM sales_foreign_table
WHERE transaction_date >= '2024-01-01'
GROUP BY region, DATE_TRUNC('month', transaction_date)
ORDER BY month, region;

Advanced Operations

Batch Table Creation and Updates

Use IMPORT FOREIGN SCHEMA to automatically create multiple foreign tables from external metadata catalogs:

IMPORT FOREIGN SCHEMA remote_schema_name
    LIMIT TO (table1, table2, table3)  -- Optional: specify specific tables
    FROM SERVER server_name
    INTO local_schema_name
    OPTIONS (
        option1 'value1',
        option2 'value2'
    );

This command is particularly useful for:

• AWS Glue: Import entire databases or selected tables from Glue Data Catalog
• Hive Metastore: Synchronize with existing Hive table definitions
• MaxCompute: Import project schemas and table structures

Performance Optimization

Partition Pruning: Design foreign tables to take advantage of data partitioning:

-- Query with partition pruning
SELECT * FROM partitioned_foreign_table 
WHERE year = 2024 AND month = 3;  -- Only accesses relevant partitions

Projection Pushdown: Select only necessary columns to reduce data transfer:

-- Efficient column selection
SELECT customer_id, amount, transaction_date 
FROM large_foreign_table 
WHERE amount > 1000;

Predicate Pushdown: Apply filters at the source to minimize data movement:

-- Filters applied at source level
SELECT * FROM remote_table 
WHERE region = 'US-WEST' 
AND date >= '2024-01-01';

Important Considerations

Security and Access Control

• Permissions: Ensure Tacnode has appropriate read permissions for external data sources
• Credentials Management: Store access credentials securely and rotate them regularly
• Network Security: Configure network access controls and VPN connections as needed
• Data Privacy: Consider data residency and compliance requirements

Performance Implications

• Network Latency: Query performance depends on network connectivity to external sources
• Data Volume: Large datasets may require optimization strategies like partitioning
• Concurrent Access: Consider impact of multiple simultaneous queries on external systems
• Caching: Implement caching strategies for frequently accessed data

Limitations

• Read-Only Operations: Foreign tables support SELECT operations but not INSERT, UPDATE, or DELETE
• Data Freshness: Data reflects the current state of external sources; changes require re-querying
• Type Mapping: Some complex data types may require special handling or conversion
• Transaction Support: Foreign table operations are not included in local transaction boundaries

Troubleshooting Common Issues

Connection Problems:

-- Test server connectivity
SELECT * FROM information_schema.foreign_servers 
WHERE server_name = 'your_server_name';

Data Type Mismatches:

• Ensure column definitions match external data schema exactly
• Use appropriate type casting for complex types like Parquet structs to JSONB

Performance Issues:

• Implement partition pruning where possible
• Consider materialized views for frequently accessed foreign data
• Monitor query execution plans for optimization opportunities

For detailed configuration instructions for specific data sources, refer to the individual foreign data wrapper documentation linked above.