Hologres:Accelerate data lake queries

Background information

As businesses deepen their digital transformation, data volumes are growing rapidly, creating challenges for traditional data analytics in terms of cost, scale, and data diversity. Hologres, combined with DLF and OSS, provides a data lake acceleration service based on a lakehouse architecture. This architecture enables low-cost storage for massive datasets, unified metadata management, and efficient data analysis.

Hologres seamlessly integrates with DLF and OSS. By using foreign tables, you can directly accelerate read and write operations on data stored in OSS in formats such as Hudi, Delta, Paimon, ORC, Parquet, CSV, and SequenceFile, without moving the data. A foreign table only maps columns and does not store the data itself. This approach reduces development and O&M costs, breaks down data silos, and enables faster business insights. Hologres supports two modes: dedicated clusters (exclusive resources) and shared cluster Serverless (pay-as-you-go). For more information, see Purchase a Hologres instance.

The following table lists the Alibaba Cloud services used in the real-time data lake solution.

Notes

Hologres shared clusters do not store data and can query OSS data lakes only by using foreign tables.

Prerequisites

This topic demonstrates how to activate the OSS, DLF, and Hologres services, using the China (Shanghai) region as an example.

1. Activate OSS and prepare test data.

   1. Navigate to the OSS activation page and follow the on-screen instructions to activate the service.

      Note

      After you activate OSS, the default billing method is pay-as-you-go. To reduce your OSS costs, we recommend that you purchase a resource plan.

   2. Log on to the OSS console and create a bucket. For more information, see Console Quick Start.

   3. Download and upload the tpch_10g_orc_3.zip test data to the tpch_10g_orc_3/ directory in your bucket.

      Note

      • After you upload the test data files, manually delete any files such as .DS_Store if they exist.

      • For faster downloads, this package contains only the nation_orc, supplier_orc, and partsupp_orc tables required for this topic.

2. Activate DLF and import the OSS test data.

   1. Navigate to the DLF activation page..

   2. Log on to the DLF console. On the Metadata Management page, click Create Database. For more information, see Manage databases, tables, and functions.

      This topic provides an example of creating the mydatabase database.

   3. On the Metadata Discovery page, create a metadata discovery job to import the OSS test data. For more information, see Metadata Discovery.

      After the job is complete, the tables appear on the Data Tables tab of the Metadata Management page.

      The list of data tables displays three ORC tables: nation_orc, partsupp_orc, and supplier_orc.

3. Activate Hologres and purchase a Hologres instance. For more information, see Purchase a Hologres instance.

   Note

   If you are a new user, you can apply for a free trial of Hologres on the Alibaba Cloud Free Trial page.

Step 1: Configure the environment

1. Enable the data lake acceleration feature for your Hologres instance.

   Navigate to the Hologres instances page. In the Actions column of the target instance, click Lake Acceleration and confirm the action. Enabling this feature restarts your Hologres instance.

2. Log on to your Hologres instance and create a database. For more information, see Connect to HoloWeb to run queries.

3. (Optional) Create an extension. This topic uses dlf_fdw as an example.

   Note

   This operation is not required for Hologres V2.1 and later because the extension is created by default. You can go to the Hologres instances page and check the version of your instance on the Instance Details page.

   CREATE EXTENSION IF NOT EXISTS dlf_fdw;

   Note

   Run this statement as a superuser in the SQL Editor in HoloWeb to create the extension. This operation applies to the entire database and only needs to be performed once per database. For more information about how to grant permissions to a Hologres account, see Manage user permissions.

4. Execute the following statement to create the dlf_server foreign server and configure Endpoint information to ensure proper access among Hologres, DLF, and OSS. For more information about other creation methods and related parameters, see Create a foreign server.

   -- Create a foreign server. This example uses the China (Shanghai) region.
   CREATE SERVER IF NOT EXISTS dlf_server FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
       dlf_region 'cn-shanghai',
       dlf_endpoint 'dlf-share.cn-shanghai.aliyuncs.com',
       oss_endpoint 'oss-cn-shanghai-internal.aliyuncs.com');

Step 2: Query the OSS data lake with foreign tables

Hologres foreign tables store the mappings to data in an OSS data lake. The data remains in the OSS data lake and does not occupy Hologres storage space. Queries are typically completed in seconds to minutes.

1. Create Hologres foreign tables and map them to the data in the OSS data lake.

   -- This topic uses mydatabase as an example. Replace it with the name of the database that you created in DLF.
   IMPORT FOREIGN SCHEMA mydatabase LIMIT TO 
   (
     nation_orc,
     supplier_orc,
     partsupp_orc
   )
   FROM SERVER dlf_server INTO public OPTIONS (if_table_exist 'update');

