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

Creating Foreign Servers and OBS Foreign Tables

Updated at:Aug 27, 2020 GMT+08:00

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 and use 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 data warehouse cluster.

    For details about how to connect to the cluster, see 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 descriptions:

    • <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;
    

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel