Chapter 10. 마켓플레이스 활용(Lab)

Snowflake SE·2025년 1월 4일
marketplacesharingsnowflake
0

Zero to Snowflake

목록 보기
20/21

0. 실습 시나리오

데이터를 확인하다보니 매출 데이터가 전혀 없는 날이 확인됩니다. 매출과 날씨가 관련이 있을 것으로 보고 이를 확인하기 위하여 마켓플레이스에서 날씨 데이터를 가져와 이를 확인하도록 하겠습니다

시나리오

1. 워크시트 준비

  1. Projects > Worksheets 에서 +를 클릭하여 새 SQL Worksheet를 만듭니다
  2. 타임스탬프로 만들어진 worksheet 에서 ...을 클릭하여 RENAME합니다
    • 이름 변경 : Lab10. Marketplace

2. 매출 정보를 위한 뷰

  1. 뷰 생성을 위해 다음과 같이 컨텍스트를 설정합니다

    // 컨텍스트 설정
USE ROLE tasty_data_engineer;
USE DATABASE tasty_db ;

  2. 이전 실습에서 적재한 테이블을 이용하여 뷰를 생성합니다

    // harmonized 스키마에 orders_v 뷰 생성
CREATE OR REPLACE VIEW harmonized.orders_v AS
SELECT oh.order_id,
       oh.truck_id,
       oh.order_ts,
       od.order_detail_id,
       od.line_number,
       m.truck_brand_name,
       m.menu_type,
       t.primary_city,
       t.region,
       t.country,
       t.franchise_flag,
       t.franchise_id,
       f.first_name AS franchisee_first_name,
       f.last_name AS franchisee_last_name,
       l.location_id,
       cl.customer_id,
       cl.first_name,
       cl.last_name,
       cl.e_mail,
       cl.phone_number,
       cl.children_count,
       cl.gender,
       cl.marital_status,
       od.menu_item_id,
       m.menu_item_name,
       od.quantity,
       od.unit_price,
       od.price,
       oh.order_amount,
       oh.order_tax_amount,
       oh.order_discount_amount,
       oh.order_total
  FROM raw_pos.order_detail od
  JOIN raw_pos.order_header oh
    ON od.order_id = oh.order_id
  JOIN raw_pos.truck t
    ON oh.truck_id = t.truck_id
  JOIN raw_pos.menu m
    ON od.menu_item_id = m.menu_item_id
  JOIN raw_pos.franchise f
    ON t.franchise_id = f.franchise_id
  JOIN raw_pos.location l
    ON oh.location_id = l.location_id
  LEFT JOIN raw_customer.customer_loyalty cl
    ON oh.customer_id = cl.customer_id;

// analytics 스키마에 orders_v 뷰 생성
CREATE OR REPLACE VIEW analytics.orders_v AS
SELECT DATE(o.order_ts) AS date, * 
  FROM harmonized.orders_v o;

    뷰생성

    • 이전 실습 단계에서 다음 테이블이 생성되고 데이터가 적재되어야 합니다
      • raw_pos.order_detail
      • raw_pos.order_header
      • raw_pos.truck
      • raw_pos.menu
      • raw_pos.franchise
      • raw_pos.location
      • raw_customer.customer_loyalty

3. 매출 결과 조회

  1. 데이터 조회를 위한 컨텍스트 설정
    // 컨텍스트 설정
--USE ROLE tasty_data_engineer;
--USE DATABASE tasty_db ;
USE WAREHOUSE tasty_de_wh;
  1. 독일 함부르크의 2022년 2월의 매출액을 확인합니다
    // order_v를 이용하여 데이터 조회
SELECT o.date,
       SUM(o.price) AS daily_sales
  FROM tasty_db.analytics.orders_v o
 WHERE 1=1
   AND o.country = 'Germany'
   AND o.primary_city = 'Hamburg'
   AND DATE(o.order_ts) BETWEEN '2022-02-10' AND '2022-02-28'
 GROUP BY o.date
 ORDER BY o.date ASC;
    • 16일부터 21일까지의 매출 데이터가 없습니다. 이런 현상이 발생한 이유를 확인해 보도록 하겠습니다

4. 마켓플레이스 데이터 가져오기

  1. Data Products > Marketplace 로 이동합니다
  2. 검색 입력창에서 frostbyte 를 입력합니다
    검색
  3. Weather Source LLC: frostbyte를 선택합니다
  4. 우측 상단에서 Get 버튼을 누릅니다
  5. Options를 열어서 데이터베이스 이름을 Weather_Source 로 정합니다
  6. 추가 역할은 PUBLIC 으로 지정하고 옵션 하단의 Get 버튼을 클릭합니다
    데이터추가
  7. Done을 클릭합니다

5. 날씨 데이터 활용

  1. Weather_Source의 날씨 테이블과 Tastydb의 푸드 트럭이 운영되는 국가와 도시를 결합한 뷰를 생성합니다
    // 푸드 트럭 운영 국가의 날씨 데이터 조회 view 생성
