Import and export

FAQ

• How do I stop an asynchronous import or export task?

• How do I use SQL to view running import tasks in the current database?

• Why is the import performance poor when I use a JDBC program to import data into an AnalyticDB for MySQL cluster?

• Are there separate charges for the Data Integration (APS) feature of a Lakehouse Edition cluster?

• When importing or exporting data with an OSS external table, should I use an internal endpoint or a public endpoint?

• How do I resolve an "endpoint unreachable" error when creating a MaxCompute external table?

• How do I resolve the "Odps external table endpoint should not contain special character" error when creating a MaxCompute external table?

• How do I resolve the "Project not found - 'xxx'" error when creating a MaxCompute external table?

• How do I resolve the "Roll back this write and submit it by writing one row at a time" error when importing data from MaxCompute?

• How do I resolve the "Query Exceeded Maximum Time Limit" error when importing data from MaxCompute?

• How do I resolve the "cant submit job for job queue is full" error when importing data from MaxCompute?

• How do I resolve the "Query execution error: odps partition num: 191 > 192, specific odps partitions" error when importing data from MaxCompute?

• How do I resolve the "ODPS Table XXX should be a partitioned table and has at least one partition in max_pt() function" error when querying data in MaxCompute?

• How do I resolve the "ErrorCode=NoSuchPartition, ErrorMessage=The specified partition does not exist" error when querying data in MaxCompute?

• How do I resolve the "Receive error response with code 500" error when querying data in MaxCompute?

• How do I resolve a "Query execution error" when querying data in MaxCompute?

• How do I import data with an array data type from MaxCompute into an AnalyticDB for MySQL cluster?

• How do I optimize data import speed from MaxCompute?

• Why is data not overwritten when I use an INSERT OVERWRITE statement to export data from an AnalyticDB for MySQL cluster to a MaxCompute external table?

• Why do I receive an "ErrorCode=SlotExceeded, ErrorMessage=Region: cn-hangzhou Project: XXX Slot Quota Exceeded" error when I use an INSERT INTO SELECT statement to write data to a MaxCompute external table?

• Why do I receive a "Catalog Service Failed. ErrorCode:202. ErrorMessage:ODPS-0110044: Flow control triggered - Request rejected by catalog server throttling, threshold 8.00, fallback or retry later" error when I use an INSERT INTO SELECT statement to write data to a MaxCompute external table?

• Why is the data in an AnalyticDB for MySQL cluster inconsistent with the source data in MaxCompute?

• Will DTS synchronization to an AnalyticDB for MySQL cluster fail if the source database contains unsupported data types?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, can I modify the data types of columns in the source table?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, how do I resolve an error caused by changing a data type in the source table?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, how do I resolve an error caused by writing an invalid date value?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, how do I resolve errors when synchronizing a table without a primary key?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, how do I resolve an error caused by an excessively long default value for a column?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, how do I resolve an error caused by writing a single record that exceeds 16 MB?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, how do I resolve an error caused by insufficient disk space?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, how do I resolve an error caused by a missing table or column?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, how do I handle cases where a value cannot be retrieved for a column during a multi-table merge?

• When synchronizing data to an AnalyticDB for MySQL cluster using DTS, can a source database, table, or column name contain a hyphen (-)?

• How do I troubleshoot data latency issues in an AnalyticDB for MySQL cluster during DTS synchronization?

• When importing data to an AnalyticDB for MySQL cluster using DataWorks, why is the write TPS lower than expected?

• When importing data to an AnalyticDB for MySQL cluster using DataWorks, why does data skew occur in the target table?

• When using the adb-import.sh script to import local data, how do I determine if the client or server load is the performance bottleneck?

• When using the adb-import.sh script to import local data, how do I parameterize the import script?

• When using the adb-import.sh script to import local data, how do I run the import job in the background?

• When using the adb-import.sh script to import local data, how do I ignore error rows during the import?

• When using the adb-import.sh script to import local data, how do I narrow down the cause of an import failure?

• When using the adb-import.sh script to import local data, how do I run the import program in a Windows environment?

Cancel an asynchronous import or export job

Log on to the AnalyticDB for MySQL console. On the Diagnostics and Optimization page, click the Import/Export Jobs tab. Find the target job and copy its Asynchronous Job Name. Then, run the CANCEL JOB "${asynchronous job name}" command to cancel the job. For more information, see submit asynchronous import jobs.

