GSP413

개요

BigQuery는 Google의 완전 관리형, 노옵스(NoOps), 저비용 분석 데이터베이스입니다. BigQuery를 사용하면 관리할 인프라나 데이터베이스 관리자가 없어도 테라바이트 단위의 대규모 데이터를 쿼리할 수 있습니다. BigQuery는 SQL을 사용하므로 사용한 만큼만 지불하는 모델의 장점을 활용할 수 있습니다. BigQuery는 데이터를 분석하여 의미 있고 유용한 정보를 찾는 데 집중할 수 있게 해줍니다.

이 실습에서 사용할 데이터 세트는 Google Merchandise Store의 Google 애널리틱스 레코드 수백만 개가 포함된 전자상거래 데이터 세트로, 여러분은 이 데이터 세트에서 사용 가능한 필드와 행을 탐색하여 유용한 정보를 파악하게 됩니다.

이 실습에서는 SQL 조인 및 유니언을 사용하여 새 보고 테이블을 만드는 방법을 중점적으로 다룹니다.

시나리오: 마케팅팀이 회사의 전자상거래 웹사이트에 업로드된 모든 제품 리뷰를 여러분과 데이터 과학팀에 제공했습니다. 여러분은 이들과 함께 협력해 다음과 같은 3가지 소스의 데이터를 조인하는 데이터 웨어하우스를 BigQuery에서 만들어야 합니다.

• 웹사이트 전자상거래 데이터
• 제품 인벤토리 재고 수준 및 리드 타임
• 제품 리뷰 감정 분석

실습할 내용

이 실습에서는 다음 작업을 수행하는 방법을 알아봅니다.

• 감정 분석을 다루는 새로운 전자상거래 데이터를 탐색합니다.
• 데이터 세트를 조인하고 새 테이블을 만듭니다.
• 유니언 및 테이블 와일드 카드로 과거 데이터를 추가합니다.

설정 및 요건

실습 시작 버튼을 클릭하기 전에

다음 안내를 확인하세요. 실습에는 시간 제한이 있으며 일시중지할 수 없습니다. 실습 시작을 클릭하면 타이머가 시작됩니다. 이 타이머에는 Google Cloud 리소스를 사용할 수 있는 시간이 얼마나 남았는지 표시됩니다.

실무형 실습을 통해 시뮬레이션이나 데모 환경이 아닌 실제 클라우드 환경에서 직접 실습 활동을 진행할 수 있습니다. 실습 시간 동안 Google Cloud에 로그인하고 액세스하는 데 사용할 수 있는 새로운 임시 사용자 인증 정보가 제공됩니다.

이 실습을 완료하려면 다음을 준비해야 합니다.

• 표준 인터넷 브라우저 액세스 권한(Chrome 브라우저 권장)

참고: 이 실습을 실행하려면 시크릿 모드 또는 시크릿 브라우저 창을 사용하세요. 개인 계정과 학생 계정 간의 충돌로 개인 계정에 추가 요금이 발생하는 일을 방지해 줍니다.

• 실습을 완료하기에 충분한 시간---실습을 시작하고 나면 일시중지할 수 없습니다.

참고: 계정에 추가 요금이 발생하지 않도록 하려면 개인용 Google Cloud 계정이나 프로젝트가 이미 있어도 이 실습에서는 사용하지 마세요.

실습을 시작하고 Google Cloud 콘솔에 로그인하는 방법

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

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

2. Google 콘솔 열기를 클릭합니다. 실습에서 리소스가 가동된 후 로그인 페이지가 표시된 다른 탭이 열립니다.

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

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

3. 필요한 경우 실습 세부정보 패널에서 사용자 이름을 복사하여 로그인 대화상자에 붙여넣습니다. 다음을 클릭합니다.

4. 실습 세부정보 패널에서 비밀번호를 복사하여 시작 대화상자에 붙여넣습니다. 다음을 클릭합니다.

   중요: 왼쪽 패널에 표시된 사용자 인증 정보를 사용해야 합니다. Google Cloud Skills Boost 사용자 인증 정보를 사용하지 마세요. 참고: 이 실습에 자신의 Google Cloud 계정을 사용하면 추가 요금이 발생할 수 있습니다.

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

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

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

