[GCP] 3-1. How to make Dataset

< DashBoard >

  머신러닝의 모델을 구체화하기 이전에 데이터셋을 직관적으로 이해하고 이를 자료로 활용하는 것은 매우 중요한 작업이다. 이는 개발자와 데이터과학자, 최종 의사결정자에게까지 두루 설득력있는 근거로 활용할 가치가 충분하다. 이를 위해 사용하는 분석기법이 바로 탐색적 데이터 분석( EDA, Exploratory Data Analysis )이다. 아래에 보이는 표는 책에서 발췌한 대쉬보드와 EDA의 차이를 나타내는 표이다. 이를 보면 EDA의 결과를 dashboard로 나타냈을때 시각적이고 유용한 자료로 활용할 수 있다는 점을 알 수 있다.

 책에서는 DashBoard로 모델을 설명하기 위한 디자인을 구성할 때 세가지 측면에서 디자인을 평가해야된다고 말하고 있다. 

• 정확하고 정직하게 데이터를 보여주고 있는가? (원시 데이터 자체가 의사 결정의 근거가 될 경우)
• 구체화된 정보 컨텐츠가 미래을 예측하는데 얼마나 도움이 되는가?
• 사용모델에 대한 권고사항과, 해당 권고에 대한 설득력 있는 이유를 포함하는가?

 

< 실습 - DashBoard 사전 데이터 구축 >

 

   GCP의 dash-board를 이용해서 혁신적인 대화형 보고서를 만드는 과정을 살펴보겠다. Google Cloud SQL에서는 관리형 MySQL서비스를 제공한다. Cloud Instance는 gcloud command를 통해 콘솔에서 만들수도 있지만 구글 클라우드 플랫폼화면에서 먼저 만드는 과정을 살펴보고 이후에 코드와 함께 CLI에서도 만드는 과정을 살펴보겠다.

 

- Platform에서 sql instance 생성하기 - 

 

1. 먼저 cloud sql에 들어간다.                                                 2. 인스턴스 만들기 클릭

< sql 접속 >

 

3. 데이터베이스 선택                                                                         4. 인스턴스 정보 입력하고 생성

 

- Cloud Console에서 sql instance 생성하기 - 

 

Console에서 sql instance는 create로 바로 생성할 수 있다. --help 명령어로 어떤식으로 사용가능한지 알아보자.

< gcloud sql instance help >

상단의 사진에서 보는것처럼 플래그값을 어떤식으로 주느냐에 따라 굉장히 디테일하고 다양하게 sql instance를 생성할 수 있다.

 

생성예시는 다음과 같다.

gcloud sql instances create flights --tier=db-n1-standard-1 --activation-policy=ALWAYS

 

이후에 Cloud Platform의 SQL에 들어가 보면 다음과 같이 flights instance가 생성되어 있는것을 볼 수 있다.

< 생성된 flights instance 모습 >

 

 

- sql instance에 테이블 생성하기 - 

 

  이후에는 flights instance에 테이블을 생성해 보도록 하겠다. 실습과정에서는 ./create_table.sh로 sql테이블을 바로 생성해버린다. 스크립트 코드를 열어 무슨 내용으로 구성되어있는지 살펴보자. ( 관련 설명은 주석으로 대신한다. )

 

< create_table.sh 코드를 실행시키고 root-password를 입력한 모습 >

 

< create_table.sh 코드 전문 >

bash authorize_cloudshell.sh

# Connect to MySQL using its IP address and do the import
MYSQLIP=$(gcloud sql instances describe flights --format="value(ipAddresses.ipAddress)")
mysql --host=$MYSQLIP --user=root --password --verbose < create_table.sql

보아하니 cloudshell 인증과 관련된 스크립트 파일이 먼저 실행되고, MySQL 호스트의 IP주소를 알아낸다. 그 이후에 해당 IP주소에 접속하여 create_table이라는 sql 파일을 실생시키는 모습이다. 먼저 authorize_cloudshell.sh의 코드모양이 궁금하니 순서대로 열어보기로 하자.

 

1. 인증 (authorize_cloudshell.sh )