View running import jobs

Run the following query:

SELECT * FROM INFORMATION_SCHEMA.kepler_meta_async_jobs where status = "RUNNING"

Poor performance of JDBC data import

• Ensure the data source generates data at a sufficient rate. If data is read from other systems or files, check for output bottlenecks on the client.

• Ensure data processing is efficient and keeps pace with data generation. This helps maintain a sufficient queue of data ready for import into AnalyticDB for MySQL.

• Ensure the client machine has sufficient system resources. Check metrics such as CPU utilization and disk I/O utilization to identify potential bottlenecks.

Link charges for data ingestion (APS)

No. The data ingestion feature (APS) itself is free of charge. However, APS tasks run on a resource group in your cluster and consume billable resources.

Internal or public address for OSS external tables

Use an internal address when you create an OSS external table. The backend nodes in AnalyticDB for MySQL access OSS via the internal network, not the public network.

'Endpoint unreachable' error for MaxCompute external tables

Cause: The 'endpoint unreachable' error occurs when the endpoint specified for a MaxCompute external table is inaccessible.

Solution: Enable the ENI network, replace the endpoint in the CREATE TABLE statement with the VPC endpoint for the instance's region, and then run the statement again.

'Odps external table endpoint should not contain special character' error

Cause: The endpoint is configured incorrectly.

Solution: Replace the endpoint in the CREATE TABLE statement with the VPC endpoint for the instance's region, and rerun the statement.

"Project not found - 'xxx'" error

• Cause: The specified project does not exist in MaxCompute, or the project name is misspelled.

  Solution: Correct the project name in your table creation statement and try creating the external table again.

• Cause: The project exists in MaxCompute but is not in the same region as your AnalyticDB for MySQL cluster.

  Solution: Ensure that your AnalyticDB for MySQL cluster and the MaxCompute project are in the same region, and then try creating the external table again.

Error in MaxCompute data import: "Rollback this write and commit one row at a time"

Cause: This error occurs when importing data with DataX due to a limitation in the AnalyticDB for MySQL connection layer.

Solution: Add the rewriteBatchedStatements=false parameter to your JDBC connection string and retry the import.

"Query Exceeded Maximum Time Limit" error

Cause: This error occurs because importing data from a large MaxCompute table exceeds the timeout for an INSERT operation in AnalyticDB for MySQL.

Solution: Modify the INSERT_SELECT_TIMEOUT parameter and retry the import. For details, see config and hint parameters.

Error: "can't submit job: job queue is full"

Cause: The number of concurrent asynchronous tasks on your AnalyticDB for MySQL cluster has exceeded the limit. You can submit a ticket to check the concurrency limit for your cluster.

Solution:

• Wait for the submitted asynchronous tasks to complete, then resubmit the asynchronous import task. To check the status of an asynchronous task, see submit asynchronous import tasks.

• You can submit a ticket to request a higher concurrency limit for your cluster.

"Query execution error: odps partition num: 192 > 191, specific odps partitions" when importing data from MaxCompute

Cause: The number of partitions in a single import operation exceeds the limit for an AnalyticDB for MySQL cluster.

Solution: Based on the specific number of partitions in the MaxCompute table, add a Hint before the SQL statement /*RC_INSERT_ODPS_MAX_PARTITION_NUM=<value>*/ to adjust the limit on the number of partitions.

max_pt() function requires a partitioned table

Cause: This error occurs when you apply the MAX_PT() function to a non-partitioned MaxCompute external table.

Solution: Ensure that the MaxCompute table is a partitioned table and has at least one partition . Then, recreate the MaxCompute external table and query it using the MAX_PT() function.

ErrorCode=NoSuchPartition error when querying MaxCompute data

Cause: The MaxCompute external table has no partitions.

Solution: Ensure the MaxCompute table contains at least one partitionPartition operations. Then, recreate the external table and query the data.

Code 500 error when querying MaxCompute data

Cause: AnalyticDB for MySQL uses the native engine to execute SQL statements, but the native engine does not support MaxCompute external tables.

Solution: Add the hint /*native_engine_task_enabled=false*/ to the beginning of the SQL statement to disable the native engine and execute the statement using the Java engine.

Query execution error when querying MaxCompute data

• Cause 1: The AccessKey cannot read the MaxCompute table due to incorrect permission settings in MaxCompute.

  Solution: Update the read and write permissions in MaxCompute and rerun the query.

