[PostgreSQL, Greenplum] ipython-sql, pgspecial로 Jupyter Notebook에서 DB접속하고 SQL query 하기
Greenplum and PostgreSQL Database 2020. 10. 20. 23:52Python의 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 [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]
|
(2-1) %%sql : 2줄 이상의 SQL query
아래 예제는 pg_catalog.pg_tables 테이블에서 스키마 이름이 pg_catalog, information_schema 가 아닌 테이블을 조회하여 1개만 반환해보는 query 입니다.
|
%%sql
|
|
(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]
|
|
%sql \dn
[Out]
|
|
%sql \dt public.ab*
[Out]
|
