MaxCompute:Merge small files

Metadata detection can identify partitions that are suitable for a file merge. A merge is recommended if a partition contains more than 100 files and the average file size is less than 64 MB. Two solutions are available.

• Immediate merge

  You can use the following command to perform an immediate merge.

  ALTER TABLE <table_name> [partition (<pt_spec>)] MERGE SMALLFILES;

  After you run the immediate merge operation on the odl_bpm_wfc_task_log table, the number of files is reduced to 19 and the storage size is reduced to 37 MB. This demonstrates that merging small files also significantly improves storage efficiency.

  In most cases, the default parameters are sufficient to merge small files. However, MaxCompute provides several parameters to meet custom requirements. The following are common parameters:

  • SET odps.merge.cross.paths=true|false

    Specifies whether to merge files across paths. If a table has multiple partitions, the merge process generates a separate MergeAction for each partition. Therefore, setting odps.merge.cross.paths to true does not change the number of paths. It only merges the small files within each path separately.

  • SET odps.merge.smallfile.filesize.threshold = 64

    Specifies the threshold, in MB, for the size of small files to be merged. Files larger than this threshold are not merged. You do not need to set this parameter. If this parameter is not set, the global variable odps_g_merge_filesize_threshold is used, which has a default value of 32 MB. If you set this parameter, its value must be greater than 32 MB.

  • SET odps.merge.maxmerged.filesize.threshold = 500

    Specifies the size of the output file after the merge, in MB. If an output file exceeds this threshold, a new output file is created. You do not need to set this parameter. If this parameter is not set, the global variable odps_g_max_merged_filesize_threshold is used, which has a default value of 500 MB. If you set this parameter, its value must be greater than 500 MB.

• Merge using a PyODPS script

  You can use PyODPS to asynchronously submit a task that merges the small files generated by the previous day's jobs. The following code provides an example script:

  import os
  from odps import ODPS
  
  # Make sure that the ALIBABA_CLOUD_ACCESS_KEY_ID environment variable is set to your Access Key ID.
  # Make sure that the ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variable is set to your Access Key Secret.
  # Do not use the Access Key ID and Access Key Secret strings directly.
  o = ODPS(
      os.getenv('ALIBABA_CLOUD_ACCESS_KEY_ID'),
      os.getenv('ALIBABA_CLOUD_ACCESS_KEY_SECRET'),
      project='your-default-project',
      endpoint='your-end-point',
  )
  
  # Note: Replace $table_name with the name of your table.
  table_name = $table_name
  t = o.get_table(table_name)
  
  # Set merge options.
  hints = {'odps.merge.maxmerged.filesize.threshold': 256}
  
  # Examples for multiple partitions
  insts = []
  # iterate_partitions lists all partitions under ds=$datetime. The partition format may vary.
  for partition in t.iterate_partitions(spec="ds=%s" % $datetime):
      instance=o.run_merge_files(table_name, str(partition), hints=hints)
  		# Click the Waiting Queue in this Logview to find the Logview for the actual execution.
      print(instance.get_logview_address())
      insts.append(instance)
  
  # Wait for the partition results.
  for inst in insts:
      inst.wait_for_completion()

  To run this script, you must first install PyODPS. For installation instructions, see PyODPS.

tbcdm.dwd_tb_log_pv_di is a physical table that the data stability system identified as requiring a small file merge. According to the metadata table tbcdm.dws_rmd_merge_task_1d, most partitions of this table contain more than 1,000 files — some even exceed 7,000 — yet the average file size is less than 1 MB.

You can use the following commands to merge the small files using the immediate merge solution.

SET odps.merge.cross.paths=true;
SET odps.merge.smallfile.filesize.threshold=128;
SET odps.merge.max.filenumber.per.instance = 2000;
ALTER TABLE tbcdm.dwd_tb_log_pv_di PARTITION (ds='20151116') MERGE smallfiles;