Data Warehousing Workshop (7) - Lesson 6: Staging Data

Snowflake❄️의 Data Warehousing Workshop을 진행하면서 작업한 내용을 정리하고자 합니다.
Badge 1: 데이터 웨어하우징 워크샵 - KR

이번 내용은 Lesson 6: Staging Data 과정을 진행하면서 작성한 내용입니다.

---

🎭 Intro to Stages

Snowflake의 warehouses는 임시 저장 창고를 의미합니다.

이 워크샵에는 US-West-2 지역의 AWS에 S3 버킷을 설정했습니다. "uni-lab-files"로 지정했습니다.
https://uni-lab-files.s3.us-west-2.amazonaws.com/

 

파일 중 하나를 살펴보면 다음과 같습니다.
https://uni-lab-files.s3.us-west-2.amazonaws.com/json_book_author_nested.json

 

어떤 파일을 열려고하면, 다음과 같은 에러가 뜨기도 합니다.

---

 

🥋 Create a Snowflake Stage Object

🥋 Create a Snowflake Stage Object

AppUI에서는 마법사 사용이 안되니, classic console을 이용합니다.

 

AWS S3 선택

Name : like_a_window_into_an_s3_bucket
Schema Name : VEGGIES
URL : s3://uni-lab-files

⚠️ AWS Key는 입력하지 않은 상태입니다.

📓 A Stage or Not A Stage?

Snowflake의 stages는 위치가 아닙니다.
이미 데이터가 준비되어 생성돼 있는 AWS S3의 파일을 로드할 수 있는 구성 정보를 snowflake에 알려주는 것입니다.

---

🥋 Use the LIST command from a Snowflake Worksheet

🥋 Use the LIST Command to View the Files

• Snowflake는 대소문자를 구분하지 않습니다.
• Snowflake는 백그라운드에서 모두 대문자로 변환합니다.
• 대소문자를 구분해서 생성하고 싶으면 큰 따옴표 " 로 묶어서 생성해야합니다.

list @LIKE_A_WINDOW_INTO_AN_S3_BUCKET;

list @like_a_window_into_an_s3_bucket;

list @LIKE_a_window_INTO_an_s3_bucket;

list @garden_plants.veggies.LIKE_a_window_INTO_an_s3_bucket;

아래와 같이 AWS S3는 대소문자를 구분합니다.

아래 쿼리를 실행하면, 다르게 결과가 나오는 것을 확인할 수 있습니다.

list @like_a_window_into_an_s3_bucket/this_;

list @like_a_window_into_an_s3_bucket/THIS_;

 

---

🥋 Use the COPY INTO Statement to Load Data

🥋 Create a Table for Soil Types

VEGGIES 스키마의 GARDEN_PLANTS 데이터베이스에서 생성

create or replace table vegetable_details_soil_type
( plant_name varchar(25)
 ,soil_type number(1,0)
);

Load a File from the S3 Bucket into the New Table

COPY INTO 문 사용
table, stage, file, file format이 필요합니다.

copy into vegetable_details_soil_type
from @like_a_window_into_an_s3_bucket
files = ( 'VEG_NAME_TO_SOIL_TYPE_PIPE.txt')
file_format = ( format_name=PIPECOLSEP_ONEHEADROW );

 

---

🎯 L6 - COPY INTO Challenge Lab 1!

🥋 Create a Soil Type Look Up Table

SYSADMIN 역할, VEGGIES 스키마의 GARDEN_PLANTS 데이터베이스에서 생성해야 합니다.

create or replace table LU_SOIL_TYPE(
SOIL_TYPE_ID number,    
SOIL_TYPE varchar(15),
SOIL_DESCRIPTION varchar(75)
 );

🎯 Create a File Format, then Use COPY INTO to Load the File into the Table

1. https://uni-lab-files.s3.us-west-2.amazonaws.com/LU_SOIL_TYPE.tsv 파일 다운로드

2. file format : L8_CHALLENGE_FF

3. COPY INTO 명령을 사용하여 Stage에서 LU_SOIL_TYPE 테이블로 파일 로드

copy into LU_SOIL_TYPE
from @like_a_window_into_an_s3_bucket
files = ( 'LU_SOIL_TYPE.tsv')
file_format = ( format_name=L8_CHALLENGE_FF );

---

🎯 L6 - COPY INTO Challenge Lab 2!

1. https://uni-lab-files.s3.us-west-2.amazonaws.com/veg_plant_height.csv 파일 다운로드

 

2. VEGGIES 스키마 에 VEGETABLE_DETAILS_PLANT_HEIGHT 테이블 생성, 파일의 헤더 행을 사용하여 열 이름을 가져와 적합한 데이터 유형 선택하기

create or replace table VEGETABLE_DETAILS_PLANT_HEIGHT(
plant_name varchar(30),
UOM varchar(2),
Low_End_of_Range number,
High_End_of_Range number
 );

3. file format 선택 COMMASEP_DBLQUOT_ONEHEADROW

4. COPY INTO 명령을 사용하여 Stage에서 LU_SOIL_TYPE 테이블로 파일 로드

copy into VEGETABLE_DETAILS_PLANT_HEIGHT
from @like_a_window_into_an_s3_bucket
files = ( 'veg_plant_height.csv')
file_format = ( format_name=COMMASEP_DBLQUOT_ONEHEADROW );

---

💯 Lesson 6: Staging Data 완료!