本文为您介绍表设计的最佳实践方式,为实际开发提供指导和依据。

产生大量小文件的操作

MaxCompute表的小文件会影响存储和计算性能。在进行表设计时,应考虑避开产生大量小文件的操作。会产生大量小文件的操作如下:
  • 使用MaxCompute Tunnel SDK上传数据时,每1次Commit会产生1个文件。这时每个文件过小(例如几KB),并且频繁上传(例如每5秒上传一次),则一小时就会产生720个小文件,一天就会产生17280个小文件。
  • 使用MaxCompute Tunnel SDK上传数据时,如果创建了Session却没有上传数据,而是直接Commit,则会产生大量空目录(在服务侧等同于小文件)。
  • 使用MaxCompute客户端执行Tunnel命令上传时,将本地大文件切分过小会导致上传后产生大量小文件。
  • 通过DataHub执行数据归档,DataHub的每个Shard写入MaxCompute时存在条件限制,即数据总量达到64 MB就Commit 1次,或每隔5分钟Commit 1次,形成1个文件。当开启的Shard数过多(例如20个Shard)时,每个Shard数据在5分钟内都远达不到64 MB(例如几百KB),就会产生大量小文件。相应地,一天会产生288×20=5760个小文件。
  • 通过DataWorks等数据开发工具将数据增量插入(INSERT INTO)MaxCompute表(或表分区)时,每次进行数据增量插入都会产生1个文件。若每次插入10条,则每天累计插入10000条记录,即会产生1000个小文件。
  • 使用阿里云DTS将数据从RDS等数据库同步到MaxCompute时,会创建全量表和增量表。在增量表进行数据插入的过程中,会因为每次数据插入条数较少而造成增量表中的小文件问题。例如,每隔5分钟执行1次同步,每次同步的数据量为10条,一天内的增量为10000条,则会产生1000个小文件。此种场景下,需要在数据同步完成后合并全量极限表和增量数据表。
  • 源数据采集客户端太多时,如果源数据通过Tunnel直接进入到一个分区,则每个源数据采集客户端提交一次数据,都会在同一分区下产生一个独立的文件,从而导致大量小文件的出现。
  • 当SLS触发FunctionCompute持续高频地向MaxCompute中传入文件时,小文件流式数据会进入MaxCompute。

根据数据划分项目空间

项目空间(Project)是MaxCompute最上层的对象。按项目空间进行资源的分配、隔离和管理,实现了多租户的管理能力。如果多个应用需要共享数据,则推荐使用同一个项目空间;反之,如果多个应用所需的数据是无关的,则推荐使用不同的项目空间。项目空间的表和分区可以通过Package授权的方式进行交换。

维度表的设计

描述属性的表通常被设计为维度表。维度表可与任意表组中的任意表进行关联,且创建时无需配置分区信息,但是对单表个数有所限制。通常要求维度表的单表量不超过1000万个。

说明
  • 维度表的数据不应被大量更新。
  • 可以使用MAPJOIN语句进行维度表和其它表的JOIN操作。

拉链表的设计

在数据仓库的数据模型设计过程中,经常会遇到如下需求:
  • 数据量较大。
  • 表中的部分字段被更新。

    例如,用户的地址、产品的描述信息、订单的状态和手机号码等。

  • 需要查看某一个时间点或时间段的历史快照信息。

    例如,查看某一个订单在某一个历史时间点的状态,或查看某一个用户在过去某段时间内更新过几次等。

  • 变化的比例不大或频率不高。

    假设总共有1000万个会员,且每天新增和发生变化的会员只有10万左右,如果每天都在表中保留一份全量,那么每次全量中会保存很多不变的信息,极大地浪费了存储资源。

MaxCompute提供了将不同表转化为极限存储表的方法。极限存储操作示例如下:
  1. 创建源表src_tbl。
    CREATE TABLE src_tbl (key0 STRING, key1 STRING, col0 STRING, col1 STRING, col2 STRING) PARTITION (datestamp_x STRING, pt0 STRING);
  2. 导入数据。
  3. 将src_tbl转变为极限存储的表。
    set odps.exstore.primarykey=key0,key1;
    [set odps.exstore.ignorekey=col0;]
    EXSTORE exstore_tbl PARTITION (datestamp_x='20140801');
    EXSTORE exstore_tbl PARTITION (datestamp_x='20140802');
说明 极限存储功能待发布,在此主要介绍其设计思想。

采集源表的设计

数据采集方式包括流式数据写入、批量数据写入和周期调度条式数据插入。数据量较大时,需确保同一个业务单元的数据使用分区表设计;数据量较小时,需优化采集频率。

  • 流式数据写入
    • 对于流式写入的数据,采集的通道通常较多,相关采集通道应该进行有效区分。在单个数据通道写入量较大的情况下,应该按照时间进行分区设计。
    • 在采集通道数据量较小的情况下,适合采取非分区表设计,将终端类型和采集时间设计成标准列字段。
    • 采用DataHub进行数据写入时,应该合理规划Shard数量,避免出现由于Shard过多导致采集通道流量较小、通道数量较多的问题。
  • 批量数据写入

    使用批量数据写入方式时,应重点关注写入周期。

  • 周期调度条式数据插入

    应避免使用周期调度条式数据插入的方法。若无法避免使用此方法,则需建立分区表,在新分区进行插入操作,减小对于原来分区的影响。

日志表的设计

