DocsICP FilingConsole
Documentation
Home Page MaxCompute Developer Reference Data Lake Analytics (Lakehouse) External Table OSS external tables Features Use metadata columns for AI data preprocessing

Use metadata columns for AI data preprocessing

Updated at:
Copy as MD
Product
My Favorites

Background

MaxCompute supports metadata columns for external tables. This feature allows you to directly access file-level metadata from underlying storage, particularly OSS, within your SQL queries. The metadata includes attributes such as file size, last modification time, and file path. You can use these properties to filter, sort, or analyze data, expanding the use cases for external tables.

In AI data preprocessing, raw data on OSS is often automatically generated by business systems for later use in AI training or fine-tuning. Not all of this data is required for training and must be filtered based on specific criteria. Often, these criteria are related to file properties, such as file size, creation time, or naming conventions, rather than the data within the files. The metadata column feature allows you to filter data based on file metadata instead of data columns. This helps you quickly build datasets for AI training.

The core implementation enhances the existing architecture in the following ways:

  • Adds 11 predefined metadata columns for queries.

  • Enhances the Hive input format wrapper to detect and process metadata columns.

  • Integrates the JindoOSS file system to retrieve file metadata.

  • Optimizes the SQL compiler to validate and parse metadata columns.

Supported metadata columns

Parameter

Type

Description

Default

_mc_ext_file_path

VARCHAR(2048)

The full OSS path of the file.

Example: oss://bucket/path/test.json

_mc_ext_file_simple_name

VARCHAR(256)

The base name of the file, without the path.

Example: test.json

_mc_ext_file_size

BIGINT

The file size in bytes.

None

_mc_ext_last_modified

TIMESTAMP

The last modification time.

None

_mc_ext_etag

VARCHAR(64)

The ETag signature of the OSS object.

None

_mc_ext_file_type

VARCHAR(32)

The type of the file, such as FILE or DIRECTORY.

FILE

_mc_ext_version_id

VARCHAR(32)

The file's version ID. This value can be NULL.

NULL

_mc_ext_owner

VARCHAR(32)

The owner of the file.

None

_mc_ext_storage_class_name

VARCHAR(32)

The storage class, such as Standard, Infrequent Access, or Archive.

Standard

_mc_ext_storage_policy_name

VARCHAR(32)

The storage policy, such as Hot, Warm, or Cold.

UNSPECIFIED

_mc_ext_fs_state_name

VARCHAR(32)

The state of the file system.

UNKNOWN

Limitations

  • Partitioned tables: Metadata columns are not supported for external partitioned tables.

  • Native readers: This feature is incompatible with the native Parquet and ORC readers.

  • Built-in processors: This feature does not support the built-in CsvStorageHandler and TsvStorageHandler.

Configuration parameter limitations

  • odps.sql.unstructured.oss.metadata.columns.enabled=true: Enables or disables the metadata column feature. The default is false.

  • odps.sql.type.system.odps2=true: You must set this parameter to true to enable the ODPS 2.0 type system.

  • odps.sql.common.table.planner.ext.hive.bridge.v2=false: Disables the common table planner.

  • odps.ext.parquet.native=false: Disables the native Parquet reader. You must set this to false as metadata columns are incompatible with this reader.

  • odps.ext.oss.orc.native=false: Disables the native ORC reader. You must set this to false as metadata columns are incompatible with this reader.

Supported formats

Metadata columns support the following formats:

  • ORC format (org.apache.hadoop.hive.ql.io.orc.OrcSerde)

  • Parquet format (org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe)

  • JSON format (org.apache.hive.hcatalog.data.JsonSerDe)

  • CSV format (org.apache.hadoop.hive.serde2.OpenCSVSerde)

Examples

Create an OSS external table

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[comment <table_comment>]
[partitioned BY (<col_name> <data_type>, ...)]
[row format serde '<serde_class>'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
]
stored AS <file_format> 
location '<oss_location>' 
[USING '<resource_name>']
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

For detailed syntax, see OSS external table.

Basic metadata column query

Prepare data

