Metadata mapping and data synchronization for Hologres

Prerequisites

1. MaxCompute and DataWorks have been activated.

2. A MaxCompute project has been created and schema-level hierarchy is enabled.

3. A RAM role has been created and a trust policy is configured.

4. ApsaraDB RDS is activated.

5. Hologres is activated.

6. Realtime Compute for Apache Flink is activated.

Step 1: Create an RDS for MySQL instance and prepare data

1. Log on to the RDS console.

   In the navigation pane on the left, click Instances. Then, select a region in the upper-left corner.

2. On the Instances page, click Create Instance.

   In this example, set Billing method to Pay-As-You-Go and Engine to MySQL 8.0.

3. On the Instances page, click the target instance's Instance ID/Name to open its details page.

4. In the navigation pane on the left, click Accounts > .

   Create a database logon account.

5. In the left navigation pane, click Databases.

   Click Create Database. Configure the following parameters:

   Parameter	Required	Description	Example
   Database Name	Yes	Must be 2 to 64 characters long. Must start with a letter and end with a letter or a digit. Can contain only lowercase letters, digits, underscores (_), and hyphens (-). Must be unique within the instance. If you use a - in a database name, the - in the created database folder name is converted to @002d.	hologres_test
   Authorized By	No	Only standard accounts are listed here. Privileged accounts have full permissions on all databases and do not need to be authorized.	the_account_from_step_4

6. Click Log On to Database. In the left navigation pane, select Database Instances. Double-click the database that you created. On the SQLConsole page, execute the following statements to create a test table and write test data.

   CREATE TABLE `orders` (
     order_id bigint not null primary key,
     user_id varchar(50) not null,
     shop_id bigint not null,
     product_id bigint not null,
     buy_fee numeric(20,2) not null,   
     create_time timestamp not null,
     update_time timestamp not null default now(),
     state int not null 
   );
   
   
   CREATE TABLE `orders_pay` (
     pay_id bigint not null primary key,
     order_id bigint not null,
     pay_platform int not null,
     create_time timestamp not null
   );
   
   
   CREATE TABLE `product_catalog` (
     product_id bigint not null primary key,
     catalog_name varchar(50) not null
   );
   
   -- Prepare data
   INSERT INTO product_catalog VALUES(1, 'phone_aaa'),(2, 'phone_bbb'),(3, 'phone_ccc'),(4, 'phone_ddd'),(5, 'phone_eee');
   
   INSERT INTO orders VALUES
   (100001, 'user_001', 12345, 1, 5000.05, '2023-02-15 16:40:56', '2023-02-15 18:42:56', 1),
   (100002, 'user_002', 12346, 2, 4000.04, '2023-02-15 15:40:56', '2023-02-15 18:42:56', 1),
   (100003, 'user_003', 12347, 3, 3000.03, '2023-02-15 14:40:56', '2023-02-15 18:42:56', 1),
   (100004, 'user_001', 12347, 4, 2000.02, '2023-02-15 13:40:56', '2023-02-15 18:42:56', 1),
   (100005, 'user_002', 12348, 5, 1000.01, '2023-02-15 12:40:56', '2023-02-15 18:42:56', 1),
   (100006, 'user_001', 12348, 1, 1000.01, '2023-02-15 11:40:56', '2023-02-15 18:42:56', 1),
   (100007, 'user_003', 12347, 4, 2000.02, '2023-02-15 10:40:56', '2023-02-15 18:42:56', 1);
   
   INSERT INTO orders_pay VALUES
   (2001, 100001, 1, '2023-02-15 17:40:56'),
   (2002, 100002, 1, '2023-02-15 17:40:56'),
   (2003, 100003, 0, '2023-02-15 17:40:56'),
   (2004, 100004, 0, '2023-02-15 17:40:56'),
   (2005, 100005, 0, '2023-02-15 18:40:56'),
   (2006, 100006, 0, '2023-02-15 18:40:56'),
   (2007, 100007, 0, '2023-02-15 18:40:56');

Step 2: Create Hologres instance and database

