개요

이 실습에서는 CSV 텍스트 파일에서 Cloud SQL로 데이터를 가져온 다음 단순 쿼리를 사용하여 기본 데이터 분석을 수행하는 방법을 알아봅니다.

이 실습에서 사용된 데이터 세트에는 NYC Taxi & Limousine Commission에서 수집되었으며 2009년부터 현재까지 뉴욕시의 모든 노란색 및 녹색 택시로 완료된 모든 운행 기록과 2015년부터 현재까지 임대 자동차(FHV)로 완료된 모든 운행 기록이 포함되어 있습니다. 승차 및 하차 날짜/시간, 승차 및 하차 위치, 이동 거리, 항목별 요금, 요율 유형, 결제 유형, 운전자가 보고한 승객 인원을 수집하는 필드가 기록에 포함됩니다.

이 데이터 세트는 광범위한 데이터 과학 개념 및 기술을 표시하는 데 사용될 수 있으며, 데이터 엔지니어링 교육과정의 일부 실습에서 사용됩니다.

목표

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

설정 및 요건

각 실습에서는 정해진 기간 동안 새 Google Cloud 프로젝트와 리소스 집합이 무료로 제공됩니다.

1. 시크릿 창을 사용하여 Qwiklabs에 로그인합니다.

2. 실습 사용 가능 시간(예: 1:15:00)을 참고하여 해당 시간 내에 완료합니다.
   일시중지 기능은 없습니다. 필요한 경우 다시 시작할 수 있지만 처음부터 시작해야 합니다.

3. 준비가 되면 실습 시작을 클릭합니다.

4. 실습 사용자 인증 정보(사용자 이름 및 비밀번호)를 기록해 두세요. Google Cloud Console에 로그인합니다.

5. Google Console 열기를 클릭합니다.

6. 다른 계정 사용을 클릭한 다음, 안내 메시지에 이 실습에 대한 사용자 인증 정보를 복사하여 붙여넣습니다.
   다른 사용자 인증 정보를 사용하는 경우 오류가 발생하거나 요금이 부과됩니다.

7. 약관에 동의하고 리소스 복구 페이지를 건너뜁니다.

Google Cloud Shell 활성화하기

Google Cloud Shell은 다양한 개발 도구가 탑재된 가상 머신으로, 5GB의 영구 홈 디렉터리를 제공하며 Google Cloud에서 실행됩니다.

Google Cloud Shell을 사용하면 명령줄을 통해 Google Cloud 리소스에 액세스할 수 있습니다.

1. Cloud 콘솔의 오른쪽 상단 툴바에서 'Cloud Shell 열기' 버튼을 클릭합니다.

   

2. 계속을 클릭합니다.

환경을 프로비저닝하고 연결하는 데 몇 분 정도 소요됩니다. 연결되면 사용자가 미리 인증되어 프로젝트가 PROJECT_ID로 설정됩니다. 예:

gcloud는 Google Cloud의 명령줄 도구입니다. Cloud Shell에 사전 설치되어 있으며 명령줄 자동 완성을 지원합니다.

• 다음 명령어를 사용하여 사용 중인 계정 이름을 나열할 수 있습니다.

gcloud auth list

출력:

Credentialed accounts: - @.com (active)

출력 예시:

Credentialed accounts: - google1623327_student@qwiklabs.net

• 다음 명령어를 사용하여 프로젝트 ID를 나열할 수 있습니다.

gcloud config list project

출력:

[core] project =

출력 예시:

[core] project = qwiklabs-gcp-44776a13dea667a6 참고: gcloud 전체 문서는 gcloud CLI 개요 가이드를 참조하세요.

작업 1. 환경 준비하기

• 프로젝트 ID에서 향후 실습에 사용될 환경 변수와 데이터를 저장할 스토리지 버킷을 생성합니다.

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

작업 2. Cloud SQL 인스턴스 만들기

1. 다음 명령을 입력하여 Cloud SQL 인스턴스를 생성합니다.

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

완료되기까지 몇 분 정도 걸립니다.

완료된 작업 테스트하기

내 진행 상황 확인하기를 클릭하여 실행한 작업을 확인합니다. 성공적으로 작업을 완료하면 평가 점수가 부여됩니다.

Cloud SQL 인스턴스를 만듭니다.

2. Cloud SQL 인스턴스의 루트 비밀번호 설정:

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

3. 비밀번호를 묻는 메시지가 표시되면 Passw0rd를 입력하고 Enter 키를 누르면 루트 비밀번호가 업데이트됩니다.

4. 이제 Cloud Shell의 IP 주소로 다음과 같이 환경 변수를 생성합니다.

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

5. 관리를 위한 Cloud Shell 인스턴스를 허용 목록에 추가하여 SQL 인스턴스에 액세스합니다.

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

6. 메시지가 표시되면 Y를 눌러 변경 사항을 수락합니다.

완료된 작업 테스트하기

내 진행 상황 확인하기를 클릭하여 실행한 작업을 확인합니다. 성공적으로 작업을 완료하면 평가 점수가 부여됩니다.

Cloud Shell 인스턴스를 허용 목록에 추가하여 SQL 인스턴스에 액세스합니다.

7. 다음을 실행하여 Cloud SQL 인스턴스의 IP 주소를 가져옵니다.

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

8. 다음과 같이 MYSQLIP 변수를 확인합니다.

