文档

外部数据包装器

更新时间:

本文介绍了外部数据包装器的用途、基本构成以及快速入门等内容。

工具用途

简介

Ganos FDW是对象关系型数据库PostgreSQL兼容版本(PolarDB PostgreSQL版(兼容Oracle))的一个时空引擎扩展,实现了PostgreSQL数据库与多种地理空间数据格式的集成和查询,它支持多格式数据源的映射为PostgreSQL的外部表,实现数据访问和查询优化。

Ganos FDW提供了对于多种空间数据类似的统一访问,Ganos FDW会自动将几何空间数据类型映射为Geometry字段类型,从而允许与数据库内部表进行统一的访问与查询。

主要业务场景

  • 数据集成

    Ganos FDW允许用户将多种地理空间数据格式(如Shapefile、GeoJSON、KML、GML等)与PostgreSQL数据库进行集成。通过配置外部数据源的连接信息和数据映射规则,用户可以在PostgreSQL中创建外部表,将外部数据源中的地理空间数据映射为PostgreSQL中的表结构。这样,不同格式的地理空间数据可以在PostgreSQL中进行统一的查询、分析和操作。

  • 数据转换和格式转换

    Ganos FDW提供了数据转换和格式转换的功能。用户可以将不同格式的地理空间数据源映射为PostgreSQL中的外部表,通过Ganos FDW可以将数据源的数据转换为PostgreSQL所需的格式和结构。这使得用户可以方便地进行数据格式的转换和集成。

  • 地理空间数据分析和查询

    Ganos FDW使得PostgreSQL可以直接查询和分析外部数据源中的地理空间数据。用户可以使用SQL查询语言对外部表进行查询,利用PostgreSQL提供的强大的地理空间分析函数和工具进行空间分析、空间关系查询、缓冲区分析等操作。

基本构成

FDW(Foreign Data Wrapper)是PostgreSQL数据库的一种扩展技术,它允许PostgreSQL在查询和操作数据时,与外部数据源进行交互。

Ganos FDW是基于PostgreSQL FDW技术实现的外部空间矢量数据包装器,允许用户在PostgreSQL中访问和查询外部空间矢量数据。

FDW

FDW(Foreign Data Wrapper)是一种用于PostgreSQL数据库的扩展模块,它允许PostgreSQL在查询和操作数据时,与外部数据源进行交互。

FDW的基本概念包括以下几个要点:

  • 外部数据源:FDW允许PostgreSQL连接和访问外部数据源,这可以是关系型数据库、文件系统、NoSQL数据库、Web服务等。外部数据源可以存储在本地或远程的服务器上。

  • 数据包装器(Wrapper):FDW提供了数据包装器,用于与外部数据源进行通信和数据交换。数据包装器是一个扩展模块,实现了PostgreSQL的FDW接口,通过这个接口,PostgreSQL可以与外部数据源进行交互。

  • 映射规则(Mapping Rules):FDW允许用户定义外部数据源中的表、列与PostgreSQL中的表、列之间的映射关系。这些映射规则指定了数据的转换、数据类型的对应关系等。用户可以通过映射规则自定义如何将外部数据源中的数据在PostgreSQL中进行表示和访问。

  • 查询优化器(Query Optimizer):FDW集成了PostgreSQL的查询优化器,可以对查询语句进行优化和转换,以提高查询性能。查询优化器可以将一部分查询操作下推到外部数据源中执行,减少数据传输和处理的开销。

  • 数据导入和导出:FDW提供了数据导入和导出的功能,允许用户将数据从外部数据源导入到PostgreSQL中,或将PostgreSQL中的数据导出到外部数据源中。这使得用户可以在PostgreSQL中进行数据分析、数据处理和数据传输等操作。

  • 安全性和权限控制:FDW提供了安全性和权限控制的功能,允许用户配置对外部数据源的访问权限。用户可以控制哪些用户或角色可以访问外部数据源,以及可以执行的操作类型,从而保护数据的安全性和完整性。

Ganos FDW

Ganos FDW是PostgreSQL数据库的扩展,使PostgreSQL数据库能够直接访问和查询多种地理空间数据格式。Ganos FDW允许用户在PostgreSQL中配置外部数据源,并将这些数据源中的地理空间数据映射为PostgreSQL中的外部表,从而实现地理空间数据的集成和查询。

