중요:

이 실습은 데스크톱/노트북에서만 완료해야 합니다.

실습당 5회만 시도할 수 있습니다.

첫 시도에 모든 문제를 맞히지 못하거나 학습을 다시 해야 하는 것은 흔한 일이며 학습 과정의 일부입니다.

실습이 시작되면 타이머를 일시중지할 수 없습니다. 1시간 30분이 지나면 실습이 종료되므로 다시 시작해야 합니다.

자세한 내용은 실습에 관한 기술적 도움말을 참고하세요.

활동 개요

데이터 파이프라인이란 다양한 소스의 데이터를 저장 및 분석을 위한 대상으로 전송하는 일련의 프로세스입니다.

데이터 파이프라인은 데이터 흐름을 자동화하고 데이터 처리 방식을 간소화하여 데이터 변환의 성능과 효율성을 높일 수 있습니다.

SQL 파이프라인을 사용하면 불필요한 단계를 없애 성능을 개선할 수 있습니다. SQL 파이프라인은 중간 데이터 세트를 만들지 않고도 대상에 데이터를 직접 쓰고 읽어들일 수 있으므로 시간과 리소스를 절약하고 결과의 정확성을 개선할 수 있습니다.

또한 SQL 파이프라인을 사용하면 데이터 스토리지 용량을 최적화할 수 있습니다. 파이프라인을 사용하여 더 이상 필요하지 않은 데이터를 삭제하거나 보관처리할 수 있기 때문입니다. 이렇게 하면 스토리지 시스템의 공간을 확보할 수 있으며 성능을 높이는 데도 도움이 됩니다.

효율적이고 유연한 SQL 파이프라인을 빌드하는 것은 특히 조직의 요구사항이 빠르게 변화하는 경우 까다로울 수 있습니다. 하지만 파이프라인을 신중하게 계획하고 설계하면 아주 복잡한 데이터 요구사항에도 적응할 수 있는 파이프라인을 만들 수 있습니다.

이 실습에서는 데이터를 관리하고 SQL을 사용하여 비즈니스 요구사항을 해결하는 효율적이고 유연한 파이프라인을 빌드합니다.

시나리오

최근 몇 년간 TheLook eCommerce는 온라인 쇼핑 덕분에 수익이 급증했습니다. 하지만 배송 시간이 따라가지 못해 고객 만족도가 감소했습니다.

TheLook eCommerce의 클라우드 데이터 분석가인 당신은 물류팀 책임자인 카이와 협업하여 물류 센터에서 각 고객까지 이동한 거리를 포함한 고객 배송에 관한 데이터를 수집, 정리, 변환, 로드하는 데이터 파이프라인을 개발해 달라는 요청을 받았습니다.

이 정보는 물류팀이 새로운 물류 센터를 열지, 기존 물류 센터를 이전할지, 새로운 운송 방법에 투자할지 등 배송 시간을 개선하고 고객 만족도를 높일 방법을 결정하는 데 도움이 됩니다.

BigQuery 및 SQL 기술을 적용하여 물류팀이 배송 실적을 더 잘 모니터링할 수 있는 신뢰할 수 있는 데이터를 제공하고 물류팀의 데이터 요구사항이 변경될 때 쉽게 업데이트할 수 있는 유연한 파이프라인을 설계해 봅시다.

먼저 데이터 세트를 만들고 수집할 데이터의 테이블 스키마를 정의합니다. 다음으로 일련의 변환을 수행하고 살펴봅니다. 그런 다음 데이터에 변환을 적용한 후, 변환된 데이터를 새로 정의된 테이블에 로드합니다. 마지막으로 이러한 쿼리를 저장 프로시져로 공식화합니다.

설정

'실습 시작'을 클릭하기 전에

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

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

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

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

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

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

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

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

