开发者社区> Yifei> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

通过OSS外表访问OSS数据

简介: 原文地址:通过OSS外表访问OSS数据 - 云原生关系型数据库 PolarDB MySQL引擎 - 阿里云PolarDB可以通过OSS外表直接查询存储在OSS上的CSV格式数据,有效地降低存储的成本。本文档主要介绍了通过OSS外表访问OSS数据的操作步骤。背景信息通过OSS外表,您可以把CSV格式的冷数据存储在OSS引擎上,并对冷数据进行查询和分析。具体原理如下:CSV格式的数据支持的数据类型包括
+关注继续查看

原文地址:通过OSS外表访问OSS数据 - 云原生关系型数据库 PolarDB MySQL引擎 - 阿里云

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

背景信息

通过OSS外表,您可以把CSV格式的冷数据存储在OSS引擎上,并对冷数据进行查询和分析。具体原理如下:

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

说明 目前不支持地理空间数据类型和NULL值的类型。

  • 数值类型

类型

大小

数据范围(有符号)

数据范围(无符号)

说明

TINYINT

1 Bytes

-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 Bytes

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。
  • 连接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。

  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_idaccess_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中只支持以下几种类型的算子和子句: > , < , >= , <= , == 等比较算子、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 `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信息。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Java使用JDBC访问数据库详细说明
Java使用JDBC访问数据库详细说明
26 0
二十九、优化数据库访问
二十九、优化数据库访问
17 0
数据湖实操讲解【OSS 访问加速】第九讲:Presto 如何高效查询 OSS 数据
数据湖 JindoFS+OSS 实操干货 36讲 每周二16点准时直播! 扫文章底部二维码入钉群,线上准时观看~ Github链接: https://github.com/aliyun/alibabacloud-jindofs
2337 0
OSS传输加速商业化,加速云上数据上传、下载访问体验
OSS 传输加速功能迎来商业化,助力客户的业务覆盖更多地域,解决远距离传输性能问题。利用全球分布的云机房能力,OSS传输加速将来自全球各地的用户访问由DNS 路由至就近的OSS接入点,用户请求到达OSS就近节点后,会经过优化过的网络及协议访问Bucket所在的数据中心。
36269 0
SQLite 数据库访问
SQLite是一个很轻量的数据库。详细介绍我就不多说了,这个东西应该在嵌入式里面用得比较多。根据我看到的资料(几个月以前看的),现在安卓应用应该也会用这个,HTML5 也支持sqlite。 SQLite也支持 SQL 语句。
1767 0
时间串变成Date类型的数据
java.text.SimpleDateFormat sf = new java.text.SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); java.util.Date d = sf.parse("2000-11-11 14:23:20"); System.out.println(d); String s = sf.format(d); Syste
1125 0
数据库压缩
数据库文件分为2个 数据库名_Data.MDF   --数据文件 数据库名_Log.LDF --日志文件 数据文件可以收缩,减少数据库未使用的空间。 操作方法,资源管理器--》数据库--》右键--》任务--》收缩--》数据库 通过选择进行收缩。
526 0
JS访问数据库[非Node.js]
提起服务器端 JavaScript,很多人第一反应都是 Node.js。其实 Java 6 开始包含 Script Engine,其中就自带了一个“阉割版”的 Mozilla Rhino - 纯 Java 实现的 JavaScript 解释器。 使用 jrunscript 就能启动这个解释器。使用 Rhino 的好处是你能使用 JavaScript 语言做开发,但又能使用现成的浩瀚的 Java
1253 0
14、HOOK和数据库访问
一、HOOK  1、局部钩子       OS感知鼠标或键盘事件,产生相应的消息,把此消息放到应用程序的消息队列中,应用程序通过调用GetMessage函数取出消息,然后调用DispatchMessage函数将这条消息调度给OS,OS会调用在设计窗口类时指定的应用程序窗口过程对这一消息进行处理。
836 0
+关注
4
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载