Process unstructured data using Object Table

This topic provides examples of how to use Object Table to query, filter, and process unstructured data stored in Object Storage Service (OSS).

Billing

The billing model depends on the operation:

Operation	Billing model	How input data volume is calculated
Refresh cached metadata	Pay-as-you-go, external table model	Number of OSS files (not file size)
View, filter, or sort objects	Pay-as-you-go, internal table model	Metadata stored in the Delta Table within MaxCompute
Call a UDF to process file content	Pay-as-you-go, external table model	Actual size of the OSS files

Use metadata filtering to avoid reading unnecessary files from OSS folders during processing tasks.

For more information, see Object Table billing.

Prerequisites

Before you begin, ensure that you have:

An OSS bucket named object-table-bucket with a folder named ot_openlake
The unstructured data from unstructured_data.zip uploaded to the ot_openlake folder
A MaxCompute project with the schema feature enabled. The examples use account UID 111740****241234, region cn-shenzhen, and project name lakehouse_yunqi_demo_sz

Available metadata columns

Each row in an Object Table represents one OSS object. The following columns are available for queries, filters, and sorting:

Column	Type	Description
`key`	STRING	Relative path of the object within the OSS location
`size`	BIGINT	Size of the object in bytes

Create an Object Table

SET odps.namespace.schema=true;
DROP TABLE IF EXISTS lakehouse_yunqi_demo_sz.default.ot_openlake_demo;

CREATE OBJECT TABLE lakehouse_yunqi_demo_sz.default.ot_openlake_demo
WITH SERDEPROPERTIES (
  'odps.properties.rolearn'='acs:ram::111740****241234:role/aliyunodpsdefaultrole')
LOCATION 'oss://oss-cn-shenzhen-internal.aliyuncs.com/object-table-bucket/ot_openlake/';

View the Object Table

SET odps.namespace.schema=true;
SHOW TABLES;

The following result is returned.

111740****241234:ot_openlake_demo

View the CREATE TABLE statement

SET odps.namespace.schema=true;
SHOW CREATE TABLE ot_openlake_demo;

The following result is returned.

CREATE OBJECT TABLE IF NOT EXISTS lakehouse_yunqi_demo_sz.`default`.ot_openlake_demo
WITH SERDEPROPERTIES (
  'serialization.format'='1',
  'odps.properties.rolearn'='acs:ram::111740****241234:role/aliyunodpsdefaultrole')
LOCATION 'oss://oss-cn-shenzhen-internal.aliyuncs.com/object-table-bucket/ot_openlake/'
TBLPROPERTIES (
  'last_modified_time'='1726564699',
  'transient_lastDdlTime'='1726564699',
  'metadata.cache.mode'='manual',
  'metadata.staleness.seconds'='3600');

Refresh cached metadata

SET odps.namespace.schema=true;
ALTER TABLE ot_openlake_demo REFRESH METADATA;

Count objects in the Object Table

SET odps.namespace.schema=true;
SELECT COUNT(*) AS ROW_COUNT FROM ot_openlake_demo;

The following result is returned.

+---------+
|row_count|
+---------+
|4        |
+---------+

Query, filter, and sort objects

Each example below targets a different filtering scenario. Mix and match conditions as needed.

Filter by file extension:

SET odps.namespace.schema=true;
SELECT key, size FROM ot_openlake_demo
WHERE key LIKE '%.json';

Filter by size:

SET odps.namespace.schema=true;
SELECT key, size FROM ot_openlake_demo
WHERE size > 100;

Filter by extension and size, sorted by size descending:

SET odps.namespace.schema=true;
SELECT key, size FROM ot_openlake_demo
WHERE key LIKE '%.json'
  AND size > 100
ORDER BY size DESC
LIMIT 10;

The following result is returned.

+-----------------+-------+
|key              | size  |
+-----------------+-------+
|hex-char.json    | 122   |
+-----------------+-------+

Call a UDF to retrieve PDF text

The workflow for extracting text from a PDF file in OSS is:

Upload the user-defined function (UDF) script and its dependency package to the MaxCompute project.
Register the UDF.
Query the Object Table, passing the object key and binary content to the UDF.

Step 1: Upload and register the UDF

Download binary_pdf2text.py and pdf_tools_cp37.tar.gz, then run the following SQL to upload the files and register the UDF in your MaxCompute project:

-- Upload files to the MaxCompute project.
ADD py binary_pdf2text.py -f;
ADD archive pdf_tools_cp37.tar.gz -f;

-- Register the UDF.
CREATE FUNCTION pdf2text AS binary_pdf2text.ExtractPDFText
  USING 'binary_pdf2text.py, pdf_tools_cp37.tar.gz' -f;

Step 2: Extract text from a PDF

The query passes two arguments to pdf2text: the object key (its path in the Object Table) and the binary content fetched by GET_DATA_FROM_OSS. For more information about GET_DATA_FROM_OSS, see Query object content for business computing.

Download demoday.pdf and upload it to the ot_openlake folder, then run:

SET odps.namespace.schema=true;
SET odps.sql.python.version=cp37;
SELECT key, size, get_json_object(
  pdf2text(
    key,
    GET_DATA_FROM_OSS("lakehouse_yunqi_demo_sz.default.ot_openlake_demo", key)
  ),'$.text.0'
) AS text
FROM ot_openlake_demo
WHERE ends_with(cast(key as string), 'demoday.pdf') = true;

The following result is returned.

+------------+------------+------------+
| key        | size       | text       |
+------------+------------+------------+
| demoday.pdf | 261567    | Simple Home Styling Simple Home Styling Easy Decorating To get started, just tap or click this placeholder text and begin typing. You can view and edit this document on your Mac, iPad, iPhone, or on...

What's next

In the DataWorks console, use the Process unstructured data based on OpenLake and Object Table example from DataWorks Gallery to explore the full capabilities of Object Table.