If you want to accelerate queries on MaxCompute data in Hologres, you can use the MaxCompute metadata mapping feature provided by data catalogs to map the metadata of MaxCompute tables to Hologres. This allows you to accelerate queries on MaxCompute data by using Hologres foreign tables.
Background information
Foreign tables and external databases of Hologres can be used to accelerate queries on data in external data sources such as MaxCompute. You can directly perform operations on external data in Hologres without importing the data into Hologres internal storage.
The MaxCompute metadata mapping feature in DataWorks leverages the foreign table capability of Hologres to map specified MaxCompute data to Hologres by creating MaxCompute foreign tables in Hologres. This helps accelerate queries on MaxCompute data.
DataWorks allows you to quickly map MaxCompute metadata at project, schema, and table levels to Hologres in a visualized manner.
Project-level metadata mapping: This type of mapping is implemented based on a Hologres external database. For information about how to create a Hologres external database, see CREATE EXTERNAL DATABASE.
Schema-level metadata mapping: This type of mapping is implemented based on Hologres foreign tables created by using the Auto Load feature.
Table-level metadata mapping: This type of mapping is implemented based on a Hologres foreign table created by using the CREATE FOREIGN TABLE statement.
For more information about accelerating queries on MaxCompute data by using Hologres, see Accelerated queries on MaxCompute data.
Limits
Only MaxCompute internal projects support mapping of MaxCompute metadata to Hologres.
Only Hologres V3.0.7 and later support mapping of MaxCompute metadata to Hologres.
MaxCompute metadata can only be mapped to Hologres exclusive instances.
Precautions
Data types supported by MaxCompute and those supported by Hologres are different, and some data types do not support mapping. Before you map MaxCompute metadata to Hologres, you must carefully read Data type mappings between MaxCompute and Hologres and then determine whether your business requirements can be met as expected.
Entry point for the feature
Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a desired region. Find the desired workspace and choose in the Actions column.
In the left-side navigation pane of the Data Studio page, click the
icon to go to the DATA CATALOG pane.In the MaxCompute directory of the DATA CATALOG pane, find the MaxCompute project, schema, or table whose metadata needs to be mapped to Hologres, right-click the project, schema, or table name, and then select Metadata Mapping to Hologres.
Project-level metadata mapping
The MaxCompute project-level metadata mapping feature creates a Hologres external database with the same name as the MaxCompute project in the specified Hologres instance to map the metadata of the MaxCompute project to the Hologres instance by using foreign tables.
In the MaxCompute directory of the DATA CATALOG pane, find the MaxCompute project whose metadata needs to be mapped to Hologres, click the
icon, and then select Metadata Mapping to Hologres.On the Metadata Mapping to Hologres tab, configure the parameters.
MaxCompute Source
Parameter
Description
Source Object Type
The type of the object whose metadata needs to be mapped to Hologres. The value of this parameter is fixed as
MaxCompute Project.Source Object Name
The name of the project whose metadata needs to be mapped to Hologres. By default, the value of this parameter is fixed as the name of the current MaxCompute project.
Hologres Destination
Parameter
Description
Instance Search Method
The way in which you want to search for the Hologres instance. Valid values:
From DataWorks Data Sources: Select the desired Hologres instance by choosing the Hologres data source associated with the current workspace. If you select this value, you also need to configure the Data Source parameter to specify the Hologres instance. In addition, you must make sure that the access identity specified for the data source has the permissions of a Hologres super user and read and write permissions on source MaxCompute tables.
NoteOnly users who are assigned the O&M or Workspace Administrator role can select data sources added in the production environment.
I Am Authorized: Select the desired Hologres instance on which you have access permissions within the current Alibaba Cloud account. If you select this value, you also need to configure the Hologres Instance parameter. In addition, you must make sure that you have the permissions of a Hologres super user and read and write permissions on source MaxCompute tables.
NoteOnly Hologres V3.0.7 and later support mapping of MaxCompute metadata to Hologres.
MaxCompute metadata can only be mapped to Hologres exclusive instances.
External Database
The name of the external database to be created in the specified Hologres exclusive instance. The metadata of the MaxCompute project will be mapped to the Hologres external database in real-time. If you no longer need to continue mapping the metadata of the MaxCompute project to the Hologres external database, you can delete the external database.
In the top toolbar of the Metadata Mapping to Hologres tab, click Run. The system executes the CREATE EXTERNAL DATABASE statement to create a Hologres external database and map the metadata of the MaxCompute project to the Hologres external database.
Schema-level metadata mapping
The MaxCompute schema-level metadata mapping feature maps the metadata of a MaxCompute schema to Hologres by using foreign tables.
To enable the system to display MaxCompute schemas in the DATA CATALOG pane, you must enable the schema feature for the related MaxCompute project. For more information, see Schema-related operations.
In the MaxCompute directory of the DATA CATALOG pane, find the schema whose metadata needs to be mapped to Hologres, click the
icon, and then select Metadata Mapping to Hologres.On the Metadata Mapping to Hologres tab, configure the following parameters.
MaxCompute Source
Parameter
Description
Source Object Type
The type of the object whose metadata needs to be mapped to Hologres. The value of this parameter is fixed as
MaxCompute Schema.Source Object Name
The name of the MaxCompute schema whose metadata needs to be mapped to Hologres. By default, the value of this parameter is fixed as the name of the current MaxCompute schema.
Hologres Destination
Parameter
Description
Instance Search Method
The way in which you want to search for the Hologres instance. Valid values:
From DataWorks Data Sources: Select the desired Hologres instance by choosing the Hologres data source associated with the current workspace. If you select this value, you also need to configure the Data Source and Schema parameters to specify the Hologres instance and the schema of the Hologres instance. In addition, you must make sure that the access identity specified for the data source has read and write permissions on source MaxCompute tables and the Hologres instance.
NoteOnly users who are assigned the O&M or Workspace Administrator role can select data sources added in the production environment.
I Am Authorized: Select the desired Hologres instance on which you have access permissions within the current Alibaba Cloud account. If you select this value, you also need to configure the Hologres Instance, Database, and Schema parameters. In addition, you must make sure that you have read and write permissions on source MaxCompute tables and the Hologres instance.
NoteOnly Hologres V3.0.7 and later support mapping of MaxCompute metadata to Hologres.
MaxCompute metadata can only be mapped to Hologres exclusive instances.
Database
Select an existing database in the Hologres instance.
Schema
The name of the schema to be created in the Hologres exclusive instance. The metadata of the MaxCompute schema will be mapped to the Hologres schema.
NoteCreating a schema in the Hologres exclusive instance is a one-time action. After the schema is created, the metadata mapped to the schema is not automatically refreshed. To refresh the metadata, you must delete the schema and manually create a schema-level metadata mapping.
In the top toolbar of the Metadata Mapping to Hologres tab, click Run. The system uses the Auto Load feature to create Hologres foreign tables and map the metadata of the MaxCompute schema to the Hologres schema by using the Hologres foreign tables.
Table-level metadata mapping
The MaxCompute table-level metadata mapping feature maps a specified MaxCompute table to a specified Hologres table by using a Hologres foreign table. You can specify the path and name of the Hologres foreign table based on your business requirements.
In the MaxCompute directory of the DATA CATALOG pane, find the MaxCompute table whose metadata needs to be mapped to Hologres, right-click the table name, and then select Metadata Mapping to Hologres.
On the Metadata Mapping to Hologres tab, configure the following parameters.
MaxCompute Source
Parameter
Description
Source Object Type
The type of the object whose metadata needs to be mapped to Hologres. The value of this parameter is fixed as
MaxCompute Table.Source Object Name
The name of the MaxCompute table whose metadata needs to be mapped to Hologres. By default, the value of this parameter is fixed as the name of the current MaxCompute table.
Hologres Destination
Parameter
Description
Instance Search Method
The way in which you want to search for the Hologres instance. Valid values:
From DataWorks Data Sources: Select the desired Hologres instance by choosing the Hologres data source associated with the current workspace. If you select this value, you also need to configure the Data Source, Schema, and External Table parameters to specify the Hologres instance, the schema of the Hologres instance, and the Hologres external table. In addition, you must make sure that the access identity specified for the data source has read and write permissions on the source MaxCompute table and the Hologres instance.
NoteOnly users who are assigned the O&M or Workspace Administrator role can select data sources added in the production environment.
I Am Authorized: Select the desired Hologres instance on which you have access permissions within the current Alibaba Cloud account. If you select this value, you also need to configure the Hologres Instance, Database, Schema, and External Table parameters. In addition, you must make sure that you have read and write permissions on the source MaxCompute table and the Hologres instance.
NoteOnly Hologres V3.0.7 and later support mapping of MaxCompute metadata to Hologres.
MaxCompute metadata can only be mapped to Hologres exclusive instances.
Database
Select an existing database in the Hologres instance.
Schema
Select an existing schema in the specified database in the Hologres instance.
External Table
The name of the external table to be created in the specified database in the Hologres exclusive instance. The metadata of the MaxCompute table will be mapped to the Hologres external table.
NoteCreating an external table in the Hologres exclusive instance is a one-time action. After the external table is created, the metadata mapped to the external table is not automatically refreshed. To refresh the metadata, you must delete the external table and manually create a table-level metadata mapping.
Field
Configure the parameters on the Mapping Field and Partition Configurations tabs for the Hologres foreign table based on your business requirements.
Action for Table Name Conflicts
The policy that you want to use to process a table name conflict. Valid values:
Skip and Create No Table
Overwrite, Delete Existing Table and Create Another Table
Report Error
In the top toolbar of the Metadata Mapping to Hologres tab, click Run. The system executes the CREATE FOREIGN TABLE statement to create a Hologres foreign table and map the metadata of the MaxCompute table to the Hologres foreign table.
What to do next
View the created foreign tables in the Hologres directory displayed in the DATA CATALOG pane.
Create a Hologres SQL node on the Data Studio page and use the Hologres SQL node to accelerate queries on MaxCompute data by using foreign tables.