[Snowflake] Loading from AWS

1. Creating S3 bucket

• 버킷 이름 설정 : 버킷 이름은 전역에서 고유해야 함
• AWS 리전 : Snowflake에서 선택한 동일한 리전을 선택함(동일한 리전끼리는 데이터 전송 비용이 무료임)

 

• 버킷 생성 확인

 

• 버킷안에 폴더 생성

 

 

2. Upload files in S3

• csv 폴더 클릭

 

• 업로드 버튼 클릭

 

• 파일 추가 버튼 클릭

 

• csv 파일 업로드

netflix_titles.csv

2.86MB

 

• csv파일을 업로드 한 절차와 동일하게 json파일을 json 폴더에 업로드

Musical_Instruments_5.json

7.10MB

 

 

3. Creating policy

• IAM 서비스 접속

 

• 새로운 역할(role) 생성

 

• 신뢰할 수 있는 엔터티 선택

 

• AWS 계정 옵션에 외부 ID 필요 선택(더미로 '00000' 적용)

 

• 권한 추가(S3 Full Access)

 

• 버킷 생성 확인

 

• ExternalID 수정

 

 

 

4. Creating integration object

• storage integration 객체 생성(ACCOUNTADMIN role로 생성 가능)

// Create storage integration object

create or replace storage integration s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE 
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::931463879539:role/snowflake-access-role'
  STORAGE_ALLOWED_LOCATIONS = ('s3://snowflakes3bucket112233/csv/', 's3://snowflakes3bucket112233/json/')
   COMMENT = 'This an optional comment'

 

• 생성한 storage integration 확인

// See storage integration properties to fetch external_id so we can update it in S3
DESC integration s3_int;

 

• STORAGE_AWS_IAM_USER_ARN 값 복사

\

 

• IAM에서 생성한 snowflake role 신뢰 정책 편집 - STORAGE_AWS_IAM_USER_ARN 값 붙여넣기

 

• STORAGE_AWS_EXTERNAL_ID 값 복사

 

• IAM에서 생성한 snowflake role 신뢰 정책 편집 - STORAGE_AWS_EXTERNAL_ID 값 붙여넣기

 

 

5. Loading from S3

• 테이블 생성

CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.movie_titles (
  show_id STRING,
  type STRING,
  title STRING,
  director STRING,
  cast STRING,
  country STRING,
  date_added STRING,
  release_year STRING,
  rating STRING,
  duration STRING,
  listed_in STRING,
  description STRING );

 

 

• file format 생성

CREATE OR REPLACE file format MANAGE_DB.file_formats.csv_fileformat
    type = csv
    field_delimiter = ','
    skip_header = 1
    null_if = ('NULL','null')
    empty_field_as_null = TRUE
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'; //'"' 안의 ','는 열 구분을 제외

 

• 스테이지 생성

CREATE OR REPLACE stage MANAGE_DB.external_stages.csv_folder
    URL = 's3://snowflakes3bucket112233/csv/'
    STORAGE_INTEGRATION = s3_int
    FILE_FORMAT = MANAGE_DB.file_formats.csv_fileformat;

 

 

6. Handle JSON

• file format 생성

CREATE OR REPLACE file format MANAGE_DB.FILE_FORMATS.json_fileformat
    type = JSON;

 

• 스테이지 생성

CREATE OR REPLACE stage MANAGE_DB.external_stages.json_folder
    URL = 's3://snowflakes3bucket112233/json/'
    STORAGE_INTEGRATION = s3_int
    FILE_FORMAT = MANAGE_DB.file_formats.json_fileformat;

 

• 스테이지 확인

SELECT * FROM @MANAGE_DB.external_stages.json_folder;

• 컬럼 확인

SELECT 
$1:asin,
$1:helpful,
$1:overall,
$1:reviewText,
$1:reviewTime,
$1:reviewerID,
$1:reviewTime,
$1:reviewerName,
$1:summary,
$1:unixReviewTime
FROM @MANAGE_DB.external_stages.json_folder;

 

• 컬럼 형식 명시, DATE 함수 사용하여 실제 날짜로 변환

SELECT 
$1:asin::STRING as ASIN,
$1:helpful as helpful,
$1:overall as overall,
$1:reviewText::STRING as reviewtext,
$1:reviewTime::STRING,
$1:reviewerID::STRING,
$1:reviewTime::STRING,
$1:reviewerName::STRING,
$1:summary::STRING,
DATE($1:unixReviewTime::int) as Revewtime
FROM @MANAGE_DB.external_stages.json_folder;

 

• 컬럼의 값 안에 ','가 들어가는 경우 CASE WHEN을 사용

SELECT 
$1:asin::STRING as ASIN,
$1:helpful as helpful,
$1:overall as overall,
$1:reviewText::STRING as reviewtext,
DATE_FROM_PARTS( 
  RIGHT($1:reviewTime::STRING,4), 
  LEFT($1:reviewTime::STRING,2), 
  CASE WHEN SUBSTRING($1:reviewTime::STRING,5,1)=',' 
        THEN SUBSTRING($1:reviewTime::STRING,4,1) ELSE SUBSTRING($1:reviewTime::STRING,4,2) END),
$1:reviewerID::STRING,
$1:reviewTime::STRING,
$1:reviewerName::STRING,
$1:summary::STRING,
DATE($1:unixReviewTime::int) as UnixRevewtime
FROM @MANAGE_DB.external_stages.json_folder;

 

 

 

Udemy의 'Snowflake - The Complete Masterclass (2023 Edition)'를 공부한 내용을 바탕으로 작성하였습니다.