참고: 왼쪽 상단에 있는 탐색 메뉴를 클릭하면 Google Cloud 제품 및 서비스 목록이 있는 메뉴를 볼 수 있습니다.

BigQuery 콘솔 열기

1. Google Cloud 콘솔에서 탐색 메뉴 > BigQuery를 선택합니다.

Cloud 콘솔의 BigQuery에 오신 것을 환영합니다라는 메시지 상자가 열립니다. 이 메시지 상자에서는 빠른 시작 가이드 및 출시 노트로 연결되는 링크가 제공됩니다.

2. 완료를 클릭합니다.

BigQuery 콘솔이 열립니다.

작업 1. 테이블을 저장할 새 데이터 세트 만들기

시작하려면 BigQuery에서 ecommerce라는 제목의 새 데이터 세트를 만들어 테이블을 저장합니다.

1. 왼쪽 창에서 BigQuery 프로젝트의 이름을 클릭합니다(qwiklabs-gcp-xxxx).

2. 프로젝트 이름 옆의 점 세 개를 클릭한 후 데이터 세트 만들기를 선택합니다.

데이터 세트 만들기 대화상자가 열립니다.

3. 데이터 세트 ID를 ecommerce로 설정하고 다른 옵션은 모두 기본값으로 둡니다.

4. 데이터 세트 만들기를 클릭합니다.

내 진행 상황 확인하기를 클릭하여 목표를 확인합니다. 테이블을 저장할 새 데이터 세트 만들기

작업 2. 제품 감정 데이터 세트 탐색하기

데이터 과학팀이 API를 통해 모든 제품 리뷰를 처리하여 각 제품에 대한 평균 감정 점수와 강도를 제공했습니다.

마케팅팀의 데이터 세트가 포함된 프로젝트는 data-to-insights입니다. BigQuery 공개 데이터 세트는 기본적으로 BigQuery에 표시되지 않습니다. 이 실습의 쿼리는 data-to-insights 데이터 세트를 사용하나 여러분은 이 데이터 세트를 볼 수 없습니다.

1. 먼저 데이터 과학팀이 만든 테이블의 복사본을 만들어 읽습니다.

create or replace TABLE ecommerce.products AS SELECT * FROM `data-to-insights.ecommerce.products` 참고: 복사본을 만드는 이유는 검토를 위해서이며, 이 실습의 쿼리는 data-to-insights 프로젝트를 사용합니다.

2. ecommerce 데이터 세트를 클릭하여 products 테이블을 표시합니다.

미리보기 및 스키마 탭을 사용하여 데이터 검토하기

1. ecommerce > products 데이터 세트로 이동하고 미리보기 탭을 클릭하여 데이터를 확인합니다.

2. 스키마 탭을 클릭합니다.

긍정적인 감정이 가장 높은 상위 5개 제품을 표시하는 쿼리 만들기

1. 쿼리 편집기에서 SQL 쿼리를 작성합니다.

