Create Foreign Server, create foreign table - Hologres - Alibaba Cloud Documentation Center

In Hologres, configure a Foreign Server to specify connection details for an external data source such as Object Storage Service (OSS). This enables you to create foreign tables and query data across systems. This topic describes how to use the HoloWeb visual tool to create a Foreign Server, manage foreign tables, and preview data.

Prerequisites

Create an OSS bucket and import data. For more information, see Quick Start.
A DLF catalog has been created. For more information, see data catalog.
Have USAGE permission for the foreign-data wrapper you plan to use. For authorization instructions, see Grant permissions.
The DLF catalog used by your Foreign Server must already contain a database and tables. For steps to create them, see Databases, tables, and functions.

Limits

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

Create a Foreign Server

In Hologres, a Foreign Server specifies connection details for accessing external data sources—such as Object Storage Service (OSS)—through a foreign-data wrapper. This enables cross-system data access and querying. You can also use user mapping to define connection details for specific users. For more information, see Create a Foreign Server.

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

On the Metadata Management page, click OSS Data Lake Acceleration > Create Foreign Server.

Parameter descriptions are as follows:

Parameter	Description
Server Name	The name of the Foreign Server. Important The Foreign Server name must be unique within the database.
DLF EndPoint	Defaults to the DLF endpoint for the current region. For more information, see Supported regions and endpoints.
DLF Catalog	Select an existing DLF catalog from the drop-down list. If the list is empty, click Create in DLF Console.
OSS EndPoint	Defaults to the native OSS endpoint for the current region. To use an OSS-HDFS endpoint, select it from the drop-down list. If you have not created an OSS bucket, click Create in OSS Console. Note OSS Endpoint: Obtain it from Regions and endpoints. OSS-HDFS Endpoint: Find it on the Overview page of any OSS bucket where OSS-HDFS is enabled.

Click Submit to complete Foreign Server creation.

Create a foreign table

Log on to the Hologres Management Console.
From the left side of the top menu bar, select a region.
Click Go to HoloWeb to open the HoloWeb development page.
In the HoloWeb top menu bar, click Metadata Management.

On the Metadata Management page, click OSS Data Lake Acceleration > Create Foreign Table.

Select one of the following acceleration modes as needed: Entire Database Acceleration, Partial Acceleration, or Single Table Acceleration. Parameter descriptions are as follows:

Entire Database Acceleration

Category	Parameter name	Description
Acceleration Mode	Entire Database Acceleration	Creates all tables under the specified DLF database as Hologres foreign tables.
DLF Data Source	Server Name	The existing Foreign Server that defines connection details for the external data source.
DLF Data Source	DB Name	The database name under the DLF catalog specified in the Foreign Server.
Target Location	Holo Schema	Defaults to public. Select another schema with appropriate permissions from the drop-down list.
Advanced Options	Table Name Conflict	If a table with the same name already exists during creation, choose one of the following actions: Ignore and continue creating other tables: Skip the current table and proceed with others. Update and modify the existing table: Update data in the existing table. Error and stop duplicate creation: Return an error and halt further creation.
Advanced Options	Unsupported Data Type	If an unsupported data type exists during table creation, choose one of the following actions: Error and fail import: Return an error and fail the import. Ignore and Skip Table of Unsupported Fields: Skip tables with unsupported fields and continue importing.

Partial Acceleration

Category	Parameter Name	Description
Acceleration Mode	Partial Acceleration	Select specific tables to create as foreign tables. Use fuzzy search by table name.
DLF Data Source	Server Name	The existing Foreign Server that defines connection details for the external data source.
DLF Data Source	DB Name	The database name under the DLF catalog specified in the Foreign Server.
Target Location	Holo Schema	Defaults to public. Select another schema with appropriate permissions from the drop-down list.
Advanced Options	Table Name Conflict	If a table with the same name already exists during creation, choose one of the following actions: Ignore and continue creating other tables: Skip the current table and proceed with others. Update and modify the existing table: Update data in the existing table. Error and stop duplicate creation: Return an error and halt further creation.
	Unsupported Data Type	If an unsupported data type exists during table creation, choose one of the following actions: Error and fail import: Return an error and fail the import. Ignore and Skip Table of Unsupported Fields: Skip tables with unsupported fields and continue importing.
	Search	Use fuzzy search by table name to find and select target tables.

Single Table Acceleration

Category	Parameter name	Description
Acceleration Mode	Single Table Acceleration	Creates a Hologres foreign table for a single specified table.
DLF Data Source	Server Name	The existing 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	The table name created in the DLF database.
Hologres Target Table	Schema	Defaults to public. Select another schema with appropriate permissions from the drop-down list.
	Table Name	The name of the new Hologres foreign table. Note After entering the target DLF table name, the system auto-fills it. Rename it if needed.
	Target Table Description	A custom description for the new Hologres foreign table.

Click Submit. The new foreign table appears under the corresponding schema on the left after refreshing.
(Optional) Double-click the created foreign table, then click Query Table to open an SQL query window and develop using standard PostgreSQL syntax.

Note
After entering the table name, all fields from the external source table appear. By default, all fields are created in the foreign table. To create only specific fields, use Single Table Acceleration or an SQL statement. For SQL-based foreign table creation, see CREATE FOREIGN TABLE.

Additional Foreign Server operations (SQL method)

After successful creation, use SQL statements to view, modify, or delete your Foreign Server. Syntax is as follows:

View created 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 descriptions are as follows:

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;
```
Parameter descriptions are as follows:

Parameter

Description

foreignserver_name

Name of the Foreign Server to delete.

CASCADE

When deleting the Foreign Server, also delete dependent objects such as foreign tables.

Additional foreign table operations

Edit a foreign table

In the navigation pane on the left of the HoloWeb page, select the Logged-in Instances list.
Click Target Instance > Target Database > Target Schema, then click Foreign Tables to view all created foreign tables.
Double-click the target foreign table, then click Edit Table on the details page.
Modify the Fields or Partitions mapped from the external source table as needed.
Click Submit to save changes to the foreign table.

Rename a foreign table

In the Logged-in Instances list in the left navigation pane of HoloWeb, locate your target foreign table. For steps, see steps 1–2 in Edit a foreign table.
Right-click the target foreign table and select Rename.
In the Modify Table Name dialog box, enter a new name.
Click OK.

Delete a foreign table

In the Logged-in Instances list in the left navigation pane of HoloWeb, locate your target foreign table. For steps, see steps 1–2 in Edit a foreign table.
Right-click the target foreign table and select Delete Table.
In the Delete Table dialog box, click OK.

Data preview

In the Logged-in Instances list in the left navigation pane of HoloWeb, locate your target foreign table. For steps, see steps 1–2 in Edit a foreign table.
Double-click the target foreign table, then click Data Preview on the foreign table details page.

FAQ

Issue: When accessing a foreign table in HoloWeb, you receive a RAM DLF API permission error. The error message includes:
```
You are not authorized to perform the operation. Please use RAM to authorize the operation.Action: [dlf:xxx].
```
Solution: Grant AliyunDLF-related OpenAPI permissions in RAM.

References

You can also create a Foreign Server using SQL. For more information, see Create a Foreign Server.
You can also create a foreign table using SQL. For more information, see Create a foreign table in a Hologres instance.
For more details about foreign table DDL statements, see:
- ALTER FOREIGN TABLE
- DROP FOREIGN TABLE