#!/bin/bash
gcloud sql instances patch flights \
	--authorized-networks $(wget -qO - http://ipecho.net/plain)/32

  인증과 관련된 코드가 단 1줄로 끝이난다. MySQL은 데이터베이스에 연결할 필요가 있을 때마다 연결하려는 시스템을 인증해야 한다. 따라서 인증된 네트워크 목록을 제공해 Cloud Shell을 인증한다. wget은 ipecho.net에 요청을 보내 요청자의 IP주소를 반환받는다.(공인IP주소를 알아내는 방법이다.)

 

2. 연결 ( MySQL Connect )

MYSQLIP=$(gcloud sql instances describe flights --format="value(ipAddresses.ipAddress)")

  MySQL을 연결하기 위해서는 해당 인스턴스의 IP주소를 연결해야 한다. 이름은 flights로 알고 있지만 IP주소는 모르기때문에 다음의 gcloud 명령어로 찾을 수 있다.

 

3. 실행 ( cloud shell에서 mysql filecode 실행 )

mysql --host=$MYSQLIP --user=root --password --verbose < create_table.sql

  마지막으로 Cloud Shell에서 MySQL을 실행하여 sql file인 create_table.sql을 실행시킨다. create_table.sql 코드는 아래에 첨부한다.

create database if not exists bts;
use bts;

drop table if exists flights;

create table flights (
  FL_DATE date,
  UNIQUE_CARRIER varchar(16),
  AIRLINE_ID varchar(16),
  CARRIER varchar(16),
  FL_NUM integer,
  ORIGIN_AIRPORT_ID varchar(16),
  ORIGIN_SEQ_ID varchar(16),
  ORIGIN_CITY_MARKET_ID varchar(16),
  ORIGIN varchar(16),
  DEST_AIRPORT_ID varchar(16),
  DEST_AIRPORT_SEQ_ID varchar(16),
  DEST_CITY_MARKET_ID varchar(16),
  DEST varchar(16),
  CRS_DEP_TIME integer,
  DEP_TIME integer,
  DEP_DELAY float,
  TAXI_OUT float,
  WHEELS_OFF integer,
  WHEELS_ON integer,
  TAXI_IN float,
  CRS_ARR_TIME integer,
  ARR_TIME integer,
  ARR_DELAY float,
  CANCELLED float,
  CANCELLATION_CODE varchar(16),
  DIVERTED float,
  DISTANCE float,
  INDEX (FL_DATE), INDEX (ORIGIN_AIRPORT_ID), INDEX(ARR_DELAY), INDEX(DEP_TIME), INDEX(DEP_DELAY)
);

  위의 sql문을 실행시킨 결과를 다음과 같이 Cloud Platform의 SQL에서 찾아볼 수 있다.

< bts db가 생성된 화면을 확인할 수 있다. >

 

- 생성한 테이블에 데이터 값 채워넣기 - 

  

  다음으로, 생성된 테이블에 각각 데이터들을 채워넣는 작업을 수행해보자. MySQL 테이블에 데이터를 채우려면 MySQL database에서 제공하는 mysqlimport 커맨드라인 도구를 사용한다. 아래의 코드는 테이블에 데이터를 채워넣기 위한 코드들이다. 코드를 보면 두개의 파일을 복사하는데 201501.csv와 201507.csv이다. 그런 다음에 mysqlimport를 실행해 로컬디스크에서 csv파일을 읽어들이고, MySQL 호스트에 계정을 연결한 다음 mysqlimport를 실행해 암호를 입력받는다. 관련 코드는 실습환경에서 populate_table.sh에 정리되어 있다.

 

※mysqlimport는 클라우드 스토리지를 직접 읽을 수 없다. 따라서 파일을 로컬 폴더에 복사한 이후, 복사된 파일을 이용함.

#!/bin/bash
if [ "$#" -ne 1 ]; then
    echo "Usage: ./populate_table.sh  bucket-name"
    exit
fi
BUCKET=$1
echo "Populating Cloud SQL instance flights from gs://${BUCKET}/flights/raw/..."

# To run mysqlimport and mysql, authorize CloudShell
bash authorize_cloudshell.sh

# the table name for mysqlimport comes from the filename, so rename our CSV files, changing bucket name as needed
counter=0
#for FILE in $(gsutil ls gs://${BUCKET}/flights/raw/2015*.csv); do
#   gsutil cp $FILE flights.csv-${counter}
for FILE in 201501.csv 201507.csv; do
   gsutil cp gs://${BUCKET}/flights/raw/$FILE flights.csv-${counter}
   counter=$((counter+1))
done

# import csv files
MYSQLIP=$(gcloud sql instances describe flights --format="value(ipAddresses.ipAddress)")

# mysqlimport 실행부분.
# --local : 로컬디스크
# --ignore-lines=1 : csv의 첫줄은 헤더이므로, 이 부분을 무시하고 진행한다.
# 쉼표로 분리

mysqlimport --local --host=$MYSQLIP --user=root --ignore-lines=1 --fields-terminated-by=',' --password bts flights.csv-*

# 로컬에 남은 파일 제거
rm flights.csv-*

 

< populate_table.sh를 실행시킨 화면 >

< mysql에 접속해 데이터가 잘 들어갔는지 확인 >

 

 

 

 

"Data Science on the Google Cloud Platform by Valliappa Lakshmanan (O'Reilly). Copyright 2018 Google Inc."