JupySQL과 chDB

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

설정

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

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

그다음 JupySQL, IPython, JupyterLab을 설치합니다:

pip install jupysql ipython jupyterlab

IPython에서 JupySQL을 사용할 수 있으며, 다음 명령으로 IPython을 실행할 수 있습니다.

ipython

또는 Jupyter Lab에서 다음을 실행합니다:

jupyter lab

참고

JupyterLab을 사용하는 경우 나머지 가이드를 따라 하기 전에 먼저 노트북을 만들어야 합니다.

데이터셋 다운로드하기

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  |
+--------------+------+--------+--------+

chDB로 CSV 파일 가져오기

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

%sql CREATE DATABASE atp

이제 CSV 파일의 데이터 구조를 기반으로 스키마를 자동으로 유도하여 rankings라는 테이블을 생성합니다:

%%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주 롤링(rolling) 기준으로 계산됩니다. 각 선수가 특정 시점에 기록한 최대 포인트 합계와 그때의 순위를 찾는 쿼리를 작성해 보겠습니다:

%%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)
)

설정​

데이터셋 다운로드하기​

chDB 및 JupySQL 구성​

chDB로 CSV 파일 가져오기​

chDB 쿼리 실행​

쿼리 저장하기​

매개변수를 사용한 쿼리 실행​

히스토그램 그리기​

설정