通过OSS外表访问OSS数据

技术原理

通过OSS外表，您可以将CSV格式的查询频度低的数据（称为冷数据）存储到OSS引擎上，并对冷数据进行查询和分析。具体原理如下：

CSV格式的数据支持的数据类型包括数值类型、日期和时间类型、字符串类型以及NULL值。具体如下：

• 数值类型

  类型	大小	数据范围（有符号）	数据范围（无符号）	说明
  TINYINT	1 Byte	-128~127	0~255	小整数值
  SMALLINT	2 Bytes	-32768~32767	0~65535	大整数值
  MEDIUMINT	3 Bytes	-8388608~8388607	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-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	时间戳（混合日期和时间值） 说明 该类型中的月份和日期必须是两位数。例如，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	极大文本数据

• NULL值

  • 插入NULL值。

    • 在OSS外表中插入NULL值。

      如果在OSS外表中插入NULL值，则需要在建表时指明对应的NULL值标记，即NULL_MARKER。OSS外表的NULL_MARKER值默认为NULL，您可以通过show create table语句来查看NULL值标记：

      show create table t1;

      查询结果如下：

      show create table t1;
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | 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)

    • 在CSV格式的文件中插入NULL值。

      如果在CSV格式的文件中需要在某一字段对应的位置插入NULL_MARKER，且NULL_MARKER两端不添加双引号，则PolarDB会把该值识别为NULL。

      说明

      • 当您在NULL_MARKER两端添加双引号，则PolarDB会识别为字符串，通过is_null语句无法查出NULL值，且如果CSV文件中被赋予NULL值的参数与OSS外表中对应的参数类型不匹配，则会报错。

      • NULL_MARKER不能设置为纯数字，也不能设置为空，且不能含有以下四种字符：

        "、\n、\r和,

      示例：创建OSS外表的建表语句如下：

      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';

      假设对应的数据文件内容如下：

      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

      则通过OSS外表查询的OSS数据如下：

      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                |
      +------+----------+---------------------+
      4 rows in set (0.00 sec)

  • 读取NULL值。

    • 从CSV格式的数据文件中读取数据时，如果CSV中的值为NULL，且OSS外表中对应的值可以为NULL时，则当前字段直接设置为NULL。

    • 从CSV格式的数据文件中读取数据时，如果CSV中的值为NULL，但OSS外表中对应的值设置为NOT NULL时，即CSV中的数据内容与OSS外表中定义的内容冲突。则会根据您设置的语法校验规则返回不同的结果。

      • 当您将语法校验规则sql_mode设置为STRICT_TRANS_TABLES时，则会报错。

      • 当您将语法校验规则sql_mode设置为除STRICT_TRANS_TABLES之外的其他模式时，如果当前字段有默认值，则当前字段的值会设置为默认值。如果没有默认值，则当前字段会根据字段类型被赋予MySQL的默认值，详情请参见数据类型默认值。且会有warning提示，您可以通过show warnings;命令查看warning提示详细信息。

      说明

      您可以通过show variables like "sql_mode";命令查看当前的语法校验规则。且可以在控制台的参数配置中通过修改sql_mode参数的值来修改当前的语法校验规则，具体请参见修改参数值。

      示例：创建一张OSS外表t，将id字段设置为NOT NULL，并且没有默认值。

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

      假设CSV格式的数据文件t.CSV中的内容为：

      NULL
      2

      通过OSS外表读取CSV格式文件中的数据会有以下两种情况：

      • 当sql_mode设置为STRICT_TRANS_TABLES时，执行如下命令，查询CSV格式文件中的数据：

        select * from t;

        报错信息如下：

        ERROR 1364 (HY000): Field 'id' doesn't have a default value

      • 当sql_mode设置为除STRICT_TRANS_TABLES之外的模式时，执行如下命令，查询CSV格式文件中的数据：

        select * from t;

        查询结果如下：

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

        其中，0为MySQL默认值。

        执行以下命令，查看warning提示信息：

        show warnings;

        查询结果如下：

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

使用限制

• 目前通过OSS外表仅支持查询CSV格式的数据。

• 目前针对OSS外表的语句只支持CREATE、SELECT、DROP三种。

  说明

  DROP操作不会删除OSS上的数据文件，仅删除PolarDB上的表信息。

• OSS外表目前不支持索引、分区和事务。

参数说明

您可以在控制台的参数配置页面查看或修改以下参数：

根据以上两个参数可以计算出OSS功能占用的总内存最大为：loose_csv_max_oss_threads * loose_csv_oss_buff_size

操作步骤

