BigQuery ML을 사용하여 농구 결과 예측

이 가이드에서는 BigQuery ML을 사용하여 농구 경기의 3점슛 야투 시도를 예측합니다. BigQuery ML을 사용하면 BigQuery에서 SQL 쿼리를 사용하여 머신러닝 모델을 만들고 실행할 수 있습니다. BigQuery ML의 목표는 SQL 실무자가 기존 도구를 사용하여 빌드할 수 있도록 지원하여 머신러닝을 대중화하고 데이터 이동의 필요성을 제거하여 개발 속도를 향상시키는 것입니다.

이 가이드에서는 BigQuery용 NCAA 농구 데이터 공개 데이터세트 샘플을 사용하여 3점슛 야투 시도를 예측하는 모델을 만듭니다.

목표

이 가이드에서는 다음을 사용합니다.

  • CREATE MODEL 문을 사용하여 선형 회귀 모형을 만들기 위한 BigQuery ML
  • ML 모델을 평가하는 ML.EVALUATE 함수
  • ML 모델을 사용하여 예측을 수행하는 ML.PREDICT 함수

비용

이 가이드는 비용이 청구될 수 있는 다음과 같은 Cloud Platform 구성요소를 사용합니다.

  • BigQuery
  • BigQuery ML
  • Cloud Datalab

BigQuery 비용에 대한 자세한 내용은 BigQuery 가격 책정 페이지를 참조하세요.

BigQuery ML 비용에 대한 자세한 내용은 BigQuery ML 가격 책정 페이지를 참조하세요.

Cloud Datalab 비용에 대한 자세한 내용은 Cloud Datalab 가격 책정 페이지를 참조하세요.

시작하기 전에

  1. Google 계정에 로그인합니다.

    아직 계정이 없으면 새 계정을 등록하세요.

  2. Google Cloud Platform 프로젝트를 선택하거나 만듭니다.

    리소스 관리 페이지로 이동

  3. Google Cloud Platform 프로젝트에 결제가 사용 설정되어 있는지 확인하세요.

    결제 사용 설정 방법 알아보기

  4. 새 프로젝트에서는 BigQuery가 자동으로 사용 설정됩니다. 기존 프로젝트에서 BigQuery를 활성화하려면 다음으로 이동하세요. BigQuery API를 사용 설정합니다.

    API 사용 설정

1단계: Cloud Datalab 설정

이 가이드에서는 Cloud Shell을 사용하여 Cloud Datalab을 설정합니다. Cloud Datalab은 BigQuery ML 및 BigQuery 작업에 사용하는 Jupyter 기반 노트북 환경을 제공합니다.

Cloud Datalab을 설정하기 전에 Compute Engine API 및 Cloud Source Repositories API를 사용 설정해야 합니다. 이러한 API를 사용 설정하는 방법에 대한 자세한 내용은 서비스 사용 설정 및 중지를 참조하세요.

