MaxCompute:GET_BLOB_FROM_OSS

blob GET_BLOB_FROM_OSS(
  STRING <full_object_table_name>,
  STRING <key>
  [, BIGINT <offset>]
  [, BIGINT <length>]
  [, STRING <object_not_found_policy>]
)

GET_BLOB_FROM_OSS(table_name, key)
GET_BLOB_FROM_OSS(table_name, key, object_not_found_policy)
GET_BLOB_FROM_OSS(table_name, key, offset, length)
GET_BLOB_FROM_OSS(table_name, key, offset, length, object_not_found_policy)

SET odps.namespace.schema=true;
SELECT CAST(
         READ_BLOB(
           GET_BLOB_FROM_OSS(
             '<project_name>.<schema_name>.ot_demo',
             key
           )
         ) AS STRING
       ) AS object_text
FROM ot_demo
WHERE key = 'example.txt';

Preparar os dados

Etapa 1: Gerar um blob para cada objeto do OSS

-- Enable data type 2.0.
SET odps.sql.type.system.odps2=true;
-- Enable three-level syntax for project.schema.table.
SET odps.namespace.schema=true;
-- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
SET odps.mcqa.disable=true;

SELECT
  key,
  GET_BLOB_FROM_OSS(
    '<project>.<schema>.txt_demo',
    key
  ) AS object_blob
FROM txt_demo limit 1;

-- This is equivalent to the following syntax:

-- Enable data type 2.0.
SET odps.sql.type.system.odps2=true;
-- Enable three-level syntax for project.schema.table.
SET odps.namespace.schema=true;
-- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
SET odps.mcqa.disable=true;

SELECT
  key,
    GET_BLOB_FROM_OSS(
        '<project>.<schema>.txt_demo',
        key,
        0,
        -1,
        'OUTPUT_NULL'
    ) AS object_blob
FROM txt_demo limit 1;

-- The following result is returned:
+------+-------------+
| key  | object_blob |
+------+-------------+
| test_travel01.txt | CAEQAxrgAgEAAAANAAAAAAAAAAEAAAAAAAAADQAAAAAAAABYAAAAXAAAAGQAAABsAAAAWAEAAFgBAABYAQAAWAEAAFgBAABgAQAAYAEAAGABAABgAQAADwEAAAAAAAAAAAAApAAAAAAAAAAAAAAAAAAAAOQAAAAAAAAAcGFuZ3U6Ly9PRFBTLUNOLUJFSUpJTkctSDoxMDI0MC9wcm9kdWN0L2FsaXl1bi9vZHBzL3l5eV90ZXN0X2Rldi8uRnV4aUpvYlRlbXBSb290LzIwMjYwODA0L0FkaG9jU2luay8yMDI2MDgwNDA5MTcyMzg4NWdqZXZsNmtoNXplNS9jb25zb2xlX3F1ZXJ5X3Rhc2tfMTc4NTgzNTA0MzQxNjBfMC8xLy1leHQtLzIwMjYwODA0MDkxNzIzODg1Z2pldmw2a2g1emU1X00xXzBfMF8wMDAwMDBfY2lkMS5ibG9iAQAAAAAAAAAiACoUsPTTV9LIq38UBOcOFkMuEmqWFngyGQoJbnMtZGlyZWN0Egx5eXlfdGVzdF9kZXY6HTIwMjYwODA0MDkxNzIzODg1Z2pldmw2a2g1emU1 |
+------+-------------+

Etapa 2: Usar a pseudocoluna _blob como abreviação

A _blob não é uma coluna real de metadados da tabela de objetos; portanto, SELECT * não a inclui automaticamente. Referencie-a explicitamente. Para definir offset, length, política de ausência ou tipo de conteúdo, use a chamada completa da função.

-- Enable data type 2.0.
SET odps.sql.type.system.odps2=true;
-- Enable three-level syntax for project.schema.table.
SET odps.namespace.schema=true;
-- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
SET odps.mcqa.disable=true;

SELECT key, _blob FROM txt_demo limit 1;

