야장에 바로 적는다 — DuckDB 답사기 제7장

序

종래 방식이 치르는 세 가지 값

THE DRAWBACKS OF THE TRADITIONAL WAY

전통적으로 데이터 과학자는 Jupyter Notebook을 써서 데이터베이스 서버나 CSV·JSON 같은 외부 데이터셋에서 데이터를 끌어와 pandas DataFrame에 담는다. 그리고 그 DataFrame을 시각화에 쓴다. 이 접근에는 몇 가지 단점이 있다.

서버가 상한다 데이터베이스 서버에 질의하는 일이 그 서버의 성능을 떨어뜨릴 수 있다. 데이터베이스 서버는 분석 작업 부하에 최적화되어 있지 않을 수 있다.
자원을 먹는다 데이터를 DataFrame으로 적재하는 일이 메모리와 계산을 비롯한 귀한 자원을 차지한다. 예컨대 데이터셋의 어떤 국면을 시각화하려는 것이 목적이라면, 시각화를 수행하기 전에 데이터셋 전체를 메모리에 적재해야 한다.
그림도 메모리를 쓴다 Matplotlib으로 시각화를 그리는 일 또한 상당한 양의 메모리를 쓴다. 내부적으로 Matplotlib은 figure, axes, line, text 등 여러 그래픽 요소를 메모리에 유지한다. 이 요소들은 생성되고 렌더링될 때마다 자원을 소비한다. 아울러 Matplotlib은 그리기에 쓰이는 데이터 배열을 다루며 처리를 위해 임시로 메모리에 저장한다. 여러 그림이나 figure를 만들면 각 figure와 그에 딸린 데이터가 명시적으로 닫히거나 지워지기 전까지 메모리에 남아, 메모리 사용이 늘어난다. 렌더링에 쓰이는 백엔드도 한몫을 하는데, 어떤 백엔드는 복잡한 시각 요소와 그래픽 인터페이스를 다루는 데 더 많은 메모리를 요구한다. 고해상도 그림이나 여러 개의 서브플롯, 큰 데이터셋을 다룰 때 특히 두드러진다.

그렇다고 pandas를 헐 것은 아니다. Power BI, Domo, Tableau, 엑셀 같은 도구가 데이터를 적재하고 시각화하는 과정을 간단하게 만들어 주지만, pandas는 내장 그리기 기능 덕에 EDA 중의 빠른 인라인 시각화에 여전히 훌륭한 선택이다. 최소한의 노력으로 선 그림, 막대 차트, 히스토그램, 산점도 같은 기본 시각화를 pandas DataFrame이나 Series에서 곧바로 만들 수 있다. 데이터를 다루면서 분포와 추세와 양상을 빠르게 눈에 담기에 편리하며, 다른 라이브러리로 갈아타거나 추가 단계를 밟을 필요가 없다.

성능을 높이려면, 이상적으로는 데이터의 처리, 곧 모든 데이터 가공과 여과를 데이터 분석을 효율적으로 수행할 수 있는 클라이언트에 떠넘기고 그 결과만 받아 시각화에 쓰는 것이 좋다. 이것이 이 장의 주제, JupySQL이다.

일러두기

DuckDB 역시 SQL로 데이터셋에 직접 접근하게 해 주지만, JupySQL을 쓰면 그 연산을 노트북의 SQL 셀로 수행할 수 있다.

검토 방법에 관한 밝힘

이 장의 결과는 거의 전부가 스크린숏 도판이다. 표의 값이나 차트의 수치는 텍스트로 확인되지 않으므로 옮기지 않았다. 대신 이 장에서 실제로 확인되는 것, 곧 명령의 구문과 옵션 목록을 정확히 옮기는 데 힘을 쏟았다. 옵션 표는 원서의 표를 그대로 따랐다.

표기 규칙은 앞 여섯 장과 같다. 문맥으로 복원되는 부분은 점선 밑줄, 복원하지 않은 부분은 …다. 이 권은 구조색을 야장의 연묵(淡墨)으로 삼았고, 강조색은 다시 치자금으로 돌아왔다.

具

JupySQL이란 무엇인가

WHAT IS JUPYSQL?

JupySQL은 Jupyter Notebook을 위한 SQL 클라이언트로, SQL을 써서 Jupyter Notebook 안에서 데이터셋에 직접 접근하게 해 준다. 핵심 착상은 Jupyter Notebook에서 SQL을 실행한다는 것이며, 이름도 거기서 나왔다. JupySQL은 Jupyter에 SQL 셀을 더하는 ipython-sql의 포크이며, 현재 Ploomber의 팀이 활발히 관리하고 개선하고 있다.