Compute Engine VM에서 Cloud Datalab을 설정하려면 다음 안내를 따르세요.

  1. GCP Console로 이동합니다.

    GCP Console

  2. Cloud Shell 활성화를 클릭합니다.

    Cloud Shell 활성화 아이콘

  3. Cloud Shell 창에서 다음 명령어를 입력하여 gcloud 도구에서 us-central1-a를 Cloud SDK의 기본 영역으로 사용하도록 구성합니다. 이 영역에는 Cloud Datalab 인스턴스가 포함됩니다.

    gcloud config set compute/zone us-central1-a
    
  4. 다음 명령어를 입력하여 Cloud Datalab 컨테이너를 실행하는 데 사용되는 mltutorial이라는 Compute Engine VM을 만듭니다. 이 명령어는 VM에 대한 SSH 연결도 만들고 원격 Cloud Datalab 웹 인터페이스를 localhost 포트 8081에 매핑합니다. 메시지가 표시되면 기본 Compute Engine 영역에 해당하는 번호를 입력합니다.

    datalab create mltutorial
    

    The connection to Datalab is now open and will remain until this command is killed라는 메시지가 표시되면 인스턴스에 연결된 것입니다. SSH 연결이 설정되면 명령줄 인터페이스가 더 이상 출력을 생성하지 않을 수 있습니다.

  5. Cloud Shell 창에서 웹 미리보기 아이콘 웹 미리보기 아이콘을 클릭합니다.

  6. 포트 변경을 선택합니다.

  7. 미리보기 포트 변경 대화상자에서 8081을 입력한 다음 변경 및 미리보기를 클릭합니다. 그러면 새 탭에서 Cloud Datalab이 열립니다.

    gcloud compute ssh --quiet --zone "us-central1-a" --ssh-flag="-N" --ssh-flag="-L" --ssh-flag="localhost:8081:localhost:8080" "${USER}@mltutorial"
    
  8. 노트북을 클릭하여 새 노트북을 만듭니다. 노트북이 새 탭에 열립니다.

  9. 제목 표시줄에서 제목 없는 노트북을 클릭하여 노트북 이름을 변경합니다.

  10. 노트북 이름 변경 대화상자에서 BigQuery ML tutorial을 입력한 다음 확인을 클릭합니다.

  11. 첫 번째 코드 셀에서 다음 명령어를 입력하여 BigQuery Python 클라이언트 라이브러리의 최신 버전으로 업데이트합니다.

    !pip install --upgrade google-cloud-bigquery
    
  12. 실행 > 이 셀에서 실행을 클릭합니다. 쿼리 결과가 코드 블록 아래에 나타납니다. 설치가 완료되면 다음 메시지가 표시됩니다.

    Successfully installed ...

  13. 이 코드에서 생성되는 출력 양이 꽤 많습니다. 출력을 숨기려면 셀 메뉴를 클릭하고 접기를 선택하여 셀을 접습니다.

    셀 메뉴

  14. 코드 추가를 클릭하여 새 코드 셀을 만듭니다.

  15. 다음 코드를 입력하여 BigQuery Python 클라이언트 라이브러리를 가져오고 클라이언트를 초기화합니다. BigQuery 클라이언트는 BigQuery API에서 메시지를 주고받는 데 사용됩니다.

    from google.cloud import bigquery
    client = bigquery.Client()
  16. 실행 > 이 셀에서 실행을 클릭합니다. 이 명령어는 출력을 생성하지 않습니다.

2단계: 데이터세트 만들기

다음으로 ML 모델을 저장할 BigQuery 데이터세트를 만듭니다. 데이터세트를 만들려면 다음 안내를 따르세요.

  1. 코드 추가를 클릭하여 새 코드 셀을 만듭니다.

  2. 다음 명령어를 입력하여 데이터세트를 만듭니다.

    dataset = bigquery.Dataset(client.dataset('bqml_tutorial'))
    dataset.location = 'US'
    client.create_dataset(dataset)
  3. 실행 > 이 셀에서 실행을 클릭합니다. 명령어 출력은 다음과 같이 표시됩니다.

    Dataset '[project_ID]:bqml_tutorial' successfully created.

3단계: 학습 및 특성 데이터 생성

다음으로 학습 및 특성 데이터를 생성합니다. 먼저 NCAA 농구 데이터 공개 데이터세트의 bigquery-public-data.ncaa_basketball.mbb_teams_games_sr 테이블을 쿼리하여 모델의 입력 특성을 생성합니다. 입력 특성을 생성한 후에는 학습 데이터를 생성합니다.

입력 특성 생성(특성 추출)

입력 특성에는 다른 시간대를 사용하는 홈팀과 원정팀의 이전 경기 통계 평균 및 표준 편차가 포함됩니다. 사용되는 시간대는 현재 경기 전에 열린 10, 5, 1개의 경기입니다. 홈팀과 원정팀 모두의 team-id 열과 season 열은 원-핫 특성으로도 사용되므로 모델의 평균 통계에 영향을 미칩니다.

입력 특성을 생성하는 데 사용되는 쿼리 길이 때문에 기본 BigQuery 웹 UI의 저장된 쿼리를 사용하여 테이블을 만듭니다.

입력 특성 데이터를 생성하려면 다음 안내를 따르세요.

  1. 아래 버튼을 클릭하여 입력 데이터 쿼리를 엽니다.

    특성 입력 쿼리 열기

    시간이 되면 쿼리를 검사합니다.

  2. 쿼리 창 아래에서 옵션 표시를 클릭합니다.

  3. 대상 테이블 섹션에서 테이블 선택을 클릭합니다.

  4. 대상 테이블 선택 대화상자에서 다음 안내를 따르세요.

    1. 프로젝트에서 프로젝트를 선택합니다.
    2. 데이터세트에서 bqml_tutorial을 선택합니다.
    3. 테이블 ID 필드에서 cume_games를 입력합니다.
    4. 확인을 클릭합니다.
  5. 쿼리 실행을 클릭합니다. 그러면 지정한 테이블에 쿼리 결과를 쓰는 쿼리 작업이 생성됩니다.

    또는 쿼리를 실행하기 전에 대상 테이블을 지정하는 것을 잊은 경우 결과 창에서 테이블로 저장 버튼을 클릭하여 임시 테이블을 영구 테이블에 복사할 수 있습니다.

  6. 쿼리를 완료하는 데는 몇 초 정도 걸립니다. 쿼리를 실행한 후 BigQuery 웹 UI의 탐색 패널에 테이블(cume_games)이 나타납니다.

