CREATE FOREIGN TABLE (for GDS Import and Export)

CREATE FOREIGN TABLE creates a GDS foreign table.

Function

CREATE FOREIGN TABLE creates a GDS foreign table in the current database for concurrent data import and export. The GDS foreign table can be read-only or write-only, used for concurrent data import and export, respectively. The OBS foreign table is read-only by default.

Precautions

Syntax

1
2
3
4
5
6
7
8
9
CREATE FOREIGN TABLE [ IF NOT EXISTS  ] table_name 
    ( [  { column_name type_name POSITION(offset,length) | LIKE source_table } [, ...]  ] ) 
    SERVER gsmpp_server 
    OPTIONS (  { option_name ' value '  }  [, ...] ) 
    [  { WRITE ONLY  |  READ ONLY  }] 
    [ WITH error_table_name | LOG INTO error_table_name] 
    [REMOTE LOG 'name'] 
    [PER NODE REJECT LIMIT 'value']
    [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

Parameter Overview

CREATE FOREIGN TABLE provides multiple parameters, which are classified as follows:

Parameter Description

Examples

Create a foreign tablecustomer_ft to import data from GDS server 10.10.123.234 in TEXT format.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE FOREIGN TABLE customer_ft
(
    c_customer_sk             integer               ,
    c_customer_id             char(16)              ,
    c_current_cdemo_sk        integer               ,
    c_current_hdemo_sk        integer               ,
    c_current_addr_sk         integer               ,
    c_first_shipto_date_sk    integer               ,
    c_first_sales_date_sk     integer               ,
    c_salutation              char(10)              ,
    c_first_name              char(20)              ,
    c_last_name               char(30)              ,
    c_preferred_cust_flag     char(1)               ,
    c_birth_day               integer               ,
    c_birth_month             integer               ,
    c_birth_year              integer                       ,
    c_birth_country           varchar(20)                   ,
    c_login                   char(13)                      ,
    c_email_address           char(50)                      ,
    c_last_review_date        char(10)
)
    SERVER gsmpp_server
    OPTIONS
(
    location 'gsfs://10.10.123.234:5000/customer1*.dat',
    FORMAT 'TEXT' ,
    DELIMITER '|',
    encoding 'utf8',
    mode 'Normal')
READ ONLY;

Create a foreign table to import data from GDS servers 192.168.0.90 and 192.168.0.91 in TEXT format. Record errors that occur during data import in foreign_HR_staffS_ft. A maximum of two data format errors are allowed during the data import.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
CREATE FOREIGN TABLE foreign_HR_staffS_ft
(
  staff_ID       NUMBER(6) ,
  FIRST_NAME     VARCHAR2(20),
  LAST_NAME      VARCHAR2(25),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID  VARCHAR2(10),
  SALARY         NUMBER(8,2),
  COMMISSION_PCT NUMBER(2,2),
  MANAGER_ID     NUMBER(6),
  section_ID  NUMBER(4)
) SERVER gsmpp_server OPTIONS (location 'gsfs://192.168.0.90:5000/* | gsfs://192.168.0.91:5000/*', format 'TEXT', delimiter E'\x08',  null '',reject_limit '2') WITH err_HR_staffS_ft;

Helpful Links

ALTER FOREIGN TABLE (for GDS), DROP FOREIGN TABLE

Parent topic: DDL Syntax