Translate

레이블이 PostgreSQL인 게시물을 표시합니다. 모든 게시물 표시
레이블이 PostgreSQL인 게시물을 표시합니다. 모든 게시물 표시

2019년 4월 7일 일요일

[PostgreSQL] 행 순서(ROW NUMBER)에 조건 적용하기

  오전 1:46    


개발프로그램 PostgreSQL 9.6

행 순서를 나타내려면 ROW_NUMBER() OVER  ORDER BY ... 구분을 사용하면 되고,
중첩  SELECT문을 사용하면 해당 값에 조건을 매길 수 있다.


SELECT *
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE) AS ROW, *
          FROM TEST_TABLE LIMIT 10) T




다음은 위 예제에서 짝수번째 행만 조회하는 쿼리이다.

SELECT *
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY DATE) AS ROW, *
          FROM TEST_TABLE LIMIT 10) T
  WHERE ROW%2 = 0

  

   댓글 없음:

2019년 4월 6일 토요일

[PostgreSQL, PostGIS] 음수 Latitude값을 양수로 전환할 때

  오후 11:38    



개발프로그램
Postgresql 9.6

 geometry 값에서 -180 ~ 180 범위의 Latitude 값을 0 ~ 360으로 표현하고 싶을 때
ST_SHIFT_LONGITUDE(geometry)를 사용하면 된다.

만약 원래부터 양수인 경우 알아서 그대로 나오고, 내부 연산도 하지 않는지 조회 속도에 영향을 주지도 않는다.

예제)

SELECT GEOM, ST_SHIFT_LONGITUDE(GEOM) FROM TEST_TABLE WHERE ST_X(GEOM) < 0


   /

   댓글 없음:

[PostgreSQL, Node.js] 소켓 통신 실시간 INSERT 이벤트 받기

  오후 11:24    


개발프로그램Postgresql 9.6

Node.js 소켓 통신 중
PostgreSQL DB에서 특정 테이블에 INSERT가 발생한 것을 캐치하고 싶다면
우선 PostgreSQL에서 다음과 같은 FUCNTION와 TRIGGER를 생성한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
-- create notify function
CREATE FUNCTION notify_trigger() RETURNS trigger AS $$
DECLARE
BEGIN
PERFORM pg_notify('watch_realtime_table', row_to_json(NEW)::text);
RETURN new;
END;
$$ LANGUAGE plpgsql;


-- create insert trigger
CREATE TRIGGER watch_realtime_table_trigger AFTER INSERT ON realtime_table
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

각각을 설명하자면
notify_trigger은 watch_realtime_table이라는 채널에 JSON 형식의 데이터를 nofity하는 역할을 하고,
watch_realtime_table_trigger는 realtime_table이라는 테이블에 INSERT가 발생하면 해당 행들에 대해 notify_trigger()함수를 실행,
즉 결과적으로 realtime_table에 INSERT가 발생할 때마다 해당 데이터들을 watch_realtime_table라는 채널에 notify한다는 것이다.

더 간단히 말하자면 watch_realtime_table라는 채널을 구분자로 INSERT된 값들을 받아올 수 있다는 뜻이다.

이제 Node.js 부분에서 해당 채널을 LISTEN하는 코드를 추가하면 되는데, 이는 다음과 같다.


1
2
3
4
5
6
7
8
var pgp = require('pg-promise')(/*options*/);

var client = new pgp.pg.Client(dburl);
client.connect();
client.query('LISTEN "watch_realtime_table"');
client.on('notification', function(data) {
    console.log(data.payload);
});

그러면 해당 데이터들을 INSERT때마다 실시간으로 JSON 형식으로 console에서 확인할 수 있다.

또한 LISTEN 작업을 중단하려면
client.query('UNLISTEN "watch_realtime_table"'); 를 이용한다.


- 참고 사이트

   /

   댓글 없음:

2018년 9월 8일 토요일

[PostgreSQL] COPY FROM CSV 사용 시 Date, Time Format 지정하기

  오전 12:25    


개발프로그램
Postgresql 9.6


csv파일의 값 형태가 컬럼형에 맞지 않아 직접 넣을 수 없을 때, 
formatting을 해야하는 경우가 있다.
예를 들면 timestamp형 데이터로 09:30:10을 넣어야 하는데
csv에 093010라고 적혀있어서 오류가 뜨는 경우이다.

결론만 말하자면 to_timestamp()를 이용하는 간편한 방법은 없다.

총 3가지가 있는데,
2, 3번의 경우 임시 데이터를 생성하므로 사용할 데이터가 대용량일 경우 
작업 PC의 하드 용량에 어느 정도 여유분이 필요하다는 주의사항이 있다.

