PolarDB:Foreign data wrapper

Introduction

Overview

GanosBase Foreign Data Wrapper (FDW) is a spatial-temporal extension for PolarDB for PostgreSQL (Compatible with Oracle) that can communicate with external geospatial data sources.

It provides unified access to external geospatial data in various formats and maps external geospatial data to geometry data in your database, allowing for query against external data directly within your database.

Scenarios

• Data integration

  GanosBase FDW seamlessly integrates external geospatial data in multiple formats, such as shapefile, GeoJSON, KML, and GML into your database. By configuring connection details to an external data source and specifying data mapping rules, you can create a foreign table within your database where external geospatial data is mapped to. This provides a uniform interface for the query, analysis, and manipulation of external geospatial data within your database, regardless of its original formats.

• Data transformation and format conversion

  When external geospatial data is mapped to foreign tables, GanosBase FDW automatically transforms it into PostgreSQL-compatible types and formats, simplifying data type conversion and data integration.

• Geospatial data analysis or query

  GanosBase FDW enables you to directly query and analyze external geospatial data. It allows you to query a foreign table using regular SQL queries and leverage PostgreSQL's powerful geospatial functions and tools for spatial analysis, spatial queries, and buffer analysis.

Components

This section describes the components and features of FDW.

FDW

A FDW is a type of extension widely used in PostgreSQL to communicate with external data sources during data query and operations.

Here are some related concepts.

• External data source: An external data source can be a relational database, file system, NoSQL database, or web service, and can be accessed through the FDW. An external data source can reside either in a local or a remote server.

• Wrapper: The FDW offers a wrapper for PostgreSQL to communicate and exchange data with external data sources.

• Mapping rules: Mapping rules define how external tables and columns correspond to tables and columns within your PostgreSQL databases. They also define the transformation of data and conversion of data types. You can use mapping rules to customize how external data is represented and accessed in PostgreSQL.

• Query optimizer: Integrated into the FDW, a query optimizer transforms and optimizes queries, enhancing query efficiency. It sends some queries to the external data source for execution, reducing overheads of data transmission and processing.

• Data import and export: The FDW enables data exchange between your PostgreSQL database and external sources. It allows you to import external data, export local data, and perform data analysis, processing, and transfer within your PostgreSQL database.

• Security and access control: The FDW allows you to configure permissions on external data sources. You can control which users or roles have access to external data and what operations they can perform, thus protecting data security and integrity.

GanosBase FDW

GanosBase FDW is an extension of PostgreSQL and allows PostgreSQL to access external geospatial data in multiple formats. It allows you to configure external geospatial data sources and map the geospatial data to foreign tables, enabling efficient data integration and query within your database.

GanosBase FDW has the following features:

• Support for multiple formats: GanosBase FDW supports popular geospatial data formats, such as shapefile, GeoJSON, KML, GML, and MapInfo. It can map data in those formats to foreign tables, so you can access and query external data directly within your database.

• Flexible data mapping: GanosBase FDW allows you to configure connection details to external data sources and specify mapping rules. By defining the schema of a foreign table and mapping external geospatial data such as features, attributes, and geometric data to columns within PostgreSQL databases, you can query and analyze external data as if it were from your local tables.

• Query optimization: GanosBase FDW employs query optimization techniques, such as predicate pushdown and local query optimization, to fetch only relevant data, thus enhancing overall query performance.

• Data manipulation: In addition to reading external data, GanosBase FDW supports data manipulation on foreign tables, such as insertion, update, and deletion, by passing these commands to external data sources for execution.

In summary, GanosBase FDW enables you to integrate external geospatial data into your database for efficient query and data analysis. With its flexible data mapping and query optimization mechanisms, you can read and manipulate external data as if it were from local tables, facilitating geospatial data integration and analysis.

For more information, see FDW SQL reference.

Quick start

Introduction

This section describes the basic usage of the GanosBase FDW extension, such as how to install the extension, register and query a foreign table, import data from a foreign table, as well as advanced usage of the extension.

Basic usage

• Install the ganos_fdw extension:

  CREATE extension ganos_fdw cascade;

  Note

  Install the extension into the public schema to avoid potential permission issues.

  CREATE extension ganos_fdw WITH schema public cascade;

• Register a geospatial data file as a foreign table:

  -- Register a shapefile as a foreign table
  SELECT ST_RegForeignTables('OSS://<ak>:<ak_secret>@<endpoint>/bucket/path/poly.shp');
  
  -- Query the information_schema.foreign_tables view to retrieve a list of registered foreign tables
  select foreign_table_name from information_schema.foreign_tables ORDER BYforeign_table_name ASC;

  Note

  You need to access OSS through internal OSS endpoints. For more information, see OSS domain names.

• Perform a query on the foreign table:

  SELECT fid, ST_AsText(geom), name, age, height
  FROM poly
  WHERE fid = 1;
  
  ------------
  1 | POLYGON((5 0,0 0,0 10,5 10,5 0)) | ZhangShan |  35 |   1.84

• Import data into a local table:

  -- Use the CREATE TABLE AS SELECT statement to create a local table with the same schema as the foreign table
  CREATE TABLE poly_db
  AS
  SELECT * FROM poly;
  
  -- If such a local table already exists, use the INSERT INTO SELECT statement
  INSERT INTO poly_db
  SELECT * FROM poly;

• (Optional) Drop the extension:

  Drop extension ganos_fdw cascade;

Advanced usage

The GanosBase FDW extension follows the FDW specifications, so you can use standard SQL to create foreign tables to access and manipulate external data.

Create a foreign server

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER ganos_fdw
OPTIONS (
    datasource 'OSS://<endpoint>/path/file',
    format '<driver>',
    open_options '<config>=<value>[ <config>=<value>]',
    config_options '<config>=<value>[ <config>=<value>]');

Take note of the following points:

• Access key ID and secret are not required for the datasource option.

• The endpoint is dependent on the region where your OSS bucket resides. To ensure data accessibility, make sure that your cluster and OSS bucket reside in the same region and are connected through the internal OSS endpoint. For more information, see OSS domain names.

• The format option specifies the driver for reading source data, which can be retrieved using ST_FDWDrivers. If no format option is specified, the system will use a default driver.

Example:

CREATE SERVER myserver
FOREIGN DATA WRAPPER ganos_fdw
OPTIONS (
    datasource 'OSS://<endpoint>/path/poly.shp',
    format 'ESRI Shapefile',
    open_options 'SHAPE_ENCODING=LATIN1',
    config_options '');,

Create a user mapping

Create a user mapping to provide credentials for your OSS bucket.

CREATE USER MAPPING
FOR <user_name>
SERVER <server_name>
OPTIONS (
  user '<oss_ak_id>',
  password '<oss_ak_secret>');

Example:

CREATE USER MAPPING
FOR CURRENT_USER
SERVER myserver
OPTIONS (
  user 'id',
  password 'secret')

Create a foreign table

Create a foreign table that maps to the external table you want to access:

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

Example:

CREATE FOREIGN TABLE example_table (
  fid bigint,
  name varchar,
  age varchar,
  value varchar
) SERVER myserver
OPTIONS (layer 'poly');

Import table definitions

Use IMPORT FOREIGN SCHEMA to import table definitions:

IMPORT FOREIGN SCHEMA ganos_fdw
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER <server_name>
    INTO <local_schema>

The schema name must be ganos_fdw.

Example:

CREATE SCHEMA imp;

IMPORT FOREIGN SCHEMA ganos_fdw
  FROM SERVER myserver
  INTO imp;

SQL reference

For more information, see FDW SQL reference.