Create metadata mappings and data synchronization for Hologres

Prerequisites

1. You have activated MaxCompute and DataWorks.

2. A MaxCompute project is created, and support for the schema level is enabled.

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

4. ApsaraDB RDS is activated.

5. Hologres is activated.

6. Flink is activated.

Step 1: Prepare an RDS MySQL data source

Create an ApsaraDB RDS for MySQL instance, create test tables, and write test data to the tables. This instance will be used as the data source.

1. Sign in to the RDS console.

   In the left navigation bar, select Instances, and in the top-left corner, select the region.

2. On the Instances page, click Create Instance.

   In this example, Billing method and Engine are set to pay-as-you-go and MySQL 8.0, respectively.

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

4. In the left navigation bar, click Accounts.

   Create a database logon account.

5. In the left navigation bar, click Databases.

   Click Create Database. Configure the following parameters:

   Parameter	Required	Description	Example
   Database Name	Required	The name must be 2 to 64 characters in length. It must start with a letter and end with a letter or a digit. It can contain only lowercase letters, digits, underscores (_), and hyphens (-). The database name must be unique within the instance. If the database name contains a hyphen (-), the hyphen (-) in the created database folder name is converted to @002d.	hologres_test
   Authorized By	Optional	Only standard accounts appear here. A privileged account has full permissions on all databases and does not require authorization.	Select the logon account you just created.

6. Click Log On to Database. In the left navigation bar, select Instance and double-click the created database. On the SQL Console page on the right, 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 the 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 a Hologres instance and a new database

Create a Hologres instance and a database. Flink uses this database to receive table data from RDS for MySQL.

1. Log on to the Hologres management console. In the upper-left corner, select a region.

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

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

   • Instance Type: Select Virtual Warehouse.

   • For Reserved Compute Resources for Virtual Warehouse, select 64 CU.

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

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

   Click the Metadata Management tab.

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

   In this example, the Hologres database is named holodb.

5. Click the Security Center tab.

   In the navigation pane on the left, click Users.

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

   • Grant the RAM role the Developer permission for the instance. This is supported only in SPM mode. For more information, see DB Management.

Step 3: Create a whole-database sync task for RDS for MySQL in Flink (ODS layer)

1. Create a whole-database sync task in Flink to synchronize data from RDS for MySQL to the public schema of the holodb Hologres database. Use the default init_warehouse compute group in Hologres to query the Operational Data Store (ODS) data.

2. Before you start, enable binary logging for MySQL. To check whether binary logging is enabled, run the show variables like "log_bin"; command in your RDS for MySQL database. For more information, see MySQL server configuration requirements.

3. Create a session cluster.

   1. Log in to the real-time compute for Apache Flink console and select a region in the upper-left corner.

   2. Click the target workspace name, and in the left navigation pane, choose O&M center > session management.

   3. Click Create Session Cluster.

4. Create a Hologres catalog.

   1. Click the name of the target workspace, and then in the left-side navigation pane, select Data Management.

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

      Parameter	Required	Description
      catalog name	Required	A custom name for the Hologres catalog.
      endpoint	Required	On the Hologres instance details page, go to the Network Information section and find the endpoint for the specified VPC. Example: hg****cn-cn-2****f-cn-shenzhen-vpc-st.hologres.aliyuncs.com:80.
      dbname	Required	The Hologres database name.
      username	Required	Your Access Key ID.
      password	Required	Your Access Key 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. Log on to the ApsaraDB RDS for MySQL console. On the instance details page, click Database Connection to view the internal endpoint, public endpoint, and internal port. For cross-VPC or public network access, ensure network connectivity. For more information, see network connectivity.
   port	Optional	The port for connecting to the server. The default value is 3306.
   default database	Required	The default database name.
   username	Required	The username to connect to the MySQL database. Log on to the ApsaraDB RDS for MySQL console. On the instance details page, click Accounts to view the username.
   password	Required	The password to connect to the MySQL database. Log on to the ApsaraDB RDS for MySQL console. On the 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 a 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 by CDAS.
      AS DATABASE <your mysql catalog>.<mysql database name> INCLUDING all tables -- Select which tables from the input database to ingest as needed.
      /*+ OPTIONS('server-id'='8001-8004') */ ;   -- Specify the server ID range for the mysql-cdc instance.

      In the top-right corner, click Deploy.

   4. On the Deployments page, click the target job to open the deployment details page.

   5. In the upper-right corner of the page, click Start, select stateless start, and then click Start.

   Note

   • By default, this example synchronizes data to the public schema of the Hologres database. You can also synchronize data to a specified schema in the destination Hologres database. For more information, see Use as a destination catalog for CDAS. After you specify a schema, the table name format also changes when you use the catalog. For more information, see Use a Hologres catalog.

   • If the data structure of a source table changes, the change is reflected in the sink table only after a data modification, such as a deletion, insertion, or update, occurs in the source table.

