All Products
Search
Document Center

ApsaraDB RDS:Quick start

Last Updated:Sep 22, 2023

A foreign data wrapper (FDW) is an extension of PostgreSQL. You can use an FDW to access external data. External data sources include the data from the databases in your RDS instance and the data from other RDS instances. In Ganos, an FDW supports unified access to various spatial data and can automatically map geometric spatial data types to the Geometry type. You can use an FDW to access and query data from foreign tables and from the tables of your RDS instance in a unified manner.

Procedure

  1. Create the Ganos_FDW extension.

    You can use one of the following methods to create the Ganos_FDW extension:

    • Directly create the Ganos_FDW extension.

      CREATE EXTENSION ganos_spatialref;
      CREATE EXTENSION ganos_geometry;
      CREATE EXTENSION ganos_fdw;
    • Use the CASCADE keyword to create the Ganos_FDW extension.

      CREATE EXTENSION Ganos_FDW CASCADE;
  2. Register a spatial data file in the shapefile format as a foreign table.

    1. Register the shapefile as a foreign table.

      SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/path/poly.shp');
      Note

      The following list describes the parameters:

      • ak_id and ak_secret specify the AccessKey ID and AccessKey secret of the AccessKey pair that is used to access your Object Storage Service (OSS) bucket. For more information, see Obtain an AccessKey pair.

      • endpoint specifies the endpoint of the OSS bucket. To ensure data accessibility, make sure that your RDS instance and your OSS bucket reside in the same region and are connected by using the internal OSS endpoint. For more information, see OSS endpoints.

      • /path/poly.shp specifies the directory of the .shp file in your OSS bucket.

    2. Query the foreign table by using the information_schema.foreign_tables view.

      SELECT foreign_table_name FROM information_schema.foreign_tables ORDER BY foreign_table_name ASC;
  3. Query data from the foreign table.

    SELECT fid, ST_AsText(geom), name, age, height FROM poly WHERE fid = 1;

    Sample output:

    fid  | ST_AsText(geom)                  | name      | age |  height
    --------------------------------------------------------------------
       1 | POLYGON((5 0,0 0,0 10,5 10,5 0)) | ZhangShan |  35 |   1.84 
  4. Import the data of the foreign table into a table that is created in your RDS instance.

    • If no table is created in your RDS instance, run the following command to create a table and insert the data into the table:

      CREATE TABLE poly_db AS SELECT * FROM poly;
    • If a table is created in your RDS instance, you can use one of the following methods to import the data into the table:

      • Use the INSERT SELECT statement.

        INSERT INTO poly_db SELECT * FROM poly;
      • Use the IMPORT FOREIGN SCHEMA statement.

        CREATE SCHEMA imp;
        
        IMPORT FOREIGN SCHEMA ganos_fdw
          FROM SERVER ganos_fdw_server
          INTO imp;
  5. Optional. Delete the Ganos_FDW extension.

    DROP EXTENSION Ganos_FDW CASCADE;