Create a Hologres External Table for Fast Queries - DataWorks

A Hologres external table maps directly to a MaxCompute table, letting you query MaxCompute data from Hologres as if it were stored in a native Hologres table — without copying the data. This topic describes how to create a Hologres external table in the DataWorks console.

Query performance on external tables is lower than on internal tables. If query speed is a priority, import the MaxCompute data into a Hologres internal table instead. For bulk imports, see Create a node to synchronize MaxCompute data with a few clicks.

Prerequisites

Before you begin, ensure that you have:

A Hologres data source added to your workspace and associated with DataStudio. See Add a Hologres data source and Associate a data source or a cluster with DataStudio.
The Workspace Manager or Development role in your workspace. See Manage permissions on workspace-level services.

Limits

Hologres external tables can only map MaxCompute tables. Other data sources are not supported.
Creating a Hologres external table in the DataWorks console is available only in the China (Shanghai) and China (Beijing) regions.

Background information

Hologres supports two table types:

Table type	Data storage	Use case
Internal table	Stores a copy of the source MaxCompute data in Hologres	Faster query performance; suitable when query speed matters more than storage cost
External table	No data copy; maps directly to the source MaxCompute table for accelerated queries and analysis	No redundant storage; suitable for ad hoc queries where data freshness is more important than speed

DataWorks provides a GUI-based workflow for creating external tables. Alternatively, create them with a DDL statement using CREATE FOREIGN TABLE. To create multiple external tables at once, see Create a node to synchronize schemas of MaxCompute tables with a few clicks.

Create a Hologres external table

The process has five steps:

Open DataStudio.
Create a workflow (skip if one already exists).
Create the external table entry.
Configure the external table.
Commit and deploy.

Step 1: Open DataStudio

Log on to the DataWorks console. In the left-side navigation pane, choose Data Modeling and Development > DataStudio. Select your workspace from the drop-down list and click Go to DataStudio.

Step 2: Create a workflow

Skip this step if you already have a workflow.

Move the pointer over the icon and select Create Workflow.
In the Create Workflow dialog box, enter a Workflow Name.
Click Create.

Step 3: Create the external table entry

Move the pointer over the icon and choose Create Table > Hologres > Table.
In the Create Table dialog box, set Table Type to External Table and configure Engine Instance, Path, and Name.

Step 4: Configure the external table

The configuration tab has three sections: Basic attribute, Physical Model, and Table structure design.

Basic attribute

Basic attribute section

Parameter	Description
Type	The type of the external table. Only MaxCompute is supported.
Server List	The server where the source MaxCompute table resides. Use the built-in `odps_server`, which is created at the Hologres underlying layer. See postgres_fdw for details.
Table	The source MaxCompute table to map. The required format depends on the model type of your MaxCompute project: Two-layer model: Set `project_name` to the name of your MaxCompute project, and `table_name` to the source table name. Three-layer model: Set `project_name` to `odps_project_name#odps_schema_name`, and `table_name` to the source table name. If the format does not match the model type, an error is reported: `failed to import foreign schema:Table not found - table_xxx`.

Physical Model

Parameters in this section are for table management and categorization only. They do not affect the underlying Hologres logic.

业务描述

Parameter	Description
Theme	The level-1 and level-2 folders in DataWorks for organizing tables by business category.
Layer	The physical data layer (for example, data import, data sharing, or data analysis) to which the table belongs. Click the icon to create a data layer. See Manage settings for tables.
Category	A finer-grained classification within the physical model. Click the icon to create a category. See Manage settings for tables.

Table structure design

Table structure design section

The table schema in this section is auto-generated from the Table parameter in the Basic attribute section and cannot be modified. Hologres reads the source MaxCompute table structure and maps each column using the corresponding data type.

Data type mappings between MaxCompute and Hologres must be one-to-one. See Data type mappings between MaxCompute and Hologres.

Step 5: Commit and deploy

After configuration is complete, commit the table to the development or production environment. After committing, the table is created in the Hologres database in the corresponding environment.

For workspaces in basic mode, commit only to the production environment. See Differences between workspaces in basic mode and workspaces in standard mode.

Operation	Description
Commit to Development Environment	Creates the table in the Hologres database in the development environment. After committing, the table schema is visible in the Hologres folder of the workflow in DataStudio. The folder shown is the one you specified when you created the table.
Load from Development Environment	Loads table information from the development environment to the current page. Available only after the table has been committed to the development environment. This operation overwrites the current page.
Commit to Production Environment	Creates the table in the Hologres database in the production environment.
Load from Production Environment	Loads table information from the production environment to the current page. Available only after the table has been committed to the production environment. This operation overwrites the current page.

What to do next

After the external table is created, use it to import MaxCompute data into Hologres internal tables on a regular schedule:

SQL: Import data from MaxCompute to Hologres by executing SQL statements
DataWorks console: Create a node to synchronize MaxCompute data with a few clicks