CREATE FOREIGN TABLE (SQL on other GaussDB(DWS))

Function

In the current database, CREATE FOREIGN TABLE creates a foreign table for collaborative analysis. The foreign table is used to access tables stored in other databases for collaborative analysis.

The foreign table is read-only. It can only be queried using SELECT.

Syntax

1
2
3
4
5
6
7

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
( [ { column_name type_name | LIKE source_table } [, ...] ] )
SERVER server_name
OPTIONS ( { option_name ' value ' } [, ...] )
[ READ ONLY ]
[ DISTRIBUTE BY {ROUNDROBIN} ]
[ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ];

Parameter Description

• IF NOT EXISTS

  Does not throw an error if a table with the same name exists. A notice is issued in this case.

• table_name

  Specifies the name of the foreign table to be created.

  Value range: a string. It must comply with the naming convention.

• column_name

  Specifies the name of a column in the foreign table. Columns are separated by commas (,).

  Value range: a string. It must comply with the naming convention.

  

  Constraints or indexes cannot be created on columns.

• type_name

  Specifies the data type of the column.

  

  Sequence and custom types are not allowed.

• SERVER server_name

  Specifies the server name, which is user-definable.

  Value range: a string indicating an existing server. It must comply with the naming convention.

• OPTIONS ( { option_name ' value ' } [, ...] )

  Specifies the following parameters for a foreign table:

  • table_name: table name of the associated cluster. If it is omitted, the foreign table name will be used.
  • schema_name: schema of the associated cluster. If it is omitted, the schema of the foreign table will be used.
  • encoding: encoding set of the associated cluster. If it is omitted, the database encoding set of the associated cluster will be used.
• READ ONLY

  Indicates that a table is a read-only foreign table.

• DISTRIBUTE BY ROUNDROBIN

  Specifies ROUNDROBIN as the distribution mode for the foreign table.

• TO { GROUP groupname | NODE ( nodename [, ... ] ) }

  Currently, TO GROUP cannot be used. TO NODE is used for internal scale-out tools.

Examples

1. Create a foreign server named server_remote. The corresponding foreign data wrapper is GC_FDW.

   1	CREATE SERVER server_remote FOREIGN DATA WRAPPER GC_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',dbname 'test', username 'test', password '{password}');

   

   • The IP addresses and port numbers of associated CNs are specified in OPTIONS. You are advised to set this parameter to an LVS address or multiple CN addresses.
2. Create a foreign table named region.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   CREATE FOREIGN TABLE region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER server_remote OPTIONS ( schema_name 'test', table_name 'region', encoding 'gbk' );

3. View the created region foreign table.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   \d+ region Foreign table "public.region" Column | Type | Modifiers | FDW Options | Storage | Stats target | Description -------------+---------+-----------+-------------+----------+--------------+------------- r_regionkey | integer | | | plain | | r_name | text | | | extended | | r_comment | text | | | extended | | Server: server_remote FDW Options: (schema_name 'test', table_name 'region', encoding 'gbk') FDW permission: read only Has OIDs: no Distribute By: ROUND ROBIN Location Nodes: ALL DATANODES

Helpful Links

DROP FOREIGN TABLE, ALTER FOREIGN TABLE (SQL on other GaussDB(DWS))