Create OSS Foreign Tables in HoloWeb for Data Lake Queries - Hologres

In Hologres, a Foreign Server defines connection details for accessing data in Object Storage Service (OSS) through a foreign-data wrapper. Once the server is configured, you can map OSS tables managed by Data Lake Formation (DLF) to Hologres foreign tables and query them using standard PostgreSQL syntax—without copying the data into Hologres.

This topic explains how to use the HoloWeb visual tool to create a Foreign Server, create foreign tables, and manage them after creation.

How it works

Setting up cross-system data access requires two main steps:

Create a Foreign Server — define the DLF catalog and OSS endpoints that Hologres uses to locate your external data.
Create foreign tables — map one or more DLF tables to Hologres, choosing the scope that fits your use case: an entire database, a selected set of tables, or a single table.

After creation, foreign tables appear in the left navigation pane of HoloWeb and can be queried, edited, renamed, deleted, or previewed.

Prerequisites

Before you begin, ensure that you have:

An OSS bucket with data imported. See Quick Start.
A DLF catalog that already contains a database and tables. See Data catalog and Databases, tables, and functions.
USAGE permission on the foreign-data wrapper you plan to use. See Grant permissions.

Limitations

Hologres does not support cross-region queries on foreign table data.

Create a Foreign Server

A Foreign Server stores the connection details—DLF endpoint, DLF catalog, and OSS endpoint—that Hologres uses to reach your external data source. You can also define per-user connection details using user mapping. For the SQL-based approach, see Create a Foreign Server.

Log on to the Hologres Management Console.
In the top menu bar, select the target region.
In the left navigation pane, click Instances. In the Actions column of the target instance, click Data Lake Acceleration to enable data lake acceleration.
Click Go to HoloWeb.
After connecting to the target database, click Metadata Management in the top menu bar.

Click OSS Data Lake Acceleration > Create Foreign Server.

Important

The Foreign Server name must be unique within the database.

Parameter	Description
Server Name	A unique name for the Foreign Server within the database.
DLF EndPoint	Defaults to the DLF endpoint for the current region. See Supported regions and endpoints.
DLF Catalog	Select an existing DLF catalog. If the list is empty, click Create in DLF Console to create one.
OSS EndPoint	Defaults to the native OSS endpoint for the current region. To use an OSS-HDFS endpoint instead, select it from the drop-down list. If no OSS bucket exists, click Create in OSS Console. For endpoint values, see Regions and endpoints (OSS) or the Overview page of any OSS-HDFS-enabled bucket (OSS-HDFS).

Click Submit.

Create a foreign table

Foreign tables can be created at three levels of scope. Choose the mode that matches your use case:

Mode	What it creates
Entire Database Acceleration	All tables in the specified DLF database
Partial Acceleration	A selected subset of tables, found by fuzzy name search
Single Table Acceleration	One specific table, with full control over the target table name

Steps:

Log on to the Hologres Management Console.
In the top menu bar, select the target region.
Click Go to HoloWeb.
Click Metadata Management in the top menu bar.
Click OSS Data Lake Acceleration > Create Foreign Table.

Select an acceleration mode and fill in the parameters.

Entire Database Acceleration

Parameter	Description
Server Name	The Foreign Server that defines connection details for the external data source.
DB Name	The database name under the DLF catalog specified in the Foreign Server.
Holo Schema	The target schema in Hologres. Defaults to public.
Table Name Conflict	Action when a table with the same name already exists: Ignore and continue creating other tables skips the conflict and proceeds; Update and modify the existing table updates the existing table; Error and stop duplicate creation stops creation and returns an error.
Unsupported Data Type	Action when a table contains unsupported column types: Ignore and Skip Table of Unsupported Fields skips those tables and continues; Error and fail import stops the import and returns an error.

Partial Acceleration

Partial Acceleration supports the same parameters as Entire Database Acceleration, plus the following:

Parameter	Description
Search	Enter a table name or partial name to search. Select the tables to include from the results.

Single Table Acceleration

Parameter	Description
Server Name	The Foreign Server that defines connection details for the external data source.
DB Name	The database name under the DLF catalog specified in the Foreign Server.
Table Name (DLF)	The name of the source table in the DLF database.
Schema	The target schema in Hologres. Defaults to public.
Table Name (Hologres)	The name of the new foreign table in Hologres. Auto-filled from the DLF table name—rename it if needed.
Target Table Description	An optional description for the foreign table.

When creating foreign tables, all fields from the external source table are included by default. To include only specific fields, use Single Table Acceleration or create the table with a SQL statement. See CREATE FOREIGN TABLE.

Click Submit. After refreshing, the new foreign table appears under the corresponding schema in the left navigation pane.
(Optional) Double-click the foreign table, then click Query Table to open an SQL query window and query the data using standard PostgreSQL syntax.

Manage Foreign Servers using SQL

After creation, use SQL to view, modify, or delete a Foreign Server.

View all Foreign Servers:

SELECT * FROM pg_foreign_server;

Modify the DLF catalog of a Foreign Server:

ALTER SERVER <foreignserver_name> OPTIONS (ADD|SET|DROP dlf_catalog ['<catalog_name>']);

Parameter	Description
`foreignserver_name`	Name of the Foreign Server to modify.
`ADD`	Add a DLF catalog to the Foreign Server.
`SET`	Modify the DLF catalog of the Foreign Server.
`DROP`	Remove the DLF catalog from the Foreign Server. Example: `ALTER SERVER foreign_server_test OPTIONS (DROP dlf_catalog);`

Delete a Foreign Server:

DROP SERVER <foreignserver_name> CASCADE;

CASCADE also deletes all dependent objects, such as foreign tables that reference this server.

Manage foreign tables

Edit a foreign table

In the left navigation pane of HoloWeb, expand Logged-in Instances and navigate to the target instance > target database > target schema > Foreign Tables.
Double-click the target foreign table.
On the details page, click Edit Table.
Modify the Fields or Partitions mapped from the external source table.
Click Submit.

Rename a foreign table

In the Logged-in Instances list, locate the target foreign table (see steps 1–2 in Edit a foreign table).
Right-click the foreign table and select Rename.
In the Modify Table Name dialog box, enter the new name.
Click OK.

Delete a foreign table

In the Logged-in Instances list, locate the target foreign table (see steps 1–2 in Edit a foreign table).
Right-click the foreign table and select Delete Table.
In the Delete Table dialog box, click OK.

Preview data

In the Logged-in Instances list, locate the target foreign table (see steps 1–2 in Edit a foreign table).
Double-click the foreign table, then click Data Preview on the details page.

Troubleshooting

Error: RAM DLF API permission denied

If you see an error similar to the following when accessing a foreign table in HoloWeb:

You are not authorized to perform the operation. Please use RAM to authorize the operation. Action: [dlf:xxx].

Grant the Resource Access Management (RAM) user or role the required AliyunDLF-related OpenAPI permissions.

What's next

Create a Foreign Server using SQL: Create a Foreign Server
Create a foreign table using SQL: Create a foreign table in a Hologres instance
Foreign table DDL reference: ALTER FOREIGN TABLE and DROP FOREIGN TABLE