Use PXF Iceberg connector to read and write Iceberg-format data between Greengage DB and Hive Metastore and REST catalogs

Connect to the Iceberg REST catalog and create a test database:

CREATE DATABASE IF NOT EXISTS customers;

Create a source data table:

CREATE TABLE customers.data (
    id INT,
    first_name VARCHAR(20),
    last_name VARCHAR(20)
);

Populate the created table with sample data:

INSERT INTO customers.data 
    VALUES (1, 'John', 'Doe'),
           (2, 'Jane', 'Doe');

Query the created table:

SELECT * FROM customers.data;

1       John    Doe
2       Jane    Doe
Time taken: 0.26 seconds, Fetched 2 row(s)

On the Greengage DB master host, create a readable PXF external table that references the earlier created customers.data Iceberg table. In the LOCATION clause, specify the PXF iceberg profile and the server configuration. In the FORMAT clause, specify pxfwritable_import, which is the built-in custom formatter function for read operations:

CREATE EXTERNAL TABLE customers_r_rest (
    id INT, 
    first_name TEXT, 
    last_name TEXT
    )
    LOCATION ('pxf://customers.data?PROFILE=iceberg&SERVER=iceberg')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

Query the created external table:

SELECT * FROM customers_r_rest;

 id | first_name | last_name
----+------------+-----------
  1 | John       | Doe
  2 | Jane       | Doe
(2 rows)

On the Greengage DB master host, create a writable PXF external table that references the earlier created customers.data Iceberg table. In the LOCATION clause, specify the PXF iceberg profile and the server configuration. In the FORMAT clause, specify pxfwritable_export, which is the built-in custom formatter function for write operations:

CREATE WRITABLE EXTERNAL TABLE customers_w_rest (
    id INT, 
    first_name TEXT, 
    last_name TEXT
    )
    LOCATION ('pxf://customers.data?PROFILE=iceberg&SERVER=iceberg')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

Insert some data into the created external table:

INSERT INTO customers_w_rest (
    id,
    first_name,
    last_name
    )
    VALUES (3, 'Bob', 'Brown'),
           (4, 'Jane', 'Smith');

Connect to the Iceberg REST catalog and query the source customers.data table:

SELECT * FROM customers.data;

1       John    Doe
2       Jane    Doe
3       Bob     Brown
4       Jane    Smith
Time taken: 0.168 seconds, Fetched 4 row(s)

DELETE FROM customers.data WHERE id IN (3,4);

On the Greengage DB master host, create a foreign server that uses the iceberg_pxf_fdw foreign data wrapper and points to the REST catalog:

CREATE SERVER iceberg_rest_server
    FOREIGN DATA WRAPPER iceberg_pxf_fdw
    OPTIONS (uri 'http://iceberg-rest:8181');

Create a user mapping that will handle authentication on the Iceberg REST server. In the SERVER clause, specify the earlier created iceberg_rest_server server; in the OPTIONS clause, provide the authentication parameters (s3_access_key_id and s3_secret_access_key):

CREATE USER MAPPING FOR CURRENT_USER 
    SERVER iceberg_rest_server
    OPTIONS (s3_access_key_id 'admin', 
             s3_secret_access_key 'password');

Create the foreign table whose structure matches the one of the customers.data table initially created in the REST catalog. In the SERVER clause, specify the earlier created iceberg_rest_server server; in the OPTIONS clause, provide the name of the earlier created customers.data table:

CREATE FOREIGN TABLE customers_f_rest (
    id INT,
    first_name TEXT,
    last_name TEXT
    )
    SERVER iceberg_rest_server
    OPTIONS (resource 'customers.data');

Query the created foreign table:

SELECT * FROM customers_f_rest;

 id | first_name | last_name
----+------------+-----------
  1 | John       | Doe
  2 | Jane       | Doe
(2 rows)

Insert some data into the created foreign table:

INSERT INTO customers_f_rest (
    id,
    first_name,
    last_name
    )
    VALUES (3, 'Bob', 'Brown'),
           (4, 'Jane', 'Smith');

Query the foreign table again:

SELECT * FROM customers_f_rest;

 id | first_name | last_name
----+------------+-----------
  1 | John       | Doe
  2 | Jane       | Doe
  3 | Bob        | Brown
  4 | Jane       | Smith
(4 rows)

Connect to the Iceberg REST catalog and query the source customers.data table:

SELECT * FROM customers.data;

1       John    Doe
2       Jane    Doe
3       Bob     Brown
4       Jane    Smith
Time taken: 0.194 seconds, Fetched 4 row(s)

DELETE FROM customers.data WHERE id IN (3,4);

On the HiveClient host, launch the Hive command line:

$ hive

Create the customers Hive table in the default database to store the sample dataset. The STORED BY ICEBERG clause instructs Hive to create the table in the Iceberg format:

