DataWorks:Map MaxCompute metadata to Hologres foreign tables

Background

Hologres uses foreign tables and external databases to accelerate queries on data from external sources, such as MaxCompute. This allows you to query external data directly in Hologres without importing it.

The metadata mapping feature in DataWorks uses the foreign table capabilities of Hologres to map specified MaxCompute data to Hologres by creating foreign tables. This enables accelerated queries on your MaxCompute data.

DataWorks provides a visual interface to quickly map MaxCompute metadata to Hologres at the project, schema, and table levels:

• Project-level metadata mapping: Uses the Hologres external database CREATE EXTERNAL DATABASE capability.

• Schema-level metadata mapping: Uses the Hologres Auto Load capability for foreign tables.

• Table-level metadata mapping: Uses the Hologres foreign table CREATE FOREIGN TABLE capability.

For more information about accelerating MaxCompute queries with Hologres, see Accelerate queries on MaxCompute data.

Limitations

• This feature is supported only for internal projects in MaxCompute.

• This feature is supported only in Hologres V3.0.7 and later.

• You can map MaxCompute metadata only to a Hologres exclusive instance.

Note

Data types in MaxCompute and Hologres differ, and some types cannot be mapped. Before you use this feature, carefully review Data type mappings between MaxCompute and Hologres and verify that the results meet your business requirements.

Access

1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a desired region. Find the desired workspace and choose Shortcuts > Data Studio in the Actions column.

2. In the left-side navigation pane, click to go to Data Directory.

3. In the MaxCompute directory, find the MaxCompute project, schema, or table whose metadata you want to map to Hologres. Right-click the object and select Map Metadata to Hologres.

Project-level metadata mapping

The project-level metadata mapping feature creates an external database in the specified Hologres instance with the same name as the MaxCompute project. The metadata of the MaxCompute project is mapped to Hologres through foreign tables.

1. In the MaxCompute directory, find the MaxCompute project whose metadata you want to map to Hologres. Click > Map Metadata to Hologres to open the metadata mapping configuration page.

2. Configure the parameters for project-level metadata mapping.

   • Source

     Parameter	Description
     Source Object Type	Confirm the object type whose metadata you want to map to Hologres. The value is fixed to MaxCompute Project.
     Source Object Name	Confirm the MaxCompute project whose metadata you want to map to Hologres. The value is fixed to the current MaxCompute project.

   • Hologres destination

     Parameter	Description
     Instance search method	Select how to find the Hologres instance. From DataWorks data source: Select the Hologres instance by choosing a Hologres data source associated with the current workspace. After you select this option, you also need to select a value for Data Sources to specify the destination Hologres instance. Make sure that the access identity specified in the data source has the SuperUser permissions on Hologres and read/write permissions on the source MaxCompute tables. Note Only users with the operator or workspace administrator role can select a production data source. I have permission: Select the destination Hologres instance from the instances that you have permission to access under the current Alibaba Cloud account. After you select this option, you also need to select a value for Hologres Instance. Make sure that you have the SuperUser permissions on Hologres and read/write permissions on the source MaxCompute tables. Note This feature is supported only in Hologres V3.0.7 and later. You can map MaxCompute metadata only to a Hologres exclusive instance.
     External Database	Define the name of the new external database to be created in the specified Hologres exclusive instance. Source metadata is mapped to the destination in real time. If you no longer need the mapping, you can delete the specified external database on the destination.

3. Click the Run button at the top. The system uses the Hologres external database CREATE EXTERNAL DATABASE capability to perform the project-level metadata mapping.

Schema-level metadata mapping

The schema-level metadata mapping feature maps the metadata under a MaxCompute schema to Hologres through foreign tables.

1. Under the MaxCompute project, find the schema whose data you want to map to Hologres. Click > Map Metadata to Hologres to open the metadata mapping configuration page.

