Use OSS Foreign Table to access Iceberg data

Step 1: Log on to the EMR cluster

Use SSH to log on to the EMR cluster.

Step 2: Start Spark SQL with Iceberg support

spark-sql \
    --conf spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions \
    --conf spark.sql.catalog.hadoop=org.apache.iceberg.spark.SparkCatalog \
    --conf spark.sql.catalog.hadoop.type=hadoop \
    --conf spark.sql.catalog.hadoop.warehouse=oss://testBucketName/warehouse

Replace oss://testBucketName/warehouse with your actual OSS warehouse path.

When the prompt changes to spark-sql>, Spark SQL is ready.

Step 3: Create an Iceberg table and write data

-- Create a database
CREATE DATABASE IF NOT EXISTS hadoop.hadoop_db;

-- Create a table and insert sample data
CREATE TABLE IF NOT EXISTS hadoop.hadoop_db.hadoop_sample(
    id BIGINT COMMENT 'unique id',
    data STRING
)
USING iceberg;

INSERT INTO hadoop.hadoop_db.hadoop_sample VALUES (1, 'a'), (2, 'b'), (3, 'c');

Verify the data:

SELECT * FROM hadoop.hadoop_db.hadoop_sample;

Step 4: Verify the OSS file structure

In the OSS console, confirm the Iceberg files exist at the following paths:

oss://testBucketName/warehouse/hadoop_db/hadoop_sample/
├── metadata/
│   ├── metadata.json
│   ├── snapshots.avro
│   └── manifests.avro
└── data/
    └── parquet files...

Step 5: Create the Iceberg foreign table in AnalyticDB for PostgreSQL

Run the following SQL in AnalyticDB for PostgreSQL:

-- Create an OSS server
CREATE SERVER oss_hadoop_srv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-hangzhou-********.aliyuncs.com',
        bucket 'testBucketName',
        catalog_type 'hadoop',
        warehouse 'oss://testBucketName/warehouse'
    );

-- Create a user mapping
CREATE USER MAPPING FOR PUBLIC
    SERVER oss_hadoop_srv
    OPTIONS (
        id 'LTAI****************',   -- AccessKey ID for OSS
        key 'yourAccessKeySecret'    -- AccessKey secret for OSS
    );

-- Create a foreign table
CREATE FOREIGN TABLE sample (
    id BIGINT,
    data text
)
SERVER oss_hadoop_srv
OPTIONS (
    format 'iceberg',
    database_name 'hadoop_db',
    table_name 'hadoop_sample'
);

Step 6: Query the data

SELECT * FROM sample;

Step 1: Log on to the EMR cluster

Use SSH to log on to the EMR cluster.

Step 2: Start Spark SQL with Hive Catalog support

spark-sql \
    --conf spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions \
    --conf spark.sql.catalog.hive=org.apache.iceberg.spark.SparkCatalog \
    --conf spark.sql.catalog.hive.type=hive

When the prompt changes to spark-sql>, Spark SQL is ready.

Step 3: Create an Iceberg table and write data

-- Create a database with OSS as the storage location
CREATE DATABASE IF NOT EXISTS hive.hive_db
    COMMENT 'Database stored on OSS'
    LOCATION 'oss://testBucketName/warehouse/hive_db.db/';

-- Create a table and insert sample data
CREATE TABLE IF NOT EXISTS hive.hive_db.hive_sample(
    id BIGINT COMMENT 'unique id',
    data STRING
)
USING iceberg;

INSERT INTO hive.hive_db.hive_sample VALUES (1, 'a'), (2, 'b'), (3, 'c');

Verify the data:

SELECT * FROM hive.hive_db.hive_sample;

Step 4: Verify the OSS storage structure

In the OSS console, confirm the Iceberg files exist at the following paths:

oss://testBucketName/warehouse/hive_db.db/hive_sample/
├── metadata/
│   ├── metadata.json
│   ├── snapshots.avro
│   └── manifests.avro
└── data/
    └── parquet files...

Step 5: Create the Iceberg foreign table in AnalyticDB for PostgreSQL

Run the following SQL in AnalyticDB for PostgreSQL:

-- Create an OSS server
CREATE SERVER oss_hive_srv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-hangzhou-********.aliyuncs.com',
        bucket 'testBucketName',
        catalog_type 'hive',
        hms_uris 'thrift://192.168.XXX.XXX:9083'
    );

-- Create a user mapping
CREATE USER MAPPING FOR PUBLIC
    SERVER oss_hive_srv
    OPTIONS (
        id 'LTAI****************',   -- AccessKey ID for OSS
        key 'yourAccessKeySecret'    -- AccessKey secret for OSS
    );

-- Create a foreign table
CREATE FOREIGN TABLE hive_sample (
    id BIGINT,
    data text
)
SERVER oss_hive_srv
OPTIONS (
    format 'iceberg',
    database_name 'hive_db',
    table_name 'hive_sample'
);

Step 6: Query the data

SELECT * FROM hive_sample;

Step 1: Create the Iceberg foreign table in AnalyticDB for PostgreSQL

Based on the Iceberg table schema in DMS OneMeta, run the following SQL in AnalyticDB for PostgreSQL:

-- Create an OSS server
CREATE SERVER oss_dms_serv
    FOREIGN DATA WRAPPER oss_fdw
    OPTIONS (
        endpoint 'oss-cn-hangzhou-********.aliyuncs.com',
        bucket 'testBucketName',
        catalog_type 'onemeta',
        dms_endpoint '<dms_endpoint_name>',
        dms_region '<dms_region_id>'
    );

-- Create a user mapping
CREATE USER MAPPING FOR PUBLIC
    SERVER oss_dms_serv
    OPTIONS (
        id 'LTAI****************',        -- AccessKey ID for OSS
        key 'yourAccessKeySecret',        -- AccessKey secret for OSS
        dms_id 'LTAI****************',    -- AccessKey ID for DMS
        dms_key 'yourAccessKeySecret'     -- AccessKey secret for DMS
    );

-- Create a foreign table
CREATE FOREIGN TABLE sample (
    id BIGINT,
    data text
)
SERVER oss_dms_serv
OPTIONS (
    format 'iceberg',
    dms_catalog_name '<dms-catalog-name>',
    database_name '<dms-database-name>',
    table_name '<dms-table-name>'
);

Step 2: Query the data

SELECT * FROM sample;