CREATE TABLE customers (
    id INT,
    first_name STRING,
    last_name STRING 
    )
    STORED BY ICEBERG;

Populate the created table with sample data:

INSERT INTO customers (
    id,
    first_name,
    last_name
    )
    VALUES (1, 'John', 'Doe'),
           (2, 'Jane', 'Doe');

Query the customers Hive table to verify that the data was loaded correctly:

SELECT * FROM customers;

+---------------+-----------------------+----------------------+
| customers.id  | customers.first_name  | customers.last_name  |
+---------------+-----------------------+----------------------+
| 1             | John                  | Doe                  |
| 2             | Jane                  | Doe                  |
+---------------+-----------------------+----------------------+
2 rows selected (0.171 seconds)

On the Greengage DB master host, create a readable PXF external table that references the earlier created default.customers Iceberg table. In the LOCATION clause, specify the PXF iceberg profile and the server configuration. In the FORMAT clause, specify pxfwritable_import, which is the built-in custom formatter function for read operations:

CREATE EXTERNAL TABLE customers_r_hive (
    id INT, 
    first_name TEXT, 
    last_name TEXT
    )
    LOCATION ('pxf://default.customers?PROFILE=iceberg&SERVER=iceberg')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

Query the created external table:

SELECT * FROM customers_r_hive;

 id | first_name | last_name
----+------------+-----------
  1 | John       | Doe
  2 | Jane       | Doe
(2 rows)

On the Greengage DB master host, create a writable PXF external table that references the earlier created default.customers Iceberg table. In the LOCATION clause, specify the PXF iceberg profile and the server configuration. In the FORMAT clause, specify pxfwritable_export, which is the built-in custom formatter function for write operations:

CREATE WRITABLE EXTERNAL TABLE customers_w_hive (
    id INT, 
    first_name TEXT, 
    last_name TEXT
    )
    LOCATION ('pxf://default.customers?PROFILE=iceberg&SERVER=iceberg')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');

Populate the created table with sample data:

INSERT INTO customers_w_hive (
    id,
    first_name,
    last_name
    )
    VALUES (3, 'Bob', 'Brown'),
           (4, 'Jane', 'Smith');

On the HiveClient host, query the customers Iceberg table:

SELECT * FROM customers;

+---------------+-----------------------+----------------------+
| customers.id  | customers.first_name  | customers.last_name  |
+---------------+-----------------------+----------------------+
| 1             | John                  | Doe                  |
| 2             | Jane                  | Doe                  |
| 3             | Bob                   | Brown                |
| 4             | Jane                  | Smith                |
+---------------+-----------------------+----------------------+
4 rows selected (0.371 seconds)

DELETE FROM customers WHERE id IN (3,4);

On the Greengage DB master host, create a foreign server that uses the iceberg_pxf_fdw foreign data wrapper and points to the Hive Metastore catalog. In the OPTIONS clause, set the catalog_type to HIVE_METASTORE and provide the URI of the Hive Metastore server and the name of the Iceberg Hive Metastore catalog:

CREATE SERVER iceberg_hive_server
    FOREIGN DATA WRAPPER iceberg_pxf_fdw
    OPTIONS (catalog_type 'HIVE_METASTORE', 
             uri 'thrift://hive-metastore:9083', 
             catalog_name 'iceberg_hive');

Create a user mapping that will handle authentication on the Iceberg Hive Metastore server. In the SERVER clause, specify the earlier created iceberg_hive_server server:

CREATE USER MAPPING FOR CURRENT_USER
    SERVER iceberg_hive_server;

Create the foreign table whose structure matches the one of the default.customers table initially created in the Hive Metastore catalog. In the SERVER clause, specify the earlier created iceberg_hive_server server; in the OPTIONS clause, provide the name of the earlier created customers table:

CREATE FOREIGN TABLE customers_f_hive (
    id INT, 
    first_name TEXT, 
    last_name TEXT
    )
    SERVER iceberg_hive_server
    OPTIONS (resource 'default.customers');

Query the created foreign table:

SELECT * FROM customers_f_hive;

 id | first_name | last_name
----+------------+-----------
  1 | John       | Doe
  2 | Jane       | Doe
(2 rows)

Insert some data into the created foreign table:

INSERT INTO customers_f_hive (
    id,
    first_name,
    last_name
    )
    VALUES (3, 'Bob', 'Brown'),
           (4, 'Jane', 'Smith');

On the HiveClient host, query the customers table:

SELECT * FROM customers;

+---------------+-----------------------+----------------------+
| customers.id  | customers.first_name  | customers.last_name  |
+---------------+-----------------------+----------------------+
| 1             | John                  | Doe                  |
| 2             | Jane                  | Doe                  |
| 3             | Bob                   | Brown                |
| 4             | Jane                  | Smith                |
+---------------+-----------------------+----------------------+
4 rows selected (0.132 seconds)

DELETE FROM customers WHERE id IN (3,4);