The Ganos_FDW extension follows the foreign data wrapper (FDW) specifications. You can use SQL statements to create foreign tables. You can also use the foreign tables that you create to manage external data sources.

Procedure

  1. Define a server.
    Syntax:
    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>]');
    The following table describes the parameters in the CREATE SERVER statement. For more information, see CREATE SERVER.
    Parameter Description
    datasource The data source that you want to access. For more information, see Object storage paths.
    format The driver that is used to access the data source. For more information, see ST_FDWDrivers. If you do not specify this parameter, the default driver is used.
    config_options The environment variables that you want to configure.
    open_options The options based on which the data source is accessed.
    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 '');
  2. Create a user mapping.
    Syntax:
    CREATE USER MAPPING 
    FOR <user_name> 
    SERVER <server_name> 
    OPTIONS (
      user '<oss_ak_id>', 
      password '<oss_ak_secret>');
    The following table describes the parameters in the CREATE USER MAPPING statement. For more information, see CREATE USER MAPPING.
    Parameter Description
    SERVER The name of the server. The value of this parameter is the same as the value of the server_name parameter in the CREATE SERVER statement that you execute in Step 1.
    user The AccessKey ID of the AccessKey pair. For more information, see Obtain an AccessKey pair.
    password The AccessKey secret of the AccessKey pair.
    Example:
    CREATE USER MAPPING 
    FOR CURRENT_USER 
    SERVER myserver 
    OPTIONS (
      user 'id', 
      password 'secret');
  3. Create a foreign table.
    Syntax:
    CREATE FOREIGN TABLE <table_name> (
     column_name data_type
      [, ...]
    ) SERVER <server_name>
    OPTIONS (layer '<layer_name>');
    The following table describes the parameters in the CREATE FOREIGN TABLE statement. For more information, see CREATE FOREIGN TABLE.
    Parameter Description
    SERVER The name of the server. The value of this parameter is the same as the value of the server_name parameter in the CREATE SERVER statement that you execute in Step 1.
    layer The name of the layer that is associated with the foreign table.
    Example:
    CREATE FOREIGN TABLE example_table (
      fid bigint,
      name varchar,
      age varchar,
      value varchar
    ) SERVER myserver
    OPTIONS (layer 'poly');
  4. Import the definition of the foreign table.
    Syntax:
    IMPORT FOREIGN SCHEMA ganos_fdw
        [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
        FROM SERVER <server_name>
        INTO <local_schema>
    Example:
    CREATE SCHEMA imp;
    
    IMPORT FOREIGN SCHEMA ganos_fdw
      FROM SERVER myserver
      INTO imp;
    Note
    • If you use a remote schema, the value of the SCHEMA parameter is fixed as ganos_fdw.
    • You can execute the CREATE SCHEMA statement to create a local schema.
    • For more information about the parameters in the IMPORT FOREIGN SCHEMA statement, see IMPORT FOREIGN SCHEMA.