EXPORT

Supported source objects

• SelectDB internal tables (any of DUPLICATE, UNIQUE, AGGREGATE data models).

• SelectDB logical views.

• External tables registered in an External Catalog (for example, Hive Metastore Catalog).

Supported destinations

• Object storage: Alibaba Cloud OSS, Amazon S3, Tencent Cloud COS, Huawei Cloud OBS, Google GCS. All declared with WITH S3 and s3.* properties.

• HDFS, including HA-enabled and Kerberos-authenticated clusters. Declared with WITH HDFS.

Supported file formats

• Parquet

• ORC

• csv (default)

• csv_with_names (header row carries column names)

• csv_with_names_and_types (header row carries names; second row carries column types)

Create a table and load data

CREATE TABLE IF NOT EXISTS demo.tbl (
    `c1` INT NULL,
    `c2` STRING NULL,
    `c3` BIGINT NULL
)
DISTRIBUTED BY HASH(c1) BUCKETS 4;

INSERT INTO demo.tbl VALUES
    (1, 'doris', 18),
    (2, 'nereids', 20),
    (3, 'pipeline', 99999),
    (4, 'Apache', 122123455),
    (5, NULL, NULL);

Export to Alibaba Cloud OSS

Write the entire demo.tbl to an OSS bucket in default csv format:

EXPORT TABLE demo.tbl TO "oss://<your_bucket>/export/tbl_"
PROPERTIES (
    "line_delimiter" = ","
)
WITH S3 (
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.region" = "oss-cn-hangzhou",
    "s3.access_key" = "<your_access_key_id>",
    "s3.secret_key" = "<your_access_key_secret>"
);

• Path scheme: prefer oss:// for Alibaba Cloud OSS; s3:// is also accepted.

• Credentials clause: regardless of the path scheme, use WITH S3 with the property keys s3.endpoint, s3.region, s3.access_key, and s3.secret_key. SelectDB v4 does not yet expose a WITH OSS keyword.

• Endpoint: when the SelectDB instance and OSS bucket are in the same region, use the internal endpoint (for example, oss-cn-hangzhou-internal.aliyuncs.com) to skip public-network charges and improve throughput.

Export to HDFS

Write demo.tbl to HDFS with a custom column delimiter. The host:port in fs.defaultFS and the target path must point to a reachable NameNode (for example, the FQDN of an EMR cluster master node in the same VPC):

EXPORT TABLE demo.tbl TO "hdfs://<namenode_host>:9000/path/to/tbl_"
PROPERTIES (
    "format" = "csv",
    "column_separator" = ","
)
WITH HDFS (
    "fs.defaultFS" = "hdfs://<namenode_host>:9000",
    "hadoop.username" = "hadoop"
);

• The SelectDB instance and the HDFS cluster must share the same VPC. With Alibaba Cloud EMR, get the NameNode FQDN from the EMR cluster's Access Links and Ports page.

• hadoop.username must have write permission on the target HDFS path; common values are hadoop or hdfs.

• After the export finishes, validate the result by reading it back with SELECT * FROM HDFS("uri"="hdfs://<namenode_host>:9000/path/to/tbl_*", ...).

Inspect export jobs

EXPORT returns immediately, so subsequent state must be tracked with SHOW EXPORT:

SHOW EXPORT FROM demo ORDER BY CreateTime DESC LIMIT 10;

Important columns:

• State: jobs progress through PENDING (queued) → EXPORTING (in flight) → FINISHED (done) or CANCELLED (manually cancelled or failed).

• Progress: percentage progress.

• Path: the target prefix you submitted.

• OutfileInfo: populated after FINISHED, listing each output file's row count, byte size, and final URL.

• ErrorMsg: error detail when the job fails; useful for debugging permissions, paths, and network issues.

Cancel a job

Jobs in PENDING or EXPORTING state can be revoked. The common pattern is to match by Label:

CANCEL EXPORT FROM demo WHERE LABEL like "%export_%";

Export to an HA-enabled HDFS cluster

In HA mode, declare the nameservices and each NameNode's RPC address inside WITH HDFS. The value of dfs.nameservices must match the logical name in fs.defaultFS; otherwise the parser raises Missing property: dfs.namenode.rpc-address.* (expected format: host:port):

