本文为您介绍外部表的数据分区功能。

前面章节中外部表关联数据是通过LOCATION指定OSS目录来实现,而在处理的时候,MaxCompute会读取目录下的所有数据,包括子目录中的所有文件。对于数据量比较大,尤其是随着时间不断积累的数据目录,对全目录扫描可能带来不必要的I/O消耗以及数据处理时间。 解决这个问题通常有两种方法:
  • 数据分区存储:对数据存放地址做好规划,考虑使用多个EXTERNAL TABLE读取不同部分的数据,让每个EXTERNAL TABLE的LOCATION指向数据的一个子集。
  • 外部表数据分区:外部表与内部表一样,支持分区表的功能,可以通过这个功能来对数据进行系统化的管理。

下面为您介绍外部表的分区功能。

分区数据在OSS上的标准组织方式和路径格式

对于存放在外部存储上(例如OSS)的数据,MaxComput没有数据的管理权限,因此如果需要使用分区表功能,在OSS上数据文件的存放路径必须符合一定的格式,路径格式如下。
partitionKey1=value1\partitionKey2=value2\...
  1. 创建外部表。
    将每天产生的Log文件存放在OSS上,并需要通过MaxCompute进行数据处理。数据处理时按照天来访问数据。假设这些Log文件为CSV格式且可以用内置Extractor访问(复杂自定义格式用法也类似),则外部分区表定义数据如下。
    CREATE EXTERNAL TABLE log_table_external (
        click STRING,
        ip STRING,
        url STRING,
      )
      PARTITIONED BY (
        year STRING,
        month STRING,
        day STRING
      )
      STORED BY 'com.aliyun.odps.CsvStorageHandler'
      WITH SERDEPROPERTIES (
     'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
    ) 
      LOCATION 'oss://oss-cn-hangzhou-zmf.aliyuncs.com/oss-odps-test/log_data/';

    如上建表语句,和前面章节例子的区别在于定义外部表时,通过PARTITIONED BY的语法指定该外部表为分区表。该例子是一个三层分区的分区表,分区的Key分别是yearmonthday

    为了让分区生效,在OSS上存储数据时需要遵循LOCATION的路径格式。有效的路径存储格式如下。
    osscmd ls oss://oss-odps-test/log_data/
    2017-01-14 08:03:35 128MB Standard oss://oss-odps-test/log_data/year=2016/month=06/day=01/logfile
    2017-01-14 08:04:12 127MB Standard oss://oss-odps-test/log_data/year=2016/month=06/day=01/logfile.1
    2017-01-14 08:05:02 118MB Standard oss://oss-odps-test/log_data/year=2016/month=06/day=02/logfile
    2017-01-14 08:06:45 123MB Standard oss://oss-odps-test/log_data/year=2016/month=07/day=10/logfile
    2017-01-14 08:07:11 115MB Standard oss://oss-odps-test/log_data/year=2016/month=08/day=08/logfile
    ...
    说明 因为数据是离线准备的,即通过osscmd或者其它OSS工具上传到OSS存储服务,所以数据路径格式也在上传时决定。
  2. 通过ALTER TABLE ADD PARTITIONDDL语句,即可把这些分区信息引入MaxCompute。
    ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '01')
    ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '02')
    ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '07', day = '10')
    ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '08', day = '08')
    ...
    说明 以上这些操作与标准的MaxCompute内部表操作一样,分区的详情请参见分区。在数据准备好并且PARTITION信息引入MaxCompute之后,即可通过SQL语句对OSS外表数据的分区进行操作。
  3. 进行数据分析。
    • 执行如下语句,分析2016年6月1日有多少不同的IP出现在Log中。
      SELECT count(distinct(ip)) FROM log_table_external WHERE year = '2016' AND month = '06' AND day = '01';

      外表log_table_external将只访问log_data/year=2016/month=06/day=01子目录下的文件(logfilelogfile.1),不会对整个log_data目录作全量数据扫描,避免大量无用的I/O操作。

    • 执行如下语句,分析2016年下半年有多少不同的IP出现在Log中。
      SELECT count(distinct(ip)) FROM log_table_external WHERE year = '2016' AND month > '06';

分区数据在OSS上的自定义路径

如果事先存在OSS上的历史数据,路径格式不是partitionKey1=value1\partitionKey2=value2\...。当需要通过MaxCompute的分区方式进行访问计算时,MaxCompute也提供了通过自定义路径来引入分区的方法。

假设OSS数据路径只有简单的分区值(而无分区Key信息),数据的显示如下。
osscmd ls oss://oss-odps-test/log_data_customized/
2017-01-14 08:03:35 128MB Standard oss://oss-odps-test/log_data_customized/2016/06/01/logfile
2017-01-14 08:04:12 127MB Standard oss://oss-odps-test/log_data_customized/2016/06/01/logfile.1
2017-01-14 08:05:02 118MB Standard oss://oss-odps-test/log_data_customized/2016/06/02/logfile
2017-01-14 08:06:45 123MB Standard oss://oss-odps-test/log_data_customized/2016/07/10/logfile
2017-01-14 08:07:11 115MB Standard oss://oss-odps-test/log_data_customized/2016/08/08/logfile
...
可以通过SQL语句将不同的子目录指定到不同的分区,示例如下。
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '01')
LOCATION 'oss://oss-cn-hangzhou-zmf.aliyuncs.com/oss-odps-test/log_data_customized/2016/06/01/';

ADD PARTITION时增加了LOCATION信息,从而实现自定义分区数据路径后,即使数据存放不符合推荐的partitionKey1=value1\partitionKey2=value2\...格式,您也可以正确地对子目录数据进行分区访问。