通过OSS外表访问OSS数据

原文地址：通过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外表目前不支持索引、分区和事务。

操作步骤

1. 上传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
  1. 创建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>"}';                  

参数说明如下表所示：

1. 创建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。

1. 数据查询。

以上述步骤示例中的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信息。