• Data Warehouse Service

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

CREATE SERVER

Function

CREATE SERVER creates an external server.

An external server stores HDFS cluster information and OBS server information. It can also be a dummy server storing information about computing resource pools. This syntax can be used only for SQL on Hadoop.

Precautions

By default, only the system administrator can create foreign server. Otherwise, creating a server requires USAGE permission on the foreign-data wrapper being used. The syntax is as follows:

GRANT USAGE ON FOREIGN DATA WRAPPER fdw_name TO username;
ALTER USER username USEFT; 

fdw_name is the name of FOREIGN DATA WRAPPER, and username is the user name of creating SERVER.

Syntax

CREATE SERVER server_name 
    FOREIGN DATA WRAPPER fdw_name
    OPTIONS ( { option_name ' value ' } [, ...] ) ;

Parameter Description

  • server_name

    Specifies the name of the server to be created.

    Value range: The length cannot be greater than 64.

  • FOREIGN DATA WRAPPER fdw_name

    Specifies the name of the foreign data wrapper.

    Value range: fdw_name is the data wrapper created by the system in the initial phase of the database. Currently, fdw_name can only be hdfs_fdw for HDFS cluster.

  • OPTIONS ( { option_name ' value ' } [, ...] )

    Specifies the parameters for the server. The detailed parameter description is as follows:

    • address

      Specifies the IP address and port number of the primary and standby nodes of the HDFS cluster, or information about the computing resource pool stored on the dummy server. The OBS server specifies the IP address or domain name of OBS.

      NOTE:
      • address is mandatory for servers whose type is HDFS or dummy.
      • address option of HDFS supports only 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 and port to represent the addresses of the primary and standby NameNodes, respectively. The set for a dummy server can be in the format of IP:Port or Domain name:Port.
      • For an OBS server, you are advised to use the region parameter instead of the address parameter.
    • region
      region specifies the IP address or domain name of the OBS server.
      NOTE:

      region and address cannot be both specified. For an OBS server, both region and address are optional. If neither of them is specified, the system will read the value of defaultRegion, which is the region specified during cluster installation.

    • hdfscfgpath

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

      NOTE:

      One and only one hdfscfgpath option must exist.

    • type

      Specifies whether dfs_fdw is connected to OBS, HDFS, or a dummy server.

      Value range:

      • OBS indicates that OBS is connected.
      • HDFS indicates that HDFS is connected.
      • dummy indicates that the connected server is the one linked to the computing resource pool.
    • username
      • Specifies the user name for connecting to the computing resource pool.
    • password
      • Specifies the password for connecting to the computing resource pool.
    NOTE:

    Usage of OBS server parameters:

    • region and address cannot be both specified for an OBS server. If region is specified, the URL of the region map file is read. If neither region nor address is specified, the value of defaultRegion in the region map file is read.
    • Only one location and one foldername parameter can be specified for an OBS foreign table.
    Parameter usage for different servers is as follows:
    Table 1 Server parameters

    Parameter

    OBS Server

    HDFS Server

    Dummy Server

    access_key

    Supported

    Not supported

    Not supported

    secret_access_key

    Supported

    Not supported

    Not supported

    region

    Supported

    Not supported

    Not supported

    encrypt

    Supported

    Not supported

    Not supported

    address

    Supported

    Supported

    Not supported

    password

    Not supported

    Not supported

    Supported

    username

    Not supported

    Not supported

    Supported

    type

    Supported

    Supported

    Supported

    hdfscfgpath

    Not supported

    Not supported

    Not supported

Example

Create the hdfs_server server, and hdfs_fdw is the built-in foreign-data wrapper.

-- Create the hdfs_server server:
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'
) ;

-- Delete the hdfs_server server:
DROP SERVER hdfs_server;

Create the obs_server server, in which dfs_fdw is the built-in foreign-data wrapper.

-- Create obs_server:
CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( 
  address 'obs.abc.com', 
   access_key 'xxxxxxxxx', 
  secret_access_key 'yyyyyyyyyyyyy', 
  type 'obs'
);

-- Delete obs_server:
DROP SERVER obs_server;

Create a dummy server.

 CREATE SERVER dummy_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS (address 'localhost:1503', username 'omm', password 'Gauss@123', type 'dummy');
CREATE SERVER
NOTE:

Currently, only one dummy server can be created in a database.

Helpful Links

DROP SERVER ALTER SERVER