Redshift에 CSV 데이터로 테이블 만들기

 

 CSV 데이터를 통째로 import해서 테이블로 만들어야 할 경우가 있을 때 개발 언어를 사용할 줄 아는 사람이라면 DBAPI 라이브러리를 사용해 (psycopg2 등) csv를 연 다음 insertmany나 for문+insertone을 날리면 되겠지만 redshift는 copy라는 좀 더 간단한 구문을 지원한다.

 더불어 DBeaver와 같은 SQL client SW를 사용한다면 해당 소프트웨어에서 CSV import 기능을 제공해주고 있을 가능성이 높다. 그런데 예전에  DBeaver로 해봤을때는 CSV형 테이블?이라는 식으로 생성되어 쿼리가 안 날려졌던 기억이 있는데 아무튼 지원은 됐다.

 Client SW를 통해서 import를 하면 내가 테이블을 따로 생성할 필요없이 클릭 몇 번으로 소프트웨어가 자동으로 컬럼 형태, 컬럼명 등을 인식해서 테이블을 생성해주는 작업까지 해주기때문에 굉장히 편리하지만 그만큼 안전하지 않다 (그래서 나는 선호하지 않는다).

 

 아무튼 여기서는 SW UI와 Redshift의 copy구문을 사용해 csv를 넣는 두 가지 방법을 기록하려고 한다.

 

Tableplus의 CSV Import 기능 사용하기

1. Redshift의 원하는 스키마에들어간 후 상단 바에서 File > Import > From CSV... 클릭

2. 원하는 파일을 클릭한 후 open을 눌러준다.

3. 다음과 같은 창이 뜨면 Create new table에 체크표시를 클릭해주면 각 컬럼별 데이터 타입이 인식된다. 여기서 원하는 타입이 다르면 바꿔주면 된다. 첫 행이 header가 아니라면 First line is header 옵션을 해제해주면 된다. 옵션을 모두 선택했다면 Import 버튼을 클릭해주면 자동으로 Insert values 쿼리가 돌아간다.

 

DBeaver의 CSV Import 기능 사용하기

1. 원하는 스키마에서 마우스 오른쪽 클릭 > Import Data 클릭

2. 저기 Exported에 있는 스키마가 내가 원하는 스키마가 맞는지 다시 확인하고 Next 클릭

3. 파일을 불러오는 창이 뜨면 원하는 csv파일을 선택한 후 Next 클릭

4. 테이블을 생성할 건지, 중복된 테이블이 있으면 어떻게 할 건지 등등의 옵션을 물어보는 창이 뜨는데 원하는 대로 설정한 후 계속 Next 클릭하면 데이터가 로드 된다 (이미 로드해버려서 DBeaver에서는 실행 못함..)

 

쿼리 돌아가다가 에러가 나는 경우

 데이터 타입이 자동으로 인식 되기 때문에 간혹 값이 안 맞아서 Insert가 안되는 경우가 있다. 나같은 경우 '... is out of range for type integer'이라는 에러가 떴는데, 이는 Intger로 인식된 행의 값 하나가 2^32보다 커서 Integer로 표현할 수 없기 때문에 난 에러이다. 따라서 해당 컬럼은 bigint라는 형으로 지정해줘야하는데, 슬프게도 Import할 때 바꾸려고 하니 목록에 해당 형이 없었다 (도대체 왜요?). 그래서 다음 방법을 사용했다.

 

Redshift copy 구문 사용하기

 Redshift copy 구문을 사용하려면 먼저 S3에 해당 csv 파일이 올려져있어야한다. 다음 순서로 실행하면 끝이다 (공식문서).

1. S3에 csv 파일 올리기

2. 테이블 만들기

3. copy 구문으로 csv 파일 넣기. Redshift에 넣고 나서는 S3에 있는 데이터 지워도 된다.

copy [스키마 이름].[테이블 이름]
from '[csv가 위치한 s3 주소 (s3://[버킷 이름]/[파일 이름].csv)]'
iam_role 'S3에 접근할 수 있는 권한이 있는 역할 ARN'
csv
IGNOREHEADER 1; --CSV의 header 1(=컬럼명)은 값으로 넣지 않음