지난번 포스팅에서는 Python의 ipython-sql, pgspecial 패키지를 사용하여 Jupyter Notebook 에서 PostgreSQL, Greenplum database 에 접속(access)하고 SQL query, meta-commands 를 하는 방법(https://rfriend.tistory.com/572)을 간략하게 소개하였습니다.


이번 포스팅에서는 psycopg2 와 ipython-sql 패키지를 사용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database 에 접속하는 4가지 방법을 추가로 소개하겠습니다.


특히, 사용자이름(username), 비밀번호(password), 호스트(host), 포트(port), 데이터베이스(database) 등과 같이 보안이 요구되고 다른 사용자에게 노출이나 공유가 되면 곤란한 정보들(DB Credentials)을 Jupyter Notebook에서 표기/노출하지 않고 별도의 파일로 보관하면서, 이를 불러와서 DB access 할 수 있는 방법들에 주안점을 두고 소개하겠습니다.


-- Jupyter Notebook 의 Cell 안에서 DB credentials 직접 입력 (* 외부 노출되므로 권장하지 않음)

(1) %sql postgresql://Username:Password@Host:Port/Database

(2) %sql $connection_string


-- 별도의 폴더에 별도의 파일로 DB credentials 관리하고, 이를 불러와서 Jupyter Notebook에 입력

    (* 보안유지 되므로 권장함)

(3) config.py 별도 파일 & %sql $connection_string

(4) db_credentions 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path





(0) Python 패키지 사전 설치


먼저, 명령 프롬프트 창에서 아래의 PostgreSQL, Greenplum database에 접속하고 SQL query 를 하기 위해 필요한 Python 패키지들을 pip로 설치해줍니다.



-- (명령 프롬프트 창에서 설치)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 



-- Jupyter Notebook 의 Cell 안에서 DB credentials 직접 입력

   (* 편리하기는 하지만, DB 접속 정보가 외부에 노출되므로 권장하지 않음. )


 (1) %sql postgresql://Username:Password@Host:Port/Database


가장 편리한 방법은 SQLAlchemy 표준 URL (database-driver://Username:Password@Host:Port/Database) 에 따라 PostgreSQL, Greenplum database에 connection engine을 생성하여 접속하는 방법입니다.


하지만, 이 방법은 Jupyter Notebook에 DB 접속정보가 고스란히 노출되기 때문에 만약 다른 조직, 팀원 간에 협업을 하고 notebook 파일을 공유해야할 일이 생길 경우 보안 방침에 위배가 되므로 권장하는 방법은 아닙니다.



%load_ext sql


# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@demo'







 (2) %sql $connection_string


%sql $connection_string 문으로 Jupyter Notebook에서 동적으로 DB credentials 를 Python string format 으로 입력받아서 PostgreSQL, Greenplum database에 접속할 수 있습니다. 


다만, 아래처럼 DB credentials 를 Jupyter notebook 의 Cell 안에서 직접 입력하면 DB 접속 정보가 외부로 노출되는 문제가 있습니다.



%load_ext sql


# DB credentials

username = "gpadmin"
password = "changeme"
host = "localhost"
port = "5432"
database = "demo"


# connection strings using Python string format
connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)


# dynamic access dredentials
%sql $connection_string

 





-- 별도의 폴더에 별도의 파일로 DB credentials 관리하고, 이를 불러와서 Jupyter Notebook에 입력

    (* 보안유지 되므로 권장함)


 (3) config.py 별도 파일 & %sql $connection_string


세번째 방법은 DB Credentials 정보를 별도의 파일에 분리해서 만들어놓고, 이를 불러와서 DB connect 하는 방법입니다. 아래에 예를 들어보면, (폴더, 파일 이름은 각자 알아서 정해주면 됨)


(a) HOME directory 밑에 DB credentials 파일을 넣어둘 'db_cred' 라는 이름의 폴더 만들고,

(b) 'db_cred' 폴더 안에 'gpdb_credentials.py', '__init__.py' 라는 이름의 2개의 Python 파일을 생성함.

     'gpdb_credentials.py' 파일에는 Dictionary (Key : Value 짝) 형태로 Username, Password, Host, Port, Database 정보를 입력해줌. 여러개의 Database 별로 credentials 정보를 각각 다른 이름의 Dictionary 로 하나의 파일 안에 생성해놓을 수 있음.

     '__init__.py' 파일은 내용은 비어있으며, 해당 폴더의 Python 파일을 패키지로 만들기 위해 생성해줌.

(c) Jupyter Notebook 을 작업하는 Directory 에서도 HOME directory 밑의 'db_cred' 폴더에 접근해서 'gpdb_credentials.py' 파일에 접근할 수 있도록 sys.path.append(cred_path) 로 Python 의 Path 에 추가해줌. (sys.path.append(cred_path))

(d) 작업을 하는 Jupyter Notebook 에서 'from gpdb_credentials import demo_db' 문으로 gpdb_credentials.py 파일에서 'demo_db' Dictionary 를 불러옴.

(e) 'demo_db' Dictionary 에서 DB connection에 필요한 정보를 파싱해옴. (dict['key'] 인덱싱)

(f) %sql $ 문 뒤에 (e)에서 파싱해서 만든 connection_string을 입력해서 DB connect 함.



%load_ext sql


# put a folder and DB credential files at HOME directory

import os
homedir = os.getenv('HOME')
cred_path = os.path.join(homedir, 'db_cred')


# add a 'cred_path' for interpreter to search
import sys
sys.path.append(cred_path)


# import DB credentials from 'gpdb_credentials.py' dictionary file.

from gpdb_credentials import demo_db


# parsing DB credentials and connect to Greenplum using %sql $connection_string

username = demo_db['Username']
password = demo_db['Password']
host = demo_db['Host']
port = demo_db['Port']
database = demo_db['Database']

connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)