JupySQL을 쓰면 데이터셋을 담아 둘 DataFrame을 유지할 필요 없이 SQL로 데이터셋에 질의할 수 있다. 예컨대 MySQL이나 PostgreSQL 같은 데이터베이스 서버에, 또는 DuckDB 엔진을 통해 CSV 파일에 접속할 수 있다. 질의의 결과는 곧바로 시각화에 쓸 수 있다.

JupySQL을 쓰려면 jupysql 패키지를 설치해야 한다. 아울러 JupySQL 안에서 DuckDB를 다루려면 duckdb-engine 패키지도 설치해야 한다.

Jupyter Notebook설치

!pip install jupysql duckdb-engine
!pip install SQLAlchemy

duckdb-engine 패키지는 DuckDB를 위한 SQLAlchemy 드라이버다. SQLAlchemy는 관계형 데이터베이스를 다루기 위한 오픈소스 파이썬 라이브러리로, 유연하고 효율적인 방식으로 데이터베이스와 상호작용하는 고수준·저수준 도구를 제공한다.

이 세 겹이 이 장의 구조를 정한다. JupySQL이 노트북에 셀을 만들고, SQLAlchemy가 연결의 규격을 정하고, DuckDB가 실제로 계산한다. 뒤에 나오는 duckdb://라는 접속 문자열이 SQLAlchemy의 어법이라는 사실도, MySQL로 갈아탈 때 문자열만 바꾸면 되는 이유도 여기서 나온다.

Jupyter Notebook에서 다음 매직 명령을 써서 JupySQL로 데이터 원천과 상호작용한다. 백분율 기호(%)로 표시된다.

옵션	하는 일
%sql	SQL 문 하나를 실행하는 행 매직 명령이다.
%%sql	여러 줄에 걸친 SQL 문을 실행하는 셀 매직 명령이다.
%sqlplot	차트를 그리는 행 매직 명령이다.

기호 하나가 더 붙는지 아닌지로 한 줄과 여러 줄이 갈린다. 사소해 보이지만 이것이 이 장 내내 반복되는 문법이다. 야장에 한 줄 메모를 적을 때와 한 면을 채울 때 붓을 다르게 쥐는 것과 같다.

載

확장을 얹는다 · 그리고 첫 함정

LOADING THE SQL EXTENSION

필요한 패키지를 설치했으면, 새 Jupyter Notebook을 만들고 새 셀을 만들어 %load_ext 행 매직 명령으로 sql 확장을 적재한다.

Jupyter Notebook%load_ext

%load_ext sql

도판 7-3 · %load_ext 행 매직 명령으로 sql 확장을 적재한다

주의 — 주석을 같은 줄에 두지 않는다

여기서 짚어 둘 중요한 점이 하나 있다. 주석을 %load_ext 매직 명령과 같은 줄에 두지 않는다. 그러면 SQL 문에서 오류가 난다. 예컨대 앞 문장에 주석을 붙이려 한다고 하자.

Jupyter Notebook오류가 난다

%load_ext sql         # load the sql extension

이는 “sql # load the sql extension”이라는 이름의 모듈을 적재하려 시도해 오류로 이어진다. 주석을 달려면 별개의 줄에 둔다.

Jupyter Notebook이렇게 쓴다

# load the sql extension
%load_ext sql

매직 명령이 줄 전체를 인수로 삼는다는 사정이 이 함정의 원인이다. 파이썬 문장이 아니라 명령줄에 가까운 문법이므로, 파이썬의 주석 규칙이 통하지 않는다. 야장에도 격이 있다. 본문을 적는 칸과 여백에 적는 방주를 섞으면 나중에 읽을 수 없게 된다.

接

엔진을 붙인다

INTEGRATING WITH DUCKDB

sql 확장을 적재했으면, 데이터를 처리할 데이터베이스 엔진을 적재해야 한다. 이 절에서는 DuckDB를 쓴다. 다음 문장이 DuckDB 메모리 데이터베이스를 띄운다.

Jupyter Notebook접속 문자열

# 메모리 데이터베이스
%sql duckdb://

# 영속 데이터베이스 — 파일 이름을 지정한다
%sql duckdb:///MyDB.db

# 도움말이 필요하면 문서 문자열을 표시한다
%sql?

도판 7-4 · %sql? 로 JupySQL의 문서 문자열을 표시한다

이 문장은 SQLAlchemy 방식의 접속 문자열로 데이터베이스 엔진, 여기서는 DuckDB 엔진에 접속한다. 사선이 둘인 것과 셋인 것의 차이를 눈여겨보아야 한다. duckdb://는 메모리이고 duckdb:///MyDB.db는 파일이다. 제1장과 제3장에서 :memory:와 파일 이름으로 갈렸던 그 선택이, 여기서는 사선 하나로 갈린다.

JupySQL은 일반적으로 연결을 알아서 관리한다. %sql로 SQL 명령을 실행하면 연결을 열고 명령을 실행하고 실행 후에 연결을 자동으로 닫는다. 제1장에서 conn.close()를 잊지 말라고 당부했던 그 일이, 여기서는 도구의 몫이 된다.

