[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook에서 DB 접속하는 4가지 방법

지난번 포스팅에서는 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)을 소개하겠습니다.

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

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