Redshift : unload & copy (Manifest format으로 s3 서버에 query 결과 upload하기, s3서버에서 파일 불러와 database table 만들기)

 

 

 

 

 

 

 

 

Redshift -> S3

-- Redshift -> S3

unload (' ----- (S3 server에 올릴 data를 추출하는 query)
    select  item_id
            , item_name
            , price
    from test_table
    --
    where valid in (''valid'') ----- (쿼리가 따옴표로 감싸져있기때문에 따옴표 2개로 string을 감싸야함.)
')
to 's3://items/price_info/' ----- (unload 속에 적힌 query 결과가 저장될 s3 server의 경로)
iam_role 'credential' ----- (s3 server에 로그인하기 위한 credential)
manifest ----- (manifest format으로 저장)
delimiter '|' ----- (column 구분자는 | 콤마가 아닌 |를 사용한 이유는 item_name 컬럼의 데이터에 콤마가 있을 수 있기 때문.)
null as '' ----- (null값은 공백('')으로 채움)
header ----- (가장 첫 행에 column name을 표시해줌)
allowoverwrite ----- (target 경로에 이미 parquet 파일이 있다면 덮어씌움. cleanpath 옵션과 동시 사용 불가.)
cleanpath ----- (target 경로에 존재하는 모든 파일을 제거한 후 unload를 진행. allowoverwrite 옵션과 동시 사용 불가.)
;

간혹 to '~~' 부분에 명시된 S3 directory에 내가 원하는 데이터만 남아있는 것이 아니라 기존에 있는 파일들까지 존재하는 경우가 있습니다.

이런 경우 나중에 S3 directory를 참조하여 table을 만들 때 내가 원하지 않는 데이터까지 포함되어 table이 만들어지는 경우가 있습니다.

따라서 directory 하나에는 내가 원하는 파일들만 남겨놔야 나중에 참조할 때 혼동이 적으므로 unload를 할 때에는 아래와 같은 옵션 중 하나를 명시해주는게 좋습니다.

 

allowoverwrite 옵션 = S3 sever 상에서 겹쳐질 가능성이 있는 파일을 덮어씌워서 unload 진행.

cleanpath 옵션 = to '~~' 부분에 명시된 S3 directory에 있는 모든 파일을 제거한 후에 unload 진행.

(cleanpath 옵션과 allowoverwrite 옵션은 동시에 사용할 수 없고, 둘 중 하나만 사용해야 합니다.)

 

 

 

S3 -> Redshift

-- S3 -> Redshift

drop table if exists result_table; ----- (기존 table이 있다면 drop)
create temp table result_table ( ----- (S3 server에 있는 data를 담을 table 생성)
    item_id       bigint,
    item_name     varchar(max),
    price         bigint
)
;

copy result_table ----- (생성한 table 이름)
from 's3://items/price_info/manifest' ----- (생성한 table에 넣을 data가 있는 S3 server상의 directory)
iam_role 'credential' ----- (s3 server에 로그인하기 위한 credential)
manifest ----- (manifest format임을 명시)
delimiter '|' ----- (컬럼 구분자는 |)
null as '' ----- (null값은 공백('')으로 채움)
ignoreheader 1 ----- (컬럼 이름이 적힌 첫 행은 무시)
;

 

manifest format을 S3 server에 unload하면 item_id, item_name, price 컬럼의 데이터 등의 정보를 담긴 파일 뿐 아니라 manifest 파일 자체의 정보를 json 형식으로 담고있는 manifest라는 이름의 파일도 생성됩니다.

 

csv format, parquet format은 그냥 해당 경로에 있는 파일 자체를 copy하면 되지만

manifest format은 copy를 할 때 manifest format 정보를 담고있는 json파일을 from 경로에 적어줘야 합니다.

 

그래서 위 예시를 보면 아래처럼 from 경로의 마지막 부분에 manifest가 적힌 것을 볼 수 있죠.

...
from 's3://items/price_info/manifest' ----- (생성한 table에 넣을 data가 있는 S3 server상의 directory)
...

 

 

- Redshift data type

bigint -> 숫자(정수)

float -> 숫자(소수)

varchar -> 문자

timestamp -> 날짜&시간

 

 

 

 

 

 

- Error : org.apache.hadoop.io.Text cannot be cast to org.apache.hadoop.io.LongWritable

간혹 S3를 통한 data 이동 중 위같은 Error가 발생할 때가 있습니다.

예를들어 Redsfhit 에서 S3로 데이터를 upload하고 upload된 S3 상의 데이터를 참조하여 Hive table을 만들었을 때 Hive table은 잘 만들어졌는데 이 Hive table에 대해 select 구문을 실행하면 위같은 Error가 발생할 때가 있습니다.

이런 경우 Redshift에서 S3로 데이터를 upload하는 unload 부분에서 Redshift table을 그냥 select하여 unload하지 말고 아래 예시처럼 컬럼 각각에 대한 datatype을 명시해준 채로 unload한 후 그 data type과 맞게 Hive table을 생성하면 보통 위 Error는 해결됩니다.

 

-- Redshift -> S3

unload('
    select  col1::varchar
            , col2::bigint
    from test_table_1
    --
    where col3 in (''valid'')
')
to 's3://root_dir/test_dir/'
iam_role 'credentials'
format as parquet
allowoverwrite
cleanpath
;