問

야장에 적어 묻는다

PERFORMING QUERIES

%sql 매직 명령으로 CSV 파일 airlines.csv에 질의한다. 파일에 직접 질의한다는 점을 눈여겨본다. 표를 먼저 만들 필요가 없다.

Jupyter Notebook한 줄 질의

%sql SELECT * FROM airlines.csv

도판 7-5 · CSV 파일이 DuckDB로 적재된다

%sql 매직 명령의 결과는 sql.run.ResultSet 객체다. 원한다면 결과를 pandas DataFrame으로 바꿀 수 있다.

Jupyter NotebookResultSet → DataFrame

rs = %sql SELECT * FROM 'airlines.csv'
df = rs.DataFrame()    # pandas DataFrame으로 변환한다

일러두기 — 표시 한도와 설정 파일

결과의 기본 표시 한도는 10행이다. 모든 행을 표시하려면 다음 문장을 쓴다.

Jupyter Notebook%config

%config SqlMagic.displaylimit = None    # 또는 s…

설정 상세는 파일에서 읽어 올 수도 있다. 예컨대 ~/.jupysql/config이며, 이때는 toml 패키지의 설치가 필요하다(pip install toml).

질의가 여러 줄에 걸치면 %%sql 셀 매직 명령을 쓴다.

Jupyter Notebook여러 줄 질의

%%sql
SELECT
    count(*) as Count, STATE
FROM airports.csv
GROUP BY STATE
ORDER BY Count

도판 7-6 · 주별 공항 수를 센다. 제5장에서 같은 일을 파이썬 문자열 안의 SQL로 했던 것과 견주어 볼 만하다

여기까지의 질의는 CSV 파일을 DuckDB로 적재하지만, 데이터가 DuckDB 데이터베이스의 표로 영속되지 않는다. airlines의 내용을 다시 쓰려면 질의를 다시 수행해야 한다. 표를 데이터베이스에 남기려면 CREATE TABLE 문을 쓴다.

Jupyter Notebook영속화

%%sql
CREATE TABLE airlines
as
FROM 'airlines.csv'

# 표의 내용을 가져온다
%sql SELECT * FROM airlines

airlines.csv의 내용이 airlines라는 표로 저장된다. 파일 기반 DuckDB 데이터베이스를 쓰고 있으므로 MyDB.db 안에 들어간다

주의 — 메모리와 파일의 갈림

영속 데이터베이스로 DuckDB를 열면 표가 데이터베이스에 자동으로 저장된다. 다음번에 데이터베이스를 열면 표가 그대로 있다. 반면 메모리 DuckDB 데이터베이스에서는 Jupyter 커널을 재시작하면 모든 표가 사라진다.

察

표를 살피는 세 명령 · %sqlcmd

INSPECTING WITH %SQLCMD

DuckDB 데이터베이스의 표를 확인하려면 %sqlcmd 명령을 쓴다. 세 가지 쓰임이 나란히 제시된다.

Jupyter Notebook%sqlcmd 세 가지

# ① 데이터베이스의 표 목록
%sqlcmd tables

# ② 표의 스키마 — -t 인수로 표 이름을 준다
%sqlcmd columns -t airlines

# ③ 표에 관한 통계
%sqlcmd profile -t airlines

도판 7-7 · 7-8 · 7-9 · DuckDB 데이터베이스에 저장된 airlines 표, 그 스키마, 그리고 통계

제3장에서 CLI의 점 명령으로 하던 일들이 여기서 백분율 명령으로 되풀어진다. .table은 %sqlcmd tables가 되고, DESCRIBE는 %sqlcmd columns가 된다. 통계를 내는 profile은 제4장의 describe()에 대응한다. 같은 일을 하는 손잡이가 도구마다 다른 이름으로 달려 있다는 것, 여러 인터페이스를 오가는 사람이 겪는 일이다.

하려는 일	제3장 · CLI	제4장 · 관계형 API	제7장 · JupySQL
표 목록	.table	—	%sqlcmd tables
스키마 보기	DESCRIBE · .schema	—	%sqlcmd columns -t
기초 통계	—	describe()	%sqlcmd profile -t

片

질의를 이름 붙여 갈무리한다 · 스니펫

STORING SNIPPETS

질의의 결과를 DuckDB 데이터베이스의 표로 저장하는 것 말고도, 질의 자체를 저장해 두었다가 나중에 다시 부를 수 있다. 이를 스니펫이라 한다.

Jupyter Notebook--save

%%sql --save state_count
SELECT
    count(*) as Count, STATE
FROM airports.csv
GROUP BY STATE
ORDER BY Count DESC
LIMIT 10

도판 7-10 · JupySQL이 만들어진 스니펫을 실행한다

