Metadata Cache Table加速OSS外表查询

背景信息

Metadata Cache Table（MCT）缓存加速外部表是指将外部表中来自于OSS的数据文件以元信息形式存储在一个Delta Table表中。该表可以帮助提供外表文件统计信息，实现Splitting加速，旨在解决传统 OSS 外部表在小文件数量过多时，Splitting 性能较差的问题。

一个MCT缓存加速外表示例详情如下，每行数据对应外表 location 下一个数据文件的元信息。将 OSS 外表的数据文件元信息同步到 MCT 需通过手动执行刷新命令实现。

适用范围

• Schema层级

  创建MCT缓存加速外表，MaxCompute项目需要支持Schema层级，同时开启Schema语法开关。

• 只读表

  MCT缓存加速外表是一个只读表，无法通过INSERT INTO 或 INSERT OVERWRITE 等 DML 语句插入或覆写数据。如需写入数据，使用另一个指向相同OSS路径的表。

• 内部缓存表

  Metadata Cache Table是内部缓存表，对用户不可见，目前不支持直接查询其内容。

• 先执行刷新才可见

  与普通外部表不同，MCT缓存加速外部表需要执行刷新命令后，OSS上的数据文件才对表可见。未执行刷新就直接查询时，表中没有数据。

• 仅Hive体系外部表支持

  仅Hive体系的OSS直接外部表（CSV/TSV、TEXTFILE、Parquet、ORC、JSON）支持Metadata Cache功能。

语法结构

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[PARTITIONED BY (<col_name> <data_type>, ...)]
[ROW FORMAT SERDE 'serde_class'
  [WITH SERDEPROPERTIES (
    ['<property_name>'='<property_value>',...])
  ]
]
STORED AS file_format 
LOCATION 'oss://<oss_hdfs_endpoint>/<bucket>/<path_data>' 
TBLPROPERTIES (
    'metadata.cache.mode' = ['manual'|'periodic'|'crontab'],
    'metadata.staleness.seconds' = <integer>,
    '...'
);

ALTER TABLE <mc_oss_extable_name> refresh metadata;

参数说明

• 公共参数请参见基础语法参数说明。

• Metadata Cache的独有参数如下。

  property_name	是否必填	说明
  metadata.cache.mode	是	设定缓存刷新模式。 manual：手动刷新，自主控制触发时机。 periodic：周期性刷新，需配合metadata.staleness.seconds参数使用。 crontab：定时刷新，需配合metadata.crontab.expression参数使用。
  metadata.staleness.seconds	否	刷新周期，取值范围为[60, 604800]，表示60秒到1周范围内。该参数不提供强制性保障，调度器会尽可能按指定周期执行。 当metadata.cache.mode设置为periodic时，必须设置此参数。
  metadata.crontab.expression	否	定时任务表达式，采用cron表达式格式。例如，0 0 14 * * ?表示每天下午2点触发一次：0秒、0分、14时（14:00，即下午2点）、*每天、*每月、?不指定星期（与“日”互斥，避免冲突）。 当metadata.cache.mode设置为crontab时，必须设置此参数。

使用示例：ORC外部表Splitting加速

本示例在相同数据量下，对比普通ORC外部表与支持Metadata Cache的ORC外部表的读取性能。

1. 准备数据。假设OSS上已有30w+个ORC文件。Metadata Cache的优化针对大数据量级才有效果，数据量较小时可能产生负收益。

