Hologres:How to access data stored in OSS-HDFS

Prerequisites

• Data Lake Formation (DLF) must be activated. For more information, see Quick start. For a list of supported regions, see Supported regions and endpoints.

• OSS-HDFS must be enabled and your data prepared. For more information, see Enable OSS-HDFS.

• The data lake acceleration feature must be enabled for your Hologres instance. For more information, see Environment configuration.

Procedure

1. Find the OSS-HDFS bucket endpoint.

   Configure the OSS-HDFS service endpoint in Hologres. You can find the endpoint in the OSS console, on the overview page of the bucket for which OSS-HDFS is enabled.

   In the Access Port section, find the value in the Bucket Endpoint column that corresponds to the HDFS Service row. The endpoint format is similar to xxx.cn-hangzhou.oss-dls.aliyuncs.com.

2. Create a foreign server and configure its connection details.

   Important

   If you need to access data from multiple environments, such as data in both OSS-HDFS and standard OSS, you must configure a separate foreign server for each data source. For a foreign table stored in OSS-HDFS, create a dedicated foreign server and set its oss_endpoint option to the OSS-HDFS endpoint. When you import or create the foreign table, specify this server. Similarly, for a foreign table that maps to data in standard OSS, create another foreign server and set its oss_endpoint to the corresponding OSS endpoint.

   After you find the bucket endpoint, run the following statements in Hologres to configure the endpoints for DLF and OSS:

   CREATE EXTENSION IF NOT EXISTS dlf_fdw;
   CREATE SERVER IF NOT EXISTS <servername> FOREIGN data wrapper dlf_fdw options (
       dlf_region 'cn-<region>',
       dlf_endpoint 'dlf-share.cn-<region>.aliyuncs.com',
       oss_endpoint '<bucket_name>.cn-<region>.oss-dls.aliyuncs.com' -- The OSS-HDFS bucket endpoint
   );

   The following table describes the parameters.

   Parameter	Description	Example
   servername	A custom server name.	dlf_server
   dlf_region	The region ID of your DLF service. For a list of supported regions, see Supported regions and endpoints.	cn-beijing
   dlf_endpoint	For better performance, we recommend using an internal endpoint for your DLF service. For a list of supported regions, see Supported regions and endpoints. If you need to access data across regions, you must use a public endpoint. Important Using a public endpoint incurs network fees and may affect performance. For more information about network fees, see Billing overview.	dlf-share.cn-beijing.aliyuncs.com
   oss_endpoint	The OSS-HDFS endpoint that you obtained in Step 1. OSS-HDFS supports only internal network access, which means cross-region access is not supported. Note For standard OSS storage, we recommend that you use an internal OSS endpoint for better performance. To interact with Hologres and DLF across regions, you must use a public endpoint. For more information, see Access OSS over IPv6.	bucket_nametest.cn-hangzhou.oss-dls.aliyuncs.com

3. Create a foreign table to read from and write to OSS-HDFS.

   • Create a single table: Create a foreign table in the public schema of your Hologres instance that maps to the dlf_oss_test metadata table in the dlfpro DLF metadatabase.

     -- Method 1
     CREATE FOREIGN TABLE dlf_oss_test
     (
       id text,
       pt text
     )
     SERVER dlf_server -- The foreign server that you created.
     options
     (
       schema_name 'dlfpro',
       table_name 'dlf_oss_test'
     );
     -- Method 2
     IMPORT FOREIGN SCHEMA dlfpro LIMIT TO
     (
       dlf_oss_test
     )
     FROM SERVER dlf_server INTO public options (if_table_exist 'update');

   • Create tables in bulk: Map all tables from the DLF metadatabase dlfpro to the public schema in Hologres. This creates corresponding foreign tables in Hologres with the same names as the source tables.

     • Import an entire database:

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

     • Import multiple tables:

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

4. Query the data.

   After you create the foreign table, you can find it in the corresponding schema in Hologres or in the table structure directory in HoloWeb. You can query this foreign table directly to read data in OSS. For foreign tables in CSV, Parquet, or ORC format, you can also write data back to the table.

   • Query a non-partitioned table

     SELECT * FROM dlf_oss_test;

   • Query a partitioned table

     SELECT * FROM <partition_table> where dt = '2013';

For more information, see Procedure.