Jupyter Notebook이름으로 다시 부른다

%sql SELECT * FROM state_count

도판 7-11 · 저장된 스니펫의 질의를 JupySQL이 실행한다. 스니펫 이름이 표 이름처럼 쓰인다

일러두기

저장된 스니펫은 DuckDB 데이터베이스에 영속되지 않는다. 영속 데이터베이스를 쓰더라도 그렇다.

스니펫을 정의할 때 질의를 실행하고 싶지 않으면 --no-execute 옵션을 쓴다.

Jupyter Notebook--no-execute

%%sql --save state_count --no-execute
SELECT
    count(*) as Count, STATE
FROM airports.csv
GROUP BY STATE
ORDER BY Count

검토자의 주석 — 같은 이름, 다른 질의

앞의 --save state_count는 ORDER BY Count DESC LIMIT 10으로 끝나고, --no-execute 판본은 ORDER BY Count로 끝난다. 같은 이름의 스니펫이 서로 다른 질의로 두 번 정의된 것이다. 원서가 옵션 하나만 보이려고 앞의 두 줄을 흘린 것으로 보이지만, 스니펫은 이름으로 부르는 것이므로 나중 정의가 앞의 것을 덮는다는 점은 실무에서 주의할 자리다. 이름을 재사용하면 어느 질의가 남아 있는지 알기 어려워진다.

스니펫이라는 장치가 이 장에서 차지하는 자리는 작지 않다. 질의에 이름을 붙여 두면 뒤의 시각화 절에서 --table 인수에 그 이름을 그대로 넘길 수 있다. 곧 스니펫이 시각화의 재료가 된다. 야장에 적어 둔 항목에 번호를 매겨 두고 나중에 그 번호로 도면을 그리는 일과 같다.

畫

한 줄로 그림을 그린다 · %sqlplot

VISUALIZATION

JupySQL은 %sqlplot 행 매직 명령으로 차트를 그리게 한다. 이 절에서 그리는 것은 넷이다. 히스토그램, 상자 그림, 원 차트, 막대 그림이다. 그리기 전에 Matplotlib 패키지가 설치되어 있어야 한다.

Jupyter Notebook준비

!pip install matplotlib

히스토그램

먼저 질의를 만들어 airports_A라는 이름으로 저장한다. 이름이 “A”로 시작하는 주에 속한 모든 공항을 찾는 질의이며, 곧 AK, AL, AR, AS, AZ다. --no-execute 옵션이 질의의 즉시 실행을 막으므로, 이 시점에 데이터를 가져오거나 처리하지 않는다.

Jupyter Notebook스니펫 → 히스토그램

%%sql --save airports_A --no-execute
SELECT
*
FROM airports.csv
WHERE state LIKE 'A%'

# 그리기
%sqlplot histogram --table airports_A --column STATE

도판 7-12 · 이름이 “A”로 시작하는 주의 공항 수를 보이는 히스토그램

도움말 — 지연 실행이 여기서 되돌아온다

--no-execute로 질의를 정의만 해 두고, %sqlplot이 --table로 그 이름을 받을 때 비로소 실행된다. 제4장에서 익힌 지연 평가의 발상이 다른 도구에서 다른 이름으로 되풀어지는 자리다. 무엇을 물을지 정해질 때까지 읽지 않는다는 원리는 같다.

옵션	하는 일
--table	그리기에 쓸 표 또는 질의를 지정한다.
--column	그림의 열로 쓸 필드 이름을 지정한다.
-s/--schema	쓸 스키마. 기본 스키마를 쓴다면 넘길 필요가 없다.
-b/--bins	구간의 개수. 기본값은 50이다.기본 50
-B/--breaks	사용자 지정 구간 간격.
-W/--binwidth	각 구간의 너비.
-w/--with	앞서 저장한 질의를 입력 데이터로 쓴다.

또 다른 질의를 시도한다. 이번에는 Boston housing 데이터셋이며 라이선스는 CC0: Public Domain이다. 여기서는 age와, 자가 거주 주택의 중위 달러 가치를 천 단위로 나타낸 medv의 분포를 그린다. 열을 두 개 넘긴다는 점을 눈여겨본다.

Jupyter Notebook두 열을 한 그림에

%%sql --save boston
SELECT
    *
FROM boston.csv

%sqlplot histogram --column age medv --table boston

도판 7-13 · 7-14 · Boston 데이터셋의 내용과, age·medv 두 필드의 히스토그램

다음 데이터셋은 Kaggle의 Titanic 데이터셋(titanic_train.csv)이며 라이선스는 Database Contents License(DbCL) v1.0이다. 늘 하던 대로 먼저 질의를 만든다.

Jupyter Notebook구간 수를 정한다

%%sql --save titanic
SELECT
    *