학습 데이터 생성

입력 특성을 생성한 후에는 학습 데이터를 생성합니다. 학습 데이터는 bqml_tutorial.wide_games 테이블에 작성됩니다. 학습 데이터를 생성할 때 결과 예측에 사용할 경기를 제외합니다.

이 쿼리에서는 이전 쿼리를 실행하여 만든 cume_games 테이블을 자체 조인합니다. 이렇게 자체 조인하여 현재 경기 전의 홈팀과 원정팀 통계를 모두 생성합니다.

학습 데이터를 생성하는 데 사용되는 쿼리 길이 때문에 기본 BigQuery 웹 UI의 저장된 쿼리를 사용하여 테이블을 만듭니다.

학습 데이터를 생성하려면 다음 안내를 따르세요.

  1. 아래 버튼을 클릭하여 입력 데이터 쿼리를 엽니다.

    학습 데이터 쿼리 열기

    시간이 되면 쿼리를 검사합니다.

  2. 쿼리 창 아래에서 옵션 표시를 클릭합니다.

  3. 대상 테이블 섹션에서 테이블 선택을 클릭합니다.

  4. 대상 테이블 선택 대화상자에서 다음 안내를 따르세요.

    1. 프로젝트에서 프로젝트를 선택합니다.
    2. 데이터세트에서 bqml_tutorial을 선택합니다.
    3. 테이블 ID 필드에서 wide_games를 입력합니다.
    4. 확인을 클릭합니다.
  5. 쿼리 실행을 클릭합니다. 그러면 지정한 테이블에 쿼리 결과를 쓰는 쿼리 작업이 생성됩니다.

    또는 쿼리를 실행하기 전에 대상 테이블을 지정하는 것을 잊은 경우 결과 창에서 테이블로 저장 버튼을 클릭하여 임시 테이블을 영구 테이블에 복사할 수 있습니다.

  6. 쿼리를 완료하는 데는 몇 초 정도 걸립니다. 쿼리를 실행한 후 BigQuery 웹 UI의 탐색 패널에 테이블(wide_games)이 나타납니다.

4단계: 모델 만들기

다음으로 선형 회귀 모형을 만듭니다. 이 모델은 이전 경기 통계를 기반으로 조합된 3점슛 야투 시도를 예측하는 데 사용됩니다. 표준 SQL 쿼리는 CREATE MODEL 문을 사용하여 모델을 만듭니다. CREATE MODEL 문으로 모델을 만들고 학습시킬 수 있지만 이 가이드에서는 모델을 별도로 학습시킵니다.

CREATE MODEL 쿼리를 실행하여 모델을 만들려면 다음 안내를 따르세요.

  1. Cloud Datalab 노트북으로 전환합니다.

  2. 코드 추가를 클릭하여 새 코드 셀을 만듭니다.

  3. BigQuery Python 클라이언트 라이브러리는 최소한의 코드로 쿼리를 실행할 수 있는 매직 명령어를 제공합니다. 클라이언트 라이브러리에서 매직 명령어를 로드하려면 다음 코드를 입력합니다.

    %load_ext google.cloud.bigquery
    
  4. 실행 > 이 셀에서 실행을 클릭합니다. 이 명령어는 출력을 생성하지 않습니다.

  5. 코드 추가를 클릭하여 새 코드 셀을 만듭니다.

  6. BigQuery 클라이언트 라이브러리는 SQL 쿼리를 실행하고 Pandas DataFrame으로 결과를 반환하는 셀 매직 %%bigquery를 제공합니다. 셀에 다음 표준 SQL 쿼리를 입력합니다. #standardSQL 프리픽스는 이 클라이언트 라이브러리에 필요하지 않습니다. 표준 SQL이 기본 쿼리 구문입니다.

Jupyter

%%bigquery
CREATE OR REPLACE MODEL bqml_tutorial.ncaa_model OPTIONS(
  model_type='linear_reg', max_iteration=50) AS
SELECT
  * EXCEPT(
    game_id, season, scheduled_date,
    total_three_points_made,
    total_three_points_att),
  total_three_points_att as label
FROM
  bqml_tutorial.wide_games
WHERE
  # remove the game to predict
  game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'