• Cause 2: The schema or column names of the AnalyticDB for MySQL table do not match those of the MaxCompute table.

  Solution: In AnalyticDB for MySQL, recreate the external table to match the schema and column names of the MaxCompute table. Then, rerun the query.

• Cause 3: The specified MaxCompute partition does not exist.

  Solution: Correct the MaxCompute partition specified in your query and rerun the query.

• Cause 4: The MaxCompute table contains too many small files.

  Solution: Merge the small files in MaxCompute and then rerun the query. Use the sample statement below. For more details, see Merge small files.

  ALTER TABLE tablename [PARTITION] MERGE SMALLFILES;

Import array<string> from MaxCompute to AnalyticDB for MySQL

Cause: You cannot directly import array<string> data to AnalyticDB for MySQL because MaxCompute external tables do not support nested types.

Solution: You can export the data from MaxCompute to OSS in Parquet format, and then use AnalyticDB for MySQL to read the Parquet data from OSS.

Optimize data import speed from MaxCompute

• If the load on storage nodes is low, adjust the value of SQL_OUTPUT_BATCH_SIZE and re-import the data using the following statement:

  set adb_config SQL_OUTPUT_BATCH_SIZE = 6000;

• If the MaxCompute table has too many partitions, set ENABLE_ODPS_MULTI_PARTITION_PART_MATCH to false and re-import the data using the following statement:

  set adb_config ENABLE_ODPS_MULTI_PARTITION_PART_MATCH=false;

If the issue persists, submit a ticket to Alibaba Cloud Technical Support.

Data overwrite failure for MaxCompute external tables

MaxCompute external tables do not support data overwrite.

"Slot Quota Exceeded" error for MaxCompute INSERT INTO SELECT

Cause: The slots required for the write operation exceed the slot quota.

Solution: Use one of the following methods.

• You can use an exclusive resource group for data integration. It provides a higher slot quota than a public resource group for data integration.

• The default concurrency for an INSERT INTO SELECT task is 16. Reduce the concurrency by adding the hint /*TASK_WRITER_COUNT=<value>*/ to the beginning of the SQL statement. The value must be an integer greater than 0.

Flow control error when writing to MaxCompute external tables

Cause: When you write data to a MaxCompute external table, if the operation creates new partitions, MaxCompute calls the createPartition operation. Creating partitions too frequently triggers the throttling mechanism in MaxCompute, which causes this error.

Solution: Check whether your INSERT INTO SELECT task writes data to multiple partitions concurrently. If so, add the hint /*TASK_WRITER_COUNT=<value>*/ to your SQL statement to reduce the concurrency. The value must be an integer greater than 0. If this does not resolve the issue, submit a ticket.

Record count mismatch

Cause: AnalyticDB for MySQL automatically removes records with duplicate primary keys.

Solution: Check for duplicate primary keys in your MaxCompute source data.

Handling unsupported data types

If a source database contains data types that are not supported by AnalyticDB for MySQL, such as geospatial data types, AnalyticDB for MySQL drops the corresponding columns during schema initialization.

For a list of supported data types, see Basic Data Types and Complex Data Types.

Change data types during synchronization

During data synchronization, you can modify the data types of columns in a source table. This feature only supports changes among integer data types or among floating-point data types. You can only change a data type to one with a wider range, such as from single precision to double precision.

• Integer data types: You can change from a smaller to a larger type among Tinyint, Smallint, Int, and Bigint. For example, changing Tinyint to Bigint is supported, but changing Bigint to Tinyint is not.

• Floating-point data types: You can change from Float to Double, but not from Double to Float.

DTS data synchronization to an AnalyticDB for MySQL cluster: Errors from data type changes

Errors:

Cause: This error occurs because DTS does not support this DDL operation when you change the data type of a source table during synchronization. For more information, see Does DTS support changing the data type of a source table during data synchronization to an AnalyticDB for MySQL cluster?

Solution:

• Synchronization of specific objects: Resynchronize the table. To do this, remove the table from the synchronization objects, delete it from the target database, and then add it back. DTS then performs a new full synchronization and schema migration, which bypasses the unsupported DDL operation.

• Full database synchronization: In AnalyticDB for MySQL, create a new table with a different name but the same table structure as the source table. Use INSERT INTO SELECT to copy the data from the original table into the new table. Then, delete the original table that has the error, rename the new table to the original name, and restart the DTS task.

