실용 PostgreSQL 스터디: chap.5, 7, 8(6 제외)

Chapter.5 데이터 가져오고 내보내기

COPY: 데이터 가져오기/내보내기 관련 커맨드

가져오기의 3 스탭

1. 구분되는 텍스트 파일 형식의 소스 데이터를 준비한다.
2. 데이터를 저장할 테이블을 만든다.
3. COPY 스크립트를 작성하여 데이터를 가져온다.

파일 내 구분 기호: 앰퍼샌드(&), 파이프(|)도 쓰긴 하지만 가장 대표적으론 쉼표(,)를 쓴다. -> 보통 쉼표로 구분된 CSV 파일을 많이 사용한다.

헤더 행: PostgreSQL에선 헤더행을 사용하지 않으므로 COPY 명령어에서 보통 HEADER옵션을 사용해서 제외한다.

큰 따옴표의 용도

• 때론 데이터에 쉼표가 포함되어 있는 경우가 있는데, 이럴 경우를 위해서 구분기호가 포함된 열에서 텍스트 한정자라는 임의의 문자로 감싸 SQL에 포함된 구분기호를 무시하도록 지시하는데 보통 쉼표로 구분된 파일에서 사용되는 텍스트 한정자로 큰 따옴표를 쓴다.
  • csv모드에서 큰 따옴표로 엮인 열 내에 텍스트 한정자가 두번 연속으로 나오면 postgreSQL이 하나를 제거한다.

COPY <table_name> (column1, column2, ...)
-- ()가 없어도 자동적으로 위치에 해당하는 값을 채워주게 되어있지만,
-- 특정 컬럼부터 값을 넣도록 해주고 싶다면 컬럼명을 지정해주면 된다.
FROM 'your/path/directory/file'
WITH (FORMAT CSV, HEADER)
-- WITH: 원하는 옵션을 괄호로 감싸 입출력파일에 맞게 조정할 수 있는 옵션을 지정할 수 있게 해줌
WHERE <condition>

 

WITH에 들어가는 옵션 종류

• 입출력 파일 형식: FORMAT <format_name>옵션, 읽거나 쓰는 파일의 유형 지정. 형식 명은 CSV, TEXT, BINARY.
• 헤더행 포함 여부: HEADER를 사용해서 소스파일에 제외할 헤더행이 있음을 지정
• 구분 기호: DELIMETER ‘character’옵션, 가져오기/내보내기 파일에서 구분자로 사용할 문자 지정
• 인용 문자: QUOTE ‘quote_character’ 텍스트 한정자 지정

WHERE을 사용해서 특정 조건에 맞는 행만 가져와서 채우도록 할 수 있다.

데이터 내보내기

• 역시 COPY 사용

COPY <table_name>
TO 'your/path/directory/output_file'
WITH (FORMAT CSV, HEADER)

쿼리 결과를 내보내고 싶다면 위의 구문에서 table_name 부분을 (<QUERY_YOU_WANT>)로 바꾸면 된다

 

Chapter.7 관계형 데이터베이스에서 테이블 조인

테이블 조인: 한 테이블의 행을 다른 테이블의 행에 연결하는 방법

JOIN 커맨드: 쿼리에서 테이블을 연결하기 위해 쓰는 문법

SELECT *
FROM <table1_name> JOIN <table2_name>
ON table1.key_column = table2.key_column

-- USING: on절에서 사용하는 열 이름이 동일한 경우 쓸 수 있는 단축어
-- ON절에서 결과가  bool값이 true나 false로 평가되는 모든 표현식을 사용할 수 있다. 
-- 즉 on t1.key1 = t2.key2뿐만 아니라 ≥ 나 ≤ 등도 사용 가능하다는 뜻이다. 
-- 분석에서 쓰이는 경우가 있다.
SELECT *
FROM <table1_name> JOIN <table2_name>
USING (<column_name>);

JOIN의 유형

JOIN

• INNER JOIN이라고도 함. 두 테이블의 조인된 열에서 일치하는 값이 있는 두 테이블의 행을 반환. 한 테이블에만 존재하는 행은 제공하지 않는다.
• 잘 구조화되고 유지 관리가 잘 된 데이터셋 작업시 양 테이블 모두에 존재하는 행만 찾아야하는 경우에 사용

LEFT JOIN

• 왼쪽 테이블의 모든 행을 반환하고 오른쪽 테이블은 일치하는 값을 가진 행만 반환

RIGHT JOIN

