Hologres:Import data from MaxCompute using SQL

Step 1: Prepare source data

Use an existing MaxCompute table or create a new one. This example uses the customer table from the public_data public dataset in MaxCompute. For information on accessing public datasets, see Use public datasets.

The customer table DDL and a sample query:

-- DDL of the table in the MaxCompute public dataset
CREATE TABLE IF NOT EXISTS public_data.customer(
  c_customer_sk BIGINT,
  c_customer_id STRING,
  c_current_cdemo_sk BIGINT,
  c_current_hdemo_sk BIGINT,
  c_current_addr_sk BIGINT,
  c_first_shipto_date_sk BIGINT,
  c_first_sales_date_sk BIGINT,
  c_salutation STRING,
  c_first_name STRING,
  c_last_name STRING,
  c_preferred_cust_flag STRING,
  c_birth_day BIGINT,
  c_birth_month BIGINT,
  c_birth_year BIGINT,
  c_birth_country STRING,
  c_login STRING,
  c_email_address STRING,
  c_last_review_date STRING,
  useless STRING);

-- Query the table to verify data
SELECT * FROM public_data.customer;

The following figure shows a sample of the data.

Step 2: Create a foreign table in Hologres

Create a foreign table to map the MaxCompute source table:

CREATE FOREIGN TABLE foreign_customer (
    "c_customer_sk" int8,
    "c_customer_id" text,
    "c_current_cdemo_sk" int8,
    "c_current_hdemo_sk" int8,
    "c_current_addr_sk" int8,
    "c_first_shipto_date_sk" int8,
    "c_first_sales_date_sk" int8,
    "c_salutation" text,
    "c_first_name" text,
    "c_last_name" text,
    "c_preferred_cust_flag" text,
    "c_birth_day" int8,
    "c_birth_month" int8,
    "c_birth_year" int8,
    "c_birth_country" text,
    "c_login" text,
    "c_email_address" text,
    "c_last_review_date" text,
    "useless" text
)
SERVER odps_server
OPTIONS (project_name 'public_data', table_name 'customer');

Field data types in the foreign table must match the data types in the MaxCompute table. For the mapping reference, see Data type mapping between MaxCompute and Hologres.

Step 3: Create an internal table in Hologres

Create an internal table to receive the imported data. Define appropriate indexes to optimize query performance. For details on table properties, see CREATE TABLE.

-- Create a column-oriented internal table
BEGIN;
CREATE TABLE public.holo_customer (
 "c_customer_sk" int8,
 "c_customer_id" text,
 "c_current_cdemo_sk" int8,
 "c_current_hdemo_sk" int8,
 "c_current_addr_sk" int8,
 "c_first_shipto_date_sk" int8,
 "c_first_sales_date_sk" int8,
 "c_salutation" text,
 "c_first_name" text,
 "c_last_name" text,
 "c_preferred_cust_flag" text,
 "c_birth_day" int8,
 "c_birth_month" int8,
 "c_birth_year" int8,
 "c_birth_country" text,
 "c_login" text,
 "c_email_address" text,
 "c_last_review_date" text,
 "useless" text
);
CALL SET_TABLE_PROPERTY('public.holo_customer', 'orientation', 'column');
CALL SET_TABLE_PROPERTY('public.holo_customer', 'bitmap_columns', 'c_customer_id,c_salutation,c_first_name,c_last_name,c_preferred_cust_flag,c_birth_country,c_login,c_email_address,c_last_review_date,useless');
CALL SET_TABLE_PROPERTY('public.holo_customer', 'dictionary_encoding_columns', 'c_customer_id:auto,c_salutation:auto,c_first_name:auto,c_last_name:auto,c_preferred_cust_flag:auto,c_birth_country:auto,c_login:auto,c_email_address:auto,c_last_review_date:auto,useless:auto');
CALL SET_TABLE_PROPERTY('public.holo_customer', 'time_to_live_in_seconds', '3153600000');
CALL SET_TABLE_PROPERTY('public.holo_customer', 'storage_format', 'segment');
COMMIT;

Step 4: Run the INSERT statement

Use INSERT INTO ... SELECT to copy data from the foreign table into the internal table. You can import all fields or a subset.

Starting from Hologres V2.1.17, you can use Serverless Computing for large-scale offline imports, large extract, transform, load (ETL) jobs, and high-volume foreign table queries. Serverless Computing uses dedicated serverless resources instead of your instance resources, which improves stability and reduces OOM errors. You pay only for the tasks you run. For details, see Serverless Computing and the Serverless Computing user guide.

-- (Optional) Use Serverless Computing for large-scale import and ETL jobs.
SET hg_computing_resource = 'serverless';

-- Import a subset of fields
INSERT INTO holo_customer (c_customer_sk, c_customer_id, c_email_address, c_last_review_date, useless)
SELECT
    c_customer_sk,
    c_customer_id,
    c_email_address,
    c_last_review_date,
    useless
FROM foreign_customer;

-- Import all fields
INSERT INTO holo_customer
SELECT * FROM foreign_customer;