1. 실습 시작 버튼을 클릭합니다. 왼쪽에는 다음과 같은 항목이 포함된 실습 세부정보 패널이 있습니다.

   • 남은 시간
   • Google Cloud 콘솔 열기 버튼
   • 이 실습에 사용해야 하는 임시 사용자 인증 정보
   • 필요한 경우 실습 진행을 위한 기타 정보
   참고: 실습 비용을 결제해야 하는 경우 결제 수단을 선택할 수 있는 팝업이 열립니다.

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

   도움말: 두 개의 탭을 각각 별도의 창으로 나란히 정렬하면 탭 간에 쉽게 전환할 수 있습니다.

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

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

{{{user_0.username | "Google Cloud username"}}}

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

4. 아래의 Google Cloud 비밀번호를 복사하여 시작하기 대화상자에 붙여넣고, 다음을 클릭합니다.

{{{user_0.password | "Google Cloud password"}}}

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

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

5. 이후에 표시되는 페이지를 클릭하여 넘깁니다.
   • 이용약관에 동의하세요.
   • 임시 계정이므로 복구 옵션이나 2단계 인증을 추가하지 마세요.
   • 무료 체험판을 신청하지 마세요.

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

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

작업 1. 데이터 세트 만들기

이 작업에서는 기존 프로젝트에 데이터 세트를 만들고 스키마와 데이터 유형을 정의합니다.

1. Cloud 콘솔의 탐색 메뉴()에서 BigQuery를 선택합니다.

참고: Cloud 콘솔의 BigQuery에 오신 것을 환영합니다 메시지 상자가 표시될 수 있으며, 여기에는 빠른 시작 가이드 및 UI 업데이트용 출시 노트 링크가 제공됩니다. 계속하려면 완료를 클릭합니다.

탐색 메뉴, 탐색기 창, 쿼리 편집기가 표시됩니다.

2. 프로젝트 ID 옆에 있는 작업 보기 아이콘()을 클릭하고 데이터 세트 만들기를 클릭합니다.

참고: 먼저 프로젝트를 선택해야 할 수 있습니다. 이렇게 하려면 Google Cloud 콘솔 제목 표시줄에서 프로젝트 선택을 클릭한 다음 프로젝트 선택 대화상자에서 해당 프로젝트 링크를 선택합니다.

3. 데이터 세트 만들기 창에서 데이터 세트 ID를 thelook_ecommerce로 설정합니다. 위치가 미국(US) 멀티 리전으로 설정되어 있는지 확인하고 다른 필드는 기본값으로 둡니다.

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

이제 프로젝트 이름 아래에 thelook_ecommerce 데이터 세트가 표시됩니다.

5. 쿼리 편집기에서 SQL 쿼리(+) 아이콘을 클릭하여 쿼리를 실행할 새 제목 없는 쿼리 탭을 엽니다.

참고: 쿼리 편집기에서 새 쿼리를 실행할 때마다 동일한 제목 없는 쿼리 탭에서 이전 쿼리 위에 새 쿼리를 복사하여 붙여넣어 이전 쿼리를 대체하거나 SQL 쿼리(+) 아이콘을 클릭하여 새 제목 없는 쿼리 탭을 열어 쿼리를 실행할 수 있습니다.

6. 제목 없는 쿼리 탭에 다음 쿼리를 복사하여 붙여넣어 product_order_fulfillment 테이블을 만듭니다.

--Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64);

7. 실행을 클릭합니다.

참고: 이 쿼리는 product_orders_fulfillment라는 새 테이블을 만듭니다(아직 없는 경우). 또는 기존 테이블을 새 테이블 정의로 대체합니다.

8. 테이블로 이동을 클릭하여 생성된 테이블 스키마와 데이터 유형을 살펴봅니다.

이 데이터 세트는 현재 비어 있지만 데이터가 로드되면 데이터가 채워지는 위치입니다.

내 진행 상황 확인하기를 클릭하여 이 작업을 올바르게 완료했음을 확인합니다.

데이터 세트 만들기

작업 2. 쿼리 결과에서 테이블 만들기

주문하는 고객과 물류 센터의 근접성을 분석하는 기능을 분석에 추가하려고 합니다. 이렇게 하려면 지리적 위치를 기반으로 값을 계산해야 합니다.

