Access OSS data using OSS foreign tables
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:
Limits
Currently, OSS foreign tables support querying CSV-formatted data only.
Currently, OSS foreign tables support only CREATE, SELECT, and DROP statements.
NoteThe 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.
NoteCurrently, 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
NoteThe 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)
NoteThe 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. TheNULL_MARKERvalue of an OSS foreign table defaults to NULL. You can run theSHOW CREATE TABLEstatement 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_MARKERat the position corresponding to a field in a CSV-formatted file, and do not add double quotation marks at either end ofNULL_MARKER, PolarDB recognizes the value as NULL.NoteIf you add double quotation marks at both ends of
NULL_MARKER, PolarDB recognizes it as a string. You cannot query NULL values by using theis_nullstatement. 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_MARKERcannot 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",NULLThe 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_modetoSTRICT_TRANS_TABLES, an error is reported.When you set the syntax validation rule
sql_modeto a mode other thanSTRICT_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 theSHOW WARNINGS;command to view the warning details.
NoteYou 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 page, modify the value of thesql_modeparameter to change the current syntax validation rule. For more information, see Modify parameter values.Example
Create an OSS foreign table named
t, set theidfield 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.CSVcontains the following data:NULL 2The following two cases occur when you read the CSV-formatted file by using the OSS foreign table:
When
sql_modeis set toSTRICT_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 valueWhen
sql_modeis set to a mode other thanSTRICT_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 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.
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.
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>"}'
);DATABASEparameter is optional. It has the same function as theoss_prefixparameter. Use theoss_prefixparameter directly.oss_sts_tokenparameter 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 |
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: |
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
|
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>"}'
); 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: |
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. |
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_ADMINprivilege, 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. Theoss_access_key_idandoss_access_key_secretparameters 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).
The OSS directory to which you upload the CSV files must match the directory of
DATABASEoross_prefixin 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 ist1, the name of the uploaded CSV file must bet1.CSV.The data fields in the CSV file must match the fields of the OSS foreign table. For example, the OSS foreign table
t1has only one fieldid, of typeINT. In this case, the uploaded CSV file can contain only one field of theINTtype.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.
NoteThe 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.
NoteThe data file name cannot have a
.CSVsuffix.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 isspecified_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 ist1.CSV. According to the parameter requirements, the.CSVsuffix is omitted.
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:
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.
|
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:
|
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.
|
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, andORArithmetic 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) > 100is 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.
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
explainto 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 conditioncan be pushed down to the remote OSS engine for execution. The remaining conditions,`name` LIKE "%1%%%%%"andGROUP 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
treeformat 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%%%%%"andGROUP BY `id` ORDER BY `id` DESC, cannot be pushed down to the OSS engine and are executed only on the local OSS Server.NoteThe cluster version must be PolarDB for MySQL 8.0.2. You can use Query the engine version to confirm the cluster version.
Use the
Jsonformat 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%%%%%"andGROUP 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
t1table: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.