Creating a Foreign Server

In this example, use common user dbuser created in (Optional) Creating a User and a Database and Granting the User Foreign Table Permissions to connect to mydatabase created by the user. You need to connect to the database through the database client tool provided by GaussDB(DWS).

You can use the gsql client to log in to the database in either of the following ways:

• If you have logged in to the gsql client, run the following command to switch the database and user:

  1	\c mydatabase dbuser;

  Enter the password as prompted.

• If you have not logged in to the gsql client or have exited the gsql client by running the \q command, run the following command to reconnect to it:

  1	gsql -d mydatabase -h 192.168.2.30 -U dbuser -p 8000 -r

  Enter the password as prompted.

For example, run the following command to create a foreign server named obs_server.

// Hard-coded or plaintext AK and SK are risky. For security purposes, encrypt your AK and SK and store them in the configuration file or environment variables.

1
2
3
4
5
6
7
8

CREATE SERVER obs_server FOREIGN DATA WRAPPER dfs_fdw 
OPTIONS ( 
  address 'obs.ap-southeast-1.myhuaweicloud.com' , 
  ACCESS_KEY 'access_key_value_to_be_replaced', 
  SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', 
  encrypt 'on',
  type 'obs' 
);

Mandatory parameters are described as follows:

• Name of the foreign server

  You can customize a name.

  In this example, the name is set to obs_server.

• FOREIGN DATA WRAPPER

  fdw_name can be hdfs_fdw or dfs_fdw, which already exists in the database.

• OPTIONS parameters
  • address

    Specifies the endpoint of the OBS service.

    Obtain the address as follows:

    1. Obtain the OBS path by performing 2 in Preparing Data on OBS.
    2. The OBS path displayed on OBS is the endpoint of the OBS service, that is, obs.example.com.
  • (Optional) Access keys (AK and SK)
    GaussDB(DWS) needs to use the access keys (AK and SK) to access OBS. Therefore, you must obtain the access keys first.

    • (Mandatory) access_key: specifies users' AK information.
    • (Mandatory) secret_access_key: specifies users' SK information.

    For details about how to obtain the access keys, see Creating Access Keys (AK and SK).

  • type

    Its value is obs, which indicates that dfs_fdw connects to OBS.

1

SELECT * FROM pg_foreign_server WHERE srvname='obs_server';

The server is successfully created if the returned result is as follows:

1
2
3
4
5
6

  srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                                                                      srvoptions

------------+----------+--------+---------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------
 obs_server |    24661 |  13686 |         |            |        | {address=xxx.xxx.x.xxx,access_key=xxxxxxxxxxxxxxxxxxxx,type=obs,secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx}
(1 row)

You can use the gsql client to log in to the database in either of the following ways:

You can use either of the following methods to create the connection:

• If you have logged in to the gsql client, run the following command to switch the database and user:

  1	\c postgres dbadmin;

  Enter the password as prompted.

• If you have not logged in to the gsql client or have exited the gsql client by running the \q command, run the following command to reconnect to it:

  1	gsql -d postgres -h 192.168.2.30 -U dbadmin -p 8000 -W password -r

1

SELECT * FROM pg_foreign_server;

The returned result is as follows:

1
2
3
4
5
6

                     srvname                      | srvowner | srvfdw | srvtype | srvversion | srvacl |                                                     srvoptions
--------------------------------------------------+----------+--------+---------+------------+--------+---------------------------------------------------------------------------------------------------------------------
 gsmpp_server                                     |       10 |  13673 |         |            |        |
 gsmpp_errorinfo_server                           |       10 |  13678 |         |            |        |
 hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca |    16476 |  13685 |         |            |        | {"address=192.168.1.245:25000,192.168.1.218:25000",hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692ca,type=hdfs}
(3 rows)

In the query result, each row contains the information about a foreign server. The foreign server associated with the MRS data source connection contains the following information:

• The value of srvname contains hdfs_server and the ID of the MRS cluster, which is the same as the MRS ID in the cluster list on the MRS management console.
• The address parameter in the srvoptions field contains the IP addresses and ports of the active and standby nodes in the MRS cluster.

You can find the foreign server you want based on the above information and record the values of its srvname and srvoptions.

For details about the syntax for creating foreign servers, see CREATE SERVER. For example:

1
2
3
4
5
6
7

CREATE SERVER hdfs_server_8f79ada0_d998_4026_9020_80d6de2692cahdfs_server FOREIGN DATA WRAPPER HDFS_FDW 
OPTIONS 
(
address '192.168.1.245:25000,192.168.1.218:25000', 
hdfscfgpath '/MRS/8f79ada0-d998-4026-9020-80d6de2692ca',
type 'hdfs'
);

Mandatory parameters are described as follows:

• Name of the foreign server

  You can customize a name.

  In this example, specify the name to the value of the srvname field recorded in 2, such as hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca.

  Resources in different databases are isolated. Therefore, the names of foreign servers in different databases can be the same.

• FOREIGN DATA WRAPPER

  This parameter can only be set to HDFS_FDW, which already exists in the database.

• OPTIONS parameters
  Set the following parameters to the values under srvoptions recorded in 2.

  • address

    Specifies the IP address and port number of the primary and standby nodes of the HDFS cluster.

  • hdfscfgpath

    Specifies the configuration file path of the HDFS cluster. This parameter is available only when type is HDFS. You can set only one path.

  • type

    Its value is hdfs, which indicates that HDFS_FDW connects to HDFS.

1

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca';

The server is successfully created if the returned result is as follows:

1
2
3
4

                     srvname                      | srvowner | srvfdw | srvtype | srvversion | srvacl |                                                     srvoptions
--------------------------------------------------+----------+--------+---------+------------+--------+---------------------------------------------------------------------------------------------------------------------
 hdfs_server_8f79ada0_d998_4026_9020_80d6de2692ca |    16476 |  13685 |         |            |        | {"address=192.168.1.245:25000,192.168.1.218:25000",hdfscfgpath=/MRS/8f79ada0-d998-4026-9020-80d6de2692ca,type=hdfs}
(1 row)