220608_Loading Taxi Data into Google Cloud SQL 2.5

택시 데이터를 Google Cloud SQL 2.5로 로드하기

 

[개요]

CSV 텍스트 파일에서 Cloud SQL로 데이터를 가져온 뒤, 단순 쿼리를 사용하여 기본 데이터 분석을 수행

 

[목표]

• Cloud SQL 인스턴스 만들기
• Cloud SQL 데이터베이스 만들기
• Cloud SQL 텍스트 데이터 가져오기
• 데이터 무결성 확인하기

 

 

---

0. 환경준비

[환경변수와 데이터를 저장할 저장용량 버킷 생성]

export PROJECT_ID=$(gcloud info --format='value(config.project)') export BUCKET=${PROJECT_ID}-ml

---

1. Cloud SQL 인스턴스 만들기

 

[Cloud SQL 인스턴스 생성]

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

[Cloud SQL 인스턴스 루트 비밀번호 설정]

gcloud sql users set-password root --host % --instance taxi \
--password Passw0rd

 

[Shell ip 주소로 환경변수 생성]

export ADDRESS=$(wget -qO - http://ipecho.net/plain)/32

 

[Shell 인스턴스를 허용목록에 추가, sql 인스턴스에 액세스]

gcloud sql instances patch taxi --authorized-networks $ADDRESS

[sql 인스턴스 ip 주소 가져오기]

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

[mysql ip 변수 확인]

echo $MYSQLIP

[택시트립 테이블 생성]

mysql --host=$MYSQLIP --user=root \
          --password --verbose

[trip 스키마 생성]

create database if not exists bts;
use bts;
drop table if exists trips;
create table trips (
vendor_id VARCHAR(16),
pickup_datetime DATETIME,
dropoff_datetime DATETIME,
passenger_count INT,
trip_distance FLOAT,
rate_code VARCHAR(16),
store_and_fwd_flag VARCHAR(16),
payment_type VARCHAR(16),
fare_amount FLOAT,
extra FLOAT,
mta_tax FLOAT,
tip_amount FLOAT,
tolls_amount FLOAT,
imp_surcharge FLOAT,
total_amount FLOAT,
pickup_location_id VARCHAR(16),
dropoff_location_id VARCHAR(16)
);

 

[항목 확인]

describe trips;

 

[trips 테이블 쿼리]

select distinct(pickup_location_id) from trips;

---

2. Cloud SQL 인스턴스에 데이터 추가하기

[CSV 파일 cloud storage에 로컬로 저장]

gsutil cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_1.csv trips.csv-1
gsutil cp gs://cloud-training/OCBL013/nyc_tlc_yellow_trips_2018_subset_2.csv trips.csv-2

 

[mysql 사용 cloud sql로 csv 파일 데이터 가져오기]

mysql --host=$MYSQLIP --user=root --password --local-infile

 

[콘솔 연결]

usb bts;

LOAD DATA LOCAL INFILE 'trips.csv-1' INTO TABLE trips
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id);

LOAD DATA LOCAL INFILE 'trips.csv-2' INTO TABLE trips
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' IGNORE 1 LINES (vendor_id,pickup_datetime,dropoff_datetime,passenger_count,trip_distance,rate_code,store_and_fwd_flag,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,imp_surcharge,total_amount,pickup_location_id,dropoff_location_id);

---

3. 데이터 무결성 확인하기

[trips 테이블 쿼리]

select distinct(pickup_location_id) from trips;

[trip 거리 쿼리 가져오기]

select
  max(trip_distance),
  min(trip_distance)
from
  trips;

[운행거리 0인 운행]

select count(*) from trips where trip_distance = 0;

[기대를 충족하지 않는 데이터 찾기]

select count(*) from trips where fare_amount < 0;

[payment_type 확인]

select
  payment_type,
  count(*)
from
  trips
group by
  payment_type;