• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. ALTER SERVER

ALTER SERVER

Function

ALTER SERVER adds, modifies, or deletes the parameters of an existing server. You can query existing servers from pg_foreign_server.

Only system administrators and the owner of the server can run this statement.

Precautions

None

Syntax

  • Change the parameters for an external server.
ALTER SERVER server_name [ VERSION 'new_version' ]
    [ OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ] ) ];
NOTE:

Currently, only the SET operation is supported on an HDFS server. ADD and DROP operations are not supported. The syntax for SET and DROP operations is retained for later use.

  • Change the owner of an external server.
ALTER SERVER server_name 
    OWNER TO new_owner;
  • Change the name of an external server.
ALTER SERVER server_name 
    RENAME TO new_name;

Parameter Description

The parameters for modifying the server are as follows:

  • server_name

    Specifies the name of the server to be modified.

  • new_version

    Indicates the new version of the server.

  • The server parameters in OPTIONS are as follows:
    • address

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

      NOTE:
      • address is mandatory for HDFS servers. Therefore, ADD and DROP operations are not supported.
      • address only supports IPv4 addresses in dot-decimal notation, and an address string cannot contain spaces. Groups of addresses are separated by comma. Using a colon (:) to separate the IP address and port. You are advised to set two groups of IP addresses and ports to specify the addresses of the HDFS primary and standby NameNodes, respectively.
    • hdfscfgpath

      Specifies the HDFS cluster configuration file.

      NOTE:
      • If HDFS is in security mode, hdfscfgpath is mandatory.
      • If you set hdfscfgpath, you can only set one value for path.
    • region

      Specifies the IP address or domain name of the OBS server. This parameter is available only when type is OBS.

  • new_owner

    Indicates the new owner of the server. To change the owner, you must be the owner of the external server and a direct or indirect member of the new owner role, and must have the USAGE permission on the encapsulator of the external server.

  • new_name

    Indicates the new name of the server.

Examples

Create the hdfs_server server, and hdfs_fdw is the built-in foreign data wrapper.
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000', hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS');

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server';
   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                              srvoptions                                           
    
-------------+----------+--------+---------+------------+--------+---------------------------------------------------------------------------------------------------
----
 hdfs_server |       10 |  13332 |         |            |        | {"address=10.10.0.100:25000,10.10.0.101:25000",hdfscfgpath=/opt/hadoop_client/HDFS/hadoop/etc/hadoop}
(1 row)
Change the current name to the IP address of the HDFS server.
ALTER SERVER hdfs_server OPTIONS ( SET address '10.10.0.110:25000,10.10.0.120:25000');

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server';
   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                              srvoptions
-------------+----------+--------+---------+------------+--------+-------------------------------------------------------------------------------------------------------
 hdfs_server |       10 |  13167 |         |            |        | {"address=10.10.0.110:25000,10.10.0.120:25000",hdfscfgpath=/opt/hadoop_client/HDFS/hadoop/etc/hadoop}
(1 row)
--Change the current name to hdfscfgpath of the HDFS server.
ALTER SERVER hdfs_server OPTIONS ( SET hdfscfgpath '/opt/bigdata/hadoop');

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server';
   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                   srvoptions                                    
-------------+----------+--------+---------+------------+--------+---------------------------------------------------------------------------------
 hdfs_server |       10 |  13332 |         |            |        | {"address=10.10.0.110:25000,10.10.0.120:25000",hdfscfgpath=/opt/bigdata/hadoop}
(1 row)
-- Delete hdfs_server.
DROP SERVER hdfs_server;

Relevant Links

CREATE SERVER DROP SERVER