Chapter 9. 반정형 데이터 처리(Lab)

0. 실습 시나리오

푸드트럭 브랜드 메뉴의 재료 및 영양소를 파악하기 위해 JSON 데이터를 정형화하여 분석이 용이하도록 만듭니다
이번 실습을 위해 JSON 데이터를 먼저 적재하고, JSON 데이터를 점 표기법과 flatten 함수를 이용해 정형화하는 과정을 확인합니다
그리고 최종 사용자가 쉽게 데이터를 분석할 수 있도록 뷰를 생성합니다

---

1. 워크시트 준비

1. Projects > Worksheets 에서 +를 클릭하여 새 SQL Worksheet를 만듭니다
2. 타임스탬프로 만들어진 worksheet 에서 ...을 클릭하여 RENAME합니다
   • 이름 변경 : Lab9. Semi-structured
3. Snowsight UI또는 다음과 같은 SQL로 worksheet 컨텍스트를 설정합니다

   // 컨텍스트 설정
   USE ROLE sysadmin ;
   USE WAREHOUSE demo_build_wh ;
   USE DATABASE tasty_db ;
   USE SCHEMA raw_pos ;

---

2. JSON 데이터 적재

1. 다음과 같이 menu 테이블을 생성합니다

   // menu 테이블 생성
   CREATE OR REPLACE TABLE menu
   (
       menu_id NUMBER(19,0),
       menu_type_id NUMBER(38,0),
       menu_type VARCHAR(16777216),
       truck_brand_name VARCHAR(16777216),
       menu_item_id NUMBER(38,0),
       menu_item_name VARCHAR(16777216),
       item_category VARCHAR(16777216),
       item_subcategory VARCHAR(16777216),
       cost_of_goods_usd NUMBER(38,4),
       sale_price_usd NUMBER(38,4),
       menu_item_health_metrics_obj VARIANT
   );

   menu_item_health_metrics_obj 컬럼의 데이터 타입 VARIANT임을 기억하세요

2. menu 테이블에 데이터를 적재합니다

   // menu 데이터 적재
   COPY INTO menu
   FROM @public.s3load/raw_pos/menu/
        file_format = (format_name = 'public.csv_ff')
   ;

   Chapter 2 데이터 적재 실습에서 생성한 익스터널 스테이지 s3load를 사용합니다

---

3. 반정형 데이터 확인

1. 다음과 같이 컨텍스트를 설정합니다

   // 컨텍스트 설정
   USE ROLE tasty_data_engineer;
   USE WAREHOUSE tasty_de_wh;
   USE DATABASE tasty_db ;
   USE SCHEMA raw_pos ;

2. 적재된 데이터를 조회해 봅니다

   // 데이터 조회
   SELECT TOP 10
          m.truck_brand_name,
          m.menu_type,
          m.menu_item_name,
          m.menu_item_health_metrics_obj
     FROM menu m ;

   • 결과값에서 menu_item_health_metrics_obj컬럼의 데이터 셀을 하나 클릭하면 오른쪽 창에서 JSON 데이터가 보기 좋게 표시됩니다

   

3. 점표기법을 사용하여 JSON 데이터를 조회해 봅니다

   // 점표기법
   SELECT m.menu_item_health_metrics_obj:menu_item_id AS menu_item_id,
          m.menu_item_health_metrics_obj:menu_item_health_metrics AS menu_item_health_metrics
     FROM menu m;

   • 점표기법을 사용하여 menu_item_id 컬럼은 일반 컬럼화 시킬 수 있었지만, menu_item_health_metrics 컬럼은 여전히 반정형 형태의 데이터가 남아있음을 확인할 수 있습니다

   

---

4. 평면화(flatten)

이전 쿼리에서 menu_item_health_metrics에서 여전히 남아있는 반정형 데이터를 추출하기 위해 점표기법을 계속 사용할 수 있지만, 이번에는 flatten 함수와 lateral join 구문을 이용해서 데이터를 탐색해 보겠습니다

1. Flatten 함수와 Laternal 조인을 사용합니다

   // laternal flatten
   SELECT m.menu_item_name,
          obj.value:"ingredients"::VARIANT AS ingredients
     FROM menu m,
          LATERAL FLATTEN (input => m.menu_item_health_metrics_obj:menu_item_health_metrics) obj;

   

