文档

基于OSS外表的单表多文件查询

更新时间:

一般情况下,OSS外表存储的数据是冷数据,数据量比较大,当单个CSV格式的数据文件过大时,对其进行查询会非常耗时。因此PolarDB支持单表多文件查询功能,您可以将单个OSS外表的数据文件拆分为多个小的数据文件,以加快查询速度。本文介绍了基于OSS外表的单表多文件查询的操作步骤。

前提条件

PolarDB集群版本需满足如下条件之一:

  • PolarDB MySQL版8.0.1版本且修订版本为8.0.1.1.28及以上。

  • PolarDB MySQL版8.0.2版本且修订版本为8.0.2.2.5.1及以上。

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

操作步骤

  1. 拆分CSV文件。

    1. 您可以将CSV文件按行拆分为多个小的CSV文件,且单个CSV文件大小建议为128 MB,最大限制为1 GB。

      说明

      拆分CSV格式的文件时,必须按照完整的一行数据进行拆分,不能从一行数据中间进行拆分,需要保证每一个OSS数据文件的完整性。

      文件命名规则如下:

      • 使用OSS外表的建表语句CONNECTION参数中配置的文件名称。如果该参数中没有配置文件名称,则数据文件名称为当前OSS外表的表名.CSV。示例如下:

        • CONNECTION参数中已配置文件名称。

          CREATE TABLE t1 (id int) engine=csv CONNECTION="server_name/a/b/c/d/t1";

          通过示例可以看出:OSS上的数据文件路径为oss_prefix/a/b/c/d/,数据文件名称为t1.CSV

        • CONNECTION参数中没有配置文件名称。

          CREATE TABLE t1 (id int) engine=csv CONNECTION="server_name";

          则数据文件名称为t1.CSV

      • 需要拆分的数据文件名称 - 任意数字.CSV。示例如下:

        假设需要拆分的数据文件名称为t1.CSV,则拆分后的文件名称为 t1.CSVt1-1.CSVt1-2.CSV等。

    2. 上传数据文件。

      文件拆分后,您需要手动将所有的CSV文件上传到OSS上的同一路径下。此处以使用ossutil命令行工具批量上传CSV文件为例,关于ossutil命令行工具更多内容请参见ossutil

      ossutil cp localfolder/ oss://examplebucket/desfolder/ --include "*.CSV" -r

      其中,localfolder为待上传的CSV文件的文件夹名称,oss://examplebucket/desfolder/为OSS上的CSV文件路径。使用过程中请根据实际使用场景进行替换。

  2. 添加OSS连接信息。

    您可以通过创建OSS server来添加OSS连接信息。语法如下:

    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。请保证在该语法中配置的数据文件路径与拆分文件中上传到OSS上的数据文件路径一致。

    参数说明如下表所示:

    参数名称

    参数类型

    参数说明

    server_name

    字符串

    OSS server名称。

    说明

    该参数为全局参数,且全局唯一。该参数不区分大小写,最大长度不超过64个字符,超过64个字符的名称会被自动截断。您可以将OSS server名称指定为带引号的字符串。

    my_oss_endpoint

    字符串

    OSS对应区域的域名。

    说明

    如果是从阿里云的主机访问数据库,应该使用内网域名(即带有“internal”的域名),避免产生公网流量。

    例如:oss-cn-xxx-internal.aliyuncs.com

    my_oss_bucket

    字符串

    数据文件所在OSS的bucket,需要通过OSS预先创建。

    说明

    OSS的bucket和PolarDB最好在同一个可用区内,以减少两者之间的网络延迟。

    my_oss_access_key_id

    字符串

    OSS账号的AccessKey ID。

    my_oss_access_key_secret

    字符串

    OSS账号的AccessKey Secret。

    my_oss_prefix

    字符串

    当前CSV格式的数据文件在OSS中的路径。

    my_oss_sts_token

    字符串

    OSS临时访问凭证。获取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权限。目前,高权限账户默认具有该权限,并且高权限账户可以给低权限账户赋予该权限。

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

  3. 创建OSS外表,具体请参见创建OSS外表。外表创建成功后,PolarDB会根据您设定的路径,查找对应的文件。

  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修改后仍然报错,请联系阿里云技术支持解决。

  5. (可选)增加新的数据文件。

    如果您需要上传新的数据文件,并在t1表中读取该文件中的数据,您可以执行步骤1上传目标文件,上传完成后,对t1表执行以下操作,即可使用查询命令查询新上传文件中的数据。

    FLUSH TABLE t1;

内容优化

如需详细了解请参考冷数据并行查询