Hologres:Requête inter-bases de données (bêta)

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;

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

-- 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;

-- 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;

-- 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;

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

ALTER DATABASE <database> SET hg_experimental_enable_dml_read_replica=ON;