echo $MYSQLIP

IP 주소가 출력됩니다.

9. mysql 명령줄 인터페이스에 로그인하여 taxi trips 테이블을 생성합니다.

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

10. 비밀번호를 입력하라는 메시지가 표시되면 Passw0rd를 입력합니다.

11. 다음 내용을 명령줄에 붙여넣어서 trips 테이블에 대한 스키마를 생성합니다.

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) );

완료된 작업 테스트하기

내 진행 상황 확인하기를 클릭하여 실행한 작업을 확인합니다. 성공적으로 작업을 완료하면 평가 점수가 부여됩니다.

bts 데이터베이스와 trips 테이블을 생성합니다.

12. mysql 명령줄 인터페이스에 다음을 입력하여 가져온 항목을 확인합니다.

describe trips;

13. 다음과 같이 trips 테이블을 쿼리합니다.

select distinct(pickup_location_id) from trips;

그러면 데이터베이스에 아직 데이터가 없기 때문에 비어 있는 세트가 반환됩니다.

14. 다음과 같이 mysql 대화형 콘솔을 종료합니다.

exit

작업 3. Cloud SQL 인스턴스에 데이터 추가하기

이제 뉴욕시 택시 운행 CSV 파일을 Cloud Storage에 로컬로 저장합니다. 리소스 사용을 줄이기 위해 데이터의 하위 세트(2만 행 이하)로만 작업합니다.

1. 명령줄에서 다음을 실행합니다.

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

2. mysql 대화형 콘솔에 연결하여 로컬 인파일 데이터를 로드합니다.

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

3. 비밀번호를 입력하라는 메시지가 표시되면 Passw0rd를 입력합니다.

4. 다음과 같이 mysql 대화형 콘솔에서 데이터베이스를 선택합니다.

use bts;

5. local-infile을 사용하여 로컬 CSV 파일 데이터를 로드합니다.

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);

작업 4. 데이터 무결성 확인하기

데이터를 소스에서 가져올 때마다 데이터 무결성을 확인하는 것은 항상 중요합니다. 이는 대략적으로 데이터가 기대에 부합하게 만드는 것입니다.

1. 고유한 승차 위치 지역을 위해 다음과 같이 trips 테이블에 쿼리합니다.

select distinct(pickup_location_id) from trips;

그러면 159개의 고유 ID가 반환됩니다.

2. trip_distance 열부터 살펴보겠습니다. 콘솔에 다음 쿼리를 입력합니다.

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

운행 거리는 0~1,000마일 정도로 예상할 수 있습니다. 반환된 최대 운행 거리 85마일은 합리적으로 보이지만 최소 운행 거리 0은 버그로 보입니다.

3. 데이터 세트에 운행 거리가 0인 운행이 얼마나 많이 있습니까?

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

데이터베이스에 운행 거리가 0인 운행은 155건 있습니다. 이러한 운행은 추가 조사가 필요합니다. 해당 운행에 0이 아닌 지불 금액이 연결되어 있는 것을 볼 수 있습니다. 허위 거래일까요?

4. 기대를 충족하지 않는 데이터를 더 찾을 수 있는지 알아보겠습니다. fare_amount 열은 양수여야 합니다. 데이터베이스에서 실제로 그런지 다음 쿼리를 입력하여 알아봅니다.

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

값이 양수가 아닌 운행이 14건 반환될 것입니다. 이러한 운행도 추가 조사가 필요합니다. 어쩌면 요금이 음수 값인 합당한 이유가 있을 수도 있습니다. 그러나 이러한 결과를 도출하는 데이터 파이프라인에 버그가 없는지 확인하는 것은 데이터 엔지니어의 역할입니다.

5. 마지막으로 payment_type 열을 확인하겠습니다.

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

쿼리 결과는 다음과 같이 네 가지의 결제 유형이 있다는 것을 보여줍니다.

• Payment type = 1에는 13,863개 행이 있습니다.
• Payment type = 2에는 6,016개 행이 있습니다.
• Payment type = 3에는 113개 행이 있습니다.
• Payment type = 4에는 32개 행이 있습니다.

문서를 확인해 보면 결제 유형 1은 신용 카드 사용, 결제 유형 2는 현금, 결제 유형 4는 분쟁을 가리킵니다. 이 수치는 합당합니다.

6. 다음과 같이 'mysql' 대화형 콘솔을 종료합니다.

exit

실습 종료하기

실습을 완료하면 실습 종료를 클릭합니다. Google Cloud Skills Boost에서 사용된 리소스를 자동으로 삭제하고 계정을 지웁니다.

실습 경험을 평가할 수 있습니다. 해당하는 별표 수를 선택하고 의견을 입력한 후 제출을 클릭합니다.

별점의 의미는 다음과 같습니다.

• 별표 1개 = 매우 불만족
• 별표 2개 = 불만족
• 별표 3개 = 중간
• 별표 4개 = 만족
• 별표 5개 = 매우 만족

의견을 제공하고 싶지 않다면 대화상자를 닫으면 됩니다.

의견이나 제안 또는 수정할 사항이 있다면 지원 탭을 사용하세요.

Copyright 2020 Google LLC All rights reserved. Google 및 Google 로고는 Google LLC의 상표입니다. 기타 모든 회사명 및 제품명은 해당 업체의 상표일 수 있습니다.