이 페이지는 Cloud Translation API를 통해 번역되었습니다.

BigQuery SQL 및 Vertex AI를 사용한 생성형 통계

1. 소개

이 Codelab에서는 BigQuery SQL 쿼리와 Vertex AI PaLM API를 사용하여 영화 성공 평점 예측 및 처방 앱을 빌드합니다. 텍스트 생성을 수행하는 데 사용되는 모델은 text-bison이며 BigQuery에서 원격 함수로 호스팅됩니다.

사용되는 서비스 목록은 다음과 같습니다.

BigQuery ML
Vertex AI PaLM API
Cloud Shell

빌드할 항목

생성할 항목:

모델을 포함할 BigQuery 데이터 세트
영화의 장르 및 런타임 속성을 기반으로 영화의 성공 점수를 예측하는 BigQuery ML 모델
Vertex AI PaLM API를 원격 함수로 호스팅하는 BigQuery 모델
BigQuery와 Vertex AI 간의 연결을 설정하기 위한 외부 연결

2. 요구사항

브라우저(Chrome 또는 Firefox 등)
결제가 사용 설정된 Google Cloud 프로젝트

시작하기 전에

Google Cloud 콘솔의 프로젝트 선택기 페이지에서 Google Cloud 프로젝트를 선택하거나 만듭니다.
Cloud 프로젝트에 결제가 사용 설정되어 있어야 하므로 프로젝트에 결제가 사용 설정되어 있는지 확인하는 방법을 알아보세요.
필요한 모든 API (BigQuery API, Vertex AI API, BigQuery Connection API)가 사용 설정되어 있는지 확인합니다.
bq가 사전 로드되어 제공되는 Google Cloud에서 실행되는 명령줄 환경인 Cloud Shell을 사용합니다. gcloud 명령어 및 사용법은 문서를 참조하세요.

Cloud 콘솔에서 오른쪽 상단에 있는 Cloud Shell 활성화를 클릭합니다.

프로젝트가 설정되지 않은 경우 다음 명령어를 사용하여 설정합니다.

gcloud config set project <YOUR_PROJECT_ID>

브라우저에 https://console.cloud.google.com/bigquery를 입력하여 직접 BigQuery 콘솔로 이동합니다.

3. 데이터 준비

이 사용 사례에서는 movielens 소스에서 파생된 movies 데이터 세트를 사용합니다.

데이터 세트를 만듭니다.

BigQuery 데이터 세트는 테이블의 모음입니다. 데이터 세트의 모든 테이블은 동일한 데이터 위치에 저장됩니다. 커스텀 액세스 제어를 연결하여 데이터 세트 및 해당 테이블에 대한 액세스를 제한할 수도 있습니다.

Cloud Shell에서 bq mk 명령어를 사용하여 'movie_insights'라는 데이터 세트를 만듭니다.

bq mk --location=us-central1 movie_insights

소스 파일을 Cloud Shell 머신에 클론합니다.

git clone https://github.com/AbiramiSukumaran/movie_score_genai_insights

Cloud Shell 머신에 생성된 새 프로젝트 디렉터리로 이동합니다.

cd movie_score_genai_insights

bq load 명령어를 사용하여 CSV 파일을 BigQuery 테이블에 로드합니다 (BigQuery UI에서 직접 업로드할 수도 있음).

bq load --source_format=CSV --skip_leading_rows=1 movie_insights.movie_score \
./movies_data.csv \ Id:numeric,name:string,rating:string,genre:string,year:numeric,released:string,score:string,director:string,writer:string,star:string,country:string,budget:numeric,company:string,runtime:numeric,data_cat:string

샘플을 쿼리하여 movie_score 테이블과 데이터가 데이터 세트에 생성되었는지 확인할 수 있습니다.

bq query --use_legacy_sql=false \
SELECT name, rating, genre, runtime FROM movie_insights.movie_score limit 3;

4. 데이터에서 ML로

GENRE 및 RUNTIME 속성을 기반으로 영화의 성공 점수를 예측하는 분류 모델을 만들어 보겠습니다. CREATE MODEL 문을 'LOGISTIC_REG' 옵션과 함께 사용하겠습니다. 로지스틱 회귀 모델을 만들고 학습시킵니다.