CREATE EXTERNAL TABLE mc_ext_opencsv_web_sales
(
    ws_sold_date_sk BIGINT, 
    ws_sold_time_sk BIGINT, 
    ws_ship_date_sk BIGINT, 
    ws_item_sk BIGINT NOT NULL, 
    ws_bill_customer_sk BIGINT, 
    ws_bill_cdemo_sk BIGINT, 
    ws_bill_hdemo_sk BIGINT, 
    ws_bill_addr_sk BIGINT, 
    ws_ship_customer_sk BIGINT, 
    ws_ship_cdemo_sk BIGINT, 
    ws_ship_hdemo_sk BIGINT, 
    ws_ship_addr_sk BIGINT, 
    ws_web_page_sk BIGINT, 
    ws_web_site_sk BIGINT, 
    ws_ship_mode_sk BIGINT, 
    ws_warehouse_sk BIGINT, 
    ws_promo_sk BIGINT, 
    ws_order_number BIGINT NOT NULL, 
    ws_quantity BIGINT, 
    ws_wholesale_cost DECIMAL(7,2), 
    ws_list_price DECIMAL(7,2), 
    ws_sales_price DECIMAL(7,2), 
    ws_ext_discount_amt DECIMAL(7,2), 
    ws_ext_sales_price DECIMAL(7,2), 
    ws_ext_wholesale_cost DECIMAL(7,2), 
    ws_ext_list_price DECIMAL(7,2), 
    ws_ext_tax DECIMAL(7,2), 
    ws_coupon_amt DECIMAL(7,2), 
    ws_ext_ship_cost DECIMAL(7,2), 
    ws_net_paid DECIMAL(7,2), 
    ws_net_paid_inc_tax DECIMAL(7,2), 
    ws_net_paid_inc_ship DECIMAL(7,2), 
    ws_net_paid_inc_ship_tax DECIMAL(7,2), 
    ws_net_profit DECIMAL(7,2)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH serdeproperties (
  'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
)
STORED AS TEXTFILE 
LOCATION 'oss://oss-<region>-internal.aliyuncs.com/<oss_path>/'
;

SET odps.namespace.schema=true;
INSERT OVERWRITE TABLE mc_ext_opencsv_web_sales SELECT * FROM bigdata_public_dataset.tpcds_100g.web_sales;

SELECT COUNT(*) FROM mc_ext_opencsv_web_sales;
SET odps.sql.unstructured.oss.metadata.columns.enabled=true;
SET odps.sql.type.system.odps2=true;

-- Query the metadata of all files in the external table.

SELECT
    _mc_ext_file_path,
    _mc_ext_file_simple_name,
    _mc_ext_file_size,
    _mc_ext_last_modified,
    _mc_ext_etag,
    _mc_ext_file_type,
    _mc_ext_version_id,
    _mc_ext_fs_state_name,
    _mc_ext_owner,
    _mc_ext_storage_class_name,
    _mc_ext_storage_policy_name
FROM mc_ext_opencsv_web_sales;

Filter by file size

Prepare data

-- Example table creation statement
CREATE EXTERNAL TABLE IF NOT EXISTS mc_ext_parquet_web_sales(
    ws_sold_date_sk BIGINT, 
    ws_sold_time_sk BIGINT, 
    ws_ship_date_sk BIGINT, 
    ws_item_sk BIGINT NOT NULL, 
    ws_bill_customer_sk BIGINT, 
    ws_bill_cdemo_sk BIGINT, 
    ws_bill_hdemo_sk BIGINT, 
    ws_bill_addr_sk BIGINT, 
    ws_ship_customer_sk BIGINT, 
    ws_ship_cdemo_sk BIGINT, 
    ws_ship_hdemo_sk BIGINT, 
    ws_ship_addr_sk BIGINT, 
    ws_web_page_sk BIGINT, 
    ws_web_site_sk BIGINT, 
    ws_ship_mode_sk BIGINT, 
    ws_warehouse_sk BIGINT, 
    ws_promo_sk BIGINT, 
    ws_order_number BIGINT NOT NULL, 
    ws_quantity BIGINT, 
    ws_wholesale_cost DECIMAL(7,2), 
    ws_list_price DECIMAL(7,2), 
    ws_sales_price DECIMAL(7,2), 
    ws_ext_discount_amt DECIMAL(7,2), 
    ws_ext_sales_price DECIMAL(7,2), 
    ws_ext_wholesale_cost DECIMAL(7,2), 
    ws_ext_list_price DECIMAL(7,2), 
    ws_ext_tax DECIMAL(7,2), 
    ws_coupon_amt DECIMAL(7,2), 
    ws_ext_ship_cost DECIMAL(7,2), 
    ws_net_paid DECIMAL(7,2), 
    ws_net_paid_inc_tax DECIMAL(7,2), 
    ws_net_paid_inc_ship DECIMAL(7,2), 
    ws_net_paid_inc_ship_tax DECIMAL(7,2), 
    ws_net_profit DECIMAL(7,2)) 
    STORED AS parquet 
    LOCATION 'oss://oss-<region>-internal.aliyuncs.com/<oss_path>/';
SET odps.sql.unstructured.oss.metadata.columns.enabled=true;
SET odps.sql.type.system.odps2=true;

-- Process only files larger than 50 bytes.
SELECT *, _mc_ext_file_simple_name, _mc_ext_file_size
FROM mc_ext_parquet_web_sales
WHERE _mc_ext_file_size > 50 limit 5;

Filter by file timestamp (JSON)

Prepare data

-- Example table creation statement
CREATE EXTERNAL TABLE IF NOT EXISTS mc_ext_json_web_sales(
    ws_sold_date_sk BIGINT, 
    ws_sold_time_sk BIGINT, 
    ws_ship_date_sk BIGINT, 
    ws_item_sk BIGINT NOT NULL, 
    ws_bill_customer_sk BIGINT, 
    ws_bill_cdemo_sk BIGINT, 
    ws_bill_hdemo_sk BIGINT, 
    ws_bill_addr_sk BIGINT, 
    ws_ship_customer_sk BIGINT, 
    ws_ship_cdemo_sk BIGINT, 
    ws_ship_hdemo_sk BIGINT, 
    ws_ship_addr_sk BIGINT, 
    ws_web_page_sk BIGINT, 
    ws_web_site_sk BIGINT, 
    ws_ship_mode_sk BIGINT,
    ws_warehouse_sk BIGINT, 
    ws_promo_sk BIGINT, 
    ws_order_number BIGINT NOT NULL, 
    ws_quantity BIGINT, 
    ws_wholesale_cost DECIMAL(7,2), 
    ws_list_price DECIMAL(7,2), 
    ws_sales_price DECIMAL(7,2), 
    ws_ext_discount_amt DECIMAL(7,2), 
    ws_ext_sales_price DECIMAL(7,2), 
    ws_ext_wholesale_cost DECIMAL(7,2), 
    ws_ext_list_price DECIMAL(7,2), 
    ws_ext_tax DECIMAL(7,2), 
    ws_coupon_amt DECIMAL(7,2), 
    ws_ext_ship_cost DECIMAL(7,2), 
    ws_net_paid DECIMAL(7,2), 
    ws_net_paid_inc_tax DECIMAL(7,2), 
    ws_net_paid_inc_ship DECIMAL(7,2), 
    ws_net_paid_inc_ship_tax DECIMAL(7,2), 
    ws_net_profit DECIMAL(7,2)) 
    STORED AS json 
    LOCATION 'oss://oss-<region>-internal.aliyuncs.com/<oss_path>/';
SET odps.sql.unstructured.oss.metadata.columns.enabled=true;
SET odps.sql.type.system.odps2=true;

-- Find the most recently modified files.
SELECT
    _mc_ext_file_simple_name,
    _mc_ext_file_size,
    _mc_ext_last_modified,
    _mc_ext_storage_class_name
FROM mc_ext_json_web_sales
ORDER BY _mc_ext_last_modified DESC
LIMIT 3;

Metadata aggregation (ORC)

Prepare data

-- Example table creation statement
CREATE EXTERNAL TABLE IF NOT EXISTS mc_ext_orc_web_sales(
    ws_sold_date_sk BIGINT, 
    ws_sold_time_sk BIGINT, 
    ws_ship_date_sk BIGINT, 
    ws_item_sk BIGINT NOT NULL, 
    ws_bill_customer_sk BIGINT, 
    ws_bill_cdemo_sk BIGINT, 
    ws_bill_hdemo_sk BIGINT, 
    ws_bill_addr_sk BIGINT, 
    ws_ship_customer_sk BIGINT, 
    ws_ship_cdemo_sk BIGINT, 
    ws_ship_hdemo_sk BIGINT, 
    ws_ship_addr_sk BIGINT, 
    ws_web_page_sk BIGINT, 
    ws_web_site_sk BIGINT, 
    ws_ship_mode_sk BIGINT, 
    ws_warehouse_sk BIGINT, 
    ws_promo_sk BIGINT, 
    ws_order_number BIGINT NOT NULL, 
    ws_quantity BIGINT, 
    ws_wholesale_cost DECIMAL(7,2), 
    ws_list_price DECIMAL(7,2), 
    ws_sales_price DECIMAL(7,2), 
    ws_ext_discount_amt DECIMAL(7,2), 
    ws_ext_sales_price DECIMAL(7,2), 
    ws_ext_wholesale_cost DECIMAL(7,2), 
    ws_ext_list_price DECIMAL(7,2), 
    ws_ext_tax DECIMAL(7,2), 
    ws_coupon_amt DECIMAL(7,2), 
    ws_ext_ship_cost DECIMAL(7,2), 
    ws_net_paid DECIMAL(7,2), 
    ws_net_paid_inc_tax DECIMAL(7,2), 
    ws_net_paid_inc_ship DECIMAL(7,2), 
    ws_net_paid_inc_ship_tax DECIMAL(7,2), 
    ws_net_profit DECIMAL(7,2)) 
    STORED AS orc 
    LOCATION 'oss://oss-<region>-internal.aliyuncs.com/<oss_path>/' 
    TBLPROPERTIES ('mcfed.orc.compress'='SNAPPY');
SET odps.sql.unstructured.oss.metadata.columns.enabled=true;
SET odps.sql.type.system.odps2=true;

-- Aggregate statistics by file type.
SELECT
    _mc_ext_file_type,
    COUNT(*) as file_count,
    SUM(_mc_ext_file_size) as total_size
FROM mc_ext_orc_web_sales
GROUP BY _mc_ext_file_type limit 5;

Metadata join (opencsv and ORC)

SET odps.sql.unstructured.oss.metadata.columns.enabled=true;
SET odps.sql.type.system.odps2=true;
SET odps.stage.reducer.mem=12288;
SET odps.stage.joiner.mem=12288;
SET odps.stage.reducer.num=100;
SET odps.stage.joiner.num=500;

-- Join two external tables and access the metadata from one of them.
SELECT 
  p.ws_sold_date_sk, 
  p.ws_ext_sales_price, 
  p._mc_ext_file_simple_name,
  p._mc_ext_file_size,
  p._mc_ext_last_modified,
  p._mc_ext_etag,
  p._mc_ext_file_type,
  p._mc_ext_version_id,
  p._mc_ext_owner,
  p._mc_ext_storage_class_name,
  p._mc_ext_storage_policy_name,
  p._mc_ext_fs_state_name,
  p._mc_ext_file_path
FROM mc_ext_opencsv_web_sales j
JOIN mc_ext_orc_web_sales p
ON j.ws_sold_date_sk = p.ws_sold_date_sk
WHERE j.ws_sold_time_sk < 50
ORDER BY p.ws_sold_date_sk;

Error handling examples

-- Attempt to create a table that explicitly includes a metadata column (this will fail).
CREATE EXTERNAL TABLE invalid_table (
    id INT,
    _mc_ext_file_size STRING  -- This fails because _mc_ext_file_size is a reserved column name.
)
STORED AS json
LOCATION 'oss://bucket/path/';

-- Query a metadata column when the feature is disabled (this will fail).
SET odps.sql.unstructured.oss.metadata.columns.enabled = false;
SELECT _mc_ext_file_size FROM <external_table>;  -- Column not found.
Previous：Schemaless QueryNext：Partition operations for OSS external tables