2. 위의 쿼리에서 ingredients 컬럼은 배열값을 출력하고 있습니다. array_contains 함수를 사용하여 배열값 중에 상추(Lettus)가 포함된 메뉴를 찾아보겠습니다.

   // array_contains 
   SELECT m.menu_item_name,
          obj.value:"ingredients"::VARIANT AS ingredients
     FROM menu m,
          LATERAL FLATTEN (input => m.menu_item_health_metrics_obj:menu_item_health_metrics) obj
    WHERE ARRAY_CONTAINS('Lettuce'::VARIANT, obj.value:"ingredients"::VARIANT);
   ;

   • 이런 종류의 쿼리는 메뉴별 재료 구입에 대한 수요 예측, 판매 빈도와 재고 소진 분석 등의 다양한 업무에서 활용될 수 있습니다.

3. 앞에서 확인한 구문을 이용하여 JSON 데이터 전체를 구조화하여 조회해 봅니다

   // 전체 컬럼 구조화 
   SELECT m.menu_item_health_metrics_obj:menu_item_id::integer AS menu_item_id,
          m.menu_item_name,
          obj.value:"ingredients"::VARIANT AS ingredients,
          obj.value:"is_healthy_flag"::VARCHAR(1) AS is_healthy,
          obj.value:"is_gluten_free_flag"::VARCHAR(1) AS is_gluten_free,
          obj.value:"is_dairy_free_flag"::VARCHAR(1) AS is_dairy_free,
          obj.value:"is_nut_free_flag"::VARCHAR(1) AS is_nut_free
     FROM menu m,
          LATERAL FLATTEN (input => m.menu_item_health_metrics_obj:menu_item_health_metrics) obj;

---

5. 반정형 데이터에 대한 정형 뷰

1. 뷰를 생성하기 전에 스키마 생성 및 권한 부여 작업을 수행합니다

   // 스키마 생성을 위한 컨텍스트 설정
   USE ROLE sysadmin ;
   USE DATABASE tasty_db ;
   
   // 뷰 생성용 스키마  
   CREATE OR REPLACE SCHEMA harmonized ;
   CREATE OR REPLACE SCHEMA analytics ;
   
   // 권한 설정을 위한 컨텍스트 설정
   USE ROLE securityadmin ;
   
   // harmonized 스키마에 대한 모든 권한 부여
   GRANT ALL ON SCHEMA tasty_db.harmonized TO ROLE tasty_data_engineer ;
   GRANT ALL ON SCHEMA tasty_db.harmonized TO ROLE tasty_dev ;
   
   // analytics 스키마에 대한 모든 권한 부여
   GRANT ALL ON SCHEMA tasty_db.analytics TO ROLE tasty_data_engineer ;
   GRANT ALL ON SCHEMA tasty_db.analytics TO ROLE tasty_dev ;
   
   // harmonized 스키마에 생성될 뷰에 대한 모든 권한 부여
   GRANT ALL ON FUTURE VIEWS IN SCHEMA tasty_db.harmonized TO ROLE tasty_data_engineer;
   GRANT ALL ON FUTURE VIEWS IN SCHEMA tasty_db.harmonized TO ROLE tasty_dev;
   
   // analytics 스키마에 생성될 뷰에 대한 모든 권한 부여
   GRANT ALL ON FUTURE VIEWS IN SCHEMA tasty_db.analytics TO ROLE tasty_data_engineer;
   GRANT ALL ON FUTURE VIEWS IN SCHEMA tasty_db.analytics TO ROLE tasty_dev;
   
   // analytics 스키마에 생성될 프로시저에 대한 모든 권한 부여
   GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA tasty_db.analytics TO ROLE tasty_data_engineer;
   GRANT USAGE ON FUTURE PROCEDURES IN SCHEMA tasty_db.analytics TO ROLE tasty_dev;

   

