[PostgreSQL, Greenplum DB] DATABASE, SCHEMA, TABLE, VIEW 만들고 데이터 등록(INSERT)하기

SQL(Structured Query Language)은 데이터베이스로부터 원하는 정보를 가져오기 위해 고안된 구조화된 질의언어입니다. ("에스큐엘" 혹은 "시퀄" 이라고 발음). 

SQL과 같은 데이터베이스 언어는 역할을 기준으로 했을 때 3가지로 구분할 수 있습니다. 

(a) 데이터 정의 언어 (DDL: Data Definition Language): create, drop 등

(b) 데이터 조작 언어 (DML: Data Manipulation Language): insert, update, delete, select 등

(c) 데이터 제어 언어 (DCL: Data Control Language): commit, rollback 등

이번 포스팅에서는 데이터 정의 언어(DDL), 데이터 조작 언어(DML)를 사용하여 PostgreSQL DB, Greenplum DB에서 

(1) Database, Schema, Table 를 만들고 삭제하는 방법: create, drop

(2) Table에 새로운 데이터를 등록하고 가져오는 방법: insert into, select from

(3) Index 를 생성하는 방법: create index

(4) VIEW 생성: create view

을 소개하겠습니다. 

Table 은 행(rows)과 열(columns)로 데이터를 저장하고 있으며, Schema에 포함되어 있습니다.  

Schema 는 논리적으로 테이블을 정의해놓은 것으로서, 여러개의 Table/ Procedure/ Function/ Index/ View 등을 포함하고 있습니다. 

Database 는 데이터를 한 곳에 관리할 수 있도록 모아둔 것으로서, 여러개의 Schema를 가질 수 있습니다. 

이를 집에 비유하자면 

• Database = House
• Schema = Floor Plan (or Blueprint)
• Table = Room

로 이해할 수 있겠네요.  

회사에서는 보통의 경우 Database, Schema, Table 생성(create)과 삭제(drop) 의 경우 critical 한 영역의 권한에 속하므로 데이터베이스 관리자 (database administrator) 에게만 권한이 있고 일반의 사용자에게는 select 조회 권한만 줍니다. 따라서 일반 분석가라면 이번 포스팅의 내용을 사용할 일이 별로 없을 수도 있을 듯 합니다만, 알아두어서 손해볼 일은 없으니 한번 찬찬히 살펴보시기 바랍니다. 

(교육 프로그램을 진행할 때 hands-on training 시 수강생 간의 테이블 사용에 대한 간섭이나 충돌을 방지하기 위해서 수강생 별로 Database나 Schema를 따로 따로 생성하여 진행하기도 합니다.)

(1) Database 만들기: CREATE DATABASE database_name;

'test' 라는 이름의 새로운 Database를 만들어 보겠습니다. 

-- Create a Database DROP DATABASE IF EXISTS test; CREATE DATABASE test;

PGAdmin III 의 왼쪽 Object browser 를 refresh 하면 아래와 같이 'test' Database 가 새로 생겼음을 확인할 수 있습니다. (명령프롬프트 창에서 psql 로는 '\l' 을 하면 데이터베이스 리스트 볼 수 있음)

PGAdmin III 의 GUI 에서 새로 만든 'test' Database Server로 새로 연결(connect) 하려면 아래의 화면 캡쳐처럼 우측 상단의 '<new connection>' 에서 'test Database' 를 선택하고, username과 rolename, password를 차례대로 입력해주면 됩니다. (혹은 왼쪽의 Object browser에서 'test' DB를 선택 후 'SQL editor' 창을 새로 띄워도 됩니다). 

cf. SQL Server 나 MySQL 의 경우 서버에서 다수의 Database를 동시에 사용할 수 있으며, 'USE dbname' statement를 사용해서 Database 를 Default Database 를 변경할 수 있습니다. 하지만, PostgreSQL 은 단 하나의 Database만 서버에서 사용할 수 있기 때문에 'USE dbname' statement는 없습니다. PostgreSQL에서는 사용하고 싶은 Database 를 변경하려면 현재 서버에서 연결되어 있는 Database를 닫고 (close the current connection), 다른 Database 에 연결(then connect to another database in server) 해주어야 합니다. (명령 프롬프트 창에서는 psql dbname 해주면 됨)

(2) Schema 만들기: CREATE SCHEMA schema_name;

이번에는 위의 (1)번에서 새로 만들었던 'test' Database 안에 'cust'라는 이름의 Schema를 만들어보겠습니다. (Customer 주제의 데이터를 다루는 사용자들이 사용할 데이터 저장 공간)