1. Log on to the Hologres Management Console, and in the upper-left corner, select the region.

   If you do not have an instance, purchase a Hologres instance.

   • For Product Type, select Exclusive Instance (pay-as-you-go).

   • For Instance Type, select Virtual Warehouse.

   • For Reserved Computing Resources of Compute Group, select 64 CU.

2. On the Instances page, click the target instance name.

3. On the instance details page, click Connect to Instance.

   Click the Metadata Management tab.

4. Click Create Database. In the dialog box that appears, enter a Database Name and keep the other parameters at their default settings.

   For this tutorial, the database name is holodb.

5. Click the Security Center tab.

   In the navigation pane on the left, click User Management.

   • Grant the SuperUser permission to the AliyunODPSDefaultRole role, allowing it to perform table operations in Hologres. For more information, see User Management.

   • Grant the Developer permission to the RAM role for the instance. This feature is supported only in Simple Permission Model (SPM) mode. For more information, see DB Management.

Step 3: Create a sync task for RDS for MySQL (ODS layer)

1. Create a full-database sync task in Flink to synchronize data from your RDS for MySQL database to the public schema of the holodb Hologres database. You can then use the default compute group init_warehouse in Hologres to query the ODS data.

2. Before you start, you must enable binary logging for the RDS for MySQL database. Run the show variables like "log_bin"; command to verify if binary logging is enabled. For more information, see MySQL Server Configuration Requirements.

3. Create a session cluster.

   1. Log on to the Flink console, and select a region in the upper-left corner.

   2. Click the name of the target workspace. In the navigation pane on the left, select O&M > Session Clusters.

   3. Click Create Session Cluster.

4. Create a Hologres catalog.

   1. Click the name of the target workspace. In the navigation pane on the left, select Catalogs .

   2. On the Catalog List page, click Create Catalog. In the Create Catalog dialog box, select Hologres, click Next, and then configure the following parameters:

      Parameter	Required	Description
      catalog name	Required	You can customize the MySQL catalog name.
      endpoint	Required	You can obtain the endpoint for the Select VPC from the Network Information section on the Hologres instance details page: hg****cn-cn-2****f-cn-shenzhen-vpc-st.hologres.aliyuncs.com:80.
      dbname	Required	The name of the Hologres database.
      username	Required	The AccessKey ID.
      password	Required	The AccessKey secret.

5. Create a MySQL catalog.

   On the Catalog List page, click Create Catalog. In the Create Catalog dialog box, select MySQL, click Next, and configure the following parameters:

   Parameter	Required	Description
   catalog name	Required	A custom name for the MySQL Catalog.
   hostname	Required	The IP address or hostname of the MySQL database. You can log on to the RDS MySQL console, and on the database instance details page, click Database Connection to view the Internal Endpoint, Public Endpoint, and Internal Port. You must establish network connectivity for cross-VPC or Internet access. For more information, see Network Connectivity.
   port	Default	The port used to connect to the server. The default value is 3306.
   default database	Required	The name of the default database.
   username	Required	The username used to connect to the MySQL database server. Log on to the ApsaraDB RDS for MySQL console. On the database instance details page, click Accounts to view the username.
   password	Required	The password used to connect to the MySQL database server. You can log on to the ApsaraDB RDS for MySQL console. On the database instance details page, click Accounts to view the password.

6. Synchronize data from RDS for MySQL to Hologres using Flink.

   1. Click the target workspace name, and then in the left navigation pane, select Development > ETL.

   2. On the Drafts tab, click to create a new folder.

   3. Right-click the folder and select New Blank Stream Draft. In the New Draft dialog box, enter a Name and select an Engine Version.

      CREATE DATABASE IF NOT EXISTS <your hologres catalog>.<hologres database name>    -- The table_property.binlog.level parameter was set when the catalog was created, so binary logging is enabled for all tables created using CDAS.
      AS DATABASE <your mysql catalog>.<mysql database name> INCLUDING all tables -- You can select the tables from the source database that you want to synchronize.
      /*+ OPTIONS('server-id'='8001-8004') */ ;   -- Specify the server-id range for the MySQL CDC instance.

      In the upper-right corner, click Deploy.

   4. On the Deployments page, click the name of the target job to open its Configuration page.

   5. In the upper-right corner of the deployment details page for the target job, click Start, select Initial Mode, and then click Start.

   Note

   • By default, this tutorial synchronizes data to the public schema of the Hologres database. You can also synchronize data to a specific schema in the target Hologres database. For more information, see Use as a destination catalog for CDAS. If you specify a schema, the naming format for tables in the catalog also changes. For more information, see Use a Hologres catalog.

   • If a source table's data structure changes, the destination table reflects the change only after a data modification (insert, update, or delete) occurs in the source table.

