BigQuery에서 Wikipedia 데이터 세트 쿼리하기

1. 개요

BigQuery는 Google의 완전 관리형, 노옵스(NoOps), 저비용 분석 데이터베이스입니다. BigQuery를 사용하면 관리할 인프라가 없어도 테라바이트 단위의 데이터를 쿼리할 수 있으며 데이터베이스 관리자도 필요하지 않습니다. BigQuery는 친숙한 SQL을 사용하며 종량제 모델을 활용할 수 있습니다. BigQuery는 데이터를 분석하여 의미 있고 유용한 정보를 찾는 데 집중할 수 있게 해줍니다.

이 Codelab에서는 BigQuery를 사용하여 Wikipedia 데이터 세트를 살펴봅니다.

학습할 내용

BigQuery 사용 방법
실제 데이터 세트를 BigQuery에 로드하는 방법
대규모 데이터 세트에 대한 유용한 정보를 얻기 위해 쿼리를 작성하는 방법

필요한 항목

Google Cloud 프로젝트
브라우저(Google Chrome 또는 Firefox 등)

설문조사

본 가이드를 어떻게 사용하실 계획인가요?

읽기만 할 계획입니다

읽은 다음 연습 활동을 완료할 계획입니다

귀하의 Google Cloud 사용 경험을 평가해 주세요.

초급

중급

고급

2. 설정 및 요건

BigQuery 사용 설정

아직 Google 계정이 없다면 계정을 만들어야 합니다.

Google Cloud 콘솔에 로그인하고 BigQuery로 이동합니다. 브라우저에 다음 URL을 입력하면 BigQuery 웹 UI를 직접 열 수도 있습니다.

https://console.cloud.google.com/bigquery

서비스 약관에 동의합니다.
BigQuery를 사용하려면 먼저 프로젝트를 만들어야 합니다. 프롬프트에 따라 새 프로젝트를 만듭니다.

프로젝트 이름을 선택하고 프로젝트 ID를 기록합니다.

프로젝트 ID는 모든 Google Cloud 프로젝트에서 고유한 이름입니다. 이 ID는 나중에 이 Codelab에서 PROJECT_ID라고 부릅니다.

이 Codelab에서는 BigQuery 샌드박스 한도 내에서 BigQuery 리소스를 사용합니다. 결제 계정은 필요하지 않습니다. 나중에 샌드박스 한도를 삭제하려면 Google Cloud 무료 체험에 가입하여 결제 계정을 추가하면 됩니다.

다음 섹션에서 Wikipedia 데이터 세트를 로드합니다.

3. 데이터 세트 만들기

먼저 프로젝트에서 새 데이터 세트를 만듭니다. 데이터 세트는 여러 테이블로 구성됩니다.

데이터 세트를 만들려면 리소스 창에서 프로젝트 이름을 클릭한 다음 데이터 세트 만들기를 클릭합니다.

데이터 세트 ID로 lab를 입력합니다.

데이터 세트 만들기를 클릭하여 빈 데이터 세트를 만듭니다.

4. bq 명령줄 프로그램으로 데이터 로드

Cloud Shell 활성화

Cloud Console에서 Cloud Shell 활성화를 클릭합니다.

이전에 Cloud Shell을 시작하지 않았으면 설명이 포함된 중간 화면 (스크롤해야 볼 수 있는 부분)이 제공됩니다. 이 경우 계속을 클릭합니다 (이후 다시 표시되지 않음). 이 일회성 화면은 다음과 같습니다.

Cloud Shell을 프로비저닝하고 연결하는 작업은 몇 분이면 끝납니다.

이 가상 머신에는 필요한 개발 도구가 모두 포함되어 로드됩니다. 영구적인 5GB 홈 디렉터리를 제공하고 Google Cloud에서 실행되므로 네트워크 성능과 인증이 크게 개선됩니다. 이 Codelab에서 대부분의 작업은 브라우저나 Chromebook만 사용하여 수행할 수 있습니다.

Cloud Shell에 연결되면 인증이 완료되었고 프로젝트가 해당 프로젝트 ID로 이미 설정된 것을 볼 수 있습니다.

Cloud Shell에서 다음 명령어를 실행하여 인증되었는지 확인합니다.

gcloud auth list

명령어 결과

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Cloud Shell에서 다음 명령어를 실행하여 gcloud 명령어가 프로젝트를 알고 있는지 확인합니다.

gcloud config list project

명령어 결과

[core]
project = <PROJECT_ID>

또는 다음 명령어로 설정할 수 있습니다.

gcloud config set project <PROJECT_ID>

명령어 결과

Updated property [core/project].

BigQuery에 데이터 로드

