Import MaxCompute data to Hologres with DataWorks - Hologres

You can use DataWorks scheduling tasks to periodically import data from a MaxCompute partitioned table to a Hologres partitioned table. This ensures data integrity and accuracy, combines the strengths of both platforms, and improves data processing efficiency and reliability.

Prerequisites

Purchase and activate a Hologres instance. For more information, see Purchase a Hologres instance.
Activate MaxCompute and create a project. For more information, see Activate MaxCompute.
Activate DataWorks and create a workspace. For more information, see Create a workspace.

Usage notes

Ensure that the schema service is not enabled for the MaxCompute tenant or project. For more information about schemas, see schema operations.

Data preparation

This topic uses the partitioned table dwd_product_movie_basic_info from the MaxCompute public dataset public_data as an example. The following code shows the schema of the dwd_product_movie_basic_info table.

-- DDL for the MaxCompute partitioned table
CREATE TABLE IF NOT EXISTS public_data.dwd_product_movie_basic_info(
  movie_name STRING COMMENT 'The name of the movie.',
  director STRING COMMENT 'The director.',
  scriptwriter STRING COMMENT 'The scriptwriter.',
  area STRING COMMENT 'The production region or country.',
  actors STRING COMMENT 'The actors.',
  `type` STRING COMMENT 'The genre.',
  movie_length STRING COMMENT 'The movie duration.',
  movie_date STRING COMMENT 'The release date.',
  movie_language STRING COMMENT 'The language.',
  imdb_url STRING COMMENT 'The IMDB ID.'
) 
PARTITIONED BY (ds STRING) STORED AS ALIORC;

Procedure

Prepare MaxCompute data.
1. Log on to the DataWorks console, switch to the destination region, and in the left-side navigation pane, choose Data Analysis and Service > Data Analysis. Click Go to Data Analysis. In the left-side navigation pane, click the icon to go to the SQL Query page.
2. On the SQL Query page, enter the following SQL statement to view the data in the 20170112 partition, and then click Run.
```
SELECT * FROM public_data.dwd_product_movie_basic_info WHERE ds = '20170112';
```
Create a foreign table in Hologres.
The column order and data types of the foreign table must match those of the MaxCompute table.
1. Log on to the HoloWeb SQL Editor page.
2. Below the SQL Editor tab, click the icon to open an Ad-hoc Query window. In the toolbar of the query window, select the Instance Name and Database for your Hologres instance.
3. In the Ad-hoc Query editor, enter the following statement and click Run.
  The following statement uses the IMPORT FOREIGN SCHEMA command to create a Hologres foreign table named dwd_product_movie_basic_info.
```
IMPORT FOREIGN SCHEMA public_data LIMIT TO (dwd_product_movie_basic_info) FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'update');
```

Create an internal table in Hologres.

Create an internal table in Hologres to receive and store data.

On the HoloWeb development page, click Ad-hoc Query.

In the new Ad-hoc Query window, select your Instance Name and Database. Then, enter the following statement in the SQL editor and click Run.

Because this example imports data from a MaxCompute partitioned table, the target internal table in Hologres must also be a partitioned table.

Note

The following DDL statement is a simple example. For optimal query performance, create the table and set appropriate indexes based on your actual business requirements.

BEGIN;
CREATE TABLE "public"."holo_dwd_product_movie_basic_info" (
 "movie_name" TEXT,
 "director" TEXT,
 "scriptwriter" TEXT,
 "area" TEXT,
 "actors" TEXT,
 "type" TEXT,
 "movie_length" TEXT,
 "movie_date" TEXT,
 "movie_language" TEXT,
 "imdb_url" TEXT,
 "ds" TEXT
)
PARTITION BY LIST (ds);
CALL SET_TABLE_PROPERTY('"public"."holo_dwd_product_movie_basic_info"', 'orientation', 'column');
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."movie_name" IS 'The name of the movie.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."director" IS 'The director.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."scriptwriter" IS 'The scriptwriter.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."area" IS 'The production region or country.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."actors" IS 'The actors.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."type" IS 'The genre.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."movie_length" IS 'The movie duration.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."movie_date" IS 'The release date.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."movie_language" IS 'The language.';
COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."imdb_url" IS 'The IMDB ID.';
COMMIT;

Develop the data import task.

In this step, you create a Hologres SQL node to run scheduled jobs on the partitioned table.

Log on to the DataWorks console, go to the Data Development page, and create a Hologres SQL node. For more information, see Hologres SQL node.

In the node editor, enter the following statements.

You cannot directly write partitioned data into a parent partitioned table in Hologres. You must first create a child partitioned table in Hologres that corresponds to the partition key value of the source MaxCompute partitioned table. Then, you can import the partitioned data into the corresponding child partitioned table. The partition key value is controlled by the ${bizdate} parameter, which is automatically assigned a value by the scheduling system to enable periodic scheduling. For more information about scheduling parameters, see Supported formats of scheduling parameters.

Note

The imported partition data must match the partition key value (in this example, ds). Otherwise, an error occurs.

The following two scenarios demonstrate different approaches. Choose one based on your business logic.

Scenario 1: Import new partition data.

