Snowflake AI_EXTRACT で複数の文書種を構造化抽出してみた

CREATE DATABASE makita_doc_ai_db;
CREATE SCHEMA makita_doc_ai_db.doc_ai_schema;

USE DATABASE makita_doc_ai_db;
USE SCHEMA doc_ai_schema;

USE WAREHOUSE COMPUTE_WH;

CREATE TABLE IF NOT EXISTS prompt_templates (
    template_id VARCHAR PRIMARY KEY,   -- テンプレートID(例: INSPECTION_REVIEWS)
    response_format VARIANT             -- JSON Schema 本体
);

INSERT INTO prompt_templates
SELECT 'INSPECTION_REVIEWS',
PARSE_JSON('{
    "schema": {
        "type": "object",
        "properties": {
            "list_of_units": {
                "description": "Extract the table showing all units and conditions",
                "type": "object",
                "properties": {
                    "unit_name": {"type": "array"},
                    "condition": {"type": "array"}
                }
            },
            "inspection_date": {"type": "string"},
            "inspection_grade": {"type": "string"},
            "inspector": {"type": "string"}
        }
    }
}');

CREATE STAGE IF NOT EXISTS my_pdf_stage
  DIRECTORY = (ENABLE = TRUE)
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

CREATE OR REPLACE FUNCTION extract_document_data(
    stage_name STRING,
    file_path STRING
)
RETURNS VARIANT
LANGUAGE SQL
AS $$
    SELECT AI_EXTRACT(
        file => TO_FILE(stage_name, file_path),
        responseFormat => (
            SELECT response_format
            FROM prompt_templates
            WHERE template_id = 'INSPECTION_REVIEWS'
        )
    ):response
$$;

INSERT INTO prompt_templates
SELECT 'TECHNICAL_WHITEPAPER',
PARSE_JSON('{
    "schema": {
        "type": "object",
        "properties": {
            "title": {"type": "string", "description": "ドキュメントのタイトル"},
            "product_name": {"type": "string", "description": "紹介されているプロダクト・サービス名"},
            "key_features": {"type": "array", "description": "主要機能のリスト"},
            "use_cases": {"type": "array", "description": "想定ユースケース"},
            "target_audience": {"type": "string", "description": "想定読者(エンジニア / マネジメント / 経営層など)"},
            "publication_date": {"type": "string", "description": "発行日(記載があれば)"}
        }
    }
}');

CREATE OR REPLACE FUNCTION extract_document_data(
    stage_name STRING,
    file_path STRING,
    p_template_id STRING       -- ← 引数名はカラム名 template_id と衝突させない
)
RETURNS VARIANT
LANGUAGE SQL
AS $$
    SELECT AI_EXTRACT(
        file => TO_FILE(stage_name, file_path),
        responseFormat => (
            SELECT response_format
            FROM prompt_templates
            WHERE template_id = p_template_id
        )
    ):response
$$;

SELECT extract_document_data('@my_pdf_stage', 'inspection.pdf', 'INSPECTION_REVIEWS');
SELECT extract_document_data('@whitepaper_stage', 'snowflake_guide.pdf', 'TECHNICAL_WHITEPAPER');

CREATE STAGE IF NOT EXISTS whitepaper_stage
  DIRECTORY = (ENABLE = TRUE)
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

ALTER STAGE whitepaper_stage REFRESH;
SELECT * FROM DIRECTORY('@whitepaper_stage');

SELECT extract_document_data(
    '@whitepaper_stage',
    'ebook-the-essential-guide-to-data-engineering-updated.pdf',
    'TECHNICAL_WHITEPAPER'
) AS result;

{
  "title": "Snowflake",
  "product_name": "Snowflake",
  "key_features": [
    "Lineage tracing",
    "Data cataloging",
    "Access privileges",
    "Change management"
  ],
  "use_cases": [
    "高頻率取引",
    "詐欺検出"
  ],
  "target_audience": "エンジニア",
  "publication_date": "None"
}

WITH extracted AS (
    SELECT extract_document_data(
        '@whitepaper_stage',
        'ebook-the-essential-guide-to-data-engineering-updated.pdf',
        'TECHNICAL_WHITEPAPER'
    ) AS data
)
SELECT
    data:title::STRING AS title,
    data:product_name::STRING AS product_name,
    data:key_features AS key_features,
    data:use_cases AS use_cases,
    data:target_audience::STRING AS target_audience,
    data:publication_date::STRING AS publication_date
FROM extracted;

CREATE OR REPLACE TABLE document_metadata AS
SELECT
    RELATIVE_PATH AS file_name,
    SIZE AS file_size,
    LAST_MODIFIED AS last_modified,
    FILE_URL AS snowflake_file_url,
    extract_document_data(
        '@whitepaper_stage',
        RELATIVE_PATH,
        'TECHNICAL_WHITEPAPER'
    ) AS extracted_data
FROM DIRECTORY('@whitepaper_stage');

Single-row subquery returns more than one row.

WHERE template_id = template_id   -- ★ カラム = カラム と解釈されて常に TRUE

{
  "use_cases": ["高頻率取引", "詐欺検出"],
  "target_audience": "エンジニア"
}