BigQuery 콘솔의 SQL 작업공간 쿼리 편집기 섹션에서 아래 쿼리를 실행합니다.

CREATE OR REPLACE MODEL
  `movie_insights.model_rating_by_runtime_genre`
OPTIONS
  ( model_type='LOGISTIC_REG',
    auto_class_weights=TRUE,
    data_split_method='NO_SPLIT',
    model_registry='vertex_ai',   
    vertex_ai_model_version_aliases=['logistic_reg', 'experimental'],
    input_label_cols=['score']
  ) AS
SELECT name, genre,runtime, score
FROM
  movie_insights.movie_score
WHERE
  data_cat = 'TRAIN';

쿼리 세부정보:

CREATE MODEL 문은 SELECT 문의 학습 데이터를 사용하여 모델을 학습시킵니다.
OPTIONS 절은 모델 유형과 학습 옵션을 지정합니다. 여기서 LOGISTIC_REG 옵션은 로지스틱 회귀 모델 유형을 지정합니다. 바이너리 로지스틱 회귀 모델과 멀티클래스 로지스틱 회귀 모델을 서로 다르게 지정할 필요는 없습니다. BigQuery ML은 라벨 열의 고유 값 수를 기반으로 학습할 대상을 결정할 수 있습니다.
data_split_method=‘NO_SPLIT' 쿼리 조건 (data_cat = 'TRAIN')에 따라 BQML이 데이터를 학습하도록 합니다. 또한 ‘AUTO_SPLIT’ 이 옵션에서는 프레임워크 (또는 이 경우 서비스)가 학습/테스트 분할의 파티션을 무작위로 지정하도록 허용합니다.
input_label_cols 옵션은 SELECT 문의에서 라벨 열로 사용할 열을 지정합니다. 여기서 라벨 열은 점수이므로 모델은 각 행에 있는 다른 값을 기반으로 10개의 점수 값 중 가장 가능성이 높은 값을 학습합니다.
‘auto_class_weights=TRUE’는 옵션은 학습 데이터에서 클래스 라벨의 균형을 조정합니다. 기본적으로 학습 데이터에는 가중치가 없습니다. 학습 데이터 라벨의 균형이 맞지 않는 경우 모델은 가장 인기 있는 라벨 클래스를 더 무겁게 예측하도록 학습할 수 있습니다.
SELECT 문은 csv 데이터로 로드한 테이블을 쿼리합니다. WHERE 절은 입력 테이블의 행을 필터링하여 이 단계에서 TRAIN 데이터 세트만 선택합니다.
다음 구성은 선택사항이므로 BigQuery ML이 Vertex AI 모델 레지스트리에 이를 명시적으로 등록할 수 있습니다. 자세한 내용은 이 블로그. model_registry='vertex_ai', vertex_ai_model_version_aliases=['logistic_reg', 'experimental']를 참조하세요.

생성되면 BigQuery SQL 작업공간의 SCHEMA 섹션에 아래가 표시됩니다.

모델을 만든 후에 ML.EVALUATE 함수를 사용하여 모델의 성능을 평가합니다. ML.EVALUATE 함수는 실제 데이터로 예측 값을 평가합니다.

MODEL 페이지에서 모델의 평가 측정항목을 볼 수도 있습니다.

주요 측정항목 한눈에 보기:

정밀도 - 양성으로 식별된 사례 중 실제로 양성이었던 사례의 비율은 어느 정도인가요? 정밀도 = 참양성 / (참양성 + 거짓양성) 재현율 - 실제 양성 중 올바르게 식별된 비율은 얼마나 되나요? 재현율 = 참양성 / (참양성 + 거짓음성) 정확성 - 분류 모델을 평가하기 위한 측정항목으로, 모델이 실제로 올바르게 된 예측의 비율입니다. 정확성 = 올바른 예측 횟수 / 총 예측 수

5. 모델을 사용하여 영화 점수 예측

예측 시간!!!! 다음 쿼리는 데이터 세트에서 'TEST'로 분류된 각 영화의 점수를 예측합니다. 데이터입니다.

BigQuery 콘솔의 SQL 작업공간 쿼리 편집기 섹션에서 아래 쿼리를 실행합니다.

SELECT
  *