2. Query the data.

   For example:

   -- TPC-H Q11 query
   SELECT
           ps_partkey,
           SUM(ps_supplycost * ps_availqty) AS value
   FROM
           partsupp_orc,
           supplier_orc,
           nation_orc
   WHERE
           ps_suppkey = s_suppkey
           AND s_nationkey = n_nationkey
           AND RTRIM(n_name) = 'EGYPT'
   GROUP BY
           ps_partkey HAVING
                   SUM(ps_supplycost * ps_availqty) > (
                           SELECT
                                   SUM(ps_supplycost * ps_availqty) * 0.000001
                           FROM
                                   partsupp_orc,
                                   supplier_orc,
                                   nation_orc
                           WHERE
                                   ps_suppkey = s_suppkey
                                   AND s_nationkey = n_nationkey
                                   AND RTRIM(n_name) = 'EGYPT'
                   )
   ORDER BY
           value DESC;

Step 3 (Optional): Query with internal tables

You can import data from an OSS data lake into Hologres internal tables. The data is stored in Hologres, which offers better query performance and more powerful data processing capabilities. For more information about storage fees, see Billing overview.

1. Create internal tables in Hologres with the same schema as the foreign tables. The following statements are examples:

   -- Create the nation table.
   DROP TABLE IF EXISTS NATION;
   BEGIN;
   CREATE TABLE NATION (
       N_NATIONKEY INT NOT NULL PRIMARY KEY,
       N_NAME TEXT NOT NULL,
       N_REGIONKEY INT NOT NULL,
       N_COMMENT TEXT NOT NULL
   );
   CALL set_table_property('NATION', 'distribution_key', 'N_NATIONKEY');
   CALL set_table_property('NATION', 'bitmap_columns', '');
   CALL set_table_property('NATION', 'dictionary_encoding_columns', '');
   COMMIT;
   -- Create the supplier table.
   DROP TABLE IF EXISTS SUPPLIER;
   BEGIN;
   CREATE TABLE SUPPLIER (
       S_SUPPKEY INT NOT NULL PRIMARY KEY,
       S_NAME TEXT NOT NULL,
       S_ADDRESS TEXT NOT NULL,
       S_NATIONKEY INT NOT NULL,
       S_PHONE TEXT NOT NULL,
       S_ACCTBAL DECIMAL(15, 2) NOT NULL,
       S_COMMENT TEXT NOT NULL
   );
   CALL set_table_property('SUPPLIER', 'distribution_key', 'S_SUPPKEY');
   CALL set_table_property('SUPPLIER', 'bitmap_columns', 'S_NATIONKEY');
   CALL set_table_property('SUPPLIER', 'dictionary_encoding_columns', '');
   COMMIT;
   -- Create the partsupp table.
   DROP TABLE IF EXISTS PARTSUPP;
   BEGIN;
   CREATE TABLE PARTSUPP (
       PS_PARTKEY INT NOT NULL,
       PS_SUPPKEY INT NOT NULL,
       PS_AVAILQTY INT NOT NULL,
       PS_SUPPLYCOST DECIMAL(15, 2) NOT NULL,
       PS_COMMENT TEXT NOT NULL,
       PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY)
   );
   CALL set_table_property('PARTSUPP', 'distribution_key', 'PS_PARTKEY');
   CALL set_table_property('PARTSUPP', 'bitmap_columns', 'ps_availqty');
   CALL set_table_property('PARTSUPP', 'dictionary_encoding_columns', '');
   COMMIT;

2. Synchronize data from the Hologres foreign tables to the Hologres internal tables.

   -- Import data from Hologres foreign tables to internal tables.
   INSERT INTO nation SELECT * FROM nation_orc;
   INSERT INTO supplier SELECT * FROM supplier_orc;
   INSERT INTO partsupp SELECT * FROM partsupp_orc;

3. Query data in the Hologres internal tables.

   -- TPC-H Q11 query
   SELECT
           ps_partkey,
           SUM(ps_supplycost * ps_availqty) AS value
   FROM
           partsupp,
           supplier,
           nation
   WHERE
           ps_suppkey = s_suppkey
           AND s_nationkey = n_nationkey
           AND RTRIM(n_name) = 'EGYPT'
   GROUP BY
           ps_partkey HAVING
                   SUM(ps_supplycost * ps_availqty) > (
                           SELECT
                                   SUM(ps_supplycost * ps_availqty) * 0.000001
                           FROM
                                   partsupp,
                                   supplier,
                                   nation
                           WHERE
                                   ps_suppkey = s_suppkey
                                   AND s_nationkey = n_nationkey
                                   AND RTRIM(n_name) = 'EGYPT'
                   )
   ORDER BY
           value DESC;

FAQ

An ERROR: babysitter not ready,req:name:"HiveAccess" error occurs when you create a Hologres foreign table.

• Cause: The data lake acceleration feature is not enabled.

• Solution: Navigate to the Hologres instances page. In the Actions column for the target instance, click Lake Acceleration and follow the prompts to enable the feature.

Related documentation

This topic is a tutorial. For complete documentation on accelerating queries on an OSS-based data lake, see Accelerate queries on an OSS-based data lake by using DLF.