Step 4: Load data into Hologres

In Hologres, data is stored in table groups.

When you use the compute group init_warehouse to query data in the table group (in this example, order_dw_tg_default) of the holodb database, you must first load the table group for the compute group. You can then use the init_warehouse compute group to query and write data.

In HoloWeb, click the SQL Editor tab. After confirming the instance and database names, run the following commands.

For more information, see Create a new virtual warehouse instance.

1. Log on to the Hologres Management Console, and in the upper-left corner, select the region.

2. In the navigation pane on the left, click Instances.

   On the Instances page, click the target instance name.

3. On the instance details page, click Connect to Instance.

4. Click the SQL Editor tab.

   After you confirm the instance name and database name, run the following command. After the data is loaded, you can see that the computing resource group has loaded the data of the holodb_tg_default table group.

   -- View the table groups in the current database.
   SELECT tablegroup_name FROM hologres.hg_table_group_properties GROUP BY tablegroup_name;
   
   -- Load a table group into the compute group.
   CALL hg_table_group_load_to_warehouse ('<hologres database name>.<table group name>', '<your Virtual Warehouse name>', 1);
   
   -- Check the status of loaded table groups.
   SELECT * FROM hologres.hg_warehouse_table_groups;

5. Run the following commands to view the data in the three tables synchronized from MySQL to Hologres.

   --- Query data from the orders table.
   SELECT * FROM orders;
   
   --- Query data from the orders_pay table.
   SELECT * FROM orders_pay;
   
   --- Query data from the product_catalog table.
   SELECT * FROM product_catalog;

Step 5: Create DWD layer table in Flink

Building the DWD layer leverages the Hologres connector's partial column update capability. With this feature, you can use INSERT statements to perform partial column updates. The job uses high-performance point lookups from Hologres' row-based and hybrid row-columnar tables to query various dimension tables. Additionally, Hologres' strong resource isolation architecture ensures that write, read, and analytics jobs do not interfere with each other.

Use the Flink Catalog feature to create the DWD layer wide table dwd_orders in Hologres.

1. Log on to the Flink console, and select a region in the upper-left corner.

2. Click the target workspace name, and then in the navigation pane on the left, select Development > Scritps.

3. On the New Script tab, you can click to create a new query script.

   Enter the following code and click Run in the upper-right corner.

   -- Wide table fields must be nullable because different streams write to the same result table, and any column can be NULL.
   CREATE TABLE <hologres catalog>.<hologres database>.dwd_orders (
     order_id bigint not null,
     order_user_id string,
     order_shop_id bigint,
     order_product_id bigint,
     order_product_catalog_name string,
     order_fee numeric(20,2),
     order_create_time timestamp,
     order_update_time timestamp,
     order_state int,
     pay_id bigint,
     pay_platform int comment 'platform 0: phone, 1: pc', 
     pay_create_time timestamp,
     PRIMARY KEY(order_id) NOT ENFORCED
   );
   
   -- You can modify Hologres physical table properties through the catalog.
   ALTER TABLE <hologres catalog>.<hologres database>.dwd_orders SET (
     'table_property.binlog.ttl' = '604800' -- Set the binlog TTL to one week.
   );