1. 上传CSV格式的数据至OSS。

   您可以通过命令行工具ossutil将本地CSV格式的数据上传到远程OSS引擎上。

   说明

   • 上传CSV文件的OSS目录需要与OSS server中DATABASE或oss_prefix的目录保持一致。

   • 上传的CSV文件名需要设置为外表名.CSV，且文件名后缀CSV必须是大写格式。例如，创建的OSS外表为t1，则上传的CSV文件名需要设置为t1.CSV。

   • CSV文件中的数据字段与OSS外表字段需要匹配。例如：创建的OSS外表 t1表中只有一个字段id，类型为INT。则上传的CSV文件中也只能有一个INT类型的字段。

   • 建议您直接上传本地MySQL的数据文件，并依据表定义创建对应的OSS外表。

2. 添加OSS连接信息。

   您可以通过创建OSS server来添加OSS连接信息。

   说明

   通过其他方式连接OSS的功能由于存在安全风险已经被禁用。目前仅支持通过创建OSS server的方式来添加OSS连接信息，并与OSS建立连接。

   • 若PolarDB MySQL版为8.0.1版本且修订版本为8.0.1.1.28及以上。或PolarDB MySQL版为8.0.2版本且修订版本为8.0.2.2.5及以上时，语法如下：

     CREATE SERVER <server_name> 
     FOREIGN DATA WRAPPER oss 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>"}'
     );

     说明

     • PolarDB MySQL版为8.0.1版本且修订版本为8.0.1.1.29及以上，或PolarDB MySQL版为8.0.2版本且修订版本为8.0.2.2.6及以上时，支持使用my_oss_sts_token参数。

     • 该语法支持DATABASE参数，若您创建的OSS server中既存在DATABASE参数，又存在my_oss_prefix参数，则最终查找文件的路径为my_oss_prefix/DATABASE。添加DATABASE参数的方法请参见下文中的内容。

     参数说明如下表所示：

     参数名称	参数类型	是否必填	参数说明
     server_name	字符串	是	OSS server名称。 说明 该参数为全局参数，且全局唯一。该参数不区分大小写，最大长度不超过64个字符，超过64个字符的名称会被自动截断。您可以将OSS server名称指定为带引号的字符串。
     my_oss_endpoint	字符串	是	OSS对应区域的域名。 说明 如果是从阿里云的主机访问数据库，应该使用内网域名（即带有“internal”的域名），避免产生公网流量。 例如：华东1（杭州）OSS节点的内网域名：oss-cn-xxx-internal.aliyuncs.com
     my_oss_bucket	字符串	是	数据文件所在OSS的bucket，需要通过OSS预先创建。 说明 OSS的bucket和PolarDB最好在同一个可用区内，以减少两者之间的网络延迟。
     my_oss_access_key_id	字符串	是	RAM用户或阿里云账号的AccessKey ID。 如何创建AccessKey请参见创建AccessKey。
     my_oss_access_key_secret	字符串	是	RAM用户或阿里云账号的AccessKey Secret。 如何创建AccessKey请参见创建AccessKey。
     my_oss_prefix	字符串	否	当前CSV数据文件在OSS中的目录。
     my_oss_sts_token	字符串	否	STS临时访问凭证。 说明 当使用STS临时访问凭证访问OSS时，该参数必填。 my_oss_sts_token参数值有默认的过期时间。如果my_oss_sts_token已过期，您需要通过以下命令重置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>"}');

     说明

     • 创建OSS server时需要SERVERS_ADMIN权限，您可以通过show grants for 当前用户命令查看当前用户是否具有SERVERS_ADMIN权限。目前，高权限账户默认具有该权限，并且高权限账户可以给低权限账户赋予该权限。

     • 如果您当前没有SERVERS_ADMIN权限，会提示错误信息：Access denied; you need (at least one of) the SERVERS_ADMIN OR SUPER privilege(s) for this operation。

     • 如果您是普通账户没有SERVERS_ADMIN权限，可以使用高权限账户执行：GRANT SERVERS_ADMIN ON *.* TO `users`@`%` WITH GRANT OPTION；如果您是高权限账户没有SERVERS_ADMIN权限，您可以点击集群列表找到所需要设置的集群ID/名称>配置与管理>账号管理>重置权限以重置权限，等待一段时间后，再查看高权限账户，此时就有SERVERS_ADMIN权限了。

     • 如果您是高权限用户，可以通过SELECT Server_name, Extra_server_info FROM mysql.servers;命令查看您创建的OSS Server信息，且oss_access_key_id和oss_access_key_secret参数信息因为涉及安全信息会被加密处理，无法查看其详细信息。

   • 若PolarDB MySQL版为8.0.1版本且修订版本在8.0.1.1.25.4至8.0.1.1.28之间，或PolarDB MySQL版为8.0.2版本且修订版本在8.0.2.2.1至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>"}'
     );                  

     说明

     该版本的语法不支持oss_prefix参数。

     参数说明如下表所示：

     参数名称	参数类型	是否必填	参数说明
     server_name	字符串	是	OSS server名称。 说明 该参数为全局参数，且全局唯一。该参数不区分大小写，最大长度不超过64个字符，超过64个字符的名称会被自动截断。您可以将OSS server名称指定为带引号的字符串。
     my_database_name	字符串	否	当前CSV数据文件在OSS中的目录名称。
     my_oss_endpoint	字符串	是	OSS对应区域的域名。 说明 如果是从阿里云的主机访问数据库，应该使用内网域名（即带有“internal”的域名），避免产生公网流量。 例如：oss-cn-xxx-internal.aliyuncs.com
     my_oss_bucket	字符串	是	数据文件所在OSS的bucket，需要通过OSS预先创建。
     my_oss_access_key_id	字符串	是	RAM用户或阿里云账号的AccessKey ID。 如何创建AccessKey请参见创建AccessKey。
     my_oss_access_key_secret	字符串	是	RAM用户或阿里云账号的AccessKey Secret。 如何创建AccessKey请参见创建AccessKey。