CREATE OR REPLACE VIEW harmonized.daily_weather_v AS
SELECT hd.*,
       TO_VARCHAR(hd.date_valid_std, 'YYYY-MM') AS yyyy_mm,
       pc.city_name AS city,
       c.country AS country_desc
  FROM weather_source.onpoint_id.history_day hd
  JOIN weather_source.onpoint_id.postal_codes pc
    ON pc.postal_code = hd.postal_code
   AND pc.country = hd.country
  JOIN raw_pos.country c
    ON c.iso_country = hd.country
   AND c.city = hd.city_name;

    공유데이터를이용한뷰생성

  1. daily_weather_v 뷰를 사용하여 2022년 2월의 함부르크의 일평균 기온을 확인합니다

    // 2022년 2월의 함부르크의 일평균 기온
SELECT dw.country_desc,
       dw.city_name,
       dw.date_valid_std,
       AVG(dw.avg_temperature_air_2m_f) AS avg_temperature_air_2m_f
  FROM harmonized.daily_weather_v dw
 WHERE 1=1
   AND dw.country_desc = 'Germany'
   AND dw.city_name = 'Hamburg'
   AND YEAR(date_valid_std) = '2022'
   AND MONTH(date_valid_std) = '2'
 GROUP BY dw.country_desc, dw.city_name, dw.date_valid_std
 ORDER BY dw.date_valid_std DESC;

  2. 위의 결과를 그래프로 생성하여 시각화 합니다

    • 결과 테이블 상단의 Chart를 클릭합니다
    • (optional) AVG_TEMPERATURE_AIR_2M_F를 none으로 설정합니다
    • (optional) Fill area를 uncheck 합니다
    • (optional) Show points를 check 합니다
      온도
    • 이 그래프를 보면 16일부터 21일 사이 매출 데이터가 없는 이유를 설명할 수 없어 보입니다. 원인을 찾을 수 있는 다른 지표가 필요해 보이네요

  3. daily_weather_v 뷰를 사용하여 2022년 2월의 함부르크의 바람의 최대값을 확인합니다. 이전에 확인하던 방법과 마찬가지로 그래프로 시각화 해봅니다

    // 2022년 2월의 함부르크의 일 최대 풍속
SELECT dw.country_desc,
       dw.city_name,
       dw.date_valid_std,
       MAX(dw.max_wind_speed_100m_mph) AS max_wind_speed_100m_mph
  FROM harmonized.daily_weather_v dw
 WHERE 1=1
   AND dw.country_desc IN ('Germany')
   AND dw.city_name = 'Hamburg'
   AND YEAR(date_valid_std) = '2022'
   AND MONTH(date_valid_std) = '2'
 GROUP BY dw.country_desc, dw.city_name, dw.date_valid_std
 ORDER BY dw.date_valid_std DESC ;
    • 결과 테이블 상단의 Chart를 클릭합니다
    • (optional) Fill area를 uncheck 합니다
    • (optional) Show points를 check 합니다
      풍속
    • 이제 푸드 트럭의 매출이 없었던 이유를 짐작할 수 있겠네요. 매출이 0인 날짜의 풍속은 태풍 수준이었습니다.

5. 데이터 인사이트

지금까지 확인한 내용을 매출과 날씨를 통합하여 직접 확인할 수 있는 분석 뷰를 만들어 사용자에게 제공하려고 합니다.
그리고 사용자에게는 화씨보다 익숙한 섭씨로 온도를 표현하고 인치를 밀리미터로 보여주려고 합니다.

  1. SQL함수를 생성합니다

    // 화씨를 섭씨로
CREATE OR REPLACE FUNCTION analytics.fahrenheit_to_celsius(temp_f NUMBER(35,4))
RETURNS NUMBER(35,4)
AS
$$
    (temp_f - 32) * (5/9)
$$;

// 인치를 밀리미터로
CREATE OR REPLACE FUNCTION analytics.inch_to_millimeter(inch NUMBER(35,4))
RETURNS NUMBER(35,4)
AS
$$
    inch * 25.4
$$;

  2. 매출과 날씨를 결합한 SQL 작성합니다

    // 매출과 날씨
SELECT fd.date_valid_std AS date,
       fd.city_name,
       fd.country_desc,
       ZEROIFNULL(SUM(odv.price)) AS daily_sales,
       ROUND(AVG(fd.avg_temperature_air_2m_f),2) AS avg_temperature_fahrenheit,
       ROUND(AVG(analytics.fahrenheit_to_celsius(fd.avg_temperature_air_2m_f)),2) AS avg_temperature_celsius,
       ROUND(AVG(fd.tot_precipitation_in),2) AS avg_precipitation_inches,
       ROUND(AVG(analytics.inch_to_millimeter(fd.tot_precipitation_in)),2) AS avg_precipitation_millimeters,
       MAX(fd.max_wind_speed_100m_mph) AS max_wind_speed_100m_mph
  FROM harmonized.daily_weather_v fd
  LEFT JOIN harmonized.orders_v odv
    ON fd.date_valid_std = DATE(odv.order_ts)
   AND fd.city_name = odv.primary_city
   AND fd.country_desc = odv.country
 WHERE 1=1
   AND fd.country_desc = 'Germany'
   AND fd.city = 'Hamburg'
   AND fd.yyyy_mm = '2022-02'
 GROUP BY fd.date_valid_std, fd.city_name, fd.country_desc
 ORDER BY fd.date_valid_std ASC;
    • 이제 함부르크의 2022년 2월의 일별 매출액과 평균 기온, 평균 강우량, 최대 풍속을 볼 수 있게 되었습니다

  3. 위의 쿼리를 이용하여 원하는 도시의 매출액과 날씨를 볼 수 있는 뷰를 만들어 사용자에게 제공하려고 합니다

    // 매출과 날씨 뷰
