Hologres:Query data across databases (beta)

Limits

• Only Hologres V1.1 and later allow you to query data across databases. If the version of your Hologres instance is earlier than V1.1, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

• Cross-database queries are supported only for instances with the same major version and of Hologres V1.1 or later. For example, you cannot query data in a database of a Hologres instance V1.1 from a database in a Hologres instance V1.3.

• Only the data in Hologres internal tables can be queried across databases. You cannot query data in Hologres foreign tables or Hologres views across databases.

• Only the data in Hologres parent tables can be queried across databases. You cannot query data in Hologres child tables across databases.

• Only basic data types such as INT, BIGINT, and DATE are supported. Complex data types such as JSON are not supported.

• Statements such as UPDATE, DELETE, and TRUNCATE cannot be executed on foreign tables.

• The IP address of a Hologres instance is not fixed and may be subject to the IP address whitelist. We recommend that you do not configure an IP address whitelist when you query data across databases.

Procedure

To query data across databases, perform the following steps:

1. Create an extension.

   Before you query data across databases, you must execute the following statement to create an extension in a database as a superuser. An extension is created by a superuser of an instance at the database level. For each database, you need to create an extension only once.

   -- Create an extension.
   CREATE EXTENSION hologres_fdw;

   Note

   If you want to drop the extension, execute the following statement:

   DROP EXTENSION hologres_fdw;

2. Create a server.

   After an extension is created, execute the following statement to create a server. You can use the server to connect to the Hologres instance whose data you want to query across databases.

   Note

   You can create multiple servers for a database.

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

   Parameter	Description	Example
   server_name	The custom name of the server.	holo_fdw_server
   host	The classic network endpoint of the Hologres instance. You can view the classic network endpoint of the Hologres instance on the instance details page in the Hologres console.	hgpostcn-cn-xxx-cn-hangzhou-internal.hologres.aliyuncs.com
   port	The port number of the Hologres instance. You can view the port number of the Hologres instance on the instance details page in the Hologres console.	80
   dbname	The name of the source database whose data you want to query across databases.	testdb

3. Create a user mapping.

   After the server is created, execute the following statement to create a user mapping that is used for data queries. The specified user in the user mapping to be created must have the permissions to query data in the source database.

   Note

   You can create multiple user mappings for a database.

   CREATE USER MAPPING FOR <The unique ID (UID) of the Alibaba Cloud account> SERVER <server_name> 
   OPTIONS (
     access_id '<access_id>', 
     access_key '<access_key>'
   );

   Parameter	Description
   server_name	The name of the server that is created in Step 2.
   access_id	The AccessKey ID of the account that is used to connect to Hologres. You can obtain the AccessKey ID in the User Management console.
   access_key	The AccessKey secret of the account that is used to connect to Hologres. You can obtain the AccessKey secret in the User Management console.

   Examples:

   -- Create a user mapping for the current user.
   create user mapping for current_user server holo_fdw_server options
   (
       access_id 'LTAI5txxx', access_key 'y8LUUyyy'
   );
   
   -- Create a user mapping for the RAM user whose UID is 123xxx.
   create user mapping for "p4_123xxx" server holo_fdw_server options
   (
       access_id 'LIlY5txxx', access_key 'KsjkXKyyy'
   );
   
   -- Drop the user mappings.
   Drop USER MAPPING for CURRENT_USER server holo_fdw_server;
   Drop USER MAPPING for "p4_123xxx" server holo_fdw_server;

4. Create a foreign table.

   You can use one of the following methods to create a foreign table:

   • (Recommended) Execute the IMPORT FOREIGN SCHEMA statement to create a foreign table.

     The IMPORT FOREIGN SCHEMA statement is easy to use. Sample statement:

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

     Note

     When you synchronize the metadata of a source table to a foreign table, a large amount of metadata is read from the external database in which the source table resides. We recommend that you configure the LITMIT TO parameter to specify the source table whose metadata you require. This narrows down the synchronization scope and improves efficiency.

     Parameter	Description	Example
     holo_remote_schema	The name of the schema in which the source table to be queried across databases resides.	remote
     remote_table	The name of the source table that you want to query across databases. After the statement is executed, a foreign table with the same name as the source table is created in the current database.	lineitem
     server_name	The name of the server.	holo_fdw_server
     holo_local_schema	The name of the schema in which the foreign table resides.	local
     OPTION 'values'	The policies that are used to handle conflicts for creating the foreign table. Valid values: import_collate: specifies whether to include COLLATE options for columns. Default value: true. import_default: specifies whether to include DEFAULT expressions for columns. Default value: false. import_not_null: specifies whether to include NOT NULL constraints for columns. Default value: true.	import_not_null 'true'

   • Execute the CREATE FOREIGN TABLE statement to create a foreign table.

     The following SQL statement provides an example:

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

     Parameter	Description	Example
     local_table	The name of the foreign table. By default, the foreign table is created in the public schema. If you want to specify a schema, add the schema name before the table name in the Schema name.Table name format.	public.lineitem
     server_name	The name of the server.	holo_fdw_server
     remote_schema_name	The name of the schema in which the source table to be queried across databases resides.	public
     remote_table	The name of the source table that you want to query across databases.	holo_lineitem

