Common UDFs for Object Tables (Java)

This document describes common Java UDFs used to extract metadata and identify the format of unstructured files in MaxCompute Object Tables. It includes function descriptions and usage steps.

In AI data processing, extracting metadata and preprocessing unstructured data, such as images, audio, video, PDFs, and Office documents, is a typical use case for Object Tables. Before unstructured data stored in Object Storage Service (OSS) can be used in processes like model training, content understanding, or retrieval-augmented generation (RAG), you often need to extract file metadata in batches. For example:

Extract dimensions, format, and EXIF information from images.
Extract the duration, sample rate, encoding format, and keyframes from audio and video files.
Parse the page count, author, and creation time from PDF or Word documents.

Through its Object Table feature, MaxCompute provides the following Java UDFs to perform metadata extraction and lightweight preprocessing on files in object storage directly within SQL. The extracted results can be used to build structured metadata tables, filter or sample datasets, and provide input for downstream tasks like feature engineering and vectorization.

Download the Java UDF JAR package

Download the Java UDF JAR package: lakehouse-udf-1.0-SNAPSHOT.jar

AI-UDF list

GetFileMeta

The GetFileMeta function extracts metadata from a file. It returns a Map<String, String>.

Supported file formats
Type
Supported formats
Image
jpg, jpeg, png, bmp, gif, tif, tiff
Audio/Video
mp3, mp4, avi, wav
Office Document
pdf, doc, docx, ppt, pptx, xls, xlsx

Signatures

evaluate(Binary data, String fileFormat)
evaluate(Binary data, String fileFormat, String metadataKeyDelimiter)
evaluate(Binary data, String fileFormat, String metadataKeyDelimiter, String parseFailPolicy)
evaluate(Binary data, String fileFormat, Map<String, String> options)

Parameters

Parameter	Type	Default	Description
`metadataKeyDelimiter`	String	`"."`	The delimiter for hierarchical metadata key names. Use this parameter if your metadata has a hierarchical structure. For image files, this acts as a delimiter between the metadata directory and tag. For example: Default: `"EXIF.DateTimeOriginal"` Custom`/`: `"EXIF/DateTimeOriginal"`
`password`	String	`null`	The password for the encrypted PDF file. This applies only to the `pdf` format. Other encrypted files, such as encrypted Office documents, are not supported.
`parseFailPolicy`	String	`"OUTPUT_NULL"`	The policy for handling parsing failures. Valid values are: OUTPUT_DEFAULT: Silently returns null. OUTPUT_NULL: Silently returns null. WARN_AND_NULL: Outputs a warning log and then returns null. THROW_EXCEPTION: Throws an exception and interrupts the process.
`metadataKeyExclusionRegex`	String	`null`	A regular expression filter for metadata keys. It removes metadata items whose keys match the regular expression. Separate multiple patterns with commas. For example: `"Photoshop."` (removes entries whose keys start with `Photoshop`) `".Path Info.*"` (removes entries whose keys contain the substring `Path Info`)

GetFileFormat

GetFileFormat extracts the detailed format of a file, such as json, csv, orc, or parquet. The return type is String.

Signatures
- Usage 1: Extract the format from a file name
```
evaluate(String fileName, String parseFailPolicy)
```
- Usage 2: Extract the format from binary data and a file name
```
evaluate(Binary data, String fileName, String parseFailPolicy)
```
Parameters
parseFailPolicy: The policy for handling parsing failures. The data type is String. The default value is "OUTPUT_NULL". The valid values are as follows:
- OUTPUT_DEFAULT: Silently returns null.
- OUTPUT_NULL: Silently returns null.
- WARN_AND_NULL: Outputs a warning log and then returns null.
- THROW_EXCEPTION: Throws an exception and interrupts the process.

GetFileMediaType

GetFileMediaType detects the top-level MIME media type of a file based on the IANA registry. The return type is String.

Supported media types:
- application
- audio
- example
- font
- haptics
- image
- message
- model
- multipart
- text
- video
Signatures
- Usage 1: Detect the media type from a file name
```
evaluate(String fileName, String parseFailPolicy)
```
- Usage 2: Detect the media type from binary data and a file name
```
evaluate(Binary data, String fileName, String parseFailPolicy)
```
Parameters
parseFailPolicy: The policy for handling parsing failures. The data type is String. The default value is "OUTPUT_NULL". The valid values are as follows:
- OUTPUT_DEFAULT: Silently returns null.
- OUTPUT_NULL: Silently returns null.
- WARN_AND_NULL: Outputs a warning log and then returns null.
- THROW_EXCEPTION: Throws an exception and interrupts the process.

GetFileContentType

GetFileContentType returns the complete MIME content type (Content-Type) of a file. The return type is String.

Signatures
- Usage 1: Detect the content type from a file name
```
evaluate(String fileName, String parseFailPolicy)
```
- Usage 2: Detect the content type from binary data and a file name
```
evaluate(Binary data, String fileName, String parseFailPolicy)
```
Parameters
parseFailPolicy: The policy for handling parsing failures. The data type is String. The default value is "OUTPUT_NULL". The valid values are as follows:
- OUTPUT_DEFAULT: Silently returns null.
- OUTPUT_NULL: Silently returns null.
- WARN_AND_NULL: Outputs a warning log and then returns null.
- THROW_EXCEPTION: Throws an exception and interrupts the process.

GetFileName

