DatabricksのAI Functionsを活用した顧客レビューの分析

こちらのチュートリアルをウォークスルーします。

https://docs.databricks.com/ja/large-language-models/ai-functions-example.html

# データの準備

最初に、分析に使うデータを準備します。DatabricksワークスペースにはAmazonレビューのデータセットがあるのでこちらを使います。

https://qiita.com/taka_yayoi/items/3f8ccad13c6efd242be1#amazon

```py
df = spark.read.format("parquet").option("header", True).load("dbfs:/databricks-datasets/amazon/data20K/")
display(df)
```
![Screenshot 2024-03-06 at 10.39.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/423cb481-9996-fcd4-a52d-a1cbd523488c.png)

テーブルとして永続化します。

```py
df.write.saveAsTable("takaakiyayoi_catalog.default.reviews")
```

# AI Functionsの活用

:::note info
**注意**
AI Functionsを使うには、[ペイパートークンがサポートされているリージョン](https://docs.databricks.com/ja/machine-learning/foundation-models/index.html#required)のDatabricksワークスペースでサーバレスのSQLウェアハウスを使う必要があります。
:::

## レビューの感情分析

[ai_sentiment](https://docs.databricks.com/ja/sql/language-manual/functions/ai_analyze_sentiment.html)でレビューの感情分析を行います。

```sql
%sql
SELECT
  review,
  ai_analyze_sentiment(review) AS sentiment
FROM
  takaakiyayoi_catalog.default.reviews LIMIT 1;
```
![Screenshot 2024-03-06 at 10.43.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/f4d4bc8d-aa10-8df2-c21f-7b55a754a667.png)

## レビューの分類

[ai_classify](https://docs.databricks.com/ja/sql/language-manual/functions/ai_classify.html)を使ってレビューを分類します。

```sql
%sql
SELECT
  review,
  ai_classify(
    review,
    ARRAY(
      "Arrives too late",
      "Wrong size",
      "Wrong color",
      "Dislike the style"
    )
  ) AS reason
FROM
  takaakiyayoi_catalog.default.reviews
WHERE
  ai_analyze_sentiment(review) = "negative" LIMIT 10
```
![Screenshot 2024-03-06 at 10.45.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/3c29790c-33b1-46ef-ecea-146ab47cdc41.png)

なお、このチュートリアルで触れていませんが、[ai_translate](https://docs.databricks.com/en/sql/language-manual/functions/ai_translate.html)を使うことでレビューを日本語にすることもできます、

```sql
%sql
SELECT
  ai_translate(review, "ja"),
  ai_classify(
    review,
    ARRAY(
      "Arrives too late",
      "Wrong size",
      "Wrong color",
      "Dislike the style"
    )
  ) AS reason
FROM
  takaakiyayoi_catalog.default.reviews
WHERE
  ai_analyze_sentiment(review) = "negative" LIMIT 10
```
![Screenshot 2024-03-06 at 10.46.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/f96dfeda-5155-6bf4-6b34-8b726f113fba.png)

## レビューからの情報抽出

[ai_extract](https://docs.databricks.com/ja/sql/language-manual/functions/ai_extract.html)を使って、適正なサイズに関する情報を抽出します。

```sql
%sql
SELECT
  review,
  ai_extract(review, array("usual size")) AS usual_size,
  ai_classify(review, array("Size is wrong", "Size is right")) AS fit
FROM
  takaakiyayoi_catalog.default.reviews LIMIT 10
```
![Screenshot 2024-03-06 at 10.48.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/e205ec41-06c0-9119-66f6-9a7dfe425841.png)

## レビューに基づいてレスポンスを生成

[ai_gen](https://docs.databricks.com/ja/sql/language-manual/functions/ai_gen.html)

```sql
%sql
SELECT
  review,
  ai_gen(
    "Generate a reply in 60 words to address the customer's review.
    Mention their opinions are valued and a 30% discount coupon code has been sent to their email.
    Customer's review: " || review
  ) AS reply
FROM
  takaakiyayoi_catalog.default.reviews
WHERE
  ai_analyze_sentiment(review) = "negative" LIMIT 10
```
![Screenshot 2024-03-06 at 10.50.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/e42dec79-b29a-8c2f-45a5-2e40eafed52d.png)

こちらも翻訳が可能です。

```sql
%sql
SELECT
  ai_translate(review, "ja"),
  ai_translate(ai_gen(
    "Generate a reply in 60 words to address the customer's review.
    Mention their opinions are valued and a 30% discount coupon code has been sent to their email.
    Customer's review: " || review
  ), "ja") AS reply
FROM
  takaakiyayoi_catalog.default.reviews
WHERE
  ai_analyze_sentiment(review) = "negative" LIMIT 10
```
![Screenshot 2024-03-06 at 10.54.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/c8f5543e-f879-2f3d-2b17-4df68cc52d82.png)


### はじめてのDatabricks

[はじめてのDatabricks](https://qiita.com/taka_yayoi/items/8dc72d083edb879a5e5d)

### Databricks無料トライアル

[Databricks無料トライアル](https://databricks.com/jp/try-databricks)