개요

이 실습에서는 2개의 서로 다른 공개 데이터 세트를 분석하고, 이를 먼저 개별적으로 그런 다음 조합한 상태로 쿼리를 실행하여 흥미로운 인사이트를 도출합니다.

학습 내용

이 실습에서 학습할 내용은 다음과 같습니다.

• BigQuery 콘솔에서 대화형 쿼리를 수행합니다.
• 다양한 데이터 세트를 조합하여 분석을 실행합니다.

기본 요건

이 실습은 기초 레벨 실습으로, BigQuery 및 SQL에 어느 정도 경험이 있는 학습자를 대상으로 합니다.

소개

이 실습에서는 BigQuery에서 제공되는 두 가지 공개 데이터 세트인 미국 해양대기청(NOAA)의 날씨 데이터와 뉴욕시의 자전거 대여 데이터를 사용합니다.

또한 과학자가 무척 유용하게 사용할 수 있는 Google Cloud Platform의 다양한 측면을 살펴봅니다.

1. 서버리스: 작업을 위해 데이터를 로컬 컴퓨터로 다운로드할 필요 없이 클라우드에 있는 데이터 세트를 이용하면 됩니다.
2. 사용 편의성: 색인 생성과 같은 데이터 준비 과정 없이 데이터 세트에 애드혹 SQL 쿼리를 실행할 수 있습니다. 데이터 탐색에 무척 유용한 특성입니다.
3. 규모: 규모가 극히 큰 데이터 세트도 대화형으로 데이터 탐색을 수행할 수 있습니다. 적정 시간 내 작업을 수행하기 위해 데이터의 일부를 샘플링할 필요가 없습니다.
4. 공유성: 다른 데이터 세트의 데이터에 아무런 문제 없이 쿼리를 실행할 수 있습니다. BigQuery는 데이터 세트를 공유할 수 있는 편리한 방법입니다. 물론 데이터를 비공개로 보호하거나 특정인을 지정해서만 공유할 수도 있습니다. 모든 데이터를 공개할 필요는 없습니다.

최종적으로 비가 오는 날에 자전거 대여가 감소하는지 여부를 확인합니다.

설정 및 요건

실습 설정

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

1. 실습 시작 버튼을 클릭합니다. 실습 비용을 결제해야 하는 경우 결제 수단을 선택할 수 있는 팝업이 열립니다. 왼쪽에는 다음과 같은 항목이 포함된 실습 세부정보 패널이 있습니다.

   • Google Cloud 콘솔 열기 버튼
   • 남은 시간
   • 이 실습에 사용해야 하는 임시 사용자 인증 정보
   • 필요한 경우 실습 진행을 위한 기타 정보

2. Google Cloud 콘솔 열기를 클릭합니다(Chrome 브라우저를 실행 중인 경우 마우스 오른쪽 버튼으로 클릭하고 시크릿 창에서 링크 열기를 선택합니다).

   실습에서 리소스가 가동되면 다른 탭이 열리고 로그인 페이지가 표시됩니다.

   팁: 두 개의 탭을 각각 별도의 창으로 나란히 정렬하세요.

   참고: 계정 선택 대화상자가 표시되면 다른 계정 사용을 클릭합니다.

3. 필요한 경우 아래의 사용자 이름을 복사하여 로그인 대화상자에 붙여넣습니다.

   {{{user_0.username | "Username"}}}

   실습 세부정보 패널에서도 사용자 이름을 확인할 수 있습니다.

4. 다음을 클릭합니다.

5. 아래의 비밀번호를 복사하여 시작하기 대화상자에 붙여넣습니다.

   {{{user_0.password | "Password"}}}

   실습 세부정보 패널에서도 비밀번호를 확인할 수 있습니다.

6. 다음을 클릭합니다.

   중요: 실습에서 제공하는 사용자 인증 정보를 사용해야 합니다. Google Cloud 계정 사용자 인증 정보를 사용하지 마세요. 참고: 이 실습에 자신의 Google Cloud 계정을 사용하면 추가 요금이 발생할 수 있습니다.