이 작업에서는 original users 및 distribution_centers 테이블에 제공된 위도 및 경도 값에서 기하학적 점을 만듭니다.

1. 다음 쿼리를 쿼리 편집기에 복사합니다.

--Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY);

이 쿼리는 users 및 distribution_centers 테이블의 원본 열을 포함하는 customers 및 centers 테이블의 테이블 정의를 만듭니다. 이 쿼리는 두 테이블에 point_location 열도 추가합니다. 이러한 point_location 열의 데이터 유형은 Geography로 설정됩니다.

2. 실행을 클릭합니다.

내 진행 상황 확인하기를 클릭하여 이 작업을 올바르게 완료했음을 확인합니다.

관련 데이터를 저장할 새 테이블 만들기

작업 3. BigQuery 데이터에 변환 수행하기

이 작업에서는 ST_GEOGPOINT(lon, lat)를 호출하는 ST_GEOGPOINT 지리 함수를 사용하여 점 지오메트리를 만듭니다.

먼저 다음 SQL 쿼리를 실행하여 centers 및 customers 테이블을 만들고 채웁니다. 이러한 쿼리는 데이터의 원래 위치(경도, 위도)가 BigQuery thelook_ecommerce 공개 데이터 세트에 있으므로 SELECT 문에서 데이터를 로드합니다.

Google 공개 데이터 세트는 BigQuery에 저장된 데이터 세트 중에서 Google Cloud 공개 데이터 세트 프로그램을 통해 일반 대중에게 제공되는 데이터 세트입니다. 공개 데이터 세트를 사용하면 데이터를 로드하고 유지관리하지 않고도 BigQuery 내에서 바로 사용할 수 있는 공개 데이터를 쉽게 사용할 수 있습니다.

1. 다음 쿼리를 쿼리 편집기에 복사합니다.

--load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters;

이 쿼리는 지리 변환을 포함하여 centers 테이블을 로드합니다.

2. 실행을 클릭합니다.

참고: 테이블로 이동을 클릭하여 생성된 테이블 스키마와 데이터 유형을 탐색할 수 있습니다.

3. 이제 다음 쿼리를 쿼리 편집기에 복사합니다.

--load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users;

이 쿼리는 지역 변환을 포함한 고객 테이블을 로드합니다.

4. 실행을 클릭합니다.

다음으로 분석할 데이터 값을 계산해야 합니다.

고객과 물류 센터 간의 관계가 중요하므로 이러한 지리적 지점의 위치를 사용하여 각 고객에게 가장 가까운 물류 센터와 해당 위치들의 거리를 계산합니다.

이렇게 하려면 지리 함수인 ST_DISTANCE를 사용하여 고객 위치와 센터 간의 최소 거리를 계산합니다.

5. 다음 스칼라 서브 쿼리를 쿼리 편집기에 복사합니다.

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

이 쿼리는 거리를 킬로미터 단위로 계산합니다.

6. 실행을 클릭합니다.

스크린샷에 표시된 것처럼 쿼리는 각 고객과 가장 가까운 유통 센터 간의 거리를 반환합니다. 잠시 시간을 내어 SELECT 문을 검토하여 거리 계산이 어떻게 도출되는지 알아보세요.

7. 저장 드롭다운을 펼치고 쿼리 저장을 선택합니다.

쿼리 저장 대화상자가 열립니다.

8. 이름 필드에 Calculate Customer Distance to Closest Center를 입력합니다.
9. 리전에서 리전을 선택합니다.
10. 저장을 클릭합니다.

쿼리가 쿼리 목록에 저장됩니다.

내 진행 상황 확인하기를 클릭하여 이 작업을 올바르게 완료했음을 확인합니다.

BQ 데이터에 변환 수행하기

작업 4. 더 쉽게 업데이트할 수 있도록 저장 프로시저 만들기

저장 프로시저란 데이터베이스에 저장되어 단일 단위로 실행될 수 있는 SQL 문 집합입니다.