FROM titanic_train.csv
WHERE age NOT NULL AND embarked NOT NULL

# 나이를 10개 구간으로 묶어 분포를 그린다
%sqlplot histogram --column age --bins 10 --table titanic

도판 7-15 · 7-16 · Titanic 데이터셋의 내용과 나이의 분포. 원서는 이 히스토그램에서 승객 대다수가 30대였다고 읽는다

도움말 — 구간이란

히스토그램에서 구간(bin)은 데이터 점을 묶는 데 쓰는 값의 범위이며, 각 구간의 높이가 그 범위 안 데이터의 빈도를 나타낸다.

그림에 손을 대는 법 · AxesSubplot

그림을 변수에 대입해 다듬을 수도 있다. 그 변수의 유형은 matplotlib.axes._subplots.AxesSubplot이다. 이렇게 하면 그림의 여러 국면을 더 통제할 수 있다. 제목과 이름표, 격자선과 눈금을 조정하고, 범례를 고치고, 선과 표지와 막대의 색과 양식을 손보고, 주석이나 추가 서브플롯까지 넣을 수 있다.

Jupyter Notebookpatches를 손본다

!pip install seaborn

ax = %sqlplot histogram --column age --bins 10 …

import seaborn

# https://seaborn.pydata.org/generated/seaborn.co…
palette_color = seaborn.color_palette('pastel')

for i, bar in enumerate(ax.patches):
    bar.set_alpha(0.8)
    bar.set_color(palette_color[i])
    bar.set_edgecolor('black')

ax.set_title("Distribution of Age on Titanic")
ax.set_xlabel("Age")

도판 7-17 · 막대마다 색을 달리 준 히스토그램

도움말 — patches란

Matplotlib에서 patches는 그림에 더할 수 있는 2차원 도형 또는 그래픽 객체이며, 사각형·원·다각형·타원 등의 기하 형태를 나타낸다.

여기서 이 장의 절충이 드러난다. %sqlplot 한 줄은 편하지만 그 한 줄로 정할 수 있는 것은 많지 않다. 세밀하게 손보려면 결국 Matplotlib 객체를 붙들어야 하고, 그러면 서론에서 지적한 그 메모리 부담이 되돌아온다. 편의와 통제를 맞바꾸는 자리이며, 어느 도구에서든 되풀어지는 일이다.

상자 그림

JupySQL로 만들 수 있는 또 하나의 인기 있는 그림이 상자 그림이다. 수염 그림이라고도 하며, 데이터셋의 최소값, 제1사분위수(Q1), 중앙값(Q2), 제3사분위수(Q3), 최대값을 요약해 분포를 표시하는 데 쓰인다. 데이터의 퍼짐과 치우침을 시각적으로 나타내며 잠재적 이상치를 드러낸다. 가운데 상자가 사분위수 범위(IQR)를 감싸 데이터의 가운데 50%를 담고, 상자에서 뻗어 나온 선, 곧 수염이 사분위수 밖 데이터의 범위를 나타내며 이상치는 제외한다. 상자 그림은 여러 집단의 분포를 견주고 데이터의 변동성과 대칭성을 빠르게 알아보는 데 특히 유용하다.

Jupyter Notebookboxplot · 방향 전환

# 앞 절에서 저장한 titanic 스니펫을 쓴다
%sqlplot boxplot --column age --table titanic

# 가로로 그린다
%sqlplot boxplot --column age --table titanic --orient h

도판 7-18 · 7-19 · Titanic 데이터셋 나이의 분포를 보이는 상자 그림. 원은 이상치다. 그리고 가로 방향으로 그린 같은 그림

도움말 — 이상치란

이상치는 데이터셋의 다른 관측과 뚜렷이 벗어나는 데이터 점이다. 통상 데이터의 일반적 분포 밖에 놓인 값으로 정의되며, 나머지 데이터 점보다 훨씬 높거나 낮은 경우가 많다. 이상치는 데이터의 변동성이나 측정 오류로 생길 수 있고, 또는 새로운 현상을 알리는 것일 수도 있다.

옵션	하는 일
-t/--table	쓸 표. DuckDB를 쓴다면 CSV나 JSON 같은 파일 경로를 주어 표처럼 곧바로 질의할 수 있다.
-s/--schema	쓸 스키마. 기본 스키마를 쓴다면 넘길 필요가 없다.
-c/--column	그릴 열. 값을 둘 이상 넘길 수 있다. 예컨대 -c a b c다.
-o/--orient	상자 그림의 방향. h는 가로, v는 세로다.
-w/--with	앞서 저장한 질의를 입력 데이터로 쓴다.

원 차트

airports.csv 파일로 주별 공항 수를 얻는 질의를 만든다. 원 차트를 다룰 만하게 하려고 상위 다섯 주만 추린다.

Jupyter Notebookpie · --show-numbers

