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 URL을 입력하여 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까지

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

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';

Query Details:

CREATE MODEL 문은 SELECT 문의 학습 데이터를 사용하여 모델을 학습시킵니다.
OPTIONS 절은 모델 유형과 학습 옵션을 지정합니다. 여기서 LOGISTIC_REG 옵션은 로지스틱 회귀 모델 유형을 지정합니다. 바이너리 로지스틱 회귀 모델과 멀티클래스 로지스틱 회귀 모델을 구분하여 지정할 필요는 없습니다. BigQuery ML은 라벨 열의 고유 값 수를 기반으로 학습할 대상을 결정할 수 있습니다.
data_split_method='NO_SPLIT'은 BQML이 쿼리 조건 (data_cat = 'TRAIN')에 따라 데이터를 학습하도록 강제합니다. 또한 이 옵션에서 'AUTO_SPLIT'을 사용하여 프레임워크 (또는 이 경우 서비스)가 학습/테스트 분할의 파티션을 무작위로 지정하도록 하는 것이 좋습니다.
input_label_cols 옵션은 SELECT 문에서 라벨 열로 사용할 열을 지정합니다. 여기에서 라벨 열은 score이므로 모델은 각 행에 있는 다른 값을 기반으로 score의 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 작업공간의 스키마 섹션에 아래와 같이 표시됩니다.

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

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

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

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

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'
     )
  );

결과는 다음과 같습니다.

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

예측 결과 및 모델 분석:

예측을 통해 결과를 이해하기 위한 두 가지 분석 단계를 수행할 수 있습니다.

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

자세한 내용은 문서를 참고하세요.

6. 데이터를 생성형 AI에

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

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

7. 외부 연결 만들기

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

BigQuery 탐색기 창 (BigQuery 콘솔의 왼쪽)에서 +추가 버튼을 클릭하고 나열되는 인기 소스에서 '외부 데이터 소스에 연결'을 클릭합니다.
연결 유형으로 'BigLake 및 원격 함수'를 선택하고, 위치 유형으로 '리전'을, 값으로 'us-central1 (아이오와)'를, 연결 ID로 'bq_llm_connection'을 제공합니다.

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

권한 부여

이 단계에서는 서비스 계정에 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 형식 텍스트 생성 모델의 응답입니다. 콘텐츠는 생성된 텍스트 결과 b를 나타냅니다. 안전 속성은 대규모 언어 모델에서 의도하지 않거나 예상치 못한 응답을 방지하기 위해 Vertex AI Palm API에서 사용 설정되는 조정 가능한 기준점이 있는 기본 제공 콘텐츠 필터를 나타냅니다. 안전 기준점을 위반하는 응답은 차단됩니다.

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

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

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 제품 문서를 참고하세요.