Object table

Core capabilities

Object Table provides the following core capabilities:

• Map unstructured data in OSS (PDFs, images, PowerPoint files) to a table and automatically extract file metadata.

• Process unstructured data with materialized views, ETL, and AI Function. Generate and store embeddings natively within AnalyticDB for MySQL. Incremental change detection minimizes redundant computation.

• Use AI Function (such as ai_embed) for inference and classification. Combine vector search with full-text search for dual-channel recall.

• Join unstructured and structured data in AnalyticDB for MySQL, unifying analytics across data types.

With Object Table and AI Function, you can process, retrieve, and analyze unstructured data from OSS directly within AnalyticDB for MySQL, without exporting data or calling external services.

Use cases

Overall architecture

Computing process

Create an object table

An object table has a fixed column structure for reading unstructured data—you do not specify columns when creating it. After creation, refresh the object table before querying.

CREATE OBJECT TABLE [schema_name.]object_table_name
WITH (
    uri = 'oss://my-bucket/my-uri/',
    endpoint = 'oss-xxxx.aliyuncs.com',
    [role_arn = 'acs:ram::12345678****:role/ADB-OSS-Reader',]
    [storage_type = '[oss | s3',]
    [distributed_key = 'columnName[,...]]',]
    [clustered_key = '[columnName{:asc}] [,...]]',]
    [storage_policy = '[hot | cold]',]
    [partition_column = 'columnName',]
    [partition_column_format = 'YYYYMMDD',]
    [lifecycle = N]
) [COMMENT 'object table'];

Parameters

Fixed columns of an object table

Object tables have a fixed column structure and do not support custom columns.

DDL limitations

Object tables support a limited set of DDL operations.

Query data

After creating and refreshing an object table, query it like a regular table. The metadata column is JSON type—parse it with JSON functions.

-- Query all data
SELECT * FROM <object_table_name>;
-- Extract fields from metadata by using a JSON function
SELECT uri, etag, json_extract(metadata, '$.uri') FROM <object_table_name>;
-- Use shorthand syntax to extract a JSON field
SELECT metadata->>'$.uri' FROM <object_table_name>;

Query table schema

-- View the column definitions of the object table
DESC <object_table_name>;
-- Query column information from INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '<object_table_name>';
-- View the index information of the object table
SHOW INDEX FROM <object_table_name>;

Refresh an object table

Refresh an object table before querying. Currently, only manual refresh is supported.

REFRESH OBJECT TABLE [<schema_name>.]<table_name>;

Enable binlog

Enable binlog to support incremental data refresh with an incremental materialized view.

ALTER OBJECT TABLE [<schema_name>.]<table_name> SET binlog=true;

Scenario

A company stores PDF invoices in OSS and needs to:

• Query and manage PDF file metadata with SQL.

• Extract key fields (invoice date, number, amount) and table data from invoices with AI.

• Persist extracted results in a materialized view for retrieval and analysis.

Step 1: Enable the AI Function environment

To enable the AI Function environment, submit a ticket to technical support.

Step 2: Prepare OSS data and grant access

1. Upload the PDF files for parsing to a directory in an OSS bucket. Example:

   oss://pdf-oss-bj/ai/pdf-test/

   Note

   An object table automatically reads the metadata of all files in the specified URI directory. You do not need to register files individually.

   Sample data

   The sample PDF file (sample.pdf) used in this topic is a typical invoice that contains both key-value pairs and a table.

   The key-value pair data in this sample invoice includes Date Issued (26/3/2021), Invoice Number (INV-10012), Amount Due ($1,699.48), and Due Date (25/4/2021), along with company and customer address information. The table data is a line-item table with four columns: DESCRIPTION, RATE, QTY, and AMOUNT. The table contains items such as Services, Consulting, and Materials, with summary fields such as Subtotal, Discount, Tax, and Total, and payment terms at the bottom.

