Access OSS data using OSS foreign tables

Updated at:

PolarDBYou can directly query CSV-formatted data stored in OSS by using OSS foreign tables, effectively reducing storage costs. This topic describes how to use OSS foreign tables to access data in OSS.

Prerequisites

Your PolarDB cluster must meet one of the following conditions:

  • Engine version is MySQL 8.0.1, and the revision is 8.0.1.1.25.4 or later.

  • Engine version is MySQL 8.0.2, and the revision is 8.0.2.2.1 or later.

For information about how to check the cluster version, see Query the engine version.

How it works

With OSS foreign tables, you can store infrequently queried CSV-formatted data (known as cold data) in the OSS engine, and query and analyze the cold data. The following figure shows how it works:OSS foreign table

Limits

  • Currently, OSS foreign tables support querying CSV-formatted data only.

  • Currently, OSS foreign tables support only CREATE, SELECT, and DROP statements.

    Note

    The DROP operation does not delete data files in OSS. It deletes only table information on PolarDB.

  • Currently, OSS foreign tables do not support indexes, partitions, or transactions.

  • CSV-formatted data supports numeric types, date and time types, string types, and NULL values, as described below.

    Note
    • Currently, geospatial data types are not supported.

    • Currently, querying compressed CSV files is not supported.

    • NULL values are supported only when one of the following conditions is met.

      • Engine version is MySQL 8.0.1, and the revision is 8.0.1.1.28 or later.

      • Engine version is MySQL 8.0.2, and the revision is 8.0.2.2.5 or later.

    Numeric types

    Type

    Size

    Data range (signed)

    Data range (unsigned)

    Description

    TINYINT

    1 Byte

    -128 to 127

    0 to 255

    A small integer value

    SMALLINT

    2 Bytes

    -32,768 to 32,767

    0 to 65,535

    A large integer value

    MEDIUMINT

    3 Bytes

    -8,388,608 to 8,388,607

    0 to 16,777,215

    A large integer value

    INT or INTEGER

    4 Bytes

    -2,147,483,648 to 2,147,483,647

    0 to 4,294,967,295

    A large integer value

    BIGINT

    8 Bytes

    -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

    0 to 18,446,744,073,709,551,615

    A very large integer value

    FLOAT

    4 Bytes

    -3.402823466 E+38 to -1.175494351E-38; 0; 1.175494351E-38 to 3.402823466351E+38

    0; 1.175494351E-38 to 3.402823466E+38

    A single-precision floating-point value

    DOUBLE

    8 Bytes

    -2.2250738585072014E-308 to -1.7976931348623157E+308; 0; 1.7976931348623157E+308 to 2.2250738585072014E-308

    0; 1.7976931348623157E+308 to 2.2250738585072014E-308

    A double-precision floating-point value

    DECIMAL

    For DECIMAL(M,D), if M>D, the size is M+2; otherwise, it is D+2

    Depends on the values of M and D

    Depends on the values of M and D

    A decimal value

    Date and time types

    Type

    Size

    Data range

    Data format

    Description

    DATE

    3 Bytes

    1000-01-01 to 9999-12-31

    YYYY-MM-DD

    A date value

    TIME

    3 Bytes

    -838:59:59 to 838:59:59

    HH:MM:SS

    A time value or duration

    YEAR

    1 Byte

    1901 to 2155

    YYYY

    A year value

    DATETIME

    8 Bytes

    1000-01-01 00:00:00 to 9999-12-31 23:59:59

    YYYY-MM-DD HH:MM:SS

    A combined date and time value

    Note

    The month and day in this type must be two digits. For example, write January 1, 2020 as 2020-01-01 instead of 2020-1-1. Otherwise, the query cannot be correctly executed after it is pushed down to OSS.

    TIMESTAMP

    4 Bytes

    1970-01-01 00:00:00 to 2038-01-19 03:14:07

    YYYY-MM-DD HH:MM:SS

    A timestamp (a combined date and time value)

    Note

    The month and day in this type must be two digits. For example, write January 1, 2020 as 2020-01-01 instead of 2020-1-1. Otherwise, the query cannot be correctly executed after it is pushed down to OSS.

    String types

    Type

    Size

    Description

    CHAR

    0 to 255 Bytes

    A fixed-length string

    VARCHAR

    0 to 65,535 Bytes

    A variable-length string

    TINYBLOB

    0 to 255 Bytes

    A binary string of no more than 255 characters

    TINYTEXT

    0 to 255 Bytes

    A short text string

    BLOB

    0 to 65,535 Bytes

    Long text data in binary format

    TEXT

    0 to 65,535 Bytes

    Long text data

    MEDIUMBLOB

    0 to 16,777,215 Bytes

    Medium-length text data in binary format

    MEDIUMTEXT

    0 to 16,777,215 Bytes

    Medium-length text data

    LONGBLOB

    0 to 4,294,967,295 Bytes

    Very large text data in binary format

    LONGTEXT

    0 to 4,294,967,295 Bytes

    Very large text data

    NULL values

    Insert NULL values

    • Insert NULL values into an OSS foreign table.

      To insert a NULL value into an OSS foreign table, specify the corresponding NULL value marker, NULL_MARKER, when you create the table. The NULL_MARKER value of an OSS foreign table defaults to NULL. You can run the SHOW CREATE TABLE statement to view the NULL value marker:

      SHOW CREATE TABLE t1;

      The query result is as follows:

      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                      |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `id` int(11) DEFAULT NULL
      ) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='server_name' |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
    • Insert NULL values into CSV-formatted files.

      If you need to insert NULL_MARKER at the position corresponding to a field in a CSV-formatted file, and do not add double quotation marks at either end of NULL_MARKER, PolarDB recognizes the value as NULL.

      Note
      • If you add double quotation marks at both ends of NULL_MARKER, PolarDB recognizes it as a string. You cannot query NULL values by using the is_null statement. An error is reported if the parameter assigned a NULL value in the CSV file does not match the type of the corresponding parameter in the OSS foreign table.

      • NULL_MARKER cannot be set to a numeric-only value or an empty value, and cannot contain the following four characters:

        ", \n, \r, and ,

      Example

      The statement to create an OSS foreign table is as follows:

      CREATE TABLE `t1` (
        `id` int(11) DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `time` timestamp NULL DEFAULT NULL
      ) ENGINE=CSV NULL_MARKER='NULL' CONNECTION='server_name';

      Assume that the corresponding data file contains the following data:

      1,"xiaohong","2022-01-01 00:00:00"
      NULL,"xiaoming","2022-02-01 00:00:00"
      3,NULL,"2022-03-01 00:00:00"
      4,"xiaowang",NULL

      The OSS data queried by using the OSS foreign table is as follows:

      SELECT * FROM t1;
      +------+----------+---------------------+
      | id   | name     | time                |
      +------+----------+---------------------+
      |    1 | xiaohong | 2022-01-01 00:00:00 |
      | NULL | xiaoming | 2022-02-01 00:00:00 |
      |    3 | NULL     | 2022-03-01 00:00:00 |
      |    4 | xiaowang | NULL                |
      +------+----------+---------------------+

    Read NULL values

    • When you read data from a CSV-formatted data file, if the CSV value is NULL and the corresponding OSS foreign table value can be NULL, the field is directly set to NULL.

    • When you read data from a CSV-formatted data file, if the CSV value is NULL but the corresponding OSS foreign table value is set to NOT NULL, the data in the CSV file conflicts with the definition in the OSS foreign table. Different results are returned based on the syntax validation rule that you set.

      • When you set the syntax validation rule sql_mode to STRICT_TRANS_TABLES, an error is reported.

      • When you set the syntax validation rule sql_mode to a mode other than STRICT_TRANS_TABLES, the field is set to its default value if it has one. If it has no default value, the field is assigned the MySQL default value based on the field type. For more information, see Data Type Default Values. A warning is also returned. You can run the SHOW WARNINGS; command to view the warning details.

    Note

    You can run the SHOW VARIABLES LIKE "sql_mode"; command to view the current syntax validation rule. You can also go to the PolarDB console and, on the Settings and Management > Parameters page, modify the value of the sql_mode parameter to change the current syntax validation rule. For more information, see Modify parameter values.

    Example

    Create an OSS foreign table named t, set the id field to NOT NULL, and do not set a default value.

    CREATE TABLE `t` (
      `id` int(11) NOT NULL
    ) ENGINE=CSV 
    CONNECTION="server_name";

    Assume that the CSV-formatted data file t.CSV contains the following data:

    NULL
    2

    The following two cases occur when you read the CSV-formatted file by using the OSS foreign table:

    • When sql_mode is set to STRICT_TRANS_TABLES, run the following command to query the CSV-formatted file:

      SELECT * FROM t;

      The following error message is returned:

      ERROR 1364 (HY000): Field 'id' doesn't have a default value
    • When sql_mode is set to a mode other than STRICT_TRANS_TABLES, run the following command to query the CSV-formatted file:

      SELECT * FROM t;

      The query result is as follows:

      +----+
      | id |
      +----+
      |  0 |
      |  2 |
      +----+
      2 rows in set, 1 warning (0.00 sec)

      In this result, 0 is the MySQL default value. Run the following command to view the warning details:

      SHOW WARNINGS;

      The query result is as follows:

      +---------+------+-----------------------------------------+
      | Level   | Code | Message                                 |
      +---------+------+-----------------------------------------+
      | Warning | 1364 | Field 'id' doesn't have a default value |
      +---------+------+-----------------------------------------+
      1 row in set (0.00 sec)