-- This is equivalent to the following syntax:

-- Enable data type 2.0.
SET odps.sql.type.system.odps2=true;
-- Enable three-level syntax for project.schema.table.
SET odps.namespace.schema=true;
-- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
SET odps.mcqa.disable=true;

SELECT
  key,
  GET_BLOB_FROM_OSS(
    '<project>.<schema>.txt_demo',
    key
  ) AS `_blob`
FROM txt_demo limit 1;

-- The following result is returned:
+------+-------+
| key  | _blob |
+------+-------+
| test_travel01.txt | CAEQAxreAgEAAAANAAAAAAAAAAEAAAAAAAAADQAAAAAAAABYAAAAXAAAAGQAAABsAAAAVgEAAFYBAABWAQAAVgEAAFYBAABeAQAAXgEAAF4BAABeAQAADwEAAAAAAAAAAAAApAAAAAAAAAAAAAAAAAAAAOIAAAAAAAAAcGFuZ3U6Ly9PRFBTLUNOLUJFSUpJTkctSDoxMDI0MC9wcm9kdWN0L2FsaXl1bi9vZHBzL3l5eV90ZXN0X2Rldi8uRnV4aUpvYlRlbXBSb290LzIwMjYwODA0L0FkaG9jU2luay8yMDI2MDgwNDA5MzAyNzk2NWc4M2FpamkyNjVuL2NvbnNvbGVfcXVlcnlfdGFza18xNzg1ODM1ODI3NDA1MF8wLzEvLWV4dC0vMjAyNjA4MDQwOTMwMjc5NjVnODNhaWppMjY1bl9NMV8wXzBfMDAwMDAwX2NpZDEuYmxvYgEAAAAAAAAAIgAqFA2jSV9PtAiPH3AM0+59uTImxTmQMhkKCW5zLWRpcmVjdBIMeXl5X3Rlc3RfZGV2OhwyMDI2MDgwNDA5MzAyNzk2NWc4M2FpamkyNjVu |
+------+-------+

Etapa 3: Ler uma parte do conteúdo do objeto

1. O exemplo a seguir referencia até 64 bytes a partir do byte 0 de cada objeto.

   -- Enable data type 2.0.
   SET odps.sql.type.system.odps2=true;
   -- Enable three-level syntax for project.schema.table.
   SET odps.namespace.schema=true;
   -- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
   SET odps.mcqa.disable=true;
   
   SELECT
     key,
     read_blob(  
       GET_BLOB_FROM_OSS(
         '<project>.<schema>.txt_demo',
         key,
         0,
         64
     ))AS object_segment
   FROM txt_demo limit 1;
   
   -- The following result is returned:
   +------+----------------+
   | key  | object_segment |
   +------+----------------+
   | test_travel01.txt | =E5=8C=97=E4=BA=AC=C2=B7=E6=95=85=E5=AE=AB=EF=BC=88=E5=8E=86=E5=8F=B2=E5=8F=A4=E8=BF=B9=EF=BC=89=EF=BC=9A=E7=BA=A2=E5=A2=99=E9=BB=84=E7=93=A6=E9=97=B4=E6=B5=81=E6=B7=8C=E7=9D=80=E5=85=AD=E7=99 |
   +------+----------------+

2. Ao ler este blob, a função read_blob opera dentro do intervalo de bytes especificado. Também é possível extrair um subsegmento deste blob.

   SET odps.sql.type.system.odps2=true;
   -- Enable three-level syntax for project.schema.table.
   SET odps.namespace.schema=true;
   -- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
   SET odps.mcqa.disable=true;
   
   SELECT read_blob(
            GET_BLOB_FROM_OSS(
              '<project>.<schema>.txt_demo',
              key,
              0,
              64
            ),
            0,
            32
          ) AS first_32_bytes_of_segment
   FROM txt_demo limit 1;
   
   -- The following result is returned:
   +---------------------------+
   | first_32_bytes_of_segment |
   +---------------------------+
   | =E5=8C=97=E4=BA=AC=C2=B7=E6=95=85=E5=AE=AB=EF=BC=88=E5=8E=86=E5=8F=B2=E5=8F=A4=E8=BF=B9=EF=BC=89 |
   +---------------------------+

