DocsICP FilingConsole
Documentation
Home Page MaxCompute Developer Reference Data Lake Analytics (Lakehouse) External schema Hologres external schema

Hologres external schema

Updated at:
Copy as MD
Product
My Favorites

Hologres external schemas connect MaxCompute to a Hologres database through JDBC to retrieve metadata in real time. You can read snapshots of underlying Hologres files through JDBC or direct Pangu file reads.

Function introduction

Hologres is a real-time data warehouse engine that supports real-time writing, updating, and analysis of massive datasets. It is compatible with standard SQL (PostgreSQL protocol) and supports OLAP and ad hoc analysis at petabyte scale, as well as high-concurrency, low-latency data serving. Hologres is deeply integrated with MaxCompute for creating, analyzing, and querying OLAP models. By creating Hologres external data sources in MaxCompute, you enable the following federation scenarios:

  • Reading data from a real-time data warehouse to archive it in a large-scale data warehouse.

  • Reading dimension data or data mart model data for contextual computing with fact tables from the ODS, DWD, and DWS layers.

  • Reading OLAP model data for high-performance, low-cost offline computing, then returning results to the real-time data warehouse for analysis through 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 grant permissions to a RAM role in Hologres, then specify that RAM role in MaxCompute to map to a Hologres schema. Data objects within the external schema are managed by 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. Tasks that use this data source 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 Schema