2. 创建普通ORC外部表并读取数据，查看Splitting时长。

   CREATE EXTERNAL TABLE mc_ext_orc_test_30w (
       ss_sold_date_sk BIGINT COMMENT '销售日期SK',
       ss_sold_time_sk BIGINT COMMENT '销售时间SK',
       ss_item_sk BIGINT COMMENT '商品SK',
       ss_customer_sk BIGINT COMMENT '客户SK',
       ss_cdemo_sk BIGINT COMMENT '客户人口统计SK',
       ss_hdemo_sk BIGINT COMMENT '家庭人口统计SK',
       ss_addr_sk BIGINT COMMENT '地址SK',
       ss_store_sk BIGINT COMMENT '店铺SK',
       ss_promo_sk BIGINT COMMENT '促销SK',
       ss_ticket_number BIGINT COMMENT '票据号',
       ss_quantity BIGINT COMMENT '数量',
       ss_wholesale_cost DOUBLE COMMENT '批发成本',
       ss_list_price DOUBLE COMMENT '列表价格',
       ss_sales_price DOUBLE COMMENT '销售价格',
       ss_ext_discount_amt DOUBLE COMMENT '折扣金额',
       ss_ext_sales_price DOUBLE COMMENT '扩展销售价格',
       ss_ext_wholesale_cost DOUBLE COMMENT '扩展批发成本',
       ss_ext_list_price DOUBLE COMMENT '扩展列表价格',
       ss_ext_tax DOUBLE COMMENT '税费',
       ss_coupon_amt DOUBLE COMMENT '优惠券金额',
       ss_net_paid DOUBLE COMMENT '实付金额',
       ss_net_paid_inc_tax DOUBLE COMMENT '含税实付金额',
       ss_net_profit DOUBLE COMMENT '净利润'
   )
   comment '一张Metadata Cache的ET'
   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
   with serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   STORED AS orc
   location 'oss://oss-<region>-internal.aliyuncs.com/external_table/tpcds_data/orc/mc_ext_orc_test_30w/'
   ;
   
   -- 读取SQL
   SET odps.job.workers.limit = 10;
   SELECT 
       CASE 
           WHEN ss_sold_time_sk BETWEEN 0 AND 21600 THEN '00:00-06:00'
           WHEN ss_sold_time_sk BETWEEN 21601 AND 43200 THEN '06:00-12:00'
           WHEN ss_sold_time_sk BETWEEN 43201 AND 64800 THEN '12:00-18:00'
           ELSE '18:00-24:00'
       END AS time_period,
       COUNT(*) AS sales_count,
       SUM(ss_ext_sales_price) AS total_sales
   FROM mc_ext_orc_test_30w
   GROUP BY 
       CASE 
           WHEN ss_sold_time_sk BETWEEN 0 AND 21600 THEN '00:00-06:00'
           WHEN ss_sold_time_sk BETWEEN 21601 AND 43200 THEN '06:00-12:00'
           WHEN ss_sold_time_sk BETWEEN 43201 AND 64800 THEN '12:00-18:00'
           ELSE '18:00-24:00'
       END
   ORDER BY total_sales DESC;

