GCP Settings 2022 ver

GCP, BigQuery, Python
Page content

개요

  • GCP 빅쿼리를 연동하는 예제를 구현한다.
  • 먼저 빅쿼리를 통해 데이터를 적재하는 예제를 확인한다.
  • 구글 코랩에서 빅쿼리 데이터를 불러온다.
  • 데이터 스튜디오에서 빅쿼리 데이터를 불러온다.

소개

Google Cloud 회원가입

  • 준비물
    • Google 계정
    • 신용카드나 체크카드 (개인적으로 돈이 없는 체크카드 사용 권장)
  • 구글 클라우드 사이트 접속
  • 무료 서버 받으려면 아래 화면에서 TRY IT FREE 를 클릭한다.

Untitled

  • 가입 시, 무료 크레디트 300달러를 부여하며 3개월 정도 기간 부여됨

Untitled

  • 가입을 위해 카드 정보를 입력하면 바로 가입이 완료가 된다.

BigQuery 들어가기

  • 구글 클라우드 콘솔 화면은 다음과 같다.

Untitled

  • 먼저 왼쪽 상단에서 신규 프로젝트를 생성한다.

Untitled

  • 왼쪽 메뉴 바에서 스크롤을 계속 내리면 하단에서 BigQuery 메뉴를 클릭할 수 있다.

Untitled

  • 검색창에서 public을 검색하고 실행한다.

Untitled

  • Found 1 result. Broaden search to all projects 영역을 클릭하면 public 데이터를 확인할 수 있다.

Untitled

쿼리 테스트

  • SQL 쿼리를 테스트한다.
SELECT count(*) FROM `bigquery-public-data.covid19_public_forecasts.county_14d`
  • 결과창에 다음과 같이 나오면 정상적으로 쿼리가 들어간 것을 확인할 수 있다.

Untitled

  • 이번엔 조금 복잡한 코드를 작성한다.
SELECT EXTRACT(YEAR FROM starttime) AS year,
    EXTRACT(MONTH FROM starttime) AS month,
    COUNT(starttime) AS number_one_way
FROM
    `bigquery-public-data.new_york_citibike.citibike_trips`
WHERE
    start_station_name != end_station_name
GROUP BY year, month
ORDER BY year ASC, month ASC
  • 위 쿼리 결과의 값은 아래와 같다.

Untitled

데이터 추가

  • 이번엔 데이터를 추가하도록 한다.

    • 빅쿼리에 데이터 추가 방법은 여러가지 있다. 그러나 여기에서는 빅쿼리 GUI에서 업로드 하는 방법을 소개한다.
    • 해당 파일을 내려받는다. mascots.csv

  • 프로젝트 설정 창에서 Open 을 클릭한다.

  • 아래 화면에서 먼저 CREATE DATASET을 클릭한다.

Untitled

  • 먼저 아래와 같이 Create Dataset을 설정한다.
    • 지역은 US 기준으로 설정하는 것을 추천한다.

Untitled

  • 동일한 방식으로 Create Table을 클릭한다.

Untitled

  • mascots.csv 파일을 불러오면 아래와 같이 설정을 하면 끝이다.

Untitled

  • 쿼리 테스트를 통해 데이터를 조회한다.
SELECT * FROM `bigquery-public-data-338604.baseketbll.mascots` LIMIT 10

Untitled

  • 이번에는 외부 데이터와 함께 쿼리를 작성해본다.
    • 검색창에서 ncaa를 검색한 뒤, ncaa_basketball 데이터를 pin project로 고정한다.
WITH matchups AS (
SELECT g.win_team_id
  , g.lose_team_id
  , (SELECT win_masc.tax_family FROM `basketball.mascots` win_masc WHERE win_masc.id = g.win_team_id) AS tax_family_winner
  , (SELECT win_masc.tax_genus FROM `basketball.mascots` win_masc WHERE win_masc.id = g.win_team_id) AS tax_genus_winner
  , (SELECT lose_masc.tax_family FROM `basketball.mascots` lose_masc WHERE lose_masc.id = g.lose_team_id) AS tax_family_loser
  , (SELECT lose_masc.tax_genus FROM `basketball.mascots` lose_masc WHERE lose_masc.id = g.lose_team_id) AS tax_genus_loser
FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` g
)
SELECT
  SUM(IF(tax_family_winner = "Felidae" AND tax_genus_loser = "Canis", 1, 0)) AS num_cat_wins,
  SUM(IF(tax_genus_winner = "Canis" AND tax_family_loser = "Felidae", 1, 0)) AS num_dog_wins
FROM matchups

Untitled

구글 코랩 연동 (with Python)

  • 이번에는 구글 코랩에서 파이썬을 활용하여 해당 코드를 직접 실행하는 코드를 작성한다.
  • 먼저 계정인증을 진행한다.
from google.colab import auth
auth.authenticate_user()
print('Authenticated')
  • 테이블을 Interactive하게 보여주기 위해 아래 코드를 실행한다.
%load_ext google.colab.data_table
  • 이번에는 프로젝트 id를 설정하고, 쿼리를 작성하여 결괏값을 확인한다.
from google.cloud import bigquery

project_id = 'bigquery-public-data-338604'
client = bigquery.Client(project=project_id)

df = client.query('''
  SELECT * FROM `basketball.mascots`
''').to_dataframe()

df

Untitled

  • 빅쿼리 콘솔에서 진행했던 해당 쿼리를 재 진행한다. 결괏값이 동일하면 된 것이다.
df = client.query('''
  WITH matchups AS (
  SELECT g.win_team_id
  , g.lose_team_id
  , (SELECT win_masc.tax_family FROM `basketball.mascots` win_masc WHERE win_masc.id = g.win_team_id) AS tax_family_winner
  , (SELECT win_masc.tax_genus FROM `basketball.mascots` win_masc WHERE win_masc.id = g.win_team_id) AS tax_genus_winner
  , (SELECT lose_masc.tax_family FROM `basketball.mascots` lose_masc WHERE lose_masc.id = g.lose_team_id) AS tax_family_loser
  , (SELECT lose_masc.tax_genus FROM `basketball.mascots` lose_masc WHERE lose_masc.id = g.lose_team_id) AS tax_genus_loser
  FROM `bigquery-public-data.ncaa_basketball.mbb_historical_tournament_games` g
  )
  SELECT
    SUM(IF(tax_family_winner = "Felidae" AND tax_genus_loser = "Canis", 1, 0)) AS num_cat_wins,
    SUM(IF(tax_genus_winner = "Canis" AND tax_family_loser = "Felidae", 1, 0)) AS num_dog_wins
  FROM matchups
''').to_dataframe()

df

Untitled

데이터 스튜디오 with 빅쿼리

Untitled

  • Create - Report 클릭한다.

Untitled

  • Google Connectors에서 BigQuery를 클릭한다.
    • 필자는 iris.csv 파일을 업로드 하였다.

Untitled

  • 위와 같이 순차적으로 클릭을 한 뒤 ADD 버튼을 클릭한다.

Untitled

  • ADD TO REPORT를 클릭한다.
    • 이제 데이터 스튜디오에서 시각화 차트를 작성할 수 있다.

Untitled