4. Create a job to consume binlogs from the ODS layer tables orders and orders_pay in real time.

   1. Click the target workspace name, and then in the left navigation pane, select Development > ETL.

   2. Create an SQL stream job named DWD. Copy the following code into the SQL editor. Then, deploy and start the job. This job joins the orders table with the product_catalog dimension table and writes the result to the dwd_orders table, enriching the data in real time.

      BEGIN STATEMENT SET;
      
      INSERT INTO <your hologres catalog name>.<your hologres database name>.dwd_orders 
       (
         order_id,
         order_user_id,
         order_shop_id,
         order_product_id,
         order_fee,
         order_create_time,
         order_update_time,
         order_state,
         order_product_catalog_name
       ) SELECT o.*, dim.catalog_name 
         FROM <your hologres catalog name>.<your hologres database name>.orders as o
         LEFT JOIN <your hologres catalog name>.<your hologres database name>.product_catalog FOR SYSTEM_TIME AS OF proctime() AS dim
         ON o.product_id = dim.product_id;
      
      INSERT INTO <your hologres catalog name>.<your hologres database name>.dwd_orders 
        (pay_id, order_id, pay_platform, pay_create_time)
         SELECT * FROM <your hologres catalog name>.<your hologres database name>.orders_pay;
      
      END;

   3. View the data in the dwd_orders wide table.

      In HoloWeb, connect to your Hologres instance, log on to the target database, and run the following command in the SQL editor.

      SELECT * FROM dwd_orders;

      The result is shown below:

      +------------+---------------+---------------+------------------+----------------------------+------------+-------------------+-------------------+-------------+------------+--------------+-----------------+
      | order_id   | order_user_id | order_shop_id | order_product_id | order_product_catalog_name | order_fee  | order_create_time | order_update_time | order_state | pay_id     | pay_platform | pay_create_time | 
      +------------+---------------+---------------+------------------+----------------------------+------------+-------------------+-------------------+-------------+------------+--------------+-----------------+
      | 100002     | user_002      | 12346         | 2                | phone_bbb                  | 4000.04    | 2023-02-15 15:40:56 | 2023-02-15 18:42:56 | 1           | NULL       | NULL         | NULL            | 
      | 100004     | user_001      | 12347         | 4                | phone_ddd                  | 2000.02    | 2023-02-15 13:40:56 | 2023-02-15 18:42:56 | 1           | NULL       | NULL         | NULL            | 
      | 11111      | user_test     | 12346         | 2                | phone_bbb                  | 4000.04    | 2025-12-15 00:00:00 | 2025-12-15 00:00:00 | 1           | NULL       | NULL         | NULL            | 
      | 100001     | user_001      | 12345         | 1                | phone_aaa                  | 5000.05    | 2023-02-15 16:40:56 | 2023-02-15 18:42:56 | 1           | NULL       | NULL         | NULL            | 
      | 100007     | user_003      | 12347         | 4                | phone_ddd                  | 2000.02    | 2023-02-15 10:40:56 | 2023-02-15 18:42:56 | 1           | NULL       | NULL         | NULL            | 
      | 100006     | user_001      | 12348         | 1                | phone_aaa                  | 1000.01    | 2023-02-15 11:40:56 | 2023-02-15 18:42:56 | 1           | NULL       | NULL         | NULL            | 
      | 100005     | user_002      | 12348         | 5                | phone_eee                  | 1000.01    | 2023-02-15 12:40:56 | 2023-02-15 18:42:56 | 1           | NULL       | NULL         | NULL            | 
      | 100003     | user_003      | 12347         | 3                | phone_ccc                  | 3000.03    | 2023-02-15 14:40:56 | 2023-02-15 18:42:56 | 1           | NULL       | NULL         | NULL            | 
      +------------+---------------+---------------+------------------+----------------------------+------------+-------------------+-------------------+-------------+------------+--------------+-----------------+

1. In DataWorks, bind the MaxCompute project and the Hologres instance.

2. Go back to MaxCompute to create an external schema.

Step 6: Bind MaxCompute and Hologres resources

1. Log on to the DataWorks console and select a region in the upper-left corner.

2. Create a DataWorks workspace. In this tutorial, the workspace is named Hologres_DW_TEST.

3. On the Workspaces page, click the name of the target workspace.

4. On the Workspace Details page, click Computing Resource in the left-side navigation pane.

   On the Computing Resource page, click Associate Computing Resources and select MaxCompute and Hologres.

   Fill in the basic information. For more information, see Bind computing resources.