편의를 위해 Wikimedia 페이지 조회 데이터 세트의 2019년 4월 10일 데이터 일부가 Google Cloud Storage(gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz)에 제공됩니다. 데이터 파일은 GZip으로 압축된 CSV 파일입니다. bq 명령줄 유틸리티를 사용하여 이 파일을 직접 로드할 수 있습니다. 로드 명령어의 일부로 파일의 스키마도 설명합니다.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

다음과 같은 몇 가지 고급 옵션을 사용하여 페이지 조회수 파일을 로드했습니다.

파일을 CSV 파일로 파싱해야 함을 나타내도록 --source_format CSV를 설정합니다. CSV가 기본 형식이므로 이 단계는 선택사항입니다.
단일 공백이 필드를 구분하는 데 사용됨을 나타내도록 --field_delimiter " "를 설정합니다.
CSV 파일을 로드하는 동안 오류를 무시하고 열 수가 적은 행을 포함하려면 --allow_jagged_rows를 설정합니다.
문자열에 따옴표가 없음을 나타내려면 --quote ""을 설정합니다.
CSV 파일을 파싱하는 동안 최대 3개의 오류를 무시하도록 --max_bad_records 3를 설정합니다.

bq 명령줄에 대한 자세한 내용은 문서를 참고하세요.

5. 데이터 세트 미리보기

BigQuery 콘솔에서 방금 로드한 테이블 중 하나를 엽니다.

프로젝트를 펼칩니다.
데이터 세트를 펼칩니다.
표를 선택합니다.

스키마 탭에서 테이블 스키마를 확인할 수 있습니다. 4. 세부정보 탭으로 이동하여 테이블에 있는 데이터의 양을 확인합니다.

미리보기 탭을 열어 테이블의 행을 선택하여 확인합니다.

6. 쿼리 작성

새 쿼리 작성을 클릭합니다.

그러면 쿼리 편집기가 표시됩니다.

다음 쿼리를 작성하여 2019년 4월 10일 오후 2시부터 3시까지의 위키미디어 조회수를 확인합니다.

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

실행을 클릭합니다.

몇 초 후 결과가 하단에 표시되고 처리된 데이터의 양도 표시됩니다.

이 쿼리는 테이블이 691.4MB인데도 123.9MB를 처리했습니다. BigQuery는 쿼리에 사용된 열의 바이트만 처리하므로 처리된 데이터의 총량이 테이블 크기보다 훨씬 적을 수 있습니다. 클러스터링 및 파티셔닝을 사용하면 처리되는 데이터의 양을 더욱 줄일 수 있습니다.

7. 더 고급 쿼리

Wikipedia 페이지 조회수 확인하기

Wikimedia 데이터 세트에는 Wikipedia, Wiktionary, Wikibooks, Wikiquotes를 비롯한 모든 Wikimedia 프로젝트의 페이지 조회수가 포함되어 있습니다. WHERE 문을 추가하여 영어 Wikipedia 페이지로만 쿼리를 좁힙니다.

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

wiki 열을 추가로 쿼리하여 처리된 데이터 양이 124MB에서 204MB로 증가했습니다.

BigQuery는 CONTAINS, GROUP BY,, ORDER BY와 같은 친숙한 SQL 절과 여러 집계 함수를 지원합니다. 또한 정규 표현식을 사용하여 텍스트 필드를 쿼리할 수도 있습니다. 다음 중 하나를 사용해 보세요.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

여러 테이블에서 쿼리

와일드 카드 테이블을 사용하여 유니온을 형성할 테이블 범위를 선택할 수 있습니다.

먼저 다음 시간의 페이지 조회수를 새 테이블에 로드하여 쿼리할 두 번째 테이블을 만듭니다.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

쿼리 편집기에서 'pageviews_2019'을 접두사로 사용하여 테이블을 쿼리하여 로드한 두 테이블 모두를 쿼리합니다.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

_TABLE_SUFFIX 유사 열을 사용하여 테이블을 더 선택적으로 필터링할 수 있습니다. 이 쿼리는 4월 10일에 해당하는 테이블로 제한됩니다.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. 삭제

원하는 경우 bq rm 명령어로 만든 데이터 세트를 삭제합니다. -r 플래그를 사용하여 포함된 테이블을 삭제합니다.

bq rm -r lab

9. 축하합니다.

BigQuery와 SQL을 사용하여 실제 Wikipedia 페이지 조회 데이터 세트를 쿼리했습니다. 페타바이트 규모의 데이터 세트를 쿼리할 수 있습니다.

자세히 알아보기

다른 사용자가 현재 BigQuery를 어떻게 사용하는지 알아보려면 BigQuery 서브레딧을 확인하세요.
BigQuery에서 사용할 수 있는 공개 데이터 세트를 찾습니다.
BigQuery에 데이터를 로드하는 방법 알아보기