create a foreign server, create a foreign table - Hologres

In Hologres, you can configure a foreign server to specify the connection information of an external data source, such as an Object Storage Service (OSS) bucket. Then, you can create a foreign table to access and query data across systems. This topic describes how to use the HoloWeb visualization tool to create a foreign server, manage foreign tables, and preview data.

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 left-side navigation pane, click Instances. On the page that appears, find the desired instance and click Data Lake Acceleration in the Actions column to enable data lake acceleration.
In the left-side navigation pane of the Hologres console, click Go to HoloWeb to go to the HoloWeb console.
In the HoloWeb console, connect to the desired database. In the top navigation bar, click Metadata Management.

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

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 the created DLF catalog from the drop-down list. If no DLF catalog is created, click Create in DLF Console.
OSS EndPoint	The OSS endpoint. By default, the endpoint of the native OSS in the current region is used. You can select an OSS-HDFS endpoint from the drop-down list. If no OSS bucket is created, click Create in OSS Console. Note You can obtain the OSS endpoint from Regions and endpoints. You can obtain the OSS-HDFS endpoint on the Overview page of the bucket for which OSS-HDFS is enabled.

Click Submit. The foreign server is created.

Create a foreign table

Log on to the Hologres console.
In the top navigation bar, select a region from the drop-down list.
In the left-side navigation pane of the Hologres console, click Go to HoloWeb to go to the HoloWeb console.
In the top navigation bar, 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: The system skips the tables that contain data of unsupported data types and imports data from other tables.

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: The system skips the tables that contain data of unsupported data types and imports data from other tables.
	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. You can refresh the left-side instance list to view the created foreign table in the schema that you selected.
Optional. Double-click the name of the foreign table that you created, click Query Table in the upper-right corner. In the SQL query window, execute standard PostgreSQL statements to develop data.
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 left-side navigation pane of the HoloWeb console, click Instances Connected.
Click the name of the desired instance, the name of the desired database, and then Foreign Tables. All created foreign tables are displayed.
Double-click the name of the desired foreign table. On the page that appears, click Edit Table.
Modify the fields or partitions of the foreign table based on your business requirements.
Click Submit.

Rename a foreign table

In the left-side navigation pane of the HoloWeb console, find the desired foreign table in the Instances Connected list by following Steps 1 to 2 in the Edit a foreign table section.
Right-click the name of the 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-side navigation pane of the HoloWeb console, find the desired foreign table in the Instances Connected list by following Steps 1 to 2 in the Edit a foreign table section.
Right-click the name of the foreign table and select Delete Table.
In the Delete Table message, click OK.

Preview data

In the left-side navigation pane of the HoloWeb console, find the desired foreign table in the Instances Connected list by following Steps 1 to 2 in the Edit a foreign table section.
Double-click the name of the foreign table. On the 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 AliyunDLF-related permissions on DLF API operations.

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