Hologres:Cross-database query (beta)

Step 1: Create the extension

A superuser must run the following statement once per database to install the FDW extension:

CREATE EXTENSION hologres_fdw;

Step 2: Create a server

After the extension is installed, create a server object that points to the remote Hologres instance:

CREATE SERVER <server_name> FOREIGN DATA WRAPPER hologres_fdw OPTIONS (
    host '<endpoint>',
    port '<port>',
    dbname '<dbname>'
);

Step 3: Create a user mapping

Create a user mapping to authenticate the cross-database connection. The mapped account must have SELECT permission on the source tables.

CREATE USER MAPPING FOR <account_uid> SERVER <server_name>
OPTIONS (
    access_id '<access_id>',
    access_key '<access_key>'
);

Examples:

-- Map the current user.
CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server OPTIONS (
    access_id 'yourAccessKeyId',
    access_key 'yourAccessKeySecret'
);

-- Map a RAM user.
CREATE USER MAPPING FOR "p4_123xxx" SERVER holo_fdw_server OPTIONS (
    access_id 'yourAccessKeyId',
    access_key 'yourAccessKeySecret'
);

-- Remove a user mapping.
DROP USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server;
DROP USER MAPPING FOR "p4_123xxx" SERVER holo_fdw_server;

Step 4: Create a foreign table

Use one of the following two methods to create a foreign table. The IMPORT FOREIGN SCHEMA method is simpler and is recommended for most cases.

IMPORT FOREIGN SCHEMA <holo_remote_schema>
[{ LIMIT TO | EXCEPT } (<remote_table> [, ...])]
FROM SERVER <server_name>
INTO <holo_local_schema>
[OPTIONS (<option> '<value>' [, ...])];

CREATE FOREIGN TABLE <local_table> (
    col_name type,
    ...
) SERVER <server_name>
OPTIONS (schema_name '<remote_schema_name>', table_name '<remote_table>');

Step 5: Query the foreign table

After the foreign table is created, query it directly:

SELECT * FROM <holo_local_table> LIMIT 10;

Step 6 (optional): Import data into a local internal table

If foreign table query performance does not meet your needs, or if you need a local copy of the data, import it into a Hologres internal table:

INSERT INTO <holo_table> SELECT * FROM <holo_local_table>;

Create the target internal table before running this statement. See Manage internal tables for details.

List servers

SELECT
    s.srvname AS "Name",
    pg_catalog.pg_get_userbyid(s.srvowner) AS "Owner",
    f.fdwname AS "Foreign-data wrapper",
    pg_catalog.array_to_string(s.srvacl, E'\n') AS "Access privileges",
    s.srvtype AS "Type",
    s.srvversion AS "Version",
    CASE WHEN srvoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')'
    END AS "FDW options",
    d.description AS "Description"
FROM
    pg_catalog.pg_foreign_server s
    JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid = s.srvfdw
    LEFT JOIN pg_catalog.pg_description d ON d.classoid = s.tableoid
        AND d.objoid = s.oid
        AND d.objsubid = 0
WHERE
    f.fdwname = 'hologres_fdw';

List user mappings

SELECT
    um.srvname AS "Server",
    um.usename AS "User name",
    CASE WHEN umoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(umoptions)), ', ') || ')'
    END AS "FDW options"
FROM
    pg_catalog.pg_user_mappings um
WHERE
    um.srvname != 'query_log_store_server';

Delete a user mapping

DROP USER MAPPING FOR <account_uid> SERVER <server_name>;

Delete a server

DROP SERVER <server_name>;

Source instance configuration

DDL for the source Hologres internal table

BEGIN;
CREATE SCHEMA remote;
CREATE TABLE "remote"."lineitem" (
    "l_orderkey"      int8        NOT NULL,
    "l_linenumber"    int8        NOT NULL,
    "l_suppkey"       int8        NOT NULL,
    "l_partkey"       int8        NOT NULL,
    "l_quantity"      int8        NOT NULL,
    "l_extendedprice" int8        NOT NULL,
    "l_discount"      int8        NOT NULL,
    "l_tax"           int8        NOT NULL,
    "l_returnflag"    text        NOT NULL,
    "l_linestatus"    text        NOT NULL,
    "l_shipdate"      timestamptz NOT NULL,
    "l_commitdate"    timestamptz NOT NULL,
    "l_receiptdate"   timestamptz NOT NULL,
    "l_shipinstruct"  text        NOT NULL,
    "l_shipmode"      text        NOT NULL,
    "l_comment"       text        NOT NULL
);
COMMIT;

