GET_BLOB_FROM_OSS

Prepare the data

Step 1: Generate a blob for each OSS object

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

Step 2: Use the _blob pseudo-column as a shorthand

_blob is not an actual metadata column of the object table, so SELECT * does not automatically include this column, and you must reference it explicitly. If you need to set the offset, length, missing policy, or content type, use the full function call.

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

Step 3: Read a portion of the object content

1. The following example references up to 64 bytes starting from byte 0 of each object.

   -- 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. When you read this blob, read_blob operates within the specified byte range. You can also extract a sub-segment from this 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 |
   +---------------------------+

Step 4: Set content type and view blob metadata

The JSON object returned by the GET_BLOB_METADATA function contains fields such as content_type, content_length, storage_type, checksum, and content_encoding.

For a value generated by GET_BLOB_FROM_OSS, the storage_type is OSS. If you do not explicitly pass a content_type, the content_type field in the metadata is 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"} |
+---------------+

Step 5: Write to a blob table

1. If you need to store the results for long-term use or reuse in subsequent tasks, you can write them to a table that supports the blob data type. For more information, see 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. After writing, you can use read_blob to read the content.

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

Step 6: Process only filtered objects

You can first filter object table queries based on metadata and then generate blob values only for the matching objects.

For example, to process only TXT files smaller than or equal to 170 bytes, you can filter the object table metadata first. This reduces the number of OSS status queries and subsequent content reads.

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

Split tasks by object size

GET_BLOB_FROM_OSS can trigger task splitting based on object size. This helps avoid long tail issues that can occur when large files are concentrated on a few workers due to splitting based only on the number of metadata rows.

When you use size-based splitting, you can tune the split unit based on the object size distribution and per-object processing time:

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

Avoid unintentionally reading entire large objects

• If a downstream process requires only the file header, magic number, or a small range of content, you should set offset and length.

• To filter objects, give priority to using the metadata columns of the object table, such as key, size, type, and last_modified.

• GET_BLOB_FROM_OSS itself primarily generates a reference, but read_blob and writing to a BLOB table may trigger an actual object read, so you should evaluate the cost based on the actual OSS traffic and computing resources.

• If a large number of objects return WARN_AND_NULL, you should refresh the Object Table metadata or check the OSS data lifecycle instead of relying on per-object warnings in the long run.

Why must the first parameter use the full table name?

This parameter is used not only to identify the Object Table but also to retrieve the OSS access configuration for the table from the task context. We recommend that you always use the three-part full name project.schema.object_table and ensure that it matches the Object Table that is actually scanned in the FROM clause.

Why does the function return NULL even if the object exists in the object table?

Object Table queries use cached metadata. If an object is deleted after the most recent metadata refresh, the default policy OUTPUT_NULL returns NULL. You can refresh the metadata, or temporarily use WARN_AND_NULL or THROW_EXCEPTION to locate the inconsistent data.

Does GET_BLOB_FROM_OSS download the object?

No. The function accesses OSS to get the object's status and size, but it returns a blob reference. The object bytes are read only when you call read_blob, pass the blob to a function that reads its content, or write it to a destination table that materializes the content.

Can a blob be used as a partition column or primary key?

No. Type validation prevents a blob from being used as a partition column or primary key. A blob also cannot be nested within complex types such as ARRAY, MAP, or STRUCT. A blob should be used as a regular data column.

Should I use GET_BLOB_FROM_OSS or a signed URL?

• Subsequent processing remains within the MaxCompute SQL and BLOB systems by using GET_BLOB_FROM_OSS.

• To immediately retrieve the binary content of an object, use GET_DATA_FROM_OSS.

• To allow ECS, third-party services, or HTTP clients to directly access OSS, use GET_SIGNED_URL_FROM_OSS. A signed URL has an expiration time and is affected by the Bucket Public Access configuration and network endpoint reachability.

Error when querying blob details

• Error details

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

• Root cause

  By default, SQL nodes in DataWorks use MaxQA for execution.

• Solution

  When you query an object table from a DataWorks SQL node, you must include SET odps.mcqa.disable=true;.