Create a Hologres external table-DataWorks(DataWorks)-阿里云帮助中心

You can create a Hologres external table by using DDL statements or the visual tools in DataWorks. This topic describes how to use the visual tools in DataWorks to create a Hologres external table.

Prerequisites

Your workspace has a Hologres compute resource bound to DataStudio. For more information, see DataStudio (old version): Bind a Hologres compute resource.
The user who creates the table must have a role with development permissions, such as Workspace Administrator or Development. For more information, see Manage permissions on services at the workspace level.

Background information

Hologres supports two types of tables: internal tables and external tables. They differ in the following ways:

Internal table: Stores data physically within Hologres. You can synchronize data from a MaxCompute source table to a Hologres internal table for fast query and analysis. This method provides better query performance than using an external table.
External table: Does not store data. Instead, it maps to a MaxCompute source table to accelerate query and analysis. This method avoids data redundancy and eliminates the need for data import and export, allowing you to get query results quickly.

DataWorks, as a data development platform, provides a convenient visual method for creating tables. To create multiple Hologres external tables at a time, you can use the Schema synchronization from MaxCompute feature. You can also create tables directly in Hologres by using DDL statements. For more information, see CREATE FOREIGN TABLE.

Limits

You can create only Hologres external tables that are mapped to MaxCompute tables.
This feature is available only in the China (Shanghai) and China (Beijing) regions.

Procedure

Go to the Data Studio page.

Log on to the DataWorks console. In the target region, click Data Integration > Data Integration in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Integration.
Create a Workflow.

If you already have a Workflow, skip this step.
1. Hover over the icon and select Create Workflow.
2. In the Create Workflow dialog box, enter a Workflow Name.
3. Click Create.
Create a Hologres external table.
1. Hover over the icon and choose Create Table > Hologres > Table.
2. In the Create Table dialog box, select External Table for Table Type, and configure the engine, path, and name for the table.
  
  After you complete the configuration, click Create.

Configure the Hologres external table.

On the configuration page for the Hologres external table, configure the properties of the table.

Configure basic properties.

The following table describes the main basic properties.

Parameter	Description
Type	The type of Hologres external table to create. Note You can create only Hologres external tables that are mapped to MaxCompute tables.
Servers	The server that the external table uses to connect to the MaxCompute data source. Note To create an external table for MaxCompute, you must call the `odps_server` external table server that is already created in the underlying layer of Hologres. For more information about the principle, see postgres_fdw.
Table	The MaxCompute source table that the Hologres external table queries. You must configure the following parameters based on the model type of the MaxCompute project: two-layer model: project_name: the name of the MaxCompute project. table_name: the name of the MaxCompute source table to be queried. three-layer model: project_name: the name of the MaxCompute project and schema, in the format of `odps_project_name#odps_schema_name`. table_name: the name of the MaxCompute source table to be queried. Note If the configured parameters do not meet the requirements of the model type, an error is reported. Example: `failed to import foreign schema:Table not found - table_xxx`.

Configure business information.

Note

A table's business information is for management purposes and does not affect its underlying logic.

Parameter	Description
Theme	The level-1 and level-2 folders to which the table belongs. You can classify tables based on their business purposes and add tables of the same type to the same folder. Note The level-1 and level-2 themes appear as folders in DataWorks to help you manage tables.
Layer	The data warehouse layer to which the table belongs. You can use layers to define and manage data warehouse layers, which are usually divided into the landing, common, and application layers. You can add a table to an appropriate layer based on its business purpose. Note Click the icon to create a custom layer. For more information, see Manage tables.
Category	The category of the table. You can further classify a table from a business perspective. Categories usually include Basic Service, Advanced Service, and Other. Note Click the icon to create a custom category. For more information, see Manage categories.

Configure the table schema.

On the Table Structure Design page, go to the Field Design tab and select the fields to map to the Hologres external table. In this example, the two text-type fields smischema and smitable are selected. After you configure the Table parameter under Configure basic properties in Step 4, the schema from the MaxCompute source table is automatically displayed. This schema is used to create the final Hologres external table and cannot be edited.

Note
When you create a Hologres external table based on a MaxCompute table, data types are mapped between the two. The field types in the external table must correspond one-to-one with the field types in the MaxCompute source table. For information about data type mappings between MaxCompute and Hologres, see Data type mappings between MaxCompute and Hologres.

Commit and deploy the Hologres external table.

After you define the table schema, you must commit it to the development environment and the production environment. Once the table is committed, you can view it in the corresponding engine project.

Note

If you use a workspace in basic mode, you need to commit the table only to the production environment. For more information about the differences between basic mode and standard mode, see Differences between workspace modes.

Actions	Description
Load from Development Environment	Loads the information about the table from the development environment to the current page. Note You can perform this operation only after the table is committed to the development environment. Performing this operation overwrites the information on the current page with the table's information from the development environment.
Commit to Development Environment	Commits the table to the development environment of DataWorks. This creates the table in the Hologres database that corresponds to the development environment. After the table is committed, you can view its schema in the Hologres directory of the corresponding workflow (the path you selected when you created the table) in DataStudio.
Load from Production Environment	Loads the information about the table from the production environment to the current page. Note You can perform this operation only after the table is committed to the production environment. Performing this operation overwrites the information on the current page with the table's information from the production environment.
Commit to Production Environment	Commits the table to the production environment of DataWorks. This creates the table in the Hologres database that corresponds to the production environment.

Next steps

After the Hologres external table is created, you can use it to periodically import data from MaxCompute to a Hologres internal table. You can use one of the following methods to import data:

Import data by using SQL commands. For more information, see Import data from MaxCompute by using SQL.
Import data by using the visual tools in DataWorks. For more information, see Data synchronization from MaxCompute node.