PolarDB可以通过OSS外表直接查询存储在OSS上的CSV格式数据,有效地降低存储的成本。本文档主要介绍了通过OSS外表访问OSS数据的操作步骤。

前提条件

PolarDB集群版本需满足如下条件之一:
  • PolarDB MySQL引擎8.0.1版本且修订版本为8.0.1.1.25.4及以上。
  • PolarDB MySQL引擎8.0.2版本且修订版本为8.0.2.2.1及以上。

如何确认集群版本,详情请参见查询版本号

背景信息

通过OSS外表,您可以把CSV格式的冷数据存储在OSS引擎上,并对冷数据进行查询和分析。具体原理如下:OSS外表
CSV格式的数据支持的数据类型包括数值类型、日期和时间类型、字符串类型。具体如下
说明 目前不支持地理空间数据类型和NULL值的类型。
  • 数值类型
    类型 大小 数据范围(有符号) 数据范围(无符号) 说明
    TINYINT 1 Byte -128~127 0~255 小整数值
    SMALLINT 2 Bytes -32768~32767 0~65535 大整数值
    MEDIUMINT 3 Bytes -8388608~8388 607 0~16777215 大整数值
    INT或INTEGER 4 Bytes -2147483648~2147483647 0~4294967295 大整数值
    BIGINT 8 Bytes -9,223,372,036,854,775,808~9223372036854775807 0~18446744073709551615 极大整数值
    FLOAT 4 Bytes -3.402823466 E+38~-1.175494351E-38;0;1.175494351E-38~3.402823466351E+38 0;1.175494351E-38~3.402823466E+38 单精度浮点数值
    DOUBLE 8 Bytes -2.2250738585072014E-308~-1.7976931348623157E+308;0;1.7976931348623157E+308~2.2250738585072014E-308 0;1.7976931348623157E+308~2.2250738585072014E-308 双精度浮点数值
    DECIMAL 对于DECIMAL(M,D) ,如果M>D,为M+2;否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
  • 日期和时间类型
    类型 大小 数据范围 数据格式 说明
    DATE 3 Bytes 1000-01-01~9999-12-31 YYYY-MM-DD 日期值。
    TIME 3 Bytes -838:59:59~838:59:59 HH:MM:SS 时间值或持续时间。
    YEAR 1 Byte 1901~2155 YYYY 年份值。
    DATETIME 8 Bytes 1000-01-01 00:00:00~9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值。
    说明 该类型中的月份和日期必须是两位数。例如,2020年1月1日要写成2020-01-01 ,而不能写成2020-1-1,否则该查询下推到OSS后无法被正确执行。
    TIMESTAMP 4 Bytes 1970-01-01 00:00:00~2038 YYYYMMDD HHMMSS 时间戳(混合日期和时间值)。
    说明 该类型中的月份和日期必须是两位数。例如,2020年1月1日要写成2020-01-01 ,而不能写成2020-1-1,否则该查询下推到OSS后无法被正确执行。
  • 字符串类型
    类型 大小 说明
    CHAR 0~255 Bytes 定长字符串
    VARCHAR 0~65535 Bytes 变长字符串
    TINYBLOB 0~255 Bytes 不超过255个字符的二进制字符串
    TINYTEXT 0~255 Bytes 短文本字符串
    BLOB 0~65535 Bytes 二进制形式的长文本数据
    TEXT 0~65535 Bytes 长文本数据
    MEDIUMBLOB 0~16777215 Bytes 二进制形式的中等长度文本数据
    MEDIUMTEXT 0~16777215 Bytes 中等长度文本数据
    LONGBLOB 0~4294967295 Bytes 二进制形式的极大文本数据
    LONGTEXT 0~4294967295 Bytes 极大文本数据

使用限制

  • 目前通过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。
  2. 连接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>"}'
        );                  
        参数说明如下表所示:
        参数 类型 备注
        server_name 字符串 OSS server名称。
        说明 该参数为全局参数,且全局唯一。该参数不区分大小写,最大长度不超过64个字符,超过64个字符的名称会被自动截断。您可以将OSS server名称指定为带引号的字符串。
        my_database_name 字符串 当前CSV数据文件在OSS中的目录名称。
        my_oss_endpoint 字符串 OSS对应区域的域名。
        说明 如果是从阿里云的主机访问数据库,应该使用内网域名(即带有“internal”的域名),避免产生公网流量。
        my_oss_bucket 字符串 数据文件所在OSS的bucket,需要通过OSS预先创建。
        my_oss_acess_key_id 字符串 OSS账号ID。
        my_oss_acess_key_secret 字符串 OSS账号KEY。
      2. 创建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_idaccess_key_secret为OSS账号ID和账号KEY;endpoint为OSS服务的域名,域名要写全;bucket为OSS服务的bucket名称;database为CSV数据文件在OSS中的目录名称;table是CSV数据文件名,这里注意不需要写后缀.CSV
  3. 数据查询。
    以上述步骤示例中的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中只支持以下几种类型的算子和子句: > , < , >= , <= , == 等比较算子、LIKEINAND , 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 `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)
    其中,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 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)
    其中,engine conditions:后面的条件可以被下推到远程OSS引擎上执行,其余的条件`name` LIKE "%1%%%%%"GROUP BY `id` ORDER BY `id` DESC不能被下推到OSS引擎上执行,只会在本地OSS server上执行。
    说明 集群版本需为PolarDB MySQL引擎8.0.2版本,您可以通过查询版本号确认集群版本。
  • 通过Json格式查看OSS Foreign Table的执行计划。示例如下:
    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)
    同上,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信息。