3. 创建OSS外表。

   定义了OSS Server之后，您需要在PolarDB上创建OSS外表，与OSS建立连接。示例如下：

   create table t1 (id int) engine=csv connection="connection_string";

   其中，connection_string由以下内容组成，且使用“/”来进行连接：

   • OSS Server名称。

   • （可选）OSS上的数据文件路径。

     说明

     当PolarDB MySQL版为8.0.1版本且修订版本为8.0.1.1.28及以上。或PolarDB MySQL版为8.0.2版本且修订版本为8.0.2.2.5及以上时，支持配置OSS上的数据文件路径。

   • （可选）数据文件名称。

     说明

     数据文件名称后面不能有 .CSV后缀。

   示例：

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

   通过示例可以看出：OSS上的数据文件路径为oss_prefix/a/b/c/d/，数据文件为t1.CSV。

   说明

   • 您可以只使用数据文件名称来指定OSS外表对应的数据文件。例如：create table t1 (id int) engine=csv connection="server_name/t2"，则PolarDB会在OSS上的oss_prefix路径下查找t2.CSV的文件。

   • 如果您在connection_string中添加了OSS上的数据文件路径，则您必须添加对应的数据文件名称。否则，查找对应的文件时会将路径的最后一段识别为文件名称。

   • 如果不指定数据文件名称，则当前表对应的OSS文件为当前表名.CSV；如果指定数据文件名称，则当前表对应的OSS文件为指定的数据文件名称.CSV。

   OSS外表创建完成后，您可以通过show create table命令查看已创建的表。请检查已创建的表的引擎是否为CSV，如果不是，可能是您当前的PolarDB版本过低，不支持OSS引擎。

4. 数据查询。

   以上述步骤示例中的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%";

   查询数据的过程中，常见的报错信息及报错原因请参见下表：

   说明

   如果在查询数据的过程中，没有报错信息但有警告信息时，您需要通过SHOW WARNINGS;命令查看报错信息。

   报错信息	报错原因	解决方案
   OSS error: No corresponding data file on the OSS engine.	OSS上没有找到对应的数据文件。	您需要根据上述规则检查OSS上对应的路径下是否存在数据文件。 若存在，确认数据文件格式是否符合命名规则。即符合外表名.CSV，且文件名后缀CSV必须为大写格式。 若不存在，则需要将数据文件上传至目标路径。
   There is not enough memory space for OSS transmission. Currently requested memory %d.	没有足够的空间进行OSS查询。	您可以通过以下两种方式中的任意一种来修复该错误： 在控制台的参数配置中通过修改loose_csv_max_oss_threads参数值来运行更多的OSS线程。 通过flush table关闭某些OSS表的线程。
   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;	当前的数据库实例无法连接OSS服务器。	检查当前的数据库实例与OSS bucket是否在同一个可用区。 如果不在同一个可用区，则需要将当前的数据库实例与OSS bucket放在同一个可用区。 如果在同一个可用区，您可以将endpoint修改为公网的endpoint。如果endpoint修改后仍然报错，请联系阿里云技术支持解决。

查询优化

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.

多节点之间同步OSS server信息

目前，PolarDB集群的主节点和只读节点共用一个OSS server，以保证在两个节点上都可以访问OSS上的数据。且两个节点间OSS Server信息同步是无锁的，以保证在两个节点上的操作不会互相影响。

当您修改OSS Server信息后，修改内容会无锁地同步到只读节点，如果只读节点上有线程持有OSS Server的锁，则可能会导致OSS server信息同步时间延迟。此时，您可以通过执行/*force_node='pi-bpxxxxxxxx'*/ flush privileges; 或/*force_node='pi-bpxxxxxxxx'*/flush table oss_foreign_table;命令来手动更新只读节点的OSS server信息。