Todos os produtos
Search

Este produto

    MaxCompute:Funções para arquivos compactados

    Central de documentação

    MaxCompute:Funções para arquivos compactados

    Última atualização: Sep 02, 2026

    O MaxCompute oferece funções escalares integradas para analisar dados binários de arquivos compactados diretamente em SQL. É possível ler metadados e o conteúdo de formatos como zip, jar, tar, tar.gz, tar.bz2, tar.xz e tar.zst sem precisar descompactá-los em disco.

    Todos os exemplos de SQL neste tópico são instruções executáveis. Para parâmetros de entrada binária, use obrigatoriamente o literal hexadecimal completo no formato x'...'.

    Visão geral das funções

    As duas funções a seguir são funções escalares, e não funções com valor de tabela. Elas retornam um tipo de dado complexo (ARRAY / MAP) e podem ser usadas diretamente na cláusula SELECT.

    Nome da função

    Entrada

    Tipo de retorno

    Uso

    LIST_ARCHIVE_METADATA

    BINARY

    ARRAY<STRUCT<file_path:STRING, size:BIGINT, mtime:BIGINT>>

    Lista os metadados de todas as entradas de um arquivo compactado.

    GET_ARCHIVE_CONTENT

    BINARY [, STRING]

    MAP<STRING, STRING>

    Extrai um mapa de nomes de arquivo para seu respectivo conteúdo a partir de um arquivo compactado.

    Observações de uso

    • Tipo de entrada

      Ambas as funções aceitam apenas entrada do tipo BINARY. Caso seus dados estejam como STRING, faça a conversão antes.

    • Decodificação de conteúdo

      • A função GET_ARCHIVE_CONTENT retorna o conteúdo como STRING após decodificá-lo do formato binário.

      • Arquivos não textuais, como imagens ou arquivos de classe, podem apresentar caracteres ilegíveis.

    • Consumo de memória

      Essas funções carregam todo o conteúdo do arquivo compactado na memória para análise. Arquivos muito grandes podem consumir recursos significativos. Limite o tamanho de cada registro individual.

    • Detecção automática de formato

      Não é necessário especificar o formato. As funções detectam automaticamente o formato com base no conteúdo binário.

    • Correspondência de padrão apenas em nomes de arquivo

      Os curingas correspondem ao file_path das entradas no arquivo compactado, e não ao conteúdo do arquivo.

    • Entradas de diretório

      Entradas de diretório (como META-INF/) em arquivos JAR ou ZIP possuem size igual a 0 em LIST_ARCHIVE_METADATA, e seu valor é uma string vazia em GET_ARCHIVE_CONTENT.

    • Ausência de mtime

      No formato tar.gz, o campo mtime pode ser 0.

    Formatos de compactação e compressão suportados

    As funções detectam automaticamente o formato com base no conteúdo binário. Não é preciso especificá-lo.

    Formatos suportados

    Formato

    Magic number

    Contêiner

    Algoritmo de compressão

    zip (stored)

    50 4b 03 04

    ZIP

    stored (método 0)

    zip (deflate)

    50 4b 03 04

    ZIP

    deflate (método 8)

    jar

    50 4b 03 04

    ZIP

    Geralmente stored

    tar

    Sem magic number uniforme

    TAR

    Sem compressão

    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

    Como funciona a detecção de formato

    • zip / jar: Detecta o cabeçalho PK (0x504b0304). Suporta dois métodos de compressão para entradas zip: stored (método 0) e deflate (método 8). Um arquivo JAR é essencialmente um arquivo ZIP que também contém uma entrada de diretório META-INF/.

    • tar.gz / tar.bz2 / tar.xz / tar.zst: O sistema detecta primeiro o cabeçalho de compressão externo (gzip 1f8b, bzip2 425a68, xz fd377a58 ou zstd 28b52ffd) e, em seguida, descompacta e analisa o arquivo tar interno.

    • tar: Sem compressão externa. Analisa os dados diretamente com base na estrutura de blocos de 512 bytes do tar.

    LIST_ARCHIVE_METADATA

    Esta função lista os metadados de todas as entradas presentes em um arquivo compactado.

    Sintaxe

    LIST_ARCHIVE_METADATA(<binary_input>)

    Parâmetros

    binary_input: Obrigatório. O conteúdo BINARY do arquivo compactado.

    Valor de retorno

    Retorna um ARRAY: ARRAY<STRUCT<file_path:STRING, size:BIGINT, mtime:BIGINT>>. Cada elemento do array representa uma entrada no arquivo compactado, incluindo entradas de diretório. Os campos são:

    Campo

    Tipo

    Descrição

    file_path

    STRING

    Caminho relativo da entrada dentro do arquivo compactado, como hello.txt ou META-INF/MANIFEST.MF.

    size

    BIGINT

    Tamanho não compactado da entrada em bytes. Para uma entrada de diretório, este valor é 0.

    mtime

    BIGINT

    Horário da última modificação da entrada, como timestamp Unix em segundos. Em alguns casos, o valor pode ser 0.

    Ordem do array: Os elementos são retornados na mesma ordem em que aparecem no arquivo compactado.

    Exemplos

    Exemplo 1: Uso de tabela de objetos

    1. Faça upload do arquivo de dados de teste test_01.zip para o OSS.

    2. Crie uma Object Table para ler os metadados.

      -- 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  |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+

    3. Utilize uma função de arquivo compactado para listar os metadados de todas as entradas.

      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}] |