The GetFileName function extracts the file name from a file path. The return type is String.

Signature

evaluate(String filePath, String parseFailPolicy)

Parameters
parseFailPolicy: The policy for handling parsing failures. The data type is String. The default value is "OUTPUT_NULL". The valid values are as follows:
- OUTPUT_DEFAULT: Silently returns null.
- OUTPUT_NULL: Silently returns null.
- WARN_AND_NULL: Outputs a warning log and then returns null.
- THROW_EXCEPTION: Throws an exception and interrupts the process.

Usage examples

Prepare data
Log in to the OSS console and upload your data. This example uses the following test data: object_table_demo_files.zip.

Upload the UDF JAR package and create UDFs

Download the Java UDF JAR package: lakehouse-udf-1.0-SNAPSHOT.jar

This example uses odpscmd. For other methods of uploading a JAR package, see Java UDFs.

-- Enter the odpscmd environment.
SET odps.sql.allow.namespace.schema=true;
SET odps.namespace.schema=true;
SET odps.sql.type.system.odps2=true;

-- Create an Object Table.
DROP TABLE IF EXISTS udf_demo;
CREATE OBJECT TABLE udf_demo 
LOCATION 'oss://<endpoint>/<bucket>/path/to/files/'; -- Replace with the OSS location of your uploaded files.
-- Refresh the Object Table metadata.
ALTER TABLE udf_demo refresh metadata;

-- Upload the UDF JAR package and then create the functions.
add jar /path/to/lakehouse-udf-1.0-SNAPSHOT.jar;  -- Replace with the absolute path to your local UDF JAR package.
create function get_file_name as 'com.aliyun.odps.lakehouse.udf.GetFileName' using 'lakehouse-udf-1.0-SNAPSHOT.jar';
create function get_file_media_type as 'com.aliyun.odps.lakehouse.udf.GetFileMediaType' using 'lakehouse-udf-1.0-SNAPSHOT.jar';
create function get_file_content_type as 'com.aliyun.odps.lakehouse.udf.GetFileContentType' using 'lakehouse-udf-1.0-SNAPSHOT.jar';
create function get_file_format as 'com.aliyun.odps.lakehouse.udf.GetFileFormat' using 'lakehouse-udf-1.0-SNAPSHOT.jar';
create function get_file_meta as 'com.aliyun.odps.lakehouse.udf.GetFileMeta' using 'lakehouse-udf-1.0-SNAPSHOT.jar';

Use functions in an Object Table

set odps.sql.allow.namespace.schema=true;
set odps.namespace.schema=true;
set odps.sql.type.system.odps2=true;

-- Query the file content type and file media type.
select 
  key, 
  get_file_content_type(key, "OUTPUT_DEFAULT"), 
  get_file_media_type(key, "OUTPUT_DEFAULT") 
from udf_demo;
-- Result:
+------------+------------+------------+
| key        | _c1        | _c2        | 
+------------+------------+------------+
| lancePaper.pdf | application/pdf | application | 
| sample.avi | video/x-msvideo | video      | 
| sample.doc | application/msword | application | 
| sample.gif | image/gif  | image      | 
| sample.jpeg | image/jpeg | image      | 
| sample.jpg | image/jpeg | image      | 
| sample.mp3 | audio/mpeg | audio      | 
| sample.mp4 | video/mp4  | video      | 
| sample.png | image/png  | image      | 
| sample.ppt | application/vnd.ms-powerpoint | application | 
| sample.wav | audio/vnd.wave | audio      | 
| sample.xls | application/vnd.ms-excel | application | 
+------------+------------+------------+

-- Query the file format and metadata (assuming all file names have a format suffix, such as "xxx.pdf").
select 
  key,
  get_file_format(key, 'OUTPUT_DEFAULT'), 
  get_file_meta(
    -- The get_data_from_oss function gets the file content from OSS.
    get_data_from_oss('three_tier_model.default.udf_demo', key), -- File content in binary format.
    get_file_format(key, 'OUTPUT_DEFAULT')  -- File type, such as pdf, mp3, avi, or jpg.
  )
from udf_demo;

FAQ

Handling Java class conflict errors

Error message

java.lang.LinkageError: loader constraint violation: 
  when resolving overridden method "org.apache.xerces.jaxp.SAXParserImpl.parse(Lorg/xml/sax/InputSource;Lorg/xml/sax/helpers/DefaultHandler;)V" 
  the class loader (instance of com/aliyun/odps/udf/impl/utils/OdpsUserCodeLoaderFactory$ChildFirstLoader) of the current class, org/apache/xerces/jaxp/SAXParserImpl, 
  and its superclass loader (instance of <bootloader>), have different Class objects for the type org/xml/sax/helpers/DefaultHandler used in the signature
    at org.apache.xerces.jaxp.SAXParserFactoryImpl.newSAXParser(Unknown Source)
    at org.apache.tika.mime.MimeTypesReader.newSAXParser(MimeTypesReader.java:223)

Cause
The UDF JAR package provided in this document can cause class conflicts with other resources in your project, such as existing Java UDFs.
Solution
Create a new schema, upload the UDF JAR package to it, and then create and use the UDF. If the issue persists, create a new project before using the function.

Type	Supported formats
Image	jpg, jpeg, png, bmp, gif, tif, tiff
Audio/Video	mp3, mp4, avi, wav
Office Document	pdf, doc, docx, ppt, pptx, xls, xlsx