MotherDuckのLLM機能を使ってテキストデータから情報を抜き出してみた

my_ecl_trial:
  outputs:
    dev:
      type: duckdb
      path: "md:my_db?motherduck_token={{ env_var('MOTHERDUCK_TOKEN') }}"
      threads: 4
      extensions:
        - httpfs
        - parquet
  target: dev

sources:
  - name: hf_reviews_ja
    meta:
      external_location: "hf://datasets/SetFit/amazon_reviews_multi_ja@~parquet/default/train/0000.parquet"
    tables:
      - name: train

{{ config(materialized='view') }}

with source_data as (
    select
        id,
        text as review_text,
        label as stars,
        row_number() over (partition by label order by random()) AS row_num
    from {{ source('hf_reviews_ja', 'train') }}
    where length(text) >= 200
),

final as (
    select
        id,
        review_text,
        stars
    from source_data
    WHERE row_num <= 20
    order by stars
)

select *
from final

{{ config(materialized="table") }}

select id, prompt_struct_response.*
from
    (
        select
            id,
            prompt(
                'あなたはテキストから分析に必要な情報（コンテキスト）を抽出するAIアシスタントです。\n'
                || '顧客の商品レビューのテキストから情報を抜き出してください。指示した形式のデータを返し、レスポンスは日本語にしてください。\n'
                || '以下が情報を抜き出すレビューになります:'
                || '```'
                || review_text
                || '```',
                struct := {
                    sentiment:'VARCHAR',
                    product_features:'VARCHAR[]',
                    pros:'VARCHAR[]',
                    cons:'VARCHAR[]',
                    mentions_price:'BOOLEAN',
                    mentions_shipping:'BOOLEAN',
                    mentions_packaging:'BOOLEAN',
                    purchase_reason:'VARCHAR[]',
                    quality_concerns:'VARCHAR[]',
                },
                struct_descr := {
                    sentiment:'レビューの感情（センチメント）を返す。値は `positive`、`neutral` または `negative` のみ取ることができる',
                    product_features:'レビューで言及されている機能のリスト、言及がない場合は空の配列を返す',
                    pros:'レビューで言及されているメリットや肯定的な側面のリスト、言及がない場合は空の配列を返す',
                    cons:'レビューで言及されているデメリットや否定的な側面のリスト、言及がない場合は空の配列を返す',
                    mentions_price:'レビューが価格情報に言及しているかどうかを示す',
                    mentions_shipping:'レビューが配送情報に言及しているかどうかを示す',
                    mentions_packaging:'レビューが梱包情報に言及しているかどうかを示す',
                    purchase_reason:'レビューで言及されている購入理由のリスト、言及がない場合は空の配列を返す',
                    quality_concerns:'レビューで言及された品質に関する懸念のリスト、言及がない場合は空の配列を返す',
                }
            ) as prompt_struct_response
        from {{ ref('raw_reviews_text_ja') }}
    )

{{ config(materialized="view") }}

select
    id,
    sentiment,
    replace(unnest(product_features), '"','') as product_features,
    replace(unnest(pros), '"','') as pros,
    replace(unnest(cons), '"','') as cons,
    replace(unnest(purchase_reason), '"','') as purchase_reason,
    replace(unnest(quality_concerns), '"','') as quality_concerns
from {{ ref("stg_review_processed") }}

{{ config(materialized="view") }}

select
    sentiment,
    count(*) as count,
    sum(case when mentions_price = true then 1 else 0 end) as price_count,
    sum(case when mentions_shipping = true then 1 else 0 end) as shipping_count,
    sum(case when mentions_packaging = true then 1 else 0 end) as packaging_count
from {{ ref("stg_review_processed") }}
group by
    sentiment

❯ dbt run
05:37:00  Running with dbt=1.9.3
05:37:00  Registered adapter: duckdb=1.9.2
05:37:01  Found 7 models, 1 source, 426 macros
05:37:01  
05:37:01  Concurrency: 4 threads (target='dev')
05:37:01  
05:37:38  1 of 7 START sql view model main.raw_reviews_text_ja ........................... [RUN]
05:37:41  1 of 7 OK created sql view model main.raw_reviews_text_ja ...................... [OK in 2.98s]
05:37:41  2 of 7 START sql table model main.stg_review_processed ......................... [RUN]
05:37:54  2 of 7 OK created sql table model main.stg_review_processed .................... [OK in 13.56s]
05:37:54  3 of 7 START sql view model main.review_summary ................................ [RUN]
05:37:54  4 of 7 START sql view model main.stg_review_unnested_attributes ................ [RUN]
05:37:57  3 of 7 OK created sql view model main.review_summary ........................... [OK in 3.20s]
05:37:57  4 of 7 OK created sql view model main.stg_review_unnested_attributes ........... [OK in 3.20s]
05:37:57  5 of 7 START sql view model main.customer_feedbacks ............................ [RUN]
05:37:57  6 of 7 START sql view model main.purchase_reasons .............................. [RUN]
05:37:57  7 of 7 START sql view model main.quality_concerns .............................. [RUN]
05:38:01  5 of 7 OK created sql view model main.customer_feedbacks ....................... [OK in 3.19s]
05:38:01  6 of 7 OK created sql view model main.purchase_reasons ......................... [OK in 3.19s]
05:38:01  7 of 7 OK created sql view model main.quality_concerns ......................... [OK in 3.60s]
05:38:01  
05:38:01  Finished running 1 table model, 6 view models in 0 hours 1 minutes and 0.41 seconds (60.41s).
05:38:01  
05:38:01  Completed successfully
05:38:01  
05:38:01  Done. PASS=7 WARN=0 ERROR=0 SKIP=0 TOTAL=7