FROM
  ML.PREDICT (MODEL movie_insights.model_rating_by_runtime_genre,
    (
    SELECT
      *
    FROM
      movie_insights.movie_score
    WHERE
      data_cat= 'TEST'
     )
  );

결과는 다음과 같습니다.

모델 결과는 영화의 Prediction_score를 1~10 범위로 표시합니다 (분류). 왜 각 영화에 대해 여러 개의 예측 행이 있는지 궁금할 것입니다. 이는 모델이 가능한 예측 라벨과 각 라벨의 발생 확률을 내림차순으로 반환했기 때문입니다.

예측 결과 및 모델 분석:

예측으로 두 가지 훌륭한 분석 단계를 수행하여 결과를 이해할 수 있습니다.

모델이 이러한 예측 결과를 생성하는 이유를 이해하려면 ML.EXPLAIN_PREDICT 함수를 사용하면 됩니다.
일반적으로 소득 계층을 결정하는 데 가장 중요한 특성이 무엇인지 알아보려면 ML.GLOBAL_EXPLAIN 함수를 사용하면 됩니다.

이 단계에 관한 자세한 내용은 문서를 참고하세요.

6. 데이터에서 생성형 AI로

SQL 쿼리만 사용하는 Vertex AI의 text-bison (최신) 모델을 생성형 AI에 사용하여 LLM (대규모 언어 모델)에 영화 점수에 영향을 미치는 요인의 요약을 요청하여 5점을 초과하도록 하여 영화 데이터 세트에 대한 인사이트를 제공해 보겠습니다.

여기서 만든 movie_score 테이블이 이 단계의 입력도 됩니다.
BigQuery ML과 Vertex 서비스 간의 액세스를 설정하기 위해 외부 연결이 생성됩니다.
BigQuery GENERATE_TEXT 구성은 Vertex AI에서 원격으로 PaLM API를 호출하는 데 사용됩니다.

7. 외부 연결 만들기

아직 사용 설정하지 않았다면 BQ Connection API를 사용 설정하고 연결 구성 세부정보에서 서비스 계정 ID를 기록해 둡니다.

BigQuery 탐색기 창 (BigQuery 콘솔 왼쪽)에서 +추가 버튼을 클릭하고 '외부 데이터 소스에 연결'을 클릭합니다. 목록에 나온 인기 매체에서
연결 유형을 'BigLake 및 원격 함수'로 선택하고 위치 유형을 '지역'으로 입력합니다. 값은 'us-central1 (아이오와)'입니다. 'bq_llm_connection' 연결 ID로

연결이 생성되면 연결 구성 세부정보에서 생성된 서비스 계정을 기록해 둡니다.

권한 부여

이 단계에서는 서비스 계정에 Vertex AI 서비스에 액세스할 수 있는 권한을 부여합니다.

IAM을 열고 외부 연결을 주 구성원으로 만든 후 복사한 서비스 계정을 추가한 다음 'Vertex AI 사용자'를 선택합니다. 역할

8. 원격 ML 모델 만들기

호스팅된 Vertex AI 대규모 언어 모델을 나타내는 원격 모델을 만듭니다.

CREATE OR REPLACE MODEL
  movie_insights.llm_model REMOTE
WITH CONNECTION `us-central1.bq_llm_connection` OPTIONS (remote_service_type = 'CLOUD_AI_LARGE_LANGUAGE_MODEL_V1');

Vertex AI의 CLOUD_AI_LARGE_LANGUAGE_MODEL_V1 API를 원격 함수로 활용하는 movie_insights 데이터 세트에 llm_model이라는 모델을 만듭니다. 완료되는 데 몇 초 정도 걸릴 수 있습니다.

9. ML 모델을 사용하여 텍스트 생성

모델이 생성되면 이 모델을 사용하여 텍스트를 생성, 요약 또는 분류합니다.

SELECT
  ml_generate_text_result['predictions'][0]['content'] AS generated_text,
  ml_generate_text_result['predictions'][0]['safetyAttributes']
    AS safety_attributes,
  * EXCEPT (ml_generate_text_result)