DTS to AnalyticDB for MySQL: resolve invalid date value errors

Error message:

Cannot parse "2013-05-00 00:00:00": Value 0 for dayOfMonth must be in the range [1,31]]

Cause: AnalyticDB for MySQL does not support writing invalid date values.

Solution:

• If the synchronization task is in the full initialization phase, correct the value in the source table. For example, change '2013-05-00 00:00:00' to '2013-05-01 00:00:00'.

• If the synchronization task is in the incremental synchronization phase, remove the table from the synchronization object, correct the invalid value in the source table, add the table back to the synchronization object, and restart the synchronization task.

• If the synchronization task is in the incremental synchronization phase of a full database synchronization, contact Alibaba Cloud Technical Support to enable a switch to write invalid date values as null.

DTS to AnalyticDB for MySQL: Error synchronizing tables without a primary key

Error message:

DTS-077004: Record Replicator error. cause by [[17003,2020051513063717201600100703453067067] table not exist => xxx_table] 

Cause: AnalyticDB for MySQL does not support synchronizing tables without primary keys.

Solution: To resolve this error, manually create the table with a primary key in the target database, then restart the DTS task.

DTS synchronization: "Default value is too long" error

Error message:

default value is too long

Solution: Submit a ticket to request that Alibaba Cloud technical support upgrade your AnalyticDB for MySQL cluster to the latest version.

DTS to AnalyticDB for MySQL sync: Resolve record size error (>16 MB)

Error:

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (120468711 > 33554432). You can change this value on the server by setting the max_allowed_packet' variable.

Solution: Submit a ticket to ask Alibaba Cloud Technical Support to upgrade your AnalyticDB for MySQL cluster to the latest version.

DTS to AnalyticDB for MySQL: Insufficient disk space error

Error message:

disk is over flow

Solution: Delete unnecessary data to free up disk space, or contact Alibaba Cloud Technical Support to scale up the AnalyticDB for MySQL cluster. Once sufficient disk space is available, restart the DTS task.

Resolve DTS errors: Missing tables or columns

Error message:

table not exist => t1

Solution: In the DTS configuration, ensure the option to synchronize all DDL statements is selected.

Missing values in multi-table merge

Error:

No value present

Cause: In a multi-table merge, this error occurs if a schema change is made to a source table, such as adding a new field. This change creates a schema mismatch among the source tables, which prevents DTS from writing data to the target table.

Solution: Please submit a ticket to DTS technical support.

Hyphens in database, table, or column names

AnalyticDB for MySQL does not allow hyphens (-) in database, table, or column names. To ensure successful data synchronization, DTS automatically replaces hyphens (-) with underscores (_).

If data synchronization fails due to other issues with database, table, or column names, such as those containing spaces or Chinese characters, contact Alibaba Cloud technical support.

Troubleshoot data latency during DTS synchronization

• The DTS synchronization link uses the medium specification by default. If the source database is under a heavy write load, you may need to upgrade the instance configuration for maximum performance.

• For a table without a primary key, the column selected to act as one can cause hot row updates, which are very slow. To resolve this issue, submit a ticket to AnalyticDB for MySQL.

• When the write performance of an AnalyticDB for MySQL cluster reaches a bottleneck, you need to upgrade the AnalyticDB for MySQL specifications.

Low write TPS during DataWorks data import

Insufficient client load can lead to low CPU utilization, disk I/O utilization, and write response time on the cluster. Although the database server can quickly consume the incoming data, the low data volume results in a write TPS that is lower than expected. To improve performance, increase the batch size and the Maximum Concurrency for the import task. Data import performance typically scales linearly with client-side load.

Data skew in AnalyticDB for MySQL from DataWorks import

When data skew occurs in a target table, some cluster nodes become overloaded, which degrades import performance. In this situation, the cluster's CPU utilization and disk I/O are low, but the write response time is high. You can identify the skewed table on the Diagnostics and Optimization > Data Modeling Diagnostics page. To resolve this issue, redesign the table structure and then re-import the data. For more information, see Table structure design.

Identify client bottlenecks for adb-import.sh

A client-side bottleneck prevents you from maximizing the database load. Use the following methods to identify bottlenecks on the client or its host server:

• Log in to the AnalyticDB for MySQL console. On the left-side navigation pane, click Monitoring Information and Diagnostics and Optimization to review performance metrics for potential bottlenecks.

