PostgreSQL에서 pg_analytics를 이용해 iceberg 테이블 읽기

1. iceberg table 데이터 조회

[postgres@DESKTOP-OUB301V ~]$ aws s3 ls --profile iceberg_test s3://test-iceberg-parquet/iceberg-warehouse/default/sample_table/data/
2025-01-07 19:03:05        916 00000-0-121ba239-b336-4375-9b16-1a0d8030a8fe-0-00001.parquet
2025-01-07 19:22:36        909 00000-0-c67ceb11-8b9b-4100-8e3e-ad8db6d40cd6-0-00001.parquet
2025-01-07 19:03:05        923 00001-1-121ba239-b336-4375-9b16-1a0d8030a8fe-0-00001.parquet
2025-01-07 19:22:36        902 00001-1-c67ceb11-8b9b-4100-8e3e-ad8db6d40cd6-0-00001.parquet

2. Foreign wrapper & server 생성

-- CREATE WRAPPER
s3test=#  CREATE FOREIGN DATA WRAPPER iceberg_wrapper
HANDLER iceberg_fdw_handler
VALIDATOR iceberg_fdw_validator;
CREATE FOREIGN DATA WRAPPER

-- CREATE SERVER
s3test=# CREATE SERVER iceberg_server
FOREIGN DATA WRAPPER iceberg_wrapper;
CREATE SERVER

3. user mapping

s3test=# CREATE USER MAPPING FOR current_user
SERVER iceberg_server
OPTIONS (
  type 'S3',
  key_id '****', -- AWS Access Key ID
  secret '****', -- AWS Secret Access Key
  region 'ap-northeast-2'
);

4. Foreign table 생성

-- CREATE FOREIGN TABLE
s3test=# CREATE FOREIGN TABLE iceberg_test ()
SERVER iceberg_server
OPTIONS (files 's3://test-iceberg-parquet/iceberg-warehouse/default/sample_table');

-- SELECT FOREIGN TABLE
s3test=# select * from iceberg_test;
 id |  name   | timestamp
----+---------+------------
  4 | Dave    | 2023-01-04
  5 | Eve     | 2023-01-05
  1 | Alice   | 2023-01-01
  2 | Bob     | 2023-01-02
  3 | Charlie | 2023-01-03
  
 -- CTAS
 s3test=# CREATE TABLE icebergtb as select * From iceberg_test ;

s3test=# select * from icebergtb;
 id |  name   | timestamp
----+---------+------------
  4 | Dave    | 2023-01-04
  5 | Eve     | 2023-01-05
  1 | Alice   | 2023-01-01
  2 | Bob     | 2023-01-02
  3 | Charlie | 2023-01-03
(5 rows)