Parameters

You can go to the PolarDB console and use the Settings and Management > Parameters page to view or modify the following parameters:

Parameter

Level

Parameter description

loose_csv_oss_buff_size

Session

The amount of memory currently used by one OSS thread. The default value is 134217728. Unit: Byte.

Valid values: 4096 to 134217728

loose_csv_max_oss_threads

Global

The number of OSS threads currently allowed to run. The default value is 1.

Valid values: 1 to 100

Based on the preceding two parameters, the maximum total memory used by the OSS feature is loose_csv_max_oss_threads * loose_csv_oss_buff_size.

Note

When you use the OSS feature, the total memory used by OSS should not exceed 5% of the memory of the current node. Otherwise, out-of-memory issues may occur.

Procedure

Create an OSS Server

First create an OSS Server to add OSS connection information and establish a connection to OSS.

Note

The feature for connecting to OSS by using other methods has been disabled because of security risks. Currently, you can add OSS connection information and establish a connection to OSS only by creating an OSS Server.

CREATE statements for later versions

If your cluster meets one of the following conditions, use the following CREATE syntax:

  • Engine version is MySQL 8.0.1, and the revision is 8.0.1.1.28 or later.

  • Engine version is MySQL 8.0.2, and the revision is 8.0.2.2.5 or later.

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>"}'
);
Note
  • DATABASE parameter is optional. It has the same function as the oss_prefix parameter. Use the oss_prefix parameter directly.

  • oss_sts_token parameter is supported only when one of the following conditions is met.

    • Engine version is MySQL 8.0.1, and the revision is 8.0.1.1.29 or later.

    • Engine version is MySQL 8.0.2, and the revision is 8.0.2.2.6 or later.