Python

sql = """
    CREATE OR REPLACE MODEL `bqml_tutorial.ncaa_model`
    OPTIONS (
        model_type='linear_reg',
        max_iteration=50 ) AS
    SELECT
        * EXCEPT (
            game_id, season, scheduled_date,
            total_three_points_made,
            total_three_points_att),
        total_three_points_att as label
    FROM
        `bqml_tutorial.wide_games`
    WHERE
        # remove the game to predict
        game_id != 'f1063e80-23c7-486b-9a5e-faa52beb2d83'
"""
df = client.query(sql).to_dataframe()
print(df)

  1. 실행 > 이 셀에서 실행을 클릭합니다.

  2. 쿼리를 완료하는 데 몇 분 정도 걸립니다. 첫 번째 반복이 완료된 후 BigQuery 웹 UI의 탐색 패널에 모델(ncaa_model)이 나타납니다. 쿼리는 CREATE MODEL 문을 사용하므로 쿼리 결과가 표시되지 않습니다. 출력은 빈 문자열입니다.

5단계: 학습 통계 가져오기

모델 학습의 결과를 확인하려면 ML.TRAINING_INFO 함수를 사용하거나 BigQuery 웹 UI에서 통계를 확인하면 됩니다. 이 기능은 현재 BigQuery 기본 BigQuery 웹 UI에서 사용할 수 없습니다. 이 가이드에서는 ML.TRAINING_INFO 함수를 사용합니다.

머신러닝 알고리즘은 많은 예를 검사하고 손실을 최소화하는 모델을 찾으려고 시도함으로써 모델을 빌드합니다. 이 프로세스를 경험적 위험 최소화라고 합니다.

손실은 잘못된 예측에 대한 페널티입니다. 예를 들어 모델 예측이 얼마나 잘못되었는지를 나타내는 숫자입니다. 모델의 예측이 완벽하면 손실은 0이고 그렇지 않으면 손실은 그보다 커집니다. 모델 학습의 목표는 모든 예에서 평균적으로 손실이 적은 가중치와 편향의 집합을 찾는 것입니다.

CREATE MODEL 쿼리를 실행할 때 생성된 모델 학습 통계를 확인하려면 다음 안내를 따르세요.

  1. 코드 추가를 클릭하여 새 코드 셀을 만듭니다.

  2. 셀에 다음 표준 SQL 쿼리를 입력합니다.

Jupyter

%%bigquery
SELECT
  *
FROM
  ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)

Python

sql = """
    SELECT
        *
    FROM
        ML.TRAINING_INFO(MODEL `bqml_tutorial.ncaa_model`)
"""
df = client.query(sql).to_dataframe()
print(df)

  1. 실행 > 이 셀에서 실행을 클릭합니다.

  2. 쿼리가 완료되면 쿼리 아래에 결과가 나타납니다. 결과는 다음과 같이 표시됩니다.

    ML.TRAINING_INFO 출력

    학습 데이터 손실 열은 학습 데이터세트에서 지정된 반복 후에 계산된 손실 통계를 나타냅니다. 선형 회귀를 수행했으므로 이 열은 평균 제곱 오차입니다. 평가 데이터 손실 열은 홀드아웃 데이터세트(모델을 검증하기 위해 학습에서 보류된 데이터)에서 계산된 손실 통계와 같습니다.

    ML.TRAINING_INFO 함수에 대한 자세한 내용은 BigQuery ML 구문 참조를 확인하세요.

6단계: 모델 평가

모델을 만든 후에 ML.EVALUATE 함수를 사용하여 모델의 성능을 평가합니다.

모델을 평가하는 ML.EVALUATE 쿼리를 실행하려면 다음 안내를 따르세요.

  1. 코드 추가를 클릭하여 새 코드 셀을 만듭니다.

  2. 셀에 다음 표준 SQL 쿼리를 입력합니다.

Jupyter

%%bigquery
WITH eval_table AS (
SELECT
  *,
  total_three_points_att AS label
FROM
  `bqml_tutorial.wide_games` )
SELECT
  *
FROM
  ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
    TABLE eval_table)

Python

sql = """
    WITH eval_table AS (
        SELECT
            *,
            total_three_points_att AS label
        FROM
            `bqml_tutorial.wide_games` )
    SELECT
        *
    FROM
        ML.EVALUATE(MODEL `bqml_tutorial.ncaa_model`,
            TABLE eval_table)
"""
df = client.query(sql).to_dataframe()
print(df)

  1. 실행 > 이 셀에서 실행을 클릭합니다.

  2. 쿼리가 완료되면 쿼리 아래에 결과가 나타납니다. 결과는 다음과 같이 표시됩니다.

    ML.EVALUATE 출력

    선형 회귀를 수행했으므로 결과에 다음 열이 포함됩니다.

    • mean_absolute_error
    • mean_squared_error
    • mean_squared_log_error
    • median_absolute_error
    • r2_score
    • explained_variance

