ALTER FOREIGN TABLE (for HDFS or OBS)
Function
Modifies an HDFS or OBS foreign table.
Precautions
None
Syntax
- Set a foreign table's attributes:
1 2
ALTER FOREIGN TABLE [ IF EXISTS ] table_name OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ]);
- Set the owner of the foreign table:
1 2
ALTER FOREIGN TABLE [ IF EXISTS ] tablename OWNER TO new_owner;
- Update a foreign table column:
1 2
ALTER FOREIGN TABLE [ IF EXISTS ] table_name MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] );
- Modify the column of the foreign table:
1 2
ALTER FOREIGN TABLE [ IF EXISTS ] tablename action [, ... ];
The action syntax is as follows:1 2 3 4 5 6 7
ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type | ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL | ALTER [ COLUMN ] column_name SET STATISTICS [PERCENT] integer | ALTER [ COLUMN ] column_name OPTIONS ( {[ ADD | SET | DROP ] option ['value'] } [, ... ]) | MODIFY column_name data_type | MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | MODIFY column_name [ CONSTRAINT constraint_name ] NULL
For details, see ALTER TABLE.
- Add a foreign table informational constraint:
1 2 3 4
ALTER FOREIGN TABLE [ IF EXISTS ] tablename ADD [ CONSTRAINT constraint_name ] { PRIMARY KEY | UNIQUE } ( column_name ) [ NOT ENFORCED [ ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION ] | ENFORCED ];
For parameters about adding an informational constraint to a foreign table, see Parameter Description in CREATE FOREIGN TABLE (For HDFS).
- Remove a foreign table informational constraint.
1 2
ALTER FOREIGN TABLE [ IF EXISTS ] tablename DROP CONSTRAINT constraint_name ;
Parameter Description
- IF EXISTS
Sends a notification instead of an error if no tables have identical names. The notification prompts that the table you are querying does not exist.
- tablename
Specifies the name of an existing foreign table to be modified.
Value range: an existing foreign table name
- new_owner
Specifies the new owner of the foreign table.
Value range: A string indicating a valid user name.
- data_type
Specifies the new type for an existing column.
Value range: A string compliant with the identifier naming rules.
- constraint_name
Specifies the name of a constraint to add or delete.
- column_name
Specifies the name of an existing column.
Value range: a string. It must comply with the naming convention.
For details on how to modify other parameters in the foreign table, such as IF EXISTS, see Parameter Description in ALTER TABLE.
Examples
- Establish HDFS_Server, with HDFS_FDW or DFS_FDW as the foreign data wrapper.
1
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS');
-
Create an HDFS foreign table without partition keys.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
DROP FOREIGN TABLE IF EXISTS ft_region; CREATE FOREIGN TABLE ft_region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/gauss.db/region_orc11_64stripe/' ) DISTRIBUTE BY roundrobin;
- Change the type of the r_name column to text in the ft_region foreign table.
1
ALTER FOREIGN TABLE ft_region ALTER r_name TYPE TEXT;
- Run the following command to mark the r_name column of the ft_region foreign table as not null:
1
ALTER FOREIGN TABLE ft_region ALTER r_name SET NOT NULL;
Helpful Links
CREATE FOREIGN TABLE (SQL on OBS or Hadoop), DROP FOREIGN TABLE
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot