Load External Data Using Foreign Table

SynxDB allows you to access data stored in remote data sources using foreign tables. You can use foreign tables to connect to other databases (such as Oracle and MySQL) or external data sources (such as CSV files) through a foreign data wrapper (FDW).

Use foreign table

You can create a foreign table using the following command:

Note

Before creating a foreign table, you need to create a foreign server first.

CREATE FOREIGN TABLE <external_table_name> (
   col_1 data_type,
   col_2 data_type,
   ...
) SERVER <server_name>
OPTIONS (<option_name> '<option_value>');

For example:

CREATE FOREIGN TABLE my_foreign_table (
   id INTEGER,
   name TEXT
) SERVER remote_data
OPTIONS (table_name 'external_table');

In this example, the table my_foreign_table is the foreign table created in the local database, while the actual data is stored in a remote table called external_table.

Create foreign table using the LIKE clause

You can use the LIKE clause to quickly create a foreign table based on the structure of an existing table. By using this clause, you can define a foreign table without explicitly listing the table’s structure.

Note

Foreign tables created using LIKE do not inherit the distribution settings of the existing table. These settings should be defined when you create the new foreign table.

The following example shows how to use the LIKE clause to create a foreign table:

CREATE FOREIGN DATA WRAPPER dummy; -- Creates a foreign data wrapper.
CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; -- Creates a foreign server.
CREATE TABLE ft_source_table(a INT, b INT, c INT) DISTRIBUTED BY (b); -- Creates a table 'ft_source_table'.
CREATE FOREIGN TABLE my_foreign_table (LIKE ft_source_table) SERVER s0;  -- Creates a Foreign Table based on 'ft_source_table'.
\d+ ft_like
                                    Foreign table "public.ft_like"
Column |  Type   | Collation | Nullable | Default | FDW options | Storage | Stats target | Description
--------+---------+-----------+----------+---------+-------------+---------+--------------+-------------
a      | integer |           |          |         |             | plain   |              |
b      | integer |           |          |         |             | plain   |              |
c      | integer |           |          |         |             | plain   |              |
Server: s0

Query a foreign table

After creating a foreign table, you can query it just like a local table:

SELECT * FROM my_foreign_table;

This query will retrieve remote data in real-time over the network.

Write data to a foreign table with ordered insertion

In SynxDB’s MPP architecture, standard INSERT ... SELECT queries cannot guarantee that the logical row order defined by the ORDER BY clause in the source SELECT statement is physically preserved in the target table. This is because data is redistributed and written in parallel across multiple segments, which inherently breaks any initial ordering.

SynxDB addresses this limitation for foreign tables. When you use INSERT INTO ... SELECT ... ORDER BY to write data into a foreign table, the query planner automatically injects an explicit sorting operator into the execution plan before the final insertion step. This ensures that rows are correctly ordered according to the ORDER BY clause before being handed off to the foreign table’s data handler (such as the writable external table handler).

This feature guarantees deterministic output ordering for ETL pipelines writing sorted data to external tables.

Example

The following example demonstrates how to create a writable external table and insert data with a preserved sort order.

Create the writable and readable external tables, and a source data table:

CREATE WRITABLE EXTERNAL TABLE ext_w_output
    ("date_dtm" TEXT, "num" TEXT, "segnum" TEXT)
    LOCATION ('demoprot://output.txt')
    FORMAT 'CSV' ENCODING 'UTF8'
    DISTRIBUTED BY (segnum);

CREATE TABLE source_data ("date_dtm" TEXT, "num" TEXT, "segnum" TEXT)
    DISTRIBUTED BY (date_dtm);

INSERT INTO source_data VALUES ('z20260103', '33', 'seg10');
INSERT INTO source_data VALUES ('z20260101', '11', 'seg10');
INSERT INTO source_data VALUES ('z20260102', '22', 'seg10');

Insert data into the writable external table with ORDER BY to preserve row order:

INSERT INTO ext_w_output
WITH data AS (
    SELECT 'date_dtm' AS "date_dtm", 'num' AS "num", 'seg10' AS "segnum"
    UNION ALL
    SELECT "date_dtm"::TEXT, num AS "num", segnum AS "segnum"
    FROM source_data
)
SELECT "date_dtm", "num", "segnum"
FROM data
ORDER BY "date_dtm";

Use EXPLAIN to verify that the query planner injects a Sort operator before the Insert step:

EXPLAIN INSERT INTO ext_w_output
WITH data AS (
    SELECT 'date_dtm' AS "date_dtm", 'num' AS "num", 'seg10' AS "segnum"
    UNION ALL
    SELECT "date_dtm"::TEXT, num AS "num", segnum AS "segnum"
    FROM source_data
)
SELECT "date_dtm", "num", "segnum"
FROM data
ORDER BY "date_dtm";

The execution plan shows a Sort node directly under the Insert node, ensuring data is sorted before writing to the external table:

Insert on ext_w_output
    ->  Sort
        Sort Key: ('date_dtm'::text)
        ->  Redistribute Motion 3:3  (slice1; segments: 3)
                Hash Key: ('seg10'::text)
                ->  Sort
                    Sort Key: ('date_dtm'::text)
                    ->  Append
                            ->  Result
                            ->  Seq Scan on source_data

The output file will contain data sorted in the order specified by ORDER BY:

date_dtm,num,seg10
z20260101,11,seg10
z20260102,22,seg10
z20260103,33,seg10