The following table describes the parameters:

Parameter

Parameter type

Required

Parameter description

server_name

String

Yes

The OSS Server name.

Note

This is a global and globally unique parameter. It is case-insensitive and has a maximum length of 64 characters. Names longer than 64 characters are automatically truncated. You can specify the OSS Server name as a quoted string.

my_database_name

String

No

The directory in OSS where the current CSV data file is located.

Note

If the OSS Server that you create includes both the DATABASE parameter and the my_oss_prefix parameter, the final file lookup path is my_oss_prefix/DATABASE. For information about how to add the DATABASE parameter, see the content below.

my_oss_endpoint

String

Yes

The domain name of the OSS region.

Note

If you access the database from an Alibaba Cloud host, use an internal domain name (one that contains "internal") to avoid public network traffic.

For example, the internal domain name of the OSS node in China (Hangzhou) is:oss-cn-xxx-internal.aliyuncs.com

my_oss_bucket

String

Yes

The OSS bucket where the data file is located. Create the bucket in OSS in advance.

Note

To reduce network latency, the OSS bucket and PolarDB should preferably be in the same zone.

my_oss_access_key_id

String

Yes

The AccessKey ID of a Resource Access Management (RAM) user or Alibaba Cloud account.

my_oss_access_key_secret

String

Yes

The AccessKey secret of a Resource Access Management (RAM) user or Alibaba Cloud account.

