DocsICP FilingConsole
官方文档
首页

Hologres external schema

更新时间：
复制 MD 格式
产品详情
我的收藏

MaxCompute provides external schemas for Hologres. These schemas connect to a Hologres database through a Java Database Connectivity (JDBC) link to retrieve metadata in real time. You can also use these schemas to read snapshots of underlying Hologres files in real time using JDBC or direct Pangu file reads.

Function introduction

Hologres is a one-stop real-time data warehouse. It supports real-time writing, updating, and analysis of massive amounts of data. It supports standard SQL and is compatible with the PostgreSQL protocol. Hologres also supports online analytical processing (OLAP) and ad hoc analysis of petabyte-scale data, along with online data services (Serving) with high concurrency and low latency. Hologres is deeply integrated with MaxCompute. It supports the creation, analysis, and querying of OLAP models in a MaxCompute data warehouse. MaxCompute can create Hologres external data sources. This federation with Hologres enables the following scenarios:

  • Reading data from a real-time data warehouse and archiving it in a large-scale data warehouse.

  • You can read dimension data or data mart model data to perform contextual computing with fact tables from the operation data store (ODS), data warehouse detail (DWD), and data warehouse service (DWS) layers.

  • Reading OLAP model data for high-performance, low-cost offline computing and returning the model results to the real-time data warehouse for analysis using Hologres foreign tables or remote function invocations.

Hologres external schemas are mapped at the database.schema level and use a RAM role for authentication. You first grant permissions to a RAM role in Hologres and then specify that RAM role in MaxCompute to map to a Hologres schema. In MaxCompute, data objects within the external schema are managed using the MaxCompute permission system based on object names.

Procedure

Step 1: Prepare test data

If you already have Hologres data, skip this step.

Create a Hologres instance and a new database.

Step 2: Create a Hologres external data source

  1. Log in to the MaxCompute console and select a region in the upper-left corner.

  2. In the left-side navigation pane, choose Manage Configurations > External Data Source .

  3. On the External Data Source page, click Create External Data Source.

  4. In the Create External Data Source dialog box, configure the parameters. The following tables describe the parameters.

    Parameter

    Required

    Description

    External Data Source Type

    Required

    Select Hologres.

    External Data Source Name

    Required

    A custom name. The name must follow these rules:

    • Start with a letter and contain only lowercase letters, underscores (_), and digits.

    • Be no more than 128 characters in length.

    For example, holo_external_source.

    Description

    Optional

    Enter a description as needed.

    Connection Method

    Required

    The default value is classic network access (internal network).

    Instance ID

    Required

    Select the Hologres instance to connect to in the current region.

    Host

    Required

    The system generates this by default.

    Port

    Required

    The system generates this by default.

    Database name

    Required

    The name of the Hologres database to connect to.

    Authentication And Authorization

    Required

    Select RAM Role.

    Only the Alibaba Cloud RAM role method is supported for creating a Hologres external schema.

    Role ARN

    Required

    The Alibaba Cloud Resource Name (ARN) of the RAM role.

    1. Log in to the RAM console.

    2. In the left navigation bar, select Identities > Roles.

    3. In the Basic Information section, you can obtain the ARN.

    Example: acs:ram::124****:role/aliyunodpsdefaultrole.

    Foreign Server Supplemental Properties

    Optional

    Additional properties for the external data source. After you specify these properties, tasks that use this data source can access the source system according to the defined behavior.

    Note

    For information about supported parameters, see future updates to the official documentation. More parameters will be released as the product evolves.

  5. Click OK to create the external data source.

Step 3: Create an external schema

  1. Operations on external schemas can be performed only in a MaxCompute project that has the schema-level feature enabled.

    1. In the left-side navigation pane, choose Manage Configurations > Projects.

    2. On the Projects page, click Enable Schema in the Actions column for the target project.

  2. Before you perform schema operations or query data from an external schema, you must enable schema support for the SQL syntax. To do so, add the SET odps.namespace.schema=true; statement before your SQL statement.

  3. In a MaxCompute client, execute the following command to create an external schema that references the Hologres external data source:

    CREATE EXTERNAL SCHEMA IF NOT EXISTS <external_schema_name>
with  <holo_foreign_server_name>
ON '<holoschema_name>' ;

    The following list describes the parameters:

    • external_schema_name: The name of the external schema. For example, holo_schema_test.

    • holo_foreign_server_name: The name of the foreign server. In this example, the value is holo_external_source.

    • holoschema_name: The name of the Hologres schema to be mapped. In this example, the value is public.

Step 4: Access the data source system using SQL

  • List the tables in the Hologres schema.

    • Method 1:

      SHOW tables IN <external_schema_name>; -- external_schema_name is the name of the external schema.

    • Method 2:

      USE SCHEMA <external_schema_name>; -- external_schema_name is the name of the external schema.
