DataWorks:Create a Hologres external 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

1. 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.

2. Create a Workflow.

   If you have an existing 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.

3. 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.

4. Configure the Hologres external table.

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

   1. 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.

   2. Configure business information.

      Note

      The business information of a table is only used for management purposes based on business requirements and does not involve underlying logic implementation.

      Parameter	Description
      Theme	The level-1 folder and level-2 folder to which the table belongs. You can classify tables based on business purposes and place tables of the same type in the same folder. Note Level-1 themes and level-2 themes are only the folder structure presented in DataWorks and are intended to help you manage tables.
      Layer	The physical data warehouse layer to which the table belongs. Layers are used to define and manage the hierarchy of a data warehouse. Typical layers include the staging layer, the shared layer, and the analytics layer. You can assign the table to an appropriate layer based on its business purpose. Note Click the icon to customize layers. For more information, see Table management.
      Physical Category	The physical category of the table. This provides a more detailed classification of the table from a business perspective. Typical categories include the basic business layer, the advanced business layer, and others. Note Click the icon to customize physical categories. For more information, see Category management.

   3. 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.

5. Commit and deploy the Hologres external table.

   After you define the table structure, you must submit it to the development environment and the production environment. The table is visible in the corresponding engine project only after it is successfully submitted.

   Note

   If your workspace is in basic mode, you only need to submit the table to the production environment. For more information about basic mode and standard mode workspaces, see Workspace mode differences.

   Operation	Description
   Load from Development Environment	Loads information about the development table from the development environment and displays it on the current page. Note This operation is available only after the table has been submitted to the development environment. After this operation is performed, the table information from the development environment overwrites the table information on the current page.
   Commit to Development Environment	Submits the table to the development environment of DataWorks, which creates the table in the Hologres database that corresponds to the development environment. After you submit the table, you can view the table structure in the Hologres directory of the corresponding workflow (the path you selected when creating the table) in Data Studio.
   Load from Production Environment	Loads information about the production table from the production environment and displays it on the current page. Note This operation is available only after the table has been submitted to the production environment. After this operation is performed, the table information from the production environment overwrites the table information on the current page.
   Commit to Production Environment	Submits the table to the production environment of DataWorks, which creates the table in the Hologres database that corresponds to the production environment.