my_oss_prefix

String

No

The directory in OSS where the current CSV data file is located.

my_oss_sts_token

String

No

An STS temporary access credential.

Note
  • When Access OSS by using STS temporary credentials, this parameter is required.

  • my_oss_sts_token parameter values have a default expiration time. If my_oss_sts_token has expired, you must run the following command to reset all parameter values in EXTRA_SERVER_INFO.

    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>"}');

CREATE statements for earlier versions

If your cluster meets one of the following conditions, use the following CREATE syntax:

  • Engine version is MySQL 8.0.1, and the revision version is between 8.0.1.1.25.4 and 8.0.1.1.28.

  • Engine version is MySQL 8.0.2, and the revision version is between 8.0.2.2.1 and 8.0.2.2.5.

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>"}'
);                  
Note

The CREATE syntax of the current version does not support the oss_prefix and oss_sts_token parameters.

The following table describes the parameters:

Parameter

Parameter type

Required

Parameter description

server_name

String

Yes

The OSS Server name.

Note

This is a global and globally unique parameter. It is case-insensitive and has a maximum length of 64 characters. Names longer than 64 characters are automatically truncated. You can specify the OSS Server name as a quoted string.

my_database_name

String

No

The name of the directory in OSS where the current CSV data file is stored.

my_oss_endpoint

String

Yes

The endpoint of the corresponding OSS region.

Note

If you access the database from an Alibaba Cloud host, use the internal endpoint (the endpoint that contains "internal") to avoid generating Internet traffic.

For example:oss-cn-xxx-internal.aliyuncs.com

my_oss_bucket

String

Yes

The OSS bucket where the data files are stored. The bucket must be created in OSS in advance.

my_oss_access_key_id

String

Yes

The AccessKey ID of the RAM user or Alibaba Cloud account.

my_oss_access_key_secret

String

Yes

The AccessKey secret of the RAM user or Alibaba Cloud account.

Note

Creating an OSS Server requires the SERVERS_ADMIN privilege. You can run the SHOW GRANTS FOR username; command to check whether the current user has the SERVERS_ADMIN privilege.

  • If you do not have the SERVERS_ADMIN privilege, the following error message is returned: Access denied; you need (at least one of) the SERVERS_ADMIN OR SUPER privilege(s) for this operation. Submit a ticket to contact us, or search for the group number on DingTalk and join the DingTalk group for assistance. DingTalk group number: 24490017825.

  • If you are a privileged user, after you create an OSS Server, you can run the SELECT Server_name, Extra_server_info FROM mysql.servers; command to view information about the OSS Server that you created. The oss_access_key_id and oss_access_key_secret parameters are encrypted because they contain security-related information, so you cannot view their details.

Upload data

You can use the ossutil command line interface to upload local CSV-formatted files to Object Storage Service (OSS).

Note
  • The OSS directory to which you upload the CSV files must match the directory of DATABASE or oss_prefix in the OSS Server.

  • The name of the uploaded CSV file must be foreign_table_name.CSV, and the CSV extension in the file name must be uppercase. For example, if the OSS foreign table that you create is t1, the name of the uploaded CSV file must be t1.CSV.

  • The data fields in the CSV file must match the fields of the OSS foreign table. For example, the OSS foreign table t1 has only one field id, of type INT. In this case, the uploaded CSV file can contain only one field of the INT type.

  • As a best practice, directly upload the data files from your local MySQL database and create the corresponding OSS foreign table based on the table definition.

Create an OSS foreign table

After you define the OSS Server, you must create an OSS foreign table on PolarDB to establish a connection with OSS. Example:

CREATE TABLE <table_name> (create_definition,...) engine=csv connection="<connection_string>";

In this example, connection_string consists of the following parts and uses / to establish the connection:

  • The OSS Server name.

  • (Optional) The path to the data file in OSS.

    Note

    The data file path is supported only when one of the following conditions is met.

    • Engine version is MySQL 8.0.1, and the revision is 8.0.1.1.28 or later.

    • Engine version is MySQL 8.0.2, and the revision is 8.0.2.2.5 or later.

  • (Optional) The data file name.

    Note
    • The data file name cannot have a .CSV suffix.

    • If you do not specify a data file name, the OSS file corresponding to the current table is current_table_name.CSV. If you specify a data file name, the OSS file corresponding to the current table is specified_data_file_name.CSV.

    • If you add a data file path in OSS, you must specify a data file name. Otherwise, when searching for the corresponding file, the system identifies the final path segment as the file name.