• Run the following commands on the client or its host server to identify bottlenecks.

Parameterize the adb-import.sh script

If your import files use a consistent row and column delimiter, you can modify the script to accept the tableName and dataPath as parameters. This lets you use a single script to import data into multiple tables by passing different table names and file paths as arguments.

tableName=$1
dataPath=$2

Run the import with parameters:

# sh adb-import.sh table_name001 /path/table_001
# sh adb-import.sh table_name002 /path/table_002
# sh adb-import.sh table_name003 /path/table_003

Run adb-import.sh in the background

To run the import program in the background, use the following command:

# nohup sh adb-import.sh &

After the program starts, check the log by running the following command. If the log contains an exception stack, it indicates an error that you need to troubleshoot.

# tail -f nohup.out

To verify that the import process is still running, use the following command:

# ps -ef|grep import

Ignore error lines with adb-import.sh

Error lines from the import tool fall into the following two categories:

• SQL execution errors.

  For this type of error, you can ignore the error line by setting the ignoreErrors=true parameter. In this case, the execution result will print the detailed error file, the starting line number (because batchSize is set, the error line is within batchSize lines after the starting line number), and the SQL that failed to execute.

• Column count mismatch.

  If a file contains an unexpected number of columns, the import tool immediately stops processing it and prints an error message. Because this error indicates a malformed file, it cannot be ignored. You must manually inspect and correct the file. The tool prints an error message similar to the following:

  [ERROR] 2021-03-22 00:46:40,444 [producer- /test2/data/lineitem.csv.split00.100-41] analyticdb.tool.ImportTool
  (ImportTool.java:591) -bad line found and stop import! 16, file = /test2/data/tpch100g/lineitem.csv.split00.100, rowCount = 7, current row = 3|123|179698|145|73200.15|0.06|0.00|R|F|1994-02-02|1994-01-04|1994-02-
  23|NONE|AIR|ongside of the furiously brave acco|

Troubleshooting adb-import.sh import failures

To quickly identify the cause of an import failure, use the following methods to narrow the troubleshooting scope:

• When an import fails, the AnalyticDB for MySQL import tool prints an error log detailing the cause of the failure. By default, the SQL statement in the log is truncated to 1,000 characters. To view the complete statement, increase the failureSqlPrintLengthLimit parameter. For example, you can set it to 1,500:

  printErrorSql=true
  failureSqlPrintLengthLimit=1500;

• The import tool processes SQL statements in batches, and a single batch, controlled by the batchSize parameter, can contain thousands of rows. This makes it difficult to pinpoint the row causing the error. To isolate the problematic row, reduce the batchSize parameter. For example, you can set it to 10:

  batchSize=10;

• If you have identified which file split contains an error, you can reproduce the error by importing only that file. To do this, modify the dataPath parameter to point directly to the problematic file:

  dataPath=/u01/this/is/the/directory/where/product_info/stores/file007;

Run the import tool on Windows

A batch script is not yet available for Windows. To run the import tool, call the JAR file directly:

usage: java -jar adb-import-tool.jar [-a <arg>] [-b <arg>] [-B <arg>] [-c <arg>] 
      [-D <arg>] [-d <arg>] [-E <arg>] [-f <arg>] [-h <arg>] [-I <arg>] 
      [-k <arg>] [-l <arg>] [-m <arg>] [-n <arg>] [-N <arg>] [-O <arg>]
      [-o <arg>] [-p <arg>] [-P <arg>] [-Q <arg>] [-s <arg>]  [-S <arg>]
      [-t <arg>] [-T <arg>] [-u <arg>] [-w <arg>][-x <arg>] [-y <arg>] [-z <arg>]

Examples:

• Example 1: Import a single file using default parameters:

  java -Xmx8G -Xms8G -jar adb-import-tool.jar -hyourhost.ads.aliyuncs.com -uadbuser -ppassword -P3306 -Dtest --dataFile /data/lineitem.sample --tableName LINEITEM

• Example 2: Import all files in a folder, optimized for maximum throughput:

  java -Xmx16G -Xms16G -jar adb-import-tool.jar -hyourhost.ads.aliyuncs.com -uadbuser -ppassword -P3306 -Dtest --dataFile /data/tpch100g --tableName LINEITEM --concurrency 64 --batchSize 2048