CREATE FOREIGN TABLE (SQL on OBS or Hadoop)

Function

CREATE FOREIGN TABLE creates an HDFS or OBS foreign table in the current database to access structured data stored in the HDFS or OBS distributed cluster file system. You can also use the HDFS or OBS foreign table to export data in ORC and Parquet formats to HDFS or OBS.

A foreign table is a virtual table within a database or big data platform that does not physically store data. Instead, it serves as a mapping to data residing in external storage systems, such as HDFS or OBS. This mapping is established using metadata, which includes the table structure, the location of the data, and its file format. It enables users to directly access or operate external data using standard SQL statements. A foreign table serves as a data proxy that logically maps data in external file systems to table structures that can be identified by databases.

Foreign tables are designed to break data silos. Foreign tables allow database users to access external system data in a familiar way through metadata mapping and export data to external systems. In this way, data can be efficiently collaborated, analyzed, and shared across systems, especially in big data scenarios, significantly reducing data movement costs and improving collaboration efficiency.

When DWS accesses external data sources through foreign tables, the access performance varies according to data sources.

• Data stored in OBS: Data storage is decoupled from compute. The cluster storage cost is low, and storage capacity is not limited. Clusters can be deleted at any time. However, the compute performance depends on the OBS access performance and is lower than that of HDFS. OBS is recommended when data computing is infrequent.
• Data stored in HDFS: Data storage is not decoupled from compute. The cluster storage cost is high, and storage capacity is limited. The compute performance is high. You must export data before you delete clusters. HDFS is recommended for computing-intensive scenarios.

The following read and write foreign tables are supported only in the storage-compute decoupling 3.0 version.

Precautions

• When using an OBS foreign table to access data in an OBS bucket, you need to ensure that your DWS cluster and OBS bucket are in the same region (for example, CN-Hong Kong).
• Only the system administrator dbadmin or a common user who has been granted the USEFT permission can perform foreign table operations.

  1	ALTER USER user_name USEFT;

• The HDFS foreign table and OBS foreign table are classified into read-only, write-only, and read-write foreign tables. Read-only foreign tables are used for query. Write-only foreign tables can be used to export data from DWS to the distributed file system. Read-write foreign tables can be used for query and data export. Only 9.1.0.100 and later versions support read-write foreign tables.
• Supported formats:
  • Importing data: Only data in ORC, TEXT, CSV, CARBONDATA, PARQUET, and JSON formats can be imported for query.
  • Exporting data: For OBS foreign tables, data in ORC and PARQUET (supported only in storage-compute decoupling 3.0 version) can be exported. For HDFS foreign tables, data in ORC and PARQUET can be exported.
• Before using a foreign table, you need to manually create a foreign server, which is a virtual object configured in the database and used to define parameters (such as the address, protocol, and authentication information) for connecting to an external data source. Through the foreign servers, the local database can directly query and insert external data, just like operating local tables. (The syntax is similar, but may be restricted by performance or functions.) For details, see CREATE SERVER.
• If the foreign data wrapper is set to HDFS_FDW or DFS_FDW when you manually create a foreign server, you need to configure the DISTRIBUTE BY clause to specify the distribution mode when creating a read-only foreign table.

Syntax

