[PostgreSQL, Greenplum] ipython-sql, pgspecial로 Jupyter Notebook에서 DB접속하고 SQL query 하기

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 를 참고하세요.

 

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

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