2. Configure RAM authorization

   AnalyticDB for MySQL requires authorization through a RAM role to access OSS.

   • Same-account scenario: If the AnalyticDB for MySQL instance and the OSS bucket are in the same Alibaba Cloud account, you can omit the role_arn parameter. The system automatically uses the built-in service role for authorization.

   • Cross-account scenario: You must create a role in the RAM console, grant it OSS read permissions, and obtain the corresponding ARN (Cross-account authorization).

Step 3 (Optional): Extract PDF schema

Before parsing at scale, probe the schema of a single PDF to understand its table and form structures for more precise extraction.

SELECT ai_pdf_extract(
  'qwen35_plus',
  'oss://my-bucket/invoices/invoice_001.pdf',
  '{"page_index": 1}'
);

From the returned JSON result, obtain the table_schema and form_schema. Reuse them in subsequent queries to improve parsing efficiency.

Step 4: Create an object table

• Same-account scenario: You do not need to specify the role_arn. If you omit it, the system uses its built-in role_arn by default.

  CREATE OBJECT TABLE object_table_test WITH (
    uri = 'oss://pdf-oss-bj/ai/pdf-test/',-- The directory for the OSS resource
    endpoint = 'oss-cn-beijing-internal.aliyuncs.com'-- The endpoint of the OSS resource
  );

• Cross-account scenario: You must specify the role_arn.

  CREATE OBJECT TABLE object_table_test WITH (
    uri      = 'oss://my-bucket/invoices/',
    endpoint = 'oss-cn-hangzhou-internal.aliyuncs.com',
    role_arn = 'acs:ram::1234567890:role/adb-oss-reader'
  );

Step 5: Refresh the object table

Manually refresh the object table before querying.

-- Refresh the object table.
REFRESH OBJECT TABLE object_table_test;
-- Query the status of the refresh job.
SELECT * FROM INFORMATION_SCHEMA.kepler_meta_refresh_object_table_job
ORDER BY start_time DESC LIMIT 10;
-- Query data in the object table.
SELECT * FROM object_table_test LIMIT 10;

Step 6: Parse PDF content with AI

Combine an object table with AI Function to extract and analyze PDF content.

SELECT ai_pdf_extract(
  <model_name>,
  <file_url>,
  '{"page_index": <page_index>, "timeout": <timeout>}'
);

SELECT ai_pdf_extract(
  'qwen35_plus',
  metadata,
  '{"page_index": 1, "timeout": 300}'
)
FROM object_table_test
LIMIT 1;

WITH pdf_data AS (
  SELECT
    ai_pdf_extract(
      'qwen35_plus',
      metadata,
      '{"page_index": 1, "schema":{
        "table_schema":[
          {"original_name":"DESCRIPTION","standard_name":"description","field_type":"string"},
          {"original_name":"RATE","standard_name":"rate","field_type":"number"},
          {"original_name":"QTY","standard_name":"qty","field_type":"number"},
          {"original_name":"AMOUNT","standard_name":"amount","field_type":"number"}
        ],
        "form_schema":[
          {"original_name":"Billed To","standard_name":"billed_to","field_type":"string"},
          {"original_name":"Invoice Number","standard_name":"invoice_number","field_type":"string"},
          {"original_name":"Date Issued","standard_name":"date_issued","field_type":"date"},
          {"original_name":"Amount Due","standard_name":"amount_due","field_type":"number"},
          {"original_name":"Total","standard_name":"total","field_type":"number"}
        ]
      }, "timeout": 300}'
    ) AS result
  FROM object_table_test
)
SELECT
  JSON_UNQUOTE(JSON_EXTRACT(result, '$.data[2].content.filename'))          AS file_name,
  JSON_UNQUOTE(JSON_EXTRACT(result, '$.data[2].content.size'))              AS file_size,
  JSON_UNQUOTE(JSON_EXTRACT(result, '$.data[2].content.creation_date'))     AS create_date,
  JSON_UNQUOTE(JSON_EXTRACT(result, '$.data[2].content.modification_date')) AS modify_date,
  JSON_UNQUOTE(JSON_EXTRACT(result, '$.data[2].content.page_count'))        AS page_count
