'Greenplum Database' 태그의 글 목록 (2 Page)

'Greenplum Database'에 해당되는 글 21건

2019.12.15 [PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum)
2019.08.25 [Greenplum] 오픈소스 Greenplum DB와 Apache MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib)
2019.05.16 [Greenplum DB] GPDB에 PL/R Language Extension, R 패키지 수동 설치 방법
2019.04.27 [Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore)
2019.04.11 [Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법
2019.04.10 [Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기
2019.03.27 [Greenplum DB] GPDB docker에 PostGIS 설치하기
2019.03.21 [Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot)
2019.03.10 [Greenplum DB] 외부 데이터를 Table에 업로드하는 5가지 방법 : CREATE EXTERNAL TABLE, COPY, INSERT INTO VALUES(), pd.DataFrame.to_sql(), DBeaver import
2018.08.13 [Greenplum DB] Greenplum DB, MADlib, PL/R, PL/Python을 Docker Image를 이용하여 환경구성 하기

[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum)

Greenplum and PostgreSQL Database 2019. 12. 15. 23:22

이번 포스팅에서는 PostgreSQL, Greenplum Database를 이용해서 시계열 데이터를 특정 시간 단위 구간(예: 10분 단위, 1시간 단위, 1일 단위, 1달 단위, 1년 단위 등) 별로 집계/ 요약하는 방법을 소개하겠습니다.

(* python pandas로 특정 시간 단위 구간별로 시계열 데이터를 집계하는 방법은 https://rfriend.tistory.com/495 참조)

-- 사전 준비 작업 --

(1) '년-월-일' date dimension table 만들기

(2) '시간:분' time dimension table 만들기

(3) '년-월-일 시간:분' cross-join 한 date & time dimension table 만들기

(4) 예제 시계열 데이터(time series) table 만들기

==> '날짜-시간' 기준정보를 '시계열 거래 데이터'에 merge 하기

-- 10분 단위 구간별로 시계열 데이터 집계/ 요약하기 --

(5) 10분 단위 구간별로 첫번째 행 값(first row value), 마지막 행 값(last row value) 구하기

(6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기

(7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중위수(median), 3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기

(8) 10분 단위 구간별로 평균(mean), 표본 분산(sample variance), 표본 표준편차(sample standard deviation) 구하기

(9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)

(1) '년-월-일' date dimension table 만들기

날짜(년-월-일)를 기준으로 년(year), 분기(quarter), 월(month), 주(week), 일(day), 공휴일(holiday) 등의 다양한 관점의 group by 할 수 있는 구분자를 생성해놓은 테이블을 만들어보겠습니다. 날짜(년-월-일) 관련 기준 정보, 매핑 테이블이라고 보면 되겠습니다.

아래 예제는 2019년 12월1일 ~ 2019년 12월 31일 까지의 31일 기간의 날짜 데이터 기준정보를 만들었습니다.

만약 2019년 1월1일~2019년 12월 31일 까지의 365일 전체의 날짜 데이터 기준정보를 만들고 싶다면 from 절을 아래와 같이 수정해주면 됩니다.

[ 2019년 1월 1일~2019년 12월 31일 (365일) 기간 일 별 기준정보 테이블 만드는 FROM 절 generate query ]

FROM (

SELECT '2019-01-01'::DATE + SEQUENCE.DAY AS datum

FROM generate_series(0, 364) AS SEQUENCE(DAY)

GROUP BY SEQUENCE.DAY

) DQ

아래의 postgresql wiki 페이지의 sql query를 참고하였습니다.

* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions

------------------------
-- date dimension table
------------------------
-- create 'dim_date' table
DROP TABLE IF EXISTS dim_date;
CREATE TABLE dim_date (
date DATE NOT NULL,
year INT NOT NULL,
month INT NOT NULL,
month_nm VARCHAR(9) NOT NULL,
day INT NOT NULL,
day_of_year INT NOT NULL,
weekday_nm VARCHAR(9) NOT NULL,
calender_week INT NOT NULL,
formatted_date CHAR(12) NOT NULL,
quartal CHAR(2) NOT NULL,
year_quartal CHAR(7) NOT NULL,
year_month CHAR(7) NOT NULL,
year_calendar_week CHAR(7) NOT NULL,
weekend VARCHAR(7) NOT NULL,
holiday_korea VARCHAR(20) NOT NULL,
period VARCHAR(20) NOT NULL,
cwstart DATE NOT NULL,
cwend DATE NOT NULL,
month_start DATE NOT NULL,
month_end DATE NOT NULL
);

-- create index on 'date'
CREATE INDEX dim_date_date_idx ON dim_date(date);

-- insert generate date dimension data (from 2019-01-01 to 2019-12031)
-- into 'dim_date' table
INSERT INTO dim_date (
SELECT
datum AS date,
EXTRACT(YEAR FROM datum)::INT AS year,
EXTRACT(MONTH FROM datum)::INT AS month,
-- Localized month name
to_char(datum, 'TMMonth') AS month_nm,
EXTRACT(DAY FROM datum)::INT AS day,
EXTRACT(doy FROM datum)::INT AS day_of_year,
-- Localized weekday
to_char(datum, 'TMDay') AS weekday_nm,
-- ISO calendar week
EXTRACT(week FROM datum)::INT AS calendar_week,
to_char(datum, 'dd. mm. yyyy') AS formatted_date,
'Q' || to_char(datum, 'Q') AS quartal,
to_char(datum, 'yyyy/"Q"Q') AS year_quartal,
to_char(datum, 'yyyy/mm') AS year_month,
-- ISO calendar year and week
to_char(datum, 'iyyy/IW') AS year_calendar_week,
-- Weekend
CASE WHEN EXTRACT(isodow FROM datum) IN (6, 7) THEN 'Weekend'
ELSE 'Weekday' END
AS weekend,
-- holidays for Korea in year 2019
CASE WHEN to_char(datum, 'MMDD')
IN ('0101', '0205', '0206', '0207', '0301', '0505', '0512',
'0606', '0815', '0912', '0913', '0914', '1003', '1009', '1225')
THEN 'Holiday' ELSE 'No holiday' END
AS holiday_korea,
-- Some periods of the year, adjust for your organisation and country
CASE WHEN to_char(datum, 'MMDD') BETWEEN '0701' AND '0831'
THEN 'Summer break'
WHEN to_char(datum, 'MMDD') BETWEEN '1115' AND '1225'
THEN 'Christmas season'
WHEN to_char(datum, 'MMDD') > '1225' OR to_char(datum, 'MMDD') <= '0106'
THEN 'Winter break'
ELSE 'Normal' END
AS period,
-- ISO start and end of the week of this date : from monday to sunday
datum + (1 - EXTRACT(isodow FROM datum))::INTEGER AS CWStart, -- monday
datum + (7 - EXTRACT(isodow FROM datum))::INTEGER AS CWEnd, -- sunday
-- Start and end of the month of this date
datum + (1 - EXTRACT(DAY FROM datum))::INTEGER AS month_start,
((datum + (1 - EXTRACT(DAY FROM datum))::INTEGER + '1 month'::INTERVAL)::DATE
- '1 day'::INTERVAL)::DATE AS month_end
FROM (
SELECT '2019-12-01'::DATE + SEQUENCE.DAY AS datum
FROM generate_series(0, 30) AS SEQUENCE(DAY)
GROUP BY SEQUENCE.DAY
) DQ
ORDER BY 1);

SELECT * FROM dim_date ORDER BY date;

(2) '시간:분' time dimension table 만들기

시간('시간:분')을 기준으로 시간(time), 분(minute), 10분 단위 구간(time_span_min_10), 1시간 단위 구간(time_span_hour_1), 일 구분 범주(day_category) 등으로 구분해서 집계/요약할 수 있는 구분자, 기준 정보, 매핑 테이블을 만들어보겠습니다.

* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions

------------------------
-- time dimension table
------------------------
-- create dimension table 'dim_time'
DROP TABLE IF EXISTS dim_time;
CREATE TABLE dim_time (
time_of_day CHAR(6) NOT NULL,
hour INT NOT NULL,
minute INT NOT NULL,
time_span_min_10 CHAR(15) NOT NULL,
time_span_hour_1 CHAR(15) NOT NULL,
day_category VARCHAR(15) NOT NULL
);

-- create index on 'time_of_day'
CREATE INDEX dim_time_time_of_day_idx ON dim_time(time_of_day);

-- insert generated time_of_day data into 'dim_time' table
INSERT INTO dim_time (
SELECT
TO_CHAR(MINUTE, 'hh24:mi') AS time_of_day,
-- Hour of the day (0 - 23)
EXTRACT(HOUR FROM MINUTE)::INTEGER AS hour,
-- Minute of the day (0 - 1439)
(EXTRACT(HOUR FROM MINUTE)*60 +
EXTRACT(MINUTE FROM MINUTE))::INT AS minute,
-- Extract and format 10 minutes time span
TO_CHAR(
MINUTE -
(EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10
|| 'minutes')::INTERVAL, 'hh24:mi') || ' – ' ||
TO_CHAR(
MINUTE -
(EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10
|| 'minutes')::INTERVAL + '10 minutes'::INTERVAL, 'hh24:mi')
AS time_span_min_10,
-- Extract and format 1 hour time span
TO_CHAR(
MINUTE -
(EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60
|| 'minutes')::INTERVAL, 'hh24:mi') || ' – ' ||
TO_CHAR(
MINUTE -
(EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60
|| 'minutes')::INTERVAL + '60 minutes'::INTERVAL, 'hh24:mi')
AS time_span_hour_1,
-- Names of day periods
CASE WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '06:00' AND '08:59'
THEN 'morning'
WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '09:00' AND '11:59'
THEN 'AM'
WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '12:00' AND '17:59'
THEN 'PM'
WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '18:00' AND '21:59'
THEN 'evening'
ELSE 'night'
END AS day_category
FROM (SELECT '00:00'::TIME + (SEQUENCE.MINUTE || ' minutes')::INTERVAL
AS MINUTE
FROM generate_series(0,1439) AS SEQUENCE(MINUTE)
GROUP BY SEQUENCE.MINUTE
) DQ
ORDER BY 1
);

SELECT * FROM dim_time ORDER BY time_of_day LIMIT 60;

(3) '년-월-일 시간:분' cross-join 한 date & time dimension table 만들기

(1)번에서 만든 dim_date 테이블과 (2)번에서 만든 dim_time 테이블을 cross join 하여 'dim_date_time' 테이블을 만들어보겠습니다.

31일 (dim_date) * 24시간 * 60분 (dim_time) = 44,640 개의 전체 행 (total number of rows) 을 가지는 '년-월-일 시간:분' (dim_date_time) 기준정보 매핑 테이블을 가지고 이제 년 ~ 분 단위를 넘나들면서 원하는 시간 단위 구간별로 시계열 데이터를 집계/ 요약할 수 있습니다.

앞으로 join 의 기준이 될 '년-월-일 시간:분'(dt) 칼럼에 대해서는 빠른 join을 위해서 index 를 생성해주었습니다.

---------------------------------------
-- cross join b/w dim_date and dim_time
---------------------------------------
DROP TABLE IF EXISTS dim_date_time;
CREATE TABLE dim_date_time AS (
SELECT
to_char((dim_date.date || ' ' || dim_time.time_of_day)::timestamp,
'yyyy-mm-dd hh24:mi') AS dt
, dim_date.*
, dim_time.*
FROM dim_date
CROSS JOIN dim_time
);

-- create index on 'dt'
CREATE INDEX dim_date_time_dt_idx ON dim_date_time(dt);

SELECT * FROM dim_date_time ORDER BY dt LIMIT 5;

SELECT COUNT(*) FROM dim_date_time;
44640

(4) 예제 시계열 데이터(time series) table 만들기 ==> '날짜-시간' 기준 정보 merge 하기

간단한 예제로 '년-월-일 시간:분'(dt), 가격(price), 수량(amt) 의 3개 칼럼을 가지는 거래 시계열 데이터 테이블을 만들어보겠습니다.

---------------------
-- Transaction table
---------------------
-- create ts table
DROP TABLE IF EXISTS ts;
CREATE TABLE ts (
dt TEXT NOT NULL
, price NUMERIC NOT NULL
, amt INT NOT NULL
);

-- insert transaction data into ts table
INSERT INTO ts VALUES
('2019-12-19 00:00', 12, 4),
('2019-12-19 00:02', 21, 2),
('2019-12-19 00:04', 41, 1),
('2019-12-19 00:06', 79, 4),
('2019-12-19 00:08', 61, 2),
('2019-12-19 00:10', 81, 1),
('2019-12-19 00:12', 24, 3),
('2019-12-19 00:14', 62, 1),
('2019-12-19 00:16', 76, 3),
('2019-12-19 00:18', 63, 1),
('2019-12-19 00:20', 95, 2),
('2019-12-19 00:22', 82, 1),
('2019-12-19 00:24', 82, 3),
('2019-12-19 00:26', 70, 1),
('2019-12-19 00:28', 30, 4),
('2019-12-19 00:30', 33, 1),
('2019-12-19 00:32', 22, 2),
('2019-12-19 00:34', 77, 3),
('2019-12-19 00:36', 58, 3),
('2019-12-19 00:38', 96, 3),
('2019-12-19 09:02', 10, 2),
('2019-12-19 09:08', 50, 3),
('2019-12-19 14:04', 20, 1),
('2019-12-19 16:00', 10, 5),
('2019-12-19 21:00', 30, 4)
;

SELECT * FROM ts ORDER BY dt;

다음으로, '시계열 거래 데이터'(ts table)에 '날짜-시간 기준정보'(dim_date_time table) 를 '년-월-일 시간:분'(dt) 칼럼을 기준으로 병합(left outer join)해보겠습니다. 이제 특정 시간 단위 구간별로 집계, 요약할 수 있는 데이터 준비가 되었습니다.

※ 만약 '시계열 거래 데이터'에 특정 날짜-시간대에 값이 존재하던지 말던지 간에 동일한 특정 시간 단위 구간별로 값을 집계, 요약하고자 한다면, (즉, '시계열 거래 데이터'가 없으면 NULL이나 '0'으로 집계) LEFT OUTER JOIN 할 때 왼쪽에 '날짜-시간 기준정보'(dim_date_time) 테이블을 놓고, 오른쪽에 '시계열 거래 데이터'(ts) 테이블을 놓고 left outer join 을 해주어야 합니다.

------------------------------------
-- merge 'date-time' dimension table
------------------------------------
DROP TABLE IF EXISTS ts_dt;
CREATE TABLE ts_dt AS (
SELECT
a.*
, b.date
, b.time_span_min_10
, b.time_span_hour_1
, b.day_category
FROM ts a
LEFT OUTER JOIN dim_date_time b ON a.dt = b.dt
);

SELECT * FROM ts_dt ORDER BY dt LIMIT 5;

(5) 10분 단위 구간별로 첫번째 행 값(first-row value), 마지막 행 값(last row value) 구하기

시계열 데이터를 시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별로 첫번째 행의 값(first row's value)을 구하는 방법에는 두가지가 있습니다.

첫번째 방법은 ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt) 의 window function을 사용하는 것입니다. (오름차순 정렬 order by dt)

두번째 방법은 DISTINCT ON (date, time_span_min_10) 함수를 사용하는 것입니다.

-- (1) first, last by time_span_min_109
-- (1-1-1) way 1: first using ROW_NUMBER()
WITH time_span_min_10_tmp AS (
SELECT
date
, time_span_min_10
, price
, ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt)
AS row_num
FROM ts_dt
)
SELECT
date
, time_span_min_10
, price AS price_10m_first
FROM time_span_min_10_tmp
WHERE row_num = 1
;

-- (1-1-2) way 2: first using DISTINCT ON
SELECT DISTINCT ON (date, time_span_min_10)
date
, time_span_min_10
, price AS price_10m_first
FROM ts_dt
ORDER BY date, time_span_min_10, dt, price;

시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별 마지막 행의 값(last row's value)을 구하는 두가지 방법은 위에서 소개한 첫번째 행의 값을 구하는 방법에서 정렬 기준을 정반대로 해서, 내림차순 정렬(ORDER BY dt DESC) 을 해주면 됩니다.

-- (1-2-1) way 1: last using ROW_NUMBER()
WITH time_span_min_10_tmp AS (
SELECT
date
, time_span_min_10
, price
, ROW_NUMBER()
OVER (PARTITION BY date, time_span_min_10 ORDER BY dt DESC)
AS row_num
FROM ts_dt
)
SELECT
date
, time_span_min_10
, price AS price_10m_last
FROM time_span_min_10_tmp
WHERE row_num = 1
;

-- (1-2-2) way 2: last using DISTINCT ON
SELECT
DISTINCT ON (date, time_span_min_10)
date
, time_span_min_10
, price AS price_10m_last
FROM ts_dt
ORDER BY date, time_span_min_10, dt DESC, price;

(6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기

10분 단위 구간(10 minutes time span) 별로 가격(price) 칼럼의 합(sum)을 먼저 구하고, ==> 시간 순서대로 정렬된 상태에서 10분 단위 구간별로 SUM() OVER () window function을 사용해서 누적 합(cumulative sum)을 구해보겠습니다.

-- (2) sum, cumulative sum
-- sum, cumulative sum by time_span_min_10
WITH price_10m_sum_tmp AS (
SELECT
date
, time_span_min_10
, sum(price) AS price_10m_sum
FROM ts_dt
GROUP BY 1, 2
)
SELECT
date
, time_span_min_10
, price_10m_sum
, SUM(price_10m_sum)
OVER ( ORDER BY time_span_min_10
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS price_10m_cumsum
FROM price_10m_sum_tmp
;

집계/ 요약을 하는 시간 단위 구간을 위의 10분에서 이번에는 1 시간 (1 hour time span) 단위 구간별로 변경해서 가격 합(sum)과 가격 누적합(cumulative sum)을 구해보겠습니다. 위의 (3)번에서 '날짜-시간 기준정보 테이블'(dim_date_time table)에서 '1시간 단위 구간' 칼럼 (time_span_hour_1) 을 만들어놓았었기 때문에 group by 집계/ 요약하는 기준이 되는 칼럼을 'time_span_min_10'을 'time_span_hour_1'으로 변경해주기만 하면 됩니다.

-- sum, cumulative sum by time_span_hour_1
WITH price_1h_sum_tmp AS (
SELECT
date
, time_span_hour_1
, sum(price) AS price_1h_sum
FROM ts_dt
GROUP BY 1, 2
)
SELECT
date
, time_span_hour_1
, price_1h_sum
, SUM(price_1h_sum)
OVER ( ORDER BY time_span_hour_1
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS price_1h_cumsum
FROM price_1h_sum_tmp;

(7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중앙값(median),
3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기

최소값은 min(), 최대값은 max(), 1사분위수/중앙값/3사분위수는 percentile_disc(), 범위는 max() - min() 함수를 이용해서 time_span_min_10 으로 group by 집계, 요약하였습니다.

1/2/3 사분위수(quantile) 구하는 percentile_disc(0.25 / 0.5 / 0.75) within group (order by price) 함수를 사용해서 IQR ( = q3 - q1) 구할 수도 있습니다.

-- (3) min, q1, median, q3, max, range by time_span_min_10
SELECT
date
, time_span_min_10
, MIN(price) AS price_10m_min
, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY price) AS price_10m_q1
, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) AS price_10m_median
, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY price) AS price_10m_q3
, MAX(price) AS price_10m_max
, MAX(price) - MIN(price) AS price_10m_range
FROM ts_dt
GROUP BY 1, 2
ORDER BY 1, 2;

(8) 10분 단위 구간별로 평균(mean), 분산(variance), 표준편차(standard deviation) 구하기

10분 단위 구간(10 minutes time span) 별로 표본 분산(sample variance)은 variance() 함수, 표본 표준편차(sample standard deviation)은 stddev() 함수를 이용하여 구하였습니다.

10분 단위 구간별로 만약 행의 개수가 1개만 있는 경우 분산, 표준편차를 구할 수 없으므로 NULL 값을 반환하게 되는데요, 이런 경우 coalesce() 를 사용해서 '0'으로 채워주었습니다.

-- (4) mean, variance, standard deviation by time_span_min_10
SELECT
date
, time_span_min_10
, AVG(price) AS price_10m_mean
, COALESCE(VARIANCE(price), 0) AS price_10m_var
, COALESCE(STDDEV(price), 0) AS price_10m_stddev
FROM ts_dt
GROUP BY 1, 2
ORDER BY 1, 2;

(9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)

-- (5) amount-weighted average price
WITH price_mult_amt_tmp AS (
SELECT
dt
, date
, time_span_min_10
, amt
, (price * amt) AS price_mult_amt
FROM ts_dt
)
SELECT
date
, time_span_min_10
, ROUND((sum(price_mult_amt) / sum(amt)), 2) AS price_10m_amount_weighted_avg
FROM price_mult_amt_tmp
GROUP BY 1, 2
ORDER BY 1, 2;

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

이번 포스팅이 도움이 되었다면 아래의 '공감~

'를 꾹 눌러주세요. :-)

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum] PL/R Error ERROR: Error on receive from seg0 slice1: server closed the connection unexpectedly 확인사항과 대처 방안 (0)	2020.04.01
[Greenplum] PL/R을 이용한 그룹별 선형회귀모형 분산 병렬 학습 및 예측 (0)	2020.03.29
[Postgres, Greenplum] 현재 수행중인 query 조회하고 강제 종료 (kill) 하는 방법 (0)	2019.11.28
[Greenplum] 오픈소스 Greenplum DB와 Apache MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib) (0)	2019.08.25
[GPDB, PostgreSQL] Python DataFrame을 Sqlalchemy engine을 이용해 DB Table에 직접 쓰기 : df.to_sql() (0)	2019.07.28

Posted by Rfriend

[Greenplum] 오픈소스 Greenplum DB와 Apache MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib)

Greenplum and PostgreSQL Database 2019. 8. 25. 10:08

제가 2019년 1월 30일에 Pivotal 의 Data Science Webinar 에서 발표했었던 "오픈소스 그림플럼 DB와 아파치 MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib)" 자료를 공유합니다.

[ Agenda ]

1. Why Graph Analytics?

2. What is Graph Analytics?

3. Graph Analytics with Apache MADlib on Greenplum in parallel

presented by Hongdon Lee, Pivotal Senior Data Scientist

[ 파일 첨부: Graph Analytics with Greenplum and Apache MADlib ]

Pivotal_Graph_Analytics_w_MADlib_GPDB_20190130.pdf

개별 단위별 객체, 대상만을 놓고 나누어 분석하는 환원주의(Reductionism: Divide and conquer) 대비 전체를 하나로 놓고 관계와 연결에 주목해서 분석하면 새로운 인사이트를 얻을 수 있다는 전체론 관점(Holism: Everything has to be understood in relation to the whole)의 분석 기법으로 그래프 이론(graph theory)과 네트워크 분석(network analysis)을 소개하는 자료입니다.

그래프 분석 시 도전 사항으로 복잡도가 매우 높아 연산 부하가 크고 분석이 불가능하거나 시간이 오래 걸린다는 점입니다. 이를 해결할 수 있는 대안으로 MPP(Massively Parallel Processing) 아키텍처 기반의 오픈소스 Greenplum DB와 Apache MADlib을 이용한 그래프, 네트워크 분석의 In-Database 병렬 처리 분석을 소개하였습니다.

Webinar 발표할 때 재미를 더하기 위해서 슬라이드 중간 중간에 제 사진도 좀 넣어보았는데요, 이렇게 파일 공유하려니 좀 쑥스럽기도 하네요. ^^; Webinar 발표할 때는 우리나라말로 했었는데요, 글로벌 데이터 팀 구성원들한테도 공유할 생각으로 슬라이드는 영어로 만들었습니다. 중간에 수식도 많은데 영어라서 눈에 잘 안들어온다는 피드백이 있었는데요, 양해 바랍니다. ^^;;;

알고리즘 세부 소개하는 부분은 위키피디아를 많이 참고하였습니다.

저는 실제 프로젝트 하면서 그래프 / 네트워크 분석이 제공할 수 있는 인사이트의 유용함을 경험하기도 했구요, Greenplum DB 에서 MADlib으로 분석하면서 In-DB parallel processing의 강력함을 경험했던 지라 기회되면 꼭 한번 소개를 하고 싶었던 주제였습니다. R이나 Python으로 노트북에서 network 분석 공부할 땐 아무 문제 없다가도, 실제 기업이나 공공기관의 수 terabyte, petabyte 급 데이터를 마주하게 되면 당황하기 마련인데요, 이럴 때 사용할 수 있는 방법, 툴입니다.

주요 장표 몇 장만 아래에 화면 캡쳐한거 소개하자면요,

Everything is connected!

Network: Everywhere with Everything, All the time

What is Graph Theory?

Graph algorithms and measures

Tools for Graph Analytics

Apache MADlib: Scaleable, In-Database Machine Learning in SQL

Apache MADlib: Graph Analytics Functions

그래프/ 네트워크 분석 주제 중에서 Page Rank 에 대한 간단한 예제 SQL 코드와 Graphviz, PyGraphviz를 활용한 네트워크 시각화 코드도 공유합니다.

[ 그래프 분석 MADlib SQL codes ]

graph_pagerank_madlib.sql

----------------------------------------------

-- Graph Analytics with Greenplum and MADlib

----------------------------------------------

-- CREATE VERTEX TABLE

DROP TABLE IF EXISTS vertex;

CREATE TABLE vertex(

id INTEGER

) DISTRIBUTED RANDOMLY;

INSERT INTO vertex

VALUES

(0),

(1),

(2),

(3),

(4),

(5),

(6);

select * from vertex

-- CREATE EDGE TABLE

DROP TABLE IF EXISTS edge;

CREATE TABLE edge(

src INTEGER,

dest INTEGER,

user_id INTEGER

)

DISTRIBUTED BY (user_id);

INSERT INTO edge

VALUES

(0, 1, 1), (0, 2, 1), -- user id 1

(0, 4, 1), (1, 2, 1),

(1, 3, 1), (2, 3, 1),

(2, 5, 1), (2, 6, 1),

(3, 0, 1), (4, 0, 1),

(5, 6, 1), (6, 3, 1),

(0, 1, 2), (0, 2, 2), -- user id 2

(0, 4, 2), (1, 2, 2),

(1, 3, 2), (2, 3, 2),

(3, 0, 2), (4, 0, 2),

(5, 6, 2), (6, 3, 2);

select * from edge;

-- (1) Compute the PageRank with All IDs

DROP TABLE IF EXISTS pagerank_out, pagerank_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_out' -- Output table of RageRank

, NULL -- Default damping factor (0.85)

);

SELECT * FROM pagerank_out ORDER BY pagerank DESC;

-- (2) Compute the PageRank of vertices associated with each user using the grouping feature

DROP TABLE IF EXISTS pagerank_gr_out, pagerank_gr_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_gr_out' -- Output table of PageRank

, NULL -- Default damping factor (0.85)

, NULL -- Default max iterations (100)

, 0.00000001 -- Threshold

, 'user_id'); -- Grouping column name

SELECT * FROM pagerank_gr_out ORDER BY user_id, pagerank DESC;

-- (3) Personalized PageRank of vertices {2, 4}

DROP TABLE IF EXISTS pagerank_pers_out, pagerank_pers_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_pers_out' -- Output table of PageRank

, NULL -- Default damping factor (0.85)

, NULL

-- Default max iterations (100)

, NULL -- Default Threshold (1/number of vertices*1000)

, NULL -- No Grouping

, '{2, 4}' -- Personalization vertices

);