日志的本质是个流水表,不涉及记录的更新。日志表设计需注意以下几点:
  • 考虑是否需要对日志进行去重处理。
  • 考虑是否需要扩展维度属性。
    • 通过考虑业务使用的频次以及关联是否会造成产出的延迟,来确定是否需要关联维度表、扩展维度属性字段。
    • 需要谨慎选择是否对维度表进行扩展。
  • 考虑区分终端类型。
    • 日志表中的数据量很庞大,在业务分析使用时,通常会按PC端、APP端来统计分析。由于PC端、APP端采用不同的体系采集数据,所以通常需要按照终端设计多个明细DWD表。
    • 如果终端较多但数据量不大,例如,一个终端的数据量小于1 TB但采集次数较多,则可以不对终端进行分区,设置终端信息为普通列。
说明
  • 对日志表进行分区设计时,可以按照日志采集的时间进行分区。在写入数据前进行数据的采集和整合,整合好后,一次性提交数据(通常是每64 MB提交1次)。
  • 日志数据很少会对原来分区执行更新操作,可以用INSERT操作进行少量数据的插入,但通常需要限制插入次数。
  • 如果有大量的更新操作,需要采用INSERT OVERWRITE操作避免小文件问题。
  • 为日志表设置合理的分区,并对长久不被访问的冷热数据配置归档操作。

互动明细表的设计

周期快照表中存放的是每天收藏的所有记录的快照。

周期快照表中历史累计的记录有很多,每天需要将当天增量表与前一天的全量表合并才能生成快照,非常耗资源。统计最近1天的新增收藏数,需要扫描全量表。建议您建立一个事务性事实表,建立一个存放当前有效收藏的周期快照表,以满足各种不同业务的统计分析需要,以降低资源的消耗。

MaxCompute表数据更新与删除操作

MaxCompute实现关系型数据库所支持的Delete、Update、Merge SQL的示例如下:
  • 准备表
    //上日全量表。
    table1(key1 STRING,key2 STRING,col1 STRING,col2 STRING);
    //今日增量表。
    table2(key1 STRING,key2 STRING,col1 STRING,col2 STRING);
    //今日增量表(删除)。
    table3(key1 STRING,key2 STRING,col1 STRING,col2 STRING);
  • Update(将table2表中的记录的值更新到table1表中)
    INSERT OVERWRITE TABLE table1
    SELECT t1.key1
          ,t1.key2
          ,CASE WHEN t2.key1 IS NOT NULL THEN t2.col1 ELSE t1.col1 END AS col1
          ,CASE WHEN t2.key2 IS NOT NULL THEN t2.col2 ELSE t1.col2 END AS col2
      FROM table1 t1
      LEFT OUTER JOIN table2 t2 ON t1.key1 = t2.key1 AND t1.key2 = t2.key2
    ;
  • Delete(从table1表中删除table2表中的记录)
    INSERT OVERWRITE TABLE table1
    SELECT t1.key1
          ,t1.key2
          ,t1.col1
          ,t1.col2
      FROM table1 t1
      LEFT OUTER JOIN table2 t2 ON t1.key1 = t2.key1 AND t1.key2 = t2.key2
      WHERE t2.key1 IS NULL
    ;                        
  • Merge(当日发生过删除操作)
    INSERT OVERWRITE TABLE table1
    SELECT *
      FROM(
    //先把上日和今日都存在的记录从上日表中排除,再把今日删除的记录排除。剩下的就是今日没有更新的记录。
    SELECT t1.key1
          ,t1.key2
          ,t1.col1
          ,t1.col2
      FROM table1 t1
      LEFT OUTER JOIN table2 t2 ON t1.key1 = t2.key1 AND t1.key2 = t2.key2
      LEFT OUTER JOIN table3 t3 ON t1.key1 = t3.key1 AND t1.key2 = t3.key2
      WHERE t2.key1 IS NULL OR t3.key1 IS NULL
      UNION ALL
    //合并上今日增量,就是今日的全量。
    SELECT t2.key1
          ,t2.key2
          ,t2.col1
          ,t2.col2
      FROM table2 t2)tt
    ;
  • Merge(当日没有发生过删除操作)
    INSERT OVERWRITE TABLE table1
    SELECT *
      FROM(
    //先把上日存在、今日也存在的记录从上日表中排除,剩下的就是今日没有更新的记录。
    SELECT t1.key1
          ,t1.key2
          ,t1.col1
          ,t1.col2
      FROM table1 t1
      LEFT OUTER JOIN table2 t2 ON t1.key1 = t2.key1 AND t1.key2 = t2.key2
      WHERE t2.key1 IS NULL
      UNION ALL
    //再合并上今日增量,就是今天的全量。
    SELECT t2.key1
          ,t2.key2
          ,t2.col1
          ,t2.col2
      FROM table2 t2)tt
    ;

表创建设计示例

  • 场景

    天气情况信息采集。

  • 基本信息
    • 数据信息包括地名、关于此地的属性信息(例如,面积和基本人口数量等)和天气信息。
    • 属性的数据变化较小,但天气信息数据采用多个终端采集,且数据量较大。
    • 天气信息变化较大,但在终端数量稳定的情况下流量基本稳定。
  • 表设计指南
    • 建议将数据信息划分为基本属性表和天气日志表,分别用于存储变化小和变化大的数据。
    • 因为天气信息的数据量巨大,在对天气日志表按照地域进行分区后,可以按照时间(例如,天)进行二级分区。此种分区方式可避免发生因某一个地点或某一个时间的天气变化而造成其他无关数据变化。
    • 建议采集终端上使用DataHub进行数据汇聚,然后依据稳定的流量值选择合适的Shard通道数量,以批量数据传输的方式写入到天气日志表中,而非INSERT INTO。
说明
  • 设计互动明细表时,区分存量数据和增量数据之间的关系非常重要。
  • 新增数据应作为增量数据写入新分区。
  • 应尽量减少对已有分区中的数据进行修改和插入新数据。
  • 在插入数据或覆盖全表时,应尽量选用INSERT OVERWRITE而并非选择INSERT INTO