Archive file functions - MaxCompute - Alibaba Cloud Documentation Center

MaxCompute introduces built-in scalar functions to parse the binary data of archive files directly in SQL. You can read the file metadata and content of formats such as zip, jar, tar, tar.gz, tar.bz2, tar.xz, and tar.zst without decompressing them to disk.

All SQL examples in this topic are runnable statements. For binary input parameters, you must use the complete x'...' hex literal.

Function overview

The following two functions are scalar functions, not table-valued functions. They return a complex data type (ARRAY / MAP) and can be used directly in the SELECT clause.

Function name	Input	Return type	Usage
`LIST_ARCHIVE_METADATA`	`BINARY`	`ARRAY<STRUCT<file_path:STRING, size:BIGINT, mtime:BIGINT>>`	Lists the metadata of all entries in an archive file.
`GET_ARCHIVE_CONTENT`	`BINARY` [, `STRING`]	`MAP<STRING, STRING>`	Extracts a map of filenames to their content from an archive file.

Usage notes

Input type
Both functions accept only BINARY input. If your data is a STRING, you must first cast it.
Content decoding
- GET_ARCHIVE_CONTENT returns the content as a STRING by decoding it from binary.
- Non-text files, such as images or class files, may appear garbled.
Memory usage
The functions read the entire content of an archive file into memory for parsing. Very large archive files can consume significant resources. Limit the size of a single record.
Automatic format detection
You do not need to specify the format. The functions automatically detect the format based on the binary content.
Pattern matching on filenames only
Wildcards match the file_path of entries in the archive file, not the file content.
Directory entries
Directory entries (such as META-INF/) in JAR or ZIP files have a size of 0 in LIST_ARCHIVE_METADATA, and their value is an empty string in GET_ARCHIVE_CONTENT.
Missing mtime
For the tar.gz format, the mtime may be 0.

Supported archive and compression formats

The functions automatically detect the format based on the binary content. You do not need to specify it.

Supported formats

Format	Magic number	Container	Compression algorithm
zip (stored)	`50 4b 03 04`	ZIP	stored (method 0)
zip (deflate)	`50 4b 03 04`	ZIP	deflate (method 8)
jar	`50 4b 03 04`	ZIP	Usually stored
tar	No uniform magic number	TAR	No compression
tar.gz	`1f 8b 08`	TAR	gzip
tar.bz2	`42 5a 68` (`BZh`)	TAR	bzip2
tar.xz	`fd 37 7a 58 5a`	TAR	xz
tar.zst	`28 b5 2f fd`	TAR	zstd

How format detection works

zip / jar: Detects the PK header (0x504b0304). It supports two compression methods for zip entries: stored (method 0) and deflate (method 8). A JAR file is essentially a ZIP file that also contains a META-INF/ directory entry.
tar.gz / tar.bz2 / tar.xz / tar.zst: The system first detects the outer compression header (gzip 1f8b, bzip2 425a68, xz fd377a58, or zstd 28b52ffd), and then decompresses and parses the inner tar archive.
tar: No outer compression. Parses the data directly based on the tar 512-byte block structure.

LIST_ARCHIVE_METADATA

This function lists the metadata of all entries in an archive file.

Syntax

LIST_ARCHIVE_METADATA(<binary_input>)

Parameters

binary_input: Required. The BINARY content of the archive file.

Return value

Returns an ARRAY: ARRAY<STRUCT<file_path:STRING, size:BIGINT, mtime:BIGINT>>. Each element in the array represents an entry in the archive file, including directory entries. The fields are:

Field	Type	Description
`file_path`	`STRING`	The relative path of the entry within the archive file, such as `hello.txt` or `META-INF/MANIFEST.MF`.
`size`	`BIGINT`	The uncompressed size of the entry in bytes. For a directory entry, this value is `0`.
`mtime`	`BIGINT`	The modification time of the entry, a Unix timestamp in seconds. In some cases, the value may be `0`.

Array order: The elements are returned in the order they appear in the archive file.

Examples

Example 1: Using an object table

Upload the test data file test_01.zip to OSS.

Create an Object Table to read the metadata.

-- Create an Object Table
SET odps.namespace.schema=true; 

CREATE OBJECT TABLE ot_test_01 
WITH serdeproperties ( 
 'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole') 
LOCATION 'oss://oss-<region>-internal.aliyuncs.com/<oss-path>/'
;

-- Refresh metadata
ALTER TABLE ot_test_01 REFRESH METADATA;

-- Query the Object Table
SET odps.namespace.schema=true;
SELECT * FROM ot_test_01;