Etapa 4: Definir o tipo de conteúdo e visualizar os metadados do blob

O objeto JSON retornado pela função GET_BLOB_METADATA contém campos como content_type, content_length, storage_type, checksum, and content_encoding.

Para valores gerados por GET_BLOB_FROM_OSS, o storage_type é OSS. Se você não passar explicitamente um content_type, o campo content_type nos metadata será NULL.

-- Enable data type 2.0.
SET odps.sql.type.system.odps2=true;
-- Enable three-level syntax for project.schema.table.
SET odps.namespace.schema=true;
-- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
SET odps.mcqa.disable=true;

SELECT GET_BLOB_METADATA(
         GET_BLOB_FROM_OSS(
           '<project>.<schema>.pdf_demo',
           key,
           'THROW_EXCEPTION',
           'application/pdf'
         )
       ) AS blob_metadata
FROM pdf_demo
WHERE key LIKE '%.pdf' limit 1;

-- The following result is returned:
+---------------+
| blob_metadata |
+---------------+
| {"checksum":null,"content_type":"application/pdf","storage_type":"OSS","is_compressed":false,"content_length":261567,"content_encoding":null,"blob_storage_tier":"STANDARD"} |
+---------------+

Etapa 5: Gravar em uma tabela blob

1. Para armazenar resultados para uso a longo prazo ou reutilização em tarefas subsequentes, grave-os em uma tabela compatível com o tipo de dado blob. Para mais informações, consulte Blob data type and multi-modal storage.

   -- Enable data type 2.0.
   SET odps.sql.type.system.odps2=true;
   -- Enable three-level syntax for project.schema.table.
   SET odps.namespace.schema=true;
   -- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
   SET odps.mcqa.disable=true;
   
   CREATE TABLE IF NOT EXISTS object_blob_sink (
     object_key STRING,
     object_data blob
   )
   TBLPROPERTIES (
     'table.format.version' = '2',
     'transactional' = 'true'
   );
   
   INSERT INTO object_blob_sink
   SELECT
     key,
     GET_BLOB_FROM_OSS(
       '<project>.<schema>.txt_demo',
       key,
       'THROW_EXCEPTION'
     )
   FROM txt_demo;

2. Após a gravação, use read_blob para ler o conteúdo.

   -- Enable data type 2.0.
   SET odps.sql.type.system.odps2=true;
   
   SELECT
     object_key,
     CAST(READ_BLOB(object_data) AS STRING) AS object_text
   FROM object_blob_sink
   WHERE object_key = 'test_travel01.txt';
   
   -- The following result is returned:
   +------------+-------------+
   | object_key | object_text |
   +------------+-------------+
   | test_travel01.txt | Beijing · Forbidden City (Historical Site): Within the red walls and yellow tiles flows the imperial majesty of six hundred years. Walking among the golden ginkgo trees in autumn is the best way to feel the profound history and serene beauty of the Forbidden City. |
   +------------+-------------+

Etapa 6: Processar apenas objetos filtrados

Filtre primeiro as consultas da object table com base nos metadata e gere valores blob apenas para os objetos correspondentes.

Por exemplo, para processar apenas arquivos TXT com tamanho menor ou igual a 170 bytes, filtre primeiro os metadata da object table. Isso reduz o número de consultas de status do OSS e leituras de conteúdo subsequentes.

-- Enable data type 2.0.
SET odps.sql.type.system.odps2=true;
-- Enable three-level syntax for project.schema.table.
SET odps.namespace.schema=true;
-- If you run this from a DataWorks SQL node, add the following line to disable MaxQA.
SET odps.mcqa.disable=true;

SELECT
  key,
  GET_BLOB_FROM_OSS(
    '<project>.<schema>.txt_demo',
    key,
    'WARN_AND_NULL',
    'application/txt'
  ) AS document_blob
FROM txt_demo
WHERE key LIKE '%.txt'
  AND size <= 170;

