Create a foreign server, create a foreign table - Hologres

Prerequisites

An OSS bucket is created and data is imported into the OSS bucket. For more information, see Get started with OSS.
A Data Lake Formation (DLF) catalog is created. For more information, see Data catalog.
Your account is granted the USAGE permission on the foreign data wrapper (FDW) that you use. For more information, see Permission authorization.
A database and a table already exist in the DLF catalog of the foreign server. For more information about how to create a database and a table, see Database tables and functions.

Limits

The external data source and the mapped Hologres foreign table must reside in the same region.

Create a foreign server

In Hologres, a foreign server is used to define the connection information that an FDW uses to access external data sources, such as an OSS bucket, for cross-system data access and queries. You can also create user mappings to define user-specific connection information. For more information, see Create a foreign server.

Log on to the Hologres console.
In the top navigation bar, select a region from the drop-down list.
In the navigation pane on the left, click Instances. In the Actions column of your target instance, click Lake Acceleration to enable this feature.
Click Go to HoloWeb to open the HoloWeb development page.
After you log in to the target database, click Metadata Management in the top navigation bar.

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

The Server Name must start with a letter or an underscore (_), contain only letters, digits, and underscores, and be 1 to 63 characters long.

The following table describes the parameters.

Parameter	Description
Server Name	The name of the foreign server that you want to create. Important The name of the foreign server must be unique in the database.
DLF EndPoint	The endpoint of DLF. By default, the endpoint of DLF in the current region is used. For more information about the supported regions and endpoints of DLF, 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 to create one.
OSS EndPoint	The native OSS Endpoint in the current region is used by default. You can select an OSS-HDFS Endpoint from the drop-down list. If you do not have an OSS bucket, click Create in OSS Console. Note You can obtain the OSS endpoint from Regions and endpoints. You can find the OSS-HDFS Endpoint on the Overview page of an OSS-HDFS-enabled bucket in the OSS console.

Click Submit to create the foreign server.

Create a foreign table

Log on to the Hologres console.
In the top navigation bar, select a region from the drop-down list.
Click Go to HoloWeb to open the HoloWeb development page.
In the top navigation bar of HoloWeb, click Metadata Management.

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

You can specify Entire Project, Selected Tables, or Single Table as the acceleration method. The following tables describe the parameters related to each acceleration method.

Entire Project

Category	Parameter	Description
Acceleration Method	Entire Project	A method that creates Hologres foreign tables for all source tables in the specified database in DLF.
DLF Data Source	Server Name	The name of the foreign server that you created. This parameter is used to specify the connection information of the external data source.
DLF Data Source	DB Name	The name of the database in the DLF catalog that is specified in the foreign server.
Destination Location	Holo Schema	The default value is public. You can select another schema on which you have permissions from the drop-down list.
Advanced Settings	Processing Rule for Table Name Conflicts	The processing rule if a foreign table that you want to create is named the same as an existing foreign table in the database. Valid values: Ignore Conflicts and Continue Creating Tables: The system skips the creation of this table and continues to create other tables. Change Table Names: The system updates data in the existing foreign table. Report Error and Create No Table: The system reports an error message and stops creating tables.
Advanced Settings	Processing Rule for Unsupported Data Types	The processing rule if specific data types in the source tables are not supported by Hologres. Valid values: Report Error and Stop Import: The system reports an error message, and data import fails. Ignore and Skip Table of Unsupported Fields: Skips tables with unsupported fields and continues the import.

Selected Tables

Category	Parameter	Description
Acceleration Method	Selected Tables	A method that creates Hologres foreign tables for specified source tables. You can search for the tables by enter a table name in the search box for fuzzy match.
DLF Data Source	Server Name	The name of the foreign server that you created. This parameter is used to specify the connection information of the external data source.
DLF Data Source	DB Name	The name of the database in the DLF catalog that is specified in the foreign server.
Destination Location	Holo Schema	The default value is public. You can select another schema on which you have permissions from the drop-down list.
Advanced Settings	Processing Rule for Table Name Conflicts	The processing rule if a foreign table that you want to create is named the same as an existing foreign table in the database. Valid values: Ignore Conflicts and Continue Creating Tables: The system skips the creation of this table and continues to create other tables. Change Table Names: The system updates data in the existing foreign table. Report Error and Create No Table: The system reports an error message and stops creating tables.
	Processing Rule for Unsupported Data Types	The processing rule if specific data types in the source tables are not supported by Hologres. Valid values: Report Error and Stop Import: The system reports an error message, and data import fails. Ignore and Skip Table of Unsupported Fields: Skips tables with unsupported fields and continues the import.
	Search	Performs a fuzzy match to search for tables by table name and then selects tables.