-- The following result is returned:
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+
| key  | size       | type | last_modified | storage_class | etag | restore_info | owner_id   | owner_display_name |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+
| test_01.zip | 7246 | Normal | 2026-08-31 06:40:38 | Standard | 337E*****3101C02F1 | NONE| 122***014 | 122***014  |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+

Use an archive function to list the metadata of all entries.

SET odps.namespace.schema=true;

SELECT 
    LIST_ARCHIVE_METADATA(data) FROM 
(
    SELECT 
        GET_DATA_FROM_OSS('<project>.<schema>.ot_test_01', key) as data
    FROM ot_test_01
);

-- The following result is returned:
+------+
| _c0  |
+------+
| [{file_path:test_01/, size:0, mtime:1786418145}, {file_path:test_01/hello.txt, size:13, mtime:1786418039}, {file_path:__MACOSX/test_01/._hello.txt, size:5106, mtime:1786418039}, {file_path:test_01/data.txt, size:13, mtime:1786418061}, {file_path:__MACOSX/test_01/._data.txt, size:5122, mtime:1786418061}] |
+------+

Example 2: Other formats

Zip format

A zip file that contains hello.txt ("hello world") and data.txt ("test data 123").

SELECT LIST_ARCHIVE_METADATA(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000');

-- The following result is returned:
[{file_path:hello.txt, size:11, mtime:1699977600}, {file_path:data.txt, size:13, mtime:1699977600}]

Jar and stored format

A jar file in stored format that contains META-INF/, META-INF/MANIFEST.MF, and hello.txt ("hello jar").

SELECT LIST_ARCHIVE_METADATA(x'504b03040a0000080000f4a1bc5c000000000000000000000000090004004d4554412d494e462ffeca0000504b03040a0000080000f4a1bc5c6388d7f13a0000003a000000140000004d4554412d494e462f4d414e49464553542e4d464d616e69666573742d56657273696f6e3a20312e300d0a437265617465642d42793a20312e382e305f3334352028416c6962616261290d0a0d0a504b03040a000008000014a0bc5cdf3b649609000000090000000900000068656c6c6f2e74787468656c6c6f206a6172504b01020a000a0000080000f4a1bc5c0000000000000000000000000900040000000000000000000000000000004d4554412d494e4e462ffeca0000504b01020a000a0000080000f4a1bc5c6388d7f13a0000003a00000014000000000000000000000000002b0000004d4554412d494e462f4d414e49464553542e4d46504b01020a000a000008000014a0bc5cdf3b6496090000000900000009000000000000000000000000009700000068656c6c6f2e747874504b05060000000003000300b4000000c70000000000');

-- The following result is returned:
[{file_path:META-INF/, size:0, mtime:1779970540}, {file_path:META-INF/MANIFEST.MF, size:58, mtime:1779970540}, {file_path:hello.txt, size:9, mtime:1779969640}]

-- Note: The jar file contains a META-INF/ directory entry, which has a size of 0.

Tar.gz format

A tar.gz file that contains hello.txt ("hello world") and data.csv ("test data 123").

SELECT LIST_ARCHIVE_METADATA(x'1f8b08000000000002ffedd3410a83400c85e1394a4e20998edaf30c5570312038b1edf1eb74d3e2da5ac4ffdb2464179237f4298d953dcdfd8e2edaba7ed7c5baaafaf0e9cbdc7bd5e044dd0ee66c7112d964c9efe50e6228f797c738a5cee17cba68b1bae5bbfb6bfe9b55feb569afe47f0fd66793f204e22f81380000000000000000000000001cce0b293b365100280000');

-- The following result is returned:
[{file_path:hello.txt, size:11, mtime:0}, {file_path:data.csv, size:13, mtime:0}]

-- Note: The mtime for a tar.gz file may be 0, depending on the time field in the inner TAR header.

Tar format

A tar file that contains hello.txt ("hello world").

SELECT LIST_ARCHIVE_METADATA(x'68656c6c6f2e7478740000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000030303030363434003030303030303000303030303030300030303030303030303031330031343532343737303430300030303736343500203000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000757374617200303000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0-000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');

-- The following result is returned:
[{file_path:hello.txt, size:11, mtime:1700000000}]

GET_ARCHIVE_CONTENT

This function extracts a map of filenames to their content from an archive file.

Syntax

-- Without a pattern: returns the content of all files in the archive
GET_ARCHIVE_CONTENT(<binary_input>)

-- With a pattern: returns only the content of files whose names match the pattern
GET_ARCHIVE_CONTENT(<binary_input>, <pattern>)

Parameters

Parameter	Type	Required	Description
`binary_input`	`BINARY`	Yes	The raw byte content of the archive file.
`pattern`	`STRING`	No	A glob-style filename matching pattern. If omitted, the function returns all files.

Return value

Returns a MAP: MAP<STRING, STRING>. The key is the filename (file_path), and the value is the file content, decoded as a STRING.

The value for a directory entry, such as META-INF/, is an empty string.
The map is sorted alphabetically by filename.

Pattern matching rules

Wildcard	Description	Example
`*`	Matches any number of characters, including none	`*.txt` → All .txt files
`?`	Matches a single character	`h?llo.txt` → hello.txt
`.`	A literal character	`hello.*` → hello.txt, hello.csv
Other characters	Literal match	`data.csv` → Only data.csv

The pattern only matches filenames, not file content. If no files match the pattern, an empty map {} is returned.

Examples

Example 1: Using an object table

Upload the test data file test_01.zip to OSS.

Create an Object Table to read the metadata.

-- Create an Object Table
SET odps.namespace.schema=true; 

CREATE OBJECT TABLE ot_test_01 
WITH serdeproperties ( 
 'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole') 
LOCATION 'oss://oss-<region>-internal.aliyuncs.com/<oss-path>/'
;

-- Refresh metadata
ALTER TABLE ot_test_01 REFRESH METADATA;

-- Query the Object Table
SET odps.namespace.schema=true;
SELECT * FROM ot_test_01;

-- The following result is returned:
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+
| key  | size       | type | last_modified | storage_class | etag | restore_info | owner_id   | owner_display_name |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+
| test_01.zip | 7246 | Normal | 2026-08-31 06:40:38 | Standard | 337E*****3101C02F1 | NONE| 122***014 | 122***014  |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+

Use an archive function to extract a map of filenames to their content.

SET odps.namespace.schema=true;

SELECT 
    GET_ARCHIVE_CONTENT(data,'test_01/hello.*') FROM 
(
    SELECT 
        GET_DATA_FROM_OSS('<project>.<schema>.ot_test_01', key) as data
    FROM ot_test_01
);

-- The following result is returned:
+------+
| _c0  |
+------+
| {test_01/hello.txt:hello world
} |
+------+

Example 2: Other formats

All files (zip)

SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000');

-- The following result is returned:
{data.txt:test data 123, hello.txt:hello world}

With `*.txt` pattern

SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000', '*.txt');

-- The following result is returned:
{data.txt:test data 123, hello.txt:hello world}

With `hello.*` pattern

SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000', 'hello.*');

-- The following result is returned:
{hello.txt:hello world}

Jar format with `*.txt`

A jar file with *.txt (filters out MANIFEST.MF and the directory).

SELECT GET_ARCHIVE_CONTENT(x'504b03040a0000080000f4a1bc5c000000000000000000000000090004004d4554412d494e462ffeca0000504b03040a0000080000f4a1bc5c6388d7f13a0000003a000000140000004d4554412d494e462f4d414e49464553542e4d464d616e69666573742d56657273696f6e3a20312e300d0a437265617465642d42793a20312e382e305f3334352028416c6962616261290d0a0d0a504b03040a000008000014a0bc5cdf3b649609000000090000000900000068656c6c6f2e74787468656c6c6f206a6172504b01020a000a0000080000f4a1bc5c0000000000000000000000000900040000000000000000000000000000004d4554412d494e462ffeca0000504b01020a000a0000080000f4a1bc5c6388d7f13a0000003a00000014000000000000000000000000002b0000004d4554412d494e462f4d414e49464553542e4d46504b01020a000a000008000014a0bc5cdf3b6496090000000900000009000000000000000000000000009700000068656c6c6f2e747874504b05060000000003000300b4000000c70000000000', '*.txt');

-- The following result is returned:
{hello.txt:hello jar}

Tar.gz with `*.csv`

A tar.gz file with *.csv (filters by extension).

SELECT GET_ARCHIVE_CONTENT(x'1f8b08000000000002ffedd3410a83400c85e1394a4e20998edaf30c5570312038b1edf1eb74d3e2da5ac4ffdb2464179237f4298d953dcdfd8e2edaba7ed7c5baaafaf0e9cbdc7bd5e044dd0ee66c7112d964c9efe50e6228f797c738a5cee17cba68b1bae5bbfb6bfe9b55feb569afe47f0fd66793f204e22f81380000000000000000000000001cce0b293b365100280000', '*.csv');

-- The following result is returned:
{data.csv:test data 123}

Tar without pattern

SELECT GET_ARCHIVE_CONTENT(x'68656c6c6f2e74787400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000303030303634340030303030303030003030303030303000303030303030303030313300313435323437373034303000303037363435002030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000007573417200303000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0-000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0-000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0-000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0-000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0-00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000-0.o');

-- The following result is returned:
{hello.txt:hello world}

Exceptions and edge cases

The LIST_ARCHIVE_METADATA and GET_ARCHIVE_CONTENT functions do not throw exceptions. Instead, they fail gracefully by returning NULL or an empty collection:

Scenario	Input	LIST_ARCHIVE_METADATA return value	GET_ARCHIVE_CONTENT return value
Invalid archive data	`x'deadbeef'`	`NULL`	`NULL`
NULL input	`NULL`	`NULL`	`NULL`
Empty binary	`x''`	`[]` (empty array)	`{}` (empty map)
Pattern does not match any file	`'<archive>', 'nonexistent.*'`	—	`{}`
Pattern is NULL	`'<archive>', NULL`	—	`{}`

Distinction:

The return value for unparsable data is NULL.
An empty input (x'') returns an empty set. This is because an empty binary is valid but has no content, while deadbeef is in an unrecognized format.

Edge case examples

-- Invalid data → NULL
SELECT LIST_ARCHIVE_METADATA(x'deadbeef');        -- \N
SELECT GET_ARCHIVE_CONTENT(x'deadbeef');          -- \N

-- NULL input → NULL
SELECT LIST_ARCHIVE_METADATA(NULL);               -- \N
SELECT GET_ARCHIVE_CONTENT(NULL);                 -- \N

-- Empty binary → Empty collection
SELECT LIST_ARCHIVE_METADATA(x'');                -- []
SELECT GET_ARCHIVE_CONTENT(x'');                  -- {}

-- Pattern does not match → Empty Map
SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000', 'nonexistent.*');  -- {}

-- Pattern is NULL → Empty Map
SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000', NULL);  -- {}

Expanding complex return types

Because the functions return complex data types like ARRAY<STRUCT> and MAP<STRING,STRING>, you can use LATERAL VIEW EXPLODE to expand the results into multiple rows. The following examples assume a table named archives(id BIGINT, content BINARY). Replace the table and column names as needed.

Expanding LIST_ARCHIVE_METADATA results

-- Assume the table archives(id BIGINT, content BINARY) stores archive data
SELECT id, meta.file_path, meta.size, meta.mtime
FROM archives
LATERAL VIEW EXPLODE(LIST_ARCHIVE_METADATA(content)) t AS meta
WHERE id = 1;

Expanding GET_ARCHIVE_CONTENT results

SELECT id, k AS file_name, v AS file_content
FROM archives
LATERAL VIEW EXPLODE(GET_ARCHIVE_CONTENT(content, '*.txt')) t AS k, v
WHERE id = 1;

Filter by file size

SELECT id, meta.file_path, meta.size
FROM archives
LATERAL VIEW EXPLODE(LIST_ARCHIVE_METADATA(content)) t AS meta
WHERE id = 1 AND meta.size > 10;

Quick reference

This example uses a zip archive that contains hello.txt ("hello world") and data.txt ("test data 123"). The hex value is x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000':

Objective	SQL	Result
List file metadata	SELECT LIST_ARCHIVE_METADATA(<zip_bin>);	[{file_path:hello.txt, size:11, mtime:1699977600}, {file_path:data.txt, size:13, mtime:1699977600}]
Get all file content	SELECT GET_ARCHIVE_CONTENT(<zip_bin>);	{data.txt:test data 123, hello.txt:hello world}
Get only .txt files	SELECT GET_ARCHIVE_CONTENT(<zip_bin>, '*.txt');	{data.txt:test data 123, hello.txt:hello world}
Get files starting with "hello"	SELECT GET_ARCHIVE_CONTENT(<zip_bin>, 'hello.*');	{hello.txt:hello world}
No match found	SELECT GET_ARCHIVE_CONTENT(<zip_bin>, 'nonexistent.*');	{}

Function overview

Usage notes

Supported archive and compression formats

Supported formats

How format detection works

LIST_ARCHIVE_METADATA

Syntax

Parameters

Return value

Examples

Example 1: Using an object table

Example 2: Other formats

Zip format

Jar and stored format

Tar.gz format

Tar format

GET_ARCHIVE_CONTENT

Syntax

Parameters

Return value

Pattern matching rules

Examples

Example 1: Using an object table

Example 2: Other formats

All files (zip)

With *.txt pattern

With hello.* pattern

Jar format with *.txt

Tar.gz with *.csv

Tar without pattern

Exceptions and edge cases

Edge case examples

Expanding complex return types

Expanding LIST_ARCHIVE_METADATA results

Expanding GET_ARCHIVE_CONTENT results

Filter by file size

Quick reference

With `*.txt` pattern

With `hello.*` pattern

Jar format with `*.txt`

Tar.gz with `*.csv`