You can use the OSSOUTFILE feature provided by PolarDB to export database tables as CSV files and store them in the OSS engine.
Prerequisites
Your PolarDB cluster must meet one of the following conditions:
-
The kernel version is MySQL 8.0.1, and the revision version is 8.0.1.1.30 or later.
-
Kernel version is MySQL 8.0.2, and the patch version is 8.0.2.2.8 or later.
To check your cluster version, see Query version number.
Notes
-
Exporting data files to the OSS engine through DMS connected to PolarDB is not supported. The corresponding error message is:
Can not issue executeUpdate() or executeLargeUpdate() for SELECTsConnect to PolarDB using a Client or the Command Line, then perform the export. For details, see Connect to a database cluster.
-
To back up data incrementally to OSS, create a backup schedule and configure the backup schedule to enable incremental backups to OSS.
-
Run export tasks on a read-only node. Add the HINT syntax
/*FORCE_SLAVE*/to your SQL statement or connect directly to a read-only node to perform the export.
Parameter description
|
Parameter |
Description |
|
loose_oss_outfile_buffer_size |
Memory size each OSSOUTFILE thread can use. Valid values: 102400 to 536870912. Default value: 134217728. Unit: Byte. Generally, more memory results in faster export speed. |
|
loose_max_oss_outfile_threads |
Number of OSSOUTFILE threads PolarDB can start simultaneously. Valid values: 1 to 100. Default value: 1. Unit: count. Use the command Note
This parameter is no longer effective for clusters running PolarDB MySQL Edition version 8.0.1.1.38 or later. |
The maximum total memory used by the cluster during CSV file export to the OSS engine using the OSSOUTFILE feature is:
loose_max_oss_outfile_threads * loose_oss_outfile_buffer_sizeAvoid letting the OSSOUTFILE feature use more than 5% of a node's total memory. Otherwise, other workloads on the node may be affected.
Usage
Syntax
SELECT * FROM table_name INTO OSSOUTFILE 'outfile_path' COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' NULL_MARKER BY 'NULL' LINES TERMINATED BY '\n';-
COLUMNS TERMINATED BY: Specifies the separator between fields. -
OPTIONALLY ENCLOSED BY: Specifies the character that encloses field values. With OPTIONALLY, only string-type fields are enclosed. Without it, all field types are enclosed. -
NULL_MARKER BY: Specifies how NULL values appear in the output file. NULL output values follow these rules, listed from highest to lowest priority:-
NULL_MARKER: Directly defines the NULL output value. You can set
NULL_MARKERto any value. This has the highest priority. -
ESCAPED BY: Accepts only a single character. The NULL output becomes that character followed by N. For example, if
ESCAPED BY '\', NULL appears as \N. This has second priority. -
NULL (default): If neither of the above is specified, NULL appears as the string “NULL”. This has the lowest priority. The “NULL” string is not enclosed by
ENCLOSEDcharacters.
-
-
LINES TERMINATED BY: Specifies the line separator.
For details, see the MySQL official documentation.
Parameter Description
|
Parameter |
Description |
|
outfile_path |
Specifies the file location in OSS and includes the following parts:
Join these three parts with |
|
table_name |
Table name. |
Procedure
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"}'); -
This example uses the OSS Server creation statement for newer versions. Adjust the statement based on your cluster’s Milvus version.
-
The
oss_endpointmust be an internal network address in the formatoss-{xxx}-internal.aliyuncs.com.
Export to OSS
Take the uploaded OSS file oss://polardb/B_outfile/tpch/1t/parallel-lineitem.CSV as an example:
-
Bucket name: polardb.
-
Path and filename:
B_outfile/tpch/1t/parallel-lineitem.CSV.
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';Check memory and thread usage
If your cluster’s Milvus version is 8.0.1.1.38 or later, use the following commands to view real-time total memory and thread usage for the current export task.
-
View real-time total memory usage in bytes.
SHOW STATUS LIKE "%Oss_outfile_memory_used%"; -
View real-time total thread count.
SHOW STATUS LIKE "%Oss_outfile_threads_running%";
If you run the export task using the Cluster Endpoint, you must run both the SHOW STATUS command and the export command on the same node to view accurate memory resource usage for that node.