Export tables to OSS as a CSV file using OSSOUTFILE - PolarDB

Use the OSSOUTFILE feature to export a PolarDB for MySQL database table to a CSV file in an Object Storage Service (OSS) bucket.

Prerequisites

Before you begin, make sure that:

Your PolarDB cluster runs one of the following engine versions:
- PolarDB for MySQL 8.0.1, revision version 8.0.1.1.30 or later
- PolarDB for MySQL 8.0.2, revision version 8.0.2.2.8 or later
You connect to the cluster using a database client or the MySQL command-line client — not Data Management (DMS). Connecting through DMS returns the error Can not issue executeUpdate() or executeLargeUpdate() for SELECTs.

For instructions on connecting to a cluster, see Connect to a cluster.

Usage notes

Run export tasks on a read-only node. Use the /*FORCE_SALVE*/ hint in your SQL statement, or connect directly to a read-only node.
The total memory used by OSSOUTFILE must not exceed 5% of the node memory. Calculate it with the formula: loose_max_oss_outfile_threads × loose_oss_outfile_buffer_size.
If you need incremental backups to OSS, set up a backup schedule instead. See Create a backup schedule and Configure a backup schedule.

Syntax

SELECT * FROM table_name
  INTO OSSOUTFILE 'oss-server/[task-path/]filename.csv'
  COLUMNS TERMINATED BY ','
  [OPTIONALLY] ENCLOSED BY '"'
  NULL_MARKER BY 'NULL'
  LINES TERMINATED BY '\n';

Clause	Description
`INTO OSSOUTFILE 'outfile_path'`	The destination path in OSS. See Construct the outfile path.
`COLUMNS TERMINATED BY`	Delimiter between fields.
`[OPTIONALLY] ENCLOSED BY`	Quote character wrapping field values. With `OPTIONALLY`, only string-type fields are quoted. Without it, all field types are quoted.
`NULL_MARKER BY`	How NULL values appear in the exported file. Priority order: (1) `NULL_MARKER` — any string you specify; (2) `ESCAPED BY` — a single escape character prefixed to `N` (e.g., `ESCAPED BY '\'` outputs `\N`); (3) default — the string `NULL` with no enclosing quotes.
`LINES TERMINATED BY`	Delimiter between rows.

For the full MySQL SELECT ... INTO syntax reference, see MySQL documentation.

Construct the outfile path

The outfile_path follows this pattern:

oss-server-name/[optional-task-path/]output-filename.csv

Part	Required	Description
`oss-server-name`	Yes	The name of the OSS server object you created with `CREATE SERVER`. It encodes the bucket name and base prefix.
`optional-task-path/`	No	One or more path segments that create subdirectories inside the bucket prefix. Use multiple `/`-separated segments for a multi-level path.
`output-filename.csv`	Yes	The name of the file to create in OSS. Must be included in the path.

The full path stored in OSS is: OSS server base path + optional task path + filename.

System parameters

Parameter	Valid values	Default	Description
`loose_oss_outfile_buffer_size`	102400–536870912	134217728	Maximum memory per OSSOUTFILE thread, in bytes. A larger value speeds up the export.
`loose_max_oss_outfile_threads`	1–100	1	Maximum number of concurrent OSSOUTFILE threads. Run `SHOW STATUS LIKE "Oss_outfile_threads_running";` to check how many threads are currently active. Note This parameter has no effect on PolarDB for MySQL 8.0.1.1.38 or later.

Export a table to OSS

Step 1: Create an OSS server

CREATE SERVER outserver
  FOREIGN DATA WRAPPER oss
  OPTIONS (
    EXTRA_SERVER_INFO '{
      "oss_endpoint":           "oss-cn-zhangjiakou-internal.aliyuncs.com",
      "oss_bucket":             "polardb",
      "oss_access_key_id":      "*******",
      "oss_access_key_secret":  "********",
      "oss_prefix":             "B_outfile"
    }'
  );

Note

Set oss_endpoint to an internal endpoint in the format oss-{region}-internal.aliyuncs.com. The example above targets the polardb bucket with the base prefix B_outfile. Adjust the statement based on your cluster's engine version — see Use OSS foreign tables to access OSS data for version-specific syntax.

Step 2: Export data to OSS

The following statement exports the lineitem table to oss://polardb/B_outfile/tpch/1t/parallel-lineitem.CSV:

polardb is the bucket name (from the OSS server definition).
B_outfile/tpch/1t/parallel-lineitem.CSV is the file path and name within the bucket.

SELECT * FROM lineitem
  INTO OSSOUTFILE 'outserver/tpch/1t/parallel-lineitem.CSV'
  COLUMNS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  NULL_MARKER BY 'NULL'
  LINES TERMINATED BY '\n';

To overwrite an existing file at the same path, add the OSS_REPLACE_EXISTED_FILE() hint:

SELECT /*+ OSS_REPLACE_EXISTED_FILE() */ * FROM lineitem
  INTO OSSOUTFILE 'outserver/tpch/1t/parallel-lineitem.CSV'
  COLUMNS TERMINATED BY '|@|'
  OPTIONALLY ENCLOSED BY '"'
  NULL_MARKER BY ""
  LINES TERMINATED BY '\n';

Warning

If OSS version control is disabled, the overwritten file cannot be restored. Enable version control before using this hint if you need recovery capability.

Note

The OSS_REPLACE_EXISTED_FILE() hint requires PolarDB for MySQL 8.0.1 revision 8.0.1.1.31 or later, or PolarDB for MySQL 8.0.2 revision 8.0.2.2.9 or later.

Step 3: Monitor resource usage

For clusters running PolarDB for MySQL 8.0.1.1.38 or later, check real-time resource usage while the export runs:

-- Total memory used by the export task (in bytes)
SHOW STATUS LIKE "%Oss_outfile_memory_used%";

-- Number of active export threads
SHOW STATUS LIKE "%Oss_outfile_threads_running%";

Note

If you use the cluster endpoint, the SHOW STATUS and export statements must run on the same node to return accurate results.

Considerations

Keep the following in mind when planning exports:

Memory limits: Total OSSOUTFILE memory (loose_max_oss_outfile_threads × loose_oss_outfile_buffer_size) must not exceed 5% of node memory. Exceeding this limit affects other services running on the same node.

What's next

Troubleshooting

ERROR 1086 (HY000): File 'xxxx' already exists

A file with the same name already exists in the OSS bucket. Either delete the existing file from OSS and re-run SELECT ... INTO OSSOUTFILE ..., or use the OSS_REPLACE_EXISTED_FILE() hint to overwrite it directly (see Step 2).

OSS error: The bucket you access does not belong to you (AccessDenied)

The AccessKey pair used in the OSS server definition does not have permission on the specified bucket. Check the permissions of your AccessKey pair. For setup instructions, see Create an AccessKey pair.