ApsaraDB for SelectDB:Use OSS Load to import data

When to use OSS Load

OSS Load is the right choice when:

• You need to import large batches of files (tens to hundreds of gigabytes)

• Your files are stored in an OSS bucket in the same region as your SelectDB instance

• You need to load CSV, PARQUET, or ORC files

OSS Load uses the S3 protocol internally, so the syntax contains AWS and S3 keywords. This is expected — ApsaraDB for SelectDB supports any S3-compatible object storage, and OSS is accessed using the S3-compatible endpoint.

Prerequisites

Before you begin, ensure that you have:

• An AccessKey pair. See Create an AccessKey pair.

• An OSS bucket in the same region as your ApsaraDB for SelectDB instance. See Get started by using the OSS console.

• OSS accessible over a virtual private cloud (VPC) — OSS Load uses the internal VPC endpoint, not the public endpoint

Syntax

LOAD LABEL <load_label>
(
    data_desc1[, data_desc2, ...]
)
WITH S3
(
    "AWS_PROVIDER" = "OSS",
    "AWS_REGION" = "<region>",
    "AWS_ENDPOINT" = "<endpoint>",
    "AWS_ACCESS_KEY" = "<AccessKey ID>",
    "AWS_SECRET_KEY" = "<AccessKey secret>"
)
PROPERTIES
(
    "key1" = "value1", ...
);

Parameters

[MERGE|APPEND|DELETE]
DATA INFILE
(
    "file_path1"[, "file_path2", ...]
)
[NEGATIVE]
INTO TABLE `<table_name>`
[PARTITION (p1, p2, ...)]
[COLUMNS TERMINATED BY "<column_separator>"]
[FORMAT AS "<file_type>"]
[(column_list)]
[COLUMNS FROM PATH AS (c1, c2, ...)]
[PRECEDING FILTER predicate]
[SET (column_mapping)]
[WHERE predicate]
[DELETE ON expr]
[ORDER BY source_sequence]
[PROPERTIES ("key1"="value1", ...)]

Import data from OSS

This example creates a table, prepares a sample CSV file in OSS, and loads it using OSS Load.

Step 1: Create the target table.

CREATE TABLE test_table
(
    id int,
    name varchar(50),
    age int,
    address varchar(50),
    url varchar(500)
)
DISTRIBUTED BY HASH(id) BUCKETS 4
PROPERTIES("replication_num" = "1");

Step 2: Upload your data file to OSS.

Upload a CSV file named test_file.txt to your OSS bucket. The file should look like:

1,yang,32,shanghai,http://example.com
2,wang,22,beijing,http://example.com
3,xiao,23,shenzhen,http://example.com

Step 3: Submit the import job.

LOAD LABEL test_db.test_label_1
(
    DATA INFILE("s3://your_bucket_name/test_file.txt")
    INTO TABLE test_table
    COLUMNS TERMINATED BY ","
)
WITH S3
(
    "AWS_PROVIDER" = "OSS",
    "AWS_REGION" = "oss-cn-beijing",
    "AWS_ENDPOINT" = "oss-cn-beijing-internal.aliyuncs.com",
    "AWS_ACCESS_KEY" = "<your_access_key>",
    "AWS_SECRET_KEY" = "<your_secret_key>"
)
PROPERTIES
(
    "timeout" = "3600"
);

The LOAD statement has four sections:

• LABEL: A unique identifier for this import job, used to query its status and cancel it if needed.

• Data declaration: The source file path, format, and destination table.

• WITH S3: OSS connection credentials and endpoint.

• PROPERTIES: Job-level settings such as timeout.

Monitor and manage import jobs

SHOW LOAD
[FROM <db_name>]
[
   WHERE
   [LABEL [ = "your_label" | LIKE "label_matcher"]]
   [STATE = ["PENDING"|"ETL"|"LOADING"|"FINISHED"|"CANCELLED"]]
]
[ORDER BY ...]
[LIMIT limit][OFFSET offset];

SHOW LOAD FROM example_db WHERE LABEL LIKE "2014_01_02" LIMIT 10;

SHOW LOAD FROM example_db WHERE LABEL = "load_example_db_20140102" ORDER BY LoadStartTime DESC;

SHOW LOAD FROM example_db WHERE LABEL = "load_example_db_20140102" AND STATE = "LOADING";

SHOW LOAD FROM example_db ORDER BY LoadStartTime DESC LIMIT 5,10;
SHOW LOAD FROM example_db ORDER BY LoadStartTime DESC LIMIT 10 OFFSET 5;

CANCEL LOAD
[FROM <db_name>]
WHERE [LABEL = "<load_label>" | LABEL LIKE "<label_pattern>"];

CANCEL LOAD
FROM example_db
WHERE LABEL = "example_db_test_load_label";

CANCEL LOAD
FROM example_db
WHERE LABEL LIKE "example_";

Troubleshooting