对象表（Object Table）

使用ai_pdf_extract函数直接查询（快速验证）

此方式返回原始 JSON 结果，适合在调试阶段快速查看 AI 解析的完整输出。

语法

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

参数说明

返回结果

函数返回一个 JSON 字符串，包含以下三类信息：

示例

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

CTE + JSON 解析（推荐，适合提取结构化字段）

示例

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;

子查询 + LATERAL VIEW（适合展开表格行数据）

示例

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;

返回示例

全量物化视图

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;

-- 查询物化视图
SELECT * FROM invoice_mv_full;

增量物化视图

当 OSS 中持续有新的 PDF 文件写入时，可以通过增量物化视图自动完成新增文件的解析，无需重复处理已有文件。

1. 开启对象表 Binlog。

   ALTER TABLE object_table_test BINLOG = true;

2. 创建增量物化视图，设置自动刷新间隔。

   说明

   AI Function 的处理耗时较长，建议将增量刷新间隔设置为 5 分钟或更长，避免刷新任务堆积。

   -- 创建只缓存 AI 结果的物化视图
   CREATE MATERIALIZED VIEW invoice_mv_cache
   REFRESH FAST NEXT now() + INTERVAL 5 MINUTE
   AS
   SELECT 
       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 ai_result
   FROM object_table_test;
   
   -- 创建虚拟视图 (进行“实时”展开)
   CREATE VIEW invoice_mv_incremental_view AS
   SELECT
       JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.description')) AS description,
       CAST(JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.rate')) AS DOUBLE) AS rate,
       CAST(JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.qty')) AS DOUBLE) AS qty,
       CAST(JSON_UNQUOTE(JSON_EXTRACT(row_data, '$.amount')) AS DOUBLE) AS amount
   FROM invoice_mv_cache
   LATERAL VIEW EXPLODE(
       -- 将数组解析出来
       CAST(JSON_EXTRACT(ai_result, '$.data[0].content') AS ARRAY<JSON>)
   ) t AS row_data;
   
   SELECT * FROM invoice_mv_cache;
   SELECT * FROM invoice_mv_incremental_view;