SELECT * FROM pagerank_pers_out ORDER BY pagerank DESC;

SELECT * FROM pagerank_pers_out_summary;

[ Graphviz를 활용한 네트워크 시각화 Python codes ]

NW_visualization.py

#!/usr/bin/env python2

# -*- coding: utf-8 -*-

"""

Created on Tuesday Jau 29 2019

@author: Hongdon Lee

"""

#%% network visualization using Graphviz

import numpy as np

import pandas as pd

import pygraphviz as pgv

def run_query(query):

import pandas as pd

import psycopg2 as pg

# DB Connection

conn = pg.connect(host='localhost',

port='5432',

dbname='gpadmin',

user='gpadmin',

password='pivotal')

# Get a DataFrame

query_result = pd.read_sql(query, conn)

conn.close()

return query_result

#%% Network Edge Table

query = """

select a.*, b.pagerank

from edge a

left outer join pagerank_out b on a.src = b.id

;

"""

edge_pagerank = run_query(query)

#%% PageRank Values

query = """

select * from pagerank_out;

"""

pagerank_out = run_query(query)

#%% NW Visualization using Graphviz with different size proportional to PageRank

import pygraphviz as pgv

# Generating the output flow_graph with PyGraphviz

flow_graph = pgv.AGraph(strict=False, directed=True) # directed graph

# Flow Direction(Left to Right, or Top to Bottom)

flow_graph.graph_attr['rankdir'] = 'LR' # from Left to Right

# Node Shape

flow_graph.node_attr['shape'] = 'circle'

# Making node with different size proportional to PageRank

for i in range(len(pagerank_out)):

label_text = str(pagerank_out.id[i]) + '\n(' + str(pagerank_out.pagerank[i].round(decimals=2)) + ')'

node_width = pagerank_out.pagerank[i]*10

node_height = pagerank_out.pagerank[i]*10

flow_graph.add_node(str(pagerank_out.id[i]),

label=label_text,

**{'width': str(node_width),

'height': str(node_height)})

# Adding edge with different color by user_id

colors = ['blue', 'red']

for i in range(len(edge_pagerank)):

if edge_pagerank.user_id[i] == 1:

color_text = colors[0]

else:

color_text = colors[1]

flow_graph.add_edge(str(edge_pagerank.src[i])

, str(edge_pagerank.dest[i])

, color = color_text)

#----- Finally, Draw the Network Diagram using dot program :-)

flow_graph.draw("/Users/ihongdon/Documents/nw_diagram.png", prog='dot')

[ Docker image를 이용해서 Greenplum, MADlib, PL/R, PL/Python 분석 환경 구성하는 방법 (싱글 노드의 로컬 기능 테스트, 공부 용도로)]

https://rfriend.tistory.com/379

https://hub.docker.com/r/hdlee2u/gpdb-analytics

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

ps. 앤 해서웨이(ANNE HATHAWAY)와 연결해주실 분 계신가요? 6 degrees of separation 의 기적이 저에게도 일어날 수 있으려나요? ^^"

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum) (0)	2019.12.15
[Postgres, Greenplum] 현재 수행중인 query 조회하고 강제 종료 (kill) 하는 방법 (0)	2019.11.28
[GPDB, PostgreSQL] Python DataFrame을 Sqlalchemy engine을 이용해 DB Table에 직접 쓰기 : df.to_sql() (0)	2019.07.28
[GPDB, Postgres] Python DataFrame을 Postgresql, Greenplum DB에 Copy 해서 넣는 방법 (0)	2019.07.17
[Greenplum DB] GPDB에 PL/R Language Extension, R 패키지 수동 설치 방법 (0)	2019.05.16