예시 답안:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` ORDER BY sentimentScore DESC LIMIT 5

2. 부정적인 감정이 가장 높은 상위 5개 제품을 표시하도록 쿼리를 수정하고 NULL 값을 필터링합니다.

예시 답안:

SELECT SKU, name, sentimentScore, sentimentMagnitude FROM `data-to-insights.ecommerce.products` WHERE sentimentScore IS NOT NULL ORDER BY sentimentScore LIMIT 5

감정이 가장 낮은 제품은 무엇인가요?

내 진행 상황 확인하기를 클릭하여 목표를 확인합니다. 제품 감정 데이터 세트 탐색하기

작업 3. 데이터 세트를 조인하여 유용한 정보 찾기

시나리오: 이달 첫날에 인벤토리팀이 제품 인벤토리 데이터 세트의 orderedQuantity 필드가 최신 상태가 아니라고 알려왔습니다. 2017년 8월 1일의 제품별 총매출을 쿼리하고 이를 인벤토리의 현재 재고 수준과 비교하여 어떤 제품을 먼저 다시 보충해야 하는지 확인하려면 여러분의 도움이 필요합니다.

productSKU별 일일 판매량 계산하기

1. 아래 요구사항에 맞춰 ecommerce 데이터 세트에 새 테이블을 만듭니다.

• 제목을 sales_by_sku_20170801로 지정
• data-to-insights.ecommerce.all_sessions_raw를 데이터 소스로 사용
• 고유한 결과만 포함
• productSKU 반환
• 총 주문 수량 반환(productQuantity). 힌트: SUM() with a IFNULL 조건 사용
• 20170801의 판매만 표시되도록 필터링
• ORDER BY를 사용해 주문이 가장 많은 순으로 SKU 표시

예시 답안:

# pull what sold on 08/01/2017 CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170801 AS SELECT productSKU, SUM(IFNULL(productQuantity,0)) AS total_ordered FROM `data-to-insights.ecommerce.all_sessions_raw` WHERE date = '20170801' GROUP BY productSKU ORDER BY total_ordered DESC #462 skus sold

2. sales_by_sku 테이블을 클릭한 후 미리보기 탭을 클릭합니다.

판매된 고유 제품 SKU는 몇 개입니까?

답변: 462개

다음으로 두 데이터 세트를 조인하여 제품 인벤토리 정보로 판매 데이터를 보강합니다.

판매 데이터 및 재고 데이터 조인

1. 조인과 제품 인벤토리 데이터 세트의 다음 필드를 사용하여 웹사이트 전자상거래 데이터를 보강합니다:

• name
• stockLevel
• restockingLeadTime
• sentimentScore
• sentimentMagnitude

2. 일부만 작성된 다음 쿼리를 완성하세요.

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ORDER BY total_ordered DESC

예시 답안:

# join against product inventory to get name SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU ORDER BY total_ordered DESC

3. 이제 다음 사항을 포함하도록 작성한 쿼리를 수정합니다.

• (total_ordered / stockLevel)의 계산된 필드에 "ratio"라는 별칭을 지정합니다. 힌트: 재고 수준이 0일 때 0으로 나누는 오류를 피하려면 SAFE_DIVIDE(field1,field2)를 사용합니다.
• 월초에 이미 인벤토리가 50% 이상 소진된 제품만 포함하도록 결과를 필터링합니다.

예시 답안:

# calculate ratio and filter SELECT DISTINCT website.productSKU, website.total_ordered, inventory.name, inventory.stockLevel, inventory.restockingLeadTime, inventory.sentimentScore, inventory.sentimentMagnitude, SAFE_DIVIDE(website.total_ordered, inventory.stockLevel) AS ratio FROM ecommerce.sales_by_sku_20170801 AS website LEFT JOIN `data-to-insights.ecommerce.products` AS inventory ON website.productSKU = inventory.SKU # gone through more than 50% of inventory for the month WHERE SAFE_DIVIDE(website.total_ordered,inventory.stockLevel) >= .50 ORDER BY total_ordered DESC

내 진행 상황 확인하기를 클릭하여 목표를 확인합니다. 데이터 세트를 조인하여 유용한 정보 찾기

작업 4. 레코드 추가하기

해외팀이 이미 2017년 8월 2일에 오프라인 매장 판매를 수행했으며, 이를 일일 판매 테이블에 기록하려 합니다.

빈 테이블을 새로 만들어 2017년 8월 2일의 productSKU별 판매를 저장합니다.

1. 스키마의 경우 다음 필드를 지정합니다.

• 테이블 이름을 ecommerce.sales_by_sku_20170802로 지정
• productSKU STRING
• total_ordered를 INT64 필드로 설정

예시 답안:

CREATE OR REPLACE TABLE ecommerce.sales_by_sku_20170802 ( productSKU STRING, total_ordered INT64 );

2. 이제 날짜가 공유된 판매 테이블 2개가 있는지 확인합니다. 테이블 결과에서 Sales_by_sku 테이블 이름 옆의 드롭다운 메뉴를 사용하거나 브라우저를 새로고침하여 다음과 같이 왼쪽 메뉴에 표시되는지 확인합니다.

3. 영업팀에서 제공한 판매 레코드를 입력합니다.

INSERT INTO ecommerce.sales_by_sku_20170802 (productSKU, total_ordered) VALUES('GGOEGHPA002910', 101)

4. 테이블 미리보기를 통해 레코드가 표시되는지 확인하고 테이블 이름을 클릭하여 결과를 확인합니다.

과거 데이터를 함께 추가하기

동일한 스키마를 갖는 데이터를 함께 추가하는 방법에는 여러 가지가 있습니다. 두 가지 일반적인 방법은 유니언과 테이블 와일드 카드를 사용하는 것입니다.

• 유니언은 서로 다른 결과 집합의 행을 함께 추가하는 SQL 연산자입니다.
• 테이블 와일드 카드을 사용하면 간단한 SQL 문으로 여러 테이블을 쿼리할 수 있습니다. 와일드 카드 테이블은 표준 SQL에서만 사용할 수 있습니다.

1. 아래 두 테이블의 모든 레코드를 출력하는 유니언 쿼리를 작성합니다.

• ecommerce.sales_by_sku_20170801
• ecommerce.sales_by_sku_20170802

SELECT * FROM ecommerce.sales_by_sku_20170801 UNION ALL SELECT * FROM ecommerce.sales_by_sku_20170802 참고: UNION과 UNION ALL의 차이는 UNION이 중복 레코드를 포함하지 않는다는 점입니다.

일일 판매 테이블이 많을 때 생길 수 있는 문제는 무엇일까요? 서로 연결된 여러 개의 UNION 문을 작성해야 한다는 점입니다.

더 나은 해결책은 테이블 와일드 카드 필터와 _TABLE_SUFFIX 필터를 사용하는 것입니다.

2. (*) 테이블 와일드 카드를 사용하여 ecommerce.sales_by_sku_의 모든 2017년 레코드를 선택하는 쿼리를 작성합니다.

예시 답안:

SELECT * FROM `ecommerce.sales_by_sku_2017*`

3. 이전 쿼리를 수정하여 결과를 2017년 8월 2일로 제한하는 필터를 추가합니다.

예시 답안:

SELECT * FROM `ecommerce.sales_by_sku_2017*` WHERE _TABLE_SUFFIX = '0802' 참고: 고려할 수 있는 또 다른 옵션은 파티션을 나눈 테이블을 만들어 일일 판매 데이터를 적절한 파티션으로 자동으로 수집하는 것입니다.

내 진행 상황 확인하기를 클릭하여 목표를 확인합니다. 레코드 추가하기

수고하셨습니다

보고 테이블을 만든 다음 SQL 조인 및 유니언을 사용해 뷰를 조작하여 샘플 전자상거래 데이터를 탐색했습니다.

다음 단계/더 학습하기

• 이미 Google 애널리틱스 계정이 있고 BigQuery에서 자체 데이터 세트를 쿼리하고 싶으신가요? 이 내보내기 가이드를 따르세요.
• 조인을 위한 SQL 문법을 자세히 검토하려면 BigQuery 조인 문서를 확인하세요.
• Google 데이터 세트 검색을 리소스로 활용하세요.
• 다음 실습을 확인하세요.
  • BigQuery로 NCAA 데이터 탐색하기
  • Cloud Composer: 다양한 위치에서 BigQuery 테이블 복사

Google Cloud 교육 및 자격증

Google Cloud 기술을 최대한 활용하는 데 도움이 됩니다. Google 강의에는 빠른 습득과 지속적인 학습을 지원하는 기술적인 지식과 권장사항이 포함되어 있습니다. 기초에서 고급까지 수준별 학습을 제공하며 바쁜 일정에 알맞은 주문형, 실시간, 가상 옵션이 포함되어 있습니다. 인증은 Google Cloud 기술에 대한 역량과 전문성을 검증하고 입증하는 데 도움이 됩니다.

설명서 최종 업데이트: 2024년 2월 3일

실습 최종 테스트: 2023년 10월 31일

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