FROM
  ML.GENERATE_TEXT(
    MODEL `movie_insights.llm_model`,
    (
 SELECT
      CONCAT('FROM THE FOLLOWING TEXT ABOUT MOVIES, WHAT DO YOU THINK ARE THE FACTORS INFLUENCING A MOVIE SCORE TO BE GREATER THAN 5?: ', movie_data) AS prompt
    FROM (
      SELECT
        REPLACE(STRING_AGG( CONCAT('A movie named ',name, ' from the country ', country, ' with a censor rating of ',rating, ' and a budget of ', budget, ' produced by ', company, ' with a runtime of about ', runtime, ' and in the genre ', genre, ' starring ', star, ' has had a success score of ', score, '') ), ',','. ') AS movie_data
      FROM (
        SELECT
          *
        FROM
          `movie_insights.movie_score`
        WHERE
          CAST(SCORE AS INT64) > 5
        LIMIT
          50) ) AS MOVIES
    ),
    STRUCT(
      0.2 AS temperature,
      100 AS max_output_tokens));

**설명:

ml_generate_text_result** 는 콘텐츠와 안전 속성을 모두 포함하는 JSON 형식의 텍스트 생성 모델의 응답입니다. a. 콘텐츠는 생성된 텍스트 결과 b를 나타냅니다. 안전 속성은 대규모 언어 모델에서 의도하지 않거나 예상치 못한 응답을 방지하기 위해 Vertex AI Palm API에서 사용 설정된 조정 가능한 기준점이 있는 기본 제공 콘텐츠 필터를 나타냅니다. 안전 기준점을 위반하면 응답이 차단됩니다.

ML.GENERATE_TEXT는 텍스트 생성 작업을 수행하기 위해 BigQuery에서 Vertex AI LLM에 액세스하는 데 사용하는 구조입니다.

CONCAT는 PROMPT 문과 데이터베이스 레코드를 추가합니다.

movie_insights는 데이터 세트 이름이고 movie_score는 프롬프트 설계에 사용할 데이터가 있는 테이블의 이름입니다.

Temperature는 응답의 무작위성을 제어하기 위한 프롬프트 매개변수로, 관련성 측면에서는 낮을수록 좋습니다.

Max_output_tokens는 응답을 포함할 단어의 수입니다.

쿼리 응답은 다음과 같습니다.

보시다시피 응답이 중첩되고 형식이 지정되지 않습니다.

10. 쿼리 결과 평면화

쿼리에서 JSON을 명시적으로 디코딩할 필요가 없도록 결과를 평면화해 보겠습니다.

SELECT
  *
FROM
  ML.GENERATE_TEXT( MODEL movie_insights.llm_model,
    (
    SELECT
      CONCAT('FROM THE FOLLOWING TEXT ABOUT MOVIES, WHAT DO YOU THINK ARE THE FACTORS INFLUENCING A MOVIE SCORE TO BE GREATER THAN 5?: ', movie_data) AS prompt
    FROM (
      SELECT
        REPLACE(STRING_AGG( CONCAT('A movie named ',name, ' from the country ', country, ' with a censor rating of ',rating, ' and a budget of ', budget, ' produced by ', company, ' with a runtime of about ', runtime, ' and in the genre ', genre, ' starring ', star, ' has had a success score of ', score, '') ), ',','. ') AS movie_data
      FROM (
        SELECT
          *
        FROM
          `movie_insights.movie_score`
        WHERE
          CAST(SCORE AS INT64) > 5
        LIMIT
          50) ) AS MOVIES),
    STRUCT( 0.2 AS temperature,
      100 AS max_output_tokens,
      TRUE AS flatten_json_output));

**설명:

Flatten_json_output** 은 불리언을 나타내며, true로 설정되면 JSON 응답에서 추출된 이해 가능한 평면적 텍스트를 반환합니다.

쿼리 응답은 다음과 같습니다.

11. 삭제

이 게시물에서 사용한 리소스 비용이 Google Cloud 계정에 청구되지 않도록 하려면 Vertex AI 엔드포인트 페이지로 이동하여 ML 단계에서 만든 Vertex AI 엔드포인트를 삭제하면 됩니다.

12. 축하합니다

축하합니다. SQL 쿼리만 사용하여 영화 데이터 세트에 Vertex AI API를 사용하여 BQML 모델을 만들고 LLM 기반 분석을 수행했습니다. Vertex AI LLM 제품 문서에서 사용 가능한 모델에 대해 자세히 알아보세요.