%%sql --save airports_by_state
SELECT count(*) as Count, STATE
FROM airports.csv
GROUP BY STATE
ORDER BY Count
DESC LIMIT 5

# 원 차트를 그린다
%sqlplot pie --table airports_by_state --column STATE

도판 7-20 · 7-21 · 공항이 가장 많은 다섯 주와, 조각마다 백분율을 표시한 원 차트

옵션	하는 일
-t/--table	쓸 표. DuckDB를 쓴다면 파일 경로를 주어 표처럼 곧바로 질의할 수 있다.
-s/--schema	쓸 스키마. 기본 스키마를 쓴다면 넘길 필요가 없다.
-c/--column	그릴 열.
-w/--with	앞서 저장한 질의를 입력 데이터로 쓴다.
-S/--show-numbers	원 위에 백분율을 표시한다.

검토자의 주석 — 두 줄로 나뉜 ORDER BY

질의의 마지막 두 줄이 ORDER BY Count와 DESC LIMIT 5로 나뉘어 있다. SQL은 줄바꿈을 공백으로 다루므로 실행에는 문제가 없으나, 읽는 사람에게는 ORDER BY Count에서 문장이 끝난 듯 보인다. 제5장에서 원 차트를 다듬어 갔던 그 절과 같은 데이터를 쓰고 있으니, 함께 놓고 보면 도구가 달라져도 하는 일은 같다는 것이 드러난다.

막대 그림 · 그리고 겹쳐 그리기

앞 절에서 저장한 airports_by_state 스니펫으로 주별 공항 수를 막대 차트로 그린다. --show-numbers 옵션은 막대마다 그 위에 숫자를 표시한다.

Jupyter Notebookbar · 색 손보기

%sqlplot bar --table airports_by_state --column S…

# AxesSubplot의 patches로 막대를 손본다
ax = %sqlplot bar --table airports_by_state --col…

import seaborn

palette_color = seaborn.color_palette('pastel')

# 막대를 하나씩 돈다
for i, bar in enumerate(ax.patches):
    bar.set_alpha(0.8)              # 투명도
    bar.set_color(palette_color[i])   # 색
    bar.set_edgecolor('black')        # 테두리 색

ax.set_title("Number of airports for each state")
ax.set_xlabel("State")

도판 7-22 · 7-23 · 주별 공항 수를 그린 막대 차트와, 막대마다 색을 달리 주고 축 이름표와 제목을 고친 것

다시 Titanic 데이터셋으로 돌아간다. 연령대에 따른 생존율을 살펴 나이가 생존 가능성에 영향을 주었는지 알아보려 한다. 그러려면 여러 연령대를 정의하고, 각 집단에서 몇이 살고 몇이 죽었는지를 세야 한다.

Jupyter NotebookCASE로 연령대를 만든다

%%sql --save titanic_age_groups
SELECT
    AgeGroup,
    SUM(CASE WHEN Survived = 1 THEN 1 ELSE 0 END) AS SurvivedCount,
    SUM(CASE WHEN Survived = 0 THEN 1 ELSE 0 END) AS NotSurvivedCount
FROM (
    SELECT
        CASE
            WHEN Age >= 0  AND Age < 10 THEN '0-9'
            WHEN Age >= 10 AND Age < 20 THEN '10-19'
            WHEN Age >= 20 AND Age < 30 THEN '20-29'
            WHEN Age >= 30 AND Age < 40 THEN '30-39'
            WHEN Age >= 40 AND Age < 50 THEN '40-49'
            WHEN Age >= 50 AND Age < 60 THEN '50-59'
            ELSE '60+' -- 60세 이상은 여기에 든다고 본다
        END AS AgeGroup,
        Survived
    FROM titanic_train.csv
) AS AgeGroups
GROUP BY AgeGroup
ORDER BY AgeGroup;

도판 7-24 · 나이에 따른 생존을 알아보는 질의의 출력. 제3장의 부질의와 제5장의 CASE가 여기서 한 문장에 모인다

살아남은 이들의 막대 차트를 그리고, 그 위에 살아남지 못한 이들의 막대 차트를 겹쳐 그린다.

Jupyter Notebook두 그림을 겹친다

ax1 = %sqlplot bar --column AgeGroup SurvivedCount --table titanic_age_groups
for i, bar in enumerate(ax1.patches):
    bar.set_alpha(0.4)
    bar.set_edgecolor('black')

ax2 = %sqlplot bar --column AgeGroup NotSurvivedCount --table titanic_age_groups
ax2.legend(["Survived", "Did not survive"],loc='upper right')
for i, bar in enumerate(ax2.patches):
    bar.set_alpha(0.4)
    bar.set_edgecolor('black')
ax2.set_ylabel("Count")
ax2.set_title("Survivability for different age groups")