3. 创建支持Metadata Cache的ORC外部表，手动刷新文件元信息后读取数据，查看Splitting时长。

   CREATE EXTERNAL TABLE mc_ext_orc_test_30w_mct (
       ss_sold_date_sk BIGINT COMMENT '销售日期SK',
       ss_sold_time_sk BIGINT COMMENT '销售时间SK',
       ss_item_sk BIGINT COMMENT '商品SK',
       ss_customer_sk BIGINT COMMENT '客户SK',
       ss_cdemo_sk BIGINT COMMENT '客户人口统计SK',
       ss_hdemo_sk BIGINT COMMENT '家庭人口统计SK',
       ss_addr_sk BIGINT COMMENT '地址SK',
       ss_store_sk BIGINT COMMENT '店铺SK',
       ss_promo_sk BIGINT COMMENT '促销SK',
       ss_ticket_number BIGINT COMMENT '票据号',
       ss_quantity BIGINT COMMENT '数量',
       ss_wholesale_cost DOUBLE COMMENT '批发成本',
       ss_list_price DOUBLE COMMENT '列表价格',
       ss_sales_price DOUBLE COMMENT '销售价格',
       ss_ext_discount_amt DOUBLE COMMENT '折扣金额',
       ss_ext_sales_price DOUBLE COMMENT '扩展销售价格',
       ss_ext_wholesale_cost DOUBLE COMMENT '扩展批发成本',
       ss_ext_list_price DOUBLE COMMENT '扩展列表价格',
       ss_ext_tax DOUBLE COMMENT '税费',
       ss_coupon_amt DOUBLE COMMENT '优惠券金额',
       ss_net_paid DOUBLE COMMENT '实付金额',
       ss_net_paid_inc_tax DOUBLE COMMENT '含税实付金额',
       ss_net_profit DOUBLE COMMENT '净利润'
   )
   comment '一张Metadata Cache的ET'
   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
   with serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   STORED AS orc
   location 'oss://oss-<region>-internal.aliyuncs.com/external_table/tpcds_data/orc/mc_ext_orc_test_30w/'
   tblproperties (
     'metadata.cache.mode' = 'manual');
   
   -- 手动刷新外部表的元信息。
   ALTER TABLE mc_ext_orc_test_30w_mct REFRESH METADATA;
   
   -- 读取SQL
   SET odps.job.workers.limit = 10;
   SELECT 
       CASE 
           WHEN ss_sold_time_sk BETWEEN 0 AND 21600 THEN '00:00-06:00'
           WHEN ss_sold_time_sk BETWEEN 21601 AND 43200 THEN '06:00-12:00'
           WHEN ss_sold_time_sk BETWEEN 43201 AND 64800 THEN '12:00-18:00'
           ELSE '18:00-24:00'
       END AS time_period,
       COUNT(*) AS sales_count,
       SUM(ss_ext_sales_price) AS total_sales
   FROM mc_ext_orc_test_30w_mct
   GROUP BY 
       CASE 
           WHEN ss_sold_time_sk BETWEEN 0 AND 21600 THEN '00:00-06:00'
           WHEN ss_sold_time_sk BETWEEN 21601 AND 43200 THEN '06:00-12:00'
           WHEN ss_sold_time_sk BETWEEN 43201 AND 64800 THEN '12:00-18:00'
           ELSE '18:00-24:00'
       END
   ORDER BY total_sales DESC;

4. 结论对比。本示例预期Splitting性能提升10倍（10x）。测试中，相同SQL读取相同OSS目录时，普通外部表的Splitting时长为18s，支持Metadata Cache的外部表不到1s，符合预期。

   

性能对比结果

Metadata Cache Table可减少Splitting时长，从而降低查询延迟和资源消耗。以下为不同文件数量下的测试详情可供参考。

1. 准备数据。向OSS的不同目录分别上传100、1k、1w、10w、33w、100w、330w和1000w个JSON文件。所有文件的内容均为{"id": 1, "dt": "xxx"}。

2. 创建普通JSON外部表及开启MCT加速的JSON外部表，并刷新后者的文件元信息。

   -- 创建直接外表
   CREATE EXTERNAL TABLE
   three_tier_model.mct.et_test_100(id BIGINT, dt STRING) 
   STORED AS `json` LOCATION 'oss://oss-<region>.aliyuncs.com/<path>';
   
   -- 创建直接外表并开启MCT加速。
   CREATE EXTERNAL TABLE
   three_tier_model.mct.mct_test_100(id BIGINT, dt STRING) 
   STORED AS `json` LOCATION 'oss://oss-<region>.aliyuncs.com/<path>'
   tblproperties('metadata.cache.mode'='manual');
   
   -- refresh
   ALTER TABLE mct_test_100 refresh metadata;
   
   -- 同理可为1k/1w/10w/33w/100w/330w/1000w创建表

3. 测试结果。

   数据规模	普通外表	MCT外表
   100	0.1s	0.1s
   1000	0.1s	0.1s
   10000 (10k)	1.1s	0.1s
   100000 (100k)	16.9s	0.7s
   330000 (330k)	53.0s	4.0s
   1000000 (1m)	2min29s	9.9s
   3300000 (3.3m)	31min16s	43.5s
   10000000 (10m)	1h26min	4min40s