This topic describes how to use the OSSOUTFILE feature of PolarDB to export database tables into a CSV file in an Object Storage Service (OSS) bucket.
Prerequisites
Your PolarDB cluster runs one of the following database engine versions:
PolarDB for MySQL 8.0.1 with a revision version of 8.0.1.1.30 or later.
PolarDB for MySQL 8.0.2 with a revision version of 8.0.2.2.8 or later.
For information about how to query the engine version of a PolarDB cluster, see Query the engine version.
Precautions
You cannot access a PolarDB cluster by using Data Management (DMS) to export data files to OSS. Otherwise, the following error message appears:
Can not issue executeUpdate() or executeLargeUpdate() for SELECTsYou can access a PolarDB cluster by using a client or the MySQL command-line client MySQL to export data files to OSS. For more information, see Connect to a cluster.
If you want to perform incremental backups of data to OSS, you can create and configure a backup schedule. For more information, see Create a backup schedule and Configure a backup schedule.
We recommend performing export tasks on a read-only node. You can use the
/*FORCE_SALVE*/hint in the SQL statement or directly connect to a read-only node to export data.
Parameters
Parameter | Description |
loose_oss_outfile_buffer_size | The maximum memory size allowed for each OSSOUTFILE thread. Valid values: 102400 to 536870912. Default value: 134217728. Unit: bytes. The larger the memory size, the faster the export. |
loose_max_oss_outfile_threads | The maximum number of OSSOUTFILE threads that can be simultaneously started in PolarDB. Valid values: 1 to 100. Default value: 1. You can execute the Note This parameter does not take effect for PolarDB for MySQL 8.0.1.1.38 or later. |
You can calculate the maximum total memory used by the OSSOUTFILE feature by using the following formula:
loose_max_oss_outfile_threads * loose_oss_outfile_buffer_sizeThe total memory used by the OSSOUTFILE feature must not exceed 5% of the node memory. Otherwise, other services on the current node may be affected.
Usage notes
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: the delimiter between fields.OPTIONALLY ENCLOSED BY: the identifiers at both ends of a field. After OPTIONALLY is added, identifiers are added only to both ends of a string-type field. Otherwise, identifiers are added to both ends of all field types.NULL_MARKER BY: specifies how NULL values in the database tables are represented in the exported text file. NULL values can be represented in the exported data file by using the following methods (listed from the highest to lowest priority):NULL_MARKER: directly defines the output representation of NULL values in the exported text file.
NULL_MARKERallows you to use any arbitrary string as the representation of the NULL value. This method has the highest priority.ESCAPED BY: specifies a single escape character that prefixes the N character to represent the NULL value. For example, if you specify
ESCAPED BY '\', NULL values are represented as \N in the exported data file.NULL (default): If you do not use one of the preceding methods, the NULL values are represented by using the string "NULL". This method has the lowest priority. NULL does not have
ENCLOSEDidentifiers at both ends.
LINES TERMINATED BY: the delimiter between rows.
For more information, see MySQL documentation.
Parameters
Parameter | Description |
outfile_path | The path in the OSS bucket that stores the exported data file. It contains the following parts:
The parts are separated with forward slashes ( |
table_name | The name of the database table from which you want to export data to OSS. |
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"}'); In this example, the preceding statement is used to create an OSS server for a cluster of later versions. In actual operations, modify the statement based on the engine version of your cluster.
Set the
oss_endpointparameter to an internal endpoint in theoss-{xxx}-internal.aliyuncs.comformat.
Export data to OSS
For example, you can execute the following statement to export data to oss://polardb/B_outfile/tpch/1t/parallel-lineitem.CSV.
polardb is the bucket name.
B_outfile/tpch/1t/parallel-lineitem.CSVis the file path and name.
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';Monitor the resource usage of data export tasks
If your cluster runs PolarDB for MySQL 8.0.1.1.38 or later, you can execute the following statement to view the total memory size and total number of threads used by the current export task in real time:
polardb is the bucket name
SHOW STATUS LIKE "%Oss_outfile_memory_used%";View the total memory size (in bytes) used by the export task in real time.
SHOW STATUS LIKE "%Oss_outfile_threads_running%";
If you run an export task on a node by using the cluster endpoint, you can query information about the memory resources used by the task only if you execute the SHOW STATUS and export statements on the same node.