7. 이후에 표시되는 페이지를 클릭하여 넘깁니다.

   • 이용약관에 동의합니다.
   • 임시 계정이므로 복구 옵션이나 2단계 인증을 추가하지 않습니다.
   • 무료 체험판을 신청하지 않습니다.

잠시 후 Google Cloud 콘솔이 이 탭에서 열립니다.

참고: Google Cloud 제품 및 서비스 목록이 있는 메뉴를 보려면 왼쪽 상단의 탐색 메뉴를 클릭하거나 검색창에 제품 또는 서비스 이름을 입력합니다.

작업 1. 자전거 대여 데이터 살펴보기

이 작업에서는 citibike_trips 테이블의 스키마를 살펴보고 쿼리를 실행하여 이동 시간을 분석하고 각 자전거의 총 주행 거리를 계산하여 공개 bigquery-public-data 데이터 세트, 특히 new_york_citibike 데이터 세트를 살펴봅니다.

1. Google Cloud 콘솔의 탐색 메뉴()에서 BigQuery를 클릭합니다.

'Cloud 콘솔의 BigQuery에 오신 것을 환영합니다.'라는 메시지 상자가 열립니다. 이 메시지 상자에서는 빠른 시작 가이드 링크 및 UI 업데이트 목록을 확인할 수 있습니다.

2. 완료를 클릭합니다.

3. 왼쪽 창에서 + 추가를 클릭한 다음 이름으로 프로젝트에 별표표시를 클릭합니다. 다음으로, 팝업 창에서 bigquery-public-data를 입력하고 마지막으로 별표표시를 클릭합니다.

4. BigQuery 콘솔의 왼쪽 창에 프로젝트가 2개 표시됩니다. 하나는 이름이 Qwiklabs 프로젝트 ID이고 하나는 이름이 bigquery-public-data입니다.

5. BigQuery 콘솔 왼쪽 창에서 bigquery-public-data > new_york_citibike > citibike_trips 테이블을 선택합니다.

6. 테이블(citibike_trips) 창에서 스키마 탭을 클릭합니다.

7. 열 이름과 데이터 유형을 검토합니다.

8. 파란색 + 버튼을 클릭하여 새 쿼리를 작성합니다.

다음 쿼리를 입력합니다.

SELECT MIN(start_station_name) AS start_station_name, MIN(end_station_name) AS end_station_name, APPROX_QUANTILES(tripduration, 10)[OFFSET (5)] AS typical_duration, COUNT(tripduration) AS num_trips FROM `bigquery-public-data.new_york_citibike.citibike_trips` WHERE start_station_id != end_station_id GROUP BY start_station_id, end_station_id ORDER BY num_trips DESC LIMIT 10

9. 실행을 클릭합니다. 결과를 보고 어떤 목적의 쿼리인지 알아내셨나요?

   힌트: 가장 이용량이 많은 상위 10개 편도 대여의 일반적인 이용 시간

10. 다음으로, 아래 쿼리를 실행하여 다른 흥미로운 사실을 찾아보겠습니다. 바로 데이터 세트에 있는 각 자전거의 총 주행 거리입니다. 참고로 쿼리 결과는 상위 5개로 제한됩니다.

WITH trip_distance AS ( SELECT bikeid, ST_Distance(ST_GeogPoint(s.longitude, s.latitude), ST_GeogPoint(e.longitude, e.latitude)) AS distance FROM `bigquery-public-data.new_york_citibike.citibike_trips`, `bigquery-public-data.new_york_citibike.citibike_stations` as s, `bigquery-public-data.new_york_citibike.citibike_stations` as e WHERE start_station_name = s.name AND end_station_name = e.name) SELECT bikeid, SUM(distance)/1000 AS total_distance FROM trip_distance GROUP BY bikeid ORDER BY total_distance DESC LIMIT 5 참고: 이 쿼리의 경우 데이터 세트에 있는 citibike_stations라는 다른 테이블도 사용하여 자전거 대여소 정보를 가져왔습니다.