-- Create a temporary child partitioned table.
BEGIN;
CREATE TABLE IF NOT EXISTS "public".tmp_holo_dwd_product_movie_basic_info_${bizdate}  (
 "movie_name" TEXT,
 "director" TEXT,
 "scriptwriter" TEXT,
 "area" TEXT,
 "actors" TEXT,
 "type" TEXT,
 "movie_length" TEXT,
 "movie_date" TEXT,
 "movie_language" TEXT,
 "imdb_url" TEXT,
 "ds" TEXT
);
COMMIT;
-- Refresh the foreign table schema.
IMPORT FOREIGN SCHEMA public_data LIMIT TO (dwd_product_movie_basic_info) FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'update');
-- Wait for 30 seconds before importing data to Hologres to prevent synchronization failures caused by slow metadata cache updates in Hologres.
SELECT pg_sleep(30); 
-- Import data from MaxCompute to the temporary child partitioned table.
INSERT INTO "public".tmp_holo_dwd_product_movie_basic_info_${bizdate} 
SELECT 
    "movie_name",
    "director",
    "scriptwriter",
    "area",
    "actors",
    "type",
    "movie_length",
    "movie_date",
    "movie_language",
    "imdb_url",
    "ds"
FROM "public".dwd_product_movie_basic_info
WHERE ds='${bizdate}';
-- Import the new partition data.
BEGIN;
ALTER TABLE tmp_holo_dwd_product_movie_basic_info_${bizdate} RENAME TO holo_dwd_product_movie_basic_info_${bizdate};
-- Attach the new child partitioned table to the parent partitioned table.
ALTER TABLE holo_dwd_product_movie_basic_info ATTACH PARTITION holo_dwd_product_movie_basic_info_${bizdate} FOR VALUES IN ('${bizdate}');
COMMIT;

Scenario 2: Refresh historical partition data.

-- Create a temporary child partitioned table.
BEGIN;
CREATE TABLE IF NOT EXISTS "public".tmp_holo_dwd_product_movie_basic_info_${bizdate}  (
 "movie_name" TEXT,
 "director" TEXT,
 "scriptwriter" TEXT,
 "area" TEXT,
 "actors" TEXT,
 "type" TEXT,
 "movie_length" TEXT,
 "movie_date" TEXT,
 "movie_language" TEXT,
 "imdb_url" TEXT,
 "ds" TEXT
);
COMMIT;
-- Refresh the foreign table schema.
IMPORT FOREIGN SCHEMA public_data LIMIT TO (dwd_product_movie_basic_info) FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'update');
-- Wait for 30 seconds before importing data to Hologres to prevent synchronization failures caused by slow metadata cache updates in Hologres.
SELECT pg_sleep(30); 
-- Import data from MaxCompute to the temporary child partitioned table.
INSERT INTO "public".tmp_holo_dwd_product_movie_basic_info_${bizdate} 
SELECT 
    "movie_name",
    "director",
    "scriptwriter",
    "area",
    "actors",
    "type",
    "movie_length",
    "movie_date",
    "movie_language",
    "imdb_url",
    "ds"
FROM "public".dwd_product_movie_basic_info
WHERE ds='${bizdate}';
-- Refresh the historical partition data.
BEGIN;
ALTER TABLE IF EXISTS holo_dwd_product_movie_basic_info DETACH PARTITION holo_dwd_product_movie_basic_info_${bizdate};
DROP TABLE IF EXISTS holo_dwd_product_movie_basic_info_${bizdate};
ALTER TABLE tmp_holo_dwd_product_movie_basic_info_${bizdate} RENAME TO holo_dwd_product_movie_basic_info_${bizdate};
-- Attach the child partitioned table to the parent partitioned table.
ALTER TABLE holo_dwd_product_movie_basic_info ATTACH PARTITION holo_dwd_product_movie_basic_info_${bizdate} FOR VALUES IN ('${bizdate}');
COMMIT;

Configure scheduling properties.
On the Hologres SQL node editor page, click Configure Scheduling in the right-side pane to configure the node's scheduling properties.
Note
Change the following parameters and leave the others at their default values.
- Basic properties
  Parameter
  Value
  Parameters
  bizdate=${yyyymmdd}
- Time properties: In the Time Properties section, set Scheduling Type to Normal Scheduling, keep the default Validity Period (1970-01-01 to 9999-01-01), set Scheduling Cycle to Day, set Timeout to System Default, and do not select Depends on Previous Cycle.
  Parameter
  Value
  Instance generation mode
  Immediately After Deployment
  Rerun
  Allow upon Failure Only
  Scheduled time
  00:05
- Scheduling dependencies
  You can set the dependency to the root node or an existing parent node based on your business logic. First, set Automatic Parsing to On, and then click Parse Lineage. The system automatically parses the root node. Then, set Automatic Parsing to Off.
Publish the scheduling node.
1. On the Hologres SQL node editor page, click the icon in the toolbar to save the node.
2. Click the icon in the toolbar to submit the node.
3. In the Submit New Version dialog box, enter a Change description.
4. Click OK.
Run the node in Operation Center.
1. On the Hologres SQL node editor page, click O&M in the upper-right corner.
2. In the left-side navigation pane of the Operation Center page, choose Auto Triggered Nodes Maintenance > Recurring Job.
3. On the Recurring Job page, right-click the node and choose Backfill Data > Current Node.
4. In the left-side navigation pane, choose Assistant > Data Backfill to view the running task and its status.
View the data.
After the task runs successfully, Hologres automatically creates a child partitioned table for the partition's data.
1. Create a Hologres SQL node on the Data Development page. For more information, see Hologres SQL node.
2. On the node editor page, enter the following statements to query the data.
  - View the data in the child partitioned table.
```
SELECT * FROM holo_dwd_product_movie_basic_info_20170112;
```
  - View the total number of rows in the parent partitioned table.
```
SELECT COUNT (*) FROM holo_dwd_product_movie_basic_info;
```

Parameter	Value
Instance generation mode	Immediately After Deployment
Rerun	Allow upon Failure Only
Scheduled time	00:05

Parameter	Value
Parameters	bizdate=${yyyymmdd}