이 작업에서는 지금까지 작업 1~3에서 수동으로 순차적으로 생성한 모든 테이블 정의, 변환, 수집문을 포함하는 저장 프로시저를 만듭니다.

저장 프로시저(이 경우 sp_create_load_tables)를 만들려면 스크립트 시작 부분에 CREATE OR REPLACE PROCEDURE 문을 추가하고 그 뒤에 저장 프로시저의 이름과 BEGIN 문을 추가합니다. 프로시저의 구성요소를 모두 추가한 후 END 문을 추가합니다.

프로시저에 추가하려면 테이블 정의와 데이터 수집 부분을 배치하면 됩니다. 선호하는 방식에 따라 모든 테이블 정의를 앞에 배치하거나 테이블별로 클러스터링할 수 있습니다. 하지만 이 실습에서는 각 테이블 정의 다음에 데이터 수집 부분과 이전 단계에서 실행된 기타 변환이 나옵니다.

계산된 distance_to_closest_center 및 closest_centervalues를 저장할 수 있는 열을 고객 테이블에 만들 수도 있습니다. 이 절차의 장점은 스키마가 변경되거나 추가 열을 포함하거나 계산된 열을 생성하려는 경우 저장 프로시저에 이러한 열을 쉽게 추가하거나 업데이트할 수 있다는 것입니다. 업데이트가 완료되면 저장 프로시저가 정기적으로 실행되도록 설정할 수 있습니다.

이제 이전에 작업 1~3에서 실행한 모든 단계를 단일 단위로 실행하는 저장 프로시저를 만듭니다. 다음 코드를 실행하면 테이블이 업데이트되고 적절하게 채워집니다.

1. 다음 쿼리를 새 제목 없음 쿼리 탭에 복사합니다.

