[PostgreSQL] 외부 데이터 COPY

이 글은 개인의 학습을 목적으로 정리한 글입니다. 이점 참고하고 읽어주세요;)

---

이번 포스팅에서는 pgAdmin에서 쿼리문을 통해 외부 데이터를 pgAdmin 내부의 테이블로 COPY 하는 방법에 대해 알아보겠습니다.

 

우선 pgAdmin에 가져올 CSV 데이터를 저장하겠습니다.

 

여기서 CSV 파일이란 엑셀 파일과 같이 열과 행으로 구성되어 데이터 베이스에서 사용되는 파일 형식입니다.

CSV라는 이름은 Comma-Seperated Value의 첫 글자를 딴 것으로부터 유래했습니다.

엑셀이나, 다른 DB를 통해 데이터를 확인하면 열과 행에 맞춰 볼 수 있지만, 실제 CSV 파일을 열어보면

위의 사진처럼 데이터들이 콤마(,)를 통해서 구분되고 있음을 알 수 있습니다. 즉 엑셀이나 DB에서 볼 때엔 정갈하게 각 행과 열에 맞춰진 칸에서 표현되는 데이터들이, 실제로는 콤마를 통해 구분되고 있기 때문에 CSV라는 이름이 붙었다고 합니다.

"그렇다면 원래 데이터 자체에 콤마(,)가 포함되는 문자들은 어떻게 구분을 하지?" 라는 의문이 들 수도 있습니다.

예를 들어, 주소의 경우 OO로 9번길, 13OO동 100호 이런 식으로 데이터 자체에 콤마가 들어갈 수밖에 없는 경우에는 굉장히 난감해질 수 있습니다.

이럴 때에는 구분자로서 큰 따옴표("")를 통해 데이터를 묶어줍니다. "OO로 9번길, 13OO동 100호" 이렇게 데이터를 큰따옴표를 통해 묶어주면 CSV 파일 자체적으로 큰 따옴표 내부의 콤마는 구분자가 아닌 문자로 인식하게 됩니다.

 

다시 CSV파일을 가져오는 걸로 돌아오면,

https://github.com/anthonydb/practical-sql 

anthonydb/practical-sql

이 깃허브 리포의 Chapter 04 디렉터리의 us_counties_2010.csv 파일을 사용합니다. 저는 리포를 통째로 다운로드하여서 파일을 가져왔습니다.

---

그다음으로는 pgAdmin에 us_counties_2010.csv 파일을 저장할 테이블을 만들어줘야 합니다. postgreSQL에서 COPY를 통해 외부 데이터를 가져오기 위해선 반드시 데이터를 저장할 테이블이 미리 존재해야 합니다.

us_counties_2010.csv를 알맞게 담을 수 있는 테이블은 앞서 다운로드한 Chapter 04 디렉터리의 Chapter_04.sql 문을 열면 첫 번째로 보이는 CREATE문을 복사해서 쿼리문에 복사 붙여 넣기 해주면 됩니다.

만들어야 할 테이블의 칼럼과 설정이 상당히 복잡해서 손으로 일일이 작성하기엔 상당한 무리이기 때문에 복사 붙여넣기를 이용하시는 게 좋습니다. 위의 캡처는 CREATE문의 처음 일부분입니다.

CREATE문을 통해서 us_counties_2010이라는 테이블을 그 아래의 조건으로 생성했습니다. 여기서 만든 us_counties_2010 테이블은 이후에 우리가 CSV 파일을 COPY 해서 저장할 테이블입니다.

---

이제 pgAdmin으로 CSV파일을 가져오는 COPY 쿼리문을 살펴보겠습니다.

 

첫 줄의 COPY문 다음의 us_counties_2010은 CSV 파일을 저장할 테이블 이름입니다. 바로 위에서 CREATE를 통해 생성한 바로 그 테이블입니다.

 

다음으로 FROM은 데이터를 가져올 파일의 pull path입니다. 즉, 가져올 CSV 파일이 존재하는 파일 경로를 가져와야 합니다. 저는 가상 환경 기반의 리눅스 운영체제를 사용 중이라 파일 경로가 위의 캡처와 같고, 윈도의 경우는 형식이 조금 다릅니다. 윈도에서는 파일 탐색기를 통해 CSV 파일이 존재하는 디렉터리에 가서 탐색기의 주소창을 클릭하고 파일이 존재하는 위치의 pull path를 쉽게 복사해서 가져올 수 있습니다.

 

마지막으로 맨 마지막 줄의 WITH은 파일을 복사하는 설정입니다. 첫 번째 변수인 FORMAT은 딱 보면 알 수 있듯이 가져오는 파일의 확장자입니다. 우리는 CSV 파일을 사용하니 FORMAT CSV를 입력해줍니다. CSV 이외에도 TEXT, BINARY 등등의 확장자를 사용할 수 있습니다.

그 다음에 나오는 HEADER는 가져오려는 파일에 헤더가 존재한다는 걸 pgAdmin에게 알려줍니다. 여기서 헤더란 쉽게 테이블의 칼럼들이라고 생각하면 됩니다. 위에서 CSV 파일을 캡처한 화면을 보여드렸는데, 맨 첫 줄을 보면 대문자로 쓰인 단어들을 볼 수 있습니다. 그렇게 맨 첫 줄에 대문자로 적힌 문자들이 바로 HEADER입니다. 이렇게 WITH으로 HEADER를 선언하지 않으면 pgAdmin에서 CSV 파일의 첫 줄인 HEADER 역시 데이터로 인식하고 COPY해옵니다. 하지만  우리는 이미 us_counties_2010 테이블을 만들면서 해당 칼럼들을 만들었기 때문에 굳이 필요 없는 HEADER를 가져올 이유가 없는데, 이때 WITH 문을 통해 HEADER를 선언하면 pgAdmin 자체적으로 CSV 파일의 첫 줄은 건너뛰고 두 번째 줄부터 데이터를 읽어오게 됩니다.

---

이렇게 CSV 파일을 us_counties_2010 테이블에 COPY한 뒤에, SELECT문을 통해 us_counties_2010 테이블을 전부 읽어오면 위의 캡처와 같이 데이터들이 쭉 저장된 걸 확인할 수 있습니다.