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;

 



  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

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



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

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


Posted by R Friend R_Friend