CREATE OR REPLACE PROCEDURE `thelook_ecommerce.sp_create_load_tables`() BEGIN --Create empty product_orders_fulfillment table CREATE OR REPLACE TABLE `thelook_ecommerce.product_orders_fulfillment` ( order_id INT64, user_id INT64, status STRING, product_id INT64, created_at TIMESTAMP, returned_at TIMESTAMP, shipped_at TIMESTAMP, delivered_at TIMESTAMP, cost NUMERIC, sale_price NUMERIC, retail_price NUMERIC, category STRING, name STRING, brand STRING, department STRING, sku STRING, distribution_center_id INT64) ; --load the product_order_fulfillment table from public dataset CREATE OR REPLACE TABLE thelook_ecommerce.product_orders_fulfillment AS SELECT items.*, products.id AS product_id_products, products.name AS product_name, products.category AS product_category, FROM bigquery-public-data.thelook_ecommerce.order_items AS items JOIN bigquery-public-data.thelook_ecommerce.products AS products ON (items.product_id = products.id); --Create empty centers table CREATE OR REPLACE TABLE `thelook_ecommerce.centers` ( id INT64, name STRING, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the centers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.centers` AS SELECT id, name, latitude, longitude, ST_GEOGPOINT(dcenters.longitude, dcenters.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dcenters ; --Create empty customers table CREATE OR REPLACE TABLE `thelook_ecommerce.customers` ( id INT64, first_name STRING, last_name STRING, email STRING, age INT64, gender STRING, state STRING, street_address STRING, postal_code STRING, city STRING, country STRING, traffic_source STRING, created_at TIMESTAMP, latitude FLOAT64, longitude FLOAT64, point_location GEOGRAPHY); --load the customers table from public dataset and include geography transformation CREATE OR REPLACE TABLE `thelook_ecommerce.customers` AS SELECT id, first_name, last_name, email, age, gender, state, street_address, postal_code, city, country, traffic_source, created_at, latitude, longitude, ST_GEOGPOINT(users.longitude, users.latitude) AS point_location FROM `bigquery-public-data.thelook_ecommerce.users` AS users ; END ;

2. 실행을 클릭합니다.

저장 프로시저는 필요한 모든 테이블을 만들고 변환된 데이터로 채웁니다. 하지만 저장 프로시저는 고객과 가장 가까운 물류 센터와의 거리를 보여주는 쿼리 결과를 반환하지 않습니다. 아래 7단계로 이동하여 이 작업을 실행하세요.

3. 저장 드롭다운을 펼치고 쿼리 저장을 선택합니다. 쿼리 저장 대화상자가 열립니다.
4. 이름 필드에 sp_create_load_tables를 입력합니다.
5. 리전에서 리전을 선택합니다.
6. 저장을 클릭합니다. 이 저장 프로시저는 데이터 세트의 루틴 섹션에 표시됩니다.
7. 실행을 클릭하여 이 저장 프로시져를 실행합니다.

위의 작업 3의 5단계에 표시된 대로 SELECT SQL 문을 실행하여 각 고객과 가장 가까운 유통 센터 간의 거리를 가져옵니다.

8. 다음 스칼라 서브 쿼리를 쿼리 편집기에 복사합니다.

SELECT customers.id as customer_id, ( SELECT MIN(ST_DISTANCE(centers.point_location, customers.point_location))/1000, FROM `thelook_ecommerce.centers` AS centers) AS distance_to_closest_center FROM `thelook_ecommerce.customers` AS customers ;

이 쿼리는 거리를 킬로미터 단위로 계산합니다.

9. 실행을 클릭합니다.

내 진행 상황 확인하기를 클릭하여 이 작업을 올바르게 완료했음을 확인합니다.

더 쉽게 업데이트할 수 있도록 저장 프로시저 만들기 참고: 이 실습에서는 예약된 쿼리를 실행하지 않지만 데이터를 업데이트하고 SQL 파이프라인을 유지하는 다양한 방법을 이해하는 것이 중요합니다.

예약된 쿼리를 설정하려면 쿼리 편집기에서 예약을 클릭하면 됩니다. 새 예약 쿼리 창이 열리고 지정할 수 있는 여러 쿼리 세부정보가 표시됩니다. 정기적으로 업데이트해야 하는 모든 쿼리에 대해 이 작업을 실행할 수 있습니다.

결론

수고하셨습니다.

SQL과 BigQuery를 사용하여 비즈니스 요구사항을 충족하는 간단하고 유연한 데이터 파이프라인을 만들었습니다.

먼저 데이터 세트를 만들고 수집할 데이터의 테이블 스키마를 정의했습니다. 그런 다음 일련의 변환을 수행하고 살펴봤습니다.

그런 다음 변환된 데이터를 새로 정의된 테이블에 로드하기 전에 데이터에 변환을 적용했습니다.

마지막으로 관심 있는 계산된 값을 포함하는 저장 프로시저로 이러한 쿼리를 공식화하고 정기 업데이트를 위해 예약된 쿼리를 설정하는 방법을 알아보았습니다.

이 실습을 통해 데이터 세트를 만들고 데이터 세트에 포함된 데이터의 테이블 스키마를 정의하는 것이 얼마나 중요한지 알게 되었습니다. 일련의 변환을 실행하고 검토함으로써 변환된 데이터를 새로 정의된 테이블에 로드하기 전에 데이터에 변환을 적용하는 기술을 개발했습니다. 또한 이러한 쿼리를 관심 있는 계산값을 포함하는 저장 프로시저로 공식화하는 방법과 정기 업데이트를 위해 예약된 쿼리를 설정하는 방법도 알아봤습니다.

BigQuery를 사용하여 SQL 파이프라인을 만들고 관리하는 방법을 이해하는 데 도움이 될 것입니다.

실습 종료하기

실습을 종료하기 전에 모든 작업을 완료했는지 확인하세요. 준비가 되면 실습 종료를 클릭한 다음 제출을 클릭합니다.

실습을 종료하면 실습 환경에 대한 액세스 권한이 삭제되며, 실습에서 완료한 작업에 다시 액세스할 수 없습니다.

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