Hologres:Speed up Paimon reads

Step 1: Create an EMR data lake cluster

1. Log on to the EMR on ECS console and create an EMR cluster. For general cluster creation steps, see Create a cluster.

2. Set the following key parameters:

   Parameter	Value
   Business scenario	Select Data Lake
   Optional services	Select Spark, Hive, and Paimon. Add other services as needed.
   Metadata	Select DLF Unified Metadata
   DLF data catalog	Select a custom catalog (for example, paimon_catalog) if you created one, or use the default catalog. If you use the default catalog, you can omit the dlf_catalog parameter when creating the foreign server later.
   Cluster storage root path	Select an OSS bucket with the OSS-HDFS service enabled

Step 2: Build the Paimon data source

Build a textfile data source using EMR Hive, then use Spark to create Paimon tables and import the data.

1. Build the data source. Follow the steps in Build data using EMR Spark, but replace the ./dbgen -vf -s 100 command with ./dbgen -vf -s 10 to generate a 10 GB dataset.

2. Start Spark SQL with the Paimon catalog configured to use DLF as the metastore:

   spark-sql --conf spark.sql.catalog.paimon=org.apache.paimon.spark.SparkCatalog --conf spark.sql.catalog.paimon.metastore=dlf

3. Create a Paimon database in your OSS-HDFS bucket:

   CREATE DATABASE paimon_db location 'oss://${oss-hdfs-bucket}/tpch_10G/paimon_tpch_10g/';

   Replace ${oss-hdfs-bucket} with the name of your OSS-HDFS-enabled bucket.

4. Switch to the new database and create Paimon tables by importing from the textfile source:

   USE paimon_db;
   
   CREATE TABLE nation_paimon
   TBLPROPERTIES ('primary-key' = 'N_NATIONKEY')
   AS SELECT * FROM ${source}.nation_textfile;
   
   CREATE TABLE region_paimon
   TBLPROPERTIES ('primary-key' = 'R_REGIONKEY')
   AS SELECT * FROM ${source}.region_textfile;
   
   CREATE TABLE supplier_paimon
   TBLPROPERTIES ('primary-key' = 'S_SUPPKEY')
   AS SELECT * FROM ${source}.supplier_textfile;
   
   CREATE TABLE customer_paimon
   PARTITIONED BY (c_mktsegment)
   TBLPROPERTIES ('primary-key' = 'C_CUSTKEY')
   AS SELECT * FROM ${source}.customer_textfile;
   
   CREATE TABLE part_paimon
   PARTITIONED BY (p_brand)
   TBLPROPERTIES ('primary-key' = 'P_PARTKEY')
   AS SELECT * FROM ${source}.part_textfile;
   
   CREATE TABLE partsupp_paimon
   TBLPROPERTIES ('primary-key' = 'PS_PARTKEY,PS_SUPPKEY')
   AS SELECT * FROM ${source}.partsupp_textfile;
   
   CREATE TABLE orders_paimon
   PARTITIONED BY (o_orderdate)
   TBLPROPERTIES ('primary-key' = 'O_ORDERKEY')
   AS SELECT * FROM ${source}.orders_textfile;
   
   CREATE TABLE lineitem_paimon
   PARTITIONED BY (l_shipdate)
   TBLPROPERTIES ('primary-key' = 'L_ORDERKEY,L_LINENUMBER')
   AS SELECT * FROM ${source}.lineitem_textfile;

   Replace ${source} with the name of the Hive database where the *_textfile tables are located.

Step 3: Connect Hologres to the Paimon data lake

Create a foreign server in your Hologres shared cluster. The server uses dlf_fdw (DLF foreign data wrapper) to authenticate with DLF and locate data in OSS.

CREATE SERVER IF NOT EXISTS dlf_server FOREIGN DATA WRAPPER dlf_fdw OPTIONS
(
    dlf_catalog 'paimon_catalog',                           -- Name of your custom DLF data catalog.
                                                             -- Omit this line if you use the default catalog.
    dlf_endpoint 'dlf-share.cn-shanghai.aliyuncs.com',     -- DLF service endpoint for your region.
    oss_endpoint 'cn-shanghai.oss-dls.aliyuncs.com'        -- OSS-HDFS endpoint for your region.
);

Replace the endpoint values with those for your region. The dlf_endpoint and oss_endpoint values must correspond to the same region as your EMR cluster and OSS bucket.

Step 4: Create foreign tables and query Paimon data

1. Import the Paimon table schema into Hologres as a foreign table:

   IMPORT FOREIGN SCHEMA paimon_db LIMIT TO
   (
     lineitem_paimon
   )
   FROM SERVER dlf_server INTO public OPTIONS (if_table_exist 'update');

   This creates a foreign table in Hologres that maps to lineitem_paimon in your Paimon data lake. No data is copied — queries read directly from OSS.

2. Run a query against the foreign table. The following example is TPC-H Q1:

   SELECT
     l_returnflag,
     l_linestatus,
     SUM(l_quantity)                                       AS sum_qty,
     SUM(l_extendedprice)                                  AS sum_base_price,
     SUM(l_extendedprice * (1 - l_discount))               AS sum_disc_price,
     SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
     AVG(l_quantity)                                       AS avg_qty,
     AVG(l_extendedprice)                                  AS avg_price,
     AVG(l_discount)                                       AS avg_disc,
     COUNT(*)                                              AS count_order
   FROM lineitem_paimon
   WHERE l_shipdate <= date '1998-12-01' - interval '120' DAY
   GROUP BY l_returnflag, l_linestatus
   ORDER BY l_returnflag, l_linestatus;

   A successful query returns rows grouped by l_returnflag and l_linestatus, with aggregated quantity, price, discount, and order count columns. If you see results, your foreign table is correctly mapped to the Paimon data in OSS. For the remaining 21 TPC-H queries, see 22 TPC-H query statements.