Ganos FDW的主要特点和功能如下:

  • 多格式支持:Ganos FDW支持许多常见的地理空间数据格式,包括Shapefile、GeoJSON、KML、GML、MapInfo等。它可以将这些格式的数据源映射为PostgreSQL中的外部表,从而实现对这些数据的访问和查询。

  • 灵活的数据映射:Ganos FDW允许用户在PostgreSQL中配置外部数据源的连接信息和数据映射规则。用户可以定义外部表的结构,并将外部数据源中的要素、属性和几何数据映射为PostgreSQL中的列,使得外部数据源的数据可以像本地表一样进行查询和分析。

  • 查询优化:Ganos FDW在查询外部数据源时,使用了一些优化技术,如谓词下推和局部查询优化,以提高查询性能。它尽量减少从外部数据源中检索的数据量,只获取满足查询条件的结果,从而避免不必要的数据传输和处理。

  • 可写支持:除了查询外部数据源,Ganos FDW还支持在外部表上进行插入、更新和删除操作。用户可以通过Ganos FDW将变更操作传递给外部数据源,以更新和同步数据。

通过Ganos FDW,用户可以轻松地将多种地理空间数据格式集成到PostgreSQL中,并利用PostgreSQL提供的强大的查询和分析功能对这些数据进行处理。Ganos FDW提供了灵活的数据映射和查询优化机制,使外部数据源的数据可以像本地表一样进行查询和操作,为地理空间数据的集成和分析提供了便利。

更多内容请参见FDW SQL参考

快速入门

简介

快速入门文档帮助用户快速理解Ganos FDW引擎的基本用法,包括扩展创建、注册外表、外表查询、数据导入、进阶功能等内容。

更多专业用法可参考FDW最佳实践文章:Ganos 矢量栅格数据快速入库方法简介

语法说明

  • 创建扩展。

    CREATE extension ganos_fdw cascade;
    说明

    建议将扩展安装在public模式下,避免权限问题。

    CREATE extension ganos_fdw WITH schema public cascade;
  • 将空间数据文件注册为外表。

    -- 注册Shapefile
    SELECT ST_RegForeignTables('OSS://<ak>:<ak_secret>@<endpoint>/bucket/path/poly.shp');
    
    -- 通过information_schema.foreign_tables 视图可以查询注册的fdw表
    select foreign_table_name from information_schema.foreign_tables ORDER BYforeign_table_name ASC;
    说明

    需要使用oss内网endpoint进行访问。

  • 对外表进行查询。

    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
  • 通过INSERT命令导入数据到数据库表中。

    -- 通过CREATE AS SELECT 方式创建相同的表结构
    CREATE TABLE poly_db
    AS
    SELECT * FROM poly;
    
    -- 如果表已经存在可以通过INSERT INTO SELECT 的方式
    INSERT INTO poly_db
    SELECT * FROM poly;

    也可以通过IMPORT FOREIGN SCHEMA导入数据到数据库表中。

    CREATE SCHEMA imp;
    
    IMPORT FOREIGN SCHEMA ganos_fdw
      FROM SERVER ganos_fdw_server
      INTO imp;
  • 删除扩展(可选)。

    Drop extension ganos_fdw cascade;

使用进阶

Ganos遵循FDW接口规范,因此可以通过SQL方式创建外部表,实现对外部数据源的操作。

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>]');

其中:

  • datasource的路径不需要OSS的Access Key的ID以及secrect。

  • Endpoint为oss的地域节点。为保证数据的可访问性,请确保云数据库与OSS所在Region相同,并使用内部endpoint地址。  相关信息请参见OSS访问域名使用规则 信息。

  • format表示使用的数据源驱动程序,可以通过ST_FDWDrivers函数获得。如果未传入则表示使用默认的驱动尝试访问。

示例如下:

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 '');,

UserMapping

为确保OSS访问所需要的ID和secret,需要创建User Mapping。

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

示例如下:

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

Table

创建对应的外部表。

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

示例如下:

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

Import

可以通过IMPORT FOREIGN SCHEMA导入整个表定义,语法如下:

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

其中远程的schema名称固定为ganos_fdw。

示例如下:

CREATE SCHEMA imp;

IMPORT FOREIGN SCHEMA ganos_fdw
  FROM SERVER myserver
  INTO imp;

SQL参考

详细SQL手册请参见FDW SQL参考