View OSS foreign tables

After an OSS foreign table is created, you can run the SHOW CREATE TABLE <table_name>; command to view the created table. Check whether the created table uses the CSV engine (that is, ENGINE=CSV). If not, your PolarDB cluster version may be too low to support the OSS engine. Check it as described in Prerequisites.

Example

CREATE TABLE t1 (id int) engine=csv connection="server_name/a/b/c/d/t1";

The preceding example shows the composition of connection_string:

  • OSS Server name: server_name.

  • Path to the data file in OSS: oss_prefix/a/b/c/d/.

  • Data file: t1. The actual file is t1.CSV. According to the parameter requirements, the .CSV suffix is omitted.

Note

You can specify the data file associated with an OSS foreign table by using only its data file name. For example, in the following example, PolarDB searches in the OSS oss_prefix path for the t2.CSV file.

CREATE TABLE t1 (id int) engine=csv connection="server_name/t2";

Query data

The following description uses the t1 table in the preceding examples.

#Query the number of data entries in the t1 table
SELECT count(*) FROM t1;

#Range query
SELECT id FROM t1 WHERE id < 10 AND id > 1;

#Point query
SELECT id FROM t1 where id = 3;

#Multi-table join
SELECT id FROM t1 left join t2 on t1.id = t2.id WHERE t2.name like "%er%";

The following table lists common error messages and error causes when you query data:

Note

If no error message is returned but a warning is returned when you query data, run the SHOW WARNINGS; command to view the warning information.

Error message

Error cause

Solution

OSS error: No corresponding data file on the OSS engine.

The corresponding data file was not found in OSS.

Based on the preceding rules, check whether a data file exists at the corresponding OSS path.

  • If it does, confirm that the data file format complies with the naming rules. That is, the file name must match foreign_table_name.CSV, and the CSV extension must be uppercase.

  • If it does not, upload the data file to the target path.

There is not enough memory space for OSS transmission. Currently requested memory %d.

There is not enough memory space for the OSS query.

You can use either of the following methods to fix this error:

  • On the Parameters page of the console, modify the loose_csv_max_oss_threads parameter value to run more OSS threads.

  • Use flush table to stop the threads of some OSS tables.

ERROR 8054 (HY000): OSS error: error message : Couldn't connect to server.Failed connect to aliyun-mysql-oss.oss-cn-hangzhou-internal.aliyuncs.com:80;

The current database cluster cannot connect to the OSS server.

Check whether the current database instance and the OSS bucket are in the same zone.

  • If they are not in the same zone, place the current database instance and the OSS bucket in the same zone.

  • If they are in the same zone, you can change the endpoint to a public endpoint. If the error persists after you change the endpoint, contact Alibaba Cloud technical support.

Query optimization

During a query, the OSS engine can push down some query conditions to the remote OSS engine for execution to improve query efficiency. This optimization is called engine condition pushdown. The conditions that can be pushed down are subject to the following limits:

  • Currently, only UTF-8-encoded CSV text files are supported.

  • SQL statements support only the following operators and arithmetic expressions:

    • Comparison operators: >, <, >=, <=, and ==

    • Logical operators: LIKE, IN, AND, and OR

    • Arithmetic expressions: + , - , * , and /

  • Only single-file queries are supported. join, order by, group by, and having subqueries are not supported.

  • The WHERE clause cannot contain aggregate conditions. For example, where max(age) > 100 is not allowed.

  • The maximum supported number of columns is 1000, and the maximum length of a column name in SQL is 1024 bytes.

  • A LIKE clause supports at most 5 % wildcards.

  • An IN clause supports at most 1024 constant items.

  • The maximum number of characters in a CSV file row or column is 256 KB.

  • The maximum SQL length is 16 KB. A WHERE clause supports at most 20 expressions, and at most 100 aggregate operations are supported.

Note

This feature is disabled by default. To enable it, run the SET SESSION optimizer_switch='engine_condition_pushdown=on'; command.

