分析外部OSS上的数据

背景信息

业务存续期间，随着时间的推移，数据的规模不断增加导致存储成本上升。用户在应对激烈的市场竞争时，业务逻辑也在不断地发生变化，对应计算分析的复杂度也在提升，因此，计算性能至关重要。另外，一个完整的数据应用，通常需要组合多个分析工具来完成不同的需求，数据需要流转在不同的系统之间。

使用列存索引功能读取OSS上的外表数据能很好的实现上述需求，优势如下：

• OSS作为云原生的存储方案有着非常高的性价比；

• 列存索引功能能够提供超高的计算速度和计算的灵活性；

• 开放的数据存储格式（如ORC、Parquet）有着广泛的兼容性以及高压缩比，便于数据在不同系统之间流转。

技术原理

列存索引（IMCI）是一个高性能的列存分析引擎，相关文档请参见PolarDB HTAP实时数据分析技术解密，ORC和Parquet也是列存格式，OSS支持高并发读取，在高并发时可以获取更高的网络吞吐。因此，IMCI的并行扫描功能可以充分利用OSS的高带宽，并通过并行计算或向量计算来提升CPU的使用效率，最终获得极高的分析速度，并支持离线和实时数据的聚合分析。

典型应用场景

以一个典型数仓架构为例，通过ETL将在线数据（关系数据库或者应用服务日志）导入离线分析平台做计算分析，分析的结果（如数仓模型的数据集市层ADS）再导入关系数据库中，用于对接BI报告、监控和广告计算等应用。

该架构有几个典型的瓶颈点：

1. 将离线数仓分析结果导入关系型数据库时，存在导入速度慢和存储成本高等问题；

2. 数据应用层（ADS）并不只是简单的查询数据和分析结果，也存在大量的计算，甚至会将离线数据和实时数据进行聚合分析，此时，行存格式的MySQL并不能很好的支撑这些操作。

使用该功能后，数据架构如下图所示：

该架构既解决了数据应用层（ADS）的存储成本，同时也实现了高性能的“二次”即席分析以及离线或在线数据的整合分析。

注意事项

• CREATE语句中必须添加COMMENT='columnar=1' 和CONNECTION信息。

• 当查询同时涉及本地表和OSS外表时，需要在本地表上创建列存索引。

参数说明

使用方法

1. 创建OSS外表。

   通过CREATE语句建表，并标明列类型、OSS连接信息以及OSS上的数据文件路径。您可以使用以下两种方式来标明列类型。

   • 第一种方式：从OSS数据文件中获取列类型（推荐），如下所示：

     CREATE FOREIGN TABLE `test` FROM CONNECTION='OSS://${oss_key}:${oss_key_secret}@${endpoint}/${bucket}/test.orc' 
     COMMENT='columnar=1';

     您可以使用SHOW create table tabname命令来查看列类型，如下所示：

   • 第二种方式：在建表时指定列类型，如下所示：

     CREATE TABLE `test` (
       `r_regionkey` bigint(20),
       `r_name` text,
       `r_comment` text,
       PRIMARY KEY (`r_regionkey`)
     )
     COMMENT='columnar=1' 
     CONNECTION='OSS://${oss_key}:${oss_key_secret}@${endpoint}/${bucket}/test.orc' 

     其中，OSS连接信息可以写在CONNECTION字段中。也可以先创建FOREIGH SERVER，然后在创建表时，在CONNECTION字段引用FOREIGH SERVER的OSS连接信息。示例如下：

     CREATE SERVER test_oss_server FOREIGN DATA WRAPPER oss
       OPTIONS (EXTRA_SERVER_INFO 
                '{"oss_bucket":"xxx, "oss_access_key_id":"xxx", "oss_endpoint":"xxx", 
                 "oss_access_key_secret":"xxx", "oss_prefix":"/test/path"}');
     SELECT * FROM mysql.servers;
     CREATE TABLE `test` (...) 
     COMMENT='columnar=1' 
     CONNECTION='test_oss_server/test.orc';

     上述配置用于读取OSS上/test/path/路径下的test.orc文件中的数据 。如果需要读取多个文件中的数据，您可以在建表时指定文件所在目录，且目录以“/”结尾。示例如下：

     CREATE TABLE `test`(...) COMMENT='columnar=1'
     CONNECTION='OSS://${oss_key}:${oss_key_secret}@${endpoint}/${bucket}/orders/2022-09-01/' 

     上述配置用于读取OSS上orders/2022-09-01/目录下所有符合条件的文件中的数据。

2. 对接MaxCompute导出数据。

   1. 执行如下命令，将数据写入OSS。

      CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_orc_external
      (
      vehicleId int,
      recordId int,
      patientId int,
      calls int,
      locationLatitute double,
      locationLongtitue double,
      recordTime string,
      direction string
      )
      STORED AS orc  
      LOCATION 'oss:///${oss_key}:${oss_key_secret}@oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo4/output/';
      INSERT INTO TABLE mc_oss_orc_external SELECT * FROM mc_oss_orc_external;

      上述命令会在output目录下创建一个名称为.odps的文件夹，包含.meta文件和保存ORC文件的文件夹。且每执行一次写入操作都会创建一个以日期为前缀的文件夹，来存放最近一次写入的数据。如：output/.odps/20220413*********/****.orc。

   2. 以ODPS（MaxCompute）兼容模式建表和读取数据。示例如下：

      SET imci_oss_scan_odps_compatible=on;
      CREATE FOREIGN TABLE `test` FROM CONNECTION='oss:///${oss_key}:${oss_key_secret}@oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo4/output/' 
      COMMENT='columnar=1';
      
      SELECT count(*) FROM test;

      说明

      开启ODPS兼容模式读取数据时，只读最近一次写入的数据。

性能测试

本次将使用TPC-H生成100 GB的数据，来测试使用列存索引功能查询OSS上外表中的数据的性能。

• 测试环境：Ice Lake 32C256 GB nvme本地盘。

• 关键参数：imci_lru_cache_capacity=6710886400（6.7 GB)

• 测试结果：测试结果请参见下表：

  SQL语句数量（单位：条）	执行SQL语句耗时（单位：秒）
  1	38.95
  2	21.38
  3	36.96
  4	23.12
  5	44.04
  6	26.31
  7	43.93
  8	55.97
  9	57.36
  10	11.55
  11	34.08
  12	40.05
  13	21.18
  14	44.61
  15	80.16
  16	10.88
  17	50.66
  18	35.57
  19	53.06
  20	46.97
  21	77.30
  22	8.90