DataWorks:Create a Hologres external table

Limits

• You can create a Hologres external table only for MaxCompute.

• You can create a Hologres external table in the DataWorks console only in the China (Shanghai) and China (Beijing) regions.

Procedure

1. Go to the DataStudio page.

   Log on to the DataWorks console. In the left-side navigation pane, choose Data Modeling and Development > DataStudio. On the page that appears, select the desired workspace from the drop-down list and click Go to DataStudio.

2. Create a workflow.

   If you have an existing workflow, skip this step.

   1. Move the pointer over the icon and select Create Workflow.

   2. In the Create Workflow dialog box, configure the Workflow Name parameter.

   3. Click Create.

3. Create a Hologres external table.
   1. Move the pointer over the icon and choose Create Table > Hologres > Table.
   2. In the Create Table dialog box, set Table Type to External Table and configure other parameters such as Engine Instance, Path, and Name.
      
4. Configure the Hologres external table.
   On the configuration tab of the Hologres external table, configure the parameters.
   1. Configure the parameters in the Basic attribute section.
      The following table describes the parameters in the Basic attribute section.

      Parameter	Description
      Type	The type of the Hologres external table. Note You can create a Hologres external table only for MaxCompute.
      Server List	The server where the source MaxCompute table resides. Note You can use the odps_server server that is created at the underlying layer of Hologres. For more information, see postgres_fdw.
      Table	The source MaxCompute table based on which you want to create a Hologres external table. The created Hologres external table is used to query data from the source MaxCompute table in the future. You must configure the following parameters based on the model type of your MaxCompute project: Two-layer model: project_name: the name of your MaxCompute project. table_name: the name of the source MaxCompute table. Three-layer model: project_name: the name of the MaxCompute project and the name of the schema to which the source MaxCompute table belongs. Specify the names in the odps_project_name#odps_schema_name format. table_name: the name of the source MaxCompute table. Note If the configurations of the preceding parameters do not meet the requirements of the MaxCompute project of a specific model type, an error is reported. Sample error: failed to import foreign schema:Table not found - table_xxx.

   2. Configure the parameters in the Physical Model section.

      Note

      The parameter configurations in the Physical Model section are only used to facilitate table management based on your business requirements and are not used to implement the underlying logic.

      

      Parameter	Description
      Theme	The level-1 folder and level-2 folder to which the Hologres internal table belongs. The parameters can be used to categorize tables based on business categories. You can store tables of the same business category in the same folder. Note The level-1 folder and level-2 folder are folders in DataWorks. You can easily manage tables in folders.
      Layer	The physical data layer to which the Hologres internal table belongs. Data layering is used to define and manage data layers. In most cases, data layers are categorized into data import layers, data sharing layers, and data analysis layers. You can store a table at a specific data layer based on the business category. Note You can click the icon to create a data layer. For more information, see Manage settings for tables.
      Category	The physical category of the Hologres internal table. The parameter is used to categorize tables in a finer-grained manner from the business dimension. In most cases, tables are categorized into tables at the basic service level, tables at the advanced service level, and tables at other levels. Note You can click the icon to create a physical category. For more information, see Manage settings for tables.

   3. Configure the parameters in the Table structure design section.
      The settings for the Table parameter that you configure in the Basic attribute section in Step 4 determine the table schema in the Table structure design section. The table schema is used to create the Hologres external table that is used to automatically read data from the source MaxCompute table. The table schema cannot be modified.

      Note Field type mappings exist between the source MaxCompute table and Hologres external table. Make sure that one-to-one mappings are established between the source MaxCompute table and Hologres external 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 the configuration of the Hologres internal table is complete, you can commit the table to the development and production environments. After the table is committed, you can query the table in the compute engine instance in the related environment.

   Note

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

   Operation	Description
   Load from Development Environment	Load the table information from the development environment and display the table information on the current page. Note You can perform this operation only after the table is committed to the development environment. After you perform this operation, the table information in the development environment overwrites the table information on the current page.
   Commit to Development Environment	Commit the table to the development environment. This way, the current table is created in the Hologres database in the development environment. After the table is committed, you can view the table schema in the Hologres folder of the related workflow in DataStudio. The folder is that you specified when you created the table.
   Load from Production Environment	Load the table information from the production environment and display the table information on the current page. Note You can perform this operation only after the table is committed to the production environment. After you perform this operation, the table information in the production environment overwrites the table information on the current page.
   Commit to Production Environment	Commit the table to the production environment. This way, the current table is created in the Hologres database in the production environment.