[개인 프로젝트] FC Online 공식 경기 분석 (4) - Snowflake 기본 설정 및 COPY

이전에 S3 - Snowflake - Preset 인프라를 구성하고, 스크래핑 코드를 수정해 주었다. 이번에는 Snowflake에서 S3의 csv 파일을 가져와 데이터베이스로 저장할 수 있도록 Worksheets를 작성할 것이다.

[개인 프로젝트] FC Online 공식 경기 분석 (3) - 인프라 구성(S3, Snowflake, Preset)

수정 사항

Primary Key - matchId -> id

matchId가 매치 별 하나씩 존재하는 줄 알고 Primary Key로 사용하려고 했지만, 확인해 보니 두 유저의 기록이 같은 matchId로 기록되는 것을 확인하였다. 이에 따라 matchId는 Primary Key로 사용할 수 없고, id 컬럼을 새로 만들어 Primary Key로 사용한다.

Snowflake Worksheets

Database, Schema 생성

fc_online 데이터베이스를 생성한 뒤 그 밑에 raw_data와 analytics Schema를 생성하였다. raw_data에 csv에 저장된 데이터를 저장할 것이고, 대시보드를 위한 ELT로 제작된 데이터를 analytics에 저장할 것이다.

-- CREATE DATABASE
CREATE DATABASE fc_online;

-- CREATE SCHEMA
CREATE SCHEMA fc_online.raw_data;
CREATE SCHEMA fc_online.analytics;

raw_data 테이블 생성

id - Primary Key

Primary Key로 설정할 컬럼이 존재하지 않았기 때문에 각 row 별로 id를 부여하여 Primary Key로 설정하기 위해 SEQUENCE를 생성해 주었다. 테이블이 4개이기 때문에 총 4개의 SEQUENCE를 생성해 주었다.

-- CREATE SEQUENCE
CREATE OR REPLACE SEQUENCE id_seq_1 START WITH 1 INCREMENT BY 1; 
CREATE OR REPLACE SEQUENCE id_seq_2 START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE SEQUENCE id_seq_3 START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE SEQUENCE id_seq_4 START WITH 1 INCREMENT BY 1;

CREATE TABLE

raw_data Schema에 저장될 4개의 테이블을 생성하였다. 위에서 생성한 SEQUENCE를 Primary Key로 설정해 주었다.

-- CREATE TABLE
CREATE OR REPLACE TABLE fc_online.raw_data.match_detail (
  id integer default id_seq_1.nextval primary key,
  matchId varchar(100),
  seasonId integer,
  matchResult varchar(32),
  matchEndType integer,
  systemPause integer,
  foul integer,
  injury integer,
  redCards integer,
  yellowCards integer,
  dribble integer,
  cornerKick integer,
  possession integer,
  offsideCount integer,
  averageRating float,
  controller varchar(64)
);

-- CREATE TABLE
CREATE OR REPLACE TABLE fc_online.raw_data.shoot (
  id integer default id_seq_2.nextval primary key,
  matchId varchar(100),
  shootTotal integer,
  effectiveShootTotal integer,
  shootOutScore integer,
  goalTotal integer,
  goalTotalDisplay integer,
  ownGoal integer,
  shootHeading integer,
  goalHeading integer,
  shootFreekick integer,
  goalFreekick integer,
  shootInPenalty integer,
  goalInPenalty integer,
  shootOutPenalty integer,
  goalOutpenalty integer,
  shootPenaltyKick integer,
  goalPenaltykick integer
);

-- CREATE TABLE
CREATE OR REPLACE TABLE fc_online.raw_data.pass (
  id integer default id_seq_3.nextval primary key,
  matchId varchar(100),
  passTry integer,
  passSuccess integer,
  shortPassTry integer,
  shortPassSuccess integer,
  longPassTry integer,
  longPassSuccess integer,
  bouncingLobPassTry integer,
  bouncingLobPassSuccess integer,
  drivenGroundPassTry integer,
  drivenGroundPassSuccess integer,
  throughPassTry integer,
  throughPassSuccess integer,
  lobbedThroughPassTry integer,
  lobbedThroughPassSuccess integer
);

-- CREATE TABLE
CREATE OR REPLACE TABLE fc_online.raw_data.defence (
  id integer default id_seq_4.nextval primary key,
  matchId varchar(100),
  blockTry integer,
  blockSuccess integer,
  tackleTry integer,
  tackleSuccess integer
);

S3의 CSV 파일 COPY

COPY 명령어로 S3에 있는 csv 파일을 테이블에 벌크 업데이트를 진행해 주었다.

-- COPY
COPY INTO fc_online.raw_data.match_detail (matchId, seasonId, matchResult, matchEndType, systemPause, foul, injury, redCards, yellowCards, dribble, cornerKick, possession, offsideCount, averageRating, controller)
FROM 's3://~/match_detail.csv'
credentials=(AWS_KEY_ID='-' AWS_SECRET_KEY='-')
FILE_FORMAT = (type='CSV' skip_header=1);

COPY INTO fc_online.raw_data.shoot (matchId, shootTotal, effectiveShootTotal, shootOutScore, goalTotal, goalTotalDisplay, ownGoal, shootHeading, goalHeading, shootFreekick, goalFreekick, shootInPenalty, goalInPenalty, shootOutPenalty, goalOutPenalty, shootPenaltyKick, goalPenaltyKick)
FROM 's3://~/shoot.csv'
credentials=(AWS_KEY_ID='-' AWS_SECRET_KEY='-')
FILE_FORMAT = (type='CSV' skip_header=1);

COPY INTO fc_online.raw_data.pass (matchId, passTry, passSuccess, shortPassTry, shortPassSuccess, longPassTry, longPassSuccess, bouncingLobPassTry, bouncingLobPassSuccess, drivenGroundPassTry, drivenGroundPassSuccess, throughPassTry, throughPassSuccess, lobbedThroughPassTry, lobbedThroughPassSuccess)
FROM 's3://~/pass.csv'
credentials=(AWS_KEY_ID='-' AWS_SECRET_KEY='-')
FILE_FORMAT = (type='CSV' skip_header=1);

COPY INTO fc_online.raw_data.defence (matchId, blockTry, blockSuccess, tackleTry, tackleSuccess)
FROM 's3://~/defence.csv'
credentials=(AWS_KEY_ID='-' AWS_SECRET_KEY='-')
FILE_FORMAT = (type='CSV' skip_header=1);

중복 행 제거

스크래핑이 진행되는 과정에서 중복 행이 존재할 수 있기 때문에 DISTINCT를 사용해 중복을 제거해 주었다.

-- DUPLICATE
CREATE OR REPLACE TABLE fc_online.raw_data.match_detail AS
SELECT DISTINCT *
FROM fc_online.raw_data.match_detail;

CREATE OR REPLACE TABLE fc_online.raw_data.shoot AS
SELECT DISTINCT *
FROM fc_online.raw_data.shoot;

CREATE OR REPLACE TABLE fc_online.raw_data.pass AS
SELECT DISTINCT *
FROM fc_online.raw_data.pass;

CREATE OR REPLACE TABLE fc_online.raw_data.defence AS
SELECT DISTINCT *
FROM fc_online.raw_data.defence;

결과

결과 - defence

다음에는 Snowflake의 raw_data Schema에 존재하는 데이터를 가지고 analytics Schema에 생성할 데이터를 만들어볼 것이다.

[개인 프로젝트] FC Online 공식 경기 분석 (5) - Snowflake analytics 테이블 생성