5. Add and view MaxCompute projects and Hologres instances.

   1. In the left-side navigation pane, choose Data Development and O&M > Data Development.

   2. Select Workspace, and click Go to Data Studio.

   3. Verify that the MaxCompute projects and Hologres instances appear.

Step 7: Create a MaxCompute external schema for a Hologres schema

Mapping Hologres tables with an external schema lets you query them in MaxCompute without creating corresponding tables and DDL metadata. MaxCompute automatically detects changes to the source table's structure or data, so you can query the latest metadata and data in real time.

1. Log on to the MaxCompute console, and select a region in the upper-left corner.

2. In the navigation pane on the left, choose Manage Configurations > External Data Source.

3. On the External Data Source page, click Create External Data Source.

4. In the Create External Data Source dialog box, configure the following parameters.

   Parameter	Required	Description
   External Data Source Type	Required	Select Hologres.
   External Data Source Name	Required	Enter a custom name. The name must meet the following requirements: It must start with a letter and can contain only lowercase letters, underscores (_), and digits. It must be no more than 128 characters long. Example: holo_external_source.
   Description	Optional	Enter a description for the data source.
   Connection Method	Required	The default value is classic network access (internal network).
   InstanceID	Required	Select the Hologres instance in the current region.
   Host	Required	Automatically generated by the system.
   Port	Required	Automatically generated by the system.
   DBNAME	Required	The name of the Hologres database.
   Authentication and authorization	Required	RAM Role Task executor: The external data source for a Hologres external project must be configured with the Task executor authentication mode.
   RoleARN	Required	The Alibaba Cloud Resource Name (ARN) of the RAM role. Log in to the RAM console. In the left navigation bar, select Identities > Roles. In the Basic Information section, you can obtain the ARN. Example: acs:ram::124****:role/aliyunodpsdefaultrole.
   Service-linked Role	Required	If you select Task executor, the Service-linked Role is acs:ram::124****:role/aliyunserviceroleformaxcomputeidentitymgmt.
   Foreign Server Supplemental Properties	Optional	Specify additional properties for the external data source. These properties define how tasks access the source system. Note Refer to the official documentation for the latest list of supported parameters. More parameters may be added in the future.

Step 8: Map Hologres and MaxCompute schemas

1. Log on to the DataWorks console and select a region in the upper-left corner.

2. In the left-side navigation pane, choose Data Development and O&M > Data Development.

3. Select Workspace, and click Go to Data Studio.

4. On the DataStudio page, click the icon in the left-side navigation pane to open the Data Catalog.

5. Expand the Hologres data catalog, right-click the target instance's schema (in this tutorial, public), and select Metadata Mapping to MaxCompute.

   On the Metadata Mapping to MaxCompute page, configure the parameters for the Hologres source and the MaxCompute destination.

   The key parameters for this tutorial are described below. You can keep the default values for the other parameters.

   Parameter	Description
   Project Find Method	Select DataWorks data source binding.
   Data Source	Select the name of the MaxCompute compute resource that is bound to DataWorks.
   External Schema Name	Enter a name for the external schema in MaxCompute that will map to the source Hologres schema. This tutorial uses public.
   External Data Source	Select the name of the Hologres federated data source that you created in MaxCompute. This tutorial uses holo_external_source.

6. In the upper-left corner of the page, click Run.

   After a successful run, you can see a MaxCompute external schema with the same name as the Hologres schema (public).

7. You can browse tables in Hologres and query their data from MaxCompute using the following SQL commands.

   SET odps.namespace.schema=true;
   SELECT * FROM public.dwd_orders;

Step 9: Create a MaxCompute external table

You can map some or all of the fields. For mapping rules, see the tblproperties parameter section in Hologres external tables.

1. Log on to the DataWorks console and select a region in the upper-left corner.

2. In the left-side navigation pane, choose Data Development and O&M > Data Development.

3. Select Workspace, and click Go to Data Studio.

4. On the DataStudio page, click the icon in the left-side navigation pane to open the Data Catalog.