평가 결과에서 중요한 통계는 평균 절대 오차입니다. 평균 절대 오차는 예측 라벨과 실제 라벨 사이의 평균 차이입니다. 이 경우에는 약 7입니다.

7단계: 모델을 사용하여 결과 예측

모델을 평가했으므로 다음 단계는 ML.PREDICT 함수를 사용하여 2018 NCAA 결승전: 미시간 대 빌라노바의 총 3점슛 야투 시도를 예측하는 것입니다.

ML.PREDICT 함수를 사용하는 쿼리를 실행하려면 다음 안내를 따르세요.

  1. 코드 추가를 클릭하여 새 코드 셀을 만듭니다.

  2. 셀에 다음 표준 SQL 쿼리를 입력합니다.

Jupyter

%%bigquery
  WITH game_to_predict AS (
  SELECT
    *
  FROM
    `bqml_tutorial.wide_games`
  WHERE
    game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
SELECT
  truth.game_id AS game_id,
  total_three_points_att,
  predicted_total_three_points_att
FROM (
  SELECT
    game_id,
    predicted_label AS predicted_total_three_points_att
  FROM
    ML.PREDICT(MODEL `bqml_tutorial.ncaa_model`,
      table game_to_predict) ) AS predict
JOIN (
  SELECT
    game_id,
    total_three_points_att AS total_three_points_att
  FROM
    game_to_predict) AS truth
ON
  predict.game_id = truth.game_id

Python

sql = """
    WITH game_to_predict AS (
        SELECT
            *
        FROM
            `bqml_tutorial.wide_games`
        WHERE
            game_id='f1063e80-23c7-486b-9a5e-faa52beb2d83' )
    SELECT
        truth.game_id AS game_id,
        total_three_points_att,
        predicted_total_three_points_att
    FROM (
        SELECT
            game_id,
            predicted_label AS predicted_total_three_points_att
        FROM
            ML.PREDICT(MODEL `bqml_tutorial.ncaa_model`,
            table game_to_predict) ) AS predict
    JOIN (
        SELECT
            game_id,
            total_three_points_att AS total_three_points_att
        FROM
            game_to_predict) AS truth
    ON
        predict.game_id = truth.game_id
"""
df = client.query(sql).to_dataframe()
print(df)

  1. 실행 > 이 셀에서 실행을 클릭합니다.

  2. 쿼리가 완료되면 쿼리 아래에 결과가 나타납니다. 결과는 다음과 같이 표시됩니다. 모델 학습은 확정적이지 않으므로 결과가 다를 수 있습니다.

    ML.PREDICT 출력

total_three_points_att 값은 결승전에서 발생한 실제 야투 수이며 50입니다. 모델의 예측은 43.41입니다.

삭제

이 가이드에서 사용한 리소스 비용이 Google Cloud Platform 계정에 청구되지 않도록 하는 방법은 다음과 같습니다.

  • 만든 프로젝트를 삭제할 수 있습니다.
  • 또는 프로젝트를 유지하고 Cloud Datalab VM을 삭제할 수 있습니다.

Cloud Datalab VM 삭제

프로젝트를 삭제하면 Cloud Datalab VM이 삭제됩니다. Cloud Platform 프로젝트를 삭제하지 않으려는 경우 Cloud Datalab VM을 삭제할 수 있습니다.

Cloud Datalab VM을 삭제하려면 다음 안내를 따르세요.

  1. Compute Engine VM 인스턴스 페이지를 엽니다.

    VM 인스턴스 페이지로 이동

  2. mltutorial 인스턴스를 선택한 다음 삭제를 클릭합니다.

  3. 확인 메시지가 표시되면 삭제를 클릭합니다.

프로젝트 삭제

프로젝트를 삭제하는 방법은 다음과 같습니다.

  1. GCP Console에서 프로젝트 페이지로 이동합니다.

    프로젝트 페이지로 이동

  2. 프로젝트 목록에서 삭제할 프로젝트를 선택하고 삭제를 클릭합니다.
  3. 대화상자에서 프로젝트 ID를 입력한 다음 종료를 클릭하여 프로젝트를 삭제합니다.

다음 단계

이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

BigQuery ML 문서