가장 간단하게는 다음과 같이 하면 된다. 그런데 쓸데없이 정보가 많다.
SELECT * FROM pg_stat_activity;이렇게 하면 현재 active 상태인 것의 query 만 보여주어 현재 어떤 쿼리를 실행하고 있는지 알 수 있다.
SELECT query FROM pg_stat_activity WHERE state = 'active';내가 실행하는 쿼리야 뻔한 수준이니만큼, 정말 현재 실행 중인 것만 보려면 좀 더 명령어를 집어 넣어주면 된다. 제외해야 할 것은 이 명령어 그 자체와 autovacumn이다.
SELECT query FROM pg_stat_activity WHERE state = 'active' AND query NOT LIKE 'autovac%' AND query NOT LIKE '%pg_stat_activity%';PostgreSQL에서 자료를 copy로 불러올 때 역슬래시가 들어가면 심각한 오류가 생긴다. escape 문자와 혼동이 되기 때문이다. 일부 테이블이 너무 크기가 작아서 확인해보니 아무래도 역슬래시와 관련되어 자료가 제대료 import 안된 것 같았다. 구글 검색을 해보니 제일 좋은 방법은 역슬래시 한 개를 두 개로 바꾸어 주는 것이다. 이를 해결하는 가장 편한 방법은 리눅스 sed를 이용하는 것이다. 역슬래시 문자가 있다면 용량이 조금 커지고, 없다면 동일한 용량의 파일이 생성된다.
sed -e 's/\\/\\\\/g' a.csv > b.csvCDM을 이용하는 사람이 생김에 따라서 person_id를 한 번 더 익명화하기로 결심했다. 매번 CDM에 자료가 추가되는 방법이면 난수를 생성하는 방법을 고민해야 할 것이다. 하지만, 수정하면서 자료를 추가하고 있기 때문에 매번 새로운 수를 만들어도 될 것 같았다. 수십만건의 난수를 효과적으로 만들기 위하여 여러 방법을 시도해 보았다. R이라면 sample(, replace = F)로 만들면 그만인데, SQL에서는 그런 명령어는 못 찾았다. 그래서 비슷한 개념으로 만들어 쿼리를 작성해 보았다.
CREATE TABLE mapping.person_anonymous_from AS SELECT DISTINCT person_id FROM cdm.person;
ALTER TABLE mapping.person_anonymous_from ADD COLUMN temp_join_a bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 );우선 person 테이블에서 고유한 person_id를 추출하여 테이블을 하나 만든다. 그리고 생성된 테이블에서 bigint 속성을 가지며 추가될 때마다 1씩 커지는 temp_join_a 컬럼을 만든다.
CREATE TABLE mapping.person_anonymous_to AS (SELECT DISTINCT trunc(random() * 1e8 + 1) AS random_key FROM generate_series(1, 1e8));
ALTER TABLE mapping.person_anonymous_to ADD COLUMN temp_join_b bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 );random() 함수는 0~1의 실수값을 생성하기 때문에 정수값으로 변환한다. 병원 환자 수를 보니 1천만개 정도 만들면 몇 년은 충분할 것 같았다. 그래서 생성된 난수에 1천만을 곱하고 1을 더한 후 정수값으로 처리한 작업을 1천만번 반복한다. 이런 방법으로 난수를 만들면 중복이 생기기 때문에 고유한 값만을 선택하여 이 값을 다른 테이블에 random_key라는 컬럼 이름으로 만든다. 이 방법은 중복되지 않는 수를 생성하는데에는 좀 멍청한 것 같다. 1억개를 만들면 60% 정도만 남는다. 그리고 마찬가지로 temp_join_b 컬럼을 만든다.
CREATE TABLE mapping.person_anonymous AS (SELECT a.person_id, b.random_key FROM mapping.person_anonymous_from AS a INNER JOIN mapping.person_anonymous_to AS b ON a.temp_join_a = b.temp_join_b);이렇게 만들어진 random_key의 순서는 충분히 임의적이라고 할 수 있다. temp_join_a에 맞추어 INNER JOIN의 형태로 결합하여 테이블을 만든다. random_key가 person_id보다 충분히 숫자가 크기 때문에 공통인 것만 선택을 하면 된다. 이러면 충분히 식별력이 없는 새로운 person_id를 만들어 낼 수 있다.
PostgreSQL 문서에 의하면 distinct는 처음 나타나는 순서대로 정렬이 되는 것이라고 한다.
오류 없이 import 가 가능하기 때문에 date 속성을 text로 받아서 처리를 하였으나, 각종 CDM 쿼리들은 start_date 를 이용하여 쿼리가 수행되기 때문에 변경하는 과정을 추가하기로 했다. 우선 절대 다수의 원자료 값들은 YYYY-MM-DD의 10글자로 되어 있는데 일부에서는 4글자 종류가 있는 것이 있었고 확인해보니 NULL으로 변환전의 ‘해당없음’이었다. 그렇다면 ‘해당없음’을 NULL로 변환한 이후에 변환 과정을 하면 된다.
ALTER TABLE cdm.drug_exposure ALTER COLUMN drug_exposure_start_date TYPE DATE USING to_date(drug_exposure_start_date, 'YYYY-MM-DD');