5. Expand the Hologres data catalog. In the public schema of your target instance, right-click the dwd_orders table, and select Metadata Mapping to MaxCompute.

6. On the Metadata Mapping to MaxCompute page, configure the parameters for the Hologres source and the MaxCompute destination.

   The table below describes the key parameters for this tutorial. You can accept the default values for all other parameters. For more information, see single-table metadata mapping.

   Parameter	Description
   Instance search method	Select From DataWorks data sources.
   Data Source	Select the MaxCompute data source that you associated with DataWorks.
   Schema	Specify the name of the MaxCompute external schema that will store the mapped metadata from the source Hologres schema. In this tutorial, set this parameter to default.
   External table	Specify the name of the new external table in MaxCompute. Data from the source table is mapped to this table. By default, the name is the same as the Hologres table name. Creating an external table is a one-time operation, and metadata is not automatically refreshed. To refresh the metadata, you must delete the current external table and re-create the mapping.
   Permissions to Access MaxCompute External Table	Select Dual-signature. If you select RamRole, you must add the user and grant the necessary database permissions in Hologres.
   Lifecycle	Set the lifecycle for the table.

7. Click Run in the top-left corner of the page.

   After the run is successful, the new external table appears in the MaxCompute schema in the navigation pane on the left.

8. Run the following statements in MaxCompute to query the data in the external table.

   SET odps.namespace.schema=true;
   SELECT * FROM dwd_orders;

Step 10: Create a recurring Hologres sync task

To periodically archive DWD table data from the Hologres real-time data warehouse to an internal table in MaxCompute, create a data synchronization task and configure a recurring schedule.

1. Log on to the DataWorks console and select a region in the upper-left corner.

2. In the left-side navigation pane, choose Data Development and O&M > Data Development.

3. Select Workspace, and click Go to Data Studio.

4. On the DataStudio page, click the icon in the left-side navigation pane to go to the Data Development page and create a directory structure.

5. On the DataStudio page, click the icon in the left-side navigation pane to open the Data Catalog.

6. Expand the Hologres data catalog, right-click the dwd_orders table under the public schema of the target instance, and select Sync Data to MaxCompute.

7. On the Metadata Mapping to MaxCompute page, configure the parameters for the Hologres source and the MaxCompute destination.

   This tutorial describes only the key parameters. You can use the default values for the other parameters. For more information, see Single-table metadata mapping.

8. In the Create Node dialog box, name the table in the cloud data warehouse dwd_holo_orders, and click OK. You are redirected to the configuration page for synchronizing data from Hologres to MaxCompute. Configure the parameters for the Hologres source and the MaxCompute destination.

   The key parameter settings for this tutorial are described below. For more information, see Configure a synchronization node.

   Parameter	Description
   Data Source	Select the Hologres data source that is bound to your DataWorks workspace.
   Schema	Select the schema where you want to store the table.
   Table	Specify a name for the MaxCompute internal table. In this tutorial, set this parameter to dwd_holo_orders.
   Lifecycle	Set the lifecycle of the table as required.
   Import Method	Select the method to write data to the MaxCompute internal table: Overwrite: Deletes the existing data in the target table before writing the new data. Append: Preserves the existing data in the target table and adds the new data.
   Access Hologres permissions	Select an authentication method to access the Hologres instance: Dual-signature: Authenticates access by using your current identity. Ensure that this identity has read permissions on the MaxCompute table and permissions on the corresponding Hologres source table. RAM role: Authenticates access by using a specified RAM role. Grant the AliyunSTSAssumeRoleAccess policy to the RAM user. For more information, see RAM role-based authorization. After the permission is granted, specify the RAM role in the RAM role field.

9. On the right side of the page, click Schedule Config. On the Schedule Config page, configure the workflow and scheduling cycle. For details, see Configure scheduling properties for a node.

10. In the upper-left corner of the page, click Run.

    After the task runs successfully, the new internal table appears under MaxCompute in the left-side navigation pane. Use the following SQL statements to query the table:

    SET odps.namespace.schema=true;
    SELECT * FROM default.dwd_holo_orders;