-- CREATE SCHEMA DROP SCHEMA IF EXISTS cust; CREATE SCHEMA cust;

PGAdmin III 의 왼쪽 Object browser 를 refresh 시켜서 보면 아래 화면캡쳐처럼 'cust' Schema가 새로 생겼음을 알 수 있습니다. 

(3) Table 만들기: CREATE TABLE table_name;

이제 'test' Database 의 'cust' Schema 에 'cust_master'라는 이름의 Table을 만들어보겠습니다. 

(생성은 CREATE TABLE table_name, 삭제는 DROP TABLE table_name)

-- CREATE TABLE W/ CONSTRAINTS DROP TABLE IF EXISTS cust.cust_master; CREATE TABLE cust.cust_master (     id INT PRIMARY KEY     , first_name VARCHAR(30) NOT NULL     , last_name VARCHAR(30) NOT NULL     , gender TEXT DEFAULT 'UNKNOWN'     , age int CHECK (age > 0 AND age < 120)     , joindate DATE CHECK (joindate > '2020-01-03') );

테이블을 만들 때 각 칼럼 별로 제약조건(Constraints)을 설정할 수 있습니다. 

제약 조건 (contraint)	설명
PRIMARY KEY	데이터 중복 금지 (UNIQUE)  & NULL 값 금지 (NOT NULL)
UNIQUE	데이터 중복 금지 (한가지 값만 가져야 함)
CHECK (condition statements)	조건식에 맞지 않는 데이터 금지
NOT NULL	NULL 값 금지
DEFAULT = value	값이 없을 경우 미리 지정한 초기값(DEFAULT VALUE) 등록

* reference: https://www.postgresql.org/docs/9.5/ddl-constraints.html

pg_catalog.pg_tables 테이블에서 schema와 table 정보가 조회할 수 있습니다. 방금 위에서 만든 'cust' Schema 내의 'cust_master' 테이블을 한번 조회해볼까요? 

-- How to list up the tables SELECT *  FROM pg_catalog.pg_tables  WHERE      schemaname = 'cust'     AND tablename LIKE 'cust%';

MPP(Massively Parallel Processing) Architecture 의 Greenplum Database 의 경우 분산키(distribution key), 압축방식 등을 추가로 지정해줄 수 있습니다. 

-- CREATE TABLE W/ CONSTRAINTS DROP TABLE IF EXISTS cust.cust_master; CREATE TABLE cust.cust_master (     id INT PRIMARY KEY     , first_name VARCHAR(30) NOT NULL     , last_name VARCHAR(30) NOT NULL     , gender TEXT DEFAULT 'UNKNOWN'     , age int CHECK (age > 0 AND age < 120)     , joindate DATE CHECK (joindate > '2020-01-03') )  WITH (appendoptimize=true, compresslevel=5)  DISTRIBUTED BY (id);

* for more information: https://gpdb.docs.pivotal.io/6-8/ref_guide/sql_commands/CREATE_TABLE.html

(4) Table 에 값(values) 등록하기        : INSERT INTO schema.table (columns) VALUES (values);

'test' Database 의 'cust' Schema 내 'cust_master' Table 에 id, first_name, last_name, gender, age, joindate 칼럼 값을 등록해보겠습니다. 

-- INSERT INTO VALUES INSERT INTO cust.cust_master (id, first_name, last_name, gender, age, joindate)  VALUES (1, 'kildong', 'hong', 'M', 30, '2020-05-15'); SELECT * FROM cust.cust_master;

아래의 INSERT INTO 문에서는 'gender' 칼럼의 값이 누락되어 있습니다. 이처럼 등록할 값이 누락될 경우 CREATE TABLE 할 때 gender 칼럼에 DEFAULT VALUE로 지정했던 'UNKNOWN' 값이 초기값으로 등록이 됩니다. 

-- default value for 'gender' column INSERT INTO cust.cust_master (id, first_name, last_name, age, joindate)  VALUES (2, 'chulsu', 'kim', 20, '2020-04-29'); SELECT * FROM cust.cust_master;

만약 INSERT INTO로 등록할 값의 순서가 Table을 생성할 때 정의했던 칼럼의 순서와 동일하고 중간에 누락값이 없다면 칼럼 이름을 나열할 필요없이 그냥 VALUES () 에 바로 값을 등록해주어도 됩니다.

-- if the sequence of values is the same with the table definition,  -- column names can be ignored INSERT INTO cust.cust_master VALUES (3, 'younghee', 'lee', 'F', 25, '2020-03-05'); SELECT * FROM cust.cust_master;

등록할 값이 여러개 있는 경우 아래처럼 VALUES 다음에 row 단위로 괄호로 묶어주고 콤마(',')로 구분해서 이어서 값(values)을 써주면 됩니다. 