FROM pdf_data;

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.description')) AS description,
  JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.rate'))        AS rate,
  JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.qty'))         AS qty,
  JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.amount'))      AS amount
FROM (
  SELECT ai_pdf_extract(
    'qwen35_plus',
    metadata,
    '{"page_index": 1, "schema":{
      "table_schema":[
        {"original_name":"DESCRIPTION","standard_name":"description","field_type":"string"},
        {"original_name":"RATE","standard_name":"rate","field_type":"number"},
        {"original_name":"QTY","standard_name":"qty","field_type":"number"},
        {"original_name":"AMOUNT","standard_name":"amount","field_type":"number"}
      ]
    }, "timeout": 300}'
  ) AS result
  FROM object_table_test
) pdf_data
LATERAL VIEW EXPLODE(JSON_EXTRACT(result, '$.data[0].content')) t AS row_data;

Step 7: Persist results with a materialized view

Save AI parsing results to a materialized view to avoid repeated model calls.

CREATE MATERIALIZED VIEW invoice_mv_full
REFRESH COMPLETE ON DEMAND
AS
SELECT
  JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.description')) AS description,
  JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.rate'))        AS rate,
  JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.qty'))         AS qty,
  JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.amount'))      AS amount
FROM (
  SELECT ai_pdf_extract(
    'qwen35_plus',
    metadata,
    '{"page_index": 1, "schema":{
      "table_schema":[
        {"original_name":"DESCRIPTION","standard_name":"description","field_type":"string"},
        {"original_name":"RATE","standard_name":"rate","field_type":"number"},
        {"original_name":"QTY","standard_name":"qty","field_type":"number"},
        {"original_name":"AMOUNT","standard_name":"amount","field_type":"number"}
      ]
    }, "timeout": 300}'
  ) AS result
  FROM object_table_test
) pdf_data
LATERAL VIEW EXPLODE(JSON_EXTRACT(result, '$.data[0].content')) t AS row_data;
-- Query the materialized view.
SELECT * FROM invoice_mv_full;

Step 8 (Optional): Process incremental data with ETL

Alternatively, use an ETL process to filter incremental data by the last_modified_at column and write AI parsing results to a standard table.

-- Create a destination table to store the parsed results.
CREATE TABLE invoice_parsed_data (
  uri         VARCHAR NOT NULL,
  description VARCHAR,
  rate        DECIMAL(10,2),
  qty         INT,
  amount      DECIMAL(10,2),
  parsed_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (uri, description)
) DISTRIBUTE BY HASH(uri);
-- Incremental write: Process only files that were added or changed in the last day.
REPLACE INTO invoice_parsed_data (uri, description, rate, qty, amount)
SELECT
  CAST(pdf_data.file_uri AS VARCHAR) as uri,
  CAST(JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.description')) AS VARCHAR) AS description,
  CAST(JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.rate')) AS DECIMAL(10,2)) AS rate,
  CAST(JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.qty')) AS int) AS qty,
  CAST(JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.amount')) AS DECIMAL(10,2)) AS amount
FROM (
  SELECT
    uri AS file_uri,
    ai_pdf_extract(
      'qwen35_plus',
      metadata,
      '{"page_index": 1, "schema":{
        "table_schema":[
          {"original_name":"DESCRIPTION","standard_name":"description","field_type":"string"},
          {"original_name":"RATE","standard_name":"rate","field_type":"number"},
          {"original_name":"QTY","standard_name":"qty","field_type":"number"},
          {"original_name":"AMOUNT","standard_name":"amount","field_type":"number"}
        ]
      }, "timeout": 300}'
    ) AS result
  FROM object_table_test
  WHERE last_modified_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
) pdf_data
LATERAL VIEW EXPLODE(JSON_EXTRACT(result, '$.data[0].content')) t AS row_data;