도판 7-25 · 7-26 · 생존자의 막대 차트, 그리고 두 막대 차트가 서로 겹친 그림

고친 차트에서 읽어 낼 수 있는 것은 셋이다. 9세 이하 아동은 절반 이상이 살아남았고, 다른 연령대에서는 절반 이상이 죽었다. 그리고 60세를 넘긴 승객의 사망 비율이 어느 연령대보다도 높다.

여기서 그림 두 장을 겹치는 기법이 값을 한다. 생존자만 그린 도판 7-25에서는 “30대 생존자가 가장 많다”는 사실만 보인다. 그러나 그 연령대에 승객 자체가 많았으므로 그것은 비율에 관한 정보가 아니다. 사망자를 겹쳐 놓아야 비로소 비율이 눈에 든다. 제5장이 세 번 되풀어 가르친 그 교훈, 세는 것보다 나누는 것이 결론을 정한다는 말이 여기서 그림의 문법으로 되돌아온다.

遠

먼 서버에 붙는 세 가지 길

INTEGRATING WITH MYSQL

여기까지의 예는 모두 CSV 파일에 기반했다. 그러나 현실에서는 많은 데이터가 데이터베이스, 특히 MySQL이나 PostgreSQL 같은 데이터베이스 서버에 놓여 있다. JupySQL이 데이터베이스 서버에 접속하려면 그 서버에 맞는 드라이버를 설치해야 한다.

Jupyter Notebook드라이버

!conda install mysqlclient -c conda-forge -y

일러두기

JupySQL은 PostgreSQL, Microsoft SQL Server, Oracle을 비롯한 다른 데이터베이스 서버에도 접속할 수 있다. 여러 데이터 원천과 플랫폼에서 JupySQL을 쓰는 방법에 관한 정보는 JupySQL Quick Start 안내에서 얻을 수 있다.

JupySQL에서 데이터베이스 서버에 접속하는 주된 방법은 셋이다. 환경 변수, .ini 파일, keyring이다. 이 절의 구조가 곧 보안의 등급이다. 뒤로 갈수록 비밀번호가 코드에서 멀어진다.

방법	비밀번호가 놓이는 자리	남는 문제
환경 변수	getpass()로 매번 입력받아 환경 변수에 담는다. 코드에 박히지 않는다.	안전하기는 하나 노트북을 실행할 때마다 사용자가 비밀번호를 입력해야 한다. 노트북 실행을 자동화하려 할 때 문제가 된다.
.ini 파일	별도의 설정 파일에 적어 둔다. 노트북 안에 박히지 않는다.	그럼에도 비밀번호가 connections.ini에 평문으로 저장된다.
keyring	운영체제의 자격 증명 관리자에 안전하게 저장한다.	원서가 권하는 방법이다. 셋 가운데 가장 안전하다.

이 절의 예는 제2장에서 서술한 것과 같은 상황을 전제한다. 컴퓨터에 MySQL 서버 인스턴스가 실행 중이고, My_DB라는 데이터베이스에 airlines라는 표 하나가 있으며, 서버에 “user1”이라는 계정이 있고 비밀번호는 “password”이며 그 계정이 My_DB와 그 표에 접근할 권한을 가진다.

① 환경 변수

먼저 데이터베이스를 가리키는 SQLAlchemy URL 표준 접속 문자열을 만들어 DATABASE_URL 환경 변수에 대입한다. 그러면 %sql 매직 명령이 지정된 데이터베이스 서버에 자동으로 접속한다. 사용자 계정의 비밀번호를 코드에 박아 넣는 대신 getpass() 함수로 사용자에게 물어 받는다.

Jupyter NotebookDATABASE_URL

from getpass import getpass

password = getpass()
username = 'user1'
host = 'localhost'
db = 'My_DB'

# 접속 문자열은 SQLAlchemy URL 표준을 따른다
connection_string = f"mysql://{username}:{password}@{host}/{db}"

# 완성된 접속 문자열의 형태 — 비밀번호가 password라고 가정한다
# 'mysql://user1:password@localhost/My_DB'

from os import environ
environ["DATABASE_URL"] = connection_string

%load_ext sql

# 연결을 적재한다 — JupySQL이 자동으로 붙는다
%sql

# 연결 목록을 본다
%sql --connections

# 연결 안의 표를 본다
%sqlcmd tables

%%sql
SELECT * FROM airlines

도판 7-27 ~ 7-30 · 비밀번호를 묻는 화면, MySQL 데이터베이스로의 연결, My_DB의 표, 그리고 airlines 표의 내용

② .ini 파일

데이터베이스 접속 상세를 .ini 파일에 저장하는 방식은 설정을 코드에서 떼어 내 유연성과 보안을 높인다. 코드베이스를 고치지 않고도 개발·시험·운영 환경마다 다른 설정을 쓸 수 있어 유지와 갱신이 쉬워진다. 사용자 이름과 비밀번호 같은 민감한 정보를 소스 코드 밖에 둘 수 있어, 버전 관리 시스템에 노출될 위험이 줄어든다.

