PolarDB:Export local tables to the OSS engine in parallel

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for MySQL cluster running Enterprise Edition, with revision number 8.0.1.1.38 or later (version 8.0.1) or 8.0.2.2.25 or later (version 8.0.2)

• An OSS bucket and an OSS server configured in your cluster. For setup instructions, see Use OSS foreign tables to access OSS data

• (Recommended) A connection to a read-only node. Run export tasks on a read-only node to avoid impacting the primary node. Add the /*FORCE_SLAVE*/ hint to your SQL statement, or connect directly to a read-only node

Limitations

• Only single-table queries (SELECT * FROM table) can be exported in parallel. Queries with JOIN, ORDER BY, or GROUP BY are not eligible for parallel export.

Syntax

SELECT [/*+parallel(N)*/] * FROM table_name
INTO OSSOUTFILE 'outfile_path'
[COLUMNS TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[NULL_MARKER BY 'string' | ESCAPED BY 'char']
[LINES TERMINATED BY 'string'];

To trigger parallel export, add the /*+parallel(N)*/ hint, where N is the number of parallel workers. Alternatively, enable the elastic parallel query feature so that the database automatically determines the degree of parallelism. For more information, see Overview.

For the full SELECT syntax that the INTO OSSOUTFILE clause extends, see MySQL documentation.

Considerations

Export a table in parallel

The following example exports the lineitem table with 4 parallel workers.

1. Check whether the query supports parallel execution. Run EXPLAIN to verify that the planner can parallelize the scan:

   EXPLAIN SELECT /*+parallel(4)*/ * FROM lineitem;

   Expected output:

   +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------+
   | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                     |
   +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------+
   |  1 | SIMPLE      | <gather1> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5392844 |   100.00 | NULL                      |
   |  1 | SIMPLE      | lineitem  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1348211 |   100.00 | Parallel scan (4 workers) |
   +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------+

   Parallel execution is available when the Extra column shows Parallel scan.

2. Create an OSS server. Skip this step if you already have an OSS server configured.

   CREATE SERVER outserver FOREIGN DATA WRAPPER oss OPTIONS (
     EXTRA_SERVER_INFO '{"oss_endpoint": "oss-cn-zhangjiakou.aliyuncs.com",
                         "oss_bucket": "polardb",
                         "oss_access_key_id": "<your-access-key-id>",
                         "oss_access_key_secret": "<your-access-key-secret>",
                         "oss_prefix": "B_outfile"}'
   );

   Replace the placeholder values with your OSS credentials and bucket information.

3. Verify that parallel export is supported for this query. Run EXPLAIN on the full export statement to confirm the planner will use parallel export:

   EXPLAIN SELECT /*+parallel(4)*/ * FROM lineitem
   INTO OSSOUTFILE 'outserver/t1.CSV'
   COLUMNS TERMINATED BY ','
   OPTIONALLY ENCLOSED BY '"'
   NULL_MARKER BY 'NULL'
   LINES TERMINATED BY '\n';

   Expected output:

   +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+--------------------------------------------------------+
   | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                                  |
   +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+--------------------------------------------------------+
   |  1 | SIMPLE      | <gather1> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5805759 |   100.00 | Parallel export OSS outfile                            |
   |  1 | SIMPLE      | lineitem  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1451439 |   100.00 | Parallel scan (4 workers); Parallel export OSS outfile |
   +----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+--------------------------------------------------------+

   Parallel export is active when the Extra column shows Parallel export OSS outfile. If it does not appear, see Troubleshooting.

4. Run the export.

   SELECT /*+parallel(4)*/ * FROM lineitem
   INTO OSSOUTFILE 'outserver/t1.CSV'
   COLUMNS TERMINATED BY ','
   OPTIONALLY ENCLOSED BY '"'
   NULL_MARKER BY 'NULL'
   LINES TERMINATED BY '\n';

   While the export runs, monitor resource usage from another session using the SHOW STATUS commands described in Considerations.

Troubleshooting

If the EXPLAIN output does not show Parallel export OSS outfile, use the optimizer trace to find the reason.

1. Enable the optimizer trace:

   SET optimizer_trace="enabled=on";

2. Execute the following statement to view the execution plans of SQL statements whose results are exported in parallel:

   EXPLAIN SELECT /*+parallel(4)*/ * FROM lineitem WHERE l_orderkey < 100
   INTO OSSOUTFILE 'default_oss_server/t1'
   COLUMNS TERMINATED BY ','
   OPTIONALLY ENCLOSED BY '"'
   NULL_MARKER BY 'NULL'
   LINES TERMINATED BY '\n';

3. Query the trace result:

   SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

4. In the output, locate the considered_parallel_outfile object and read the cause field:

   "considered_parallel_outfile": {
     "choose": false,
     "storage": "OSS",
     "format": "CSV",
     "mean_outfile_record_length": "79 B",
     "estimate_single_worker_outfile_size": "8 KB",
     "cause": "The data written by each worker should be greater than 1024 KB (pq_oss_min_worker_write_size)."
   }

   The cause field explains why parallel export was not used.

What's next

• Use OSS foreign tables to access OSS data

• Overview of elastic parallel query