Help Center> Data Warehouse Service> Developer Guide> Using Express to Query Data on OBS> Using Express Clusters> Creating Foreign Servers and OBS Foreign Tables
View PDF

Creating Foreign Servers and OBS Foreign Tables

OBS foreign tables need to be created on foreign servers. To create a foreign server, connect to the IP address or domain name of OBS by using your AK and SK. After creating a foreign server, create a foreign table and associate it with the Express sample data uploaded to OBS.

After connecting to the cluster using the SQL client tool, you can run SQL statements in the SQL client tool to create foreign servers, part tables, and lineitem tables.

  1. Use the SQL client tool to connect to the GaussDB(DWS) cluster.

    For details about how to connect to the cluster, see section Using the Data Studio GUI Client to Connect to a Cluster.

  2. Create a foreign server.

    The statement for creating a foreign server is as follows. Replace <OBS_Endpoint>, <Access_Key_Id>, and <Secret_Access_Key> with the actual values according to the parameter descriptions below the statement , and then run the statement to create a foreign server.

    1
2
3
4
5
6
7
8
    		 
    CREATE SERVER express_server
FOREIGN DATA WRAPPER dfs_fdw
OPTIONS (
    address '<OBS_Endpoint>',
    type 'obs',
    access_key '<Access_Key_Id>',
    secret_access_key '<Secret_Access_Key>'
);

    Parameter description:

    • <OBS_Endpoint>: Enter the endpoint address of OBS.

      For more information about the endpoint, visit Regions and Endpoints.

    • <Access_Key_Id> and <Secret_Access_Key>: Indicate the access key ID (AK) and secret access key (SK).

      For more information about the AK and SK, see Creating Access Keys (AK and SK).

  3. Create an OBS foreign table.

    The statement for creating an OBS foreign table is as follows, in which <obs_bucket_name> indicates the name of the OBS bucket for storing sample data. The bucket name varies with the region. Based on the region where the cluster resides, replace parameter <obs_bucket_name> with the OBS bucket name in the corresponding region, and run the statement to create an OBS foreign table. The OBS name in CN North-Beijing1 is dws-demo, and the OBS name in other regions is dws-demo-<Region>, in which <Region> indicates the region name. For information about the regions, see Regions and Endpoints. For example, if the region is cn-south-1, <obs_bucket_name> is dws-demo-cn-south-1.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
    		 
    CREATE schema tpchobs_express;
set current_schema = 'pchobs_express';
CREATE FOREIGN TABLE PART
(
        P_PARTKEY     BIGINT NOT NULL, 
        P_NAME        VARCHAR(55) NOT NULL, 
        P_MFGR        CHAR(25) NOT NULL, 
        P_BRAND       CHAR(10) NOT NULL, 
        P_TYPE        VARCHAR(25) NOT NULL,
        P_SIZE        BIGINT NOT NULL,
        P_CONTAINER   CHAR(10) NOT NULL,
        P_RETAILPRICE DECIMAL(15,2) NOT NULL,
        P_COMMENT     VARCHAR(23) NOT NULL
)
SERVER express_server
OPTIONS(
    encoding 'utf-8',
    format 'text',
    chunksize '64',
    IGNORE_EXTRA_DATA 'on',
    foldername '/<obs_bucket_name>/tpch-obs-express/part/'
    )
distribute by roundrobin;

Parent topic: Using Express Clusters