Hologres:Import MaxCompute data to Hologres regularly with DataWorks

Step 1: Prepare MaxCompute data

1. Log on to the DataWorks console. In the target region, choose Data Analytics & Services > Data Analysis in the left navigation pane. Click Go to Data Analytics. In the left navigation pane, click the icon to open the SQL Query page.

2. Enter the following SQL statement to verify data in the 20170112 partition, then click Run.

   SELECT * FROM public_data.dwd_product_movie_basic_info WHERE ds = '20170112';

   The query result is shown in the following figure. <br />

Step 2: Create an external table in Hologres

Create an external table to map the MaxCompute source table. The field order and field types must match the MaxCompute table.

1. Log on to the HoloWeb console SQL editor.

2. Click the icon below the SQL Editor tab to open an Ad-hoc Query window. In the toolbar, select your Instance Name and Database.

3. Enter the following statement and click Run. The IMPORT FOREIGN SCHEMA command creates an external table named dwd_product_movie_basic_info in Hologres that maps to the MaxCompute source table.

   IMPORT FOREIGN SCHEMA public_data LIMIT TO (dwd_product_movie_basic_info) FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'update');

Step 3: Create a managed table (internal table) in Hologres

Create a column-oriented partitioned internal table to store the imported data.

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

2. Select your Instance Name and Database, enter the following statement, and click Run.

   This is a simplified example. Adjust the Data Definition Language (DDL) to match your schema requirements, and configure appropriate indexes to optimize query performance.

   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 'Movie name';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."director" IS 'Director';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."scriptwriter" IS 'Screenwriter';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."area" IS 'Production region or country';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."actors" IS 'Lead actors';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."type" IS 'Type';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."movie_length" IS 'Movie length';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."movie_date" IS 'Release date';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."movie_language" IS 'Language';
   COMMENT ON COLUMN "public"."holo_dwd_product_movie_basic_info"."imdb_url" IS 'IMDb ID';
   COMMIT;

Step 4: Develop data for partitioned child tables

1. Log on to the DataWorks console and go to the DataStudio page. Create a Hologres SQL node. For more information, see Hologres SQL node.

2. On the node editing page, enter the SQL for your import scenario. Hologres does not support writing partitioned data directly to a partitioned parent table. Instead, create a temporary partitioned child table, load data into it, then attach it to the parent table. The partition key value is controlled by ${bizdate}, which the DataWorks scheduler replaces at runtime. The shared steps — creating the temporary table, refreshing the external table metadata, and loading data — are identical for both scenarios. Only the final attachment step differs. Shared SQL (run first in both scenarios)

   Important

   The imported data must match the partition key value. In this example, the partition key is ds. A mismatch causes an error.

   If the partition already exists, ATTACH PARTITION fails. Either use Scenario 2 to refresh it, or manually drop the existing child table before re-running.

   -- Create a temporary partitioned child 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 external table metadata
   IMPORT FOREIGN SCHEMA public_data LIMIT TO (dwd_product_movie_basic_info) FROM SERVER odps_server INTO public OPTIONS(if_table_exist 'update');
   
   -- Wait 30 seconds to let Hologres metadata cache sync before loading data
   SELECT pg_sleep(30);
   
   -- Load the target partition from MaxCompute into the temporary 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}';

   After the shared SQL, append the attachment block for your scenario: Scenario 1: Import new partitioned data Use this when the target partition does not yet exist in Hologres.

   BEGIN;
   
   ALTER TABLE tmp_holo_dwd_product_movie_basic_info_${bizdate} RENAME TO holo_dwd_product_movie_basic_info_${bizdate};
   
   -- Attach the child 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 partitioned data Use this when the target partition already exists and you need to reload it (for example, when upstream data is corrected). This scenario is idempotent: detaching, dropping, and re-attaching the child table ensures a clean reload even if the node is re-run after a failure.

   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 refreshed child 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;

Step 5: Configure scheduling

On the Hologres SQL node editing page, click Scheduling Configuration in the right-side pane.

Change only the following parameters. Keep all others at their default values.

Basic properties

${yyyymmdd} is a DataWorks built-in scheduling variable that resolves to the business date at runtime.

Time properties

Scheduling dependencies

Set the root node as the scheduling dependency, or choose an existing parent node based on your business logic. Set Code Parsing to Yes, click Code Parsing to auto-parse the root node, then set Code Parsing back to No.

Step 6: Publish the scheduling task

1. On the Hologres SQL node editing page, click the icon in the toolbar to save the node.

2. Click the icon to submit the node.

3. In the Submit New Version dialog box, enter a description in the Change Description field.

4. Click OK.

Step 7: Publish in Operation Center

1. On the Hologres SQL editing page, click O&M on the far right of the toolbar.

2. Go to the Operation Center page. In the left navigation pane, choose Scheduled Task O&M > Scheduled Tasks.

3. On the Scheduled Tasks page, right-click the node and choose Backfill Data > Current Node.

   

4. In the left navigation pane, choose O&M Assistant > Backfill Data to monitor the running tasks and their status.

Step 8: View data

After the task completes successfully, Hologres automatically creates a partitioned child table for the loaded partition.

1. Go to the DataStudio page and create a Hologres SQL node. For more information, see Hologres SQL node.

2. Enter the following queries to verify the data. Query the partitioned child table directly:

   SELECT * FROM holo_dwd_product_movie_basic_info_20170112;

   Query the total row count across the partitioned parent table:

   SELECT COUNT (*) FROM holo_dwd_product_movie_basic_info;