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

 

 

 

 

Redshift -> S3

-- Redshift -> S3

unload(' ----- (S3 server에 올릴 data를 추출하는 query)
    select  col1
            , col2
    from test_table_1
    --
    where col3 in (''valid'') ----- (쿼리 전체가 따옴표로 감싸져있기 때문에 쿼리 내부의 문자는 따옴표 2개로 string을 감싸야함.)
')
to 's3://root_dir/test_dir/' ----- (unload 속에 적힌 query 결과가 저장될 s3 server의 경로)
iam_role 'credentials' ----- (s3 server에 로그인하기 위한 credential)
csv ----- (csv format으로 저장)
delimiter ',' ----- (column 구분자는 콤마(,))
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 test_table_2; ----- (기존 table이 있다면 drop)
create table test_table_2 ( ----- (S3 server에 있는 data를 담을 table 생성)
    col1    bigint,
    col2    varchar
)
;



copy test_table_2 ----- (생성한 table 이름)
from 's3://root_dir/test_dir/' ----- (생성한 table에 넣을 data가 있는 S3 server상의 directory)
iam_role 'credentials' ----- (S3 server에 로그인하기 위한 credential)
null as '' ----- (null값은 공백('')으로 채움)
format delimiter ',' ----- (컬럼 구분자는 콤마(,))
csv ----- (csv format임을 명시)
ignoreheader 1 ----- (컬럼 이름이 가장 첫 행에 적혀있을 경우 첫 행은 제외하고 copy문 진행)
;

- Redshift data type

bigint -> 숫자(정수)

float -> 숫자(소수)

varchar -> 문자

timestamp -> 날짜&시간

 

 

 

Hive -> S3

-- Hive -> S3

drop table if exists test_table_1; ----- (기존 table이 있다면 drop)
create external table test_table_1 ( ----- (S3 server와 연결할 hive table 생성)
    col_1     bigint,
    col_2     string
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' ----- (csv format의 SerDe)
stored as textfile ----- (csv는 textfile로 간주함)
location 's3://root_dir/test_dir/' ----- (생성한 external table과 연결될 S3 server상의 directory)
;

insert overwrite table test_table_1 ----- (위에서 생성한 external table에 query 결과를 insert)
select  col_1
        , col_2
from original_table
; ----- (이렇게 S3 server와 연결된 external table에 insert를 하면 S3 server 경로에도 해당 data가 upload됨)

 

 

S3 -> Hive

-- S3 -> Hive

drop table if exists test_table_2; ----- (기존 table이 있으면 drop)
create external table test_table_2 ( ----- (S3 server에서 data를 가져와 저장할 external table 생성)
    col_1     bigint,
    col_2     string
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde' ----- (csv format의 SerDe)
stored as textfile ----- (csv format은 textfile로 간주)
location 's3://root_dir/test_dir/' ----- (생성한 table에 넣을 data가 있는 S3 server상의 directory)
tblproperties ('skip.header.line.count'='1')  ----- (optional. CSV 파일의 가장 위 첫 번째 행을 무시할 경우 사용.)
;

- Hive data type

bigint -> 숫자(정수)

float -> 숫자(소수)

string -> 문자

timestamp -> 날짜&시간

 

 

 

위 과정에서 총 4개의 예시가 있습니다.

Redshift -> S3

S3 -> Redshift

Hive -> S3

S3 -> Hive

 

위 4가지 경우를 조합해서 우리는 DB간의 데이터 이동을 할 수 있습니다.

 

e.g.

1. Redshift -> S3 진행 후 S3 -> Hive 진행하면 결국 Redshift의 data를 Hive로 옮길 수 있음.

2. Hive -> S3 진행 후 S3 -> Redshift 진행하면 결국 Hive의 data를 Redshift로 옮길 수 있음.

 

 

 

 

 

 

- 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
;