사용할 table의 컬럼은 대충
test_table (
id INT4,
date DATE,
time TIMESTAMP)
라는 전제로 하겠다. (time 외 다른 컬럼에 별 의미는 없다)
csv 파일명은 test.csv, 테이블과 동일한 컬럼 형태에 ','로 구분됐다는 전제를 두겠다.


1. 초반에 VARCHAR형으로 선언한 후 ALTER문으로 TYPE 변경하기
일단 VARCHAR형으로 선언하면 문자열이기 때문에 어떤 형식이든 입력은 성공한다.
그리고 to_timestamp()를 써서 컬럼형을 변경하는 방법이다.

장점은 임시적인 컬럼이나 파일이 필요없다는 것,
단점은 동일한 형식의 csv파일을 추가적으로 COPY할 수 없기 때문에
필요할 때는 결국 2 또는 3번 방법을 이용해야 한다는 것이다.

(HEADER는 헤더를 없애고 읽는다는 뜻, ENCODING은 말 그대로 csv 파일의 인코딩이다.)
COPY test_table(id, date, time) FROM 'test.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

ALTER TABLE test_table ALTER COLUMN time TYPE TIME USING (to_timestamp(time_char, 'HH24MISS'));


2. VARCHAR형 임시 컬럼 생성 후 본래 컬럼에 다시 UPDATE하기
예를 들어 time_char라는 임시 컬럼을 만들고 거기에 csv의 자료형을 넣고,
본래 써야 할 time 컬럼은 null로 둔 뒤
UPDATE문을 써서 변경하는 방법이다.

time_char은 이후에 컬럼을 제거해도 되지만
이후에 또 동일한 형식의 csv파일을 추가적으로 COPY할 수도 있을 경우에는
일단 냅두고 UPDATE문에 null 조건을 달아 동일한 작업을 하면 되지 않을까 싶다.

COPY test_table(id, date, time_char) FROM 'test.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

UPDATE dtg_data SET time = to_timestamp(time_char, 'HH24MISS');


3. csv 파일 자체를 해당 포맷에 맞게 변환하기
데이터의 용량이 클 경우 무조건 이 방법을 택하기를 권한다.
실제로 나는 csv파일만 약 30GB인 파일들로 작업을 했는데,
하나당 COPY에도 약 3일이 걸리고 UPDATE에도 마찬가지로 3일정도가 걸려서
실제로 많은 시간을 소모했으나
이 방법을 마지막에서야 깨닫고 시도해보니 훨씬 시간이 단축되었다.
(하지만 COPY할때 걸리는 3일은 어쩔 수가 없었던...)

언어는 무얼 쓰든 자유이나, 나는 이전에 Python으로 csv 관련 작업을 한 적이 있어 익숙한 편이라 이것을 택했다.

지금 예제의 프로그램을 써보자면 (csv파일의 위치는 C:/ 바로 아래라고 하자)

import csv
with open('C:\\test.csv', 'r') as csvfile:
    reader = csv.DictReader(csvfile)
    with open('C:\\test2.csv', 'w', newline='') as csvfile2:
        writer = csv.writer(csvfile2, delimiter = ',')
        writer.writerow(reader.fieldnames)
        for row in reader:
            split_time = row['time']
            time = split_time[0] + split_time[1] + ':' + split_time[2] + split_time[3] + ':' + split_time[4] + split_time[5]
            row['time'] = time
            writer.writerow(row.values())

test2의 newline='' 속성은 개행을 없앤다는 뜻이다.
쓰지 않을 경우 각 행들이 모두 한 줄씩의 공백(즉 엔터를 두번 친 모양)을 가진 채 생긴다.

단순히 time값을 한글자씩 쪼개어 사이사이에 ':'를 입력하고 time행을 수정,
그 후 행 단위로 csv를 입력해나가는 간단한 프로그램이다.

이렇게 하면 포맷이 설정된 새로운 test2.csv라는 파일이 형성된다.
(정확히는 측정하지 못했으나 30GB 처리 소요시간이 2~3시간쯤으로 추정된다.)

그리고 앞의 예제들과 마찬가지로 COPY를 쓰면 된다.

COPY test_table(id, date, time) FROM 'test2.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF-8';

데이터 추가시에도 이와 같이 새로운 csv 파일을 생성하고, COPY하면 된다.

참고 사이트: https://www.postgresql.org/message-id/55BA474E.7080605%40aklaver.com

   /

   댓글 없음:

피드 구독하기: 글 (Atom)