+------+

    Exemplo 2: Outros formatos

    Formato Zip

    Um arquivo zip contendo hello.txt ("hello world") e 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}]

    Formato Jar e stored

    Um arquivo jar no formato stored contendo META-INF/, META-INF/MANIFEST.MF e 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.

    Formato Tar.gz

    Um arquivo tar.gz contendo hello.txt ("hello world") e 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.

    Formato Tar

    Um arquivo tar contendo 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

    Esta função extrai um mapa de nomes de arquivo para seu respectivo conteúdo a partir de um arquivo compactado.

    Sintaxe

    -- 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>)

    Parâmetros

    Parâmetro

    Tipo

    Obrigatório

    Descrição

    binary_input

    BINARY

    Sim

    Conteúdo bruto em bytes do arquivo compactado.

    pattern

    STRING

    Não

    Padrão de correspondência de nome de arquivo no estilo glob. Se omitido, a função retorna todos os arquivos.

    Valor de retorno

    Retorna um MAP: MAP<STRING, STRING>. A chave é o nome do arquivo (file_path) e o valor é o conteúdo do arquivo, decodificado como STRING.

    • O valor para uma entrada de diretório, como META-INF/, é uma string vazia.

    • O mapa é ordenado alfabeticamente pelo nome do arquivo.

    Regras de correspondência de padrão

    Curinga

    Descrição

    Exemplo

    *

    Corresponde a qualquer quantidade de caracteres, inclusive nenhum

    *.txt → Todos os arquivos .txt

    ?

    Corresponde a um único caractere

    h?llo.txt → hello.txt

    .

    Um caractere literal

    hello.* → hello.txt, hello.csv

    Outros caracteres

    Correspondência literal

    data.csv → Apenas data.csv

    O padrão corresponde apenas aos nomes dos arquivos, não ao seu conteúdo. Se nenhum arquivo corresponder ao padrão, um mapa vazio {} será retornado.

    Exemplos

    Exemplo 1: Uso de tabela de objetos

    1. Faça upload do arquivo de dados de teste test_01.zip para o OSS.

    2. Crie uma Object Table para ler os metadados.

      -- 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  |
