AnalyticDB:Data import methods

Regular import vs. elastic import

By default, AnalyticDB for MySQL uses regular import, which reads source data on interactive resource groups (resident compute nodes) and writes indexes on storage nodes. This can drive high CPU utilization and I/O usage on storage nodes during the job.

Elastic import (available on V3.1.10.0 and later) offloads both reading and index building to job resource groups (dynamically scaled compute nodes), leaving storage nodes nearly unaffected. Use elastic import when you need higher concurrency or want to protect interactive workloads during data loading.

Elastic import limits:

• Supported only on AnalyticDB for MySQL Data Lakehouse Edition (V3.0) based on external tables using INSERT OVERWRITE INTO.

• Imports data from a single table only — joined tables are not supported.

• Supports only WHERE and LIMIT clauses; ORDER BY is not supported. In the SELECT statement, you can use asterisks (*), column names, default values, or constants for the column and value fields — SQL functions are not supported.

• Targets fact tables only.

• Supported data types: BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, VARCHAR, DATE, TIME, DATETIME, TIMESTAMP.

Elastic import requires at least 2–3 minutes to complete. For jobs that must finish within 3 minutes, use regular import instead.

Tune elastic import concurrency

To increase concurrency beyond the default, set the adb.load.job.max.acu parameter. Its default value is (number of shards) + 1. Set it to K × default value where K ≥ 1. Keep K at or below the number of partitions in the target partitioned table.

Query the number of shards in your cluster:

SELECT count(1) FROM information_schema.kepler_meta_shards;

For partition counts, see Storage analysis.

Use cases

• Data warehouse initialization: Load terabytes of historical data from OSS or HDFS into AnalyticDB for MySQL for the first time. Schedule during off-peak hours to avoid resource contention.

• Daily batch ingestion: Import gigabytes to terabytes of processed data from MaxCompute into AnalyticDB for MySQL each day for accelerated analysis.

Performance optimization

For optimization strategies specific to external table imports, see Optimize data import performance — external tables.

Use cases

• Frequent small-batch imports: Load small amounts of data every minute or every hour for near-real-time analysis.

• Multi-source consolidation: Pull data from sources like Tablestore, Redis, and PostgreSQL into a single AnalyticDB for MySQL cluster.

Get started

1. Configure the data source. Supported sources:

   • ApsaraDB RDS for MySQL

   • Oracle

   • ApsaraDB RDS for SQL Server

   • OSS

   • MaxCompute

   • HDFS

2. Add an AnalyticDB for MySQL V3.0 data source.

3. Configure source and destination connections for the synchronization job.

Performance optimization

For optimization strategies specific to DataWorks imports, see Optimize data import performance — DataWorks.

Use cases

• Real-time synchronization: Stream changes from ApsaraDB RDS for MySQL or PolarDB for MySQL to AnalyticDB for MySQL within seconds.

• Multi-source aggregation: Consolidate data from multiple RDS for MySQL instances or PolarDB for MySQL clusters into a single AnalyticDB for MySQL cluster. Use DTS's multi-table merging feature to unify multiple source tables with the same schema into one destination table.

Get started

• Use DTS to import ApsaraDB RDS for MySQL data

• Use DTS to import ApsaraDB RDS for SQL Server data

• Use DTS to import PolarDB-X data

• Use DTS to import PolarDB for MySQL data

• Use DTS to import data from a self-managed HBase database

Use cases

• Import after data preprocessing: Parse log files or apply custom transformations before loading data into AnalyticDB for MySQL in real time.

• On-premises data import: Load on-premises data that cannot be staged to OSS, HDFS, or MaxCompute beforehand.

Usage notes

• Configure an appropriate JDBC driver before connecting. See Supported MySQL JDBC driver versions.

• For large imports, configure a connection pool to reduce overhead. See Druid connection pool.

• Use batch inserts and concurrent connections to maximize throughput.

• For streaming data (for example, Apache Flink pipelines), see Import data from Apache Flink.

• For non-customized on-premises data, see Use LOAD DATA to import data to Data Warehouse Edition and Use the AnalyticDB for MySQL import tool to import data to Data Warehouse Edition.

Performance optimization

For optimization strategies specific to JDBC-based imports, see Optimize data import performance — JDBC.

Use cases

• Low-cost log and message ingestion: Use APS to stream data continuously from Simple Log Service (SLS) and ApsaraMQ for Kafka into AnalyticDB for MySQL for cost-effective storage and analysis.

• OSS metadata discovery: When OSS data lacks structured metadata, the metadata discovery feature scans OSS and builds database, table, and partition metadata — including data formats and field definitions — making the data accessible to AnalyticDB for MySQL and other analysis engines.

• Hive data migration: Migrate Hive data to OSS using the Hive data migration feature. AnalyticDB for MySQL then automatically organizes the metadata for subsequent analysis.

Get started

• Automatically synchronize the metadata of a PolarDB-X instance to a Data Lakehouse Edition cluster

• Use data synchronization to synchronize data from Simple Log Service to Data Warehouse Edition

• Use data synchronization to synchronize data from Simple Log Service to Data Lakehouse Edition

• Use data synchronization to synchronize data from ApsaraMQ for Kafka to Data Lakehouse Edition (recommended)

• Use data synchronization to synchronize data from Simple Log Service to Data Lakehouse Edition (recommended)

• Use metadata discovery to import data to Data Lakehouse Edition

• Import data from a Hive data source