Hologres:Accelerate data lake queries

Background information

As digital transformation accelerates, data volumes are growing rapidly, posing significant challenges to traditional data analytics in terms of cost, scale, and data diversity. Hologres, in collaboration with DLF and OSS, offers a data lakehouse architecture for data lake acceleration. This architecture helps businesses achieve low-cost storage for massive amounts of data, unified metadata management, and efficient data analytics and insights.

Hologres seamlessly integrates with DLF and OSS. Using foreign tables, you can directly accelerate read and write operations on data in formats such as Hudi, Delta, Paimon, ORC, Parquet, CSV, and SequenceFile stored in OSS without moving the data. Foreign tables only map fields and do not store data. This reduces development and operations costs, breaks down data silos, and enables business insights. Hologres supports two modes: dedicated instances (exclusive resources) and shared Serverless clusters (pay-as-you-go). For more information, see Purchase a Hologres instance.

The following table describes the Alibaba Cloud services involved in the real-time data lake solution.

Notes

Hologres shared clusters do not store data. They only support querying OSS data lakes using foreign tables.

Preparations

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

1. Activate OSS and prepare test data.

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

      Note

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

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

   3. Upload the tpch_10g_orc_3.zip test data to a folder in the bucket.

      Note

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

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

2. Activate DLF and import the OSS test data.

   1. Go to the DLF activation page.

   2. Log on to the Data Lake Formation console. On the Metadata Management page, click Create Database. For more information, see Databases, tables, and functions.

      This topic uses the creation of a database named mydatabase as an example.

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

      After the job is complete, you can view the data on the Tables tab of the Metadata Management page.

      

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.

   Go to the Hologres instance list. In the Actions column of the target instance, click Data Lake Acceleration and confirm the action. The Hologres instance restarts after you enable this feature.

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

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

   Note

   This extension is created by default in Hologres V2.1 and later, so you do not need to perform this operation. You can go to the Hologres instance list and check your instance version on the Instance Details page.

   CREATE EXTENSION IF NOT EXISTS dlf_fdw;

   Note

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

4. Run the following statement to create a foreign server named dlf_server and configure its endpoint information. This ensures normal access between Hologres, DLF, and OSS. For more information about how to create a foreign server and its parameters, see Create a foreign server.

   -- Create a foreign server. The China (Shanghai) region is used as an example.
   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 using a Hologres foreign table

A Hologres foreign table stores the mapping to data in an OSS data lake. The data itself is stored in the OSS data lake and does not occupy Hologres storage space. Query performance is typically in the range of seconds to minutes.

1. Create a Hologres foreign table and map the OSS data lake data to it.

   -- This topic uses mydatabase as an example. When you create the schema, replace mydatabase with the name of your custom database in DLF Metadata Management.
   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.

   After the foreign table is created, you can directly query it to read data from OSS. The following is a sample statement.

   -- TPCH Q11 query statement
   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 the OSS data lake using a Hologres internal table

To query data using a Hologres internal table, you must import data from the OSS data lake into Hologres. The data is stored in Hologres, which provides better query performance and higher data processing capabilities. For more information about storage fees, see Billing overview.

1. In Hologres, create an internal table that has the same schema as the foreign table. The following is an example.

   -- 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 table to the Hologres internal table.

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

3. Query the data in the Hologres internal table.

   -- TPCH Q11 query statement
   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

When I create a Hologres foreign table, the error message ERROR: babysitter not ready,req:name:"HiveAccess" is returned.

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

• Solution: Go to the Hologres instance list. In the Actions column of the target instance, click Data Lake Acceleration and confirm the action to enable the feature.

References

The preceding content provides a tutorial example. For a complete description of the data lake feature, see Accelerate access to OSS data lakes based on DLF.