Foreign Table

Tacnode foreign tables provide a flexible and efficient way to integrate and process external data. This feature simplifies data management, enables real-time access, promotes collaboration and sharing, and optimizes storage costs. By utilizing foreign tables, Tacnode seamlessly connects to external data sources, enhancing the database's application scenarios and overall value.

Scenarios

• Data Integration and Migration

  • Link external data sources, such as files and other databases, to tables in Tacnode using foreign tables.
  • Query and manipulate foreign data directly, eliminating the need to import it into Tacnode.
  • Streamline the data integration and migration process, reducing the effort required for data conversion and synchronization.

• Big Data Processing

  • Connect foreign tables to large datasets on distributed file systems like HDFS.
  • Use SQL queries via foreign tables to process and analyze substantial data.
  • Leverage Tacnode’s query optimization and parallel processing features to enhance data processing efficiency.

• Data Sharing and Collaboration

  • Access shared foreign data sources through foreign tables across various Tacnode instances.
  • Enable multiple teams or departments to collaborate and analyze data using a unified dataset.
  • Simplify the data-sharing process and boost data utilization.

Supported Foreign Data Source Types

• File-based foreign data sources (OSS, S3, etc.)
• AWS Glue
• Iceberg Catalog
• Unity Catalog

Basic Usage

Install the Extension

Various extensions define foreign data wrappers, enabling access to diverse foreign data sources. Currently, they support file systems like Alibaba Cloud OSS, AWS S3, and other object storage through file_fdw, AWS Glue via glue_fdw, and Hive metadata with hive_fdw.

Install the extension once. If it has already been installed, you can ignore it.

CREATE EXTENSION IF NOT EXISTS <extension_name>;

Create a Foreign Server

Use the CREATE SERVER command to establish a foreign server. This server follows the subsequent syntax:

CREATE SERVER <server_name> FOREIGN DATA WRAPPER <fdw_name>
	OPTIONS (option 'value' [,]);

• server_name: A designated server name.
• fdw_name: The currently supported foreign data wrappers include file_fdw, glue_fdw, hive_fdw, and odps_fdw.
• OPTIONS: The option may have varying parameter options depending on the foreign data wrapper.

Create a User Mapping

Use the CREATE USER MAPPING command to establish a mapping between a local user and a foreign server. Syntax:

 CREATE USER MAPPING FOR <local_user> SERVER <server_name>
 	OPTIONS (option 'value' [,]);

• local_user: Designation of the local database user name.
• server_name: The designation of the foreign server established in the preceding step.
• OPTIONS: The option will have varying parameter options depending on the foreign data source.

Create a Foreign Table

Use the CREATE FOREIGN TABLE command to establish a foreign table. Syntax:

CREATE FOREIGN TABLE <table_name> (
    <column_name> <data_type>,
     ...
    )
SERVER <server_name>
OPTIONS (option 'value' [,]);

• table_name: Indicates the name of the table.
• column_name: The names of the columns and their data types in the specified foreign table must align with the structure of the external file. For guidance on data type mapping, consult the relevant documentation.
• server_name: Refers to the name of the previously created foreign server.
• OPTIONS: The option may contain various parameters based on the features of the external data storage.

Create and Refresh Foreign Tables in Batches

The IMPORT FOREIGN SCHEMA command allows for the batch creation and updating of foreign tables. This is particularly useful for data sources with external metadata management systems, like MaxCompute and Glue. Syntax:

IMPORT FOREIGN SCHEMA remote_schema_name
    { LIMIT TO (table_name [, ...])
    | EXCEPT (table_name [, ...]) }
    FROM SERVER server_name
    INTO local_schema_name
    [ OPTIONS (option 'value' [, ...]) ];

• remote_schema_name: A foreign data source corresponding to the schema.
• table_name: Specify the table name whose metadata is desired for import or update. All tables under the schema will be imported without restrictions. However, importing many tables may extend the process duration.
• server_name: The designation of the foreign server previously established.
• local_schema_name: The table metadata is retained within the corresponding local schema.

To explore more syntax, refer to IMPORT FOREIGN SCHEMA.

Query a Foreign Table

Once you've created a table, you can query it like a regular one. The data within the table is sourced from the linked external file, and its content is dynamically read during the query process:

SELECT * FROM <table_name> LIMIT 10;

Notices

• Ensure Tacnode has permission to read the external file/database.
• Changes made to external files or databases will not automatically sync with the foreign table. To refresh it, recreate the foreign table or use the IMPORT FOREIGN SCHEMA command.
• Currently, write operations (INSERT, UPDATE, DELETE) on foreign tables are not supported.
• The foreign table's performance depends on the size and access speed of the external file, which may require optimization for larger files.
• Unsupported type mappings include: Parquet's struct type cannot be directly converted to JSONB. Using text storage and then casting it to the JSONB type is advisable.