Step 4: Load data into Hologres

Table groups store data in Hologres.

To query data from a table group, such as order_dw_tg_default, in the holodb database using the init_warehouse virtual warehouse, you must load the table group into the virtual warehouse. You can then use the init_warehouse virtual warehouse to query and write data.

On the HoloWeb developer page, click SQL Editor, confirm the instance and database names, and then execute the following commands.

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

1. Log on to the Hologres management console. In the upper-left corner, select a region.

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

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

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

4. Click the SQL Editor tab.

   Confirm the instance and database names, and then run the following commands. After the table group is loaded, you can see that the compute resource group has loaded the data from 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 virtual warehouse.
   CALL hg_table_group_load_to_warehouse ('<hologres-database-name>.<table-group-name>', '<your-virtual-warehouse-name>', 1);
   
   -- View the table groups loaded in the virtual warehouse.
   SELECT * FROM hologres.hg_warehouse_table_groups;

5. Run the following commands to view the data in the three tables that were 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 a DWD layer table in Flink

This step uses the Hologres connector's partial column update capability. You can express partial updates with INSERT DML. The job queries multiple dimension tables by using high-performance point queries, enabled by Hologres row storage and hybrid row-column storage. With strong resource isolation, write, read, and analytics workloads do not interfere with each other.

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

1. Log in to the real-time compute for Apache Flink console and select a region in the upper-left corner.

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

3. On the Query Script tab, click to create a new query script.

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

   -- Fields in a wide table must be nullable. Different streams write to the same sink table, so 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
   );
   
   -- Use a catalog to modify the properties of a Hologres physical table.
   ALTER TABLE <hologres catalog>.<hologres database>.dwd_orders SET (
     'table_property.binlog.ttl' = '604800' -- Set the binlog TTL to one week.
   );

4. Consume the binlogs of the orders and orders_pay tables in the ODS layer 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 to the SQL editor, and then deploy and start the job. The orders table is joined with the product_catalog dimension table, and the final result is written to the dwd_orders table to enrich 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.

      On the HoloWeb developer page, connect to the Hologres instance and log on to the destination database. Then, run the following command in the SQL editor.

      SELECT * FROM dwd_orders;

      The following result is returned:

      +------------+---------------+---------------+------------------+----------------------------+------------+-------------------+-------------------+-------------+------------+--------------+-----------------+
      | 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            | 
      +------------+---------------+---------------+------------------+----------------------------+------------+-------------------+-------------------+-------------+------------+--------------+-----------------+

Step 6: Create a MaxCompute external data source for a Hologres database

When you map Hologres tables using an external schema, you do not need to create tables that contain Data Definition Language (DDL) metadata in MaxCompute. If the table schema or data in the source Hologres database changes, MaxCompute detects the changes in real time. You can then query the updated metadata and data.

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

2. In the left-side navigation pane, 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 parameters. The following tables describe the parameters.

   Parameter	Required	Description
   External Data Source Type	Required	Select Hologres.
   External Data Source Name	Required	A custom name. The name must meet the following requirements: Contains only lowercase letters, digits, and underscores (_). Is no more than 128 characters long. Example: holo_external_source.
   Description	Optional	Enter a description as needed.
   Connection Method	Required	The default value is classic network access (internal network).
   InstanceID	Required	Select the Hologres instance in the current region that you want to connect to.
   Host	Required	This is generated by the system by default.
   Port	Required	This is the system-generated default.
   DBNAME	Required	The name of the Hologres database that you want to connect to.
   Authentication And Authorization	Required	RAM role
   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/aliyunodpsholorole.
   Foreign Server Supplemental Properties	Optional	Additional properties for the external data source. After you specify these properties, tasks that use this data source can access the source system based on the defined behavior. Note For information about supported parameters, see future updates in the official documentation. More parameters will be supported as the product evolves.

Step 7: Attach MaxCompute and Hologres compute resources in DataWorks

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

2. Create a new 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 navigation pane on the left.

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

   Enter the basic information. For more information, see Attach compute resources.

5. Add and view MaxCompute projects and Hologres instances.

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

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

   3. Add and view MaxCompute projects and Hologres instances.

Step 8: Map a Hologres table to MaxCompute

• External table: Synchronize a single Hologres table as needed.

• External schema: Synchronize data at the schema level. This lets you grant permissions based on table names in MaxCompute.

• External project: Query a wider range of data using a user identity. After a user is added to the project, their table permissions are controlled in Hologres.