작업 2. 날씨 데이터 세트 살펴보기

이 작업에서는 ghcnd_2015 테이블을 미리 보고 쿼리를 실행하여 뉴욕 기상관측소에서 강수량 데이터를 가져와 ghcn_d 데이터 세트를 살펴봅니다.

1. BigQuery 콘솔 왼쪽 창에서 새로 추가된 bigquery-public-data 프로젝트를 선택한 다음, ghcn_d > ghcnd_2015를 선택합니다.

2. 그런 다음 미리보기 탭을 클릭합니다. 콘솔이 다음과 유사하게 표시됩니다.

열 및 데이터 값 일부를 살펴봅니다.

3. 파란색 + 버튼을 클릭하여 새 쿼리를 작성하고 다음을 입력합니다.

SELECT wx.date, wx.value/10.0 AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE id = 'USW00094728' AND qflag IS NULL AND element = 'PRCP' ORDER BY wx.date

4. 실행을 클릭합니다.

이 쿼리의 결과로 쿼리에 제공한 ID와 일치하는 뉴욕 시내 기상관측소(NEW YORK CNTRL PK TWR에 해당하는 관측소)에서 관측된 2015년 모든 날짜의 강수량이 mm 단위로 반환됩니다.

작업 3. 강우 여부와 자전거 대여 간의 상관관계 찾기

이 작업에서는 new_york_citibike.citibike_trips 데이터 세트를 ghcn_d.ghcnd_2015 날씨 데이터 세트와 조인하여 비가 오는 날과 비가 오지 않는 날의 평균 자전거 이동 횟수를 파악하여 강우 여부와 자전거 대여 간의 상관관계를 분석합니다.

자전거 대여 데이터와 날씨 데이터를 조인하여 비 오는 날에는 자전거 대여가 감소하는지 알아보겠습니다.

1. 파란색 + 버튼을 클릭하여 새 쿼리를 작성하고 다음을 입력합니다.

WITH bicycle_rentals AS ( SELECT COUNT(starttime) as num_trips, EXTRACT(DATE from starttime) as trip_date FROM `bigquery-public-data.new_york_citibike.citibike_trips` GROUP BY trip_date ), rainy_days AS ( SELECT date, (MAX(prcp) > 5) AS rainy FROM ( SELECT wx.date AS date, IF (wx.element = 'PRCP', wx.value/10, NULL) AS prcp FROM `bigquery-public-data.ghcn_d.ghcnd_2015` AS wx WHERE wx.id = 'USW00094728' ) GROUP BY date ) SELECT ROUND(AVG(bk.num_trips)) AS num_trips, wx.rainy FROM bicycle_rentals AS bk JOIN rainy_days AS wx ON wx.date = bk.trip_date GROUP BY wx.rainy

2. 실행을 클릭합니다.

이제 소스가 서로 완전히 다른 자전거 대여 데이터 세트를 날씨 데이터 세트와 조인한 결과를 확인할 수 있습니다.

쿼리를 실행하여 산출된 결과를 보니 뉴요커는 비 오는 날에 자전거를 47% 더 적게 탄 것으로 나타납니다.

요약

이 실습에서는 두 데이터 세트에서 애드혹 쿼리를 실행했습니다. 클러스터를 설정하거나 색인을 생성하는 등의 작업 없이도 데이터를 쿼리할 수 있었습니다. 또한 두 데이터 세트를 조합하여 흥미로운 인사이트를 얻을 수 있었습니다. 이 모든 작업을 브라우저에서 벗어나지 않고 수행했습니다.

수고하셨습니다

지금까지 BigQuery에서 몇 가지 매우 흥미로운 쿼리를 실행하는 방법을 알아봤습니다.

실습 종료하기

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

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

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

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

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

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

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