• 오른쪽 테이블의 모든 행을 반환하고 왼쪽 테이블은 일치하는 값을 가진 행만 반환

LEFT & RIGHT

• 쿼리 결과에 한 테이블의 모든 행이 포함되기를 바라는 경우
• 테이블 중 하나에서 결측값을 찾으려는 경우
• 조인된 테이블의 일부 행에 일치되는 값이 없을 경우

FULL OUTER JOIN

• 두 테이블에서 모든 행을 반환하고 값이 일치하는 행은 연결한다. 일치하는 값이 없는 행은 쿼리 결과에 다른 테이블에 대한 빈 값 포함
• 일치 여부와 관계없이 조인에서 두 테이블의 모든 행을 보려는 경우

CROSS JOIN

• 두 테이블에서 가능한 모든 행 조합을 반환. 결과를 데카르트 곱이라고도 함. 이 조인은 키 열 간에 일치하는 항목을 찾을 경우가 없어 ON절이 필요하지 않다

NULL을 사용해서 결측값이 있는 행 찾기

• NULL: 데이터가 없거나 데이터가 포함되지 않아서 알 수 없는 조건을 나타내는 특수값

WHERE <column_name> IS NULL

세 가지 유형의 테이블 관계

• 일대일: 두 테이블 사이에서 어떤 키가 같은 행은 하나씩만 존재하는 관계
• 일대다: 첫 번째의 테이블의 키값은 두번째 테이블의 조인된 열에서 여러 매칭 값을 가진다.
• 다대다: 한 테이블의 여러 항목이 다른 테이블의 여러 항목과 관련된 경우

테이블에 별칭 사용하기 + 여러 테이블 조인하기

SELECT t1.column1, .... t2.column1, ..., t3.column1
FROM <table1_name> AS t1 JOIN <table2_name> AS t2
ON t1.key_column = t2.key_column
JOIN <table3_name> as t3
ON t1.key_column = t3.key_column

집합 연산자로 쿼리 결과 결합하기

조인 결과처럼 다양한 테이블이 나란히 반환되는 것 대신 하나의 결과로 출력되어 데이터를 재정렬 해야할 때, 이렇게 데이터를 조작하는 방법으로 UNION, INTERSECT, EXCEPT가 있다.

• UNION: 두 개의 쿼리가 주어졌을때, 두번째 쿼리의 행을 첫번째 쿼리의 행에 추가하고 중복을 제거하여 결합된 고유 행집합 생성. UNION ALL로 사용시 중복을 포함한 모든 행 반환
• INTERSECT: 두 쿼리에 모두 존재하는 행만 반환하고 중복 제거
• EXCEPT: 첫 쿼리에는 있지만 두번째 쿼리에는 없는 행을 반환하며 중복을 제거한다.

Chapter 8. 적시적소에 알맞은 테이블 디자인

이번 장에서는 인덱스 부분만 정리

인덱스로 쿼리 속도 향상 시키기

데이터베이스가 데이터를 찾기 위해 각 행을 스캔하는 대신 인덱스를 바로가기로 사용하게 할 수 있다.

인덱스 추가 커맨드

CREATE INDEX <INDEX_NAME> ON <TABLE_NAME> (COLUMN_NAME);

 

B-tree 인덱스: PostgreSQL의 기본 인덱스

• 균형잡힌 트리의 약자
• 값 검색시 원하는 데이터를 찾을때까지 분기를통해 트리의 위에서 아래로부터 내려다보는 데이터를 구성
• 동등 및 범위 연산자를 사용해서 정렬하고 검색할 수 있는 데이터에 유용

Explain으로 쿼리 성능 벤치마킹하기

ANALYZE 키워드를 추가하면 EXPLAIN이 쿼리를 수행하고 실제 실행시간을 표기할 수 있다.

EXPLAIN ANALYZE <query_str>;

인덱스 사용시 고려해야할 점

인덱스가 항상 필요한 것은 아니니 다음을 고려해서 필요한 순간에 사용하도록 하자

• 테이블 조인에 사용할 열에 인덱스를 추가하는 것이 좋다
• 외래키에 대한 인덱스는 계단식 삭제(cascade)동안 비용이 많이 드는 순차 스캔을 피하는 데에 도움이 된다.
• WHERE절에서 자주 사용되는 열에 인덱스를 추가하는 것이 좋다
• EXPLAIN ANALYZE를 사용해서 다양한 구성에서 성능을 테스트하라