DDL for the source Hologres partitioned table

BEGIN;
CREATE TABLE "remote"."holo_dwd_product_movie_basic_info" (
    "movie_name"     text,
    "director"       text,
    "scriptwriter"   text,
    "area"           text,
    "actors"         text,
    "type"           text,
    "movie_length"   text,
    "movie_date"     text,
    "movie_language" text,
    "imdb_url"       text,
    "ds"             text
)
PARTITION BY LIST (ds);
COMMIT;

-- Create a partition for '20170122'.
CREATE TABLE IF NOT EXISTS "remote".holo_dwd_product_movie_basic_info_20170122
    PARTITION OF "remote".holo_dwd_product_movie_basic_info
    FOR VALUES IN ('20170122');

Example 1: Query a non-partitioned table

-- Run as a superuser in the local database.

-- Step 1: Install the extension.
CREATE EXTENSION hologres_fdw;

-- Step 2: Create a server pointing to the remote instance.
CREATE SERVER holo_fdw_server FOREIGN DATA WRAPPER hologres_fdw OPTIONS (
    host 'hgpostcn-cn-i7mxxxxxxxxx-cn-hangzhou-internal.hologres.aliyuncs.com',
    port '80',
    dbname 'remote_db'
);

-- Step 3: Create a user mapping for the current user.
CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server
OPTIONS (access_id 'yourAccessKeyId', access_key 'yourAccessKeySecret');

-- Step 4: Create a local schema and import the foreign table.
CREATE SCHEMA local;

IMPORT FOREIGN SCHEMA remote
LIMIT TO (lineitem)
FROM SERVER holo_fdw_server
INTO local
OPTIONS (import_not_null 'true');

-- Step 5: Query the foreign table.
SELECT * FROM local.lineitem LIMIT 10;

Example 2: Query a partitioned table

-- Run as a superuser in the local database.

CREATE EXTENSION hologres_fdw;

CREATE SERVER holo_fdw_server FOREIGN DATA WRAPPER hologres_fdw OPTIONS (
    host 'hgpostcn-cn-i7mxxxxxxxxx-cn-hangzhou-internal.hologres.aliyuncs.com',
    port '80',
    dbname 'remote_db'
);

CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server
OPTIONS (access_id 'yourAccessKeyId', access_key 'yourAccessKeySecret');

CREATE SCHEMA local;

-- Import the partitioned parent table (child tables are included automatically).
IMPORT FOREIGN SCHEMA remote
LIMIT TO (holo_dwd_product_movie_basic_info)
FROM SERVER holo_fdw_server
INTO local
OPTIONS (import_not_null 'true');

SELECT * FROM local.holo_dwd_product_movie_basic_info LIMIT 10;

Example 3: Import data from a foreign table into a local internal table

-- Create a local schema.
CREATE SCHEMA local;

-- Create the target internal table.
BEGIN;
CREATE TABLE "local"."dwd_product_movie_basic_info" (
    "movie_name"     text,
    "director"       text,
    "scriptwriter"   text,
    "area"           text,
    "actors"         text,
    "type"           text,
    "movie_length"   text,
    "movie_date"     text,
    "movie_language" text,
    "imdb_url"       text,
    "ds"             text
);
COMMIT;

-- Copy data from the foreign table into the internal table.
INSERT INTO local.dwd_product_movie_basic_info
SELECT * FROM local.holo_dwd_product_movie_basic_info;

Error when querying a read-only replica

If you configure a read-only replica instance as the query target, you may see the following error:

internal error: Failed to get available shards for query[xxxxx], please retry later.

Run the following statement in both the primary instance of the read-only replica and the local (initiating) instance:

ALTER DATABASE <database> SET hg_experimental_enable_dml_read_replica=ON;

Use the primary instance as the query target whenever possible.