Docs›Guides›Table›Foreign Table›Foreign Table File

File-based Foreign Table

Learn how to efficiently manage foreign tables in S3, and Object Store with our comprehensive guide, enhancing your data integration and access strategies.

File-based foreign tables enable direct access to data stored in cloud object storage systems like AWS S3 and other compatible object stores. This guide covers implementing and optimizing file-based external data integration.

Overview and Benefits

File-based foreign tables provide seamless integration with cloud object storage, enabling:

Direct Data Access: Query files directly without importing
Cost Efficiency: Access data on-demand without storage duplication
Scalability: Handle petabyte-scale datasets
Flexibility: Support multiple file formats and storage providers
Real-time Analytics: Query latest data without ETL delays

Supported File Formats

Format	Use Case	Compression	Schema Evolution
Parquet	Analytics, OLAP	Built-in (Snappy, GZIP)	Schema evolution support
ORC	Big data processing	Built-in (ZLIB, Snappy)	Advanced schema support
JSON	Semi-structured data	External (GZIP)	Schema-on-read

Installation and Setup

The following instructions are written to be run from the psql command line

Install Required Extension

Install the file foreign data wrapper extension:

-- Install extension (run once per database)
CREATE EXTENSION IF NOT EXISTS file_fdw;

-- Verify installation
SELECT extname, extversion 
FROM pg_extension 
WHERE extname = 'file_fdw';

Extension Requirements

The file_fdw extension must be installed with superuser privileges and is available by default in Tacnode clusters.

Verify Available Foreign Data Wrappers

-- List all available FDWs
SELECT fdwname, fdwhandler, fdwoptions 
FROM pg_foreign_data_wrapper;

-- Check file_fdw capabilities
\des+ file_fdw

AWS S3 Integration

Create S3 Foreign Server

-- Create foreign server for AWS S3 the AWS_REGION should be the region that the S3 bucket resides in
CREATE SERVER s3_production FOREIGN DATA WRAPPER file_fdw
OPTIONS (
    AWS_REGION 'us-west-2'
);

Configure User Mapping for S3 (This step is not needed for publically available buckets)

-- Create user mapping with IAM credentials
CREATE USER MAPPING FOR current_user SERVER s3_production
OPTIONS (
    AWS_ACCESS_ID 'AKIA...your-access-key-id',
    AWS_ACCESS_KEY 'your-secret-access-key'
);

-- Create mapping for specific database user
CREATE USER MAPPING FOR data_analyst SERVER s3_production
OPTIONS (
    AWS_ACCESS_ID 'AKIA...analyst-access-key',
    AWS_ACCESS_KEY 'analyst-secret-key'
);

Use IAM Role Assumption in Production

Storing static credentials in user mappings is convenient for development but not recommended for production. See AWS IAM Trust Relationship to configure Tacnode to assume an IAM role — no long-lived keys stored in the database.

Create S3 Foreign Tables

Parquet Files

-- Create foreign table for Parquet data
CREATE FOREIGN TABLE sales_parquet (
    sale_id BIGINT,
    customer_id INT,
    product_id INT,
    sale_date DATE,
    quantity INT,
    unit_price NUMERIC(10,2),
    total_amount NUMERIC(12,2),
    region VARCHAR(50),
    sales_rep VARCHAR(100)
) SERVER s3_production
OPTIONS (
    DIR 's3://company-data-lake/sales/year=2024/',
    FORMAT 'parquet'
);

-- Partitioned Parquet data with dynamic discovery
CREATE FOREIGN TABLE sales_partitioned (
    sale_id BIGINT,
    customer_id INT,
    product_id INT,
    sale_date DATE,
    quantity INT,
    unit_price NUMERIC(10,2),
    total_amount NUMERIC(12,2),
    -- Partition columns
    year INT,
    month INT,
    region VARCHAR(50)
) SERVER s3_production
OPTIONS (
    DIR 's3://company-data-lake/sales/',
    FORMAT 'parquet'
);

ORC Files

-- Create foreign table for ORC format
CREATE FOREIGN TABLE user_activity_orc (
    user_id BIGINT,
    session_id VARCHAR(64),
    activity_timestamp TIMESTAMP,
    page_url VARCHAR(500),
    action_type VARCHAR(50),
    duration_seconds INT,
    device_type VARCHAR(20),
    ip_address INET
) SERVER s3_production
OPTIONS (
    DIR 's3://analytics-bucket/user-activity/orc/',
    FORMAT 'orc'
);