2. Configure the parameters for schema-level metadata mapping.

   • Source

     Parameter	Description
     Source Object Type	Confirm the object type whose metadata you want to map to Hologres. The value is fixed to MaxCompute Schema.
     Source Object Name	Confirm the MaxCompute schema whose metadata you want to map to Hologres. The value is fixed to the currently selected schema.

   • Hologres destination

     Parameter	Description
     Instance search method	Select how to find the Hologres instance. From DataWorks data source: Select the Hologres instance by choosing a Hologres data source associated with the current workspace. After you select this option, you also need to select values for Data Sources and Schema to specify the destination schema in the Hologres instance. Make sure that the access identity specified in the data source has read/write permissions on both the source MaxCompute tables and the destination Hologres instance. Note Only users with the operator or workspace administrator role can select a production data source. I have permission: Select the destination Hologres instance from the instances that you have permission to access under the current Alibaba Cloud account. After you select this option, you also need to select values for Hologres Instance, Database, and Schema to specify the destination schema. Make sure that you have read/write permissions on both the source MaxCompute tables and the destination Hologres instance. Note This feature is supported only in Hologres V3.0.7 and later. You can map MaxCompute metadata only to a Hologres exclusive instance.
     Database	Select an existing database in the Hologres instance.
     Schema	Define the name of the new schema to be created in the specified Hologres exclusive instance. Source schema data will be mapped to this schema. Note Creating a schema is a one-time action. Metadata is not automatically refreshed. To refresh metadata, delete the current schema and manually recreate the schema-level metadata mapping.

3. Click the Run button at the top. The system uses the Hologres Auto Load capability for foreign tables to perform the schema-level metadata mapping.

Table-level metadata mapping

The table-level metadata mapping feature maps a specified MaxCompute table to a designated table in Hologres through a foreign table. You can specify the foreign table path and customize the foreign table name.

1. Under the MaxCompute project, find the MaxCompute table whose metadata you want to map to Hologres. Right-click the table name and select Map Metadata to Hologres to open the metadata mapping configuration page.

2. Configure the parameters for table-level metadata mapping.

   • Source

     Parameter	Description
     Source Object Type	Confirm the object type whose metadata you want to map to Hologres. The default value is MaxCompute Table.
     Source Object Name	Confirm the MaxCompute table whose metadata you want to map to Hologres. The value is fixed to the currently selected MaxCompute table.

   • Hologres destination

     Parameter	Description
     Instance search method	Select how to find the Hologres instance. From DataWorks data source: Select the Hologres instance by choosing a Hologres data source associated with the current workspace. After you select this option, you also need to select values for Data Sources, Schema, and External Table to specify the destination foreign table in the Hologres instance. Make sure that the access identity specified in the data source has read/write permissions on both the source MaxCompute tables and the destination Hologres instance. Note Only users with the operator or workspace administrator role can select a production data source. I have permission: Select the destination Hologres instance from the instances that you have permission to access under the current Alibaba Cloud account. After you select this option, you also need to select values for Hologres Instance, Database, Schema, and External Table to specify the destination foreign table. Make sure that you have read/write permissions on both the source MaxCompute tables and the destination Hologres instance. Note This feature is supported only in Hologres V3.0.7 and later. You can map MaxCompute metadata only to a Hologres exclusive instance.
     Database	Select an existing database in the Hologres instance.
     Schema	Select an existing schema under the corresponding database in the Hologres instance.
     External Table	Define the name of the new foreign table to be created under the schema in the specified database of the Hologres exclusive instance. Source table data will be mapped to this table. Note Creating a foreign table is a one-time action. Metadata is not automatically refreshed. To refresh metadata, delete the current foreign table and manually recreate the metadata mapping.
     Field	Configure Mapping Fields and Partition Configurations for the destination foreign table as needed.
     Action for Table Name Conflicts	Select the conflict resolution policy when a table with the same name already exists: Skip: Do not create the table again. Overwrite: Delete the existing table and create a new one. Throw error

3. Click the Run button at the top. The system uses the Hologres foreign table CREATE FOREIGN TABLE capability to perform the table-level metadata mapping.

Next steps

• View the created foreign tables under Data Directory > Hologres.

• Use foreign tables to accelerate queries on MaxCompute data in a DataStudio > Hologres SQL node.