Hologres:OSS data lake acceleration

Configure the environment

1. Enable the DLF_FDW backend configuration for your Hologres instance.

   Go to the Hologres console. On the Instances or Instance Details page, find the target instance and click Actions in the Lake Acceleration column. After you confirm the operation, the system automatically configures DLF_FDW and restarts the instance. The service is available after the instance restarts.

   Note

   The self-service feature to enable the DLF_FDW backend configuration in the Hologres console is being rolled out. If you do not see the Lake Acceleration button, refer to Common errors when preparing for an upgrade or join the Hologres DingTalk group to provide feedback. For more information, see How do I get more online support?.

   After you enable DLF_FDW, the feature uses existing system resources (currently 1 core and 4 GB of memory) by default. No additional resource purchase is required.

2. Create an extension.

   A superuser must execute the following statement once per database to create the extension. This enables you to read data from OSS by using DLF 1.0.

   CREATE EXTENSION IF NOT EXISTS dlf_fdw;

3. Create a foreign server.

   Important

   You must use a superuser account to create a foreign server to avoid permission issues.

   Hologres supports the Multi-Catalog feature of DLF 1.0. If you have only one EMR cluster, you can use the default DLF 1.0 catalog. If you have multiple EMR clusters, you can use custom data catalogs to link your Hologres instance to different EMR clusters. You can also choose native OSS or OSS-HDFS as the data source. The following sections detail the configurations.

   • Use the DLF 1.0 default catalog and native OSS storage to create a server. Sample syntax:

     -- View existing servers. The meta_warehouse_server and odps_server are built-in servers and cannot be modified or deleted.
     SELECT * FROM pg_foreign_server;
     
     -- Drop an existing server.
     DROP SERVER SERVER_NAME CASCADE;
     
     -- Create a server.
     CREATE SERVER IF NOT EXISTS SERVER_NAME FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
         dlf_region 'REGION_ID',
         dlf_endpoint 'dlf-share.REGION_ID.aliyuncs.com',
         oss_endpoint 'oss-REGION_ID-internal.aliyuncs.com'
     );

   • Use OSS-HDFS as the data lake storage.

     • Determine the OSS-HDFS endpoint.

       In the OSS console, you can find the endpoint on the overview page of the bucket where OSS-HDFS is enabled.

       In the access port table, find the endpoint in the Bucket domain column that corresponds to the HDFS service row. The endpoint is in the format of <BucketName>.cn-hangzhou.oss-dls.aliyuncs.com.

     • Create a foreign server and configure its endpoint.

       After you confirm the bucket domain name, you can configure the DLF_FDW OSS_Endpoint option in Hologres. The example syntax is as follows.

       CREATE EXTENSION IF NOT EXISTS dlf_fdw;
       
       CREATE SERVER IF NOT EXISTS SERVER_NAME FOREIGN DATA WRAPPER dlf_fdw OPTIONS (
           dlf_region 'REGION_ID',
           dlf_endpoint 'dlf-share.REGION_ID.aliyuncs.com',
           oss_endpoint 'BUCKET_NAME.REGION_ID.oss-dls.aliyuncs.com' -- The endpoint of the OSS-HDFS bucket.
       );

     • Parameters

       Parameter	Description	Example
       SERVER_NAME	The custom name of the server.	dlf_server
       dlf_region	The region where DLF 1.0 is located. China (Beijing): cn-beijing. China (Hangzhou): cn-hangzhou. China (Shanghai): cn-shanghai. China (Shenzhen): cn-shenzhen. China (Zhangjiakou): cn-zhangjiakou. Singapore: ap-southeast-1. Germany (Frankfurt): eu-central-1. US (Virginia): us-east-1. Indonesia (Jakarta): ap-southeast-5.	cn-hangzhou
       dlf_endpoint	We recommend that you use the internal endpoint of DLF 1.0 for better access performance. China (Beijing): dlf-share.cn-beijing.aliyuncs.com. China (Hangzhou): dlf-share.cn-hangzhou.aliyuncs.com China (Shanghai): dlf-share.cn-shanghai.aliyuncs.com. China (Shenzhen): dlf-share.cn-shenzhen.aliyuncs.com. China (Zhangjiakou): dlf-share.cn-zhangjiakou.aliyuncs.com. Singapore: dlf-share.ap-southeast-1.aliyuncs.com. Germany (Frankfurt): dlf-share.eu-central-1.aliyuncs.com. US (Virginia): dlf-share.us-east-1.aliyuncs.com. Indonesia (Jakarta): dlf-share.ap-southeast-5.aliyuncs.com.	dlf-share.cn-shanghai.aliyuncs.com
       oss_endpoint	For native OSS storage, we recommend that you use the internal endpoint of OSS for better access performance. OSS-HDFS supports only internal network access.	OSS oss-cn-shanghai-internal.aliyuncs.com OSS-HDFS cn-hangzhou.oss-dls.aliyuncs.com

