[新機能]AI_FILTER関数が一般提供となったので試してみた

import pandas as pd

# 正しいファイルパスを指定します
input_file = '指定のファイルパス'

# 分割する行数
chunk_size = 100000

# ファイルを分割して保存
for i, chunk in enumerate(pd.read_csv(input_file, chunksize=chunk_size)):
    chunk.to_csv(f'output_{i+1}.csv', index=False)

print("ファイルの分割が完了しました。")

-- スキーマの作成
CREATE SCHEMA IF NOT EXISTS steam_schema;
-- テーブルの作成
CREATE OR REPLACE TABLE steam_reviews (
    review TEXT,
    hours_played VARCHAR(16777216),
    helpful VARCHAR(16777216),
    funny VARCHAR(16777216),
    recommendation VARCHAR(16777216),
    review_date VARCHAR(16777216),
    game_name VARCHAR(16777216),
    username VARCHAR(16777216)
);

-- バルクロード
COPY INTO steam_reviews
FROM (
    SELECT
        $1,    -- review
        $2,    -- hours_played
        $3,    -- helpful
        $4,    -- funny
        $5,    -- recommendation
        $6,    -- review_date
        $7,    -- game_name
        $8     -- username
    FROM @KAWABATA_MART_DB.STEAM_SCHEMA.test -- ご自身のステージ名に置き換えてください
)
FILE_FORMAT = (
    TYPE = 'CSV',
    SKIP_HEADER = 1,
    FIELD_DELIMITER = ',',
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
);

WITH test AS (
  SELECT *
  FROM steam_reviews
  LIMIT 100
)
SELECT *
FROM test
WHERE AI_FILTER(
  PROMPT(
    'In the following Steam review, is the user complaining about anti-cheat? Review: {0}',
    review
  )
);

WITH test AS (
  SELECT *
  FROM steam_reviews
  LIMIT 100
),
labeled AS (
  SELECT
    recommendation,
    AI_FILTER(
      PROMPT(
        'Return TRUE if the review complains about kernel-level anti-cheat; otherwise return FALSE. Review: {0}',
        review
      )
    ) AS is_anticheat_complaint
  FROM test
)
SELECT
  recommendation,
  COUNT(*) AS total,
  SUM(IFF(is_anticheat_complaint, 1, 0)) AS anticheat_cnt,
  anticheat_cnt / NULLIF(total, 0) AS anticheat_ratio
FROM labeled
GROUP BY recommendation
ORDER BY total DESC;

WITH pictures AS (
  SELECT TO_FILE(file_url) AS png
  FROM DIRECTORY(@KAWABATA_MART_DB.test.test)
)
SELECT FL_GET_RELATIVE_PATH(png) AS file_path
FROM pictures
WHERE AI_FILTER('猫の写真ですか？', png);

WITH pictures AS (
  SELECT TO_FILE(file_url) AS png
  FROM DIRECTORY(@KAWABATA_MART_DB.test.test)
)
SELECT FL_GET_RELATIVE_PATH(png) AS file_path
FROM pictures
WHERE AI_FILTER('星と猫の写真', png);