-- insert lots of values altogether INSERT INTO cust.cust_master VALUES  (4, 'yongsu', 'choi', 'M', 22, '2020-04-01') , (5, 'sohyun', 'park', 'F', 53, '2020-06-15') , (6, 'sedong', 'lim', 'M', 48, '2020-02-09'); SELECT * FROM cust.cust_master;

외부의 text, csv 파일 등의 데이터셋을 PostgreSQL, Greenplum DB로 import 하는 방법은  https://rfriend.tistory.com/432 를 참고하세요.

만약 PRIMARY KEY 칼럼(중복 금지, NULL 값 금지)에 중복된 값을 등록하려고 하면 중복금지 제약조건(uniqur constraint) 을 위배했다면서 ERROR 가 발생합니다. 

-- Error due to duplication of the primary key INSERT INTO cust.cust_master VALUES  (6, 'hoysun', 'sung', 'F', 34, '2020-04-11') ERROR:  duplicate key value violates unique constraint "cust_master_pkey" DETAIL:  Key (id)=(6) already exists. ********** Error ********** ERROR: duplicate key value violates unique constraint "cust_master_pkey" SQL state: 23505 Detail: Key (id)=(6) already exists.

위의 (3)번에서 Table을 만들 때  age int CHECK (age > 0 AND age < 120) 라는 CHECK Constraint 를 설정했었습니다. 아래처럼 만약 age = 130 값을 등록하려고 하면 제약조건 위배라서 ERROR가 발생합니다. 

-- Error due to violating of 'age' check constraint INSERT INTO cust.cust_master VALUES  (7, 'sedae', 'mun', 'M', 130, '2020-05-23'); ERROR:  new row for relation "cust_master" violates check constraint "cust_master_age_check" DETAIL:  Failing row contains (7, sedae, mun, M, 130, 2020-05-23). ********** Error ********** ERROR: new row for relation "cust_master" violates check constraint "cust_master_age_check" SQL state: 23514 Detail: Failing row contains (7, sedae, mun, M, 130, 2020-05-23).

(5) INDEX 생성하기       : CREATE INDEX index_name ON schema.table(column);

select 로 값을 조회해서 가져오거나 table을 join 하거나 할 때 주로 사용되는 칼럼에 INDEX를 걸어두면 속도를 많이 향상시킬 수 있습니다. (대신에 처음에 INDEX 생성 시간은 추가 소요)

-- CREATE INDEX [name] ON table(column) DROP INDEX IF EXISTS id_idx; CREATE INDEX id_idx ON cust.cust_master(id);

(6) VIEW 생성하기: CREATE VIEW view_name AS select ... from;

테이블에서 필요한 부분만 가져와서 가상의 VIEW 를 만들면 편리하게 select 문으로 조회를 할 수 있습니다. 여러개의 테이블을 join 하고 여러개의 where 조건을 사용하여 VIEW 를 만들 수도 있습니다. 

(가령, 현업에서 자주, 꾸준하게 사용하는 select query 문이 있다고 했을 때 IT팀에서 VIEW 를 만들어주면 협업과 IT팀이 모두 시간을 절약할 수 있어서 좋습니다.)

-- View DROP VIEW IF EXISTS cust.cust_master_view; CREATE VIEW cust.cust_master_view AS  SELECT id, first_name, last_name, joindate FROM cust.cust_master WHERE joindate >= '2020-05-01'; SELECT * FROM cust.cust_master_view;

만약 VIEW 를 만들 때 사용한 source table을 drop table 하려고 하면 ERROR가 납니다.

-- Error: cannot drop table because view depends on it DROP TABLE IF EXISTS cust.cust_master; ERROR:  cannot drop table cust.cust_master because other objects depend on it DETAIL:  view cust.cust_master_view depends on table cust.cust_master HINT:  Use DROP ... CASCADE to drop the dependent objects too.

VIEW 가 의존하고 있는 source table을 제거(drop)하고 싶을 때는 DROP TABLE table_name CASCADE; 처럼 뒤에 'CASCADE'를 써주면 삭제하려는 테이블에 의존하는 다른 객체들도 한꺼번에 같이 삭제를 해줍니다. 

-- Use 'CASCADE' to drop the dependent objects too DROP TABLE IF EXISTS cust.cust_master CASCADE; NOTICE:  drop cascades to view cust.cust_master_view

다음 포스팅에서는 테이블 내 데이터를 수정(UPDATE), 삭제(DELETE) 하는 방법(https://rfriend.tistory.com/538)을 소개하겠습니다. 

많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요. :-)