-- The following result is returned, showing that the data has been filtered:
+------+---------------+
| key  | document_blob |
+------+---------------+
| test_travel01.txt | CAEQAxr1AgEAAAANAAAAAAAAAAEAAAAAAAAADQAAAAAAAABYAAAAXAAAAGQAAABsAAAAVgEAAFYBAABWAQAAVgEAAFYBAABeAQAAXgEAAF4BAAB1AQAADwkAAAAAAAAAAAAApAAAAAAAAAAAAAAAAAAAAOIAAAAAAAAAcGFuZ3U6Ly9PRFBTLUNOLUJFSUpJTkctSDoxMDI0MC9wcm9kdWN0L2FsaXl1bi9vZHBzL3l5eV90ZXN0X2Rldi8uRnV4aUpvYlRlbXBSb290LzIwMjYwODA1L0FkaG9jU2luay8yMDI2MDgwNTAyNTIzNjEyMGc3bDd3NzQxa2pnL2NvbnNvbGVfcXVlcnlfdGFza18xNzg1ODk4MzU1NzI5MF8wLzEvLWV4dC0vMjAyNjA4MDUwMjUyMzYxMjBnN2w3dzc0MWtqZ19NMV8wXzBfMDAwMDAwX2NpZDEuYmxvYgEAAAAAAAAADwAAAAAAAABhcHBsaWNhdGlvbi90eHQiACoUqGGDqqv2cyDwow86F8uQa+wWHdQyGQoJbnMtZGlyZWN0Egx5eXlfdGVzdF9kZXY6HDIwMjYwODA1MDI1MjM2MTIwZzdsN3c3NDFramc= |
| test_travel02.txt | CAEQAxr1AgEAAAANAAAAAAAAAAEAAAAAAAAADQAAAAAAAABYAAAAXAAAAGQAAABsAAAAVgEAAFYBAABWAQAAVgEAAFYBAABeAQAAXgEAAF4BAAB1AQAADwkAAAAAAAAAAAAAoQAAAAAAAACkAAAAAAAAAOIAAAAAAAAAcGFuZ3U6Ly9PRFBTLUNOLUJFSUpJTkctSDoxMDI0MC9wcm9kdWN0L2FsaXl1bi9vZHBzL3l5eV90ZXN0X2Rldi8uRnV4aUpvYlRlbXBSb290LzIwMjYwODA1L0FkaG9jU2luay8yMDI2MDgwNTAyNTIzNjEyMGc3bDd3NzQxa2pnL2NvbnNvbGVfcXVlcnlfdGFza18xNzg1ODk4MzU1NzI5MF8wLzEvLWV4dC0vMjAyNjA4MDUwMjUyMzYxMjBnN2w3dzc0MWtqZ19NMV8wXzBfMDAwMDAwX2NpZDEuYmxvYgEAAAAAAAAADwAAAAAAAABhcHBsaWNhdGlvbi90eHQiACoUUhmRcOOiCCBQ0LXOdBulUociULYyGQoJbnMtZGlyZWN0Egx5eXlfdGVzdF9kZXY6HDIwMjYwODA1MDI1MjM2MTIwZzdsN3c3NDFramc= |
| test_travel04.txt | CAEQAxr1AgEAAAANAAAAAAAAAAEAAAAAAAAADQAAAAAAAABYAAAAXAAAAGQAAABsAAAAVgEAAFYBAABWAQAAVgEAAFYBAABeAQAAXgEAAF4BAAB1AQAADwkAAAAAAAAAAAAAoQAAAAAAAABFAQAAAAAAAOIAAAAAAAAAcGFuZ3U6Ly9PRFBTLUNOLUJFSUpJTkctSDoxMDI0MC9wcm9kdWN0L2FsaXl1bi9vZHBzL3l5eV90ZXN0X2Rldi8uRnV4aUpvYlRlbXBSb290LzIwMjYwODA1L0FkaG9jU2luay8yMDI2MDgwNTAyNTIzNjEyMGc3bDd3NzQxa2pnL2NvbnNvbGVfcXVlcnlfdGFza18xNzg1ODk4MzU1NzI5MF8wLzEvLWV4dC0vMjAyNjA4MDUwMjUyMzYxMjBnN2w3dzc0MWtqZ19NMV8wXzBfMDAwMDAwX2NpZDEuYmxvYgEAAAAAAAAADwAAAAAAAABhcHBsaWNhdGlvbi90eHQiACoU69Ba157khgFI/9FvtdoawcOw4UkyGQoJbnMtZGlyZWN0Egx5eXlfdGVzdF9kZXY6HDIwMjYwODA1MDI1MjM2MTIwZzdsN3c3NDFramc= |
| test_travel08.txt | CAEQAxr1AgEAAAANAAAAAAAAAAEAAAAAAAAADQAAAAAAAABYAAAAXAAAAGQAAABsAAAAVgEAAFYBAABWAQAAVgEAAFYBAABeAQAAXgEAAF4BAAB1AQAADwkAAAAAAAAAAAAAqQAAAAAAAADmAQAAAAAAAOIAAAAAAAAAcGFuZ3U6Ly9PRFBTLUNOLUJFSUpJTkctSDoxMDI0MC9wcm9kdWN0L2FsaXl1bi9vZHBzL3l5eV90ZXN0X2Rldi8uRnV4aUpvYlRlbXBSb290LzIwMjYwODA1L0FkaG9jU2luay8yMDI2MDgwNTAyNTIzNjEyMGc3bDd3NzQxa2pnL2NvbnNvbGVfcXVlcnlfdGFza18xNzg1ODk4MzU1NzI5MF8wLzEvLWV4dC0vMjAyNjA4MDUwMjUyMzYxMjBnN2w3dzc0MWtqZ19NMV8wXzBfMDAwMDAwX2NpZDEuYmxvYgEAAAAAAAAADwAAAAAAAABhcHBsaWNhdGlvbi90eHQiACoUEb5KuihKijpGxK0QjStIO4xvGuYyGQoJbnMtZGlyZWN0Egx5eXlfdGVzdF9kZXY6HDIwMjYwODA1MDI1MjM2MTIwZzdsN3c3NDFramc= |
| test_travel11.txt | CAEQAxr1AgEAAAANAAAAAAAAAAEAAAAAAAAADQAAAAAAAABYAAAAXAAAAGQAAABsAAAAVgEAAFYBAABWAQAAVgEAAFYBAABeAQAAXgEAAF4BAAB1AQAADwkAAAAAAAAAAAAApwAAAAAAAACPAgAAAAAAAOIAAAAAAAAAcGFuZ3U6Ly9PRFBTLUNOLUJFSUpJTkctSDoxMDI0MC9wcm9kdWN0L2FsaXl1bi9vZHBzL3l5eV90ZXN0X2Rldi8uRnV4aUpvYlRlbXBSb290LzIwMjYwODA1L0FkaG9jU2luay8yMDI2MDgwNTAyNTIzNjEyMGc3bDd3NzQxa2pnL2NvbnNvbGVfcXVlcnlfdGFza18xNzg1ODk4MzU1NzI5MF8wLzEvLWV4dC0vMjAyNjA4MDUwMjUyMzYxMjBnN2w3dzc0MWtqZ19NMV8wXzBfMDAwMDAwX2NpZDEuYmxvYgEAAAAAAAAADwAAAAAAAABhcHBsaWNhdGlvbi90eHQiACoUuVU6K/SEmU1aRdxjLSRgXhx0j1MyGQoJbnMtZGlyZWN0Egx5eXlfdGVzdF9kZXY6HDIwMjYwODA1MDI1MjM2MTIwZzdsN3c3NDFramc= |
+------+---------------+