%sql $connection_string






(4) db_credentials 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path


명령 프롬프트 창에서 아래처럼 0.3.9 버전의 ipython-sql을 설치해줍니다. (최신 버전은 0.4.0 이지만 Python 3.x. 버전의 ipython-sql 0.4.0 버전에 DSN connections 를 하는데 있어 config 를 반환하지 않는 bug가 있습니다. config bug fix 되기 전까지는 0.3.9 버전으로 사용하기 바랍니다.)


-- 명령 프롬프트 창에서 ipython-sql 0.3.9 버전 설치

pip install ipython-sql==0.3.9


(a) PostgreSQL, Greenplum database 접속 정보(connection info.)를 별도의 configuration file 에 저장하여 HOME directory 밑에 보관합니다. 이때 2개 이상의 복수의 DB credentials 정보를 [DB alias] 로 구분해서 하나의 configuration file에 저장해서 사용할 수 있습니다.


파일 이름을 ".odbc.ini", ".dsn.ini" 처럼 "."으로 시작하면 '숨김 파일(hidden file)'이 되어 평상시에는 탐색기, Finder에서는 볼 수가 없으므로 DB 접속정보를 관리하는데 좀더 보안에 유리합니다.

(참고로, Windows OS에서 숨김파일을 보려면, Windows 탐색기에서 [구성] > [폴더 및 검색 옵션] > [폴더 옵션] 대화상자에서 [보기] 탭을 클릭 > [고급 설정]에서 "숨김 파일 밒 폴더 표시"를 선택하면 됩니다.

Mac OS 에서는 Finder에서 "Shift + Command + ." 동시에 눌러주면 숨김 파일이 표시됩니다.)


(b) Jupyter Notebook에서 ipython-sql 로 DSN connections 을 할 수 있습니다.

    %config SqlMagic.dsn_filename = "$homedir/.odbc.ini"


(c) DB connect 된 이후에 제일 처음으로 %sql 로 SQL query 할 때 DB credentions 의 DB alias 를 [ ] 안에 넣어서 명시를 해주고(예: %sql [demo_db] SELECT version();), 그 다음부터 %sql 문으로 SQL query 할 때는 DB alias 를 안써주고 바로 SQL query 를 하면 됩니다.


