• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. ALTER FOREIGN TABLE (for GDS)

ALTER FOREIGN TABLE (for GDS)

Function

ALTER FOREIGN TABLE modifies a foreign table.

Precautions

None

Syntax

  • Set a foreign table's attributes.
    ALTER FOREIGN TABLE [ IF EXISTS ]  table_name
        OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]);
  • Set a new owner.
    ALTER FOREIGN TABLE [ IF EXISTS ] tablename
        OWNER TO new_owner;

Parameter Description

  • table_name

    Specifies the name of an existing foreign table to be modified.

    Value range: an existing foreign table name.

  • option

    Specifies the name of the option to be modified.

    Valid value: see Parameter Description in CREATE FOREIGN TABLE.

  • value

    Specifies the new value of option.

Example

-- Create a foreign table:
CREATE FOREIGN TABLE tpcds.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.185.179.143:5000/customer1*.dat',
    FORMAT 'TEXT' ,
    DELIMITER '|',
    encoding 'utf8',
    mode 'Normal')
READ ONLY;

-- Modify foreign table attributes and delete the mode option:
ALTER FOREIGN TABLE tpcds.customer_ft options(drop mode);

-- Delete the foreign table:
DROP FOREIGN TABLE tpcds.customer_ft;