-- Reset the resource setting so subsequent SQL statements do not use serverless resources.
RESET hg_computing_resource;

Step 5: Query the imported data

After the import completes, query the internal table to verify the data:

SELECT * FROM holo_customer;

Use HoloWeb for one-click synchronization

HoloWeb provides a guided UI that generates and runs the import SQL for you.

1. Open the HoloWeb page. For access instructions, see Connect to HoloWeb and execute a query.

2. In the top menu bar, choose Metadata Management > MaxCompute Query Acceleration, then click Import MaxCompute Data.

3. Configure the parameters on the Create MaxCompute Data Import Task page.

   SQL Script shows the SQL statement automatically generated from your configuration. You cannot edit the statement directly in SQL Script. To customize it, copy the statement, modify it manually, and run it as SQL.

   Category	Parameter	Description
   Select instance	Instance name	The name of the logged-on Hologres instance.
   MaxCompute source table	Project name	The name of the MaxCompute project.
   	Schema name	The MaxCompute schema name. Hidden for two-layer model projects. For three-layer model projects, select from the authorized schemas.
   	Table name	The MaxCompute table to import. Supports prefix-based fuzzy search.
   Hologres target table	Database name	The Hologres database where the internal table will be created.
   	Schema name	The Hologres schema. Defaults to public.
   	Table name	The name of the new internal table. Defaults to the MaxCompute table name, which you can rename.
   	Target table description	An optional description for the new internal table.
   Parameter settings	GUC parameters	GUC (Grand Unified Configuration) parameters to apply. For details, see GUC parameters.
   Import settings	Fields	The fields to import. Select all or a subset.
   Partition configuration	Partition field	Select a partition field. Hologres supports only first-level partitions. For multi-level MaxCompute partitions, configure one partition field; the rest map to regular fields.
   	Data timestamp	For date-partitioned MaxCompute tables, select the partition date to import.
   Index configuration	Storage mode	Column-oriented storage (default): optimized for complex queries. Row-oriented storage: optimized for primary key point queries and scans. Row-column storage: supports all row store and column store scenarios, including non-primary key point queries.
   	Table data lifecycle	The data retention period. Defaults to Permanent. Data not modified within the specified period is automatically deleted after expiry.
   	Binlog	Whether to enable Binlog. For details, see Subscribe to Hologres Binlog.
   	Binlog lifecycle	The TTL (time to live) of Binlog. Defaults to 30 days (2,592,000 seconds).
   	Distribution columns	Hologres distributes data to shards based on these columns. Rows with the same values are placed in the same shard. Using distribution columns as filter conditions improves query efficiency.
   	Segment columns	Columns used as segment keys. Queries that include segment columns can quickly locate data storage positions.
   	Clustering columns	Columns used as clustering keys. Clustering indexes accelerate range and filter queries on these columns.
   	Dictionary encoding columns	Columns for which Hologres builds dictionary mappings. Dictionary encoding converts string comparisons to numeric comparisons, accelerating GROUP BY and filter queries. All text columns are set as dictionary encoding columns by default.
   	Bitmap columns	Columns for which Hologres builds bitmap indexes. Bitmap columns quickly filter data based on specified conditions. All text columns are set as bitmap columns by default.

4. Click Submit. After the import finishes, query the internal table to verify the data.

Use DataWorks for periodic scheduling

HoloWeb one-click synchronization does not support recurring schedules. For large historical imports or periodic synchronization, use DataStudio in DataWorks. For details, see Best practices for periodically importing MaxCompute data using DataWorks.

Step 1: Refresh table statistics

Stale or missing statistics cause the query optimizer to choose a suboptimal join order, leading to excessive memory usage. This is especially common when the import query contains subqueries.

Run the ANALYZE command on all internal and foreign tables involved in the import:

ANALYZE foreign_customer;
ANALYZE holo_customer;

This updates statistical metadata and helps the query optimizer generate a better execution plan.

Step 2: Reduce the batch read size

Wide tables with many columns result in large data volumes per read batch, which can exceed memory limits.

Set hg_experimental_query_batch_size to a smaller value before the INSERT statement (default is 8192):

SET hg_experimental_query_batch_size = 1024;
INSERT INTO holo_table SELECT * FROM mc_table;

Step 3: Reduce import concurrency (Hologres earlier than V1.1)

High import concurrency consumes more CPU and memory, which can affect other internal table queries.

Set hg_experimental_foreign_table_executor_max_dop to a smaller value (default is the number of instance cores). This parameter is valid for all jobs that are run on foreign tables.

SET hg_experimental_foreign_table_executor_max_dop = 8;
INSERT INTO holo_table SELECT * FROM mc_table;

Step 4: Reduce DML concurrency (Hologres V1.1 and later)

In Hologres V1.1 and later, use hg_foreign_table_executor_dml_max_dop to control concurrency for DML statements including imports (default is 32). Setting this to a smaller value reduces the concurrency for DML statements, especially in data import and export scenarios, and prevents DML statements from consuming excessive resources.

SET hg_foreign_table_executor_dml_max_dop = 8;
INSERT INTO holo_table SELECT * FROM mc_table;