(d) Jupyter Notebook의 중간 Cell 에서 사용(connect)하려는 DB를 바꾸고 싶으면 %sql [DB_alias2] SELECT .... 처럼 [DB_alias] 부분에 다른 DB alias 이름을 명시해주고 SQL query 를 하면, 그 이후 Cell 부터는 새로운 DB 를 connect 해서 query를 할 수 있습니다.

(예: %sql [dev_db] SELECT COUNT(*) FROM tbl;)



%load_ext sql


import os
homedir = os.getenv('HOME')

# parse and configure gpdb credentials and access to GPDB
%config SqlMagic.dsn_filename = "$homedir/.odbc.ini"

# put [alias_name] after %sql in the first line
%sql [demo_db] SELECT version();

[Out] * postgresql://gpadmin:***@localhost:5432/demo

1 rows affected.



* Reference: https://pypi.org/project/ipython-sql/


다음 포스팅에서는 ipython-sql 로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법(https://rfriend.tistory.com/578)을 소개하겠습니다.


이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요!



반응형
Posted by Rfriend

댓글을 달아 주세요

Python의 SQLAlchemy (모든 DB), psycopg2 (PostgreSQL, Greenplum) 패키지를 이용하여 Spyder 나 Pycharm 과 같은 IDE에서 PostgreSQL, Greenplum database에 접속(access) 하고 SQL query 를 할 수 있습니다.

 

 

 

그렇다면 Jupyter Notebook 에서도 DB access, SQL query를 할 수 있으면 편하겠지요?

 

이번 포스팅에서는 Python의 ipython-sql 과 pgspecial 패키지를 이용하여 PostgreSQL, Greenplum database 에 Jupyter Notebook으로 접속(access DB)하여 SQL query 를 하는 방법을 소개하겠습니다.

 


(1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기

(2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)

(3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands query 하기 (\l, \dn, \dt)

 

 

 

 

 

(0) 사전 설치가 필요한 Python 패키지 리스트

 

명령 프롬프트 창에서 아래의 5개 패키지에 대해서 pip 로 설치해주시기 바랍니다.

 

sqlalchemy, psycopg2는 PostgreSQL, Greenplum DB 접속(access, connection)를 위해서 필요한 Python 패키지 입니다.

 

ipython-sql, sql_magic은 IPython으로 Jupyter Notebook에서 DB access, SQL query 를 하기 위해 필요한 Python 패키지입니다.

 

pgspecial은 Jupyter Notebook에서 PostgreSQL, Greenplum DB에 meta-commands (역슬래쉬 \ 로 시작하는, psql 에서 사용하는 \l, \dn, \dt 명령문) 를 위해 필요한 Python 패키지입니다.

 

ipython-sql 의 경우 2020.12월 현재 0.4.0 버전 (python 3.x) 이 최신인데요, %config로 DB access 하는 명령문의 bug가 아직 fix가 안되어 있어서, 아래처럼 ipython-sql==0.3.9 로 한단계 낮은 버전으로 설치해주세요.

 

 

-- 명령 프롬프트 창에서 pip 로 python 패키지 설치

 

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 

 

만약 psycopg2 모듈을 pip 로 설치하다가 에러가 나면 아래처럼 wheel package 를 이용해서 psycopg2-binary 로 설치해보세요. 

 

    Error: pg_config executable not found.    

    pg_config is required to build psycopg2 from source.  Please add the directory

    containing pg_config to the $PATH or specify the full executable path with the

    option:   

        python setup.py build_ext --pg-config /path/to/pg_config build ..

    or with the pg_config option in 'setup.cfg'.

 

 

$ pip install psycopg2-binary

 

 

 (1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기

 

%load_ext sql 로 IPython의 sql 을 로딩하여 %sql 또는 %%sql magic 명령문을 사용할 수 있습니다.

PostgreSQL, Greenplum database에 접속할 때는 SQLAlchemy 의 표준 URL connect strings 를 사용합니다.

 

[ SQLAlchemy 의 표준 Database URL]

 

 dialect+driver://username:password@hoat:port/database

 

아래의 db credentials 로 Greenplum database에 접속할 때의 예입니다.

- driver: postgresql

- username: gpadmin

- password: changeme

- host: localhost

- port: 5432

- database: demo

 

 

%load_ext sql

 

# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@testdb'

 

 

 

 

* 위의 %sql SQLAlchemy 표준 URL 방법 외에 Jupyter Notebook에서 PostgreSQL, Greenplum DB에 접속하는 다른 3가지 추가 방법은 https://rfriend.tistory.com/577 를 참고하세요.

 

 

 

 (2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)

 

Jupyter Notebook의 Cell 안에 1줄 SQL query일 경우는 %sql 로 시작하고, 2줄 이상 SQL query 일 경우에는 %%sql 로 시작합니다.

 

(2-1) %sql : 1줄의 SQL query

 

1줄짜리 SELECT 문으로 PostgreSQL의 버전을 확인해보겠습니다.

 

 

%sql SELECT version();

 

[Out]

version

PostgreSQL 9.4.24 (Greenplum Database 6.10.1 build commit:efba04ce26ebb29b535a255a5e95d1f5ebfde94e) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Aug 13 2020 02:56:29

 

 

 

 

 

 

(2-1) %%sql : 2줄 이상의 SQL query

 

아래 예제는 pg_catalog.pg_tables 테이블에서 스키마 이름이 pg_catalog, information_schema 가 아닌 테이블을 조회하여 1개만 반환해보는 query 입니다.

 

 

%%sql
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
    schemaname != 'information_schema'
LIMIT 1;

 

 

 

 

 

 (3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands (\l, \dn, \dt)

 

meta-commands 는 psql 에서 역슬래쉬(\)와 함께 사용해서 데이터베이스, 스키마, 테이블 등을 조회할 때 사용하는 명령어를 말합니다. pgspecial 패키지는 Jupyter Notebook에서 meta-commands 를 사용할 수 있게 해줍니다.

 

(3-1) database 조회 : %sql \l  (역슬래쉬 + L)

 

 

%sql \l

 

[Out]

Name

Owner

Encoding

Collate

Ctype

Access privileges

demo

gpadmin

UTF8

en_US.utf8

en_US.utf8

=Tc/gpadmin

gpadmin=CTc/gpadmin

dsuser=CTc/gpadmin

gpperfmon

gpadmin

UTF8

en_US.utf8

en_US.utf8

None

postgres

gpadmin

UTF8

en_US.utf8

en_US.utf8

None

template0

gpadmin

UTF8

en_US.utf8

en_US.utf8

=c/gpadmin

gpadmin=CTc/gpadmin

template1

gpadmin

UTF8

en_US.utf8

en_US.utf8

=c/gpadmin
gpadmin=CTc/gpadmin

 

 

 
(3-2) Schema 조회 : %sql \dn
 

 

%sql \dn

 

[Out]

Name Owner
gp_toolkit gpadmin
madlib dsuser14
public gpadmin

 

 

 

 
(3-3) Table 조회 : %sql \dt
 
아래 예제는 public 스키마에서 "ab" 로 시작하는 모든 테이블(public.ab*)을 조회한 것입니다.
 

 

%sql \dt public.ab*

 

[Out]

Schema Name Type Owner
public abalone table

gpadmin

public abalone_corr table

gpadmin

public abalone_corr_summary table

gpadmin

public abalone_correlations table

gpadmin

 

 

 

이상으로 ipython-sql, pgspecial 패키지를 사용해서 PostgreSQL, Greenplum database에 접속하고 SQL query, meta-commands 하는 방법에 대한 가장 기본적이고 개략적인 소개를 마치겠습니다.
 
* 다음번 포스팅에서는 SQLAlchemy, psycopg2, ipython-sql 로 Jupyter Notebook 에서 PostgreSQL, Greenplum database에 접속하는 4가지 방법(https://rfriend.tistory.com/577)에 대한 소소한 팁을 추가로 소개하겠습니다.
 
* ipython-sql 로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법https://rfriend.tistory.com/578 를 참고하세요.
 
* ipython-sql로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법https://rfriend.tistory.com/579 를 참고하세요.
 
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요!

 

 

 

반응형
Posted by Rfriend

댓글을 달아 주세요