Queries that meet the preceding conditions are pushed down to the OSS engine for execution. You can use the execution plan of an OSS foreign table to check which query conditions are pushed down to the OSS engine.

  • Use explain to view the execution plan of an OSS foreign table. An example follows:

    EXPLAIN SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                                                                                                            |
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
    |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15000 |     1.23 | Using where; With pushed engine condition ((`test`.`t1`.`id` > 5) and (`test`.`t1`.`id` < 100)); Using temporary; Using filesort |
    +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

    In this example, the conditions after With pushed engine condition can be pushed down to the remote OSS engine for execution. The remaining conditions, `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC, cannot be pushed down to the OSS engine and are executed only on the local OSS Server.

  • Use the tree format to view the execution plan of an OSS foreign table. An example follows:

    EXPLAIN FORMAT=tree  SELECT SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" Y `id` ORDER BY `id` DESC;
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                                                                                           |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | -> Sort: <temporary>.id DESC
        -> Table scan on <temporary>
            -> Aggregate using temporary table
                -> Filter: (t1.`name` like '%1%%%%%')  (cost=1690.00 rows=185)
                    -> Table scan on t1, extra ( engine conditions: ((t1.id > 5) and (t1.id < 100)) )  (cost=1690.00 rows=15000)
     |
    +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    In this example, the conditions after engine conditions: can be pushed down to the remote OSS engine for execution. The remaining conditions, `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC, cannot be pushed down to the OSS engine and are executed only on the local OSS Server.

    Note

    The cluster version must be PolarDB for MySQL 8.0.2. You can use Query the engine version to confirm the cluster version.

  • Use the Json format to view the execution plan of an OSS foreign table. An example follows:

    EXPLAIN FORMAT=json  SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | {
      "query_block": {
        "select_id": 1,
        "cost_info": {
          "query_cost": "1875.13"
        },
        "ordering_operation": {
          "using_filesort": false,
          "grouping_operation": {
            "using_temporary_table": true,
            "using_filesort": true,
            "cost_info": {
              "sort_cost": "185.13"
            },
            "table": {
              "table_name": "t1",
              "access_type": "ALL",
              "rows_examined_per_scan": 15000,
              "rows_produced_per_join": 185,
              "filtered": "1.23",
              "engine_condition": "((`test`.`t1`.`id` > 5) and (`test`.`t1`.`id` < 100))",
              "cost_info": {
                "read_cost": "1671.49",
                "eval_cost": "18.51",
                "prefix_cost": "1690.00",
                "data_read_per_join": "146K"
              },
              "used_columns": [
                "id",
                "name"
              ],
              "attached_condition": "(`test`.`t1`.`name` like '%1%%%%%')"
            }
          }
        }
      }
    } |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

    Likewise, the conditions after engine conditions: can be pushed down to the remote OSS engine for execution. The remaining conditions, `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC, cannot be pushed down to the OSS engine and are executed only on the local OSS Server.

If the following error occurs, some characters in the current OSS data file do not meet the requirements for OSS condition pushdown.

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.

You can manually disable condition pushdown by using hints or optimizer_switch.

  • hints

    You can use hints to disable condition pushdown for a query. For example, disable query pushdown for the t1 table:

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

    You can use optimizer_switch to disable condition pushdown for all queries in the current session.

    SET SESSION optimizer_switch='engine_condition_pushdown=off'; #Set engine_condition_pushdown to off to disable condition pushdown for all queries in the current session.

    You can run the following command to view the current optimizer_switch status of the system and determine the condition pushdown status of all queries in the current session:

    select @@optimizer_switch;

Synchronize OSS Server information across nodes

Currently, in a PolarDB cluster, the primary node and the read-only node share a single OSS Server so that data in OSS can be accessed on both nodes. In addition, OSS Server information is synchronized between the two nodes without locks, so that operations on the two nodes do not affect each other.

After you modify OSS Server information, the changes are synchronized to the read-only node without locks. If a thread on the read-only node holds the OSS Server lock, the synchronization of OSS Server information may be delayed. In this case, run /*force_node='pi-bpxxxxxxxx'*/ flush privileges; or /*force_node='pi-bpxxxxxxxx'*/flush table oss_foreign_table; to manually update the OSS Server information on the read-only node.