Single Table

Category	Parameter	Description
Acceleration Method	Single Table	A method that creates a Hologres foreign table for a specified source table.
DLF Data Source	Server Name	The name of the foreign server that you created. This parameter is used to specify the connection information of the external data source.
	DB Name	The name of the database in the DLF catalog that is specified in the foreign server.
	Table Name	The name of the table in the DLF database for which you want to create a foreign table.
Destination Hologres Table	Schema	The default value is public. You can select another schema on which you have permissions from the drop-down list.
	Table Name	The name of the Hologres foreign table that you want to create. Note After you specify the Table Name parameter in the DLF Data Source section, the same value is automatically entered for this parameter. You can rename the Hologres foreign table.
	Destination Table Description	The description of the Hologres foreign table that you want to create. You can enter a custom description.

Click Submit. Then, refresh the navigation pane on the left to see the new foreign table under the corresponding schema.
(Optional) Double-click the created foreign table and click Query Table to open the SQL editor. You can then query the data using standard PostgreSQL syntax.

Note
After you specify the Table Name parameter in the DLF Data Source section, all fields in the external source table are displayed. When you create a foreign table for the external source table, all fields are included in the foreign table by default. If you want to include specified fields in the foreign table, set Acceleration Method to Single Table, or execute SQL statements to create a foreign table. For more information about how to create foreign tables by executing SQL statements, see CREATE FOREIGN TABLE.

More operations on foreign servers by using SQL statements

After a foreign server is created, you can execute SQL statements to view, modify, or drop the foreign server.

View a created foreign server.
```
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>']) ;

The following table describes the parameters.

Parameter	Description
foreignserver_name	The name of the foreign server that you want to modify.
ADD	The operation that is performed to add a DLF catalog to the foreign server.
SET	The operation that is performed to modify the DLF catalog of the foreign server.
DROP	The operation that is performed to drop the DLF catalog of the foreign server. Example: `ALTER SERVER foreign_server_test OPTIONS (DROP dlf_catalog) ;`.

Drop a foreign server.

DROP SERVER <foreignserver_name> CASCADE;

The following table describes the parameters.

Parameter	Description
foreignserver_name	The name of the foreign server that you want to drop.
CASCADE	The cascade operation that is performed to drop objects that depend on the foreign server when you drop the foreign server. For example, foreign tables that depend on the foreign server are dropped.

More operations on foreign tables

Edit a foreign table

In the navigation pane on the left of HoloWeb, expand the Instances Connected list.
Navigate to your target instance > target database > target schema, and then click Foreign Tables to view all created foreign tables.
Double-click the target foreign table and click Edit Table on the details page.
You can modify the Fields or Partitions mapped from the external source table.
Click Submit to save your changes.

Rename a foreign table

To find the target foreign table in the Instances Connected list in the left navigation bar of the HoloWeb page, see Steps 1 and 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 left navigation bar of the HoloWeb page, find the target foreign table in the Instances Connected list. For the steps to find the table, see steps 1 and 2 in Edit Foreign Table.
Right-click the target foreign table and select Delete Table.
In the Delete Table dialog box, click OK.

Preview data

In the Instances Connected list in the left navigation bar of the HoloWeb page, find the target foreign table by following steps 1 and 2 in Edit a foreign table.
Double-click the target foreign table. On the details page that appears, click the Data Preview tab.

FAQ

Problem description: The following error message appears when a RAM user is used to access a foreign table in HoloWeb. The error message indicates that the RAM user does not have permissions to call DLF API operations. The error message contains the following information:
```
You are not authorized to perform the operation. Please use RAM to authorize the operation.Action: [dlf:xxx].
```
Solution: Grant the RAM user the required AliyunDLF permissions for API operations in the RAM console.

References

You can also use SQL statements to create foreign servers. For more information, see Create a foreign server.
You can also use SQL statements to create foreign tables. For more information, see Create a foreign table in a Hologres instance.
For more information about DDL statements for foreign tables, see the following topics:
- ALTER FOREIGN TABLE
- DROP FOREIGN TABLE