PolarDB:Use OSS foreign tables to access OSS data

Supported data types

Step 1: Create an OSS server

An OSS server stores the connection configuration for your OSS bucket. All other methods of connecting to OSS have been disabled for security reasons.

SHOW GRANTS FOR username;

SELECT Server_name, Extra_server_info FROM mysql.servers;

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER oss OPTIONS
(
  [DATABASE '<my_database_name>',]
  EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id": "<my_oss_access_key_id>","oss_access_key_secret": "<my_oss_access_key_secret>","oss_prefix":"<my_oss_prefix>","oss_sts_token":"<my_oss_sts_token>"}'
);

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER oss OPTIONS
(
  [DATABASE '<my_database_name>',]
  EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id":"<my_oss_access_key_id>","oss_access_key_secret":"<my_oss_access_key_secret>"}'
);

ALTER SERVER server_name OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>",
"oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>",
"oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');

Step 2: Upload data

Use ossutil to upload local CSV files to your OSS bucket. Follow these naming rules:

• Place CSV files in the DATABASE or oss_prefix directory configured on your OSS server.

• Name each file <foreign_table_name>.CSV. The .CSV extension must be uppercase. For example, if the table is named t1, the file must be t1.CSV.

• Make sure the CSV columns match the columns defined in the OSS foreign table.

For existing MySQL data, upload the data file directly and create the OSS foreign table based on the same table definition.

Step 3: Create an OSS foreign table

After creating an OSS server, create a foreign table to bind it to a specific CSV file:

CREATE TABLE <table_name> (create_definition,...) ENGINE=CSV CONNECTION="<connection_string>";

The connection_string is a /-separated path with up to three components:

server_name[/oss_path][/data_file_name]

Example — connect to a file in a subdirectory:

CREATE TABLE t1 (id INT) ENGINE=CSV CONNECTION="server_name/a/b/c/d/t1";

This connects t1 to the file at oss_prefix/a/b/c/d/t1.CSV in OSS.

Example — point a table to a file with a different name:

-- Queries t2.CSV in the oss_prefix path
CREATE TABLE t1 (id INT) ENGINE=CSV CONNECTION="server_name/t2";

After creation, verify the table was created with the CSV engine:

SHOW CREATE TABLE t1;

Check that the output contains ENGINE=CSV. If it does not, your cluster version is too old to support OSS foreign tables. See Prerequisites.

Step 4: Query data

Run standard SQL statements against OSS foreign tables:

-- Count rows
SELECT count(*) FROM t1;

-- Filter by range
SELECT id FROM t1 WHERE id < 10 AND id > 1;

-- Point lookup
SELECT id FROM t1 WHERE id = 3;

-- Join with another table
SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name LIKE "%er%";

If a query produces a warning instead of an error, run SHOW WARNINGS; to view the details.

Pushdown requirements

For a condition to be pushed down, all of the following must be true:

• The CSV file is UTF-8 encoded.

• Only a single file is queried (no JOIN, ORDER BY, GROUP BY, or HAVING).

• The WHERE clause uses only supported operators: >, <, >=, <=, ==, LIKE, IN, AND, OR, +, -, *, /.

• The WHERE clause contains no aggregation (for example, WHERE max(age) > 100 is not allowed).

• The query has at most 1,000 columns, each column name at most 1,024 bytes.

• The LIKE clause has at most 5 wildcards (%).

• The IN clause has at most 1,024 constants.

• Each column and row in the CSV is at most 256 KB.

• The SQL statement is at most 16 KB, with at most 20 expressions after WHERE and at most 100 aggregation operations.

View the execution plan

Use EXPLAIN to see which conditions are pushed down.

Default format:

EXPLAIN SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;

Conditions listed after With pushed engine condition are evaluated in OSS. All other conditions are evaluated locally.

Tree format (requires PolarDB for MySQL 8.0.2):

EXPLAIN FORMAT=tree SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;

Conditions listed after engine conditions: are evaluated in OSS.

JSON format:

EXPLAIN FORMAT=json SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;

Conditions in the engine_condition field are evaluated in OSS.

Disable pushdown

If your data contains characters incompatible with pushdown, you will see:

OSS error: The current query does not support engine condition pushdown. You need to use NO_ECP() hint or set optimizer_switch = 'engine_condition_pushdown=OFF' to turn off the condition push down function.

Disable pushdown per query with a hint:

SELECT /*+ NO_ECP(t1) */ `j` FROM `t1` WHERE `j` LIKE "%c%" LIMIT 10;

Disable pushdown for the current session:

SET SESSION optimizer_switch='engine_condition_pushdown=off';

Check the current status:

SELECT @@optimizer_switch;