%config 행 매직 명령의 SqlMagic.dsn_filename 옵션으로 JupySQL이 접속 상세를 찾을 파일을 확인한다.

Jupyter Notebook파일의 자리

%load_ext sql

%config SqlMagic.dsn_filename
# '/Users/weimenglee/.jupysql/connections.ini'

# 자리를 바꿀 수도 있다 — 노트북과 같은 폴더로
%config SqlMagic.dsn_filename = "connections.ini"

기본적으로 JupySQL은 홈 디렉터리의 숨은 폴더 .jupysql 안에 있는 connections.ini 파일을 찾는다

connections.ini두 절

[mysqldb]
drivername = mysql
username = user1
password = password
host = localhost
port = 3306
database = My_DB

[mysqldb2]
drivername = mysql
username = user1
password = password
host = localhost
port = 3306
database = Titanic

첫 절은 My_DB 데이터베이스에 접속하는 상세를, 둘째 절은 Titanic 데이터베이스에 접속하는 상세를 담는다

검토자의 주석 — 절 이름의 어긋남

원서 본문은 “우리 파일에는 mysql과 mysqldb2 두 절이 있다”고 적는다. 그러나 인쇄된 파일의 첫 절 이름은 [mysqldb]이며, 뒤에 이어지는 명령도 %sql --section mysqldb다. 본문의 “mysql”은 “mysqldb”의 오기로 보인다. 파일과 명령이 서로 맞으므로 그 둘을 기준으로 읽는 것이 맞다.

Jupyter Notebook절을 갈아타기

# mysqldb 절의 설정을 적재한다
%sql --section mysqldb
# Connecting and switching to connection 'mysqldb'

# 현재 연결 목록 — 환경 변수로 만든 것과 방금 만든 것, 둘이 된다
%sql --connections

# 연결이 되는지 확인한다
%%sql
SELECT * FROM airlines

# mysqldb2 절을 적재한다 — 연결이 셋이 된다
%sql --section mysqldb2
# Connecting and switching to connection 'mysqldb2'

# 다른 연결로 갈아타려면 이름을 준다
%sql mysqldb

# 환경 변수로 만든 첫 연결로 돌아가려면 접속 문자열을 준다
%sql mysql://user1:***@localhost/My_DB

도판 7-31 · 7-32 · 두 개의 연결과 세 개의 연결. 목록의 별표(*)가 현재 활성 연결을 나타낸다

연결을 여러 개 붙들고 별표로 활성 하나를 표시하는 방식은 제3장의 ATTACH와 USE를 떠올리게 한다. 그때도 먼저 나열된 것이 활성이었고 USE로 갈아탔다. 별채를 여러 채 붙여 두고 지금 어느 채에 앉아 있는지를 표시해 두는 방식이 도구마다 되풀어진다.

③ keyring

훨씬 안전한 접근은 운영체제의 자격 증명 관리자를 써서 비밀번호를 안전하게 저장하는 것이다. keyring 라이브러리가 파이썬에서 시스템 키링 서비스에 접근하는 손쉬운 길을 제공한다.

Jupyter Notebookkeyring · 한 번만 저장한다

!pip install keyring

# 접속 상세의 상수를 정의한다
username = 'user1'
host = 'localhost'
db = 'My_DB'

# 비밀번호를 물어 받아 운영체제에 저장한다
from getpass import getpass
import keyring

password = getpass()
keyring.set_password(db, username, password)

비밀번호가 저장되면 이 코드는 더 이상 필요하지 않으므로 지워도 된다

Jupyter Notebook이후에는 꺼내 쓴다

import keyring

password = keyring.get_password(db, username)
db_url = f"mysql://{username}:{password}@{host}/{db}"

# SQLAlchemy로 데이터베이스 엔진을 만든다
from sqlalchemy import create_engine
engine = create_engine(db_url)

# 엔진으로 연결을 적재한다
%load_ext sql
%sql engine

# 연결이 제대로 세워졌는지 확인한다
%sql --connections

도판 7-33 · JupySQL의 현재 연결들. 활성 연결이 환경 변수 방법으로 적재한 것과 같다는 점을 눈여겨본다

세 방법의 차이가 결국 비밀번호를 어디에 두는가에 있다는 사실이 이 절의 요지다. 코드에 두면 노출되고, 파일에 두면 평문으로 남고, 운영체제에 맡기면 그 관리자가 지켜 준다. 그리고 %sql engine 한 줄로 SQLAlchemy 엔진 객체를 곧바로 넘길 수 있다는 점은, 앞서 세 겹으로 나눈 그 구조가 다시 유용해지는 자리다.