5. Query data by using the foreign table.

   After the foreign table is created, you can query data by using the foreign table to implement cross-database queries. The following SQL statement provides an example:

   select * from <holo_local_table> limit 10;

6. (Optional) Import data to a Hologres internal table.

   If you want to import data across databases or instances, or if the query performance of the foreign table does not meet your expectations, you can execute the following SQL statement to import data to a Hologres internal table.

   Note

   Before you import data, you must create a Hologres internal table as the destination table. For more information about how to create an internal table, see Manage an internal table.

   insert into <holo_table> select * from <holo_local_table>;

Other operations

• Query the server.

  You can execute the following SQL statement to query the created server:

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

• Query the user mapping.

  You can execute the following SQL statement to query the created user mapping:

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

• Drop the user mapping.

  You can execute the following SQL statement to drop the created user mapping:

  DROP USER MAPPING FOR <UID of the Alibaba Cloud account> SERVER <server_name>;

  The server_name parameter specifies the name of the server.

• Drop the server.

  You can execute the following SQL statement to drop the created server.

  Important

  You must drop the user mapping and foreign table created on the server before you drop the server.

  DROP SERVER <server_name>;

  The server_name parameter specifies the name of the server.

Examples

This section provides relevant examples on how to query data across databases, including the preset configurations and complete sample code for the examples.

• Preset configurations

  Before you query data across databases, you must prepare a Hologres instance, create a Hologres database, and prepare relevant data in Hologres internal tables.

  • Instance-related configurations

    Parameter	Example
    ID of the source Hologres instance	hgpostcn-cn-i7mxxxxxxxxx
    Name of the source Hologres database	remote_db
    Name of the schema in the source Hologres database	remote
    Name of the internal table in the source Hologres database	lineitem
    Name of the parent table in the source Hologres database	holo_dwd_product_movie_basic_info

  • DDL statement used to create a 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 statements used to create Hologres partitioned tables

    -- Create a parent 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);
    comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_name" is 'Movie Name';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."director" is 'Director';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."scriptwriter" is 'Scriptwriter';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."area" is 'Region/Country';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."actors" is 'Actor';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."type" is 'Type';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_length" is 'Movie Length';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_date" is 'Release Date';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."movie_language" is 'Movie Language';
    comment on column "remote"."holo_dwd_product_movie_basic_info"."imdb_url" is 'IMDb URL';
    COMMIT;
    
    -- Create a child table for the 20170122 partition.
    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 data in a non-partitioned table across databases

  Note

  You must execute all of the following statements in the database from which you initiate a query across databases.

  -- Create an extension as a superuser.
  CREATE EXTENSION hologres_fdw;
  
  -- Create a server as a superuser.
  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 a user mapping for the current user.
  CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server 
  OPTIONS (access_id 'LTAIxxxxxx', access_key 'Trjgyyyyyyy');
  
  -- Create a schema. The local schema is optional for instances that use the foreign data wrapper (FDW) feature. You can use a business schema.
  CREATE SCHEMA local;
  
  -- Create a foreign table.
  IMPORT FOREIGN SCHEMA remote 
  LIMIT to (lineitem)
  FROM SERVER holo_fdw_server  
  INTO local
  OPTIONS ( 
    import_not_null 'true'
  );
  
  SELECT * FROM local.lineitem limit 10;

• Example 2: Query data in a partitioned table across databases

  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 a user mapping for the current user.
  CREATE USER MAPPING FOR CURRENT_USER SERVER holo_fdw_server 
  OPTIONS (access_id 'LTAIxxxxxx', access_key 'Trjgyyyyyyy');
  
  -- Create a schema. The local schema is optional for instances that use the FDW feature. You can use a business schema.
  CREATE SCHEMA local;
  
  -- Switch to the local instance, which is the instance that uses the FDW feature.
  IMPORT FOREIGN SCHEMA remote
  LIMIT to (holo_dwd_product_movie_basic_info)
  FROM SERVER holo_fdw_server
  INTO local
  OPTIONS (
    import_not_null 'true'
  );
  
  -- Query data in the entire table.
  SELECT * FROM local.holo_dwd_product_movie_basic_info limit 10;                    

• Example 3: Import data from a foreign table to an internal table

  -- Create a schema. The local schema is optional for instances that use the FDW feature. You can use a business schema.
  CREATE SCHEMA local;
  
  -- Create an 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;
  
  -- Import data to the internal table.
  insert into local.dwd_product_movie_basic_info select * from local.holo_dwd_product_movie_basic_info;
                      

Common error and troubleshooting

When you create a server, we recommend that you use a primary instance as the instance whose data you want to query. If the following error occurs, refer to the solution to troubleshoot the error.

• Problem description: When a read-only secondary instance is used as the instance whose data you want to query, an error message similar to the following one is reported:

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

• Solution: Execute the following SQL statement on the primary instance that corresponds to the read-only secondary instance whose data you want to query and on the instance from which you initiate the query across databases:

  ALTER DATABASE <database> SET hg_experimental_enable_dml_read_replica=ON;