Query S3 Data

-- Basic queries
SELECT region, COUNT(*) as sale_count, SUM(total_amount) as total_revenue
FROM sales_parquet
WHERE sale_date >= '2024-01-01'
GROUP BY region
ORDER BY total_revenue DESC;

-- Join S3 data with local tables
SELECT 
    s.sale_id,
    s.total_amount,
    c.customer_name,
    c.customer_tier
FROM sales_parquet s
JOIN customers c ON s.customer_id = c.customer_id
WHERE s.sale_date BETWEEN '2024-01-01' AND '2024-01-31';

-- Aggregate across partitioned data
SELECT 
    year,
    month,
    region,
    COUNT(*) as transactions,
    SUM(total_amount) as revenue
FROM sales_partitioned
WHERE year = 2024 AND month >= 1
GROUP BY year, month, region
ORDER BY year, month, region;

File Formats

Parquet Optimization

-- Optimized Parquet foreign table
CREATE FOREIGN TABLE optimized_sales (
    sale_id BIGINT,
    customer_id INT,
    sale_date DATE,
    amount NUMERIC(12,2),
    -- Partition columns should be at the end
    year INT,
    quarter INT
) SERVER s3_production
OPTIONS (
    DIR 's3://sales-data/parquet/',
    FORMAT 'parquet'
);

ORC Configuration

-- ORC with advanced configuration
CREATE FOREIGN TABLE orc_analytics (
    transaction_id BIGINT,
    amount NUMERIC(15,2),
    timestamp TIMESTAMP,
    metadata JSONB
) SERVER s3_production
OPTIONS (
    DIR 's3://analytics/orc-data/',
    FORMAT 'orc'
);

Batch Foreign Table Creation (IMPORT FOREIGN SCHEMA)

The file_fdw extension supports batch creation of foreign tables using the IMPORT FOREIGN SCHEMA command, which simplifies management of external file-based data sources.

Syntax Overview

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

Parameter Specifications

Parameter	Description	Values
`remote_schema`	Extended semantic support for file formats	`public`
`LIMIT TO`	Import only specified tables	List of table names
`EXCEPT`	Exclude specified tables from import	List of table names
`server_name`	Previously created foreign server	Server identifier
`local_schema`	Target schema for foreign table metadata	Schema name
`OPTIONS`	Additional configuration parameters	Key-value pairs

Batch Creation Examples

Batch Import Parquet Files from S3

-- Import all Parquet files from S3 directory
IMPORT FOREIGN SCHEMA public
    FROM SERVER s3_production
    INTO public
    OPTIONS (
        FORMAT 'parquet',
        DIR 's3://analytics-data/parquet-tables/'
    );

Selective Import with Table Filtering

-- Import only specific ORC tables from object storage
IMPORT FOREIGN SCHEMA public
    LIMIT TO (sales_data, customer_metrics, product_catalog)
    FROM SERVER s3_production
    INTO analytics_schema
    OPTIONS (
        FORMAT 'orc',
        DIR 's3://warehouse/orc-files/'
    );

Import with Exclusions

-- Import all tables except specified ones
IMPORT FOREIGN SCHEMA public
    EXCEPT (temp_data, backup_tables)
    FROM SERVER s3_production
    INTO data_lake
    OPTIONS (
        FORMAT 'parquet',
        DIR 's3://production-data/structured/'
    );

Operational Considerations

File Discovery Process:

Automatically scans specified directory for compatible files
Generates table schemas based on file metadata
Creates foreign table definitions with optimized settings

Schema Inference:

Parquet: Leverages embedded schema information
ORC: Uses file footer schema metadata
Automatic data type mapping to Tacnode equivalents

Limitations and Best Practices

Current Limitations:

Supports only Parquet and ORC file formats
No partitioned table support
JSON formats require manual table creation

Directory Organization:

s3://your-bucket/
├── parquet-data/
│   ├── sales_2024_q1.parquet
│   ├── sales_2024_q2.parquet
│   └── customers.parquet
└── orc-data/
    ├── transactions.orc
    └── products.orc

This comprehensive approach to file-based foreign tables enables efficient access to cloud object storage while maintaining optimal performance for your specific use cases.