Posted by Rfriend

[Greenplum DB] GPDB에 PL/R Language Extension, R 패키지 수동 설치 방법

Greenplum and PostgreSQL Database 2019. 5. 16. 15:28

Greenplum DB에 R이나 Python, Perl, Java 등의 Procedural Language Extention을 설치해서 대용량 데이터를 In-Database 분산 병렬 처리, 분석할 수 있습니다.

이번 포스팅에서는 인터넷이 되는 환경에서 다운로드한 R 패키지들을 회사/ 기관 정책상 폐쇄망으로 운영하는 환경에서 Greenplum DB에 설치하는 방법을 소개하겠습니다.

1. Greenplum PL/R Extention (Procedural Language R) 설치 방법

2. Greenplum DB에 R 패키지 설치 방법

1. Greenplum PL/R Extention 설치 방법

PL/R 은 procedural language 로서, PL/R Extension을 설치하면 Greenplum DB에서 R 프로그래밍 언어, R 패키지의 함수와 데이터셋을 사용할 수 있습니다.

Greenplum DB에 PL/R 확장 언어 설치 방법은 https://gpdb.docs.pivotal.io/5180/ref_guide/extensions/pl_r.html 를 참고하였습니다. 웹 페이지의 상단에서 사용 중인 Greenplum DB version을 선택해주세요. (아래 예는 GPDB v5.18 선택 시 화면)

