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 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.
- 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.
- 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>' );
- 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;