Foreign Wrappers, Servers, Tables, and User Mappings
HeavyConnect introduces four new types of database objects to HeavyDB. These objects follow the ISO/IEC 9075-9 (SQL/MED) standard, which defines constructs for managing external data for a SQL database.
A foreign data wrapper represents an adapter object that connects HeavyDB to an external data source and accesses required data. Different types of data wrappers exist for different data formats.
A foreign server object represents a named external datasource, along with configuration information required to access the datasource. Server objects are referenced during foreign table creation to indicate the backing datasource. A foreign server object can be reused by multiple FOREIGN TABLES when the same external datasource is used. Three local file server objects, default_local_delimited, default_local_parquet, and default_local_regex_parsed, are available for use directly in the CREATE FOREIGN TABLE statement when a custom server is not used.
A foreign table acts as a proxy to an external data source for which access details are provided by a server object. A foreign table provides an interface for querying data from an external datasource in a similar way as for a local table.
A user mapping is an object that contains access credentials to an external datasource/server. User mappings provide secure handling of credentials when access to restricted external datasources or foreign servers is required.
Organizing Your Data
For file data sources, you can use directory partitioning to organize and filter your data. This can include hard and soft links in your directory structure. HeavyConnect processes all files in a directory or can reference a single file. You can structure directories to make it easier to introduce new data for processing.
For example, using the following graphic as a reference, you can:
Include all records from 2020
Include all records in March 2020
An individual record in March 2020
All records YoY for January (softlink)
Refer to AWS documentation on organizing your data and how folders are represented in S3.