Dividir tarefas por tamanho do objeto

A GET_BLOB_FROM_OSS pode acionar a divisão de task com base no tamanho do objeto. Isso evita problemas de long tail causados pela concentração de arquivos grandes em poucos workers quando a divisão considera apenas o número de linhas de metadata.

Ao usar a divisão por tamanho, ajuste a unidade de divisão conforme a distribuição do tamanho dos objetos e o tempo de processamento por objeto:

-- The priority is KB > MB > GB. Tune based on object size distribution and per-object processing time. Do not set the unit too small just to increase parallelism.

-- Default unit configuration in GiB.
SET odps.sql.object.table.split.unit.gb=1;

-- MB configuration takes precedence over the GB configuration.
SET odps.sql.object.table.split.unit.mb=512;

-- KB configuration has the highest priority.
SET odps.sql.object.table.split.unit.kb=512;

-- To disable splitting by object size, set the following parameter. Disabling this may create additional preprocessing jobs. The actual execution plan depends on the output in Logview.
SET odps.sql.object.table.split.by.object.size.enabled=false;

Evitar a leitura não intencional de objetos grandes inteiros

• Se um processo downstream precisar apenas do cabeçalho do arquivo, magic number ou de um pequeno intervalo de conteúdo, defina offset e length.

• Para filtrar objetos, priorize as colunas de metadados da tabela de objetos, como key, size, type e last_modified.

