Creating a Foreign Table

Creating a Foreign Table

The syntax for creating a foreign table is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name 
( [ { column_name type_name 
    [ { [CONSTRAINT constraint_name] NULL |
    [CONSTRAINT constraint_name] NOT NULL |
      column_constraint [...]} ] |
      table_constraint [, ...]} [, ...] ] ) 
    SERVER dfs_server 
    OPTIONS ( { option_name ' value ' } [, ...] ) 
    DISTRIBUTE BY {ROUNDROBIN | REPLICATION}
    [ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ;

For example, when creating a foreign table product_info_ext_obs, configure the parameters in the syntax as follows.

• table_name

  Specifies the name of the foreign table to be created.

• Table column definitions
  • column_name: specifies the name of a column in the foreign table.
  • type_name: specifies the data type of the column.

  Multiple columns are separate by commas (,).

  The number of fields and field types in the foreign table must be the same as those in the data stored on OBS.

• SERVER dfs_server

  This parameter specifies the foreign server name of the foreign table. This server must exist. The foreign server connects to OBS to read data by setting its foreign server.

  Enter the name of the foreign server created by following steps in Creating a Foreign Server.

• OPTIONS parameters

  These are parameters associated with the foreign table. The key parameters are as follows:

  • format: indicates the file format on OBS. The ORC, CarbonData, and Parquet formats are supported.
  • foldername: This parameter is mandatory. It indicates the OBS path of the data source file. You only need to enter /Bucket name/Folder directory level/.

    You can use 2 in Preparing Data on OBS to obtain the complete OBS path of the data source file. The path is the endpoint of OBS.

  • totalrows: This parameter is optional. It does not indicate the total rows of the imported data. Because OBS may store many files, it is slow to analyze data. This parameter allows you to set an estimated value so that the optimizer can estimate the table size according to the value. Generally, query efficiency is relatively high when the estimated value is almost the same as the actual value.
  • encoding: encoding of data source files in foreign tables. The default value is utf8. This parameter is mandatory for OBS foreign tables.
• DISTRIBUTE BY:

  This clause is mandatory. The ROUNDROBIN and REPLICATION distribution modes are supported. By default, the ROUNDROBIN distribution mode is used.

  ROUNDROBIN indicates that when a foreign table reads data from the data source, each node in the DWS cluster randomly reads some data and integrates the random data to a complete data set.

  REPLICATION indicates that when a foreign table reads data from the data source, the DWS cluster selects a node to read all data. This is because each data node has complete table data.

• Other parameters in the syntax

  Other parameters are optional and can be configured as required. In this example, they do not need to be configured.

Based on the preceding settings, the command for creating the foreign table is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

DROP FOREIGN TABLE IF EXISTS product_info_ext_obs;
CREATE FOREIGN TABLE product_info_ext_obs
(
    product_price                integer        not null,
    product_id                   char(30)       not null,
    product_time                 date           ,
    product_level                char(10)       ,
    product_name                 varchar(200)   ,
    product_type1                varchar(20)    ,
    product_type2                char(10)       ,
    product_monthly_sales_cnt    integer        ,
    product_comment_time         date           ,
    product_comment_num          integer        ,
    product_comment_content      varchar(200)                      
) SERVER obs_server 
OPTIONS (
format 'orc', 
foldername '/mybucket/demo.db/product_info_orc/',
encoding 'utf8',
totalrows '10'
) 
DISTRIBUTE BY ROUNDROBIN;

Create an OBS foreign table that contains partition columns. The product_info_ext_obs foreign table uses the product_manufacturer column as the partition key. The following partition directories exist in obs/mybucket/demo.db/product_info_orc/:

Partition directory 1: product_manufacturer=10001

Partition directory 2: product_manufacturer=10010

Partition directory 3: product_manufacturer=10086

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

DROP FOREIGN TABLE IF EXISTS product_info_ext_obs;
CREATE FOREIGN TABLE product_info_ext_obs
(
    product_price                integer        not null,
    product_id                   char(30)       not null,
    product_time                 date           ,
    product_level                char(10)       ,
    product_name                 varchar(200)   ,
    product_type1                varchar(20)    ,
    product_type2                char(10)       ,
    product_monthly_sales_cnt    integer        ,
    product_comment_time         date           ,
    product_comment_num          integer        ,
    product_comment_content      varchar(200)   ,
    product_manufacturer	 integer
) SERVER obs_server 
OPTIONS (
format 'orc', 
foldername '/mybucket/demo.db/product_info_orc/',
encoding 'utf8',
totalrows '10'
) 
DISTRIBUTE BY ROUNDROBIN
PARTITION BY (product_manufacturer) AUTOMAPPED;