4. (Optional) Create a user mapping.

   Hologres lets you run the CREATE USER MAPPING command to map another user for accessing DLF 1.0 and OSS. For example, the owner of a foreign server can run CREATE USER MAPPING to specify a RAM user with the UID 123xxx to access external data in OSS.

   Ensure that the account has the required permissions to query the corresponding external data. For more information about the underlying principles, see postgres create user mapping.

   CREATE USER MAPPING FOR ACCOUNT_UID SERVER SERVER_NAME OPTIONS 
   (
       dlf_access_id 'YOUR_ACCESS_KEY', 
       dlf_access_key 'YOUR_ACCESS_SECRET',
       oss_access_id 'YOUR_ACCESS_KEY', 
       oss_access_key 'YOUR_ACCESS_SECRET'
   );

   Examples:

   -- Create a user mapping for the current user.
   CREATE USER MAPPING FOR current_user SERVER SERVER_NAME OPTIONS
   (
       dlf_access_id 'YOUR_ACCESS_KEY', 
       dlf_access_key 'YOUR_ACCESS_SECRET',
       oss_access_id 'YOUR_ACCESS_KEY', 
       oss_access_key 'YOUR_ACCESS_SECRET'
   );
   
   -- Create a user mapping for the RAM user 123xxx.
   CREATE USER MAPPING FOR "p4_123xxx" SERVER SERVER_NAME OPTIONS
   (
       dlf_access_id 'YOUR_ACCESS_KEY', 
       dlf_access_key 'YOUR_ACCESS_SECRET',
       oss_access_id 'YOUR_ACCESS_KEY', 
       oss_access_key 'YOUR_ACCESS_SECRET'
   );
   
   -- Drop the user mappings.
   DROP USER MAPPING FOR current_user SERVER SERVER_NAME;
   DROP USER MAPPING FOR "p4_123xxx" SERVER SERVER_NAME;

Read data from an OSS data lake

Take a DLF 1.0 data source as an example. You must prepare a metadata table in DLF 1.0 and ensure that data has been populated in this table. The following steps describe how to access OSS data through DLF 1.0 by using a foreign table in Hologres:

1. Create a foreign table in your Hologres instance.

   After the server is created, you can use the CREATE FOREIGN TABLE statement or the IMPORT FOREIGN SCHEMA statement to create one or more foreign tables in Hologres. This lets you read OSS data managed by DLF 1.0.

   Note

   If an OSS foreign table has the same name as a Hologres internal table, IMPORT FOREIGN SCHEMA skips creating that foreign table. In this case, use CREATE FOREIGN TABLE and specify a unique table name.

   Hologres can read partitioned tables from OSS. Columns of the TEXT, VARCHAR, and INT data types are supported as partition keys. The CREATE FOREIGN TABLE method maps only fields without storing data, so you can create partition fields as regular fields. The IMPORT FOREIGN SCHEMA method automatically handles table field mapping, so you do not need to manage the table fields.

   • Syntax

     -- Method 1
     CREATE FOREIGN TABLE [ IF NOT EXISTS ] oss_table_name ( [
     { column_name data_type }
       [, ... ]
       ] )
     SERVER SERVER_NAME
     OPTIONS
     (
       schema_name 'DLF_DATABASE_NAME',
       table_name 'DLF_TABLE_NAME'
     );
     
     -- Method 2
     IMPORT FOREIGN SCHEMA schema_name
         [ { limit to | except } ( table_name [, ...] ) ]
         from server SERVER_NAME
         into local_schema
         [ options ( option 'value' [, ... ] ) ]

   • Parameters

     Parameter	Description
     schema_name	The name of the metadatabase created in DLF 1.0.
     table_name	The name of the metadata table created in DLF 1.0.
     SERVER_NAME	The name of the server created in Hologres.
     local_schema	The name of the schema in Hologres.
     options	Options for the IMPORT FOREIGN SCHEMA statement. For more information, see IMPORT FOREIGN SCHEMA.

   • Examples

     • Create a single table.

       Create a foreign table to map data from the dlf_oss_test metadata table in the dlfpro DLF 1.0 metadatabase. The table is located in the public schema of Hologres. If the table already exists, it is updated.

       -- Method 1
       CREATE FOREIGN TABLE dlf_oss_test_ext
       (
         id text,
         pt text
       )
       SERVER SERVER_NAME
       OPTIONS
       (
         schema_name 'dlfpro',
         table_name 'dlf_oss_test'
       );
       
       -- Method 2
       IMPORT FOREIGN SCHEMA dlfpro LIMIT TO
       (
         dlf_oss_test
       )
       FROM SERVER SERVER_NAME INTO public options (if_table_exist 'update');

     • Create tables in bulk.

       Map all tables from the dlfpro DLF 1.0 metadatabase to the public schema in Hologres. This creates foreign tables in Hologres in bulk with the same names.

       • Import an entire database.

         IMPORT FOREIGN SCHEMA dlfpro
         FROM SERVER SERVER_NAME INTO public options (if_table_exist 'update');

       • Import multiple tables.

         IMPORT FOREIGN SCHEMA dlfpro
         (
           table1,
           table2,
           tablen
         )
         FROM SERVER SERVER_NAME INTO public options (if_table_exist 'update');

2. Query data.

   After the foreign table is created, you can query it directly to read data from OSS.

   • Non-partitioned table

     SELECT * FROM dlf_oss_test;

   • Partitioned table

     SELECT * FROM partition_table WHERE dt = '2013';