EXPORT TABLE demo.tbl TO "hdfs://my-ns/path/to/tbl_"
PROPERTIES (
    "line_delimiter" = ","
)
WITH HDFS (
    "fs.defaultFS" = "hdfs://my-ns",
    "hadoop.username" = "hadoop",
    "dfs.nameservices" = "my-ns",
    "dfs.ha.namenodes.my-ns" = "nn1,nn2",
    "dfs.namenode.rpc-address.my-ns.nn1" = "nn1-host:8020",
    "dfs.namenode.rpc-address.my-ns.nn2" = "nn2-host:8020",
    "dfs.client.failover.proxy.provider.my-ns" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"
);

Export to an HA + Kerberos HDFS cluster

EXPORT TABLE demo.tbl TO "hdfs://hacluster/path/to/tbl_"
PROPERTIES (
    "line_delimiter" = ","
)
WITH HDFS (
    "fs.defaultFS" = "hdfs://hacluster/",
    "hadoop.username" = "hadoop",
    "dfs.nameservices" = "hacluster",
    "dfs.ha.namenodes.hacluster" = "n1,n2",
    "dfs.namenode.rpc-address.hacluster.n1" = "192.168.0.1:8020",
    "dfs.namenode.rpc-address.hacluster.n2" = "192.168.0.2:8020",
    "dfs.client.failover.proxy.provider.hacluster" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider",
    "dfs.namenode.kerberos.principal" = "hadoop/_HOST@REALM.COM",
    "hadoop.security.authentication" = "kerberos",
    "hadoop.kerberos.principal" = "doris_test@REALM.COM",
    "hadoop.kerberos.keytab" = "/path/to/doris_test.keytab"
);

Export selected partitions

Export only partitions p1 and p2 of the test table, and use columns to limit the output columns:

EXPORT TABLE demo.test
PARTITION (p1, p2)
TO "oss://<your_bucket>/export/test_"
PROPERTIES (
    "columns" = "k1,k2"
)
WITH S3 (
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.region" = "oss-cn-hangzhou",
    "s3.access_key" = "<your_access_key_id>",
    "s3.secret_key" = "<your_access_key_secret>"
);

Filter data during export

Export only rows that satisfy k1 < 50 with a custom column delimiter:

EXPORT TABLE demo.test
WHERE k1 < 50
TO "oss://<your_bucket>/export/test_"
PROPERTIES (
    "columns" = "k1,k2",
    "column_separator" = ","
)
WITH S3 (
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.region" = "oss-cn-hangzhou",
    "s3.access_key" = "<your_access_key_id>",
    "s3.secret_key" = "<your_access_key_secret>"
);

Export an external table

Register a Hive Catalog first, then submit the export with the three-part name catalog.db.table:

-- 1. Create a Hive Catalog
CREATE CATALOG hive_catalog PROPERTIES (
    'type' = 'hms',
    'hive.metastore.uris' = 'thrift://hms_host:9083'
);

-- 2. Export a Hive table from the Catalog
EXPORT TABLE hive_catalog.sf1.lineitem TO "oss://<your_bucket>/export/lineitem_"
PROPERTIES (
    "format" = "csv",
    "max_file_size" = "1024MB"
)
WITH S3 (
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.region" = "oss-cn-hangzhou",
    "s3.access_key" = "<your_access_key_id>",
    "s3.secret_key" = "<your_access_key_secret>"
);

Set the export file size

Use max_file_size to cap each output file. Files exceeding the cap are split automatically:

EXPORT TABLE demo.test TO "oss://<your_bucket>/export/test_"
PROPERTIES (
    "format" = "parquet",
    "max_file_size" = "512MB"
)
WITH S3 (
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.region" = "oss-cn-hangzhou",
    "s3.access_key" = "<your_access_key_id>",
    "s3.secret_key" = "<your_access_key_secret>"
);

max_file_size accepts values from 5 MB to 2 GB. Downstream processors such as Spark or Flink usually struggle with very large files, so 256 MB to 1 GB is a safer choice.

Clear the export directory before writing

Setting delete_existing_files to true tells the engine to delete every file and subdirectory under the target prefix before writing new data:

EXPORT TABLE demo.test TO "oss://<your_bucket>/export/test_"
PROPERTIES (
    "format" = "parquet",
    "max_file_size" = "512MB",
    "delete_existing_files" = "true"
)
WITH S3 (
    "s3.endpoint" = "oss-cn-hangzhou-internal.aliyuncs.com",
    "s3.region" = "oss-cn-hangzhou",
    "s3.access_key" = "<your_access_key_id>",
    "s3.secret_key" = "<your_access_key_secret>"
);