原文地址：通过OSS外表访问OSS数据 - 云原生关系型数据库 PolarDB MySQL引擎 - 阿里云
PolarDB可以通过OSS外表直接查询存储在OSS上的CSV格式数据，有效地降低存储的成本。本文档主要介绍了通过OSS外表访问OSS数据的操作步骤。
背景信息
通过OSS外表，您可以把CSV格式的冷数据存储在OSS引擎上，并对冷数据进行查询和分析。具体原理如下：
CSV格式的数据支持的数据类型包括数值类型、日期和时间类型、字符串类型。具体如下
说明 目前不支持地理空间数据类型和NULL值的类型。
-
数值类型
-
日期和时间类型
-
字符串类型
使用限制
-
目前通过OSS外表仅支持查询CSV格式的数据。
-
目前针对OSS外表的语句只支持CREATE、SELECT、DROP三种。
说明 DROP操作不会删除OSS上的数据文件，仅删除PolarDB上的表信息。
-
OSS外表目前不支持索引、分区和事务。
操作步骤
-
上传CSV格式的数据到OSS。
您可以通过命令行工具ossutil把本地的CSV格式数据上传到远程OSS引擎上。
说明
-
上传CSV文件的OSS目录需要与OSS server中DATABASE的目录保持一致。
-
上传的CSV文件名需要设置为 外表名+.CSV 。例如，创建的OSS Foreign Table为 t1 ，则上传的CSV文件名需要设置为 t1.CSV 。
-
CSV文件中的数据字段与OSS Foreign Table字段需要匹配。例如：创建的OSS Foreign Table t1 表中只有一个字段 id ，类型为 int 。则上传的CSV文件中也只能有一个 int 类型的字段。
-
建议您直接上传本地mysql的数据文件，并依据表定义创建对应的OSS Foreign Table。
-
连接OSS。
您可以通过OSS server或者CONNECTION两种方法来连接OSS。
-
方法一：通过OSS server来连接OSS
-
创建OSS server
-
在PolarDB上创建OSS Server，即定义需要访问的OSS服务端。语法如下：
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_acess_key_id>", "oss_access_key_secret": "<my_oss_acess_key_secret>"}'; 参数说明如下表所示：
-
创建OSS Foreign Table
定义了OSS Server之后，您需要在PolarDB上创建OSS外表，建立与OSS的连接。示例如下：
create table t1 (id int not null) engine=csv connection="server_name";其中，connection可以只配置为server名；如果您希望指定OSS服务上表的名称，也可以配置为server名/表名的形式。示例如下：
create table t1 (id int not null) engine=csv connection="server_name/t2";
说明 如果不指定表名，则当前表对应的OSS文件为t1.CSV；如果指定表名，则当前表对应的OSS文件为t2.CSV。
-
方法二：通过CONNECTION来连接OSS
如果您仅希望创建一个OSS表，不想创建server的相关信息，也可以直接通过CONNECTION创建对应的OSS表来连接OSS。建表语句示例如下：
CREATE TABLE `t` (`id` int(20) NOT NULL,
`name` varchar(32) NOT NULL DEFAULT ''
) ENGINE=CSV DEFAULT CHARSET=utf8
CONNECTION="oss://access_key_id:access_key_secret@endpoint/bucket/database/table";其中，access_key_id和access_key_secret为OSS账号ID和账号KEY；endpoint为OSS服务的域名，域名要写全；bucket为OSS服务的bucket名称；database为CSV数据文件在OSS中的目录名称；table是CSV数据文件名，这里注意不需要写后缀.CSV。
-
数据查询。
以上述步骤示例中的t1表为例进行说明。
#查询t1表内的数据数量
SELECT count(*) FROM t1;
#范围查询
SELECT id FROM t1 WHERE id < 10 AND id > 1;
#点查
SELECT id FROM t1 where id = 3;
#多表join
SELECT id FROM t1 left join t2 on t1.id = t2.id WHERE t2.name like "%er%";查询优化
OSS引擎在查询过程中，可以将部分的查询条件下推到远程引擎OSS上执行，以获得更好的查询效率，这个优化被称之为engine condition pushdown。可以下推的限制条件如下：
-
目前仅支持UTF-8编码格式的CSV文本文件。
-
SQL中只支持以下几种类型的算子和子句： > , < , >= , <= , == 等比较算子、 LIKE 、 IN 、 AND , OR 逻辑算子、 + , - , * , / 算数表达式。
-
仅支持单文件查询，不支持join、order by、group by、having子查询。
-
where语句里不能包含聚合条件，例如 where max(age) > 100 是不允许的。
-
支持的最大列数是1000，SQL中最大列名长度不能超过1024个字节。
-
在LIKE语句中，支持最多5个 % 通配符。
-
在IN语句中，最多支持1024个常量项。
-
CSV文件支持单行及单列的最大字符数均为256 KB。
-
SQL最大长度为16 KB，where语句后面的表达式个数最多20个，聚合操作最多100个。
符合以上条件的查询会被下推到OSS引擎去执行。您可以通过OSS Foreign Table的执行计划来查看哪些查询条件被下推到OSS引擎上执行。
-
通过 explain 查看OSS Foreign Table的执行计划。示例如下：
EXPLAIN SELECT count(*) FROM `t3` 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 t3 NULL ALL NULL NULL NULL NULL 2 50.00 With pushed engine condition ((`test`.`t3`.`id` > 5) and (`test`.`t3`.`id` < 100)); Using temporary; Using filesort其中，With pushed engine condition后面的条件可以被下推到远程OSS引擎上执行，其余的条件`name` LIKE "%1%%%%%"、GROUP BY `id` ORDER BY `id` DESC不能被下推到OSS引擎上执行，只会在本地OSS server上执行。
-
通过 tree 格式查看OSS Foreign Table的执行计划。示例如下：
EXPLAIN format = tree SELECT count(*) FROM `t3` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;
EXPLAIN
-> Sort: <temporary>.id DESC
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Table scan on t3, extra ( engine conditions: ((t3.id > 5) and (t3.id < 100)) ) (cost=2.73 rows=2)其中，engine conditions:后面的条件可以被下推到远程OSS引擎上执行，其余的条件`name` LIKE "%1%%%%%"、GROUP BY `id` ORDER BY `id` DESC不能被下推到OSS引擎上执行，只会在本地OSS server上执行。
-
通过 Json 格式查看OSS Foreign Table的执行计划。示例如下：
EXPLAIN format = json SELECT count(*) FROM `t3` 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": "3.72"
},
"ordering_operation": {
"using_filesort": false,
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "1.00"
},
"table": {
"table_name": "t3",
"access_type": "ALL",
"rows_examined_per_scan": 2,
"rows_produced_per_join": 1,
"filtered": "50.00",
"engine_condition": "((`test`.`t3`.`id` > 5) and (`test`.`t3`.`id` < 100))",
"cost_info": {
"read_cost": "2.62",
"eval_cost": "0.10",
"prefix_cost": "2.73",
"data_read_per_join": "808"
},
"used_columns": [
"id",
"name"
]
}
}
}
}
}
同上，engine conditions:后面的条件可以被下推到远程OSS引擎上执行，其余的条件`name` LIKE "%1%%%%%"、GROUP BY `id` ORDER BY `id` DESC不能被下推到OSS引擎上执行，只会在本地OSS server上执行。
如果OSS出现错误，您可以通过hints或者optimizer_switch手动关闭条件下推功能。
-
hints
通过hints可以针对某个查询关闭条件下推功能。例如关闭t1表的查询下推功能：
SELECT /*+ NO_ECP(t1) */ `j` FROM `t1` WHERE `j` LIKE "%c%" LIMIT 10;-
optimizer_switch
通过optimizer_switch可以针对当前session，关闭所有查询的条件下推功能。
SET SESSION optimizer_switch='engine_condition_pushdown=off'; #把engine_condition_pushdown设置为off，表示关闭当前session下所有查询的条件下推功能。通过以下命令查看当前系统的optimizer_switch状态：
select @@optimizer_switch;通过以下命令重新打开条件下推功能：
SET SESSION optimizer_switch='engine_condition_pushdown=on'; #把engine_condition_pushdown设置为on，表示打开当前session下所有查询的条件下推功能。通过集群RO节点查询OSS foreign table
创建OSS foreign table的操作正常是在集群的RW节点上进行。在RW节点上创建OSS foreign table后，通过集群的RW节点和RO节点，都可以对OSS foreign table进行查询。对OSS server进行ALTER或者DROP操作时，如果当前RO节点上有表正在使用该OSS server，RO节点上OSS foreign table的查询不受影响，但获取不到最新的OSS server信息。您需要在RO节点上执行/*force_node='pi-bpxxxxxxxx'*/ flush tables，才能读取到最新的OSS server信息。