Create an HDFS foreign table.

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

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 server_name 
    OPTIONS ( { option_name ' value ' } [, ...] ) 
    [ {WRITE ONLY | READ ONLY | READ WRITE} ]
    DISTRIBUTE BY {ROUNDROBIN | REPLICATION}
   
    [ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ;

• column_constraint is as follows:

  1 2 3

  [CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]

• table_constraint is as follows:

  1 2 3

  [CONSTRAINT constraint_name] {PRIMARY KEY | UNIQUE} (column_name) [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]

Informational Constraints

In DWS, the use of data constraints depend on users. If users can make data sources strictly comply with certain constraints, the query on data with such constraints can be accelerated. Foreign tables do not support Index. Informational constraint is used for optimizing query plans.

The constraints of creating informational constraints for an HDFS foreign table are as follows:

• You can create an informational constraint only if the values in a NOT NULL column in your table are unique. Otherwise, the query result will be different from expected.
• Currently, the informational constraint of DWS supports only PRIMARY KEY and UNIQUE constraints.
• The informational constraints of DWS support the NOT ENFORCED attribute.
• UNIQUE informational constraints can be created for multiple columns in a table, but only one PRIMARY KEY constraint can be created in a table.
• Multiple informational constraints can be established in a column of a table (because the function that establishing a column or multiple constraints in a column is the same.) Therefore, you are not advised to set up multiple informational constraints in a column, and only one Primary Key type can be set up.
• Table-level COMMENT is not supported.
• Multi-column combination constraints are not supported.
• Different CNs in the same cluster cannot concurrently export data to the same write-only ORC foreign table.
• The catalog of a write-only foreign table in ORC format can only be used as the export catalog of a single foreign table of DWS. It cannot be used for multiple foreign tables, and other components cannot write other files to this catalog.

Example: Creating an HDFS Foreign Table

In HDFS, import the TPC-H benchmark test tables part and region using Hive. The path of the part table is /user/hive/warehouse/partition.db/part_4, and that of the region table is /user/hive/warehouse/mppdb.db/region_orc11_64stripe/.

1. Establish HDFS_Server, with HDFS_FDW or DFS_FDW as the foreign data wrapper.

   1 2 3 4

   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');

   

   • The IP addresses and port numbers of HDFS NameNodes are specified in OPTIONS. For details about the port number, search for dfs.namenode.rpc.port in the MRS-HDFS service configuration. In this example the port number is 25000.
   • 10.10.0.100:25000,10.10.0.101:25000 indicates the IP addresses and port numbers of the primary and standby HDFS NameNodes. It is the recommended format. Two groups of parameters are separated by commas (,).
2. Create an HDFS foreign table. The HDFS server associated with the table is hdfs_server, the corresponding file format of the ft_region table on the HDFS server is 'orc', and the file directory in the HDFS file system is '/user/hive/warehouse/mppdb. db/region_orc11_64stripe/'.
   • Create an HDFS foreign table without partition keys.

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

     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/mppdb.db/region_orc11_64stripe/' ) DISTRIBUTE BY roundrobin;

   • Create an HDFS foreign table with partition keys.

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

     CREATE FOREIGN TABLE ft_part ( p_partkey int, p_name text, p_mfgr text, p_brand text, p_type text, p_size int, p_container text, p_retailprice float8, p_comment text ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/partition.db/part_4' ) DISTRIBUTE BY roundrobin PARTITION BY (p_mfgr) AUTOMAPPED;

     

     DWS allows you to specify files using the keyword filenames or foldername. The latter is recommended. The key word distribute specifies the storage distribution mode of the ft_region table.

3. View the created server and foreign table.

   1 2 3 4 5 6 7 8 9 10 11

   SELECT * FROM pg_foreign_table WHERE ftrelid='ft_region'::regclass; ftrelid | ftserver | ftwriteonly | ftoptions ---------+----------+-------------+------------------------------------------------------------------------------ 16510 | 16509 | f | {format=orc,foldername=/user/hive/warehouse/mppdb.db/region_orc11_64stripe/} (1 row) select * from pg_foreign_table where ftrelid='ft_part'::regclass; ftrelid | ftserver | ftwriteonly | ftoptions ---------+----------+-------------+------------------------------------------------------------------ 16513 | 16509 | f | {format=orc,foldername=/user/hive/warehouse/partition.db/part_4} (1 row)

Example: Creating an HDFS Write-Only Foreign Table

Export data from the TPC-H benchmark test table region to the /user/hive/warehouse/mppdb.db/region_orc/ directory of the HDFS file system through the HDFS write-only foreign table.

1. Create an HDFS foreign table. The corresponding foreign data wrapper is HDFS_FDW or DFS_FDW, which is the same as that in Example 1.
2. Create a write-only HDFS foreign table.

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

   CREATE FOREIGN TABLE ft_wo_region ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc/' ) WRITE ONLY;

3. Writes data to the HDFS file system through a write-only foreign table.

   1	INSERT INTO ft_wo_region SELECT * FROM region;

Example: Creating an HDFS Write-Only Partitioned Foreign Table (Supported Only by Storage-Compute Decoupling 3.0 Version)

Export data from the TPC-H benchmark test table region table to the /user/hive/warehouse/mppdb.db/region_orc/ directory of the HDFS file system through the write-only, multi-level partitioned HDFS foreign table.

1. Create an HDFS foreign table. The corresponding foreign data wrapper is HDFS_FDW or DFS_FDW, which is the same as that in Example: Creating an HDFS Foreign Table.
2. Create a write-only foreign table for HDFS multi-level partitions.

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

   CREATE FOREIGN TABLE ft_wo_region_partition ( R_REGIONKEY INT4, R_NAME TEXT, R_COMMENT TEXT ) SERVER hdfs_server OPTIONS ( FORMAT 'orc', encoding 'utf8', FOLDERNAME '/user/hive/warehouse/mppdb.db/region_orc/' ) WRITE ONLY PARTITION BY (R_REGIONKEY, R_NAME);

3. Writes data to the HDFS file system through a write-only foreign table.

   1	INSERT INTO ft_wo_region_partition SELECT * FROM region;

Example: Creating an OBS Read-Only Foreign Table (Supported Only by Storage-Compute Decoupling 3.0 version)

Read data from the OBS file system through the foreign table partsupp.

1. Create an OBS server.
   

   • Hard-coded or plaintext AK and SK are risky. For security purposes, encrypt your AK and SK and store them in the configuration file or environment variables.
   • For details about the syntax for creating a server, see CREATE SERVER.

   1 2 3 4 5 6

   CREATE SERVER obs_srv FOREIGN DATA WRAPPER dfs_fdw OPTIONS ( address 'obs.ap-southeast-1.myhuaweicloud.com', type 'obs', access_key 'xxx', secret_access_key 'xxx' );

2. Create an OBS foreign table partsupp and use the cold cache in the disk cache.

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

   CREATE FOREIGN TABLE PARTSUPP ( PS_PARTKEY BIGINT NOT NULL, PS_SUPPKEY BIGINT NOT NULL, PS_AVAILQTY BIGINT NOT NULL, PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, PS_COMMENT VARCHAR(199) NOT NULL ) SERVER obs_srv options ( encoding 'utf-8', format 'parquet', foldername 'xxx', cache_policy 'NONE' ) READ ONLY DISTRIBUTE BY ROUNDROBIN;

3. Read data from OBS through the foreign table partsupp.

   1	SELECT * FROM PARTSUPP;

Example: Creating an HDFS Foreign Table Containing an Informational Constraint (NOT ENFORCED configured)

Perform operations on an HDFS foreign table that includes informational constraints.

• Create an HDFS foreign table with informational constraints.

  1 2 3 4 5 6 7 8 9 10

  CREATE FOREIGN TABLE ft_region ( R_REGIONKEY int, R_NAME TEXT, R_COMMENT TEXT , primary key (R_REGIONKEY) not enforced) SERVER hdfs_server OPTIONS(format 'orc', encoding 'utf8', foldername '/user/hive/warehouse/mppdb.db/region_orc11_64stripe') DISTRIBUTE BY roundrobin;

• Check whether the region table has an informational constraint index:

  1 2 3 4 5 6 7 8 9 10 11

  SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass; relname | relhasindex ------------------------+------------- ft_region | f (1 row) SELECT conname, contype, consoft, conopt, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey'; conname | contype | consoft | conopt | conindid | conkey ----------------+---------+---------+--------+----------+-------- ft_region_pkey | p | t | t | 0 | {1} (1 row)

• Delete the informational constraint:

  1 2 3 4 5 6

  ALTER FOREIGN TABLE ft_region DROP CONSTRAINT ft_region_pkey RESTRICT; SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey'; conname | contype | consoft | conindid | conkey ---------+---------+---------+----------+-------- (0 rows)

• Add a unique informational constraint for the foreign table:

  1	ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED;

  Delete the unique informational constraint:

  1 2 3 4 5 6

  ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique RESTRICT; SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='constr_unique'; conname | contype | consoft | conindid | conkey ---------+---------+---------+----------+-------- (0 rows)

• Add a unique informational constraint for the foreign table:

  1 2 3 4 5 6 7

  ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED disable query optimization; SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass; relname | relhasindex ------------------------+------------- ft_region | f (1 row)

  Delete the unique informational constraint:

  1	ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique CASCADE;

Example: Reading OBS JSON Data Using an OBS Foreign Table

Read json data stored in OBS using a foreign table.

1. The following JSON files are on OBS. The JSON objects contain nesting and arrays. Some objects have lost columns, and some object names are duplicate.

   {"A" : "simple1", "B" : {"C" : "nesting1"}, "D" : ["array", 2, {"E" : "complicated"}]}
   {"A" : "simple2", "D" : ["array", 2, {"E" : "complicated"}]}
   {"A" : "simple3", "B" : {"C" : "nesting3"}, "D" : ["array", 2, {"E" : "complicated3"}]}
   {"B" : {"C" : "nesting4"},"A" : "simple4",  "D" : ["array", 2, {"E" : "complicated4"}]}
   {"A" : "simple5", "B" : {"C" : "nesting5"}, "D" : ["array", 2, {"E" : "complicated5"}]}

2. Create obs_server, with DFS_FDW as the foreign data wrapper.

   1 2 3 4 5 6

   CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.ap-southeast-1.myhuaweicloud.com', ACCESS_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'OBS' );

   

   • ADDRESS is the endpoint of OBS. Replace it with the actual endpoint. You can find the domain name by searching for the value of regionCode in the region_map file.
   • ACCESS_KEY and SECRET_ACCESS_KEY are access keys for the cloud account system. Replace the values as needed.
   • TYPE indicates the server type. Retain the value OBS.
3. Create the OBS foreign table json_f and define the column names. For example, d#2_e indicates that the column is object e nested in the 2nd element of array d. The OBS server associated with the table is obs_server. foldername indicates the data source directory of the foreign table, that is, the OBS directory.
   

   // Hard-coded or plaintext AK and SK are risky. For security purposes, encrypt your AK and SK and store them in the configuration file or environment variables.

   1 2 3 4 5 6 7 8 9 10 11

   CREATE FOREIGN TABLE json_f ( a VARCHAR(10), b_c TEXT, d#1 INTEGER, d#2_e VARCHAR(30) )SERVER obs_server OPTIONS ( foldername '/xxx/xxx/', format 'json', encoding 'utf8', force_mapping 'true' )distribute by roundrobin;

4. Query the foreign table json_f. The fault tolerance parameter force_mapping is enabled by default. If a column is missing in a JSON object, NULL is filled in. If a JSON object name is duplicate, the last name prevails.

   1 2 3 4 5 6 7 8 9

   SELECT * FROM json_f; a | b_c | d#1 | d#2_e ---------+----------+-----+-------------- simple1 | nesting1 | 2 | complicated1 simple2 | | 2 | complicated2 simple3 | nesting3 | 2 | complicated3 simple4 | nesting4 | 2 | complicated4 repeat | nesting5 | 2 | complicated5 (5 rows)

Example: Accessing Data on DLI Through a DLI Foreign Table

Read a DLI multi-version foreign table using a foreign table. Only DLI 8.1.1 and later support the multi-version foreign table example.

1. Create dli_server, with DFS_FDW as the foreign data wrapper.

   1 2 3 4 5 6 7 8 9

   CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( ADDRESS 'obs.ap-southeast-1.myhuaweicloud.com', ACCESS_KEY 'xxxxxxxxx', SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', TYPE 'DLI', DLI_ADDRESS 'dli.example.com', DLI_ACCESS_KEY 'xxxxxxxxx', DLI_SECRET_ACCESS_KEY 'yyyyyyyyyyyyy' );

   

   • ADDRESS is the endpoint of OBS. DLI_ADDRESS is the endpoint of DLI. Replace it with the actual endpoint.
   • ACCESS_KEY and SECRET_ACCESS_KEY are access keys for the cloud account system to access OBS. Use the actual value.
   • DLI_ACCESS_KEY and DLI_SECRET_ACCESS_KEY are access keys for the cloud account system to access DLI. Use the actual value.
   • TYPE indicates the server type. Retain the value DLI.
2. Create the OBS foreign table customer_address for accessing DLI. The table does not contain partition columns, and the DLI server associated with the table is dli_server. Where, the project_id is xxxxxxxxxxxxxxx, the database_name on DLI is database123, and the table_name of the table to be accessed is table456. Replace them based on the actual requirements.
   

   // Hard-coded or plaintext AK and SK are risky. For security purposes, encrypt your AK and SK and store them in the configuration file or environment variables.

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

   CREATE FOREIGN TABLE customer_address ( ca_address_sk integer not null, ca_address_id char(16) not null, ca_street_number char(10) , ca_street_name varchar(60) , ca_street_type char(15) , ca_suite_number char(10) , ca_city varchar(60) , ca_county varchar(30) , ca_state char(2) , ca_zip char(10) , ca_country varchar(20) , ca_gmt_offset decimal(36,33) , ca_location_type char(20) ) SERVER dli_server OPTIONS ( FORMAT 'ORC', ENCODING 'utf8', DLI_PROJECT_ID 'xxxxxxxxxxxxxxx', DLI_DATABASE_NAME 'database123', DLI_TABLE_NAME 'table456' ) DISTRIBUTE BY roundrobin;

3. Query data in a DLI multi-version table using a foreign table.

   1 2 3 4 5

   SELECT COUNT(*) FROM customer_address; count ------- 20 (1 row)

Helpful Links

• For details about the syntax for modifying and deleting a foreign table, see ALTER FOREIGN TABLE (for HDFS or OBS) and DROP FOREIGN TABLE.
• More best practices:
  • For details about how to use an HDFS foreign table to access the remote MRS data, see Importing Table Data from MRS Hive to a GaussDB(DWS) Cluster.
  • For details about how to use an OBS foreign table, see Transferring Data Between OBS Buckets and GaussDB(DWS) Clusters.
  • For details about how to use a DLI foreign table, see Importing Table Data from DLI to a GaussDB(DWS) Cluster.