Foreign Scan

Description

In GaussDB, foreign scan is a scanner used to access foreign data sources. It can process data in foreign data sources as tables in relational databases to implement query and operations on foreign data sources. In GaussDB, foreign scan can be implemented by extending APIs. You can compile your own extension programs to access and operate foreign data sources. In addition, GaussDB provides some common extension programs, such as foreign data wrapper (FDW), which can be used to access data in other relational databases.

Typical Scenarios

• Foreign data source accessing: When you need to access foreign data sources, you can use foreign scan to read data from the foreign data source.
• Data integration: When data from multiple data sources needs to be integrated, you can use a foreign scan to process the data as a table in a relational database.
• Data migration: When data needs to be migrated from one data source to another, you can use a foreign scan to read data from the source data source and insert the data into the target data source.

Examples

Example: Access a foreign data source.

-- Prepare data.
gaussdb=# SET enable_extension = true;
WARNING:  Extension is not a secure feature, and it may cause unexpected errors.
SET
gaussdb=# CREATE SERVER foo FOREIGN DATA WRAPPER file_fdw; 
CREATE SERVER
gaussdb=# CREATE FOREIGN TABLE ft (a int, b text) SERVER foo OPTIONS ("format" 'csv', "filename" '/gaussdb/username/data.csv', "null" 'NULL');
CREATE FOREIGN TABLE

-- Execution result.
gaussdb=# EXPLAIN SELECT * FROM ft; 
                         QUERY PLAN                         
------------------------------------------------------------
 Foreign Scan on ft  (cost=0.00..138.00 rows=1280 width=36)
   Foreign File: /gaussdb/username/data.csv
(2 rows)

-- Drop.
gaussdb=# DROP FOREIGN TABLE ft;
gaussdb=# DROP SERVER foo;

In the preceding example, the output of the foreign scan operator is as follows.

Example 2: Data integration

-- Prepare data.
gaussdb=# SET enable_extension = true;
WARNING:  Extension is not a secure feature, and it may cause unexpected errors.
SET
gaussdb=# CREATE SERVER f1 FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
gaussdb=# CREATE FOREIGN TABLE ft1 (a int, b text) SERVER f1 OPTIONS ("format" 'csv', "filename" '/gaussdb/username/data.csv', "null" 'NULL');
CREATE FOREIGN TABLE
gaussdb=# CREATE SERVER f2 FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
gaussdb=# CREATE FOREIGN TABLE ft2 (a int, b text) SERVER f2 OPTIONS ("format" 'csv', "filename" '/gaussdb/username/data.csv', "null" 'NULL');
CREATE FOREIGN TABLE

-- Execution result.
gaussdb=# explain select ft.* from ft,ft2 where ft.a = ft2.a;
                               QUERY PLAN
-------------------------------------------------------------------------
 Hash Join  (cost=154.00..609.44 rows=16384 width=36)
   Hash Cond: (ft2.a = ft1.a)
   ->  Foreign Scan on ft2  (cost=0.00..266.00 rows=2560 width=4)
         Foreign File: /gaussdb/username/data.csv
   ->  Hash  (cost=138.00..138.00 rows=1280 width=36)
         ->  Foreign Scan on ft1  (cost=0.00..138.00 rows=1280 width=36)
               Foreign File: /gaussdb/username/data.csv
(7 rows)

-- Drop.
gaussdb=# DROP FOREIGN TABLE ft1;
gaussdb=# DROP FOREIGN TABLE ft2
gaussdb=# DROP SERVER f1;
gaussdb=# DROP SERVER f2;

Example 3: Data migration

-- Prepare data.
gaussdb=# SET enable_extension = true;
WARNING:  Extension is not a secure feature, and it may cause unexpected errors.
SET
gaussdb=# CREATE SERVER f1 FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
gaussdb=# CREATE FOREIGN TABLE ft1 (a int, b text) SERVER f1 OPTIONS ("format" 'csv', "filename" '/gaussdb/username/data.csv', "null" 'NULL');
CREATE FOREIGN TABLE

gaussdb=# copy ft1 from '/gaussdb/username/data.csv';
ERROR:  could not open file "/gaussdb" for reading: No such file or directory

gaussdb=# drop foreign table ft1;
DROP FOREIGN TABLE
gaussdb=# drop server f1;;
DROP SERVER