SHOW tables;

  • Examples

    • Query the names of all tables in the external schema named holo_schema_test:

      SET odps.namespace.schema=true;
USE SCHEMA holo_schema_test;
SHOW TABLES;

      Sample result:

      ALIYUN$***_com:orders
ALIYUN$***_com:orders_pay
ALIYUN$***_com:product_catalog

    • Query the data in the orders table of the holo_schema_test schema in the test project.

      SELECT * FROM test.holo_schema_test.orders;

      Sample result:

      +------------+------------+------------+------------+------------+-------------+-------------+------------+
| order_id   | user_id    | shop_id    | product_id | buy_fee    | create_time | update_time | state      | 
+------------+------------+------------+------------+------------+-------------+-------------+------------+
| 100002     | user_002   | 12346      | 2          | 4000.04    | 2023-02-15 15:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100004     | user_001   | 12347      | 4          | 2000.02    | 2023-02-15 13:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100007     | user_003   | 12347      | 4          | 2000.02    | 2023-02-15 10:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100003     | user_003   | 12347      | 3          | 3000.03    | 2023-02-15 14:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100001     | user_001   | 12345      | 1          | 5000.05    | 2023-02-15 16:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100006     | user_001   | 12348      | 1          | 1000.01    | 2023-02-15 11:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100005     | user_002   | 12348      | 5          | 1000.01    | 2023-02-15 12:40:56 | 2023-02-15 18:42:56 | 1          | 
+------------+------------+------------+------------+------------+-------------+-------------+------------+

    • Insert data into the table.

      INSERT INTO test.holo_schema_test.orders VALUES(
  11111,
  'user_test',
  12346,
  2,
  4000.04,
  CAST('2025-12-15 00:00:00' AS TIMESTAMP),
  CAST('2025-12-15 00:00:00' AS TIMESTAMP),
  1);

SELECT * FROM test.holo_schema_test.orders;

      Sample result:

      +------------+------------+------------+------------+------------+-------------+-------------+------------+
| order_id   | user_id    | shop_id    | product_id | buy_fee    | create_time | update_time | state      | 
+------------+------------+------------+------------+------------+-------------+-------------+------------+
| 11111      | user_test  | 12346      | 2          | 4000.04    | 2025-12-15 00:00:00 | 2025-12-15 00:00:00 | 1          | 
| 100002     | user_002   | 12346      | 2          | 4000.04    | 2023-02-15 15:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100004     | user_001   | 12347      | 4          | 2000.02    | 2023-02-15 13:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100001     | user_001   | 12345      | 1          | 5000.05    | 2023-02-15 16:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100006     | user_001   | 12348      | 1          | 1000.01    | 2023-02-15 11:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100005     | user_002   | 12348      | 5          | 1000.01    | 2023-02-15 12:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100003     | user_003   | 12347      | 3          | 3000.03    | 2023-02-15 14:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100007     | user_003   | 12347      | 4          | 2000.02    | 2023-02-15 10:40:56 | 2023-02-15 18:42:56 | 1          | 
+------------+------------+------------+------------+------------+-------------+-------------+------------+

    • Copy data from the federated external table orders into the data warehouse.

      MaxCompute does not allow CREATE TABLE AS operations on external schemas. Therefore, you must switch to the Default schema by executing USE SCHEMA Default; and then execute the following statement:

      -- Copy data from the federated foreign table to the data warehouse.
CREATE TABLE orders_copy AS SELECT * FROM test.holo_schema_test.orders;

-- Query the copied data in the data warehouse.
SELECT * FROM orders_copy;

      Sample result:

      +------------+------------+------------+------------+------------+-------------+-------------+------------+
| order_id   | user_id    | shop_id    | product_id | buy_fee    | create_time | update_time | state      | 
+------------+------------+------------+------------+------------+-------------+-------------+------------+
| 11111      | user_test  | 12346      | 2          | 4000.04    | 2025-12-15 00:00:00 | 2025-12-15 00:00:00 | 1          | 
| 100003     | user_003   | 12347      | 3          | 3000.03    | 2023-02-15 14:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100006     | user_001   | 12348      | 1          | 1000.01    | 2023-02-15 11:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100005     | user_002   | 12348      | 5          | 1000.01    | 2023-02-15 12:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100001     | user_001   | 12345      | 1          | 5000.05    | 2023-02-15 16:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100007     | user_003   | 12347      | 4          | 2000.02    | 2023-02-15 10:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100002     | user_002   | 12346      | 2          | 4000.04    | 2023-02-15 15:40:56 | 2023-02-15 18:42:56 | 1          | 
| 100004     | user_001   | 12347      | 4          | 2000.02    | 2023-02-15 13:40:56 | 2023-02-15 18:42:56 | 1          | 
+------------+------------+------------+------------+------------+-------------+-------------+------------+
Previous：Hive+HDFS external schemaNext：Grant permissions on federated external tables