[Databricks] AI Functions로 고객 리뷰 분류·추출·요약하기 - 활용 3편

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

%sql
CREATE SCHEMA IF NOT EXISTS workspace.review_ai_lab;

CREATE OR REPLACE TABLE workspace.review_ai_lab.reviews_raw
USING DELTA
AS
SELECT * FROM VALUES
  (1, 'I bought a Croissant in Seoul. It was fresh and buttery. The staff were friendly, and I would happily visit again.'),
  (2, 'I bought a Muffin in Busan. It was dry and tasted stale. I am disappointed and will not buy it again.'),
  (3, 'I bought Sourdough in Seoul. The bread tasted excellent, but I waited forty minutes and the service was slow.'),
  (4, 'I bought a Bagel in Incheon on Monday. The receipt lists one item, and the store opened at nine.'),
  (5, 'I ordered a Cinnamon Roll in Daejeon. It arrived warm, the icing was delicious, and the packaging kept it in perfect condition.'),
  (6, 'I ordered a Chocolate Cake in Suwon for a birthday. Delivery arrived two hours late. The box was crushed and the cake was damaged. Customer support did not reply. I want a refund.')
AS t(review_id, review_text);

SELECT *
FROM workspace.review_ai_lab.reviews_raw
ORDER BY review_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

%sql
CREATE OR REPLACE TABLE workspace.review_ai_lab.review_sentiment
USING DELTA
AS
SELECT
    review_id,
    ai_analyze_sentiment(review_text) AS sentiment
FROM workspace.review_ai_lab.reviews_raw;

SELECT r.review_id, r.review_text, s.sentiment
FROM workspace.review_ai_lab.reviews_raw r
JOIN workspace.review_ai_lab.review_sentiment s USING (review_id)
ORDER BY r.review_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

%sql
CREATE OR REPLACE TABLE workspace.review_ai_lab.review_entities
USING DELTA
AS
SELECT
    review_id,
    ai_extract(
        review_text,
        '{
          "product": {
            "type": "string",
            "description": "Product name explicitly mentioned in the review."
          },
          "city": {
            "type": "string",
            "description": "City explicitly mentioned in the review."
          },
          "issue": {
            "type": "string",
            "description": "Customer complaint or problem stated in the review. Return null if no complaint is stated."
          }
        }',
        map('version', '2.1')
    ) AS extraction
FROM workspace.review_ai_lab.reviews_raw;

SELECT
    review_id,
    extraction:response.product.value::STRING AS product,
    extraction:response.city.value::STRING AS city,
    extraction:response.issue.value::STRING AS issue,
    extraction:error_message::STRING AS error_message
FROM workspace.review_ai_lab.review_entities
ORDER BY review_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

%sql
CREATE OR REPLACE TABLE workspace.review_ai_lab.review_summaries
USING DELTA
AS
SELECT
    review_id,
    ai_summarize(review_text, 20) AS summary
FROM workspace.review_ai_lab.reviews_raw;

SELECT r.review_id, r.review_text, s.summary
FROM workspace.review_ai_lab.reviews_raw r
JOIN workspace.review_ai_lab.review_summaries s USING (review_id)
ORDER BY r.review_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

%sql
CREATE OR REPLACE TABLE workspace.review_ai_lab.reviews_enriched
USING DELTA
AS
SELECT
    r.review_id,
    r.review_text,
    s.sentiment,
    e.extraction:response.product.value::STRING AS product,
    e.extraction:response.city.value::STRING AS city,
    e.extraction:response.issue.value::STRING AS issue,
    e.extraction:error_message::STRING AS extraction_error,
    m.summary
FROM workspace.review_ai_lab.reviews_raw r
LEFT JOIN workspace.review_ai_lab.review_sentiment s USING (review_id)
LEFT JOIN workspace.review_ai_lab.review_entities e USING (review_id)
LEFT JOIN workspace.review_ai_lab.review_summaries m USING (review_id);

SELECT review_id, sentiment, product, city, issue, summary
FROM workspace.review_ai_lab.reviews_enriched
ORDER BY review_id;

1
2
3
4
5
6

%sql
SELECT review_id, sentiment, product, city, issue, summary
FROM workspace.review_ai_lab.reviews_enriched
WHERE sentiment IN ('negative', 'mixed')
  AND extraction_error IS NULL
ORDER BY review_id;