PL/R 은 패키지 형태로 되어 있으며, Pivotal Network(https://network.pivotal.io/products/pivotal-gpdb)에서 다운로드 할 수 있고, Greenplum Package Manager (gppkg) 를 사용해서 쉽게 설치할 수 있습니다.

Greenplum Package Manager (gppkg) 유틸리티는 Host와 Cluster에 PL/R 과 의존성있는 패키지들을 한꺼번에 설치를 해줍니다. 또한 gppkg는 시스템 확장이나 세그먼트 복구 시에 자동으로 PL/R extension을 설치해줍니다.

Greenplum PL/R Extention 설치 순서는 아래와 같습니다.

(0) 먼저, Greenplum DB 작동 중이고, source greenplum_path.sh 실행, $MASTER_DATA_DIRECTORY, $GPHOME variables 설정 완료 필요합니다.

psql에서 Greenplum DB 버전을 확인합니다.

psql # sql -c “select version;”

master host에서 gpadmin 계정으로 작업 디렉토리를 만듭니다.

(예: /home/gpadmin/packages)

(1) Pivotal Network에서 사용 중인 Greenplum DB version에 맞는 PL/R Extension을 다운로드 합니다.

(예: plr-2.3.3-gp5-rhel7-x86_64.gppkg)

(2) 다운로드 한 PL/R Extension Package를 scp 나 sftp 를 이용해서 Greenplum DB master host로 복사합니다. (아마 회사 정책 상 DBA만 root 권한에 접근 가능한 경우가 대부분일 것이므로, 그런 경우에는 DBA에게 복사/설치 요청을 하셔야 합니다).

$ scp plr-2.3.3-gp5-rhel7-x86_64.gppkg root@mdw:~/packages

(3) PL/R Extension Package를 gppkg 커맨드를 실행하여 설치합니다. (아래 예는 Linux에서 실행한 예)

$ gppkg -i plr-2.3.3-gp5-rhel7-x86_64.gppkg

(4) Greenplum DB를 재실행 합니다.

(GPDB를 껐다가 켜는 것이므로 DBA에게 반드시 사전 통보, 허락 받고 실행 필요합니다!)

$ gpstop -r

(5) Source the file $GPHOME/greenplum_path.sh

# source /usr/local/greenplum-db/greenplum_path.sh

R extension과 R 환경은 아래 경로에 설치되어 있습니다.

$ GPHOME/ext/R-2.3.3/

(6) 각 데이터베이스가 PL/R 언어를 사용하기 위해서는 SQL 문으로 CREATE LANGUAGE 또는 createlang 유틸리티로 PL/R을 등록해주어야 합니다. (아래는 testdb 데이터베이스에 등록하는 예)

$ createlang plr -d testdb

이렇게 하면 PL/R이 untrusted language 로 등록이 되었습니다.

참고로, Database 확인은 psql 로 \l 해주면 됩니다.

psql # \l

2. Greenplum DB에 R 패키지 설치 방법 (Installing external R packages)

(0) 필요한 R 패키지, 그리고 이에 의존성이 있는 R 패키지를 한꺼번에 다운로드 합니다. (=> https://rfriend.tistory.com/441 참조)

(1) 다운로드한 R 패키지들을 압축하여 Greenplum DB 서버로 복사합니다.

다운로드한 R 패키지들 조회해보겠습니다.

[root@mdw /]# find . | grep sp_1.3-1.tar.gz
./home/gpadmin/r-pkg/sp_1.3-1.tar.gz
[root@mdw /]# exit
logout
[gpadmin@mdw tmp]$ cd ~
[gpadmin@mdw ~]$ cd r-pkg
[gpadmin@mdw r-pkg]$ ls -la
total 47032
drwxrwxr-x 2 gpadmin gpadmin    4096 Apr 23 13:17 .
drwx------ 1 gpadmin gpadmin    4096 Apr 23 13:14 ..
-rw-rw-r-- 1 gpadmin gpadmin  931812 Apr 23 12:55 DBI_1.0.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  794622 Apr 23 12:55 LearnBayes_2.15.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  487225 Apr 23 12:55 MASS_7.3-51.3.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1860456 Apr 23 12:55 Matrix_1.2-17.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   31545 Apr 23 12:55 R6_2.4.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 3661123 Apr 23 12:55 Rcpp_1.0.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   21810 Apr 23 12:55 abind_1.4-5.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  231855 Apr 23 12:55 boot_1.3-20.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   17320 Apr 23 12:55 classInt_0.3-1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   19757 Apr 23 12:55 class_7.3-15.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   73530 Apr 23 12:55 coda_0.19-2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  658694 Apr 23 12:55 crayon_1.3.4.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   80772 Apr 23 12:55 deldir_0.1-16.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  128553 Apr 23 12:55 digest_0.6.18.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  582415 Apr 23 12:55 e1071_1.7-1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  137075 Apr 23 12:55 expm_0.999-4.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  347295 Apr 23 12:55 foreign_0.8-71.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1058430 Apr 23 12:55 gdata_2.18.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  758133 Apr 23 12:55 geosphere_1.5-7.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   33783 Apr 23 12:55 gmodels_2.18.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   12577 Apr 23 12:55 goftest_1.1-1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  187516 Apr 23 12:55 gtools_3.8.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   45408 Apr 23 12:55 htmltools_0.3.6.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1758514 Apr 23 12:55 httpuv_1.5.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1052728 Apr 23 12:55 jsonlite_1.6.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   40293 Apr 23 12:55 later_0.8.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  359031 Apr 23 12:55 lattice_0.20-38.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  200504 Apr 23 12:55 magrittr_1.5.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1581592 Apr 23 12:55 maptools_0.9-5.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  915991 Apr 23 12:55 mgcv_1.8-28.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   12960 Apr 23 12:55 mime_0.6.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   79619 Apr 23 12:55 polyclip_1.10-0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  106866 Apr 23 12:55 promises_1.0.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  255244 Apr 23 12:55 rgeos_0.4-2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  858992 Apr 23 12:55 rlang_0.3.4.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  639286 Apr 23 12:55 rpart_4.1-15.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 8166770 Apr 23 12:55 sf_0.7-3.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 2991469 Apr 23 12:55 shiny_1.3.2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   24155 Apr 23 12:55 sourcetools_0.1.7.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 3485268 Apr 23 12:55 spData_0.3.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1133621 Apr 23 12:55 sp_1.3-1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 2861828 Apr 23 12:55 spatstat.data_1.4-0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   65106 Apr 23 12:55 spatstat.utils_1.13-0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 6598638 Apr 23 12:55 spatstat_1.59-0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1227625 Apr 23 12:55 spdep_1.1-2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin    2518 Apr 23 12:55 tensor_1.5.tar.gz
-rwxr-xr-x 1 gpadmin gpadmin    2326 Apr 23 13:17 test.sh
-rw-rw-r-- 1 gpadmin gpadmin  917316 Apr 23 12:55 units_0.6-2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  564589 Apr 23 12:55 xtable_1.8-4.tar.gz

R 패키지들이 들어있는 폴더를 r-pkg.tar 이름으로 압축해보겠습니다.

[gpadmin@mdw r-pkg]$ pwd
/home/gpadmin/r-pkg
[gpadmin@mdw r-pkg]$ cd ..
[gpadmin@mdw ~]$ tar cf r-pkg.tar r-pkg
[gpadmin@mdw ~]$ ls -lrt
total 47000
drwxr-xr-x 2 gpadmin gpadmin     4096 Aug 13  2018 gpconfigs
drwxr-xr-x 2 root    root        4096 Mar 22 07:02 gppkgs
drwxrwxr-x 1 gpadmin gpadmin     4096 Apr 23 12:48 gpAdminLogs
-rw-rw-r-- 1 gpadmin gpadmin      983 Apr 23 13:14 pkg.r
drwxrwxr-x 2 gpadmin gpadmin     4096 Apr 23 13:17 r-pkg
-rw-rw-r-- 1 gpadmin gpadmin 48107520 Apr 25 01:52 r-pkg.tar

명령 프롬프트 창에서 GPDB Docker 에서 압축한 파일을 로커로 복사 후에 ==> 다른 GPDB 서버로 복사하고 압축을 풀어줍니다. (저는 Docker 환경에서 하다보니 좀 복잡해졌는데요, 만약 로컬에서 R 패키지 다운받았으면 로컬에서 바로 GPDB 서버로 복사하면 됩니다. 압축한 R패키지 파일을 scp로 복사하거나 sftp로 업로드할 수 있으며, 권한이 없는 경우 DBA에게 요청하시구요.) 아래는 mdw에서 root 계정으로 시작해서 다운로드해서 압축한 R 패키지 파일을 scp로 /root/packages 경로에 복사하는 스크립트입니다.

-- GPDB Docker에서 압축한 파일을 로컬로 복사하기
-- 다른 명령 프롬프트 창에서 복사해오고 확인하기

ihongdon-ui-MacBook-Pro:Downloads ihongdon$ docker cp gpdb-ds:/home/gpadmin/r-pkg.tar /Users/ihongdon/Downloads/r-pkg.tar
ihongdon-ui-MacBook-Pro:Downloads ihongdon$
ihongdon-ui-MacBook-Pro:Downloads ihongdon$
ihongdon-ui-MacBook-Pro:Downloads ihongdon$ ls -lrt
-rw-rw-r--   1 ihongdon  staff  48107520  4 25 10:52 r-pkg.tar

-- 다른 GPDB 서버로 복사하기
ihongdon-ui-MacBook-Pro:Downloads ihongdon$ scp r-pkg.tar root@mdw:~/package

-- 압축 해제
$ tar -xvf r-pkg.tar

Greenplum DB에 R 패키지를 설치하려면 모든 Greenplum 서버에 R이 이미 설치되어 있어야 합니다.

여러개의 Segments 에 동시에 R 패키지들을 설치해주기 위해서 배포하고자 하는 host list를 작성해줍니다.

# source /usr/local/greenplum-db/greenplum_path.sh
# vi hostfile_packages

vi editor 창이 열리면 아래처럼 R을 설치하고자 하는 host 이름을 등록해줍니다. (1개 master, 3개 segments 예시)

-- vi 편집창에서 --
smdw
sdw1
sdw2
sdw3
~
~
~
esc 누르고 :wq!

명령 프롬프트 창에서 mdw로 부터 root 계정으로 각 노드에 package directory 를 복사해줍니다.

# gpscp -f hostfile_packages -r packages =:/root

hostfile_packages를 복사해서 hostfile_all 을 만들고, mdw를 추가해줍니다.

-- copy
$ cp hostfile_packages  hostfile_all

-- insert mdw
$ vi hostfile_all

-- vi 편집창에서 --
mdw
smdw
sdw1
sdw2
sdw3
~
~
~
esc 누르고 :wq!

mdw를 포함한 모든 서버에 R packages 를 설치하는 'R CMD INSTALL r_package_name' 명령문을 mdw에서 실행합니다. (hostfile_all 에 mdw, smdw, sdw1, sdw2, sdw3 등록해놓았으므로 R이 모든 host에 설치됨)

$ pssh -f hostfile_all -v -e 'R CMD INSTALL ./DBI_1.0.0.tar.gz 
LearnBayes_2.15.1.tar.gz MASS_7.3-51.3.tar.gz Matrix_1.2-17.tar.gz 
R6_2.4.0.tar.gz Rcpp_1.0.1.tar.gz 
abind_1.4-5.tar.gz boot_1.3-20.tar.gz classInt_0.3-1.tar.gz
class_7.3-15.tar.gz coda_0.19-2.tar.gz crayon_1.3.4.tar.gz
deldir_0.1-16.tar.gz digest_0.6.18.tar.gz e1071_1.7-1.tar.gz
expm_0.999-4.tar.gz foreign_0.8-71.tar.gz gdata_2.18.0.tar.gz
geosphere_1.5-7.tar.gz gmodels_2.18.1.tar.gz goftest_1.1-1.tar.gz
gtools_3.8.1.tar.gz htmltools_0.3.6.tar.gz httpuv_1.5.1.tar.gz
jsonlite_1.6.tar.gz later_0.8.0.tar.gz lattice_0.20-38.tar.gz
magrittr_1.5.tar.gz maptools_0.9-5.tar.gz mgcv_1.8-28.tar.gz
mime_0.6.tar.gz polyclip_1.10-0.tar.gz promises_1.0.1.tar.gz
rgeos_0.4-2.tar.gz rlang_0.3.4.tar.gz rpart_4.1-15.tar.gz
sf_0.7-3.tar.gz shiny_1.3.2.tar.gz sourcetools_0.1.7.tar.gz
spData_0.3.0.tar.gz sp_1.3-1.tar.gz spatstat.data_1.4-0.tar.gz
spatstat.utils_1.13-0.tar.gz spatstat_1.59-0.tar.gz spdep_1.1-2.tar.gz
tensor_1.5.tar.gz units_0.6-2.tar.gz xtable_1.8-4.tar.gz'

특정 R 패키지를 설치하려고 할 때, 만약 의존성 있는 패키지 (dependencies packages) 가 이미 설치되어 있지 않다면 특정 R 패키지는 설치가 되지 않습니다. 따라서 위의 'R CMD INSTALL r-package-names' 명령문을 실행하면 설치가 되는게 있고, 안되는 것(<- 의존성 있는 패키지가 먼저 설치된 이후에나 설치 가능)도 있게 됩니다. 따라서 이 설치 작업을 수작업으로 반복해서 여러번 돌려줘야 합니다. loop 돌리다보면 의존성 있는 패키지가 설치가 먼저 설치가 될거고, 그 다음에 이전에는 설치가 안되었던게 의존성 있는 패키지가 바로 전에 설치가 되었으므로 이제는 설치가 되고, ...., ....., 다 설치 될때까지 몇 번 더 실행해 줍니다.

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[GPDB, PostgreSQL] Python DataFrame을 Sqlalchemy engine을 이용해 DB Table에 직접 쓰기 : df.to_sql() (0)	2019.07.28
[GPDB, Postgres] Python DataFrame을 Postgresql, Greenplum DB에 Copy 해서 넣는 방법 (0)	2019.07.17
[Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore) (0)	2019.04.27
[Greenplum DB] PostGIS에 raster2pgsql 을 사용하여 raster data import 하기 (0)	2019.04.17
[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷) (0)	2019.04.11

Posted by Rfriend

[Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore)

Greenplum and PostgreSQL Database 2019. 4. 27. 18:59

앞의 포스팅에서는 공간지리 형태의 데이터셋을 import 하는 방법들을 소개하였습니다.

이번 포스팅에서는 PostgreSQL, Greenplum DB의 PostGIS 에서 테이블(Table) 형태로 있는 공간지리 데이터에 대해서 (1) pg_dump로 공간지리 테이블을 백업하기(Backup), (2) pg_restore 로백업한 공간지리 테이블을 다시 불러오기 (Restore) 를 해보겠습니다.

(* Reference: https://github.com/PacktPublishing/Mastering-PostGIS)

(1) pg_dump로 공간지리 데이터 테이블 백업하기 (Create a Backup table)

명령 프롬프트 창에서 docker로 Greenplum DB를 실행한 후에, gpadmin 계정으로 들어가서 이미 geometry 포맷으로 만들어두었던 data_import.earthquakes_subset_with_geom 테이블을 pg_dump 를 사용하여 백업해보았습니다. (host, port, user 부분은 각자의 database 설정을 입력하면 됨)

[gpadmin@mdw tmp]$ pg_dump -h localhost -p 5432 -U gpadmin -t data_import.earthquakes_subset_with_geom -c -F c -v -b -f earthquakes_subset_with_geom.backup gpadmin

pg_dump: reading extensions

pg_dump: identifying extension members

20190417:04:24:25|pg_dump-[INFO]:-reading schemas

pg_dump: reading user-defined tables

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined functions

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined types

20190417:04:24:25|pg_dump-[INFO]:-reading type storage options

20190417:04:24:25|pg_dump-[INFO]:-reading procedural languages

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined aggregate functions

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operators

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined external protocols

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operator classes

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operator families

pg_dump: reading user-defined text search parsers

pg_dump: reading user-defined text search templates

pg_dump: reading user-defined text search dictionaries

pg_dump: reading user-defined text search configurations

20190417:04:24:26|pg_dump-[INFO]:-reading user-defined conversions

20190417:04:24:26|pg_dump-[INFO]:-reading type casts

20190417:04:24:26|pg_dump-[INFO]:-reading table inheritance information

pg_dump: finding extension tables

20190417:04:24:26|pg_dump-[INFO]:-reading rewrite rules

20190417:04:24:26|pg_dump-[INFO]:-finding inheritance relationships

20190417:04:24:26|pg_dump-[INFO]:-reading column info for interesting tables

pg_dump: finding the columns and types of table "earthquakes_subset_with_geom"

20190417:04:24:26|pg_dump-[INFO]:-flagging inherited columns in subtables

20190417:04:24:26|pg_dump-[INFO]:-reading indexes

20190417:04:24:26|pg_dump-[INFO]:-reading constraints

20190417:04:24:26|pg_dump-[INFO]:-reading triggers

pg_dump: reading dependency data

pg_dump: saving encoding = UTF8

pg_dump: saving standard_conforming_strings = on

pg_dump: dumping contents of table earthquakes_subset_with_geom

[gpadmin@mdw tmp]$

(2) pg_restore 로 백업 테이블 다시 불러오기

이미 테이블로 만들어져 있는 data_import.earthquakes_subset_with_geom 테이블을 삭제한 후에, (1)번에서 백업해둔 데이터를 불러오겠습니다.

테이블을 먼저 삭제해볼께요.

-- (2) (DBeaver db tool 에서) drop table

DROP TABLE data_import.earthquakes_subset_with_geom;

테이블을 삭제하였으니, 이제 다시 (1)번에서 백업해두었던 데이터를 다시 불러와서 테이블을 생성(Restore a Backup table)해보겠습니다.

-- (3) (명령 프롬프트 창에서) Restore using pg_restore

[gpadmin@mdw tmp]$ pg_restore -h localhost -p 5432 -U gpadmin -v -d gpadmin earthquakes_subset_with_geom.backup

pg_restore: connecting to database for restore

pg_restore: creating TABLE earthquakes_subset_with_geom

pg_restore: restoring data for table "earthquakes_subset_with_geom"

pg_restore: setting owner and privileges for TABLE earthquakes_subset_with_geom

[gpadmin@mdw tmp]$

DBeaver db tool에서 백업 테이블을 잘 불어와서 테이블이 생성이 되었는지 확인해보겠습니다.

-- (4) (DBeaver db tool 에서) 백업 되었는지 조회 확인

SELECT * FROM data_import.earthquakes_subset_with_geom LIMIT 10;

백업 테이블 불어오기(restore)가 잘 되었네요.

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[GPDB, Postgres] Python DataFrame을 Postgresql, Greenplum DB에 Copy 해서 넣는 방법 (0)	2019.07.17
[Greenplum DB] GPDB에 PL/R Language Extension, R 패키지 수동 설치 방법 (0)	2019.05.16
[Greenplum DB] PostGIS에 raster2pgsql 을 사용하여 raster data import 하기 (0)	2019.04.17
[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷) (0)	2019.04.11
[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법 (0)	2019.04.11

Posted by Rfriend

[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법

Greenplum and PostgreSQL Database 2019. 4. 11. 00:41

이번 포스팅에서는 공간지리 데이터 포맷 중에서 SHP, SHX, DBF, PRJ 로 이루어진 shape files 를 shp2pgsql 툴을 사용하여 PostgreSQL, Greenplum DB로 importing 하는 방법을 소개하겠습니다.

PostgreSQL을 설치하였다면 바로 shp2pgsql 툴을 사용할 수 있습니다.

예제 데이터셋은 https://github.com/PacktPublishing/Mastering-PostGIS/tree/master/Chapter01/data/ne_110m_coastline 에서 다운로드한 'ne_110m_coastline.shp', 'ne_110m_coastline.shx', 'ne_110m_coastline.dbf', 'ne_110m_coastline.prj' 의 4개 파일을 사용하였으며, 코드는 'Mastering PostGIS' (by Dominik Mikiewicz 외)를 참조하였습니다.

shp2pgsql 은 (1) shapefile data를 import하는 SQL문을 만들어주고, (2) shapefile data를 import하는 SQL문을 바로 psql로 보내서 import 해주는 command-line utility 입니다. 말이 좀 어려운데요, 아래 예제를 직접 보면 이해가 쉬울 듯 합니다.

Greenplum DB Docker 이미지에 PostGIS 설치해서 Greenplum 시작하는 방법은 https://rfriend.tistory.com/435 를 참고하세요.

(1) shp2pgsql로 shapefile data를 import하는 SQL문 만들기

먼저, 명령 프롬프트 창에서 'ne_110m_coastline.shp', 'ne_110m_coastline.shx', 'ne_110m_coastline.dbf', 'ne_110m_coastline.prj'의 4개 shape files 를 docker cp 명령문을 사용하여 Greenplum docker의 gpdb-ds:/tmp 폴더로 복사하겠습니다. (만약 VMware를 이용해서 Greenplum DB를 설치해서 사용 중이라면 scp 명령문으로 GPDB 안으로 파일 복사)

-- (1) 명령 프롬프트 창에서 shape files 를 docker gpdb-ds:/tmp 경로로 복사

ihongdon-ui-MacBook-Pro:~ ihongdon$

ihongdon-ui-MacBook-Pro:~ ihongdon$ cd /Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ ls

ne_110m_coastline.dbf ne_110m_coastline.prj ne_110m_coastline.shp ne_110m_coastline.shx

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp
/Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/ne_110m_coastline.shp
gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp
/Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/ne_110m_coastline.dbf
gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp
/Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/ne_110m_coastline.shx
gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp
/Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/ne_110m_coastline.prj
gpdb-ds:/tmp

다른 명령 프롬프트 창에서 GPDB에 4개의 shape files 이 잘 들어갔는지 확인해보겠습니다.

[gpadmin@mdw /]$

[gpadmin@mdw /]$ cd tmp

[gpadmin@mdw tmp]$ ls -la

total 123532

drwxrwxrwt 1 root root 4096 Apr 10 13:13 .

drwxr-xr-x 1 root root 4096 Apr 9 07:11 ..

drwxrwxrwt 2 root root 4096 Sep 11 2017 .ICE-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .Test-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .X11-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .XIM-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .font-unix

-rw-r--r-- 1 501 games 3179 Apr 8 06:02 ne_110m_coastline.dbf

-rw-r--r-- 1 501 games 147 Apr 8 06:03 ne_110m_coastline.prj

-rw-r--r-- 1 501 games 89652 Apr 8 06:03 ne_110m_coastline.shp

-rw-r--r-- 1 501 games 1172 Apr 8 06:04 ne_110m_coastline.shx

[gpadmin@mdw tmp]$

이제 shp2pgsql 유틸리티를 이용해서 data_import.ne_110m_coastline.sql 라는 이름의 ne_coastline shape files 를 import 하는 SQL 문을 만들어보겠습니다.

-- (2) 명령 프롬프트 창 docker gpdb-ds의 gpadmin@mdw 에서 shp2pgsql 실행하여 sql 생성

[gpadmin@mdw tmp]$ shp2pgsql -s 4326 ne_110m_coastline data_import.ne_coastline > data_import.ne_coastline.sql

Shapefile type: Arc

Postgis type: MULTILINESTRING[2]

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$ ls -la
total 123532
drwxrwxrwt  1 root    root        4096 Apr 10 13:13 .
drwxr-xr-x  1 root    root        4096 Apr  9 07:11 ..
drwxrwxrwt  2 root    root        4096 Sep 11  2017 .ICE-unix
drwxrwxrwt  2 root    root        4096 Sep 11  2017 .Test-unix
drwxrwxrwt  2 root    root        4096 Sep 11  2017 .X11-unix
drwxrwxrwt  2 root    root        4096 Sep 11  2017 .XIM-unix
drwxrwxrwt  2 root    root        4096 Sep 11  2017 .font-unix
-rw-rw-r--  1 gpadmin gpadmin   184004 Apr  8 08:03 data_import.ne_coastline.sql
-rw-r--r--  1     501 games       3179 Apr  8 06:02 ne_110m_coastline.dbf
-rw-r--r--  1     501 games        147 Apr  8 06:03 ne_110m_coastline.prj
-rw-r--r--  1     501 games      89652 Apr  8 06:03 ne_110m_coastline.shp
-rw-r--r--  1     501 games       1172 Apr  8 06:04 ne_110m_coastline.shx
[gpadmin@mdw tmp]$

다른 명령 프롬프트 창에서 docker GPDB에 만들어진 data_import.ne_coastline.sql 파일을 로컬로 복사해서 SQL 문이 어떻게 생겼는지 눈으로 확인을 해보겠습니다. 아래에 보는 바와 같이 CREATE TABLE "data_import"."ne_coastline" (gid serial, scalerank numeric(10,0), featurecla varchar(12)); ALTER TABLE data_import.ne_coastline ADD PRIMARY KEY (gid); SELECT AddGeometryColumn(data_import.ne_coastline, geom, 4326, MULTILINESTRING, 2); INSERT INTO data_import.ne_coastline VALUES (); 와 같이 우리가 이미 알고 있는 테이블 생성과 데이터 삽입 표준 SQL query 문이 자동으로 만들어졌음을 확인할 수 있습니다. 이 SQL 문을 실행하면 테이블 생성부터 데이터 importing까지 일괄로 수행이 됩니다.

* SQL 파일 첨부 :

data_import.ne_coastline.sql.txt

0.18MB

-- (3) (optional) 다른 명령 프롬프트 창에서 gpdb-ds:/tmp/data_import.ne_coastline.sql 를 docker gpdb에서 밖으로 복사하여 sql query 확인해 보기

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$ docker cp gpdb-ds:/tmp/data_import.ne_coastline.sql /Users/ihongdon/Documents/PostGIS/data/ne_110m_coastline/

ihongdon-ui-MacBook-Pro:ne_110m_coastline ihongdon$

-- sql query문을 열어서 확인해보면 아래와 같음

SET CLIENT_ENCODING TO UTF8;

SET STANDARD_CONFORMING_STRINGS TO ON;

BEGIN;

CREATE TABLE "data_import"."ne_coastline" (gid serial,

"scalerank" numeric(10,0),

"featurecla" varchar(12));

ALTER TABLE "data_import"."ne_coastline" ADD PRIMARY KEY (gid);

SELECT AddGeometryColumn('data_import','ne_coastline','geom','4326','MULTILINESTRING',2);

INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('1','Coastline','0105000020E61000000100000001020000000B000000BFA9980AD07664C095CA366A1FA653C04B24ADB8626364C0700E7B2E4B8E53C03EE93FF8D72764C0BF309DD0549853C02960B7EFE00764C02CDFE4B164AC53C07CB8A9DB6FEF63C08FDFE431F7C253C0A4D39170A9E663C089F492F9CEDF53C09E264A4F152464C0165BF9DF96E853C0FB978739134E64C026353A8703D253C01A655486E06064C0C3343A0771BB53C0B2C4809F216264C0974C8585ADB753C0BFA9980AD07664C095CA366A1FA653C0');

INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('0','Coastline','0105000020E61000000100000001020000000C000000D0347456A2CA18C05BC1C65E0CEF4A403FFBA3ECC62118C08AB5EFBA9B934A4010BA8804CA271BC09650268B4B214A40D09F77358C1F21C036A1DEA9ABD5494040720A9544F423C08AA1DEA904E9494088D170FB225522C0BCBB2928AC6E4A40088BF249866023C04F137F7DD0F04A40F0F906F8EDA720C07E8CF6F40E554B40D08D1B64E6491EC028FE33FFD8904B4040B24E9775EF1AC0BA12E24620964B40B0E392DBD5A516C0BAA0A43CFD464B40D0347456A2CA18C05BC1C65E0CEF4A40');

.... 너무 길어서 중간 생략함 .....

INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('0','Coastline','0105000020E6100000010000000102000000840000008881E7DEC36147C090CC237F30A85440A4E4D53906B445C084177D0569CE5440E0965643E2F243C02AD9B11188CB544044C49448A24F43C09E779CA223E354405026FC523F8B41C0C8EA56CF49E95440608E1EBFB7193BC08842041C42E15440905DA27A6BD834C02615C61682AE5440F8B7921D1BB136C06C2BF697DD955440E0B298D87C843AC0E2E995B20C9354405866666666E63FC0CCCCCCCCCC8C5440102C0E677E653FC094E34EE96081544098CADB114EDB3BC0149161156F88544050205ED72FD838C0B83B6BB75D7254408821AB5B3DE736C0F8BD4D7FF6855440E87C3F355E1236C04C4F58E2016F544020139B8F6B2B37C06CF12900C6495440F8CD3637A69F34C0FAB4C35F9361544000E292E34E892FC00C4FAF94657A544010117008558A29C0B22E6EA3016E54404029CB10C76A28C0765E6397A852544048910A630B4930C0CA9717601F2554408899999999D930C06A6666666616544058C47762D60B34C0AA315A47550B544040A2B437F8BA31C09E508880430854406066666666E632C09C99999999D95340D00182397AB433C0E288B5F814B05340F8DF4A766CAC33C05E6397A8DE685340E0E995B20C7932C05CD3BCE3143F5340F860E0B9F70834C07E130A11703C53402098A3C7EFAD35C07E6132553028534018938C9C85D533C0141956F146065340A89C4B71559933C0C03E3A75E5CF524058D72FD80DAB34C03C454772F9C95240309FE579705F33C0BA973446EB92524018A14ACD1E9835C0340C1F11538E5240C8CD70033E6F34C028A5A0DB4B745240B86ED8B628C334C084640113B85D5240480B5EF4152C36C0849ECDAACF53524040D3D9C9E09037C0427E6FD39F535240E00C1AFA275036C0BA019F1F4628524008D847A7AE4C36C052616C21C80B5240302B4D4A414738C0ACBB79AA432652403809336DFFCA38C0EA482EFF21155240A86F99D3657137C0A49C685721055240E8940ED6FF2136C09A9EB0C403DE5140E892E34EE9C035C0AC2B9FE579AA5140B8AC1743398937C0A21A2FDD249E5140C037DBDC984E38C07CD66EBBD0B65140C851F2EA1C8B39C074A25D8594DB5140B00C71AC8B3339C0B685200725B05140084CE0D6DD5C3AC08C7615527E8E5140C874763238BA37C0C408E1D1C68B514068A6ED5F595936C0FC88981249885140F8C01C3D7E0739C01E0DE02D90505140E802ECA353BF3BC0DE8442041C1E5140F8FC304278AC3EC03641D47D000851403870CE88D2C63FC0B28009DCBA075140DCB5847CD06740C01C4CC3F011EF5040D48C45D3D91941C01C7233DC80AB5040385C72DC292D42C07A832F4CA67E5040041C42959A8542C04287F9F2027C5040B42E6EA3013043C028A5A0DB4B6C50401C9430D3F6E743C0B0777FBC575D5040E8ACDD76A15544C0C4E78711C2355040DCB06D51665744C05A5F24B4E5085040A8605452279844C064D0D03FC1BD4F40887F9F71E16845C03C8AE59656574F4094C6681D553545C01CFE9AAC51F34E40A00F5D50DF6E45C0444C89247A894E40386744696FB045C02015C616820C4E40C4CCCCCCCC6446C088E63A8DB4044E406CF59CF4BE2147C0642A6F47386D4E40B8679604A82148C0E4F3C308E16D4E4004ADC090D59D48C0D0D79E5912B44E4098D2C1FA3FF348C01C4CC3F011314F408C1804560ED149C08CC43D963ED04F40446E861BF0114AC05AA31EA2D11150404C33164D67234AC0024D840D4F4B504024895E46B1D44AC0686AD95A5F865040C0120F289BA64AC0AAC64B3789B55040F81CE6CB0BFC4AC05851836918CC5040CC7F48BF7D7D4AC01AD82AC1E2165140C4E9B298D8BC49C034F44F70B12E5140C022F8DF4A8A49C0660113B87549514078CF0F23846F49C0AEADD85F767B5140A85B3D27BD014AC0CA293A92CB64514064E42CEC69474AC046B79734465B5140F0BEF1B567BA4AC0FCD478E92652514080B2295778574BC00EE544BB0A67514028D6E25300604BC00A850838849251408C3F1878EE2D4BC0BA2DCA6C90B4514018A3755435B74AC0FE1D8A027DB55140406E861BF0B149C08E7A884677A45140F0940ED6FF8D4AC078A52C431CCD51409CFEEC478A004BC0B4AC342905E35140F8FFFFFFFF7F4BC040C3A0B304DA5140045053CBD6EA4BC06E4C4F58E2E95140342861A6ED5B4BC0F051B81E852552402CB05582C5A94BC09AE7C1DD593D5240FC449E245D0F4CC000FBE8D495695240940035B56CA94CC010EA5BE674AD5240344A5E9D634C4DC0C24351A04FC65240AC76DB85E64A4DC0EC4CA1F31AE1524074B0FECF61A24EC0EEF0D7648D06534034BD529621B24FC098900F7A360B53404C07EBFF1C8450C07C62D68BA108534068300DC3472051C072693524EE035340B8AF03E78C6A51C0FAA9F1D24D1853407416F6B4C3D951C0826E2F698C4053407A1EDC9DB53151C0C22B82FFAD54534004486DE2E4B050C09CBB96900F5853401C81785DBFC251C00EF9A067B36853405CBA490C025352C0DE334B02D4825340B8E82B48334A52C0C0A94885B19B53403E2CD49AE65751C0FA1D8A027DBA53401851DA1B7C6D50C072B6B9313DD9534028CB10C7BA5450C08872A25D85F05340921D1B81780151C0EA305F5E800754408C8D40BCAEC950C0DAA7E331032154404C62105839D84FC0BCA94885B14D5440E86F4221021E4FC0B6AF03E78C545440543BFC3559534FC0CE0BB08F4E71544034B9DFA128244EC0868A71FE26825440E8F0D7648D9A4CC082828B15358C5440D0F6AFAC34114BC028E8F692C68C54401849F4328A854AC092831266DA785440C8BE2B82FF3149C03AA06CCA159C54409CE1067C7E0048C00E9DD7D825845440906A9F8EC74C47C0E6A90EB9197F544070E7FBA9F14246C0E81DA7E8486A5440ECC039234A7347C05A04FF5BC98C54408881E7DEC36147C090CC237F30A85440');

INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('3','Country','0105000020E6100000010000000102000000060000006666666666A65AC06766666666665240713D0AD7A3505AC0295C8FC2F56852400000000000205AC07B14AE47E15A5240B91E85EB51585AC0713D0AD7A33052405C8FC2F528BC5AC03E0AD7A3705D52406666666666A65AC06766666666665240');

COMMIT;

(2) shp2pgsql로 shapefile data를 import하는 SQL문을 psql로 보내서 import하기

Greenplum docker 명령 프롬프트 창에서 shp2pgsql 유틸리티를 사용해서 (1)번에서 만든 SQL문을 psql로 보내서 실행시켜 보겠습니다. (아래에서 각자 자신의 Greenplum DB 혹은 PostgreSQL DB의 host(-h), port(-p), user(-U), database(-d) 이름을 설정해 줌)

[gpadmin@mdw tmp]$ shp2pgsql -s 4326 ne_110m_coastline data_import.ne_coastline
| psql -h localhost -p 5432 -U gpadmin -d gpadmin
Shapefile type: Arc
Postgis type: MULTILINESTRING[2]
SET
SET
BEGIN
NOTICE:  CREATE TABLE will create implicit sequence "ne_coastline_gid_seq" for serial column "ne_coastline.gid"
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'gid' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "ne_coastline_pkey" for table "ne_coastline"
ALTER TABLE
                          addgeometrycolumn
----------------------------------------------------------------------
data_import.ne_coastline.geom SRID:4326 TYPE:MULTILINESTRING DIMS:2
(1 row)

INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

.... 중간 생략함 ....

INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
COMMIT
[gpadmin@mdw tmp]$

마지막으로, DBeaver db tool로 가서 SELECT 문으로 데이터가 잘 들어갔는지 조회를 해보겠습니다. 해안선(coastline)을 MULTILINESTRING 공간지리 데이터 포맷으로 저장해놓은 데이터셋이군요.

SELECT * FROM data_import.ne_coastline ORDER BY gid LIMIT 10;

이중에서 첫번째 gid의 geom만 가져다 살펴보면 아래에서 보는 것처럼 22개의 값으로 이루어져 있습니다.

MULTILINESTRING ((-163.7128956777287 -78.59566741324154, -163.1058009511638 -78.22333871857859, -161.24511349184644 -78.38017669058443, -160.24620805564453 -78.69364592886694, -159.48240454815448 -79.04633757925897, -159.20818356019765 -79.4970077452764, -161.12760128481472 -79.63420867301133, -162.43984676821842 -79.28146534618699, -163.027407803377 -78.92877369579496, -163.06660437727038 -78.8699659158468, -163.7128956777287 -78.59566741324154))

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PostGIS에 raster2pgsql 을 사용하여 raster data import 하기 (0)	2019.04.17
[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷) (0)	2019.04.11
[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기 (0)	2019.04.10
[Greenplum DB] GPDB docker에 PostGIS 설치하기 (0)	2019.03.27
[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot) (0)	2019.03.21

Posted by Rfriend

[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기

Greenplum and PostgreSQL Database 2019. 4. 10. 21:50

이번 포스팅에서는 (1) Greenplum database에 위도와 경도를 포함하고 있는 csv파일을 psql, DBeaver tool을 사용하여 import 하고, (2) PostGIS의 sql query문을 사용하여 공간정보를 뽑아내는 방법을 소개하겠습니다.

참고로, PostgreSQL, Greenplum DB에 지리공간 데이터를 importing할 수 있는 PostGIS, importing tool들은 아래와 같이 매우 다양합니다.

(1) psql을 사용하여 위경도를 포함한 csv 파일을 Greenplum DB에 import하기

예제로 사용한 데이터셋은 https://github.com/PacktPublishing/Mastering-PostGIS/tree/master/Chapter01/data/usgs-earthquakes 에서 '2.5_day.csv' 파일을 다운로드 하였으며, 사용한 예제 코드는 'Mastering PostGIS' 책을 참조하였습니다.

Greenplum docker image를 사용해서 Greenplum을 시작합니다. (자세한 설명은 아래 링크 참조)

==> https://rfriend.tistory.com/435

먼저 DBeaver sql query 편집창에서 아래와 같이 data_import schema와 earthquakes_csv 테이블을 만들어주겠습니다.

-- create schema
CREATE SCHEMA data_import;

----------
-- (1) Importing CSV data format
----------

-- create table
DROP TABLE IF EXISTS data_import.earthquakes_csv;
CREATE TABLE data_import.earthquakes_csv (
"time" timestamp with time zone,
latitude numeric,
longitude numeric,
depth numeric,
mag numeric,
magType varchar,
nst numeric,
gap numeric,
dmin numeric,
rms numeric,
net varchar,
id varchar,
updated timestamp with time zone,
place varchar,
type varchar,
horizontalError numeric,
depthError numeric,
magError numeric,
magNst numeric,
status varchar,
locationSource varchar,
magSource varchar
);

다음으로 명령 프롬프트 cmd 창에서 PostGIS가 설치된 Greenplum Docker 에 docker cp를 사용하여 '2.5_day.csv'파일을 복사해 넣습니다.

MacBook-Pro:~ ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/usgs-earthquakes/2.5_day.csv gpdb-ds:/tmp

Greenplum의 gpadmin 계정으로 들어간 명령 프롬프트 창에서 '2.5_day.csv' 파일이 잘 복사가 되었는지 확인해보겠습니다. 그리고 root 계정으로 들어가서 gpadmin 으로 소유권한을 수정(chown)하겠습니다.

[gpadmin@mdw tmp]$ ls -la

total 123532

drwxrwxrwt 1 root root 4096 Apr 10 13:13 .

drwxr-xr-x 1 root root 4096 Apr 9 07:11 ..

drwxrwxrwt 2 root root 4096 Sep 11 2017 .ICE-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .Test-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .X11-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .XIM-unix

drwxrwxrwt 2 root root 4096 Sep 11 2017 .font-unix

srwxrwxr-x 1 gpadmin gpadmin 0 Mar 22 07:19 .s.GPMC.sock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 10 12:30 .s.PGSQL.40000

-rw------- 1 gpadmin gpadmin 27 Apr 10 12:30 .s.PGSQL.40000.lock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 10 12:30 .s.PGSQL.40001

-rw------- 1 gpadmin gpadmin 27 Apr 10 12:30 .s.PGSQL.40001.lock

srwxrwxrwx 1 gpadmin gpadmin 0 Apr 10 12:30 .s.PGSQL.5432

-rw------- 1 gpadmin gpadmin 27 Apr 10 12:30 .s.PGSQL.5432.lock

-rw-r--r-- 1 501 games 4787 Apr 8 06:21 2.5_day.csv

[gpadmin@mdw tmp]$

[root@mdw tmp]# exitlogout

다음으로 psql 을 실행해서 copy 문으로 '2.5_day.csv' 파일을 data_import.earthquakes_csv 테이블에 복사해서 importing 하겠습니다.

[gpadmin@mdw tmp]$ pwd

/tmp

[gpadmin@mdw tmp]$ psql

psql (8.3.23)

Type "help" for help.

gpadmin=# copy data_import.earthquakes_csv from '/tmp/2.5_day.csv' with DELIMITER ',' CSV HEADER;

COPY 25

gpadmin=# \q

[gpadmin@mdw tmp]$

다시 DBeaver query tool 로 돌아와서, data_import.earthquakes_csv 테이블에 데이터가 잘 들어갔는지 조회를 해보겠습니다. 잘 들어갔네요. ^^

SELECT * FROM data_import.earthquakes_csv LIMIT 10;

(2) PostGIS 함수로 공간 정보 뽑아내기

이제 원천 데이터 준비가 되었으니 PostGIS의 ST_Point(경도, 위도) 로 공간데이터 점(geometry Point)을 만들고, ST_SetSRID() 로 공간 참조 ID를 만들어보겠습니다.

-- (2) Extracting spatial information from flat data

DROP TABLE IF EXISTS data_import.earthquakes_subset_with_geom;

CREATE TABLE data_import.earthquakes_subset_with_geom AS (

SELECT

id,

"time",

depth,

mag,

magtype,

place,

ST_SetSRID(ST_Point(longitude, latitude), 4326) AS geom

FROM data_import.earthquakes_csv

);

SELECT * FROM data_import.earthquakes_subset_with_geom LIMIT 10;

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷) (0)	2019.04.11
[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법 (0)	2019.04.11
[Greenplum DB] GPDB docker에 PostGIS 설치하기 (0)	2019.03.27
[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot) (0)	2019.03.21
[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜 (0)	2019.03.16

Posted by Rfriend

[Greenplum DB] GPDB docker에 PostGIS 설치하기

Greenplum and PostgreSQL Database 2019. 3. 27. 07:45

이번 포스팅에서는 PostgreSQL, Greenplum database에서 지리공간 데이터 분석 (Geo-Spatial data analysis) 을 할 수 있도록 해주는 외장 확장 오픈 소스 소프트웨어 프로그램인 PostGIS 를 Greenplum docker 위에 설치하는 방법을 소개하겠습니다.

Greenplum DB에 PostGIS 를 설치하는 가이드는 https://gpdb.docs.pivotal.io/5100/ref_guide/extensions/postGIS.html 를 참고하였습니다.

0. (사전 준비 사항) Docker를 이용하여 Greenplum DB + MADlib + PL/x 설치

CentOS + Greenplum + MADlib + PL/R + PL/Python 이 설치된 Docker Image를 이용하여 분석환경을 구성하는 자세한 내용은 https://rfriend.tistory.com/379 포스팅을 참고하기 바랍니다.

명령 프롬프트 창을 띄우고 아래 docker 명령어로 greenplum을 간편하게 설치해보세요.

---------------------------------

-- GPDB w/MADlib, PL/x on Docker : https://hub.docker.com/r/hdlee2u/gpdb-analytics

---------------------------------

-- (1) Docker Image Pull

$ docker pull hdlee2u/gpdb-analytics

$ docker images

-- (2) Docker Image Run(port 5432) -> Docker Container Creation

$ docker run -i -d -p 5432:5432 -p 28080:28080 --name gpdb-ds --hostname mdw hdlee2u/gpdb-analytics /usr/sbin/sshd -D

$ docker ps -a

-- (3) To Start Greenplum Database and Use psql

$ docker exec -it gpdb-ds /bin/bash

[root@mdw /]# su - gpadmin

[gpadmin@mdw ~]$ gpstart -a

.... GPDB start

....

CnetOS와 GPDB 버전에 맞는 PostGIS 버전을 다운로드해서 설치를 해야 합니다. IP 확인, CentOS version 확인, MADlib, PL/R 버전 확인, R & Python Data Science Package version 확인하는 방법은 아래를 참고하세요.

- CentOS : release 7.4

- Greenplum Database : ver 5.10.2

- MADlib : ver 1.15

- PL/R : 2.3.2

- DataScienceR : 1.0.1

- DataSciencePython : 1.1.1

-------------------------------------

-- IP check

[gpadmin@mdw ~]$

[root@mdw ~]# cd /home/gpadmin

[root@mdw gpadmin]# ifconfig -a

eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500

inet 172.17.0.2 netmask 255.255.0.0 broadcast 172.17.255.255

ether 02:42:ac:11:00:02 txqueuelen 0 (Ethernet)

RX packets 25395 bytes 10372326 (9.8 MiB)

RX errors 0 dropped 0 overruns 0 frame 0

TX packets 25074 bytes 79368842 (75.6 MiB)

TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

... (이하 생략)

--------------------------------------

-- MADlib, PL/R, Python Data Science Package, GP Command Center version check

--------------------------------------

[root@mdw gpadmin]# cd /setup

[root@mdw setup]# ls -al

total 810088

drwxr-xr-x 4 gpadmin gpadmin 4096 Aug 13 2018 .

drwxr-xr-x 1 root root 4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13 2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13 2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin 4096 Jul 23 2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin 29040039 Aug 13 2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10 2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13 2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin 4 Aug 13 2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin 4096 Aug 11 2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin 3023537 Aug 13 2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin 39279994 Aug 13 2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg

--------------------------------------

-- CentOS version check

[gpadmin@mdw setup]$ cat /etc/os-release

NAME="CentOS Linux"

VERSION="7 (Core)"

ID="centos"

ID_LIKE="rhel fedora"

VERSION_ID="7"

PRETTY_NAME="CentOS Linux 7 (Core)"

ANSI_COLOR="0;31"

CPE_NAME="cpe:/o:centos:centos:7"

HOME_URL="https://www.centos.org/"

BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-7"

CENTOS_MANTISBT_PROJECT_VERSION="7"

REDHAT_SUPPORT_PRODUCT="centos"

REDHAT_SUPPORT_PRODUCT_VERSION="7"

1. Pivotal Network에서 PostGIS 다운로드

(1) https://network.pivotal.io/ 접속 (다운로드를 위해서는 회원가입 필요)

> (2) 'Pivotal Greenplum Releases: 5.10.2' : https://network.pivotal.io/products/pivotal-gpdb#/releases/158026

> (3) 'Greenplum Adnvanced Analytics' : https://network.pivotal.io/products/pivotal-gpdb#/releases/158026/file_groups/1084

> (4) 'PostGIS 2.1.5+pivotal.1 for RHEL 7' file download

의 순서대로 경로를 찾아가서 PostGIS 2.1.5+pivotal.1 for RHEL 7 파일을 다운로드 합니다.

2. 다운로드한 PostGIS 압축파일을 Greenplum Docker 컨테이너 안으로 복사(copy)하기

다른 명령 프롬프트 창을 띄우고, 아래처럼 Downloads 폴더로 경로 변경 후에 docker cp 명령문으로 1번에서 다운로드한 PostGIS 2.1.5 압축 파일을 Greenplum 도커 컨테이너 안의 'gpdb-ds:/setup' 경로로 복사해주세요.

-- [At another terminal window] Copy PostGIS 2.1.5 to GPDB-DS Docker Container

ihongdon-ui-MacBook-Pro:~ ihongdon$ pwd

/Users/ihongdon

ihongdon-ui-MacBook-Pro:~ ihongdon$ cd Downloads/

ihongdon-ui-MacBook-Pro:Downloads ihongdon$ ls -al

-rw-r--r--@ 1 ihongdon staff 19839907 3 22 16:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

ihongdon-ui-MacBook-Pro:Downloads ihongdon$ docker cp postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg gpdb-ds:/setup

ihongdon-ui-MacBook-Pro:Downloads ihongdon$

3. gpadmin 계정에게 postgis-2.1.5 파일에 대한 권한 부여 (chown)

(1) gpadmin 으로 들어와 있는 명령 프롬프트 창으로 와서 root 계정으로 로그인 후에 => (2) chown 명령어를 이용하여 gpadmin 에 PostGIS 파일에 대한 권한을 부여해줍니다.

-- 파일 소유자나 소유 그룹 변경 : chown

[gpadmin@mdw setup]$ su -

Password:

Last login: Fri Mar 22 07:01:35 UTC 2019 on pts/0

[root@mdw ~]# cd /setup

[root@mdw setup]# ls -al

total 829464

drwxr-xr-x 1 gpadmin gpadmin 4096 Mar 22 07:33 .

drwxr-xr-x 1 root root 4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13 2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13 2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin 4096 Jul 23 2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin 29040039 Aug 13 2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10 2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13 2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin 4 Aug 13 2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin 4096 Aug 11 2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin 3023537 Aug 13 2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin 39279994 Aug 13 2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 501 games 19839907 Mar 22 07:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

[root@mdw setup]# chown gpadmin:gpadmin postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

4. gppkg로 각 Segment 노드에 PostGIS 설치하기

(1) 명령 프롬프트 창에서 root 계정에서 exit 후 => gpadmin 계정에서 gppkg -i 로 PostGIS 2.1.5를 설치합니다.

(2) 그러면 로그 메시지에 'gppkg:mdw:gpadmin-[INFO]:-Please run the following commands to enable the PostGIS package: $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh mydatabase install'라는 메시지가 나옵니다. 이 메시지를 추가로 실행시킵니다.

-- PostGIS 2.1.5 install

[root@mdw setup]# exit

logout

[gpadmin@mdw setup]$ ls -al

total 829464

drwxr-xr-x 1 gpadmin gpadmin 4096 Mar 22 07:33 .

drwxr-xr-x 1 root root 4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13 2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13 2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin 4096 Jul 23 2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin 29040039 Aug 13 2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10 2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13 2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin 4 Aug 13 2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin 4096 Aug 11 2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin 3023537 Aug 13 2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin 39279994 Aug 13 2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 19839907 Mar 22 07:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

[gpadmin@mdw setup]$ gppkg -i postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

20190322:07:36:54:011243 gppkg:mdw:gpadmin-[INFO]:-Starting gppkg with args: -i postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Installing package postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix /usr/local/greenplum-db-5.10.2'

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Installing postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg locally

20190322:07:36:56:011243 gppkg:mdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix /usr/local/greenplum-db-5.10.2'

20190322:07:36:56:011243 gppkg:mdw:gpadmin-[INFO]:-Installing rpms cmdStr='rpm -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix=/usr/local/greenplum-db-5.10.2'

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-Completed local installation of postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg.

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-Please run the following commands to enable the PostGIS package: $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh mydatabase install

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg successfully installed.

[gpadmin@mdw setup]$ cd $GPHOME

[gpadmin@mdw greenplum-db]$ cd share

[gpadmin@mdw share]$ ls

gdal greenplum packages postgresql proj

[gpadmin@mdw share]$ cd postgresql/

[gpadmin@mdw postgresql]$ cd contrib/

[gpadmin@mdw contrib]$ ls

citext.sql gp_distribution_policy.sql gp_svec_test.sql oid2name.txt postgis-2.1 uninstall_fuzzystrmatch.sql uninstall_hstore.sql

dblink.sql gp_session_state.sql hstore.sql orafunc.sql uninstall_citext.sql uninstall_gp_distribution_policy.sql uninstall_orafunc.sql

fuzzystrmatch.sql gp_sfv_test.sql indexscan.sql pgcrypto.sql uninstall_dblink.sql uninstall_gp_session_state.sql uninstall_pgcrypto.sql

[gpadmin@mdw contrib]$ cd postgis-2.1/

[gpadmin@mdw postgis-2.1]$ ls

install postgis_manager.sh uninstall upgrade

[gpadmin@mdw postgis-2.1]$ $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh gpadmin install

SET

BEGIN

CREATE FUNCTION

CREATE TYPE

CREATE FUNCTION

INSERT 0 1

COMMIT

ANALYZE

[gpadmin@mdw postgis-2.1]$

자, 이제 PostGIS가 Greenplum docker 컨테이너 안에 설치가 되었습니다.

5. PostGIS 샘플 Query 실행해서 테스트해보기

DBeaver DB tool로 아래의 PostGIS 테이블 생성해보고 select query 를 날려보겠습니다.

-- Create PostGIS extension

CREATE EXTENSION postgis;

-- PostGIS version check

SELECT PostGIS_Version();

-- PostGIS sample query

CREATE TABLE geom_test ( gid int4, geom geometry,

name varchar(25) );

INSERT INTO geom_test ( gid, geom, name )

VALUES ( 1, 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square');

INSERT INTO geom_test ( gid, geom, name )

VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' );

INSERT INTO geom_test ( gid, geom, name )

VALUES ( 3, 'MULTIPOINT(3 4,8 9)', '2D Aggregate Point' );

SELECT * from geom_test WHERE geom &&

Box3D(ST_GeomFromEWKT('LINESTRING(2 2 0, 3 3 0)'));

잘 작동하는군요. ^^

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법 (0)	2019.04.11
[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기 (0)	2019.04.10
[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot) (0)	2019.03.21
[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜 (0)	2019.03.16
[Greenplum DB] 외부 데이터를 Table에 업로드하는 5가지 방법 : CREATE EXTERNAL TABLE, COPY, INSERT INTO VALUES(), pd.DataFrame.to_sql(), DBeaver import (0)	2019.03.10

Posted by Rfriend

[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot)

Greenplum and PostgreSQL Database 2019. 3. 21. 23:47

데이터 크기가 작다면 Python이나 R로 데이터를 로딩해서 직접 동일 간격 구간별 집계를 한다든지, 변수별 히스토그램이나 박스 그래프, 막대그래프를 그릴 수 있을 것입니다. 하지만 데이터 크기가 로컬 컴퓨터의 메모리 한계를 벗어날 정도로 크다면 그때는 DB에서 SQL로 집계를 한 후, 집계 결과만을 Python이나 R로 가져와서 시각화를 하는 방법을 생각해볼 수 있습니다.

이번 포스팅에서는

(1) Greenplum DB, PostgreSQL DB의 width_bucket() SQL 함수를 사용하여 동일 간격 범위별로 관측치 개수를 세어보고,

(2) Python에서 width_bucket() SQL Query를 재사용하기 쉽게 사용자 정의 함수(user defined function)을 정의하여,

(3) Python으로 DB connect하여 GPDB에서 집계한 결과로 막대 그래프 그리는 방법

을 소개하겠습니다.

먼저, 예제로 사용할 간단한 houses (세금, 화장실 개수, 욕실 개수, 가격, 크기) 데이터를 DBeaver나 PGadmin IV 등의 DB tool을 사용해서 테이블을 생성하고 insert into 해보겠습니다.

DROP TABLE IF EXISTS houses;

CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,

size INT, lot INT);

INSERT INTO houses VALUES

(1 , 590 , 2 , 1 , 50000 , 770 , 22100),

(2 , 1050 , 3 , 2 , 85000 , 1410 , 12000),

(3 , 20 , 3 , 1 , 22500 , 1060 , 3500),

(4 , 870 , 2 , 2 , 90000 , 1300 , 17500),

(5 , 1320 , 3 , 2 , 133000 , 1500 , 30000),

(6 , 1350 , 2 , 1 , 90500 , 820 , 25700),

(7 , 2790 , 3 , 2.5 , 260000 , 2130 , 25000),

(8 , 680 , 2 , 1 , 142500 , 1170 , 22000),

(9 , 1840 , 3 , 2 , 160000 , 1500 , 19000),

(10 , 3680 , 4 , 2 , 240000 , 2790 , 20000),

(11 , 1660 , 3 , 1 , 87000 , 1030 , 17500),

(12 , 1620 , 3 , 2 , 118600 , 1250 , 20000),

(13 , 3100 , 3 , 2 , 140000 , 1760 , 38000),

(14 , 2070 , 2 , 3 , 148000 , 1550 , 14000),

(15 , 650 , 3 , 1.5 , 65000 , 1450 , 12000);

SELECT * FROM houses;

1. Greenplum DB, PostgreSQL DB의 width_bucket() SQL 함수를 사용하여
동일 간격 범위별로 관측치 개수를 세기

houses 테이블에서 가격(price) 변수의 최소값은 $22,500, 평균은 $122,140, 최대값은 $260,000 이군요.

SELECT

min(price) AS min_val,

avg(price) AS mean,

max(price) AS max_val

FROM houses

위에서는 SQL문의 min(), avg(), max() 함수를 써서 이들 통계량을 구했는데요, 경우에 따라서는 동일 간격의 범위/ 구간별로 관측치가 몇 개 있는지를 구해보고 싶은 경우가 있습니다. (<- 히스토그램으로 시각화를 하죠) SQL 문으로 하려면 width_bucket() 함수를 사용하면 case when 문을 길게 쓰지 않고도 간편하게 '동일 간격 범위의 bucket 별로 관측치를 집계'할 수 있습니다.

houses 테이블의 price 칼럼에 대해 5개의 동일 간격(즉, 4개의 cutting line을 사용)의 bucket별로 관측치를 세어(count) 보겠습니다. width_bucket(변수, 시작 값, 끝 값, cutting line 개수) 의 순서로 arguments를 넣어주면 되며, 이렇게 만든 bucket 별로 count(*) 하고, GROUP BY bucket 으로 그룹별 묶어주면 됩니다.

SELECT

width_bucket(price, 22500, 260000, 4) AS bucket,

count(*) AS cnt

FROM houses

GROUP BY bucket

ORDER BY bucket;

그런데, 위의 집계 결과를 보면 각 bucket의 구간이 어떻게 되는지 표만 봐서는 알 수 없어서 좀 갑갑한 면이 있습니다. 그래서 이번에는 각 bucket의 구간의 시작(from_val)과 끝(to_val)의 값을 같이 볼 수 있도록 해보겠습니다. 그리고 width_bucket(변수, 시작 값, 끝 값, cutting line 개수) 에서 '시작 값(starting point)'과 '끝 값(ending point)'을 수작업으로 직접 입력해주는 것이 아니라 해당 변수의 최소값과 최대값을 min(), max() 함수로 구해서 자동으로 입력해줄 수 있도록 with clause SQL문을 사용해서 짜보겠습니다.

WITH stats AS (

SELECT

min(price) AS min_val,

max(price) AS max_val

FROM houses

), bucket_tbl AS (

SELECT

width_bucket(price, min_val, max_val, 4) AS bucket,

count(*) AS cnt

FROM houses, stats

GROUP BY bucket

ORDER BY bucket

)

SELECT

bucket,

min_val + (bucket-1)*min_val AS from_val,

min_val + bucket*min_val AS to_val,

cnt

FROM stats, bucket_tbl;

처음보다 훨씬 보기에 좋아졌습니다. 그렇지요?!

여기까지 짜보았으니 이제 슬슬 더 욕심이 나기 시작합니다. DB에서 SQL로 bucket 별 관측치 개수를 집계할 결과를 Python이나 R로 가져와서 시각화를 해보면 더 가독성이 좋아질텐데....., 위의 SQL문을 사용자 정의 함수로 만들어서 table이름, 변수이름, bucket 개수 입력값만 바꾸어주면 알아서 집계를 해주면 더 편할텐데.... 하는 욕심 말이지요.

그래서, Python으로 Greenplum 이나 PoesgreSQL DB에 connect하여 위의 width_bucket() SQL문을 사용자 정의 함수로 만들고, DB에서 집계한 결과를 pandas의 DataFrame.plot.bar() 함수로 시각화해보겠습니다.

2. Python에서 width_bucket() SQL Query를 재사용하기 쉽게
사용자 정의 함수(user defined function)을 정의

여기서부터는 Jupyter Notebook이나 Spyder 와 같은 Python IDE에서 진행하면 됩니다. (저는 Jupyter Notebook을 사용하였습니다)

아래 Python 코드는 psycopg2 라이브러리를 사용해서 Greenplum DB 혹은 PostgreSQL DB에 접속하고, Query문을 받아서 실행시킨 후에, 그 결과를 pandas DataFrame으로 반환하는 사용자 정의 함수 read_sql() 함수를 정의한 것입니다.

import pandas as pd

import matplotlib as plt

%matplotlib inline

# UDF for GPDB connection and Querying, Save to pandas DataFrame

def read_sql(query):

import pandas as pd

import psycopg2 as pg

# DB Connection (put it with yours)

conn = pg.connect(host='localhost',

port='5432',

dbname='postgres',

user='postgres',

password='postgres')

# Get a DataFrame

result = pd.read_sql(query, conn)

conn.close()

return result

위에서 정의한 read_sql() 사용자 정의함수를 사용해서 5개 bucket 별로 관측치 개수를 집계한 SQL query문을 날려보겠습니다. SQL query문은 따옴표 3개 (""" sql query syntax """) 안에 써주면 됩니다. read_sql() 사용자 정의 함수가 잘 작동하는군요.

query = """

WITH stats AS (

SELECT

min(price) AS min_val,

max(price) AS max_val

FROM houses

), bucket_tbl AS (

SELECT

width_bucket(price, min_val, max_val, 4) AS bucket,

count(*) AS cnt

FROM houses, stats

GROUP BY bucket

ORDER BY bucket

)

SELECT

'price' AS column,

bucket,

min_val + (bucket-1)*min_val AS range_low,

min_val + bucket*min_val AS range_high,

cnt

FROM stats, bucket_tbl;

"""

rng_bucket = read_sql(query)

rng_bucket

	column	bucket	range_low	range_high	cnt
0	price	1	22500	45000	3
1	price	2	45000	67500	7
2	price	3	67500	90000	3
3	price	4	90000	112500	1
4	price	5	112500	135000	1

이제 위의 구슬들을 잘 꿰어서 진주 목걸이를 만들 차례가 되었습니다. Python의 format() 문을 접목하여 동일 간격 bucket 별로 관측치 개수를 집계하는 SQL query 문의 테이블 이름(tbl_nm), 변수 이름(var_nm), bucket 개수(bucket_num) 의 3개 인자를 받는 Python 사용자 정의 함수 rng_bucket(tbl_nm, var_nm, bucket_num)를 만들어보겠습니다.

def rng_bucket(tbl_nm, var_nm, bucket_num=10):

query = """

WITH stats AS (

SELECT

min({var_nm}) AS min_val,

max({var_nm}) AS max_val

FROM {tbl_nm}

), bucket_tbl AS (

SELECT

width_bucket({var_nm}, min_val, max_val, ({bucket_num}-1)) AS bucket,

count(*) AS cnt

FROM {tbl_nm}, stats

GROUP BY bucket

ORDER BY bucket

)

SELECT

'{var_nm}' AS column,

bucket,

min_val + (bucket-1)*min_val AS range_low,

min_val + bucket*min_val AS range_high,

cnt

FROM stats, bucket_tbl

ORDER BY bucket

""".format(tbl_nm = tbl_nm,

var_nm = var_nm,

bucket_num = bucket_num)

rng_bucket = read_sql(query)

return rng_bucket;

목걸이로 다 꿰었으니 이제 rng_bucket() 사용자 정의 함수에 'price' 변수에 대해 bucket 개수를 5개, 10개로 넣어서 실행시켜 보고, 변수 이름을 'price' 대신 'tax'를 넣어서도 실행시켜 보겠습니다.

rng_bucket('houses', 'price', 5)

	column	bucket	range_low	range_high	cnt
0	price	1	22500	45000	3
1	price	2	45000	67500	7
2	price	3	67500	90000	3
3	price	4	90000	112500	1
4	price	5	112500	135000	1

rng_bucket('houses', 'price', 10)

	column	bucket	range_low	range_high	cnt
0	price	1	22500	45000	1
1	price	2	45000	67500	2
2	price	3	67500	90000	4
3	price	4	90000	112500	1
4	price	5	112500	135000	4
5	price	6	135000	157500	1
6	price	9	202500	225000	1
7	price	10	225000	247500	1

rng_bucket('houses', 'tax', 5)

	column	bucket	range_low	range_high	cnt
0	tax	1	20	40	5
1	tax	2	40	60	6
2	tax	3	60	80	1
3	tax	4	80	100	2
4	tax	5	100	120	1

잘 동작하는군요! ^^

3. Python으로 DB connect하여 GPDB에서 집계한 결과로 막대 그래프 그리기

마지막으로, pandas DataFrame으로 반환받은 세금(tax) 변수의 5개 bucket 별 집계 결과를 pandas 막대그래프(bar graph)로 그려보겠습니다.

tax_bucket = rng_bucket('houses', 'price', 5)

ax = tax_bucket.plot.bar(x='bucket', y='cnt', rot=0)

요약하자면, 데이터 사이즈가 수백 테라바이트, 수 페타바이트급이면 Greenplum, PostgreSQL DB에서 집계하시구요, 결과는 Python이나 R로 시각화해서 보세요. 자주 쓰는 코드라면 사용자 정의 함수를 만들어놓고 재사용하시구요.

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기 (0)	2019.04.10
[Greenplum DB] GPDB docker에 PostGIS 설치하기 (0)	2019.03.27
[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜 (0)	2019.03.16
[Greenplum DB] 외부 데이터를 Table에 업로드하는 5가지 방법 : CREATE EXTERNAL TABLE, COPY, INSERT INTO VALUES(), pd.DataFrame.to_sql(), DBeaver import (0)	2019.03.10
Greenplum DB, Postgresql DB에 사용할 수 있는 오픈소스 database tool DBeaver 설치 및 사용 방법 (0)	2019.03.04

Posted by Rfriend

[Greenplum DB] 외부 데이터를 Table에 업로드하는 5가지 방법 : CREATE EXTERNAL TABLE, COPY, INSERT INTO VALUES(), pd.DataFrame.to_sql(), DBeaver import

Greenplum and PostgreSQL Database 2019. 3. 10. 23:58

Greemplum DB로 데이터 분석을 하려고 할때 처음에 하는 일이 분석에 필요한 데이터를 Greenplum DB에 schema, table 생성하고 데이터를 table에 upload하는 입니다. 이번 포스팅에서는 외부의 데이터를 Greenplum Database Table에 업로드하는 5가지 방법을 소개하겠습니다.

[ Greenplum DB에 외부 데이터를 upload하는 5가지 방법 ]

1. CREATE EXTERNAL TABLE 후 => CREATE TABLE

2. COPY table_name FROM directory DELIMITER

3. INSERT INTO VALUES ()

4. DB tool인 DBeaver Import Wizard (or pgAdmin IV)

5. Python pandas DataFrame.to_sql() 함수 사용

예제로 사용할 데이터는 UC Irvine Machine Learning Repository 에 있는 abalone 데이터셋입니다.
( * source: http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data )

전복(abalone)의 성별, 길이, 반지름, 높이, 전체 무게, 내장을 뺀 후의 무게, 내장 무게, 껍질 무게, 고리 (+1.5를 하면 나이가 됨) 변수로 구성된 데이터셋입니다. Toy project로 나이 예측이나 성장정도 분류 문제에 종종 등장하곤 하는 데이터셋입니다.

Attribute information:

   Given is the attribute name, attribute type, the measurement unit and a
   brief description.  The number of rings is the value to predict: either
   as a continuous value or as a classification problem.

	Name		Data Type	Meas.	Description
	----		---------	-----	-----------
	Sex		nominal			M, F, and I (infant)
	Length		continuous	mm	Longest shell measurement
	Diameter	continuous	mm	perpendicular to length
	Height		continuous	mm	with meat in shell
	Whole weight	continuous	grams	whole abalone
	Shucked weight	continuous	grams	weight of meat
	Viscera weight	continuous	grams	gut weight (after bleeding)
	Shell weight	continuous	grams	after being dried
	Rings		integer			+1.5 gives the age in years

   Statistics for numeric domains:

		Length	Diam	Height	Whole	Shucked	Viscera	Shell	Rings
	Min	0.075	0.055	0.000	0.002	0.001	0.001	0.002	    1
	Max	0.815	0.650	1.130	2.826	1.488	0.760	1.005	   29
	Mean	0.524	0.408	0.140	0.829	0.359	0.181	0.239	9.934
	SD	0.120	0.099	0.042	0.490	0.222	0.110	0.139	3.224

Correl 0.557 0.575 0.557 0.540 0.421 0.504 0.628 1.0

1. CREATE EXTERNAL TABLE => CREATE TABLE

먼저 web 상에 올라가 있는 데이터를 url을 이용하여 external table로 만들고, 그 다음에 create table을 해서 Greenplum DB에 넣는 방법부터 소개하겠습니다. web에 있는 데이터 불러올 때나 하둡에 있는 데이터를 GPDB에 올릴 때는 이 방법에 제일 편리한거 같습니다.

-- (1) Create an external web table

DROP EXTERNAL TABLE IF EXISTS public.abalone_external;
CREATE EXTERNAL WEB TABLE public.abalone_external(
  sex text 
  , length float8
  , diameter float8
  , height float8
  , whole_weight float8
  , shucked_weight float8
  , viscera_weight float8
  , shell_weight float8
  , rings integer -- target variable to predict
) location('http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data') 
format 'CSV' 
(null as '?');


--Create a table at Greenplum Database from an external table
DROP TABLE IF EXISTS public.abalone;
CREATE TABLE public.abalone AS
SELECT * FROM public.abalone_external
DISTRIBUTED BY (sex);

SELECT * FROM public.abalone LIMIT 10;

2. COPY table_name FROM directory DELIMITER

두번째 방법은 데이터를 csv나 text 파일로 다운로드를 한 후에 => psql로 \COPY 문을 사용해서 Greenplum DB에 데이터를 업로드하는 방법입니다. 만약 회사 보안 상 외부 website url에서 external table로 가져올 수 없는 경우에 유용하게 사용할 수 있는 방법입니다. 파일 다운로드 한 경로에 가서 pwd 로 경로 확인한 후에 FROM 뒤에 데이터를 가져올 경로를 입력하시면 됩니다.

-- (2) COPY

DROP TABLE IF EXISTS public.abalone;
CREATE TABLE public.abalone(
  sex text 
  , length float8
  , diameter float8
  , height float8
  , whole_weight float8
  , shucked_weight float8
  , viscera_weight float8
  , shell_weight float8
  , rings integer
) DISTRIBUTED BY (sex);


COPY public.abalone 
(sex, length, diameter, height, whole_weight, shucked_weight, 
viscera_weight, shell_weight, rings) 
FROM '/Users/ihongdon/Downloads/abalone.data.txt' DELIMITER ',' CSV;

Encodnig 을 설정하려면 아래의 예처럼 COPY 구문의 'ENCODING' 매개변수에 원하는 encoding 방법을 설정해주면 됩니다.

COPY public.abalone 
(sex, length, diameter, height, whole_weight, shucked_weight, 
viscera_weight, shell_weight, rings) 

FROM '/Users/ihongdon/Downloads/abalone.data.txt'
(DELIMITER ',', FORMAT CSV, ENCODING 'UTF8');

3. INSERT INTO VALUES ()

입력해야 할 데이터 개수가 몇 개 안되는 경우, 혹은 기존 table에 소수의 데이터를 추가하는 경우에 간단하게 사용할 수 있는 방법이 insert into values() 입니다. tutorial 만들거나 교육용 샘플 데이터 만들 때 주로 사용하곤 하는 방법입니다. (반면, 데이터 개수가 많아지면 아무래도 사용하기에 좀 꺼려지는 방법입니다. -_-;)

create table에서 만든 칼럼과 동일한 순서로 데이터를 삽입할 때는 칼럼 이름을 생략해도 됩니다.

-- (3)INSERT INTO VALUES()

DROP TABLE IF EXISTS public.abalone;
CREATE TABLE public.abalone(
sex text 
, length float8
, diameter float8
, height float8
, whole_weight float8
, shucked_weight float8
, viscera_weight float8
, shell_weight float8
, rings integer
) DISTRIBUTED BY (sex);


INSERT INTO public.abalone 
(sex, length, diameter, height, whole_weight, shucked_weight, viscera_weight, shell_weight, rings) 
VALUES 
('M',0.455,0.365,0.095,0.514,0.2245,0.101,0.15,15), 
('M',0.35,0.265,0.09,0.2255,0.0995,0.0485,0.07,7), 
('F',0.53,0.42,0.135,0.677,0.2565,0.1415,0.21,9), 
('M',0.44,0.365,0.125,0.516,0.2155,0.114,0.155,10), 
('I',0.33,0.255,0.08,0.205,0.0895,0.0395,0.055,7), 
('I',0.425,0.3,0.095,0.3515,0.141,0.0775,0.12,8), 
('F',0.53,0.415,0.15,0.7775,0.237,0.1415,0.33,20), 
('F',0.545,0.425,0.125,0.768,0.294,0.1495,0.26,16);

4. DB tool인 DBeaver Import Wizard 사용

csv나 text 파일로 다운로드 해놓은 데이터셋이 있는 경우, DB tool인 DBeaver나 pgAdmin IV의 Import Wizard를 사용하는 방법도 매우 편리합니다.

-- (4) DBeaver tool's Import Wizard

DROP TABLE IF EXISTS public.abalone;

CREATE TABLE public.abalone(

sex text

, "length" float8

, "diameter" float8

, "height" float8

, whole_weight float8

, shucked_weight float8

, viscera_weight float8

, shell_weight float8

, rings integer

) DISTRIBUTED BY (sex);

(1) DBeaver tool의 좌측 Database Navigator > DB > Schema > Tables > abalone 테이블에 '마우스 오른쪽' 클릭 > 'Import Data' 선택

(2) Data Transfer 창에서 'CSV' (Import from CSV file(s)) 선택

3. Input files 의 Source name 선택 > 탐색기 창이 나타나면 데이터셋이 저장되어 있는 경로로 가서 데이터셋 선택 > Open 단추 클릭 > Next 단추 클릭

4. Data Transfer: Preview data import 에서 데이터셋이 맞게 들어가 있는건지 미리보기로 확인

5. Data Transfer: Settings 화면 > Next 단추 클릭

6. Data Transfer: Confirm 창에서 최종 확인 후, 맞으면 'Finish' 단추 클릭

7. Data Transfer: Data transfer completed (소요 시간, 초)

5. Python pandas DataFrame.to_sql() 함수 사용

파일 사이즈가 분석 환경의 메모리 한계 내에서 감당할만한 수준인 경우, Python pandas의 read_csv() 함수로 데이터를 읽어들여서 DataFrame으로 만든 다음에, pandas의 to_sql() 함수를 사용하여 Greenplum DB에 pandas DataFrame을 insert할 수 있습니다. 저는 분석할 때 대부분의 경우 Python이나 R을 GPDB와 연동(connect)에서 사용하는데요, Python이나 R의 분석결과를 다시 GPDB에 집어넣을 때 사용하는 방법입니다.

(Greenplum Database에 Python 연동하는 방법은 https://rfriend.tistory.com/251 , https://rfriend.tistory.com/379 를 참고하세요)

# Importing data from website using pandas read_csv() function
abalone_data_url = "https://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data"
abalone_columns = ( 'sex', 'length', 'diameter', 'height', 'whole_weight', 'shucked_weight', 'viscera_weight', 'shell_weight', 'rings')
df_abalone = pd.read_csv(abalone_data_url, names=abalone_columns)

df_abalone.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 4177 entries, 
0 to 4176 Data columns (total 9 columns): 
sex 4177 non-null object 
length 4177 non-null float64 
diameter 4177 non-null float64 
height 4177 non-null float64 
whole_weight 4177 non-null float64 
shucked_weight 4177 non-null float64 
viscera_weight 4177 non-null float64 
shell_weight 4177 non-null float64 
rings 4177 non-null int64 
dtypes: float64(7), int64(1), object(1) 
memory usage: 293.8+ KB



# export to Greenplum DB using pandas df.to_sql() function
# postgresql://Username:Password@Host:Port/Database
conn = "postgresql://gpadmin:changeme@localhost:5432/demo"

df_abalone.to_sql(
	'abalone', 
	conn, schema='public', 
    if_exists='replace', 
    index=True, 
    index_label='id', 
    chunksize=10000)

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

이번 포스팅이 도움이 되었다면 아래의 '공감~

'를 꾹 눌러주세요.

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot) (0)	2019.03.21
[Greenplum DB] 데이터 전처리 (data preprocessing) : 결측값, 대소문자, 조건문, Substring, 날짜 (0)	2019.03.16
Greenplum DB, Postgresql DB에 사용할 수 있는 오픈소스 database tool DBeaver 설치 및 사용 방법 (0)	2019.03.04
[Docker] Error response from daemon: driver failed programming external connectivity on endpoint, port is already allocated 대처 방법 (port 강제 종료) (11)	2018.09.24
[Greenplum, Postgresql] 중복된 관측치 제거하기 (how to delete duplicated rows) (0)	2018.08.29

Posted by Rfriend

[Greenplum DB] Greenplum DB, MADlib, PL/R, PL/Python을 Docker Image를 이용하여 환경구성 하기

Greenplum and PostgreSQL Database 2018. 8. 13. 00:20

이번 포스팅에서는 도커 허브(Docker Hub)에서 Greenplum Database(이하 GPDB)에 MADlib, PL/R, PL/Python이 설치된 Docker Image를 내려받아 분석 환경을 구성하는 방법을 소개하겠습니다.

이번 포스팅에서 소개하는 gpdb-analytics 도커 이미지는 개인이 집이나 회사에서 GPDB로 MADlib, PL/R, PL/Python 사용하면서 테스트해보고 공부할 때 간편하게 사용할 수 있는 용도로 만든 것입니다.

[사전 준비] Dokcer Install

Docker Image를 이용하여 GPDB+분석툴을 설치할 것이므로, 먼저 Docker Hub (https://hub.docker.com/)에서 회원가입을 하고, https://www.docker.com/products/docker-desktop 사이트에서 자신의 OS에 맞는 Docker를 다운로드 받아 설치하시기 바랍니다.

단, Windows OS 사용자의 경우는 (1) Windows 10 Professional or Enterprise 64-bit 의 경우 'Docker CE for Windows'를 다운받아 설치하시구요, 그 이전 버전 혹은 Home Edition 버전 Windows OS 이용하시는 분의 경우는 'Docker Toolbox'를 다운로드 받아서 설치하시기 바랍니다.

[ Docker Hub에서 gpdb-analytics 도커 이미지 내려받아서 GPDB 분석 환경 구성하기 ]

1. Docker Hub에서 gpdb-analytics 도커 이미지 내려받기 (docker pull)

(터미널 사용)

## Docker 이미지 내려 받기

$ docker pull hdlee2u/gpdb-analytics

## Docker 이미지 확인 하기

$ docker images

REPOSITORY               TAG                 IMAGE ID            CREATED             SIZE
centos                     7                   d123f4e55e12        9 months ago        197MB
hdlee2u/gpdb-base        latest              bfe4e63b8e81         2 years ago           1.17GB

hdlee2u/gpdb-analytics latest 3be773a1a7e1 About a minute ago 4.93GB

2. 도커 이미지를 실행하여 Docker Container 를 생성하고, GPDB 분석 환경 시작하기

## Docker 이미지를 실행/ 5432를 기본 포트로, ssh를 2022포트를 사용하여 접근 가능하도록 Docker 컨테이너 생성

$ docker run -i -d -p 5432:5432 -p 28080:28080 --name gpdb-ds --hostname mdw hdlee2u/gpdb-analytics /usr/sbin/sshd -D

## Docker 컨테이너 목록 확인

$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

7518fd48450a 575a7d45999d "/bin/bash" 1 minute ago Up 6 hours 0.0.0.0:5432->5432/tcp, 0.0.0.0:28080->28080/tcp gpdb-ds

## Start GPDB and Use psql

$ docker exec -it gpdb-ds /bin/bash

[root@mdw /]# su - gpadmin
[gpadmin@mdw ~]$ gpstart -a

20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Starting gpstart with args: -a
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Gathering information and validating the environment...
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 5.10.2 build commit:b3c02f3acd880e2d676dacea36be015e4a3826d4'
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Greenplum Catalog Version: '301705051'
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[WARNING]:-postmaster.pid file exists on Master, checking if recovery startup required
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Commencing recovery startup checks
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Have lock file /tmp/.s.PGSQL.5432 but no process running on port 5432
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-No Master instance process, entering recovery startup mode
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Clearing Master instance lock files
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Clearing Master instance pid file
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Starting Master instance in admin mode
20180821:04:45:10:000043 gpstart:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20180821:04:45:10:000043 gpstart:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20180821:04:45:10:000043 gpstart:mdw:gpadmin-[INFO]:-Setting new master era
20180821:04:45:10:000043 gpstart:mdw:gpadmin-[INFO]:-Commencing forced instance shutdown
20180821:04:45:12:000043 gpstart:mdw:gpadmin-[INFO]:-Starting Master instance in admin mode
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Setting new master era
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Master Started...
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Shutting down master
20180821:04:45:14:000043 gpstart:mdw:gpadmin-[INFO]:-Commencing parallel segment instance startup, please wait...
...
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-Process results...
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-----------------------------------------------------
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-   Successful segment starts                                            = 2
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-   Failed segment starts                                                = 0
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-   Skipped segment starts (segments are marked down in configuration)   = 0
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-----------------------------------------------------
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-Successfully started 2 of 2 segment instances
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-----------------------------------------------------
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-Starting Master instance mdw directory /data/master/gpseg-1
20180821:04:45:18:000043 gpstart:mdw:gpadmin-[INFO]:-Command pg_ctl reports Master mdw instance active
20180821:04:45:18:000043 gpstart:mdw:gpadmin-[INFO]:-No standby master configured. skipping...
20180821:04:45:18:000043 gpstart:mdw:gpadmin-[INFO]:-Database successfully started
[gpadmin@mdw ~]$
[gpadmin@mdw ~]$
[gpadmin@mdw ~]$ psql
psql (8.3.23)
Type "help" for help.

gpadmin=# \q

3. PGAdmin IV 로 GPDB 연결하기

GPDB 5.x 버전에서 SQL Query를 할 때 PGAdmin IV 를 사용합니다. (GPDB 5.x 버전에서는 PGAdmin III 는 작동하지 않으며, 반대로 GPDB 4.x 버전에서는 PGAdmin IV가 작동하지 않고 대신 PGAdmin III만 작동합니다)

PGAdmin IV 는 https://www.pgadmin.org/download/ 에서 다운로드 하여 설치하면 됩니다.

Host : localhost
Port : 5432
Maintenance DB : gpadmin
Username : gpadmin
Password : pivotal
Group: Servers
Ternel port: 22

4. Jupyter Notebook으로 GPDB 연결하기

4-0. Python 2.7 version의 Anaconda 설치

https://www.anaconda.com/download 에서 자신의 OS에 맞는 Python 2.7 version의 Anaconda를 설치합니다. GPDB 5.x 버전은 Python 2.7 version을 지원합니다.

Anaconda를 설치하였으면, Anaconda Navigator를 실행한 후 base(root) 환경(즉, python 2.7)에서 'Jupyter Notebook'의 Launch 단추를 눌러서 Jupyter Notebook을 실행합니다.

터미널을 이용해서 가상환경을 조회, 선택하고 Jupyter Notebook을 실행할 수도 있습니다.

# 가상 환경 리스트 조회

$ conda env list

# 가상 환경 선택 (가상환경 리스트에서 python 2.7 버전 선택, windows의 경우: activate env_name)

$ source activate env_name

# Jupyter Notebook 실행

$ jupyter notebook

4-1. pip install 로 추가로 필요한 Python 패키지 설치하기

(터미널에서)

$ pip install --upgrade pip

$ pip install psycopg2

$ pip install sqlalchemy

$ pip install sql_magic

$ pip install math

$ pip install textwrap

$ pip install os

$ pip install Ipython

$ pip install ipywidgets

$ jupyter nbextension enable --py widgetsnbextension

$ pip install pygraphviz

4-2. Jupyter Notebook에서 DB Connection 설정하기

4-2-1. Python packages importing

# Common modules

import numpy as np

import pandas as pd

from pandas import DataFrame

from pandas import Series

import sklearn

import math

import textwrap as tw

# For DB Connecton

import psycopg2

from sqlalchemy import create_engine

import sql_magic

# For reproducibility

np.random.seed(2622)

# Directory

import os

4-2-2. Visualization Parms Setup

import matplotlib as mpl

import matplotlib.pyplot as plt

import seaborn as sns

# To draw plots in jupyter notebook

#%matplotlib inline

%pylab inline

from pylab import rcParams

rcParams['figure.figsize'] = 12, 8

rcParams['axes.labelsize'] = 14

rcParams['xtick.labelsize'] = 12

rcParams['ytick.labelsize'] = 12

pd.set_option('display.max_columns', None)

pd.set_option('display.max_colwidth', 1000)

# Display

import ipywidgets as widgets

import IPython.display as ipd

from IPython.display import display

# interpret string as markdown

def printmd(string):

ipd.display(ipd.Markdown(string))

# seaborn style

sns.set(style="darkgrid")

4-2-3. Greenplum Database Connection Setup

# put your own GPDB information

user = 'gpadmin'

password = 'pivotal'

host = 'localhost'

db = 'gpadmin'

connection_string = "postgresql://{user}:{password}@{host}/{db}".\

format(user=user,

password=password,

host=host,

db=db)

conn = psycopg2.connect(connection_string)

cur = conn.cursor()

conn.autocommit = True

# helper function

def query_gpdb(query):

cur.execute(query)

colnames = [desc[0] for desc in cur.description]

return DataFrame(cur.fetchall(), columns=colnames)

4-2-4. sql_magic Setup

https://github.com/pivotal-legacy/sql_magic

sql_magic is Jupyter magic for writing SQL to interact with Greenplum/PostgreSQL database, Hive and Spark. Query results are saved directly to a Pandas dataframe.

# sql_magic package and ext sql_magic to query GPDB

%load_ext sql_magic

#%reload_ext sql_magic

# sql_magic

postgres_engine = create_engine(connection_string)

%config SQL.conn_name = 'postgres_engine'

# '%execsql' for sql execution,

# '%read_sql' for reading table as a DataFrame format

from IPython.core.magic import (register_line_magic, register_cell_magic, register_line_cell_magic)

@register_cell_magic

def execsql(line, cell):

_ = postgres_engine.execute(cell)

return

드디어 GPDB를 개인 컴퓨터에서 테스트, 공부용으로 간편하게(? ^^;) 설치하여 보았습니다. 수고 많으셨습니다.

Jupyter Notebook 에서 sql 매직 언어로 DB를 조회할 수 있습니다.

# GPDB 버전 확인

%read_sql select version();

# GPDB instance 확인 (하나의 서버에 1개 master, 2개 segment가 설치된 경우임)

%read_sql select * from pg_catalog.gp_segment_configuration

# MADlib version 확인

%read_sql select madlib.version();

# PL/Languages 확인

sql query가 두 줄 이상일 경우 %%read_sql 처럼 % 두개를 앞에 써줍니다. (sql query 가 한 줄일 경우 %read_sql)

%%read_sql

select *

from pg_catalog.pg_language;

# Table 생성

%%execsql

drop table if exists tmp;

create table tmp (

id int,

var1 varchar(10)

);

5. Docker Container 중단, 재시작, 작동 중인 컨테이너 목록 확인

## Docker 컨테이너 중단, 재시작, 목록 확인

$ docker stop gpdb-ds

$ docker start gpdb-ds

$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
7518fd48450a 575a7d45999d "/bin/bash" 2 minutes ago Up 6 hours 0.0.0.0:5432->5432/tcp, 0.0.0.0:28080->28080/tcp gpdb-ds

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

---------------

혹시 아래와 같은 에러가 발생하는 경우 http://rfriend.tistory.com/396 포스팅을 참고하세요.

Error response from daemon: driver failed programming external connectivity on endpoint gpdb-ds (d519c381360008f0ac0e8d756e97aeb0538075ee1b7e35862a0eaedf887181f1): Error starting userland proxy: Bind for 0.0.0.0:5432 failed: port is already allocated

Error: failed to start containers: gpdb-ds

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Docker] Error response from daemon: driver failed programming external connectivity on endpoint, port is already allocated 대처 방법 (port 강제 종료) (11)	2018.09.24
[Greenplum, Postgresql] 중복된 관측치 제거하기 (how to delete duplicated rows) (0)	2018.08.29
[Greenplum DB] Greenplum DB에 MADlib, PL/R, PL/Python 설치한 Docker Image 만들어서 Docker Hub에 올리기 (0)	2018.08.13
[Greenplum DB] 세계 최초 오픈소스 대용량 데이터 병렬 처리 데이터 분석 플랫폼, Greenplum Database (GPDB) (3)	2018.08.08
리눅스 정규 표현식 (Linux Regular Expression) (8)	2018.08.03

Posted by Rfriend

이전 1 2 3 다음

R, Python 분석과 프로그래밍의 친구 (by R Friend)

'Greenplum Database'에 해당되는 글 21건

[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum] 오픈소스 Greenplum DB와 Apache MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] GPDB에 PL/R Language Extension, R 패키지 수동 설치 방법

1. Greenplum PL/R Extention (Procedural Language R) 설치 방법

2. Greenplum DB에 R 패키지 설치 방법

1. Greenplum PL/R Extention 설치 방법

2. Greenplum DB에 R 패키지 설치 방법 (Installing external R packages)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법

(1) shp2pgsql로 shapefile data를 import하는 SQL문 만들기

(2) shp2pgsql로 shapefile data를 import하는 SQL문을 psql로 보내서 import하기

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기

(1) psql을 사용하여 위경도를 포함한 csv 파일을 Greenplum DB에 import하기

(2) PostGIS 함수로 공간 정보 뽑아내기

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] GPDB docker에 PostGIS 설치하기

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum & PostgreSQL DB] 동일 간격 범위별로 관측치 개수를 세고(width_bucket), Python으로 막대그래프 시각화하기(bar plot)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] 외부 데이터를 Table에 업로드하는 5가지 방법 : CREATE EXTERNAL TABLE, COPY, INSERT INTO VALUES(), pd.DataFrame.to_sql(), DBeaver import

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] Greenplum DB, MADlib, PL/R, PL/Python을 Docker Image를 이용하여 환경구성 하기

4-2-1. Python packages importing

4-2-2. Visualization Parms Setup

4-2-3. Greenplum Database Connection Setup

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

카테고리

태그목록

티스토리툴바