• A GET_BLOB_FROM_OSS gera principalmente uma referência, mas read_blob e a gravação em uma tabela BLOB podem acionar uma leitura real do objeto. Avalie o custo com base no tráfego real do OSS e nos recursos de computação.

• Se muitos objetos retornarem WARN_AND_NULL, atualize os metadados da Object Table ou verifique o ciclo de vida dos dados no OSS em vez de depender de avisos por objeto a longo prazo.

Por que o primeiro parâmetro deve usar o nome completo da tabela?

Este parâmetro identifica a Object Table e recupera a configuração de acesso ao OSS da tabela no contexto da tarefa. Recomendamos sempre usar o nome completo em três partes project.schema.object_table e garantir que ele corresponda à Object Table realmente verificada na cláusula FROM.

Por que a função retorna NULL mesmo se o objeto existir na tabela de objetos?

As consultas da Object Table usam metadados em cache. Se um objeto for excluído após a atualização mais recente dos metadados, a política padrão OUTPUT_NULL retorna NULL. Atualize os metadados ou use temporariamente WARN_AND_NULL ou THROW_EXCEPTION para localizar os dados inconsistentes.

A GET_BLOB_FROM_OSS faz download do objeto?

Não. A função acessa o OSS para obter o status e o tamanho do objeto, mas retorna uma referência blob. Os bytes do objeto são lidos somente quando você chama read_blob, passa o blob para uma função que lê seu conteúdo ou o grava em uma tabela de destino que materializa o conteúdo.

Um blob pode ser usado como coluna de partição ou chave primária?

Não. A validação de tipo impede o uso de blob como partition column ou primary key. Um blob também não pode ser aninhado em complex types como ARRAY, MAP ou STRUCT. Use blob como uma coluna de dados comum.

Devo usar GET_BLOB_FROM_OSS ou uma URL assinada?

• Com GET_BLOB_FROM_OSS, o processamento subsequente permanece nos sistemas SQL e BLOB do MaxCompute.

• Para recuperar imediatamente o conteúdo binário de um objeto, use GET_DATA_FROM_OSS.

• Para permitir que ECS, serviços de terceiros ou clientes HTTP acessem diretamente o OSS, use GET_SIGNED_URL_FROM_OSS. Uma URL assinada tem tempo de expiração e depende da configuração de Acesso Público do Bucket e da acessibilidade do endpoint de rede.

Erro ao consultar detalhes do blob

• Detalhes do erro

  {"serverCostTime":191,"requestId":"068ebe0f2ed96a2aab0998e0770f40ec",
    "success":false,"errCode":"CP001",
    "errMsg":"Current access is abnormal, please start from the data analysis homepage."}

• Causa raiz

  Por padrão, os SQL nodes no DataWorks usam MaxQA para execução.

• Solução

  Ao consultar uma object table em um SQL node do DataWorks, inclua SET odps.mcqa.disable=true;.