+------+------------+------+---------------+---------------+------+--------------+------------+--------------------+

    3. Utilize uma função de arquivo compactado para extrair um mapa de nomes de arquivo para seu respectivo conteúdo.

      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
} |
+------+

    Exemplo 2: Outros formatos

    Todos os arquivos (zip)

    SELECT GET_ARCHIVE_CONTENT(x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000');

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

    Com o padrão *.txt

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

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

    Com o padrão hello.*

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

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

    Formato Jar com *.txt

    Um arquivo jar com o padrão *.txt (filtra MANIFEST.MF e o diretório).

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

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

    Tar.gz com *.csv

    Um arquivo tar.gz com o padrão *.csv (filtra por extensão).

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

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

    Tar sem padrão

    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}

    Exceções e casos extremos

    As funções LIST_ARCHIVE_METADATA e GET_ARCHIVE_CONTENT não lançam exceções. Em vez disso, elas falham de forma controlada, retornando NULL ou uma coleção vazia:

    Cenário

    Entrada

    Valor de retorno de LIST_ARCHIVE_METADATA

    Valor de retorno de GET_ARCHIVE_CONTENT

    Dados compactados inválidos

    x'deadbeef'

    NULL

    NULL

    Entrada NULL

    NULL

    NULL

    NULL

    Binário vazio

    x''

    [] (array vazio)

    {} (mapa vazio)

    Padrão não corresponde a nenhum arquivo

    '<archive>', 'nonexistent.*'

    —

    {}

    Padrão é NULL

    '<archive>', NULL

    —

    {}

    Distinção:

    • O valor de retorno para dados não analisáveis é NULL.

    • Uma entrada vazia (x'') retorna um conjunto vazio. Isso ocorre porque um binário vazio é válido, mas não possui conteúdo, enquanto deadbeef está em um formato não reconhecido.

    Exemplos de casos extremos

    -- 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);  -- {}

    Expansão de tipos de retorno complexos

    Como as funções retornam tipos de dados complexos como ARRAY<STRUCT> e MAP<STRING,STRING>, use LATERAL VIEW EXPLODE para expandir os resultados em várias linhas. Os exemplos a seguir assumem uma tabela chamada archives(id BIGINT, content BINARY). Substitua os nomes da tabela e das colunas conforme necessário.

    Expansão dos resultados de LIST_ARCHIVE_METADATA

    -- 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;

    Expansão dos resultados de GET_ARCHIVE_CONTENT

    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;

    Filtrar por tamanho de arquivo

    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;

    Referência rápida

    Este exemplo usa um arquivo zip contendo hello.txt ("hello world") e data.txt ("test data 123"). O valor hexadecimal é x'504b030414000000000000006f5785114a0d0b0000000b0000000900000068656c6c6f2e74787468656c6c6f20776f726c64504b030414000000000000006f57ff35e1450d0000000d00000008000000646174612e74787474657374206461746120313233504b0102140314000000000000006f5785114a0d0b0000000b00000009000000000000000000000080010000000068656c6c6f2e747874504b0102140314000000000000006f57ff35e1450d0000000d000000080000000000000000000000800132000000646174612e747874504b050600000000020002006d000000650000000000':

    Objetivo

    SQL

    Resultado

    Listar metadados do arquivo

    SELECT LIST_ARCHIVE_METADATA(<zip_bin>);

    [{file_path:hello.txt, size:11, mtime:1699977600}, {file_path:data.txt, size:13, mtime:1699977600}]

    Obter todo o conteúdo do arquivo

    SELECT GET_ARCHIVE_CONTENT(<zip_bin>);

    {data.txt:test data 123, hello.txt:hello world}

    Obter apenas arquivos .txt

    SELECT GET_ARCHIVE_CONTENT(<zip_bin>, '*.txt');

    {data.txt:test data 123, hello.txt:hello world}

    Obter arquivos começando com "hello"

    SELECT GET_ARCHIVE_CONTENT(<zip_bin>, 'hello.*');

    {hello.txt:hello world}

    Nenhuma correspondência encontrada

    SELECT GET_ARCHIVE_CONTENT(<zip_bin>, 'nonexistent.*');

    {}