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

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)
    

 

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

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

'를 꾹 눌러주세요.