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

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

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

对于存放在外部存储上(例如OSS)的数据,MaxCompute没有数据的管理权限,如果您需要使用分区表功能,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日出现在Log中的不同IP地址个数。
      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年下半年出现在Log中的不同IP地址个数。
      SELECT count(distinct(ip)) FROM log_table_external WHERE year = '2016' AND month > '06';

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

您可以利用MaxCompute提供的自定义路径引入分区的方法,通过MaxCompute分区方式访问存储在OSS上的路径格式不是partitionKey1=value1\partitionKey2=value2\...的历史数据。

假设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\...格式,您也可以正确地对子目录数据进行分区访问。