JupySQL과 chDB

chDB for Bun 설치 방법

JupySQL은 Jupyter 노트북과 IPython 셸에서 SQL을 실행할 수 있도록 해주는 Python 라이브러리입니다. 이 가이드에서는 chDB와 JupySQL을 사용해 데이터를 쿼리하는 방법을 알아보겠습니다.

​

준비

먼저 가상 환경을 생성합니다:

python -m venv .venv
source .venv/bin/activate

그런 다음 JupySQL, IPython, Jupyter Lab을 설치합니다:

pip install jupysql ipython jupyterlab

다음을 실행해 시작한 IPython에서 JupySQL을 사용할 수 있습니다:

ipython

또는 Jupyter Lab에서는 다음을 실행하세요:

jupyter lab

Jupyter Lab을 사용하는 경우, 이 가이드의 나머지 단계를 진행하기 전에 먼저 노트북을 만들어야 합니다.

​

데이터셋 다운로드

시간에 따른 선수 정보와 랭킹 메타데이터가 포함된 Jeff Sackmann’s tennis_atp 데이터셋 중 하나를 사용합니다. 먼저 랭킹 파일을 다운로드하겠습니다:

from urllib.request import urlretrieve

files = ['00s', '10s', '20s', '70s', '80s', '90s', 'current']
base = "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master"
for file in files:
  _ = urlretrieve(
    f"{base}/atp_rankings_{file}.csv",
    f"atp_rankings_{file}.csv",
  )

​

chDB 및 JupySQL 구성하기

다음으로, chDB용 dbapi 모듈을 불러오겠습니다:

from chdb import dbapi

그리고 chDB 연결을 생성하겠습니다. 영구 저장하는 모든 데이터는 atp.chdb 디렉터리에 저장됩니다:

conn = dbapi.connect(path="atp.chdb")

이제 sql 매직을 불러오고 chDB 연결을 만듭니다:

%load_ext sql
%sql conn --alias chdb

다음으로, 쿼리 결과가 잘리지 않도록 표시 한도를 설정합니다:

%config SqlMagic.displaylimit = None

## CSV 파일의 데이터 쿼리

atp_rankings 접두사가 붙은 파일을 여러 개 다운로드했습니다. 스키마를 파악하기 위해 DESCRIBE 절을 사용해 보겠습니다:

%%sql
DESCRIBE file('atp_rankings*.csv')
SETTINGS describe_compact_output=1,
         schema_inference_make_columns_nullable=0

+--------------+-------+
|     name     |  type |
+--------------+-------+
| ranking_date | Int64 |
|     rank     | Int64 |
|    player    | Int64 |
|    points    | Int64 |
+--------------+-------+

이 파일들에 직접 SELECT 쿼리를 작성하여 데이터 형태를 확인할 수도 있습니다:

%sql SELECT * FROM file('atp_rankings*.csv') LIMIT 1

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|   20000110   |  1   | 101736 |  4135  |
+--------------+------+--------+--------+

데이터 포맷이 약간 이상합니다. 날짜를 정리한 뒤 REPLACE 절을 사용해 정리된 ranking_date를 반환해 보겠습니다:

%%sql
SELECT * REPLACE (
  toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
LIMIT 10
SETTINGS schema_inference_make_columns_nullable=0

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|  2000-01-10  |  1   | 101736 |  4135  |
|  2000-01-10  |  2   | 102338 |  2915  |
|  2000-01-10  |  3   | 101948 |  2419  |
|  2000-01-10  |  4   | 103017 |  2184  |
|  2000-01-10  |  5   | 102856 |  2169  |
|  2000-01-10  |  6   | 102358 |  2107  |
|  2000-01-10  |  7   | 102839 |  1966  |
|  2000-01-10  |  8   | 101774 |  1929  |
|  2000-01-10  |  9   | 102701 |  1846  |
|  2000-01-10  |  10  | 101990 |  1739  |
+--------------+------+--------+--------+

​

CSV 파일을 chDB로 가져오기

이제 이 CSV 파일의 데이터를 테이블에 저장하겠습니다. 기본 데이터베이스는 데이터를 디스크에 영구 저장하지 않으므로, 먼저 다른 데이터베이스를 생성해야 합니다:

%sql CREATE DATABASE atp

이제 CSV 파일의 데이터 구조를 바탕으로 스키마(schema)가 결정되는 rankings라는 테이블(table)을 생성합니다:

%%sql
CREATE TABLE atp.rankings
ENGINE=MergeTree
ORDER BY ranking_date AS
SELECT * REPLACE (
  toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
SETTINGS schema_inference_make_columns_nullable=0

테이블에 있는 데이터를 간단히 확인해 보겠습니다:

%sql SELECT * FROM atp.rankings LIMIT 10

+--------------+------+--------+--------+
| ranking_date | rank | player | points |
+--------------+------+--------+--------+
|  2000-01-10  |  1   | 101736 |  4135  |
|  2000-01-10  |  2   | 102338 |  2915  |
|  2000-01-10  |  3   | 101948 |  2419  |
|  2000-01-10  |  4   | 103017 |  2184  |
|  2000-01-10  |  5   | 102856 |  2169  |
|  2000-01-10  |  6   | 102358 |  2107  |
|  2000-01-10  |  7   | 102839 |  1966  |
|  2000-01-10  |  8   | 101774 |  1929  |
|  2000-01-10  |  9   | 102701 |  1846  |
|  2000-01-10  |  10  | 101990 |  1739  |
+--------------+------+--------+--------+

좋습니다. 예상한 대로 출력 결과는 CSV 파일에 직접 쿼리했을 때와 동일합니다.

이제 플레이어 메타데이터에도 같은 과정을 적용하겠습니다. 이번에는 데이터가 모두 하나의 CSV 파일에 들어 있으므로 해당 파일을 다운로드하겠습니다:

_ = urlretrieve(
    f"{base}/atp_players.csv",
    "atp_players.csv",
)

그런 다음 CSV 파일의 내용을 바탕으로 players라는 테이블을 생성합니다. 또한 dob 필드를 정리하여 Date32 유형이 되도록 합니다.

ClickHouse에서 Date 유형은 1970년 이후 날짜만 지원합니다. dob 컬럼에는 1970년 이전 날짜가 포함되어 있으므로 대신 Date32 유형을 사용합니다.

%%sql
CREATE TABLE atp.players
Engine=MergeTree
ORDER BY player_id AS
SELECT * REPLACE (
  makeDate32(
    toInt32OrNull(substring(toString(dob), 1, 4)),
    toInt32OrNull(substring(toString(dob), 5, 2)),
    toInt32OrNull(substring(toString(dob), 7, 2))
  )::Nullable(Date32) AS dob
)
FROM file('atp_players.csv')
SETTINGS schema_inference_make_columns_nullable=0

실행이 완료되면 수집된 데이터를 확인할 수 있습니다:

%sql SELECT * FROM atp.players LIMIT 10

+-----------+------------+-----------+------+------------+-----+--------+-------------+
| player_id | name_first | name_last | hand |    dob     | ioc | height | wikidata_id |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
|   100001  |  Gardnar   |   Mulloy  |  R   | 1913-11-22 | USA |  185   |    Q54544   |
|   100002  |   Pancho   |   Segura  |  R   | 1921-06-20 | ECU |  168   |    Q54581   |
|   100003  |   Frank    |  Sedgman  |  R   | 1927-10-02 | AUS |  180   |   Q962049   |
|   100004  |  Giuseppe  |   Merlo   |  R   | 1927-10-11 | ITA |   0    |   Q1258752  |
|   100005  |  Richard   |  Gonzalez |  R   | 1928-05-09 | USA |  188   |    Q53554   |
|   100006  |   Grant    |   Golden  |  R   | 1929-08-21 | USA |  175   |   Q3115390  |
|   100007  |    Abe     |   Segal   |  L   | 1930-10-23 | RSA |   0    |   Q1258527  |
|   100008  |    Kurt    |  Nielsen  |  R   | 1930-11-19 | DEN |   0    |   Q552261   |
|   100009  |   Istvan   |   Gulyas  |  R   | 1931-10-14 | HUN |   0    |    Q51066   |
|   100010  |    Luis    |   Ayala   |  R   | 1932-09-18 | CHI |  170   |   Q1275397  |
+-----------+------------+-----------+------+------------+-----+--------+-------------+

​

chDB 쿼리하기

데이터 수집이 완료되었으니, 이제 가장 흥미로운 단계인 데이터 쿼리 차례입니다!

테니스 선수는 출전한 토너먼트에서의 성과에 따라 포인트를 받습니다. 각 선수의 포인트는 최근 52주를 기준으로 계산됩니다. 이제 각 선수가 누적한 최대 포인트와 해당 시점의 순위를 찾는 쿼리를 작성하겠습니다:

%%sql
SELECT name_first, name_last,
       max(points) as maxPoints,
       argMax(rank, points) as rank,
       argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC
LIMIT 10

+------------+-----------+-----------+------+------------+
| name_first | name_last | maxPoints | rank |    date    |
+------------+-----------+-----------+------+------------+
|   Novak    |  Djokovic |   16950   |  1   | 2016-06-06 |
|   Rafael   |   Nadal   |   15390   |  1   | 2009-04-20 |
|    Andy    |   Murray  |   12685   |  1   | 2016-11-21 |
|   Roger    |  Federer  |   12315   |  1   | 2012-10-29 |
|   Daniil   |  Medvedev |   10780   |  2   | 2021-09-13 |
|   Carlos   |  Alcaraz  |    9815   |  1   | 2023-08-21 |
|  Dominic   |   Thiem   |    9125   |  3   | 2021-01-18 |
|   Jannik   |   Sinner  |    8860   |  2   | 2024-05-06 |
|  Stefanos  | Tsitsipas |    8350   |  3   | 2021-09-20 |
| Alexander  |   Zverev  |    8240   |  4   | 2021-08-23 |
+------------+-----------+-----------+------+------------+

이 목록에 있는 일부 선수들이 해당 총점으로 1위는 아니었음에도 많은 점수를 쌓았다는 점이 꽤 흥미롭습니다.

​

쿼리 저장

%%sql 매직과 같은 줄에서 --save 매개변수를 사용하면 쿼리를 저장할 수 있습니다. --no-execute 매개변수를 사용하면 쿼리 실행을 건너뜁니다.

%%sql --save best_points --no-execute
SELECT name_first, name_last,
       max(points) as maxPoints,
       argMax(rank, points) as rank,
       argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC

저장된 쿼리를 실행하면 실행 전에 공통 테이블 표현식(Common Table Expression, CTE)으로 변환됩니다. 다음 쿼리에서는 플레이어가 1위였을 때 획득한 최대 포인트를 계산합니다:

%sql select * FROM best_points WHERE rank=1

+-------------+-----------+-----------+------+------------+
|  name_first | name_last | maxPoints | rank |    date    |
+-------------+-----------+-----------+------+------------+
|    Novak    |  Djokovic |   16950   |  1   | 2016-06-06 |
|    Rafael   |   Nadal   |   15390   |  1   | 2009-04-20 |
|     Andy    |   Murray  |   12685   |  1   | 2016-11-21 |
|    Roger    |  Federer  |   12315   |  1   | 2012-10-29 |
|    Carlos   |  Alcaraz  |    9815   |  1   | 2023-08-21 |
|     Pete    |  Sampras  |    5792   |  1   | 1997-08-11 |
|    Andre    |   Agassi  |    5652   |  1   | 1995-08-21 |
|   Lleyton   |   Hewitt  |    5205   |  1   | 2002-08-12 |
|   Gustavo   |  Kuerten  |    4750   |  1   | 2001-09-10 |
| Juan Carlos |  Ferrero  |    4570   |  1   | 2003-10-20 |
|    Stefan   |   Edberg  |    3997   |  1   | 1991-02-25 |
|     Jim     |  Courier  |    3973   |  1   | 1993-08-23 |
|     Ivan    |   Lendl   |    3420   |  1   | 1990-02-26 |
|     Ilie    |  Nastase  |     0     |  1   | 1973-08-27 |
+-------------+-----------+-----------+------+------------+

​

매개변수를 사용한 쿼리

쿼리에서도 매개변수를 사용할 수 있습니다. 매개변수는 일반 변수와 같습니다:

rank = 10

그런 다음 쿼리에서 {{variable}} 구문을 사용할 수 있습니다. 다음 쿼리는 선수가 처음으로 상위 10위 안에 들었던 시점부터 마지막으로 상위 10위 안에 들었던 시점까지의 일수 차이가 가장 짧았던 선수를 찾습니다:

%%sql
SELECT name_first, name_last,
       MIN(ranking_date) AS earliest_date,
       MAX(ranking_date) AS most_recent_date,
       most_recent_date - earliest_date AS days,
       1 + (days/7) AS weeks
FROM atp.rankings
JOIN atp.players ON players.player_id = rankings.player
WHERE rank <= {{rank}}
GROUP BY ALL
ORDER BY days
LIMIT 10

+------------+-----------+---------------+------------------+------+-------+
| name_first | name_last | earliest_date | most_recent_date | days | weeks |
+------------+-----------+---------------+------------------+------+-------+
|    Alex    | Metreveli |   1974-06-03  |    1974-06-03    |  0   |   1   |
|   Mikael   |  Pernfors |   1986-09-22  |    1986-09-22    |  0   |   1   |
|   Felix    |  Mantilla |   1998-06-08  |    1998-06-08    |  0   |   1   |
|   Wojtek   |   Fibak   |   1977-07-25  |    1977-07-25    |  0   |   1   |
|  Thierry   |  Tulasne  |   1986-08-04  |    1986-08-04    |  0   |   1   |
|   Lucas    |  Pouille  |   2018-03-19  |    2018-03-19    |  0   |   1   |
|    John    | Alexander |   1975-12-15  |    1975-12-15    |  0   |   1   |
|  Nicolas   |   Massu   |   2004-09-13  |    2004-09-20    |  7   |   2   |
|   Arnaud   |  Clement  |   2001-04-02  |    2001-04-09    |  7   |   2   |
|  Ernests   |   Gulbis  |   2014-06-09  |    2014-06-23    |  14  |   3   |
+------------+-----------+---------------+------------------+------+-------+

​

히스토그램 그리기

JupySQL에는 제한적이지만 차트 기능도 있습니다. 상자 그림이나 히스토그램을 만들 수 있습니다.

이제 히스토그램을 만들겠습니다. 먼저 각 플레이어가 상위 100위 안에서 달성한 순위를 계산하는 쿼리를 작성해 저장하겠습니다. 이 쿼리를 사용하면 각 순위를 달성한 플레이어 수를 집계하는 히스토그램을 만들 수 있습니다:

%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100

그런 다음 다음을 실행해 히스토그램을 만들 수 있습니다.

from sql.ggplot import ggplot, geom_histogram, aes

plot = (
  ggplot(
    table="players_per_rank",
    with_="players_per_rank",
    mapping=aes(x="rank", fill="#69f0ae", color="#fff"),
  ) + geom_histogram(bins=100)
)