CREATE OR REPLACE SECURE VIEW analytics.daily_city_metrics_v AS
SELECT fd.date_valid_std AS date,
       fd.city_name,
       fd.country_desc,
       ZEROIFNULL(SUM(odv.price)) AS daily_sales,
       ROUND(AVG(fd.avg_temperature_air_2m_f),2) AS avg_temperature_fahrenheit,
       ROUND(AVG(analytics.fahrenheit_to_celsius(fd.avg_temperature_air_2m_f)),2) AS avg_temperature_celsius,
       ROUND(AVG(fd.tot_precipitation_in),2) AS avg_precipitation_inches,
       ROUND(AVG(analytics.inch_to_millimeter(fd.tot_precipitation_in)),2) AS avg_precipitation_millimeters,
       MAX(fd.max_wind_speed_100m_mph) AS max_wind_speed_100m_mph
  FROM harmonized.daily_weather_v fd
  LEFT JOIN harmonized.orders_v odv
    ON fd.date_valid_std = DATE(odv.order_ts)
   AND fd.city_name = odv.primary_city
   AND fd.country_desc = odv.country
 WHERE 1=1
 GROUP BY fd.date_valid_std, fd.city_name, fd.country_desc
 ORDER BY fd.date_valid_std ASC;

  1. 뷰를 이용한 데이터 조회
    // 함부르크의 2022년 2월의 일별 매출액과 평균 기온, 평균 강우량, 최대 풍속
SELECT dcm.date,
       dcm.city_name,
       dcm.country_desc,
       dcm.daily_sales,
       dcm.avg_temperature_celsius,
       dcm.avg_precipitation_millimeters,
       dcm.max_wind_speed_100m_mph
  FROM analytics.daily_city_metrics_v dcm
 WHERE 1=1
   AND dcm.country_desc = 'Germany'
   AND dcm.city_name = 'Hamburg'
   AND dcm.date BETWEEN '2022-02-01' AND '2022-02-28'
 ORDER BY date DESC;

6. 협업을 위한 데이터 공유

  1. 데이터 공유를 위한 데이터 셋을 만듭니다

    // 공유할 테이블 생성
CREATE OR REPLACE TABLE analytics.daily_hamburg_202202
AS
SELECT dcm.date,
       dcm.city_name,
       dcm.country_desc,
       dcm.daily_sales,
       dcm.avg_temperature_celsius,
       dcm.avg_precipitation_millimeters,
       dcm.max_wind_speed_100m_mph
  FROM analytics.daily_city_metrics_v dcm
 WHERE 1=1
   AND dcm.country_desc = 'Germany'
   AND dcm.city_name = 'Hamburg'
   AND dcm.date BETWEEN '2022-02-01' AND '2022-02-28' ;

  2. 소비자, 즉 공유 받을 대상은 account 이름을 공급자에게 알려줘야 하는데 다음 쿼리를 통해 account 이름을 찾을 수 있습니다

    SELECT CURRENT_ACCOUNT();
    • 본 실습에서는 강사의 account를 넣어 공유하고 공유받은 화면을 함께 보겠습니다 (예, XX00000)

  3. 다음과 같은 단계로 공급자는 데이터를 공유합니다

    • 왼쪽 하단의 로그인 사용자 이름을 클릭하여 역할을 ACCOUNTADMIN으로 전환합니다
    • Data products > Private Sharing
    • Share > Create a Direct Share

    데이터공유절차1

    • Select Data > TASTY_DB > ANALYTICS > Table
      • DAILY_HAMBURG_202202 선택
    • Done
    • Secure Share Identifier : TASTY_DB_FROM_<YOUR_USERNAME>
    • Add acounts in your region by name : XX00000
    • Create Share

    데이터공유절차2

  4. 데이터를 공유받은 소비자는 다음과 같은 절차를 거쳐 로컬 데이터베이스와 같이 사용할 수 있게 됩니다

    • Data products > Private Sharing
    • 공유받은 데이터 타일 우측 상단의 화살표 클릭
    • 로컬에서 사용할 데이터베이스 이름 지정
    • ACCOUNTADMIN외에 사용할 수 있는 기본 역할(role) 지정
    • Get Data 클릭
    • View Database또는 Done 클릭
      공유받은데이터
    • 이제 공유 받은 데이터는 로컬 데이터베이스와 같이 조회할 수 있게 됩니다
      공유데이터조회
profile
Snowflake SE
Snowflake Korea SE
이전 포스트

Chapter 10. Sharing Data

다음 포스트

실습 초기화

0개의 댓글