2. 이전 실습에서 사용한 점표기법, lateral flatten 구문을 모두 사용하여 뷰를 생성합니다

   // 뷰 생성을 위한 컨텍스트 설정
   USE ROLE sysadmin;
   USE DATABASE tasty_db ;
   
   // 뷰 생성 : harmonized.menu_v
   CREATE OR REPLACE VIEW harmonized.menu_v AS
   SELECT m.menu_id,
          m.menu_type_id,
          m.menu_type,
          m.truck_brand_name,
          m.menu_item_health_metrics_obj:menu_item_id::integer AS menu_item_id,
          m.menu_item_name,
          m.item_category,
          m.item_subcategory,
          m.cost_of_goods_usd,
          m.sale_price_usd,
          obj.value:"ingredients"::VARIANT AS ingredients,
          obj.value:"is_healthy_flag"::VARCHAR(1) AS is_healthy_flag,
          obj.value:"is_gluten_free_flag"::VARCHAR(1) AS is_gluten_free_flag,
          obj.value:"is_dairy_free_flag"::VARCHAR(1) AS is_dairy_free_flag,
          obj.value:"is_nut_free_flag"::VARCHAR(1) AS is_nut_free_flag
     FROM raw_pos.menu m,
          LATERAL FLATTEN (input => m.menu_item_health_metrics_obj:menu_item_health_metrics) obj;
   
   // 뷰 생성 : analytics.menu_v
   CREATE OR REPLACE VIEW analytics.menu_v AS
   SELECT * 
          EXCLUDE (menu_type_id) --exclude MENU_TYPE_ID
          RENAME  (truck_brand_name AS brand_name) -- rename TRUCK_BRAND_NAME to BRAND_NAME
     FROM harmonized.menu_v;

   

• harmonized.menu_v를 참조하여 analytics.menu_v를 생성합니다.
• 이때 SELECT * 구문에서 유용하게 사용할 수 있는 EXCLUDE 및 RENAME 매개 변수를 활용합니다
  • EXCLUDE : SELECT * 에서 제외할 컬럼명 지정
  • RENAME : SELECT * 의 컬럼명 중 교체할 컬럼명 지정

---

6. 반정형 데이터 분석의 예시

1. 각 푸드 트럭의 브랜드 메뉴 중에 음료가 아닌 메뉴 중 재료가 겹치는 메뉴 항목이 무엇인지 알아 봅니다

   // 컨텍스트 설정
   USE ROLE tasty_data_engineer ;
   USE WAREHOUSE tasty_de_wh ;
   USE DATABASE tasty_db ;
   
   // 배열 분석
   SELECT m1.menu_type,
          m1.menu_item_name,
          m2.menu_type AS overlap_menu_type,
          m2.menu_item_name AS overlap_menu_item_name,
          ARRAY_INTERSECTION(m1.ingredients, m2.ingredients) AS overlapping_ingredients
     FROM analytics.menu_v m1
     JOIN analytics.menu_v m2
       ON m1.menu_item_id <> m2.menu_item_id -- avoid joining the same menu item to itself
      AND m1.menu_type <> m2.menu_type 
    WHERE 1=1
      AND m1.item_category <> 'Beverage' -- remove beverages
      AND m2.item_category <> 'Beverage' -- remove beverages
      AND ARRAYS_OVERLAP(m1.ingredients, m2.ingredients) -- evaluates to TRUE if one ingredient is in both arrays
    ORDER BY m1.menu_type;

   • 이 분석 쿼리에서는 배열의 데이터를 분석하기 위해 ARRAY_INTERSECTION과 ARRAYS_OVERLAP을 활용하였습니다
     

2. 푸드 트럭 브랜드별로 제한된 영양소의 메뉴 갯수를 알아봅니다

   // 영양소별 메뉴 갯수 지표
   SELECT m.brand_name,
          SUM(CASE WHEN is_gluten_free_flag = 'Y' THEN 1 ELSE 0 END) AS gluten_free_item_count,
          SUM(CASE WHEN is_dairy_free_flag = 'Y' THEN 1 ELSE 0 END) AS dairy_free_item_count,
          SUM(CASE WHEN is_nut_free_flag = 'Y' THEN 1 ELSE 0 END) AS nut_free_item_count
     FROM analytics.menu_v m
    GROUP BY m.brand_name;

3. 위의 쿼리 결과를 그래프로 변환합니다

   • 결과 세트 테이블 상단의 Chart를 클릭
   • 오른쪽 Data에서 +Add column을 클릭하고 DAIRY_FREE_ITEM_COUNT와 NUT_FREE_ITEM_COUNT추가